如何在Pandas SQL查询中动态传递变量值 [英] How to pass variable values dynamically in pandas sql query

问题描述

如何动态传递可变参数

order = 10100

status = 'Shipped'

df1 = pd.read_sql_query("SELECT  * from orders where orderNumber =""" +  
str(10100) + """ and status = """ + 'status' +"""  order by orderNumber """,cnx)

TypeError:必须为str，而不是int

TypeError: must be str, not int

尽管我将字符串转换为字符串有任何想法，但还是遇到了以上错误?

getting above error although i converted to strings any idea?

有没有其他的wy来传递参数?

is there any alternative wy to pass the parameters?

推荐答案

使用参数化的sql，方法是通过小鲍比表，未经参数化的sql可能会使您陷入困境)

Use parametrized sql by supplying the arguments via the params keyword argument. The proper quotation of arguments will be done for you by the database adapter and the code will be less vulnerable to SQL injection attacks. (See Little Bobby Tables for an example of the kind of trouble improperly quoted, non-parametrized sql can get you into.)

order = 10100

status = 'Shipped'

sql = """SELECT  * from orders where orderNumber = ?
         and status = ? order by orderNumber"""
df1 = pd.read_sql_query(sql, cnx, params=[order, status])

sql中的?是参数标记.它们被替换为params中正确引用的值.请注意，正确的参数标记取决于您使用的数据库适配器.例如，MySQLdb和psycopg2使用%s，而sqlite3和oursql使用?.

The ?s in sql are parameter markers. They get replaced with properly quoted values from params. Note that the proper parameter marker depends on the database adapter you are using. For example, MySQLdb and psycopg2 uses %s, while sqlite3, and oursql uses ?.

这篇关于如何在Pandas SQL查询中动态传递变量值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！