[sqlalchemy] Re: interface error with Decimal(0) in where clause
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',
[sqlalchemy] Re: interface error with Decimal(0) in where clause
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
[sqlalchemy] Re: interface error with Decimal(0) in where clause
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
[sqlalchemy] Re: interface error with Decimal(0) in where clause
decimals.. u can use pickling? slower, yes. On Friday 24 August 2007 10:37:53 Florent Aide 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. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: interface error with Decimal(0) in where clause
Hi, 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. There is one other option, on Windows, the SA 0.4 beta supports Microsoft Access. I believe it works on all Windows systems (i.e. not just ones that have Access installed). Paul --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: interface error with Decimal(0) in where clause
i am storing only accounting amounts so i do care... long time ago there was no decimals easily available, so we used a fixed-point arithmetic (over integers) instead of decimals. either way, u cant store 1/3, and i dont think any db supports fractions. On Friday 24 August 2007 12:17:39 Florent Aide wrote: How would you do something like this then: session.query.(LedgerLine).query(LedgerLine.base_amount.between(dec imal1, decimal2)) the between() won't work since sqlite won't be able to compare your pickled amounts. Pickling cannot be an option in all cases particularly when you are storing amounts for accounting books... Florent. On 8/24/07, svilen [EMAIL PROTECTED] wrote: decimals.. u can use pickling? slower, yes. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: interface error with Decimal(0) in where clause
How would you do something like this then: session.query.(LedgerLine).query(LedgerLine.base_amount.between(decimal1, decimal2)) the between() won't work since sqlite won't be able to compare your pickled amounts. Pickling cannot be an option in all cases particularly when you are storing amounts for accounting books... Florent. On 8/24/07, svilen [EMAIL PROTECTED] wrote: decimals.. u can use pickling? slower, yes. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
