SQL syntax error when try insert into by python

Question

I have the following table in my database:

create table tt (
id varchar(20),
hora varchar(20),
preco varchar(20),
qtde varchar(20),
cpa varchar(20),
vda varchar(20),
agressor varchar(20)
);

I put them all like varchar (30) because my head was too white with the mistakes they were making.

even with all fields like varchar (30) I am not able to insert into the following 7 values via python

19928 12:53:34 4.049,00 5 107 308 Comprador

(19928, "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 ':53:34, 4.049,00, 5, 107, 308, Comprador)' at line 1")

my command to insert into is the following:

c.execute("INSERT INTO tt VALUES ({}, {}, {}, {}, {}, {}, {})".format(id_, hora, preco, qtde, cpa, vda, agressor))
conn.commit()

could also assist me in what types of variables to use to create the table? (int, varchar (20), float)?

Tim Biegeleisen · Accepted Answer · 2020-01-26 04:44:50Z

3

You should be using a prepared statement here, which would likely also resolve your current errors:

cursor = conn.cursor(prepared=True)
sql = """INSERT INTO tt (id, hora, preco, qtde, cpa, vda, agressor)
         VALUES (%s, %s, %s, %s, %s, %s, %s)"""
cursor.execute(sql, (id_, hora, preco, qtde, cpa, vda, agressor,))
conn.commit()

Using this approach, you let Python/MySQL worry how to properly escape your string data with single quotes. Also note that it is best practice to always explicitly list out the target columns for an insert, which I have done above.

answered Jan 26, 2020 at 4:44

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

5 Comments

Tim Biegeleisen Over a year ago

Check the documentation.

Leonardo Henriques Over a year ago

cursor = conn.cursor(prepared=True) TypeError: cursor() got an unexpected keyword argument 'prepared'

Tim Biegeleisen Over a year ago

@Leo What is the driver/connector you are using? Can you check here and let us know if it looks familiar to you?

Leonardo Henriques Over a year ago

import MySQLdb conn = MySQLdb.connect(host= "localhost", user="root", passwd="****", db="tryd")

Leonardo Henriques Over a year ago

a lot of problems here. the first: this question, the second: the conector (import mysql.connector), the third: the package (i need to pip install MySQL-connector-python); your and this question help me a lot stackoverflow.com/questions/50243506/…

Nick · Accepted Answer · 2020-01-26 22:38:38Z

0

Your problem is that your query looks like this:

INSERT INTO tt VALUES (19928, 12:53:34, 4.049,00, 5, 107, 308, Comprador)

which is invalid because of unquoted time and string values, and the comma in one of the numbers. You need to use the prepared statement form:

c = conn.cursor()
c.execute("INSERT INTO tt VALUES (%s, %s, %s, %s, %s, %s, %s)",(id_, hora, preco, qtde, cpa, vda, agressor))
conn.commit()
c.close()

Note that when you change your column data types, to insert 4.049,00 into MySQL as a number you will need to first swap the , and . characters.

edited Jan 26, 2020 at 22:38

answered Jan 26, 2020 at 4:45

Nick

147k23 gold badges67 silver badges106 bronze badges

1 Comment

user2864740 Over a year ago

Note: OP only has varchar fields .. a problem for another day I suppose.

Collectives™ on Stack Overflow

SQL syntax error when try insert into by python

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related