CODEWITHSUNDEEP
mysqlsqlpython-3.x
i have no name
i have no name

Reputation: 43

how to insert an array into database in python?

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'"

Upvotes: 0

Views: 7772

Answers (2)

actopozipc
actopozipc

Reputation: 105

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()

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13006

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)

Upvotes: 1

Related Questions