Importing a CSV File into a SQLite3 Database Table Using Python

Importing a CSV file into a sqlite3 database table using Python

import csv, sqlite3

con = sqlite3.connect(":memory:") # change to 'sqlite:///your_filename.db'
cur = con.cursor()
cur.execute("CREATE TABLE t (col1, col2);") # use your column names here

with open('data.csv','r') as fin: # `with` statement available in 2.5+
    # csv.DictReader uses first line in file for column headings by default
    dr = csv.DictReader(fin) # comma is default delimiter
    to_db = [(i['col1'], i['col2']) for i in dr]

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

Import .csv files into SQL database using SQLite in Python

This works for me on Windows 10, but should work under Linux/Unix too. There are several problems:

1. The last two rows of person.csv are not correct format, but this does not prevent the program from working. You can fix this with a text editor.
2. person.csv uses tabs as the delimiter not commas.
3. There is a typo (spelling) in the line that starts with "to_db ="
4. There is a mismatch in the number of columns to import (2 instead of 11)
5. Wrong table name on executemany.

In addition, I create the database in a file rather than in memory. It is small enough that performance should not be a problem and also any changes you make will be saved.

Here is my corrected file (you can do the other table yourself):

import sqlite3, csv

# con = sqlite3.connect(":memory:")
con = sqlite3.connect("person.db")
cur = con.cursor()
cur.execute("CREATE TABLE person (personid STR,age STR,sex STR,primary_voting_address_id STR,state_code STR,state_fips STR,county_name STR,county_fips STR,city STR,zipcode STR, zip4 STR,  PRIMARY KEY(personid))") 

with open('person.csv','r') as person_table:
    dr = csv.DictReader(person_table, delimiter='\t') # comma is default delimiter
    to_db = [(i['personid'], i['age'], i['sex'], i['primary_voting_address_id'], i['state_code'], i['state_fips'], i['county_name'], i['county_fips'], i['city'], i['zipcode'], i['zip4']) for i in dr]

cur.executemany("INSERT INTO person VALUES (?,?,?,?,?,?,?,?,?,?,?);", to_db)
con.commit()

Create a sqlite table from a csv file

As per sqlite3 API reference, just put database name and error will go away.

con = sqlite3.connect("data.db")

is there a way to import csv data into a sqlite db, while not explicitly typing the columns out

If you don't want to store it in a file (as you don't want to create a table) you can do

conn = sqlite3.connect("file::memory:?cache=shared")

and to create the temp table (which would be a similar result as SELECT INTO), notice that you don't need to specify the column types

crt = 'CREATE TEMP TABLE t('
ins = 'INSERT INTO t VALUES('
for n in range(len(csvrows[0])):
    if n:
        crt += ', '
        ins += ', '
    crt += f'c{n}'
    ins += '?'
crt += ');'
ins += ');'
sqlcursor.execute(crt)
sqlcursor.executemany(ins, csvrows)

sqlite has a VALUES keyword that you can use in a SELECT UNION or similar, for example

sqlite> VALUES (0,1,2,3,4),(5,6,7,8,9) UNION SELECT * FROM sqlite_master;
0|1|2|3|4
5|6|7|8|9 

but I don't think this could help.

---

Related Topics