使用Python插入多个元组记录错误:无法将Python‘MySQL’转换为MySQL类型
错误码:
Traceback (most recent call last):
File "C:\Users\POM\AppData\Local\Programs\Python\Python37-32\lib\site-packages\mysql\connector\conversion.py", line 181, in to_mysql
return getattr(self, "_{0}_to_mysql".format(type_name))(value)
AttributeError: 'MySQLConverter' object has no attribute '_tuple_to_mysql'
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "C:\Users\POM\AppData\Local\Programs\Python\Python37-32\lib\site-packages\mysql\connector\cursor.py", line 432, in _process_params
res = [to_mysql(i) for i in res]
File "C:\Users\POM\AppData\Local\Programs\Python\Python37-32\lib\site-packages\mysql\connector\cursor.py", line 432, in <listcomp>
res = [to_mysql(i) for i in res]
File "C:\Users\POM\AppData\Local\Programs\Python\Python37-32\lib\site-packages\mysql\connector\conversion.py", line 184, in to_mysql
"MySQL type".format(type_name))
TypeError: Python 'tuple' cannot be converted to a MySQL type
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "python_mysql_2.py", line 22, in <module>
my_cursor.execute(mike_placeholders,records_list)
File "C:\Users\POM\AppData\Local\Programs\Python\Python37-32\lib\site-packages\mysql\connector\cursor.py", line 557, in execute
psub = _ParamSubstitutor(self._process_params(params))
File "C:\Users\POM\AppData\Local\Programs\Python\Python37-32\lib\site-packages\mysql\connector\cursor.py", line 437, in _process_params
"Failed processing format-parameters; %s" % err)
mysql.connector.errors.ProgrammingError: Failed processing format-parameters; Python 'tuple' cannot be converted to a MySQL type
Python代码:
#import sql.connector
import mysql.connector
#Create connection, added db we created#
connection = mysql.connector.connect(
host='localhost',
user='root',
password='123',
database='testdb_1'
)
#Create cursor for the connection
my_cursor = connection.cursor()
#Create SQL statement with placeholders and put in variable
mike_placeholders="INSERT INTO users (name,email,age) VALUES (%s, %s, %s) "
#Create list (array) of records
records_list = [('Tim','Tim@tim.com',32), ('Mary','Mary@mary.com',40), ('Sam','Sam@sam.com',50), ('Fred','Fred@fred.com',22) ]
#Execute cursor, requires SQl statement variable, record variable
my_cursor.execute(mike_placeholders,records_list)
#Commit the connection to make the change on the database
connection.commit()
发布于 2019-01-22 04:11:30
啊哈,我用错了Python术语。
在使用tuple
时,我应该使用executemany
。
my_cursor.executemany(mike_placeholders,records_list)
发布于 2019-01-22 04:04:07
你不能把列表传递给my_cursor.execute()
,你需要遍历列表:
for values in records_list:
my_cursor.execute(mike_placeholders, values)
或者,您可以多次重复(%s, %s, %s)
,并通过展平元组列表在单个查询中完成所有操作。
mike_placeholders="INSERT INTO users (name,email,age) VALUES " + ", ".join(["(%s, %s, %s)"] * len(records_list))
my_cursor.execute(mike_placeholders, sum(records_list))
发布于 2021-10-29 06:49:21
use my_cursor.executemany(mike_placeholders,records_list)
如果您有多个元素,这些元素保存在列表或元组中,
cursor.executemany(query,list) or cursor.executemany(query,tuple)
https://stackoverflow.com/questions/54296971
复制相似问题