Insert list of dictionaries and variable into table

Question

lst = [{'Fruit':'Apple','HadToday':2},{'Fruit':'Banana','HadToday':8}]

I have a long list of dictionaries of the form above.
I have two fixed variables.

person = 'Sam'
date = datetime.datetime.now()

I wish to insert this information into a mysql table.

How I do it currently

for item in lst:
    item['Person'] = person
    item['Date'] = date

cursor.executemany("""
    INSERT INTO myTable (Person,Date,Fruit,HadToday)
    VALUES (%(Person)s, %(Date)s, %(Fruit)s, %(HadToday)s)""", lst)

conn.commit()

Is their a way to do it, that bypasses the loop as the person and date variables are constant. I have tried

lst = [{'Fruit':'Apple','HadToday':2},{'Fruit':'Banana','HadToday':8}]
cursor.executemany("""
    INSERT INTO myTable (Person,Date,Fruit,HadToday)
    VALUES (%s, %s, %(Fruit)s, %(HadToday)s)""", (person,date,lst))

conn.commit()

TypeError: not enough arguments for format string

When an INSERT statement is detected, executemany creates a multi-row INSERT statement and maps each sublist to a VALUE term. This requires one value per inserted column. You could simply update the lists or implement your own formatting function to handle your needs similar to https://github.com/PyMySQL/PyMySQL/blob/master/pymysql/cursors.py#L189 — jspcal, May 17 '18 at 06:11

score 0 · Answer 1 · edited May 17 '18 at 17:55

Your problem here is, that it tries to apply all of lst into %(Fruit)s and nothing is left for %(HadToday)s).

You should not fix it by hardcoding the fixed values into the statement as you get into troubles if you have a name like "Tim O'Molligan" - its better to let the db handle the correct formatting.

Not mysql, but you get the gist: http://initd.org/psycopg/docs/usage.html#the-problem-with-the-query-parameters - learned this myself just a week ago ;o)

The probably cleanest way would be to use

cursor.execute("SET @myname = %s", (person,))
cursor.execute("SET @mydate = %s", (datetime.datetime.now(),))

and use

cursor.executemany("""
    INSERT INTO myTable (Person,Date,Fruit,HadToday)
    VALUES (@myname, @mydate, %(Fruit)s, %(HadToday)s)""", lst)

I am not 100% about the syntax, but I hope you get the idea. Comment/edit the answer if I have a misspell in it.

Insert list of dictionaries and variable into table

1 Answers1