[已解决]python批量插入数据里报错。。

叶小贤

import pymysql

conn = pymysql.connect(
    host = 'localhost',
    user = 'root',
    password = '123123',
    database = 'pysqltest'
)

cursor = conn.cursor()
for each in range(20):
    sql = "insert into test_table (id,name,code) values({},{},{}) ".format(5+(each+1),'张'+str(each+1),36+(each+1))
    cursor.execute(sql)

conn.commit()
cursor.close()
conn.close()

---------------------------------------

Traceback (most recent call last):
  File "D:/pythontest/venv/InsertSQL.py", line 13, in <module>
    cursor.execute(sql)
  File "D:\pythontest\venv\lib\site-packages\pymysql\cursors.py", line 170, in execute
    result = self._query(query)
  File "D:\pythontest\venv\lib\site-packages\pymysql\cursors.py", line 328, in _query
    conn.query(q)
  File "D:\pythontest\venv\lib\site-packages\pymysql\connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "D:\pythontest\venv\lib\site-packages\pymysql\connections.py", line 732, in _read_query_result
    result.read()
  File "D:\pythontest\venv\lib\site-packages\pymysql\connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "D:\pythontest\venv\lib\site-packages\pymysql\connections.py", line 684, in _read_packet
    packet.check_error()
  File "D:\pythontest\venv\lib\site-packages\pymysql\protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "D:\pythontest\venv\lib\site-packages\pymysql\err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.InternalError: (1054, "Unknown column '张1' in 'field list'")

最佳答案

月排行榜 / 总排行榜

yuweb

2019-9-12 14:14:39

'张'+str(x) 和你的变量结合起来相当于 '张1'
str.format()基本语法是通过 {} 和 : 来代替以前的 %
"{} {}".format("hello", "world")  # 设置指定位置
相当于'hello world'

可以试试
format(5+x,"\'张"+str(x)+"\'",x,0,36+x)

跳转到最佳答案楼层

叶小贤

发现了一个不一样的地方


当我这么写的时候 插入一条
sql = "insert into test_table (id,name,age,sex,code) values(6,'张1',1,0,37) "
cursor.execute(sql)

变量sql是这样的：
sql = (str) 'insert into test_table (id,name,age,sex,code) values(6,\'张1\',1,0,37) '


---------------------------------------------------------------
当我循环插入的时候
x = 1
for each in range(20):
    sql = "insert into test_table (id,name,age,sex,code) values({},{},{},{},{}) ".format(5+x,'张'+str(x),x,0,36+x)
    cursor.execute(sql)
    x += 1

变量sql是这样的：
sql = (str) 'insert into test_table (id,name,age,sex,code) values(6,'张1',1,0,37) '


name 少了2个反斜杠 这是为什么？

yuweb

'张'+str(x) 和你的变量结合起来相当于 '张1'
str.format()基本语法是通过 {} 和 : 来代替以前的 %
"{} {}".format("hello", "world")  # 设置指定位置
相当于'hello world'

可以试试
format(5+x,"\'张"+str(x)+"\'",x,0,36+x)

yuweb

import pymysql

conn = pymysql.connect(
    host = 'localhost',
    user = 'root',
    password = 'root',
    database = 'pysqltest'
)

cursor = conn.cursor()
for each in range(20):
    sql = "insert into test_table (id,name,age,sex,code) values({},{},{},{},{}) ".format(5+each,"\'张"+str(each)+"\'",each,0,36+each)
    cursor.execute(sql)

conn.commit()
cursor.close()
conn.close()

叶小贤

yuweb 发表于 2019-9-12 14:23
import pymysql

conn = pymysql.connect(

牛逼啊 这是什么原理啊 区别是什么

"\'张"+str(each)+"\'"
'张'+str(each+1)

yuweb

叶小贤 发表于 2019-9-12 14:25
牛逼啊 这是什么原理啊 区别是什么

"\'张"+str(each)+"\'"

简单的说
"\'张"+str(each)+"\'" 用print输出是
'张1'        (举例哈)
'张'+str(each+1) 用print输出是
张1
\'代表的是转义字符 这边你可以看下百度的解释https://baike.baidu.com/item/%E8 ... A6/86397?fr=aladdin