查询中的PyMySQL变量 [英] PyMySQL variables in queries

问题描述

我想在python代码和SQL数据库之间建立连接.我已经阅读了几种方法，但是没有得到结果.

I would like to have a connection between my python code and a SQL database. I have read several ways to do it , but I am failing to get the results.

conn = pymysql.connect(user=X,passwd=X,host=X,port=X,database=X, charset='utf8', autocommit=True)
curs = conn.cursor()

try:
    curs.execute('SELECT id,sing_name,bir_yr FROM singers_list WHERE bir_yr = ? ',year)
    data = curs.fetchall()       
    for i in data:
        yield " Data: " + str(i) + "<br/>"
except:
    yield " Failed to get data from base<br/>"

其中 year 是int python变量.我得到了正确的结果:

Where year is an int python variable. I am getting the proper results with:

curs.execute('SELECT id,sing_name,bir_yr FROM singers_list)

这意味着我已成功连接到数据库.如何在查询中包含变量? (不仅是整数，还包括字符串或任何类型的字符串)

Which means I am connecting successfully to the database . How can I include variables in queries ? (not only integers , but strings too or any type)

推荐答案

您必须在可迭代对象(通常是元组)中传递参数:

You have to pass the parameters inside an iterable - commonly a tuple:

query = 'SELECT id,sing_name,bir_yr FROM singers_list WHERE bir_yr = %s'
curs.execute(query, (year, ))

请注意，我还用%s替换了?占位符.

Note that I've also replaced the ? placeholder with %s.

还请注意，MySQL驱动程序会自动处理Python和MySQL之间的类型转换，如有必要，会添加引号并转义参数以使您免受SQL注入攻击.

Also note that the MySQL driver would automatically handle the type conversion between Python and MySQL, would put quotes if necessary and escape the parameters to keep you safe from SQL injection attacks.

这篇关于查询中的PyMySQL变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！