Python Import Csv To Sqlite

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):

Solution 1:

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

classcsvrd(object):
    defcsvFile(self):

        self.readFile('Labels.csv')

    defreadFile(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"withopen(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()

Solution 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)

Solution 3:

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 with4 elements forsecondexecute argument

Solution 4:

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.