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 column of list. database_list= ['elem1','elem2','elem3'...]

this is what in have done but no progress:

    cursor_three=conn.cursor()
    cursor_three.execute('INSERT INTO table (data) VALUES (database_list)') 
    cursor_three.commit()  

2 answers

  • answered 2018-01-14 09:05 NinjaElvis

    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)
    

  • answered 2018-01-14 09:05 Diman

    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 in zip(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])