Inserting List In Specific Column Using Python

i have a database where there are two column sess_id and data. Now i want to insert a list from py to db how should i do this. suppose this is the list to be inserted in data colum

Solution 1:

Depends on what you want to do. If you want to insert a list of values into one column you can loop through the list and insert row by row. You can also use executemany instead of execute.

If you're directly inputting the list then use excutemany, otherwise if you're looping through the list use execute.

cursor.executemany("""
              INSERT INTO table
              (data)
              VALUES (?)
              """, data_list)

OR

for i in data_list:
    cursor.execute("INSERT INTO table (data) VALUES (?)", i)

Solution 2:

You should iterate list like this:

cursor_three=conn.cursor()
for elem in database_list:
    cursor_three.execute('INSERT INTO table (data) VALUES (%s)' % elem) 
cursor_three.commit()  

After that you will have sess_id column empty unless you declared it autoincrement row. If you have two sorted arrays for example

database_id_list = [212, 444, 532...]
database_data_list= ['elem212','elem444','elem532'...]

You should use zip() function to iterate them simulteniously, like this:

for elem inzip(database_id_list, database_data_list):
    cursor_three.execute('INSERT INTO table (sess_id, data) VALUES (%s, %s)' % elem)  # elem is tuple here

Notice that this code is using string fromat function for simplness (you didn't noticed which database you're using) and it is not protected from SQL injections. You better use your sql deriver's formatter, for example for psycopg2:

cursor_three.execute('INSERT INTO table (data) VALUES (%s)', [elem])