# coding: utf-8

# In[2]:

import sqlite3 as db
con = db.connect('test.sqlite')
cur = con.cursor()
sql = 'SELECT SQLITE_VERSION()'
cur.execute(sql)
data = cur.fetchone()
print(data)
con.close()


# In[3]:

with db.connect('test.sqlite') as con:
    cur = con.cursor()
    sql = 'SELECT SQLITE_VERSION()'
    cur.execute(sql)
    data = cur.fetchone()
    print(data)
    


# In[3]:

with db.connect("test.sqlite") as con: 
    cur = con.cursor() 
    cur.execute("DROP TABLE IF EXISTS PhoneAddress") 
    cur.execute("CREATE TABLE PhoneAddress(phone CHAR(10) PRIMARY KEY, address TEXT, name TEXT unique, age INT NOT NULL)") 
    cur.execute("INSERT INTO PhoneAddress VALUES('0912173381','United State','Jhon Doe',53)") 
    cur.execute("INSERT INTO PhoneAddress(phone, address, name, age) VALUES('0928375018','Tokyo Japan','MuMu Cat',6)") 
    cur.execute("INSERT INTO PhoneAddress VALUES('0957209108','Taipei','Richard',29)") 
    cur.execute('SELECT * FROM PhoneAddress')
    data = cur.fetchone()
    print(data[0], data[1])
    print('============================')
    rows = cur.fetchall()
    for item in rows:
        print(item[0], item[1])


# In[4]:

import pandas
employee = [{'name':'Mary', 'age':23 , 'gender': 'F'},{'name':'John', 'age':33 , 'gender': 'M'}]
df = pandas.DataFrame(employee)
df


# In[5]:

with db.connect('test.sqlite') as con:
    df.to_sql(name = 'employee', con = con, if_exists='replace', index = None)