MySQLdb insert into database using lists for field names and values python

Question

I have these two lists:

list1=['a','b','c']
list2=['1','2','3']

I am trying to insert these into a database with field names like so:

a | b | c | d | e

I am currently trying putting these lists as strings and then simply adding in the execute, e.g. cur.execute(insert,(strList1,strList2)) where strList1 and strList2 are just strings of list1 and list2 formed using:

strList1=''
for thing in list1:
    strList1+=thing+','
strList1=strList1[:-1]

My current SQL statement is:

insert="""insert into tbl_name(%s) values(%s)"""
cur.execute(insert,(strList1,strList2))

I also have a follow up question: how could I ensure that say column a needed to be a primary key that on a duplicate entry it would update the other fields if they were blank?

Parameter replacement in a database statement has to be done with the real parameters (So you can not use a parameter consisting out of more parameters). You will have to assemble the complete insert string first in this case before you can insert, or you have to create a smart loop for the values(%s) part in which you then have all 5 of your values. — Norbert
– Norbert, Commented Sep 8, 2015 at 23:34
I am trying to add a row with 3 out of the 5 fields with data you can assume the default to be null, could you give an example on what you mean as 'assemble the complete insert string first' in an answer? The values could contain odd characters like -.()!? Etc etc but not foreign characters — Daniel Prinsloo
– Daniel Prinsloo, Commented Sep 8, 2015 at 23:37

Foobar · Accepted Answer · 2015-09-09 15:51:00Z

2

Do not use %s in queries as this is a security risk. This is due to %s simply inserting the value into the string, meaning it can be a whole separate query all together. Instead use "?" where you want the value to be, and add a second argument to execute in the form of a tuple like so

curs.execute("SELECT foo FROM bar WHERE foobar = ?",(some_value,))

Or in a slightly longer example curs.execute("UPDATE foo SET bar = ? WHERE foobar = ?",(first_value,second_value)) Edit:

Hopefully i understood what you want correctly this time, sadly you cannot use "?" for tables so you are stuck with %s. I made a quick little test script.

import sqlite3

list1=['foo','bar','foobar'] #List of tables
list2=['First_value','second_value','Third_value'] #List of values

db_conn = sqlite3.connect("test.db") #I used sqlite to test it quickly
db_curs = db_conn.cursor()

for table in list1: #Create all the tables in the db
    query = "CREATE TABLE IF NOT EXISTS %s(foo text, bar text,foobar text)" % table
    db_curs.execute(query)
db_conn.commit()

for table in list1: #Insert all the values into all the tables
    query = "INSERT INTO %s VALUES (?,?,?)" % table
    db_curs.execute(query,tuple(list2))
db_conn.commit()

for table in list1: #Print all the values out to see if it worked
    db_curs.execute("SELECT * FROM %s" % table)
    fetchall = db_curs.fetchall()
    for entry in fetchall:
        print entry[0], entry[1],entry[2]

edited Sep 9, 2015 at 15:51

answered Sep 9, 2015 at 1:16

Foobar

761 silver badge6 bronze badges

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

4 Comments

Daniel Prinsloo Over a year ago

But I am trying to insert and I am trying to use a list for both the column names and the values

Foobar Over a year ago

Im somewhat new to stackexchange, so i don't know if you get notified for edits. Anyway i edited my original answer and added what i think you want

Daniel Prinsloo Over a year ago

Sorry if I've explained myself poorly but what I meant is that the column names are in a list that changes, the table name does not change

Foobar Over a year ago

I see, maybe this will help.` query = "INSERT INTO foobars (%s,%s,%s) VALUES (?,?,?)" % tuple(list1) db_curs.execute(query,values)` Just note that %s is a security risk so be carefully with that. But it seems ? cannot be used in column names either, please note that "?" might be replaced by something else in diffrent databases so look up the documentation oh whichever you are using.

Shawn K · Accepted Answer · 2015-09-08 23:41:58Z

0

One thing you could do on those lists to make things easier...

     list1=['a','b','c']
     print ",".join(list1)
     #a,b,c

Your insert looks good. Seems like a batch insert would be the only other option.

answered Sep 8, 2015 at 23:41

Shawn K

7785 silver badges13 bronze badges

Comments

Norbert · Accepted Answer · 2015-09-09 14:30:11Z

0

This is the way prepared statements work (simplified): * The statement is send to the database with a list of parameters; * The statement is retrieved from the statement cache or if not present, prepared and added to the statement cache; * Parameters are applied; * Statement is executed.

Statements have to be complete except that the parameters are replaced by %s (or ? or :parm depending on the language used). Parameters are your final numerical/string/date/etc values only. So labels or other parts can not be replaced.

In your case that means:

insert="""insert into tbl_name(%s) values(%s)"""

Should become something like:

insert="""insert into tbl_name(a,b,c) values(%s,%s,%s)"""

answered Sep 9, 2015 at 14:30

Norbert

6,1043 gold badges19 silver badges40 bronze badges

Comments

Pepi · Accepted Answer · 2015-09-09 15:28:40Z

0

To use parameters, you must provide a "%s" (or %d, whatever) for each item. You can use two lists/tuples as follows:

insert="""insert into tbl_name (%s,%s,%s) values (%s, %s, %s);"""
strList1=('a','b','c')
strList2=(1,2,3)
curs.execute(insert % (strList1 + strList2))

*I'm using python3, (strList1,StrList2) doesn't work for me, but you might have slight differences.

answered Sep 9, 2015 at 15:28

Pepi

1712 silver badges8 bronze badges

Collectives™ on Stack Overflow

MySQLdb insert into database using lists for field names and values python

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related