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]