Import Data from .csv file into mysql using python

I am trying to import data from two columns of a .csv file (time hh:mm, float). I created a database and a table in mysql.

import mysql.connector
import csv

mydb = mysql.connector.connect(host='127.0.0.1',
user= 'xxx',
passwd='xxx',
db='pv_datenbank')

cursor = mydb.cursor()
# get rid of the 'ï»¿' at the beginning of the .csv file
s = open('Sonneneinstrahlung.csv', mode='r', encoding='utf-8-sig').read()
open('Sonneneinstrahlung.csv', mode='w', encoding='utf-8').write(s)
print(s)

with open('Sonneneinstrahlung.csv') as csv_file:
    csv_reader = csv.reader(csv_file, delimiter=';')
    sql = """INSERT INTO einstrahlung ('Uhrzeit', 'Einstrahlungsdaten') VALUES   (%s, %s)"""
    for row in csv_reader:
            print(row)
            print(cursor.rowcount, "was inserted.")
            cursor.executemany(sql, csv_reader)
            #cursor.execute(sql, row, multi=True)
            mydb.commit()
mydb.close()

If I run the program with executemany(), result is the following:

[’01:00′, ‘1’]
‘-1 was inserted.’

and after this I do get the error code: Not all parameters were used again.

When I try the same thing with the execute() operator, no error is shown, but the data is not inserted in the table of my database.

Here you can see the input data:

Answer

executemany takes a statement and a sequence of sets of parameters.
Try this:

with open('Sonneneinstrahlung.csv') as csv_file:
    csv_reader = csv.reader(csv_file, delimiter=';')
    sql = """INSERT INTO einstrahlung (Uhrzeit, Einstrahlungsdaten) VALUES (%s, %s)"""
    cursor.executemany(sql, csv_reader)
    mydb.commit()

Advertisement

Answer