mysql insert table by python

Question

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?

jspcal · Accepted Answer · 2018-07-24 07:48:56Z

1

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)

edited Jul 24, 2018 at 7:48

answered Jul 24, 2018 at 7:40

jspcal

52k7 gold badges76 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Thomas.Q Over a year ago

Thanks. So how could I insert the list with more than one value into the mysql.table? I only want to insert into the "term" column.

shmee Over a year ago

@Thomas.Q look at executemany perhaps? You would have to re-format your inputs into lists of tuples, though

Thomas.Q Over a year ago

Yes. JUST need change it as cursor.executemany(sql_target_a, [(x,) for x in a1]). And it works! Thx!

Collectives™ on Stack Overflow

mysql insert table by python

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related