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?
asked Feb 6, 2017 at 8:26
shikhar bansalshikhar bansal
1,5692 gold badges19 silver badges41 bronze badges
5
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].
answered Feb 6, 2017 at 8:50
ChatterOneChatterOne
3,1711 gold badge17 silver badges24 bronze badges
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[]
answered Feb 6, 2017 at 8:51
Roy HolzemRoy Holzem
85213 silver badges25 bronze badges
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 //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.
answered Feb 6, 2017 at 8:51
ainsaustiainsausti
6751 gold badge6 silver badges21 bronze badges
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]
answered Feb 6, 2017 at 9:01
metmirrmetmirr
4,1092 gold badges20 silver badges34 bronze badges
Insert Into Table
To fill a table in MySQL, use the "INSERT INTO" statement.
Example
Insert a record in the "customers" table:
import mysql.connector
mydb = mysql.connector.connect[
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
]
mycursor = mydb.cursor[]
sql = "INSERT INTO customers [name, address] VALUES [%s,
%s]"
val = ["John", "Highway 21"]
mycursor.execute[sql, val]
mydb.commit[]
print[mycursor.rowcount, "record inserted."]
Run example »
Important!: Notice the statement: mydb.commit[]. It is required to make the changes, otherwise no changes are made to the table.
Insert Multiple Rows
To insert multiple
rows into a table, use the
executemany[] method.
The second parameter of the executemany[] method is a list of tuples, containing the data you want to insert:
Example
Fill the "customers" table with data:
import mysql.connector
mydb = mysql.connector.connect[
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
]
mycursor = mydb.cursor[]
sql = "INSERT INTO
customers [name, address] VALUES [%s, %s]"
val = [
['Peter', 'Lowstreet 4'],
['Amy', 'Apple st 652'],
['Hannah', 'Mountain 21'],
['Michael', 'Valley 345'],
['Sandy', 'Ocean blvd 2'],
['Betty', 'Green Grass 1'],
['Richard', 'Sky st 331'],
['Susan', 'One way 98'],
['Vicky', 'Yellow Garden 2'],
['Ben', 'Park Lane 38'],
['William', 'Central st 954'],
['Chuck', 'Main Road
989'],
['Viola', 'Sideway 1633']
]
mycursor.executemany[sql, val]
mydb.commit[]
print[mycursor.rowcount, "was inserted."]
Run example »
Get Inserted ID
You can get the id of the row you just inserted by asking the cursor object.
Note: If you insert more than one row, the id of the last inserted row is returned.
Example
Insert one row, and return the ID:
import mysql.connector
mydb = mysql.connector.connect[
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
]
mycursor = mydb.cursor[]
sql = "INSERT INTO customers [name, address] VALUES [%s, %s]"
val = ["Michelle", "Blue Village"]
mycursor.execute[sql, val]
mydb.commit[]
print["1 record inserted, ID:", mycursor.lastrowid]
Run example »