[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 -~--~~~~--~~--~--~---
[sqlalchemy] One To Many Polymorphic Association.
##
from sqlalchemy import *
meta = BoundMetaData('sqlite://', echo=False)
# Parents table.
parents = Table('parents', meta,
Column(id, Integer, primary_key=True),
Column(data, String(50), nullable=False)
)
# Children_1 Table.
children_1 = Table('children_1', meta,
Column(id, Integer, primary_key=True),
Column(data, String(50), nullable=False)
)
# Children_2 Table.
children_2 = Table('children_2', meta,
Column(id, Integer, primary_key=True),
Column(data, String(50))
)
# Association Table.
# This is a generic table which can relate anything to parent.
assoc = Table('assoc', meta,
# parents.c.id
Column(parent_id, Integer, ForeignKey(parents.c.id)),
# associate's id either children_1.c.id or children_2.c.id or any
other child.
Column(assoc_id, Integer),
# Which can be either 'child_1' or 'child_2' for now (can be used for
extending children
# type, decides which table to look in.
Column(assoc_type, String(20))
)
###
I am a novice with respect to sqlalchemy may be RDBMS as well.
How would you like to work on this scenario to achieve backwards
cascading (may not be the right word) which means when one deletes
one specific child from children_1 table (for example), there should
not be any
association entry, which associates that child to the parent, in the
association table as well?
--~--~-~--~~~---~--~~
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: One To Many Polymorphic Association.
You might be interested by: http://techspot.zzzeek.org/?p=13
(also in the examples directory of SQLAlchemy)
On 8/24/07, praddy [EMAIL PROTECTED] wrote:
##
from sqlalchemy import *
meta = BoundMetaData('sqlite://', echo=False)
# Parents table.
parents = Table('parents', meta,
Column(id, Integer, primary_key=True),
Column(data, String(50), nullable=False)
)
# Children_1 Table.
children_1 = Table('children_1', meta,
Column(id, Integer, primary_key=True),
Column(data, String(50), nullable=False)
)
# Children_2 Table.
children_2 = Table('children_2', meta,
Column(id, Integer, primary_key=True),
Column(data, String(50))
)
# Association Table.
# This is a generic table which can relate anything to parent.
assoc = Table('assoc', meta,
# parents.c.id
Column(parent_id, Integer, ForeignKey(parents.c.id)),
# associate's id either children_1.c.id or children_2.c.id or any
other child.
Column(assoc_id, Integer),
# Which can be either 'child_1' or 'child_2' for now (can be used for
extending children
# type, decides which table to look in.
Column(assoc_type, String(20))
)
###
I am a novice with respect to sqlalchemy may be RDBMS as well.
How would you like to work on this scenario to achieve backwards
cascading (may not be the right word) which means when one deletes
one specific child from children_1 table (for example), there should
not be any
association entry, which associates that child to the parent, in the
association table as well?
--
Gaëtan de Menten
http://openhex.org
--~--~-~--~~~---~--~~
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] Generative group_by query specify columns
What is the syntax to specify the columns in a generative query? q = session.query(Foo) q = q.group_by([foo_table.c.description]) for currow in q: print currow This query selects all the columns in the Foo table, wondering how to specify the columns to select. Thanks, Curtis --~--~-~--~~~---~--~~ 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: One To Many Polymorphic Association.
On Friday 24 August 2007 19:20:00 Gaetan de Menten wrote:
You might be interested by: http://techspot.zzzeek.org/?p=13
(also in the examples directory of SQLAlchemy)
On 8/24/07, praddy [EMAIL PROTECTED] wrote:
##
from sqlalchemy import *
meta = BoundMetaData('sqlite://', echo=False)
# Parents table.
parents = Table('parents', meta,
Column(id, Integer, primary_key=True),
Column(data, String(50), nullable=False)
)
# Children_1 Table.
children_1 = Table('children_1', meta,
Column(id, Integer, primary_key=True),
Column(data, String(50), nullable=False)
)
# Children_2 Table.
children_2 = Table('children_2', meta,
Column(id, Integer, primary_key=True),
Column(data, String(50))
)
# Association Table.
# This is a generic table which can relate anything to parent.
assoc = Table('assoc', meta,
# parents.c.id
Column(parent_id, Integer, ForeignKey(parents.c.id)),
# associate's id either children_1.c.id or children_2.c.id or any
other child.
Column(assoc_id, Integer),
# Which can be either 'child_1' or 'child_2' for now (can be used
for extending children
# type, decides which table to look in.
Column(assoc_type, String(20))
)
###
I am a novice with respect to sqlalchemy may be RDBMS as well.
How would you like to work on this scenario to achieve backwards
cascading (may not be the right word) which means when one deletes
one specific child from children_1 table (for example), there should
not be any
association entry, which associates that child to the parent, in the
association table as well?
Thanks for the reply. I have already gone through that article, but was unable
to figure out the solution. Anyways, I will give it one more try. Please note
that this scenario is already there and I can't make any changes to the
tables at all.
Thanks
- Pradeep Jindal
--~--~-~--~~~---~--~~
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] Problem Mapping table/relationship to a dictionary.
Hi,
I m trying to write a way of storing a 2 level dictionary in a database.
class Foo(object):
def __init__(self):
self.a = {}
def __iadd__(self, i):
if i not in self.a:
self.a[i] = {}
return self
def keys(self):
for i in self.a.keys():
yield i
def __getitem__(self, k):
return self.a[k]
def save(self):
# saves the object in the database
f = Foo()
f += Node1
f[Node1][Node2] = edge
THe idea is that its a self-referential join (node1 - node2 ) with
association object (edge), ie. node1 has many edges. one edge has has
node1 and 1 node 2.
IS there a pattern in sqlalchemy for doing this sort of thing, I
noticed in the new version of SQLalchemy you can map to sets, but I m
a little clueless where to start.
Any help muchly appreciated.
Nathan
--~--~-~--~~~---~--~~
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: sql executemany and postgresql - probably bug
On Aug 23, 2007, at 11:18 AM, che wrote:
Hi,
On 23 Авг, 17:47, Michael Bayer [EMAIL PROTECTED] wrote:
dont compile() the insert statement yourself here; since you are only
executing it once, theres nothing to be gained by manually compiling
first.
this was the minimal code demonstrating the issue.
i planned to do this many times for bulk insert into the table in
database-independent way.
Its also the source of the error. the issue is that when the
Insert is compiled with no values clause, it produces column
entries for all three columns; but youre only sending two columns in
your argument list. this behavior is the same in 0.3.
seems there is some other prob too:
- in 0.3 it is issued 2 statements:
select nextval('Manager_id_seq')
and then:
INSERT INTO Manager (duties, name, id) VALUES (%(duties)s, %(name)s,
%(id)s)
with the ids got from the db and the other parameters.
- in 0.4 it is issued only the last insert with all ids None which
leads to the error
i cant reply to this issue for another week since im out of town. as
a workaround, placing autoincrement=False on your primary key
column for now may resolve the issue.
--~--~-~--~~~---~--~~
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: Join in where and using .join()
On Aug 24, 2007, at 1:26 AM, Arun Kumar PG wrote:
Guys,
Quick clarification:
If we have two tables A and B with relationship keys 'XYZ' in both
(B references A) then which is faster:
1) session.query(A).select_by(*[A.c.XYZ == B.c.XYZ])
or
2) session.query(A, B).join('XYZ')
2 should be faster as 1 may require more row scans ?
assuming one is select from a, b and the other is select from a join
b, theyre equivalent.
Also, the below one is returning multiple records when it should
return one (may be join is not happening correctly - anything
missing ?.)
turn on SQL echoing and see what its doing.
--~--~-~--~~~---~--~~
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: Generative group_by query specify columns
On Aug 24, 2007, at 10:43 AM, Curtis Scheer wrote: What is the syntax to specify the columns in a generative query? q = session.query(Foo) q = q.group_by([foo_table.c.description]) for currow in q: print currow This query selects all the columns in the Foo table, wondering how to specify the columns to select. query(Foo) will always select all columns that correspond to a Foo object. for individual columns, use a SQL expression instead of a Query, i.e. select([foo_table.c.cola, foo_table.c.colb]).group_by (...).where(...).etc() . --~--~-~--~~~---~--~~ 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: Problem Mapping table/relationship to a dictionary.
On Aug 24, 2007, at 7:41 PM, Nathan Harmston wrote:
Hi,
I m trying to write a way of storing a 2 level dictionary in a
database.
class Foo(object):
def __init__(self):
self.a = {}
def __iadd__(self, i):
if i not in self.a:
self.a[i] = {}
return self
def keys(self):
for i in self.a.keys():
yield i
def __getitem__(self, k):
return self.a[k]
def save(self):
# saves the object in the database
f = Foo()
f += Node1
f[Node1][Node2] = edge
THe idea is that its a self-referential join (node1 - node2 ) with
association object (edge), ie. node1 has many edges. one edge has has
node1 and 1 node 2.
IS there a pattern in sqlalchemy for doing this sort of thing, I
noticed in the new version of SQLalchemy you can map to sets, but I m
a little clueless where to start.
Any help muchly appreciated.
the example examples/adjacencytree/basic_tree.py illustrates a self-
referential Node class which contains a map of child nodes. For a
pattern dealing with edges in the middle, theres an example
eaxmples/graph/graph.py .
--~--~-~--~~~---~--~~
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] why this Query API?
hi, I'm rather new to SQLAlchemy, using version 0.3.x right now. I would like to know the reason why Query.filter() returns another Query object instead of aplying in place, if there is one. an answer to this would help me to understand better this ORM. --~--~-~--~~~---~--~~ 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: why this Query API?
Search the discussion group archive for the phrase (in quotes) in place and take a look at some of the related discussions from late June. On Aug 23, 2:10 pm, Marcos Dione [EMAIL PROTECTED] wrote: hi, I'm rather new to SQLAlchemy, using version 0.3.x right now. I would like to know the reason why Query.filter() returns another Query object instead of aplying in place, if there is one. an answer to this would help me to understand better this ORM. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
