首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >问答首页 >使用Python插入多个MySQL记录。错误:“无法将Python 'tuple‘转换为MySQL类型”

使用Python插入多个MySQL记录。错误:“无法将Python 'tuple‘转换为MySQL类型”
EN

Stack Overflow用户
提问于 2019-01-22 03:57:46
回答 4查看 13.5K关注 0票数 1

使用Python插入多个元组记录错误:无法将Python‘MySQL’转换为MySQL类型

错误码:

代码语言:javascript
运行
复制
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代码:

代码语言:javascript
运行
复制
#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()
EN

回答 4

Stack Overflow用户

发布于 2019-01-22 04:11:30

啊哈,我用错了Python术语。

在使用tuple时,我应该使用executemany

代码语言:javascript
运行
复制
my_cursor.executemany(mike_placeholders,records_list)
票数 5
EN

Stack Overflow用户

发布于 2019-01-22 04:04:07

你不能把列表传递给my_cursor.execute(),你需要遍历列表:

代码语言:javascript
运行
复制
for values in records_list:
    my_cursor.execute(mike_placeholders, values)

或者,您可以多次重复(%s, %s, %s),并通过展平元组列表在单个查询中完成所有操作。

代码语言:javascript
运行
复制
mike_placeholders="INSERT INTO users (name,email,age) VALUES " + ", ".join(["(%s, %s, %s)"] * len(records_list))
my_cursor.execute(mike_placeholders, sum(records_list))
票数 2
EN

Stack Overflow用户

发布于 2021-10-29 06:49:21

代码语言:javascript
运行
复制
use my_cursor.executemany(mike_placeholders,records_list)

如果您有多个元素,这些元素保存在列表元组中,

代码语言:javascript
运行
复制
cursor.executemany(query,list) or cursor.executemany(query,tuple)
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/54296971

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档