python import csv to sqlite

1
votes

Hi i am trying to import a csv file to a sqlite3 database using python tkinter. I open file using askopenfilename dialog and pass the file to readFile function.

def csvFile(self):
        f1 = askopenfilename()
       self.readFile(f1)

def readFile(self, filename):
    conn = sqlite3.connect('Unicommerce.db')
    cur = conn.cursor() 
    cur.execute("""CREATE TABLE IF NOT EXISTS unicom(products varchar,channel varchar,regulatory_forms varchar,shipment varchar)""")
    filename.encode('utf-8')
    print "test1"
    reader = csv.reader(filename)
    for field in reader:
        cur.execute("INSERT INTO unicom VALUES (?,?,?,?);", field)

    conn.commit()
    conn.close()

I am getting this error.

cur.execute("INSERT INTO unicom VALUES (?,?,?,?);", field)
ProgrammingError: Incorrect number of bindings supplied. The current statement uses 4, and there are 1 supplied.

I tried every possoible solution available but couldn't import file to database.
I also tried tried solutions given in these links
Importing a CSV file into a sqlite3 database table using Python
Python CSV to SQLite

EDIT: Link to input file Input File

4
pythonsqlitecsv
This error means that there's not enough values in field to match your expression. Try adding print repr(field) before your SQL command to see what field actually contains. – SuperBiasedMan
It would be simpler if you could supply some example of your input data! – Paul Rooney
It is printing this -- ['C'] . I included my input file. Check edit – Manish Gupta
What does askopenfilename does ? It looks like you're trying to use reader with the filename of the csv when it wants the file object (see docs.python.org/2/library/csv.html#csv.reader). You need to open the file before giving it to reader. Try reader = csv.reader(open(filename, "r")) – PhilipGarnero
Thanks i used open(filename) and it worked – Manish Gupta

4 Answers

2
votes

You need to open the file before you pass it to csv.reader. Heres a basic runnable example that works. I added a class to allow your existing methods to be used as is.

import sqlite3
import csv

class csvrd(object):
    def csvFile(self):

        self.readFile('Labels.csv')

    def readFile(self, filename):
        conn = sqlite3.connect('Unicommerce.db')
        cur = conn.cursor() 
        cur.execute("""CREATE TABLE IF NOT EXISTS unicom(products varchar,channel varchar,regulatory_forms varchar,shipment varchar)""")
        filename.encode('utf-8')
        print "test1"
        with open(filename) as f:
            reader = csv.reader(f)
            for field in reader:
                cur.execute("INSERT INTO unicom VALUES (?,?,?,?);", field)

        conn.commit()
        conn.close()

c = csvrd().csvFile()
1
votes

execute can work thus:

  1. cur.execute("INSERT INTO unicom VALUES (value1,value2, value3,value4);") where value1..4 is correct text representation of your data.
  2. cur.execute("INSERT INTO unicom VALUES (?, ?, ?, ?);", [value1,value2, value3,value4] where value1..4 in list and db driver liable about correct interpretation main data types.

Ok, because I can't view your data, I would something as below:

for field in reader:
    cur.execute("INSERT INTO unicom VALUES (?,?,?,?);",\
    field.slpit(my_separator)) #create list with 4 elements for second execute argument
1
votes

Use Pandas:

import pandas
import sqlite3

conn = sqlite3.connect("test.sqlite")

conn.execute("CREATE TABLE if not exists Data (Column1 TEXT, Column2 TEXT)")

df = pandas.read_csv("test.csv")
df.to_sql("Data", conn, if_exists='append', index=False)
1
votes

I would like to share a library which I have used to convert my csv into sqlite database very easily.

It is csv2sqlite.

Run: csv2sqlite.py {csv-file-path} {sqlite-db-path} [{table-name}] in terminal.

Make sure your database has the same columns numbers as per the csv.

Hope it helps.

Thank you.