how to insert an array into database in python?

Question

I have a database called my_python and I have a table called my_transport. There are 3 columns in the table: id, transport, and fee. for the first columns "id", I make it auto increment so that I don't have to insert any value into it. My question is how to insert the value of my_trans and my_fee into the database table?

 import mysql.connector
 my_trans=['car','train','ship','train'] 
 my_fee=[200,300,150,200]
 try:
 connection = mysql.connector.connect(host='localhost',
 database='my-python',
 user='root',
 password='')
 sql_insert_query = """INSERT INTO my_transport
 (`transport`, `fee`) VALUES (my_trans, my_fee)"""
 cursor = connection.cursor()
 result = cursor.execute(sql_insert_query)
 connection.commit()
 print ("Record inserted successfully into table") except mysql.connector.Error as error :
 connection.rollback() #rollback if any exception occured
 print("Failed inserting record into table {}".format(error)) finally:
 #closing database connection.
 if(connection.is_connected()):
 cursor.close()
 connection.close()
 print("MySQL connection is closed")

I have try below code but it said:

"Failed inserting record into table 1054 (42S22): Unknown column 'my_trans' in 'field list'"

• daltonfury42: Can you also include the error you are getting, for completeness? Or in case you are not getting an error, the expected output.
• i have no name: i got this message: "Failed inserting record into table 1054 (42S22): Unknown column 'my_trans' in 'field list'
• daltonfury42: Do you want to insert the values comma seperated?
• i have no name: i want them inserted into different rows
• i have no name: @EdBangga i have try your suggestion and it result in this message: Failed inserting record into table 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '[my_transport] (transport, fee) VALUES ([my_trans' at line 1

Answer 1

use .executemany to insert array of records as mentioned here

my_trans = []
my_trans.append('car', 200)
my_trans.append('train', 300)
my_trans.append('ship', 150)
my_trans.append('train', 200)
sql_insert_query = 'INSERT INTO my_transport
 (transport, fee) VALUES (%s, %s)'
cursor = connection.cursor()
result = cursor.executemany(sql_insert_query, my_trans)

• i have no name: I got this message afterusing executemany: Traceback (most recent call last): "File "/home/acer/PycharmProjects/bs1/try.py", line 14, in <module> result = cursor.executemany(sql_insert_query, my_trans, my_fee) TypeError: executemany() takes 3 positional arguments but 4 were given". Then i try to separate my_trans and my_fee but i got this message: Failed inserting record into table Failed rewriting statement for multi-row INSERT. Check SQL syntax.
• Ed Bangga: how about this?
• Ben Farmer: Maybe it's just me, but the python code in this answer doesn't seem to be valid syntax...
• Barmar: append() is a function, you don't assign to it.
• actopozipc: For anyone still wondering about this: You need 1) insert a tuple instead of several values, e.g my_trans.append(("ship", 150)). After using executemany, you also need to use connection.commit()

Answer 2

In order to use executemany, you need to create a list of tuples first

tuple_list = [('car',200), ('train',300)]

to pass it with the query

sql_insert_query = 'INSERT INTO my_transport
 (transport, fee) VALUES (%s, %s)'

as argument

cursor = connection.cursor()
result = cursor.executemany(sql_insert_query, tuple_list)
cursor.commit()