I have two sqlite databases with the same table structure. I want to combine them. I want to insert entries from the second into the first if they don't already exist in the first. So I wrote this small script (probably not the optimal way but I'm on the steep side of the learning curve ... going up I hope.) Problem is I get an interface error for a where clause when it includes a Decimal("0") entry. Seems like a bug but I hesitate to submit a ticket when I'm so green.

Here's the code and database as sql. Run as combine_databases.py db1 db2 . If db1 and db2 are the same there should be a bunch of skipped entry messages. Note I hacked the code at line 37 to get it to run for the case of a Decimal("0") comparison in the where clase.

line 37 if trloc == 0: trloc = float(trloc) # hack to make this work

So is this s bug or user error?

cf

import sys import sqlalchemy as sa import sqlalchemy.orm as orm

def update_db(table, temp, shape, sidx, nc, br, bi, axloc, trloc): try: keys = ['temp', 'shape', 'sidx','nc', 'br', 'bi', 'axloc','trloc'] vals = [temp, shape, sidx, nc, br, bi, axloc, trloc] dic = dict(zip(keys, vals)) i = table.insert().execute(dic) except: print "ERROR: failed to store data ", dic, "\n", temp, shape, sidx, nc,\

if len(sys.argv) <= 2: sys.exit("Usage: supply two or more databases: 2nd ... inserted to first")

dbs = sys.argv[1:] engines = [sa.create_engine(''.join(('sqlite:///', db))) for db in dbs] metadatas = [sa.MetaData(engine) for engine in engines] tables = [sa.Table('temperatures', md, autoload=True) for md in metadatas]

import pdb;pdb.set_trace()

TABLE = tables[0] ENGINE = engines[0] for table, engine in zip(tables[1:], engines[1:]): query = table.select() for row in engine.execute(query): temp = row[table.c.temp] shape = row[table.c.shape] sidx = row[table.c.sidx] nc = row[table.c.nc] br = row[table.c.br] bi = row[table.c.bi] axloc = row[table.c.axloc] trloc = row[table.c.trloc] #if trloc == 0: trloc = float(trloc) # hack to make this work QUERY = TABLE.select().where(sa.and_( TABLE.c.shape == shape, TABLE.c.sidx == sidx, TABLE.c.nc == nc, TABLE.c.br == br, TABLE.c.bi == bi, TABLE.c.axloc == axloc, TABLE.c.trloc == trloc)) for ROW in ENGINE.execute(QUERY): if ROW: print ".....Skipped ", row else: update_db(TABLE, temp, shape, sidx, nc, br, bi, axloc, trloc) print "Added ...... ", row

BEGIN TRANSACTION; CREATE TABLE temperatures (id INTEGER PRIMARY KEY, temp NUMERIC, shape VARCHAR(10), sidx INTEGER, nc INTEGER, br NUMERIC, bi NUMERIC, axloc NUMERIC, trloc NUMERIC); INSERT INTO temperatures VALUES(1,-1.73316368036707e-05,'circle', 10,2000,NULL,NULL,1.0e-05,0); INSERT INTO temperatures VALUES(2,4.60309299836984e-06,'circle', 10,2000,NULL,NULL,1.0e-05,0.01); INSERT INTO temperatures VALUES(3,-2.76801870080499e-06,'circle', 10,2000,NULL,NULL,1.0e-05,0.02); INSERT INTO temperatures VALUES(4,1.77485003202532e-06,'circle', 10,2000,NULL,NULL,1.0e-05,0.03); INSERT INTO temperatures VALUES(5,-1.08068601149679e-06,'circle', 10,2000,NULL,NULL,1.0e-05,0.04); INSERT INTO temperatures VALUES(6,5.37872557002763e-07,'circle', 10,2000,NULL,NULL,1.0e-05,0.05); INSERT INTO temperatures VALUES(7,-8.82840026334908e-08,'circle', 10,2000,NULL,NULL,1.0e-05,0.06); INSERT INTO temperatures VALUES(8,-2.95596726045961e-07,'circle', 10,2000,NULL,NULL,1.0e-05,0.07); INSERT INTO temperatures VALUES(9,6.28273021881235e-07,'circle', 10,2000,NULL,NULL,1.0e-05,0.08); INSERT INTO temperatures VALUES(10,-9.17917407031199e-07,'circle', 10,2000,NULL,NULL,1.0e-05,0.09); INSERT INTO temperatures VALUES(11,1.16934196740049e-06,'circle', 10,2000,NULL,NULL,1.0e-05,0.1); COMMIT;