python - mysql通过python插入表

Question

我只想将表列表插入到mysql数据库表中。代码如下:

import mysql.connector 
# Open database connection
a = [('apq3'), ('aquaporin')]
b = ["skin"]
c = ["down-regulated", "down regulation", "down regulate"]

def input_mysql(a1, b1, c1):
    db = mysql.connector.connect(user='root', password='xxxx',
                              host='localhost',
                              database='crawling')
    #sql = 'INSERT INTO crawling_result(search_content, content) VALUES(%s)' 
    sql_target_a = 'INSERT INTO a (term) VALUES(%s)'
    sql_target_b = 'INSERT INTO b (term) VALUES(%s)'
    sql_target_c = 'INSERT INTO c (term) VALUES(%s)'
    cursor=db.cursor()
    cursor.execute(sql_target_a, a1)
    cursor.execute(sql_target_b, b1)
    cursor.execute(sql_target_c, c1)
    db.commit()
    db.close()

a_content = a
b_content = b
c_content = c
input_mysql (a_content, b_content, c_content)

运行后一直显示“mysql.connector.errors.ProgrammingError: Not allparameters wereused in the SQL statements”。有人可以帮助我吗？

Answer 1

您的某些列表 - a 和 c - 具有多个值，但您在语句中仅使用一个值(%s) 。这被认为是一个错误，因为不清楚额外的参数是否是有意的。

如果您打算在源数组中的每个元素插入一行，请使用 executemany并传入一系列参数:

a = [('apq3',), ('aquaporin',)]
b = [('skin',)]
c = [('down-regulated',), ('down regulation',) ('down regulate',)]

[...]

# Insert two rows (one for each parameter list in `a`)
cursor.executemany('INSERT INTO a (term) VALUES(%s)', a)