mysql insert with tuple

Hans Mulder hansmu at xs4all.nl
Wed Nov 21 20:49:13 CET 2012


On 21/11/12 18:19:15, Christian wrote:
> Hi ,
> 
> my purpose is a generic insert via  tuple , because the number of fields and can differ. But  I'm stucking .
> 
> ilist=['hello',None,7,None,None]
> 
> #This version works, but all varchar fields are in extra '' enclosed.
> con.execute(""" INSERT INTO {} VALUES %r; """.format(table) , (tuple(ilist),))
> 
> #This produce (1054, "Unknown column 'None' in 'field list'"),
> #but without None values it works.
> con.execute(""" INSERT INTO {} VALUES %r; """.format(table) % (tuple(ilist),))

How about:

con.execute("""INSERT INTO {} VALUES ({})"""
    .format(table, ",".join("%s" for _ in ilist)), ilist)

Or perhaps break it down into smaller steps:

bind_variables = ",".join("%s" for _ in ilist))
query = "INSERT INTO {} VALUES ({})".format(table, bind_variables)
con.execute(query, ilist)


Hope this helps,

-- HansM


More information about the Python-list mailing list