Hi, As far as I know, sqlite does not allow you to store decimal objects, only floats. Which really is not the same. If you really need decimals (ie: accounting books anyone ?) then you should consider using firebird which is the only other database engine supported by SA that is embeddable in a python application without the need of and external server.
If someone has a way to accurately manipulate floats with the same precision as decimals I would gladly hear from it because for the moment I just banned sqlite from my dbengine choices for this particular reason :( Regards, Florent. On 8/22/07, cfriedalek <[EMAIL PROTECTED]> wrote: > > 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 sqlalchemy@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---