Python - MYSQL dynamic query: Insert values into table syntax [duplicate]

Question

I am using Python and mysql (import mysql connector).

I was trying to insert multiple values in a dynamic column, which I created before. However, something is messed up with the syntax of my query and I am not able to write it correctly.

What is working (not dynamic as an example): -> In Column ynoqxzvb, value '1c' is added.

                  conn.cursor()
                  select4 = """ INSERT INTO oldcards (ynoqxzvb) VALUES ('1c'); """
                  cursor.execute(select4)
                  conn.commit()

What I want to do is (dynamic):

                  select5 = """ INSERT INTO oldcards (%s) VALUES (%s); """
                  tple = (str(RouteID),str(mydict[ID1]["Card1"]))
                  cursor.execute(select5,tple)
                  conn.commit()

So basically I want to have the columnname and the inserted value dynamic using the local variables "RouteID" and "str(mydict[ID1]["Card1"])".

The error code is:

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''ynoqxzvb') VALUES ('Card2=4b')' at line 1

Does somebody know the correct syntax? Thanks in advance!

It does support dynamic query and you can also make the column name dynamic. Its just that I didnt manage to do it with multiple arguments in one query yet. — Leopold Hagen Niemann Bartels
– Leopold Hagen Niemann Bartels, Commented Jul 12, 2020 at 10:25

SevenOfNine · Accepted Answer · 2020-07-11 15:04:56Z

0

Your query is not correct, because the names of the columns do not correspond the values you are trying to insert. Assume str(RouteID) is 1 and str(mydict[ID1]["Card1"] is 2. Then cursor.execute(select5,tple) translates your query into

INSERT INTO oldcards (1) VALUES (2)

What you actually want is:

INSERT INTO oldcards (column1, column2) VALUES (1,2)

Where column1 and column2 are the names of the columns you want to insert into. So the correct way would be:

 select5 = """ INSERT INTO oldcards (column1, column2) VALUES (%s, %s); """
 tple = (str(RouteID),str(mydict[ID1]["Card1"]))
 cursor.execute(select5,tple)
 conn.commit()

answered Jul 11, 2020 at 15:04

SevenOfNine

6462 gold badges6 silver badges25 bronze badges

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

1 Comment

Leopold Hagen Niemann Bartels Over a year ago

Sorry, but that is exactly not whart I want to have. My goal is to make the column name and the inserted value both dynamic.

Collectives™ on Stack Overflow

Python - MYSQL dynamic query: Insert values into table syntax [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related