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

Can you also include the error you are getting, for completeness? Or in case you are not getting an error, the expected output. — daltonfury42
– daltonfury42, Commented Jul 23, 2019 at 3:08
i got this message: "Failed inserting record into table 1054 (42S22): Unknown column 'my_trans' in 'field list' — i have no name
– i have no name, Commented Jul 23, 2019 at 3:12
@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 — i have no name
– i have no name, Commented Jul 23, 2019 at 3:19

blizz · Accepted Answer · 2022-03-17 03:17:26Z

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)

edited Mar 17, 2022 at 3:17

blizz

4,1686 gold badges39 silver badges62 bronze badges

answered Jul 23, 2019 at 3:30

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

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

5 Comments

i have no name Over a year ago

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 Over a year ago

how about this?

Ben Farmer Over a year ago

Maybe it's just me, but the python code in this answer doesn't seem to be valid syntax...

Barmar Over a year ago

append() is a function, you don't assign to it.

actopozipc Over a year ago

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

actopozipc · Accepted Answer · 2024-10-09 10:28:08Z

0

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

answered Oct 9, 2024 at 10:28

actopozipc

1051 silver badge13 bronze badges

Collectives™ on Stack Overflow

how to insert an array into database in python?

2 Answers 2

5 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Linked

Related