In [2]:
import csv
import sqlite3
#A script to create and import a .csv into sqlite3

con = sqlite3.connect("testing.db")
cur = con.cursor()
cur.execute("CREATE TABLE data (id INTEGER PRIMARY KEY,first_name varchar(50),"
            "last_name varchar(50),email varchar(50),ip varchar(50),"
            "sex varchar(50),title varchar(50),birth_date varchar(50),address varchar(100)," 
            "city varchar(50),state varchar(50),country varchar(50),zip_postal varchar(20),"
            "phone varchar(50),work_phone varchar(50),mobile_phone varchar(50),alt_phone varchar(50),"
            "language varchar(20),signup_date varchar(20),last_login_date varchar(20),blocked varchar(20),"
            "casino_brand varchar(50),fav_game varchar(50),account_type varchar(50),currency varchar(50),"
            "net_loss float(10,2),first_deposit_date float(10,2),number_of_deposits int(10),last_deposit_date varchar(20),"
            "total_deposit_amount float(10,2),total_bonus_amount float(10,2),player_class varchar(20),address_2 varchar(20),user_name varchar(20),net_rev float(10,2));")

with open('eleeeeeeeee.csv','r') as fin: # `with` statement available in 2.5+
    # csv.DictReader uses first line in file for column headings by default
    dr = csv.DictReader(fin) # comma is default delimiter
    to_db = [(i['first_name'], i['last_name'],i['email'],i['country_name'],i['phone'],i['ip'],i['deposit']) for i in dr]

cur.executemany("INSERT INTO data (first_name, last_name, email,country,phone,ip,total_deposit_amount) VALUES (?, ?,?,?,?,?,?);", to_db)
con.commit()
In [ ]:
# A Script To update the current master database

con = sqlite3.connect("testing.db")
cur = con.cursor()

with open('5ktest.csv','r') as fin: # `with` statement available in 2.5+
    # csv.DictReader uses first line in file for column headings by default
    dr = csv.DictReader(fin) # comma is default delimiter
    to_db = [(i['first_name'], i['last_name'],i['email'],i['country'],i['currency'],i['cash_bonus'],i['vip_level']) for i in dr]

cur.executemany("INSERT INTO data (first_name, last_name, email,country,currency,total_bonus_amount,player_class) VALUES (?, ?,?,?,?,?,?);", to_db)
con.commit()
con.close()
In [18]:
#A Script to query for a specific unique ID
# open existing database
conn = sqlite3.connect('testing.db')
c = conn.cursor()

# print all lines ordered by ID decending.
for row in c.execute('SELECT * FROM data ORDER BY id DESC LIMIT 10'):
    print(row[0],row[1],row[2],row[3],row[11],row[12],row[5],row[6],row[7])
No data shown due to privacy
In [ ]:
#export database to .csv file
import sqlite3
import pandas.io.sql as sql
con = sqlite3.connect('testing.db')
table = sql.read_sql('select * from data', con)
table.to_csv('export.csv')
print("Success")
In [ ]: