How insert a list as a column in MySQL with Python?

Question

So basically I have this list :

L1 = ['hel-lo', 'world123', 'bye', 'python']

I want to insert this list in my MySQL table, as follow :

+-------------------------+
|          words          |
+-------------------------+
| hel-lo                  |
| world123                |
| bye                     |
| python                  |
+-------------------------+

In reality, my list is composed of about 1000 elements. I tried many things which I found on StackOverflow, but nothing is working. (It seems that the elements are trying to be insert with this format "['string']").

Last solution I tried :

values = [list([item]) for item in L1]
cursor.executemany(u"INSERT INTO `table`(`data`) VALUES %s", values)

Which returns this error :

 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 ''hel-lo')' at line 1

Any advices are welcomed !

dev.mysql.com/doc/connector-python/en/…

mad_
– mad_

2018-08-28 12:34:29 +00:00
Commented Aug 28, 2018 at 12:34 — mad_
– mad_, Commented Aug 28, 2018 at 12:34
I already saw this link, what I am messing ?

kuka ricin
– kuka ricin

2018-08-28 12:38:11 +00:00
Commented Aug 28, 2018 at 12:38 — kuka ricin
– kuka ricin, Commented Aug 28, 2018 at 12:38
Why you have in the table world123 and not world ?

dWinder
– dWinder

2018-08-28 12:38:44 +00:00
Commented Aug 28, 2018 at 12:38 — dWinder
– dWinder, Commented Aug 28, 2018 at 12:38
@DavidWinder my mistake sorry

kuka ricin
– kuka ricin

2018-08-28 12:39:55 +00:00
Commented Aug 28, 2018 at 12:39 — kuka ricin
– kuka ricin, Commented Aug 28, 2018 at 12:39
@DavidWinder Does it matter?

mad_
– mad_

2018-08-28 12:40:15 +00:00
Commented Aug 28, 2018 at 12:40 — mad_
– mad_, Commented Aug 28, 2018 at 12:40

Eugene Yarmash · Accepted Answer · 2018-08-28 13:07:28Z

7

You're missing parentheses around the value list in the query (should be VALUES (%s)). Also, list([item]) can be simplified to just [item]:

L1 = ['hel-lo', 'world', 'bye', 'python']
values = [[item] for item in L1]
cursor.executemany(u"INSERT INTO `instability`(`ap_name`) VALUES (%s)", values)

edited Aug 28, 2018 at 13:07

answered Aug 28, 2018 at 12:40

Eugene Yarmash

152k44 gold badges346 silver badges391 bronze badges

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

2 Comments

kuka ricin Over a year ago

Shame on me, I just noticed that I wrote " % " instead of "(%)". I have no error now but nothing is inserted is the DB

Eugene Yarmash Over a year ago

You may need to commit the transaction.

Collectives™ on Stack Overflow

How insert a list as a column in MySQL with Python?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related