error mysql.connector.errors.Programmingerror: Not enough Parameters for the SQL statement

Asked

Viewed 152 times

-2

# script de carga 
import csv 
import mysql.connector

# conexao com o banco 
mysql = mysql.connector.connect(
    host='0.0.0.0',
    user='root',
    passwd='mysql',
    database='mydesenv',
    port=3306
)
cur=mysql.cursor()
sql = """insert into mydesenv.tb_load (id, nm_name, dt_load) values (%s, %s, %s)"""
#passo01 - Ler arquivo .csv
csv_data = csv.reader('/Users/eduardoaandrad/Dropbox/Desenv/Script/csv/carga_teste.csv',delimiter=';')
for row in csv_data:
    print(row)
    cur.execute(sql,row)

mysql.commit()
cur.close()
python

2 answers

0

The problem is in the way you are reading the csv file.

In the following passage:

csv_data = csv.reader('/Users/eduardoaandrad/Dropbox/Desenv/Script/csv/carga_teste.csv',delimiter=';')

The reported file is being interpreted as a string, literally, so its loop generates a completely different output than expected in the SQL statement.

See this isolated CSV reading snippet:

import csv

csv_data = csv.reader('carga_teste.csv',delimiter=';')

for row in csv_data:
    print(type(row), row)

Even if the file does not exist, the code will run displaying the file name in the console, generating a list for each letter:

See online the result: https://repl.it/repls/WetShyRadius

To fix this situation, you should open the file with the function open and the return of function open will be the parameter for the reader of csv:

import csv

with open("carga_teste.csv") as csvFile:
  csv_data = csv.reader(csvFile, delimiter=';')

  for row in csv_data:
      print(row)

See the difference online: https://repl.it/repls/DeadInnocentOutlier

I performed these corrections in the code and the data was correctly entered.

Documentation: https://docs.python.org/3/library/csv.html

0

Daniel, thank you so much for your help !

Correct the code with the points pointed 

import mysql.connector
import csv
mydb = mysql.connector.connect(user='root', 
                               password='mysql',
                               host='0.0.0.0',
                               database='mydesenv')
cursor = mydb.cursor()
with open('/Users/eduardoaandrad/Dropbox/Desenv/script/csv/carga_teste.csv') as source:
    csv_data = csv.reader(source, delimiter=';')
     for row in csv_data:
        cursor.execute("insert into mydesenv.tb_load (num, nm_name, dt_load) values (%s, %s, %s)",row)
        mydb.commit()
cursor.close()

But still the error persists ! I noticed that the dictionary is returning everything as a single string, example: ['num;nm_name;dt_load'] I believe I should return this way ['num';'nm_name';'dt_load'] Thank you

Browser other questions tagged python

You are not signed in. Login or sign up in order to post.