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;
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
