时间戳

首页 > 解决方案 > mySQL 多行动态插入 Python

python - mySQL 多行动态插入 Python

问题描述

我的目标是在表中添加多行数据，但每次都会出错，我尝试了代码的多种变体，很抱歉没有早点问这个问题，我不知道怎么做，我不使用 Stack溢出。

更新的代码在最后。

错误：

Traceback (most recent call last):
  File "G:/Python/sqlconnector4.py", line 25, in <module>
    entry_table(number)
  File "G:/Python/sqlconnector4.py", line 17, in entry_table
    c.execute("INSERT INTO T1 (NAME , ADDRESS ) VALUES (thelist.split("'',''"))")
  File "C:\Users\tushar\AppData\Local\Programs\Python\Python36-32\lib\site-packages\mysql\connector\cursor.py", line 559, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Users\tushar\AppData\Local\Programs\Python\Python36-32\lib\site-packages\mysql\connector\connection.py", line 494, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Users\tushar\AppData\Local\Programs\Python\Python36-32\lib\site-packages\mysql\connector\connection.py", line 396, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): 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 '' at line 1

错误 

import mysql.connector

my=mysql.connector.connect(host='localhost' , user='root' , password='rootroot' , database='tushar')
c=my.cursor()

def create_table():
    c.execute("CREATE TABLE IF NOT EXISTS T1 (ID INT AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(30) NOT NULL , ADDRESS VARCHAR(50) NOT NULL)")
    my.commit()

def entry_table(n):
    thelist = []  
    for x in range(0,n) :
        user_name = input("ENTER NAME NUMBER {}: ".format(x+1))
        user_state = input("ENTER STATE {} LIVES IN : ".format(user_name))
        thelist.append(user_name)
        thelist.append(user_state)
        c.execute("INSERT INTO T1 (NAME , ADDRESS ) VALUES (thelist.split("'',''"))")
        my.commit()
    for x in range(0,n) :
        c.execute("SELECT * FROM T1")

number = int(input("Enter The Number of Rows You want to Fill : "))

create_table()
entry_table(number)

导入 mysql.connector

my=mysql.connector.connect(host='localhost', user='root', password='rootroot', database='tushar') c=my.cursor()

定义创建表（）：

c.execute("CREATE TABLE IF NOT EXISTS T1 (ID INT AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(30) NOT NULL , ADDRESS VARCHAR(50) NOT NULL)")
my.commit()

def entry_table(n):

for x in range(0,n) :
    user_name = input("ENTER NAME NUMBER {}: ".format(x+1))
    user_state = input("ENTER STATE {} LIVES IN : ".format(user_name))
    c.execute("INSERT INTO T1 (NAME , ADDRESS ) VALUES ( ? , ?)", user_name , user_state )
    my.commit()

c.execute("SELECT * FROM T1")
myresult = c.fetchall()

for x in myresult:
      print(x)

number = int(input("输入要填充的行数：")) create_table() entry_table(number) c.close() my.close()

标签： pythonmysqlpython-idle

解决方案

该错误是因为您将 python 语法作为 sql 语句的一部分传递，并且命令没有意义。许多修复，最简单的一个是您不需要列表

def entry_table(n):
    for x in range(0,n) :
        user_name = input("ENTER NAME NUMBER {}: ".format(x+1))
        user_state = input("ENTER STATE {} LIVES IN : ".format(user_name))
        data = (user_name, user_state)
        c.execute("INSERT INTO T1 (NAME , ADDRESS ) VALUES (%s, %s);", data)
        my.commit()
    c.execute("SELECT * FROM T1")
    print(c.fetchall())

其他一些超出范围但相关的事情：

  1. 事务可以在循环之后提交，因为我们没有访问它之前的数据。
  2. 游标永远不会关闭。
  3. 连接永远不会关闭。

编辑：替换?,?为%s, %s每个 API 文档

推荐阅读