python - Python：将日期列表传递给 PostgreSQL 查询并循环 n 次

Question

嗨，我使用的是 PostgreSQL 12，并且有一个由几个 CTE 组成的 INSERT sql 语句，如下所示：

WITH CTE1 as (
select date, count(*) from customers where date = %(date_list)s group by date
),
CTE2 as (
select date, count(*) from customers where account_deleted is NULL and date = %(date_list)s group by date
)
INSERT INTO customers_final (date, customers)
select * from CTE1 
union all
select * from CTE2 

我现在想使用 Python 循环遍历不同客户数据集的日期列表（随着时间的推移，客户的不同快照）并多次运行上述查询，针对与我列表中不同日期对应的每个客户数据集运行一次。

Example of list: ('2021-03-21', '2021-03-22', '2021-03-23', etc). 

预期结果应该是每个数据集每个日期一行（一个 CTE）。由于我们在示例中有 2 个 CTE，我们应该期望每个数据集每个日期有 2 行。

下面应该适用于单个数据集。不知道如何还遍历不同的表（客户表）：

def insert_data(date_list: list):

sql_query = """WITH CTE1 as (
select date, count(*) from customers where date = %(date_list)s group by date
),
CTE2 as (
select date, count(*) from customers where account_deleted is NULL and date = %(date_list)s group by date
)
INSERT INTO customers_final (date, customers)
select * from CTE1 
union all
select * from CTE2""" (

 for date in date_list:
        if date_list is not None and len(date_list) > 0:
            params = {"date": date}
            cursor.execute(sql_query, {"date": date})

        else:
            log.warning(f'No records found.')


if __name__ == '__main__':
    date_list = ['2021-03-29', '2021-03-28', '2021-03-27', '2021-03-26']
    insert_data(date_list)

标签： pythonsqlpostgresqlpsycopg2