Python MYSQL exception when building a query statement

Question

I build a SQL statement in python that yields this result (a print of the "query" variable sent to the console during execution):

"INSERT INTO tbl_order_line (order_line,Item_Name,Item_Variant,SKU,Qty,Item_Price,Item_Weight,Item_Custom_Text,Qty_refunded,Qty_restocked) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"

I also build a variable that yields this for the data to be used in the INSERT (again a print to the console made during execution of the "parms" variable):

("10034","Lemons (3ea)","","PRODICE-25","2","1.4","0","","0","0")

I have verified that the table name and field names are correct and that the number of fields and parameters are 10 each.

The data elements are VARCHAR to eliminate any data type issues.

The error I receive is:

TypeError: not all arguments converted during bytes formatting.

The execute statement is:

c.execute(query, parms)

I have looked through several threads and have added a comma after the "parms" in the execute but get the same result.

Any guidance is appreciated.

As noted all are VARCHAR - I changed them hoping to eliminate any data type issues — Glenn
– Glenn, Commented Jul 3, 2020 at 3:19

Tim Biegeleisen · Accepted Answer · 2020-07-03 03:23:50Z

1

I can speculate here and suggest that maybe some other part of your prepared statement setup has an issue. Here is a valid way of doing what you are attempting:

conn = mysql.connector.connect(...)
c = conn.cursor(prepared=True)        # critical, turn on prepared statement mode here

sql = """INSERT INTO tbl_order_line (order_line, Item_Name, Item_Variant, SKU, Qty,
                                     Item_Price, Item_Weight, Item_Custom_Text,
                                     Qty_refunded, Qty_restocked)
         VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""

parms = ("10034", "Lemons (3ea)", "", "PRODICE-25", "2", "1.4", "0", "", "0", "0")

c.execute(sql, parms)
c.commit()

answered Jul 3, 2020 at 3:23

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

4 Comments

Glenn Over a year ago

After some review I sorted that the connect did have an error (some old 2.7 code) and the conn.cursor(prepared=True) is now installed without error. I am building the query statement with field names pulled from a database so I can't "hardcode" the statement as above but am hoping the print to consolw which shows the contents of the query variable is an accurate representation of what is being passed. If it is correct then the only difference is the extra quotes which I added but got error:mysql.connector.errors.InterfaceError: You have an error in your SQL syntax;

Glenn Over a year ago

I did comment out my code and paste in yours and it works - my problem is that I don't see a difference when I run my code and see the parameters in the console and the code you provided - could the fact that I create the field names from input rather than hardcoding them cause an issue?

Tim Biegeleisen Over a year ago

I really don't know, I just tried to give you a canonical version of what you are doing.

Glenn Over a year ago

Understood - thank you - back the the drawing board :-)

Collectives™ on Stack Overflow

Python MYSQL exception when building a query statement

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related