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 [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
-~----------~----~----~----~------~----~------~--~---
