Error in trying to import a CSV file into a sqlite3 database table using Python

Question

Trying to import this .csv into sqlite with python and getting an error:

import csv, sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()

cur.execute("CREATE TABLE t (ID, LIMIT_BAL, SEX, EDUCATION, MARRIAGE, AGE, PAY_0, PAY_2, PAY_3, PAY_4, PAY_5, PAY_6, BILL_AMT1, BILL_AMT2, BILL_AMT3, BILL_AMT4, BILL_AMT5, BILL_AMT6, PAY_AMT1, PAY_AMT2, PAY_AMT3, PAY_AMT4, PAY_AMT5, PAY_AMT6, Default);")

with open('C:\\Users\\Joseph\\Desktop\\W7\\UCI_Credit_Card.csv','rb') as fin: 

    dr = csv.DictReader(fin)

    to_db = [(i['col1'], i['col2']) for i in dr]

cur.executemany("INSERT INTO t (col1, col2) VALUES (?, ?);", to_db)
con.commit()
con.close()'''

Error: >>> ================ RESTART: C:/Users/Joseph/Desktop/W7/Wk7test.py ================ Traceback (most recent call last): File "C:/Users/Joseph/Desktop/W7/Wk7test.py", line 5, in cur.execute("CREATE TABLE t (ID, LIMIT_BAL, SEX, EDUCATION, MARRIAGE, AGE, PAY_0, PAY_2, PAY_3, PAY_4, PAY_5, PAY_6, BILL_AMT1, BILL_AMT2, BILL_AMT3, BILL_AMT4, BILL_AMT5, BILL_AMT6, PAY_AMT1, PAY_AMT2, PAY_AMT3, PAY_AMT4, PAY_AMT5, PAY_AMT6, Default);") sqlite3.OperationalError: near "Default": syntax error

default is a SQLite keyword: sqlite.org/lang_keywords.html You'll need to rename that column. — mechanical_meat
Renamed column and changed 'rb' to 'r' to fix a text error but now getting: Traceback (most recent call last): File "C:\Users\Joseph\Desktop\W7\Wk7test.py", line 11, in <module> to_db = [(i['col1'], i['col2']) for i in dr] File "C:\Users\Joseph\Desktop\W7\Wk7test.py", line 11, in <listcomp> to_db = [(i['col1'], i['col2']) for i in dr] KeyError: 'col1' — Dan Severn

tit tit · Accepted Answer · 2020-05-19T23:33:49

Not answering directly to your question, but importing a csv can be done easily and quickly (especially large ones) by using the following :

.open databasePath
.mode csv
.separator ; /* or , */
.import csvFilePath tableName

or by putting those lines into a file (myfile) and executing it (under Python, NodeJS , ... command line)

sqlite3 foo ".read myfile"

It will create also a foo empty database that you may have to delete, not too much a pain.

possible drawbacks : you get a table that shares the same header name than the csv and the types may not be the desired ones. However, this can be solved by creating another database from the import one, along performing data trasformation ... thats another story.

main advantage : it is a straight forward process and you have a table that reflects the csv source, from which you can build the following steps.

Error in trying to import a CSV file into a sqlite3 database table using Python

1 Answers