Using python to build an sqlite database from multiple csv files

Question

I am trying to use Python (Enthought Canopy Editor) to build an sqlite database from csv files. I would like to import all csv files contained in a folder into separate tables, with the table name being assigned based on the csv filename. So far I have managed to copy and paste chunks of code found online into the following:

import csv
import sqlite3
# Create the database
connection = sqlite3.connect('C:/ROAST/3_ANALYSIS/03_SQL-PY/primo.db')
cursor = connection.cursor()

# Create the table
cursor.execute('DROP TABLE IF EXISTS A08')
cursor.execute('CREATE TABLE  A08 (uno, due, tre) ')
connection.commit()

# Load the CSV file into CSV reader
csvfile = open('C:/ROAST/3_ANALYSIS/03_SQL-PY\A08_csv/A08_B1_T5.csv', 'rb')
creader = csv.reader(csvfile, delimiter=',', quotechar='|')

# Iterate through the CSV reader, inserting values into the database
for t in creader:
cursor.execute('INSERT INTO A08 VALUES (?,?,?)', t )

# Close the csv file, commit changes, and close the connection
csvfile.close()
connection.commit()
connection.close()'

What, precisely, is your question?

Robᵩ
– Robᵩ

2016-05-13 18:37:01 +00:00
Commented May 13, 2016 at 18:37 — Robᵩ
– Robᵩ, Commented May 13, 2016 at 18:37

Robᵩ · Accepted Answer · 2016-05-13 18:54:21Z

2

Here is how I might approach your problem.

import csv
import sqlite3
import glob
import os

def do_directory(dirname, db):
    for filename in glob.glob(os.path.join(dirname, '*.csv')):
        do_file(filename, db)

def do_file(filename, db):
        with open(filename) as f:
            with db:
                data = csv.DictReader(f)
                cols = data.fieldnames
                table=os.path.splitext(os.path.basename(filename))[0]

                sql = 'drop table if exists "{}"'.format(table)
                db.execute(sql)

                sql = 'create table "{table}" ( {cols} )'.format(
                    table=table,
                    cols=','.join('"{}"'.format(col) for col in cols))
                db.execute(sql)

                sql = 'insert into "{table}" values ( {vals} )'.format(
                    table=table,
                    vals=','.join('?' for col in cols))
                db.executemany(sql, (list(map(row.get, cols)) for row in data))

if __name__ == '__main__':
    conn = sqlite3.connect('foo.db')
    do_directory('.', conn)

Replace the final __main__ section as appropriate. Perhaps you'll want:

connection = sqlite3.connect('C:/ROAST/3_ANALYSIS/03_SQL-PY/primo.db')
do_file('C:/ROAST/3_ANALYSIS/03_SQL-PY\A08_csv/A08_B1_T5.csv', connection)

or

connection = sqlite3.connect('C:/ROAST/3_ANALYSIS/03_SQL-PY/primo.db')
do_directory('C:/ROAST/3_ANALYSIS/03_SQL-PY\A08_csv',connection)

edited May 13, 2016 at 18:54

answered May 13, 2016 at 18:45

Robᵩ

170k20 gold badges251 silver badges323 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Andreuccio Over a year ago

Rob, I cannot thank you enough for this. It really got me going after several failed attempts - which added to my frustration! I will now need to understand how to manage the sqlite database through Python, i.e. adding columns to perform mathematical operations, merging/appending rows (when data updates or replicates the existing) etc. Thanks!

Robᵩ Over a year ago

You might reconsider your approach. Do yourself a favor and investigate ORMs like SQLAlchemy before you commit to this approach.

Andreuccio Over a year ago

Rob, thanks for your further comments. I have spoken with a senior academic in computing, who has recommended I do a wider use of Python for data analysis - using SQL as a flat database. This would come in handy as I am planning to work (with due help) at a web dashboard with D3 charts (linking data analytics with visualisation by means of django).

Andreuccio Over a year ago

Furthermore, can I bug you with one more follow-up question? To complete the database, I would need to import a wide range of csv files, part of which will have repeated data which would need to be deleted. The filenames have the following structure: XXX(house)_XX(room)_XX(visit to download data) So I will have A08_B1_T4.csv (data collected february to may) and A08_B1_T5.csv (same data collected february to october). I would like both T4 and T5 to be uploaded into the SQLite DB, but them to be eventually merged into ONE table A08_B1_T. Does it make sense?

Collectives™ on Stack Overflow

Using python to build an sqlite database from multiple csv files

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related