Thanks. I didn't realize sqlite didn't support Decimal. As shown
above, I haven't setup my table to require decimal support, only
numeric. But SQLAlchemy metadata reports that the numeric type is
decimal after I autoload the table. This seems to be a default of
SQLAlchemy. However I can't find out how to change this default, to
say, Float. Can anyone help?
btw I am interested in Decimal support for another case where I use an
arbitrary precision library (GMP) so this thread has helped me out
with that. Thanks.
On Aug 24, 5:37 pm, "Florent Aide" <[EMAIL PROTECTED]> wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
