I’m having doubts about how to insert several elements into the database by mysql.

Question

import mysql.connector

mysql.connection = mysql.connector.connect(host='localhost',
                                          user='root',
                                          password='rgs050601',
                                          database='testegr')

sql = "INSERT INTO pet (id,numero,idade) VALUES %s"

pd = "(5,6,1),(9,8,1),(7,5,9)"

cursor = mysql.connection.cursor()

cursor.execute(sql,(pd,))
mysql.connection.commit()

this is not my original code I only made as a very simple test base wanted to insert the values of pd directly in the query but without using the executemany or concatenate the values in the string above that would be the one of the query

Answer 1

One way to solve using your code is by using the f-string python 3. Simply modify your code to:

import mysql.connector

cnx = mysql.connector.connect(host='localhost',
                              user='root',
                              password='rgs050601',
                              database='testegr')

sql = "INSERT INTO pet (id,numero,idade) VALUES (%s,%s,%s)"
pd = [(5,6,1),(9,8,1),(7,5,9)]

cursor = cnx.cursor()

for item in pd:
    print(item) #esta linha é desnecessária e pode ser removida
    cursor.execute(sql, item)
cnx.commit()

Check that this required changing the variable sql.

It was also necessary to modify the variable pd.

To be able to insert multiple lines it was necessary to insert row by row into a for loop. For more information you can see in the documentation more details.

If you want it is possible to use your approach, but it is necessary to stop using the f-string and use the method format of string.

Note that at the end of the string there is a {} is in that space that we will concatenate the string we want.

Follow an example:

sql = "INSERT INTO pet (id,numero,idade) VALUES {}"
pd = '(5,6,1),(9,8,1),(7,5,9)'
cursor = cnx.cursor()
cursor.execute(sql.format(pd))
cnx.commit()

I hope I’ve helped.