How to insert a list containing values seperated by comma into MySQL database using python

Question

I have to insert a list having integer values into a column of MySQL database table using Python. First I tried

cur.execute("insert into time_Interval (name, time_interval_list) values (%s, %s)",\
        #   (user_name, interval_list))

Where "interval_list" is the list variable having values that I want to insert into "time_interval_list" column of "time_Interval" table. Code is working but does not insert the values. Then I tried this solution changing the code as

var_string = ','.join('?' * len(interval_list))
cur.execute("insert into time_Interval (name, time_interval_list) values (%s, %s)",\
            (user_name, var_string))

Again, code was working fine but inserts a list of question mark. I am unable to find the solution.

How to do this correctly. Any suggestion will be appreciated

score 0 · Answer 1 · answered Jan 30 '18 at 13:51

0

Try changing

var_string = ','.join('?' * len(interval_list))

to

var_string = ','.join(interval_list)

answered Jan 30 '18 at 13:51

Rakesh

81,458
17
76
113

Thanks for your response, actually I have to typecast it as a string list – mfazil Jan 30 '18 at 14:35

score 0 · Answer 2 · answered Jan 30 '18 at 14:42

The following code helped me bulk insert data into a database table using Python.

You can first create a tuple of tuples from the data you want to insert as follows

var_string = ','.join(cur.mogrify("(%s,%s)", x).decode('utf-8') for x in interval_list)

The interval_list would include both name and time_interval_list values whehn creating the tuple of tuples. Then run exceute command giving the tuple of tuples as input to the values.

cur.execute("INSERT INTO time_Interval (name, time_interval_list) VALUES " + var_string)

This command bulk inserts data into a database table very quickly.

How to insert a list containing values seperated by comma into MySQL database using python

2 Answers2