2

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

Community
  • 1
  • 1
Manish Gupta
  • 4,438
  • 18
  • 57
  • 104

4 Answers4

2

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()
Paul Rooney
  • 20,879
  • 9
  • 40
  • 61
2

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)
Zerge
  • 91
  • 1
  • 4
1

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
Michael Kazarian
  • 4,376
  • 1
  • 21
  • 25
1

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.

Hiren
  • 676
  • 7
  • 21