CODEWITHSUNDEEP
pythonmysql
user3855343
user3855343

Reputation: 43

dynamic mysql insert statement in python

I need to update columns values in csv to mysql database and the csv values are dynamic for one file it may be 10 columns and for other it may be 5 columns.

my understanding in python we need to use list also this question raised earlier is similar to my requirement but here the values are static so it can be predefined where's in my case being dynamic need to a solution to have %%s under VALUES to be multiplied according to my column values dynamically.

MySQL Dynamic Query Statement in Python

   ds_list=['name','id','class','chapter','MTH','YEAR']

   vl_list=['xxxxx','978000048','x-grade','Science',mar,2017]

   sql = 'INSERT INTO ann1 (%s) VALUES (%%s, %%s, %%s, %%s, %%s, %%s)' %    ','.join(ds_list)

   cur.execute(sql, vl_list)

   conn.commit()

Upvotes: 0

Views: 6113

Answers (1)

An Se
An Se

Reputation: 968

So, if you have two lists, one with headers and the other with values – you can create yourself dynamic INSERT query.

query_placeholders = ', '.join(['%s'] * len(vl_list))
query_columns = ', '.join(ds_list)

insert_query = ''' INSERT INTO table (%s) VALUES (%s) ''' %(query_columns, query_placeholders)

and then execute & commit your query by passing list with values in query.

cursor.execute(insert_query, vl_list)

Upvotes: 4

Related Questions