Iterative insert into MySQL table from a python list

Question

I tried to insert the items (using the MySQLdb module) in the list below into mysql table only to find out that the letter 'x' was inserted instead of the names in the list.

name_list=['james', 'john', 'michael']
for x in name_list:
    cur.execute("INSERT INTO box(names) VALUES('x')")
db.commit()

Any suggestions? Thanks.

Here's a method that worked, but only for int datatype:

for x in xrange(1,1000,2):
    cursor.execute('INSERT INTO Persons (PersonID) VALUES(' + str(x) + ');')

As a side note, with many Python database interfaces, you can use executemany here, which is both simpler and potentially faster. You still need to use a placeholder, as in AER's answer, but then you can replace the whole for loop with cur.executemany("INSERT INTO box(names) VALUES(%s)", [(name,) for name in name_list]). (Notice that I had to create a list of tuples of 1 string each, not just a list of lists, because I think MySQLdb expects each row to be a tuple.) — abarnert
– abarnert, Commented Feb 19, 2014 at 0:18
HI @abarnert, thanks for your effort in trying to solve this problem. I'm still getting the following error message:OperationalError: (1054, "Unknown column 'names' in 'field list'") after using executmany. — Tiger1
– Tiger1, Commented Feb 19, 2014 at 0:29
Are you sure the box table has a names column in the first place? More generally, when you run your original code, does it actually successfully insert three rows with 'x' values? Because if there's a typo in what you posted here, AER will obviously have the same typo in his code… — abarnert
– abarnert, Commented Feb 19, 2014 at 0:40
yea, my original code inserted over 50 x's. When I run the code on individual names, the get inserted, with no problem. But when trying to insert many in iteration, there the problem comes in. — Tiger1
– Tiger1, Commented Feb 19, 2014 at 0:55
I don't know what you mean by "insert many in iteration". You mean "insert many in a single executemany call", or "insert many by iterating around execute"? Can you post the exact code that you're using for the working and non-working cases (either editing your question, creating a new question with links between them, or posting somewhere like pastebin.com, whichever seems most appropriate)? — abarnert
– abarnert, Commented Feb 19, 2014 at 0:58

abarnert · Accepted Answer · 2014-02-19 00:17:03Z

3

If this is the case you have inserted the string x. The variable x does not parse into the SQL command. However most database libraries' execute statements have a neat trick when inserting parameters as follows:

MySQLdb

cur.execute("INSERT INTO box(names) VALUES(%s)", (x,))

*Note that the x is as a (x,) so it is considered a tuple when it is placed into the SQL command.

pyodbc

cur.execute("INSERT INTO box(names) VALUES(?)",x)

As you can see, the details are slightly different for different libraries. See the module's paramstyle for which format it uses for the placeholders within the string, and the module's docs for what it does when given a single argument for the parameters instead of a tuple.

This will take the x and put it in the values of the command. Even better it formats it according to the destination table!

edited Feb 19, 2014 at 0:17

abarnert

368k54 gold badges626 silver badges691 bronze badges

answered Feb 18, 2014 at 23:59

AER

1,5411 gold badge19 silver badges39 bronze badges

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

7 Comments

Tiger1 Over a year ago

Hi AER, thanks for your response. I got the following error when I tried your solution:TypeError: not all arguments converted during string formatting.

AER Over a year ago

Are you using MySQLdb or pyodbc? Or others (see below)

abarnert Over a year ago

There are half a dozen MySQL interfaces for Python; they all support this basic idea (as do almost all other database interfaces for Python), but most of them use %s instead of ? for the placeholders.

abarnert Over a year ago

+1, but the real benefit of letting the database format things instead of doing it yourself isn't just that it takes care of the types for you, it's that you don't have to handle complicated (and database-specific) quoting rules, leaving yourself open to SQL injection if you get it wrong. (And, as a side benefit, with some databases it can even help performance, because they can compile and cache the statement with placeholders instead of having to compile each statement independently.)

abarnert Over a year ago

You should be able to find what parameter placeholder format your database library uses by printing out the module's paramstyle. But I'm pretty sure MySQLdb uses format paramstyle, meaning you want %s here.

|

Collectives™ on Stack Overflow

Iterative insert into MySQL table from a python list

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related