I can calculate the motion of heavenly bodies but not the madness of people. -Isaac Newton

## Data Munging with In-Memory SQLite Database

In-memory database is a powerful functionality in SQLite. Because the whole in-memory database is saved in memory instead of hard drive, it allows a faster access to the data. When used properly with python, it can become a handy tool to efficiently accomplish complex operations in data munging that can’t be easily done with generic python codes.

In the demonstrate below, I will show how to input lists into tables in a in-memory database, merge 2 tables, and then fetch data from the resulted table.

```acct = [['id1', '123-456-7890'], ['id2', '234-567-8901'],
['id3', '999-999-9999']]

hist = [['id1', 'deposited', 100], ['id1', 'withdraw', 40],
['id2', 'withdraw', 15], ['id1', 'withdraw', 25],
['id2',  'deposited', 30], ['id4',  'deposited', 30]]

import sqlite3

con = sqlite3.connect(":memory:")

with con:
cur = con.cursor()

cur.execute("create table tbl_acct (id text, phone text)")
cur.executemany("insert into tbl_acct values(?, ?)", acct)

cur.execute("create table tbl_hist (id text, trans text, amount real)")
cur.executemany("insert into tbl_hist values(?, ?, ?)", hist)

cur.execute("drop table if exists tbl_join")
cur.execute("create table tbl_join as select a.*, b.phone from tbl_hist as a \
inner join tbl_acct as b on a.id = b.id order by a.id")

cur.execute("select * from tbl_join")
for i in cur:
print i

cur.close()

con.close

# output:
#(u'id1', u'deposited', 100.0, u'123-456-7890')
#(u'id1', u'withdraw', 40.0, u'123-456-7890')
#(u'id1', u'withdraw', 25.0, u'123-456-7890')
#(u'id2', u'withdraw', 15.0, u'234-567-8901')
#(u'id2', u'deposited', 30.0, u'234-567-8901')
```