任何制作参数化查询并将其封装为python函数的方法

Question

我想实现一个python函数，该函数将使用参数执行SQL查询。为此，我开始使用psycopg2访问本地数据库。然而，我已经编写了一堆非常相似的SQL查询，而每个SQL语句在取值方面略有不同。我的目标是编写参数化SQL，这样我就可以将其封装在python函数中，理想情况下，我可以使用任意参数进行函数调用，这样它就可以替换SQL语句中的参数值。我查阅了SO的帖子，得到了一些想法，但无法实现紧凑的python函数，该函数可以用任意参数执行SQL语句。我知道如何使用**kwargs、*args编写带有传递任意参数的python函数，但不知道如何在python函数中为参数SQl编写。有没有什么有效的方法可以在python中轻松做到这一点？有什么可行的方法可以实现这一点？

我的数据库架构：

这是我在postgresql:中的表模式

CREATE TABLE mytable(
date_received DATE,
pk_est VARCHAR,
grd_name VARCHAR,
cl_val SMALLINT,
quant_received NUMERIC,
mg_fb_price NUMERIC,
freight NUMERIC,
standard_price NUMERIC,
grd_delv_cost NUMERIC,
order_type VARCHAR,
pk_name VARCHAR,
item_type VARCHAR,
waiting_days NUMERIC,
item_name VARCHAR,
mk_price_variance NUMERIC,
);

我的示例SQL查询

下面是一个需要参数化的SQL查询示例：

SELECT
date_trunc('week', date_received) AS received_week,
cl_val,
ROUND(ROUND(SUM(quant_received * standard_price)::numeric,4) / SUM(quant_received),4) AS mk_price_1,
ROUND(ROUND(SUM(quant_received * mg_fb_price)::numeric,4) / SUM(quant_received),4) AS mg_price_1,
ROUND(ROUND(SUM(quant_received * mk_price_variance)::numeric,4) / SUM(quant_received),4) AS fb_mk_price_var,
ROUND(ROUND(SUM(quant_received * freight)::numeric,4) / SUM(quant_received),4) AS freight_new,
ROUND(ROUND(SUM(quant_received * grd_delv_cost)::numeric,4) / SUM(quant_received),4) AS grd_delv_cost_new,
TO_CHAR(SUM(quant_received), '999G999G990D') AS Volume_Received
FROM mytable
WHERE date_received >= to_date('2010-10-01','YYYY-MM-DD')
AND date_received <= to_date('2012-12-31','YYYY-MM-DD')
AND item_type = 'processed'
AND cl_val IN ('12.5','6.5','8.1','8.5','9.0')
AND order_type IN ('formula')
AND pk_name IN ('target','costco','AFG','KFC')
AND pk_est NOT IN ('12')
GROUP BY received_week,cl_val
ORDER BY received_week ASC ,cl_val ASC;

我当前的尝试：

import psycopg2
connection = psycopg2.connect(database="myDB", user="postgres", password="passw", host="localhost", port=5432)
cursor = connection.cursor()
cursor.execute(
"""
select * from mytable where date_received < any(array['2019-01-01'::timestamp, '2020-07-10'::timestamp])
""")
record = cursor.fetchmany()

另一次尝试：

cursor.execute("""
select date_trunc('week', date_received) AS received_week,
cl_val,
ROUND(ROUND(SUM(quant_received * standard_price)::numeric,4) / SUM(quant_received),4) AS mk_price_1,
from (
select * from mytable 
where  item_type = %s and order_type IN %s
) t;
""", (item_type_value, order_type_value))
results = [r[0] for r in cursor.fetchall()]

但在我的代码中，有很多硬编码的部分需要参数化。我想知道在python中有什么方法可以做到这一点。有人能告诉我如何做到这一点吗？用python函数实现参数化SQL可行吗？知道吗？感谢

目标

我希望实现这样的功能：

def parameterized_sql(**kwargs, *args):
connection = psycopg2.connect(database="myDB", user="postgres", password="passw", host="localhost", port=5432)
cursor = connection.cursor()
cursor.execute("""SQL statement with parameter""")
## maybe more

这只是python函数的一个骨架，我想实现它，但不确定它是否可行。任何反馈都会有所帮助。感谢

更新：

我期待着通用的python函数，它可以将参数值传递给SQL主体，这样我就可以避免编写许多SQL查询，这些查询实际上在彼此之间有很多重叠，而且它不是参数化的。目标是使参数化SQL查询可以在python函数中执行。

Answer 1

如果要将命名参数传递给cursor.execute()，可以使用%(name)s语法并传入dict。有关更多详细信息，请参阅文档。

以下是使用您的查询的示例：

import datetime
import psycopg2
EXAMPLE_QUERY = """
SELECT
date_trunc('week', date_received) AS received_week,
cl_val,
ROUND(ROUND(SUM(quant_received * standard_price)::numeric,4) / SUM(quant_received),4) AS mk_price_1,
ROUND(ROUND(SUM(quant_received * mg_fb_price)::numeric,4) / SUM(quant_received),4) AS mg_price_1,
ROUND(ROUND(SUM(quant_received * mk_price_variance)::numeric,4) / SUM(quant_received),4) AS fb_mk_price_var,
ROUND(ROUND(SUM(quant_received * freight)::numeric,4) / SUM(quant_received),4) AS freight_new,
ROUND(ROUND(SUM(quant_received * grd_delv_cost)::numeric,4) / SUM(quant_received),4) AS grd_delv_cost_new,
TO_CHAR(SUM(quant_received), '999G999G990D') AS Volume_Received
FROM mytable
WHERE date_received >= %(min_date_received)s
AND date_received <= %(max_date_received)s
AND item_type = %(item_type)s
AND cl_val IN %(cl_vals)s
AND order_type IN %(order_types)s
AND pk_name IN %(pk_names)s
AND pk_est NOT IN %(pk_ests)s
GROUP BY received_week ,cl_val
ORDER BY received_week ASC, cl_val ASC;
"""

def execute_example_query(cursor, **kwargs):
"""Execute the example query with given parameters."""
cursor.execute(EXAMPLE_QUERY, kwargs)
return cursor.fetchall()

if __name__ == '__main__':
connection = psycopg2.connect(database="myDB", user="postgres", password="passw", host="localhost", port=5432)
cursor = connection.cursor()
execute_example_query(
cursor,
min_date_received = datetime.date(2010, 10, 1),
max_date_received = datetime.date(2012, 12, 31),
item_type = 'processed',
cl_vals = ('12.5', '6.5', '8.1', '8.5', '9.0'),
order_types = ('formula',),
pk_names = ('target', 'costco', 'AFG', 'KFC'),
pk_ests = ('12',)
)

Answer 2

看看：

https://www.psycopg.org/docs/sql.html

"该模块包含可用于以方便和安全的方式动态生成SQL的对象和函数。SQL标识符(例如表和字段的名称(不能像查询参数一样传递给execute((方法：；

这里有很多例子。如果他们没有显示你想做什么，那么修改你的问题，以显示你想如何更改查询的具体示例。

下面是我在代码中使用的一个示例：

insert_list_sql = sql.SQL("""INSERT INTO
notification_list ({}) VALUES ({}) RETURNING list_id
""").format(sql.SQL(", ").join(map(sql.Identifier, list_flds)),
sql.SQL(", ").join(map(sql.Placeholder, list_flds)))

list_flds是我从attrs数据类中获取的字段列表，如果我修改该类，这些字段可能会更改。在这种情况下，我不修改表名，但没有什么可以阻止您添加用{}替换表名，然后以另一个sql的格式提供表名。SQL((。只需将上面的内容封装在一个函数中，该函数接受要使其成为动态的参数。