Python: Can't insert list values into a mysql column

Question

I have an int list list = [1, 2, 3]

And a mysql table with an empty second column:

key value
a 
b
c

I want to populate the value column with values from my list so as to make the table look like this:

key value
a   1
b   2
c   3

But I am always getting a TypeError: 'int' object is not iterable

My python code is:

conn = MySQLdb.connect(host="localhost", user="root", passwd="root")
cursor = conn.cursor()
list = [1, 2, 3]
cursor.executemany("INSERT INTO my_table (value) VALUES %d", list)

How am I to solve the issue and get the empty column populated? Thanks!

P.S.: this is just a sample. In reality, the list and the table are much bigger.

Daniel Roseman · Accepted Answer · 2018-01-29 16:09:21Z

3

You need a list of lists or tuples.

my_list = [[1], [2], [3]]

(Also, don't overwrite the built-in list() type with your own variable.)

answered Jan 29, 2018 at 16:09

Daniel Roseman

602k68 gold badges910 silver badges923 bronze badges

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

3 Comments

Dan Over a year ago

Thanks! But now I get _mysql_exceptions.ProgrammingError: %d format: a number is required, not str. Changed %d to %s which triggered 1064, "You have an error in your SQL syntax;. Tried the solution from this stackoverflow ticket, but no like either. How am I to solve this?

Daniel Roseman Over a year ago

The placeholder should always be %s.

Dan Over a year ago

I have changed the last line to cursor.executemany("INSERT INTO my_table (value) VALUES %s", list), but got another error: 1064, "You have an error in your SQL syntax;. Could you please give me a hint on how to fix that?

Collectives™ on Stack Overflow

Python: Can't insert list values into a mysql column

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related