Mysql and python error when entering data

Question

I have the following code:

#encoding: utf-8
from django.utils.encoding import smart_str
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="[--CENSURADO--]",
  passwd="[--CENSURADO--]",
  database="Planilhas"
)
mycursor = mydb.cursor()

clientes = []

print "Loading table..."
f = open("Completo.csv", "r")
for x in f:  
  clientes.append(x)

temp = []
for x in clientes:
        temp.append(x.split(';'))
clientes = temp

for c in clientes:
        sql = """INSERT INTO 'Completa'('id', 'cpf', 'nome', 'ordem', 'tipo', 'posto', 'sub on', 'upag', 'valor$
"""
        values = ()
        for data in c:
                sql = sql.replace("%s", data, 1)

        print sql
        mycursor.execute(sql)
        mydb.commit()

He basically reads a file. csv with 25 columns and takes the data in each row and replaces the query in the %s reference and theoretically inserts it into the database, but returns the following error:

Traceback (most recent call last):
  File "model_for_completa.py", line 33, in <module>
    mycursor.execute(sql)
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/cursor.py", line 559, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 494, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 396, in _handle_result
    raise errors.get_exception(packet)
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 ''Completa'('id', 'cpf', 'nome', 'ordem', 'tipo', 'posto', 'sub on', 'upag', 'val' at line 1

Code out:

Loading table... INSERT INTO 'Completa'('id', 'cpf', 'nome', 'ordem', 'tipo', 'posto', 'sub on', 'upag', 'valor', 'prazo', 'banco', 'nascimento', 'endereco', 'numero', 'complemento', 'bairro', 'cidade', 'uf', 'cep', 'tel_fixo_1', 'tel_fixo_2', 'tel_fixo_3', 'tel_cel_1', 'tel_cel_2', 'tel_cel_3') VALUES ('','CPF','NOME','ORDEM','TIPO','POSTO','SUB_OM','UPAG','VALOR','PRAZO','BANCO','Data Nasc','ENDERECO','NUMERO','COMPLEMENTO','BAIRRO','CIDADE','UF','CEP','FIXO1_TEL','FIXO2_TEL','FIXO3_TEL','CEL1_TEL','CEL2_TEL','CEL3_TEL ')

Answer 1

A tip, to read files csv use the module csv, instead of splitting the data yourself, it’s much easier.

Now, by answering your question to insert data into mysql using mysql Connector, you should not replace o %s by value - instead, pass the values separately for the method .execute(), and mysql will take care to insert them correctly in the query.

import csv

sql = """INSERT INTO Completa(id, cpf, nome, ordem, tipo, posto, `sub on`, upag, valor, prazo, banco, nascimento, endereco, numero, complemento, bairro, cidade, uf, cep, tel_fixo_1, tel_fixo_2, tel_fixo_3, tel_cel_1, tel_cel_2, tel_cel_3)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""

with open("Completo.csv", newline='') as f:
     cf = csv.reader(f, delimiter=';')
     for c in cf:
        mycursor.execute(sql, c)

As you can see, c is a sequence of values being passed separately to the method execute() containing the data that will be inserted. The query follows without changes, with the %s even as they are.

This way, you also do not need to worry about quotation marks since mysql will take care of the data type automatically, and also no need to worry about sql injection in the variables because they will never be part of the query.

An even faster option is to use the executemany(); So the operation is prepared once only for all data. It can make a lot of difference if it is too many records:

with open("Completo.csv", newline='') as f:
    cf = csv.reader(f, delimiter=';')
    mycursor.executemany(sql, cf)