def test_multi_table():
fn1 = _rand_db_filename()
fn2 = _rand_db_filename()
sql.makedb(filename=fn1,
lists=list(zip([0, 1], ['a', 'b'], [1.0, 2.0])),
colnames=['idx', 'c1', 'c2'],
mode='w',
table='tab1')
sql.makedb(filename=fn2,
lists=list(zip([0, 1, 2], ['c', 'd', 'e'], [3.0, 4.0, 5.0])),
colnames=['idx', 'c1', 'c2'],
mode='w',
table='tab1')
# in memory db
db = sql.SQLiteDB(':memory:')
db.executescript("attach '%s' as db1; attach '%s' as db2" % (fn1, fn2))
cmd = """
select
db1.tab1.idx,
db1.tab1.c1 as db1_c1,
db2.tab1.c1 as db2_c1,
db2.tab1.c2 as db2_c2
from
db1.tab1,db2.tab1
where
db1.tab1.idx==db2.tab1.idx;
"""
dct = db.get_dict(cmd)
assert dct['idx'] == [0, 1]
assert dct['db1_c1'] == ['a', 'b']
assert dct['db2_c1'] == ['c', 'd']
assert dct['db2_c2'] == [3.0, 4.0]
# multi table
db = sql.SQLiteDB(fn1)
header1 = [('idx', 'INTEGER'), ('c1', 'TEXT'), ('c2', 'REAL')]
assert db.get_header(table='tab1') == header1
header2 = [('x', 'REAL'), ('y', 'TEXT')]
db.create_table(header2, table='tab2')
db.execute("insert into tab2 ('x', 'y') values (1.0, 'a')")
db.execute("insert into tab2 ('x', 'y') values (2.0, 'b')")
db.attach_column(col='z', values=[88, 99], table='tab2')
header2 += [('z', 'INTEGER')]
assert db.get_header(table='tab2') == header2
assert db.has_table('tab2')
assert db.has_column('idx', table='tab1')
assert db.has_column('x', table='tab2')
assert db.get_max_rowid('tab2') == 2
db.set_table('tab1')
assert db.has_column('idx')
assert db.get_header() == header1
assert db.get_max_rowid() == 2
db.set_table('tab2')
assert db.get_header() == header2
assert db.has_column('x')
assert db.get_max_rowid() == 2
def test_multi_table():
fn1 = _rand_db_filename()
fn2 = _rand_db_filename()
sql.makedb(filename=fn1, lists=zip([0,1],['a','b'],[1.0,2.0]),
colnames=['idx', 'c1', 'c2'], mode='w', table='tab1')
sql.makedb(filename=fn2, lists=zip([0,1,2],['c','d','e'],[3.0,4.0,5.0]),
colnames=['idx', 'c1', 'c2'], mode='w', table='tab1')
# in memory db
db = sql.SQLiteDB(':memory:')
db.executescript("attach '%s' as db1; attach '%s' as db2" %(fn1, fn2))
cmd = """
select
db1.tab1.idx,
db1.tab1.c1 as db1_c1,
db2.tab1.c1 as db2_c1,
db2.tab1.c2 as db2_c2
from
db1.tab1,db2.tab1
where
db1.tab1.idx==db2.tab1.idx;
"""
dct = db.get_dict(cmd)
assert dct['idx'] == [0,1]
assert dct['db1_c1'] == ['a','b']
assert dct['db2_c1'] == ['c','d']
assert dct['db2_c2'] == [3.0,4.0]
# multi table
db = sql.SQLiteDB(fn1)
header1 = [('idx','INTEGER'),
('c1', 'TEXT'),
('c2', 'REAL')]
assert db.get_header(table='tab1') == header1
header2 = [('x','REAL'), ('y', 'TEXT')]
db.create_table(header2, table='tab2')
db.execute("insert into tab2 ('x', 'y') values (1.0, 'a')")
db.execute("insert into tab2 ('x', 'y') values (2.0, 'b')")
db.attach_column(col='z', values=[88,99], table='tab2')
header2 += [('z', 'INTEGER')]
assert db.get_header(table='tab2') == header2
assert db.has_table('tab2')
assert db.has_column('idx', table='tab1')
assert db.has_column('x', table='tab2')
assert db.get_max_rowid('tab2') == 2
db.set_table('tab1')
assert db.has_column('idx')
assert db.get_header() == header1
assert db.get_max_rowid() == 2
db.set_table('tab2')
assert db.get_header() == header2
assert db.has_column('x')
assert db.get_max_rowid() == 2
def test_attach_fill_column():
lists = list(zip(['a', 'b'] * 2, [1.0, 2.0] * 2))
colnames = ['foo', 'bar']
dbfn = pj(testdir, 'test3.db')
if os.path.exists(dbfn):
os.remove(dbfn)
db = sql.makedb(dbfn, lists, colnames, close=False)
# attach_column, fill_column
db.attach_column('baz', values=[1, 2, 3, 4, 5, 6], extend=False, start=1)
for col, typ in db.get_header():
if col == 'baz':
assert typ == 'INTEGER'
assert db.get_max_rowid() == 4
assert db.get_list1d('select baz from test3') == [1, 2, 3, 4]
# need `extend` b/c up to now, table has 4 rows
db.fill_column('baz', values=[5, 6], extend=True, start=5)
assert db.get_max_rowid() == 6
assert db.get_list1d('select baz from test3') == [1, 2, 3, 4, 5, 6]
assert db.get_list1d(
"select foo from test3") == ['a', 'b'] * 2 + [None] * 2
assert db.get_list1d(
"select bar from test3") == [1.0, 2.0] * 2 + [None] * 2
# `extend` kwd not needed b/c table already has 6 rows
db.attach_column('baz2', values=[1, 2, 3, 4, 5, 6], start=1)
assert db.get_list1d('select baz2 from test3') == [1, 2, 3, 4, 5, 6]
db.fill_column('baz2', values=[1, 4, 9, 16], overwrite=True, start=1)
assert db.get_list1d('select baz2 from test3') == [1, 4, 9, 16, 5, 6]
# attach_column(..., overwrite=True) = fill_column()
db.attach_column('baz2',
values=[2, 4, 6, 8, 10, 12],
overwrite=True,
start=1)
assert db.get_list1d('select baz2 from test3') == [2, 4, 6, 8, 10, 12]
def test_attach_fill_column():
lists = zip(['a','b']*2, [1.0,2.0]*2)
colnames = ['foo','bar']
dbfn = pj(testdir, 'test3.db')
if os.path.exists(dbfn):
os.remove(dbfn)
db = sql.makedb(dbfn, lists, colnames, close=False)
# attach_column, fill_column
db.attach_column('baz', values=[1,2,3,4,5,6],
extend=False, start=1)
for col, typ in db.get_header():
if col == 'baz':
assert typ == 'INTEGER'
assert db.get_max_rowid() == 4
assert db.get_list1d('select baz from test3') == [1,2,3,4]
# need `extend` b/c up to now, table has 4 rows
db.fill_column('baz', values=[5,6],
extend=True, start=5)
assert db.get_max_rowid() == 6
assert db.get_list1d('select baz from test3') == [1,2,3,4,5,6]
assert db.get_list1d("select foo from test3") == [u'a', u'b']*2 + [None]*2
assert db.get_list1d("select bar from test3") == [1.0, 2.0]*2 + [None]*2
# `extend` kwd not needed b/c table already has 6 rows
db.attach_column('baz2', values=[1,2,3,4,5,6],
start=1)
assert db.get_list1d('select baz2 from test3') == [1,2,3,4,5,6]
db.fill_column('baz2', values=[1,4,9,16],
overwrite=True, start=1)
assert db.get_list1d('select baz2 from test3') == [1,4,9,16,5,6]
# attach_column(..., overwrite=True) = fill_column()
db.attach_column('baz2', values=[2,4,6,8,10,12],
overwrite=True, start=1)
assert db.get_list1d('select baz2 from test3') == [2,4,6,8,10,12]
def test_makedb():
lists = list(zip(['a', 'b'], [1.0, 2.0]))
colnames = ['foo', 'bar']
dbfn = _rand_db_filename()
table = dbfn.split('/')[-1].replace('.db', '')
sql.makedb(filename=dbfn, lists=lists, colnames=colnames, mode='w')
db = sql.SQLiteDB(dbfn, table=table)
dct = db.get_dict("select * from %s" % table)
assert dct['foo'] == ['a', 'b']
assert dct['bar'] == [1.0, 2.0]
sql.makedb(filename=dbfn, lists=lists, colnames=colnames, mode='a')
db = sql.SQLiteDB(dbfn, table=table)
dct = db.get_dict("select * from %s" % table)
assert dct['foo'] == ['a', 'b'] * 2
assert dct['bar'] == [1.0, 2.0] * 2
# makedb, set table name, close=False, return open db
dbfn = _rand_db_filename()
table = dbfn.split('/')[-1].replace('.db', '')
db = sql.makedb(dbfn, lists, colnames, mode='w', table=table, close=False)
dct = db.get_dict("select * from %s" % table)
assert dct['foo'] == ['a', 'b']
assert dct['bar'] == [1.0, 2.0]
def test_makedb():
lists = zip(['a','b'],[1.0,2.0])
colnames = ['foo', 'bar']
dbfn = _rand_db_filename()
table = dbfn.split('/')[-1].replace('.db','')
sql.makedb(filename=dbfn, lists=lists, colnames=colnames, mode='w')
db = sql.SQLiteDB(dbfn, table=table)
dct = db.get_dict("select * from %s" %table)
assert dct['foo'] == [u'a', u'b']
assert dct['bar'] == [1.0, 2.0]
sql.makedb(filename=dbfn, lists=lists, colnames=colnames, mode='a')
db = sql.SQLiteDB(dbfn, table=table)
dct = db.get_dict("select * from %s" %table)
assert dct['foo'] == [u'a', u'b']*2
assert dct['bar'] == [1.0, 2.0]*2
# makedb, set table name, close=False, return open db
dbfn = _rand_db_filename()
table = dbfn.split('/')[-1].replace('.db','')
db = sql.makedb(dbfn, lists, colnames, mode='w',
table=table, close=False)
dct = db.get_dict("select * from %s" %table)
assert dct['foo'] == [u'a', u'b']
assert dct['bar'] == [1.0, 2.0]
