Turning CSVs Into SQLite Tables with Python

Overview:
1) Unzip all files
2) Convert files to pandas dataframes
3) Create SQL tables from those dataframes

gzip, shutil, and os in Python:

#####Unzipping Files
import gzip
import shutil
import os
z_folder = 'zippedData'
cwd = os.getcwd()
dir_to_unzip = os.path.join(cwd, z_folder)
def unzipDir(dir_name, verbose=False):
for name in os.listdir(dir_name):
path = os.path.join(dir_name, name)
#print(path)
if os.path.isfile(path) and path[-3:]=='.gz':
if verbose:
print('unzipping',path)
new_name=name[:-3]
new_path=os.path.join(dir_name,new_name)
with gzip.open(path, 'rb') as f_in:
with open(new_path, 'wb') as f_out:
shutil.copyfileobj(f_in, f_out)
unzipDir(dir_to_unzip, verbose=True)

Pandas Dataframes

import pandas as pddfs = {}
def make_dfs(dir_name):
for currfile in os.listdir(dir_name):
path = os.path.join(dir_name, currfile)
if os.path.isfile(path) and (path[-4:]=='.csv':
currdf = pd.read_csv(os.path.join(dir_name,currfile), header=0)
dfs[currfile]=currdf
make_dfs(dir_to_unzip)

SQLite

import sqlite3
from sqlite3 import Error
from pandas import DataFrame
def create_conn(path):
conn = None
try:
conn = sqlite3.connect(path)
print("Connection successful")
except Error as e:
print("The error % occurred.".format(e))
return conn

conn = create_conn('TestDB1.db')
c = conn.cursor()
def convertDFtoSQL(df_name):
#df_name='df_movie_budgets'
df = dfs[df_name]
cols = str(list(df.columns))[1:-1]
createString = 'CREATE TABLE '+ df_name + '(' + cols + ')'
try:
c.execute(createString)
except:
pass
df.to_sql(df_name, conn, if_exists='replace', index=False)


c.execute('SELECT * FROM '+df_name)
#Checking that it came through
dfOut = pd.DataFrame(c.fetchall())
dfOut.columns = [i[0] for i in c.description]
print(df_name)
display(dfOut.head(4))
for df in dfs.keys():
convertDFtoSQL(df)
Formatted Collected Python Code Suitable for Jupyter Notebook use, to process zipped csv files into SQLite tables

--

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Basic Bash for Beginners: Getting Around Your Computer Like a Pro

Indiana Jones carefully extracting an artifact

How to Prepare for the Confluent Certified Operator for Apache Kafka (CCOAK) exam

How To Block / Blacklist a Number on Asus Zenpad 3s 8.0 Z582KL

How To Block / Blacklist a Number on Asus Zenpad 3s 8.0 Z582KL

Android Architecture Components

Contact Management System In C Language

Operators Priorities: Python Complete Course — Part 17

A dummy image for better reading and navigation.

Variables in Java

Create a script to download websites while learning few Linux commands — for beginners.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Eric Hansen

Eric Hansen

More from Medium

Identifiers in Python

Python Virtual Environments

Using Python to generate an Anki deck

Using the U.S. Census Bureau API with Python