CODEWITHSUNDEEP
pythonmysqlsql-insert
Thomas.Q
Thomas.Q

Reputation: 477

mysql insert table by python

I just want to insert the table list into the mysql database table. Here's the code:

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)

After running, it keeps showed that "mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement". Can some one help me?

Upvotes: 0

Views: 122

Answers (1)

jspcal
jspcal

Reputation: 51934

Some of your lists - a and c - have more than one value, but you're only using one value in your statements (%s). This is considered an error since it's unclear if the extra parameters were intentional or not.

If you intend to insert one row per element in the source arrays, use executemany and pass in a sequence of parameters:

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)

Upvotes: 1

Related Questions