the "convert to Decimal" thing is a new feature for Numeric types in
version 0.4. If you wish to use Python floats, you can either use
the Float type, or specify "asdecimal=False" to the constructor of
any Numeric type.
On Aug 27, 2007, at 12:44 AM, cfriedalek wrote:
>
> 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 [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
-~----------~----~----~----~------~----~------~--~---
