Moving fiscal data from a pandas dataframe to a sqlite local database

This post includes code from Scalable-Data-Analysis-in-Python-with-Dask and coiled-examples.

import numpy as np
import pandas as pd

df = pd.read_csv('df_panel_fix.csv')

df.columns

Index(['Unnamed: 0', 'province', 'specific', 'general', 'year', 'gdp', 'fdi',
       'rnr', 'rr', 'i', 'fr', 'reg', 'it'],
      dtype='object')

df_subset = df[["year", "reg", "province", "gdp", "fdi", 'it',"specific"]]
df_subset

	year	reg	province	gdp	fdi	it	specific
0	1996	East China	Anhui	2093.30	50661	631930	147002.0
1	1997	East China	Anhui	2347.32	43443	657860	151981.0
2	1998	East China	Anhui	2542.96	27673	889463	174930.0
3	1999	East China	Anhui	2712.34	26131	1227364	285324.0
4	2000	East China	Anhui	2902.09	31847	1499110	195580.0
...	...	...	...	...	...	...	...
355	2003	East China	Zhejiang	9705.02	498055	2261631	391292.0
356	2004	East China	Zhejiang	11648.70	668128	3162299	656175.0
357	2005	East China	Zhejiang	13417.68	772000	2370200	656175.0
358	2006	East China	Zhejiang	15718.47	888935	2553268	1017303.0
359	2007	East China	Zhejiang	18753.73	1036576	2939778	844647.0

360 rows × 7 columns

df_subset.columns = ["year", "region", "province", "gdp", "fdi", 'it',"specific"]

df_subset

	year	region	province	gdp	fdi	it	specific
0	1996	East China	Anhui	2093.30	50661	631930	147002.0
1	1997	East China	Anhui	2347.32	43443	657860	151981.0
2	1998	East China	Anhui	2542.96	27673	889463	174930.0
3	1999	East China	Anhui	2712.34	26131	1227364	285324.0
4	2000	East China	Anhui	2902.09	31847	1499110	195580.0
...	...	...	...	...	...	...	...
355	2003	East China	Zhejiang	9705.02	498055	2261631	391292.0
356	2004	East China	Zhejiang	11648.70	668128	3162299	656175.0
357	2005	East China	Zhejiang	13417.68	772000	2370200	656175.0
358	2006	East China	Zhejiang	15718.47	888935	2553268	1017303.0
359	2007	East China	Zhejiang	18753.73	1036576	2939778	844647.0

360 rows × 7 columns

import sqlalchemy as db
from sqlalchemy import create_engine
import sqlite3
import pandas as pd

engine = db.create_engine('sqlite:///fiscal_data.db')
connection = engine.connect()
metadata = db.MetaData()

fiscal_data = db.Table('fiscal_data', metadata, 
    db.Column('year',db.Integer, nullable=True, index=False),
    db.Column('region',db.String, nullable=True),
    db.Column('province',db.String, nullable=True),
    db.Column('gdp',db.String, nullable=True),
    db.Column('fdi',db.Integer, nullable=True),
    db.Column('it',db.Integer, nullable=True),
    db.Column('specific', db.Integer, nullable=True)
)

metadata.create_all(engine) #Creates the table

fiscal_data

Table('fiscal_data', MetaData(bind=None), Column('year', Integer(), table=<fiscal_data>), Column('region', String(), table=<fiscal_data>), Column('province', String(), table=<fiscal_data>), Column('gdp', String(), table=<fiscal_data>), Column('fdi', Integer(), table=<fiscal_data>), Column('it', Integer(), table=<fiscal_data>), Column('specific', Integer(), table=<fiscal_data>), schema=None)

df_subset.to_sql('fiscal_data', con=engine, if_exists='append', index=False)

engine.execute("SELECT year, region, province, gdp FROM fiscal_data LIMIT 10").fetchall()

[(1996, 'East China', 'Anhui', '2093.3'),
 (1997, 'East China', 'Anhui', '2347.32'),
 (1998, 'East China', 'Anhui', '2542.96'),
 (1999, 'East China', 'Anhui', '2712.34'),
 (2000, 'East China', 'Anhui', '2902.09'),
 (2001, 'East China', 'Anhui', '3246.71'),
 (2002, 'East China', 'Anhui', '3519.72'),
 (2003, 'East China', 'Anhui', '3923.11'),
 (2004, 'East China', 'Anhui', '4759.3'),
 (2005, 'East China', 'Anhui', '5350.17')]

sql = """
SELECT year
, region
, province
, gdp 
FROM fiscal_data
"""

cnxn = connection

df = pd.read_sql(sql, cnxn)

df.tail(30)

	year	region	province	gdp
330	2002	Northwest China	Xinjiang	1612.65
331	2003	Northwest China	Xinjiang	1886.35
332	2004	Northwest China	Xinjiang	2209.09
333	2005	Northwest China	Xinjiang	2604.19
334	2006	Northwest China	Xinjiang	3045.26
335	2007	Northwest China	Xinjiang	3523.16
336	1996	Southwest China	Yunnan	1517.69
337	1997	Southwest China	Yunnan	1676.17
338	1998	Southwest China	Yunnan	1831.33
339	1999	Southwest China	Yunnan	1899.82
340	2000	Southwest China	Yunnan	2011.19
341	2001	Southwest China	Yunnan	2138.31
342	2002	Southwest China	Yunnan	2312.82
343	2003	Southwest China	Yunnan	2556.02
344	2004	Southwest China	Yunnan	3081.91
345	2005	Southwest China	Yunnan	3462.73
346	2006	Southwest China	Yunnan	3988.14
347	2007	Southwest China	Yunnan	4772.52
348	1996	East China	Zhejiang	4188.53
349	1997	East China	Zhejiang	4686.11
350	1998	East China	Zhejiang	5052.62
351	1999	East China	Zhejiang	5443.92
352	2000	East China	Zhejiang	6141.03
353	2001	East China	Zhejiang	6898.34
354	2002	East China	Zhejiang	8003.67
355	2003	East China	Zhejiang	9705.02
356	2004	East China	Zhejiang	11648.7
357	2005	East China	Zhejiang	13417.68
358	2006	East China	Zhejiang	15718.47
359	2007	East China	Zhejiang	18753.73

#http://manpages.ubuntu.com/manpages/precise/man1/sqlite3.1.html

# sqlite3 fiscal_data.db

# create table memos(text, priority INTEGER);
# insert into memos values('example 1', 10);
# insert into memos values('example 2', 100);
# select * from memos;

# sqlite3 -line fiscal_data.db 'select * from memos where priority > 20;'