python - 无法从mysql python包执行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 的更多信息