无法从\u mysql python packag执行sql插入

Question

我正在尝试使用python的Mysql包向Mysql表插入一个查询。我写的问题是：

que = '''insert into all_prices(date,market,commodity,variety,arrival,Minimum_Price,Maximum_Price,Modal_Price,source,state)values('''+str(a)+''','''+market_name+''','''+commodity+''','''+variety+''','''+arrival+''','''+Min_Price+''','''+Max_Price+''','''+Modal_Price+''','''+'''delagrimarket,delhi)'''

db.query(que)

que的值之一是：

que = insert into all_prices(date,market,commodity,variety,arrival,Minimum_Price,Maximum_Price,Modal_Price,source,state)values(1,azadpur,Apple,Royal Delicious,899,6000,11000,8200,delagrimarket,delhi)

但是我得到了一些错误，比如：

_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Delicious,899,6000,11000,8200,delagrimarket,delhi)' at line 1")

有人能帮忙解决这个问题吗。你知道吗

Answer 1

你可以试试这个：

que = '''
insert into
all_prices
(date, market, commodity, variety, arrival, Minimum_Price, Maximum_Price, Modal_Price, source, state)
values('{0}', '{1}', {2}, '{3}', '{4}', '{5}', '{6}', '{7}', 'delagrimarket', 'delhi')'''
.format(str(a), market_name, commodity, variety, arrival, Min_Price, Max_Price, Modal_Price)
db.query(que)

更清洁的方法是：

vals = (str(a), market_name, commodity, variety, arrival, Min_Price, Max_Price, Modal_Price)
que = '''
insert into
all_prices
(date, market, commodity, variety, arrival, Minimum_Price, Maximum_Price, Modal_Price, source, state)
values('{0}', '{1}', {2}, '{3}', '{4}', '{5}', '{6}', '{7}', 'delagrimarket', 'delhi')
'''
db.query(que.format(*vals))

如果您想用不同的值多次执行同一查询，那么第二种方法非常有用。你知道吗

查看此以了解有关string.format格式https://docs.python.org/2/library/string.html#string.Formatter.format

Answer 2

最好使用类似的字符串格式

que = '''insert into all_prices(date,market,commodity,variety,arrival,Minimum_Price,Maximum_Price,Modal_Price,source,state)values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'''
vals = (str(a), market_name, commodity, variety, arrival, Min_Price, Max_Price, Modal_Price, "delagrimarket", "delhi")
con.query(que % values)

有关字符串格式的更多信息，请查看python docs。你知道吗