1

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

Leonardo Henriques
  • 506
  • 5
  • 19

2 Answers2

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • [Check the documentation](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursorprepared.html). – Tim Biegeleisen Jan 26 '20 at 04:48
  • cursor = conn.cursor(prepared=True) TypeError: cursor() got an unexpected keyword argument 'prepared' – Leonardo Henriques Jan 26 '20 at 04:51
  • @Leo What is the driver/connector you are using? Can you check [here](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursorprepared.html) and let us know if it looks familiar to you? – Tim Biegeleisen Jan 26 '20 at 04:53
  • import MySQLdb conn = MySQLdb.connect(host= "localhost", user="root", passwd="****", db="tryd") – Leonardo Henriques Jan 26 '20 at 04:56
  • 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 https://stackoverflow.com/questions/50243506/caching-sha2-password-is-not-supported-mysql – Leonardo Henriques Jan 26 '20 at 05:12
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.

Nick
  • 138,499
  • 22
  • 57
  • 95