dynamic mysql insert statement in python

Question

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()

An Se · Accepted Answer · 2017-09-30 03:20:27Z

4

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)

edited Sep 30, 2017 at 3:20

answered Sep 30, 2017 at 3:15

An Se

98814 silver badges27 bronze badges

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

4 Comments

user3855343 Over a year ago

Hi An se thanks for your answer when I execute i receive ProgrammingError: (1064, "You have an error in your SQL syntax;

An Se Over a year ago

can you print the resulting 'insert_query' and 'vl_list' and copy paste it here?

user3855343 Over a year ago

sorry , made mistake while specifying the table name it works. is there a similar way to do in update statement also instead of insert statement so that where clause can be used in query.

user3855343 Over a year ago

Hi An Se, actually I copy paste your code and made it working, if you can explain in short what code actually does, it would be helpful to write code myself for update statement.

Collectives™ on Stack Overflow

dynamic mysql insert statement in python

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related