Importing CSV into MySQL

[gehrenfeld]

I have a CSV file that contains 10234 records. Here are what the first 20 rows look like.
Each row has 19 column. The delimiter='\t'

Gary Ehrenfeld
# 558686118
ID Time Record Type Historic Glucose (mg/dL) Scan Glucose (mg/dL) Non-numeric Rapid-Acting Insulin Rapid-Acting Insulin (units) Non-numeric Food Carbohydrates (grams) Non-numeric Long-Acting Insulin Long-Acting Insulin (units) Notes Strip Glucose (mg/dL) Ketone (mmol/L) N/A N/A N/A Previous Time Updated Time
132 2018/07/28 01:41 0 141
133 2018/07/28 01:56 0 133
134 2018/07/28 02:11 0 126
135 2018/07/28 02:27 1 123
137 2018/07/28 02:27 0 126
138 2018/07/28 02:42 0 119
139 2018/07/28 02:57 0 96
140 2018/07/28 03:12 0 79
141 2018/07/28 03:27 0 71
142 2018/07/28 03:42 0 69
143 2018/07/28 03:57 0 72
144 2018/07/28 04:14 1 79
147 2018/07/28 05:16 6 2018/07/28 05:16 2018/07/28 05:16
148 2018/07/28 04:12 0 83
149 2018/07/28 04:27 0 86
150 2018/07/28 04:42 0 85
151 2018/07/28 04:57 0 86

The MySQL table.

id int
r_date datetime
rtype int
hist int
scan int

I want to import only the first 5 rows from each column into the database.

My python code.

import mysql.connector
from mysql.connector import Error
import csv
from tkinter import *

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='meter',
                                         user='root',
                                         password='root')
    if connection.is_connected():
        db_Info = connection.get_server_info()
        print("Connected to MySQL database... MySQL Server version on ", db_Info)
        cursor = connection.cursor()
        cursor.execute("select database();")
        record = cursor.fetchone()
        print("Your connected to - ", record)
except Error as e:
    print("Error while connecting to MySQL", e)
finally:
    # closing database connection.
    if (connection.is_connected()):
        cursor.close()
        print("MySQL connection is closed")

cursor = connection.cursor()

f = open('summary.csv', 'r')
dr = csv.reader(f, delimiter='\t')
for row in dr:
        cursor.execute('INSERT INTO readings (ID, r_date, rtype, hist, scan) VALUES (?, ?, ?, ?, ?)',
                       row)
connection.commit()

The error I get.

"Not all parameters were used in the SQL statement")
mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

Thanks for any help.

[micseydel]

My best guess, without playing around with it, is that

        cursor.execute('INSERT INTO readings (ID, r_date, rtype, hist, scan) VALUES (?, ?, ?, ?, ?)',
                       row)

ought to be

        cursor.execute('INSERT INTO readings (ID, r_date, rtype, hist, scan) VALUES (?, ?, ?, ?, ?)',
                       row[:5])

[gehrenfeld]

I will give it a try.

Thank you

---

Tried the row[:5]. Got the same error.

I just insert all the fields and it works okay now.

Not the best solution but it works.