Insert nested arrays into sql from python

Question

I have a list contains many lists in python.

my_list = [['city', 'state'], ['tampa', 'florida'], ['miami','florida']]

The nested list at index 0 contains the column headers, and rest of the nested lists contain corresponding values. How would I insert this into sql server using pyodbc or slqalchemy? I have been using pandas pd.to_sql and want to make this a process in pure python. Any help would be greatly appreciated.

expected output table would look like:

city |state
-------------
tampa|florida
miami|florida

What database are you using?

jignatius
– jignatius

2020-03-04 20:46:16 +00:00
Commented Mar 4, 2020 at 20:46 — jignatius
– jignatius, Commented Mar 4, 2020 at 20:46
@jignatius sql server

codingInMyBasement
– codingInMyBasement

2020-03-04 20:47:56 +00:00
Commented Mar 4, 2020 at 20:47 — codingInMyBasement
– codingInMyBasement, Commented Mar 4, 2020 at 20:47

jignatius · Accepted Answer · 2020-03-05 18:42:43Z

1

Since the column names are coming from your list you have to build a query string to insert the values. Column names and table names can't be parameterised with placeholders (?).

import pyodbc 

conn = pyodbc.connect(my_connection_string)
cursor = conn.cursor()

my_list = [['city', 'state'], ['tampa', 'florida'], ['miami','florida']]

columns = ','.join(my_list[0]) #String of column names
values = ','.join(['?'] * len(my_list[0])) #Placeholders for values
query = "INSERT INTO mytable({0}) VALUES ({1})".format(columns, values)

#Loop through rest of list, inserting data
for l in my_list[1:]:
    cursor.execute(query, l)
conn.commit() #save changes

Update:

If you have a large number of records to insert you can do that in one go using executemany. Change the code like this:

columns = ','.join(my_list[0]) #String of column names
values = ','.join(['?'] * len(my_list[0])) #Placeholders for values
#Bulk insert
query = "INSERT INTO mytable({0}) VALUES ({1})".format(columns, values)
cursor.executemany(query, my_list[1:])
conn.commit() #save change

edited Mar 5, 2020 at 18:42

answered Mar 4, 2020 at 21:27

jignatius

6,5442 gold badges19 silver badges37 bronze badges

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

1 Comment

codingInMyBasement Over a year ago

how would I make this a batch insert? My real data set I have hundreds of rows of values.

Błotosmętek · Accepted Answer · 2020-03-04 20:55:17Z

0

Assuming conn is already open connection to your database:

cursor = conn.cursor()
for row in my_list:
    cursor.execute('INSERT INTO my_table (city, state) VALUES (?, ?)', row)
cursor.commit()

answered Mar 4, 2020 at 20:55

Błotosmętek

13k23 silver badges30 bronze badges

Comments

AzyCrw4282 · Accepted Answer · 2020-03-05 01:02:17Z

0

Since the columns value are are the first elemnts in the array, just do:

q ="""CREATE TABLE IF NOT EXISTS stud_data (`{col1}` VARCHAR(250),`{col2}` VARCHAR(250); """
sql_cmd = q.format(col1 = my_list[0][0],col2 = my_list[0][1])

mcursor.execute(sql)#Create the table with columns

Now to add the values to the table, do:

for i in range(1,len(my_list)-1):
   sql = "INSERT IGNORE into test_table(city,state) VALUES (%s, %s)"
   mycursor.execute(sql,my_list[i][0],my_list[i][1])
mycursor.commit()
print(mycursor.rowcount, "Record Inserted.")#Get count of rows after insertion

edited Mar 5, 2020 at 1:02

answered Mar 4, 2020 at 20:57

AzyCrw4282

7,8945 gold badges26 silver badges43 bronze badges

Collectives™ on Stack Overflow

Insert nested arrays into sql from python

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related