def sqlquery(sqlcmd="select * from prices", path=cu.fixpath('data/sqlite/histdatadb')):
import sqlite3
conn = sqlite3.connect(path)
try:
cur = conn.cursor()
for row in cur.execute(sqlcmd):
yield row
finally:
conn.close()
def sqlpopulatedata(files=cu.fixpath('data/csv/*.csv'), path=cu.fixpath('data/sqlite/histdatadb'), table='test', **kwargs):
import sqlite3
conn = sqlite3.connect(path)
try:
cur = conn.cursor()
def insert(data, table, verbose=False):
date = float(data[0])
sym = data[1]
open_ = float(data[2])
high = float(data[3])
low = float(data[4])
close = float(data[5])
volume = float(data[6])
adjclose = float(data[7])
insert = """
INSERT INTO %(table)s (Date, Sym, Open, High, Low, Close, Volume, AdjClose)
VALUES ('%(date)s', '%(sym)s', %(open_)s, %(high)s, %(low)s, %(close)s,
%(volume)s, %(adjclose)s)
""" % vars()
if verbose:
print insert
cur.execute(insert)
for line in readfiles(files):
insert(line, table, **kwargs)
conn.commit()
finally:
conn.close()
def sqlcreatetable(path=cu.fixpath('data/sqlite/histdatadb'), tablename='test'):
import sqlite3
conn = sqlite3.connect(path)
cur = conn.cursor()
cur.execute("""DROP TABLE IF EXISTS %(tablename)s""" % vars())
cur.execute("""CREATE TABLE %(tablename)s (Date REAL
,Sym TEXT
,Open REAL
,High REAL
,Low REAL
,Close REAL
,Volume REAL
,AdjClose REAL)""" % vars())
cur.execute("""CREATE INDEX dateidx_%(tablename)s ON %(tablename)s (Date)""" % vars())
cur.execute("""CREATE INDEX symidx_%(tablename)s ON %(tablename)s (Sym)""" % vars())
conn.commit()
conn.close()