CODEWITHSUNDEEP
pythonmysql
shikhar bansal
shikhar bansal

Reputation: 1679

Inserting a Python List in SQL Table

I have a python list like L = [1,5,4,2,5,6,...around 100 elements] and an SQL table with 100 columns. I want to insert each element of L in the corresponding column in SQL Table. For an example, 1 gets inserted in column 1 and 5 gets inserted in column 2 (in the same row). The SQL query will be like INSERT into table (0,1,2,3,4 ... 100 column names) values (%s, %s, %s, %s .... 100 times) Can there be a better way to accomplish this?

Upvotes: 0

Views: 10644

Answers (4)

metmirr
metmirr

Reputation: 4302

q: the first part of query, v: second part, for %ss 

q = "insert into table ("
for i in range(100):
    q += str(i) + ", "
q += "100)"
v = "values ("
for i in range(100):
    v += "%s, "
v += "%s)"

insert_query = q+v
conn.execute(insert_query, data_list)

Upvotes: 0

ainsausti
ainsausti

Reputation: 715

You can use the pandas library as follows:

First, convert your list into a dataframe

import pandas as pd

L = [1, 2, 3, 4, 5, 6, 7]

df = pd.DataFrame([L])

The output will be a dataframe with one row and generic columns and index:

   0  1  2  3  4  5  6 
0  1  2  3  4  5  6  7

And then just use the to_sql command to insert the row into your table. The doc of the method is in http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html. So, it will look similar to:

df.to_sql(table_name, connection, ...) # Look at the rest of the parameters if necessary.

The connection parameter refers to the connection with the database and must be established before this line of code.

Upvotes: 0

Roy Holzem
Roy Holzem

Reputation: 870

In this example:

lst = ['2', '5', '0', '1', '9']
for i in enumerate(lst):
    print i

we get this results as a tuple:

(0, '2')
(1, '5')
(2, '0')
(3, '1')
(4, '9')

i[0] represents the 0'th element in your list and the 0'th column in your SQL. so we save the 0'th value (which is 2) into the 0'ths column, and so on.

so we write a function for this: 

def inserting():
    cursor.execute("""
    INSERT INTO table(i[0]) \
    VALUES (%s) """, (i[1])
    cursor.close()

and then:

for i in enumerate(L):
    inserting()

Upvotes: 1

ChatterOne
ChatterOne

Reputation: 3541

If your question is only about syntax, you can use a list containing field names and question marks for the values.

Something like this (not tested):

field_names = ('field1', 'field2', 'field3') # and so on
insert_data = ('value1', 'value2', 'value3') # and so on

db_connection.execute('INSERT INTO table (' + ','.join(field_names) + ') VALUES (' + ','.join('?' * len(insert_data)) + ')' , insert_data)

If your data count is the same as your table field's count, you can omit the field names (i.e. if you have 100 fields, 100 values and the values are already sorted in the same way that the fields were declared when creating the table).

Upvotes: 3

Related Questions