[sqlalchemy] How to do aggregates with mapped entity objects?
I have a expenses table with a amount field. I have mapped that table to a Expense entity object. How do I sum up all the amounts using my entity object.? --~--~-~--~~~---~--~~ 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: Spatial data (PostGIS/OGC) with SqlAlchemy
I just wanted to give an update. I have found a solution that works
ok for now but I had to hack a bit. I ended up having to use
psycopg2's ability to override how python classes are converted
to/from a database. psycopg2 seems to have more complete support for
this then SA, so it let me convert to an exact string representation
for my type (ie. GeomFromText('POINT(1 1)') ). I combined this with
the GeoTypes library to represent geometry types and to read them back
from the OGC binary format.
The relevant code looks like this:
import sqlalchemy as sa
import psycopg2.extensions
import logging
pe = psycopg2.extensions
from GeoTypes import (OGGeoTypeFactory, WKBParser,
OGGeometry, OGPoint, OGPolygon, OGLineString)
class PostGisWKBFactory(object):
def __init__(self):
pass
def __call__(self, s=None):
factory = OGGeoTypeFactory()
parser = WKBParser(factory)
parser.parseGeometry(s)
return factory.getGeometry()
class GeometryType(sa.types.TypeEngine):
def __init__(self, SRID, typeName, dimension):
super(GeometryType, self).__init__()
self.mSrid = SRID
self.mType = typeName.upper()
self.mDim = dimension
self.bfact = PostGisWKBFactory()
def __repr__(self):
return %s:%s-%s(%s) % (self.__class__.__name__, self.mType,
self.mDim, self.mSrid)
def get_col_spec(self):
return GEOMETRY
def convert_bind_param(self, value, engine):
# Could be used to make the type _conform_
#return pe.AsIs(str(value))
return value
def convert_result_value(self, value, engine):
geom_obj = self.bfact(binascii.a2b_hex(value))
geom_obj._srid = self.mSrid # set directly
return geom_obj
class GeometryPOINT(GeometryType):
def __init__(self, srid):
super(GeometryPOINT,self).__init__(srid, POINT, 2)
class GeometryLINESTRING(GeometryType):
def __init__(self, srid):
super(GeometryPOINT,self).__init__(srid, LINESTRING, 2)
Then I added the following method to the GeoTypes.Geometry class to
allow it to represent itself to psycopg2 correctly.
# Interface to allow psycopg2 to convert to database automatically
def getquoted(self):
return self.__str__()
def __conform__(self, proto):
# Return ourselves since we have a getquoted method
return self
I still don't see a way to handle this directly with SA, so if anyone
can tell me a way to let SA know exactly how I want the object's value
to appear in the generated SQL statement please let me know so I can
refine my code.
Thanks,
Allen
On 2/25/07, Allen Bierbaum [EMAIL PROTECTED] wrote:
[snip]
When I use this with my table and datamapper code, it looks like
everything is working fine but the generated SQL insert statement
fails with a exception:
sqlalchemy.exceptions.SQLError: (ProgrammingError) parse error -
invalid geometry
'INSERT INTO gis_entity (id, name, pos) VALUES (%(mId)s, %(mName)s,
%(mPos)s)' {'mName': 'New entity', 'mId': 1L, 'mPos':
GeomFromText('POINT(100 100)',-1)}
I know from using sql directly in pgadmin3 that this line works correctly:
insert into gis_entity (id, name, pos) values (2, 'New entity',
GeomFromText('POINT(100 100)', -1));
Does anyone see how this varies from the sql statement issued by SA?
By looking at the postgres log I figured out what was causing the
error, but I still don't know how to fix it.
The problem is that SA considers GeomFromText('POINT(100 100)', -1)
to be a string so it puts it in single quotes when creating the SQL
command to execute. This causes problems because them postgres doesn't
know it could be calling a method instead. I have tried returning an
sqlalchemy.func object but this doesn't work either.
Any ideas?
-Allen
--~--~-~--~~~---~--~~
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] How do I get columns in sqlalchemy group by function
I have the following columns in some of my select columns: group_by_column = sqlalchemy.func.sum(column1) How do I get column1 from the group_by_column object (In other words is there any way to find out what columns the function is going to be applied to?). I did a dir(group_by_column) and saw that it had a field called columns, but that did not have the values I was looking for. Thanks, VJ --~--~-~--~~~---~--~~ 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: How to do aggregates with mapped entity objects?
You don't, it doesn't work that way. You can map the aggregate as a (SA) property of another class, or make it a (Python) property/function of the Expense class. Either way you'll have to create a select object, either for the mapping or a manual query. On 2/27/07, Pradeep [EMAIL PROTECTED] wrote: I have a expenses table with a amount field. I have mapped that table to a Expense entity object. How do I sum up all the amounts using my entity object.? --~--~-~--~~~---~--~~ 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: Spatial data (PostGIS/OGC) with SqlAlchemy
these are all SA bugs/possible inaccuracies in API usage. you should
be able to execute the function as I described below, you should be
able to put the function into an INSERT statement as well. Ill test
these features later today.
On Feb 25, 2007, at 3:43 PM, Allen Bierbaum wrote:
I tried your idea but it doesn't seem to be valid code. Here is the
code I tried:
print Trying with a transaction.
conn = db.connect()
trans = conn.begin()
conn.execute(sa.func.AddGeometryColumn
('','gis_entity','fpos',-1,'LINESTRING',2))
trans.commit()
conn.close()
and here is the exception I get on the execute line:
Try with a transaction.
INFO:sqlalchemy.engine.base.Engine.0x..d0:BEGIN
Traceback (most recent call last):
File gis_type_test.py, line 73, in ?
conn.execute(sa.func.AddGeometryColumn
('','gis_entity','fpos',-1,'LINESTRING',2,
engine=db))
File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py,
line 258, in execute
return Connection.executors[type(object).__mro__[-2]](self,
object, *multiparams, **params)
KeyError: class 'sqlalchemy.sql._CompareMixin'
I am now running into a new problem as well though. I can use my
previous hack to insert the column but I can't add data to it because
I have been unable to call GeomFromText(..) on the insert.
I tried to get this working by defining my own type:
class GeometryType(sa.types.TypeEngine):
def __init__(self, SRID, typeName, dimension):
super(GeometryType, self).__init__()
self.mSrid = SRID
self.mType = typeName.upper()
self.mDim = dimension
def __repr__(self):
return %s:%s-%s(%s) % (self.__class__.__name__, self.mType,
self.mDim, self.mSrid)
def get_col_spec(self):
return GEOMETRY
def convert_bind_param(self, value, engine):
return 'GeomFromText(\'%s\',%s)'%(value, self.mSrid)
def convert_result_value(self, value, engine):
# Not used yet
return value
When I use this with my table and datamapper code, it looks like
everything is working fine but the generated SQL insert statement
fails with a exception:
sqlalchemy.exceptions.SQLError: (ProgrammingError) parse error -
invalid geometry
'INSERT INTO gis_entity (id, name, pos) VALUES (%(mId)s, %(mName)s,
%(mPos)s)' {'mName': 'New entity', 'mId': 1L, 'mPos':
GeomFromText('POINT(100 100)',-1)}
I know from using sql directly in pgadmin3 that this line works
correctly:
insert into gis_entity (id, name, pos) values (2, 'New entity',
GeomFromText('POINT(100 100)', -1));
Does anyone see how this varies from the sql statement issued by SA?
I have stared at it for 20 minutes and I don't see a difference.
Is there any way to see the raw SQL statement sent by SA to postgres?
I have turned up the debug output level to full but I still only can
see the format string and parameters used for making the sql
statement:
INFO:sqlalchemy.engine.base.Engine.0x..50:INSERT INTO gis_entity (id,
name, pos) VALUES (%(mId)s, %(mName)s, %(mPos)s)
INFO:sqlalchemy.engine.base.Engine.0x..50:{'mName': 'New entity',
'mId': 1L, 'mPos': GeomFromText('POINT(100 100)',-1)}
Does anyone see what I am doing wrong here?
Once I get this working, then I have to figure out how to get the data
back out from postgis. What I want to end up with here is an SQL
statement like this:
select id, name, AsText(pos) as pos from gis_entity;
Note that pos is retrieved through a server side function ('AsText')
that unpacks the binary representation from the database into a string
representation. Is there any way to do this with SA so the system
will always create queries in this form whenever it tries to retrieve
the value of pos?
I don't know if this makes a solution easier, but I am using mappers
for all my tables. What I would really like to have is a mapper that
would just automatically know to wrap all references to pos in
INSERT calls with a call to the server-side function 'GeomFromText'
and correspondingly wrap all references to pos in SELECT calls with
a call to the server-side function 'AsText'. Is it possible to do
this at the mapper level? If it is, then that could greatly simplify
everything I am trying to do here.
Thanks for you help.
-Allen
On 2/25/07, Michael Bayer [EMAIL PROTECTED] wrote:
if the function youre calling needs a transaction commit, why dont
you call an explicit transaction ?
conn = engine.connect()
trans = conn.begin()
conn.execute(func.AddGeometryColumn
('','gis_entity','fpos',-1,'LINESTRING',2))
trans.commit()
On Feb 25, 2007, at 11:02 AM, Allen Bierbaum wrote:
I have been pursuing this further on my own and one of the issues I
have run into is how to cleanly add a geometry column to a database.
The posting referenced in the first e-mail [2] talks about doing
this
directly with psycopg2 cursor because the poster could not get it
working with SA. I gave it another try to see if I could get
[sqlalchemy] Auto-joins via FK
Shouldn't SA be able to figure out the following joins without needing the
explicit join params?
I've got to be missing something dumb.
from sqlalchemy import *
metadata = BoundMetaData('sqlite:///:memory:')
ent = Table('entity', metadata,
Column('id', INT, Sequence('entity_id'), primary_key=True,
nullable=False),
Column('nm', VARCHAR(12), nullable=False)
)
loc = Table('location', metadata,
Column('id', INT, Sequence('location_id'),
primary_key=True, nullable=False),
Column('nm', VARCHAR(50))
)
el = Table('el_rel', metadata,
Column('typ', VARCHAR(12), nullable=False),
Column('ident', INT, ForeignKey('entity.id'),
nullable=False),
Column('idloc', INT, ForeignKey('location.id'),
nullable=False)
)
metadata.create_all()
ent.insert().execute({'id':1, 'nm':'Eone'}, {'id':2, 'nm':'Etwo'},
{'id':3, 'nm':'Ethree'})
loc.insert().execute({'id':1, 'nm':'Lone'}, {'id':2, 'nm':'Ltwo'}, {'id':3,
'nm':'Lthree'})
el.insert().execute({'typ':'TEST1', 'ident':1, 'idloc':1}, {'typ':'TEST2',
'ident':2, 'idloc':2})
testcases = [('2-way -ent', [ent.c.nm], None),
('2-way -loc', [loc.c.nm], None),
('3-way -ent+loc', [ent.c.nm, loc.c.nm], None),
('3-way, explicit join', [ent.c.nm, loc.c.nm],
el.join(ent, ent.c.id==el.c.ident).join(loc,
loc.c.id==el.c.idloc))
]
for test_name, test_cols, test_from in testcases:
print
print test_name
q = select(test_cols, el.c.typ == 'TEST1')
if test_from:
q.append_from(test_from)
print str(q)
print q.execute().fetchall()
Thx,
Rick
--~--~-~--~~~---~--~~
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: Spatial data (PostGIS/OGC) with SqlAlchemy
OK, for the function execution, the syntax that works with release
0.3.5 is:
conn = db.connect()
trans = conn.begin()
conn.execute(func.AddGeometryColumn
('','gis_entity','fpos',-1,'LINESTRING',2).select())
trans.commit()
conn.close()
notice it just adds a select().in changeset 2364, you dont need
the select.
inserts with functions are done like this:
# without a connection
table.insert().execute(somecolumn = func.GeometryFunction(arg1, arg2,
arg3...))
# with a connection
conn.execute(table.insert(), somecolumn = func.GeometryFunction(arg1,
arg2, arg3...))
On Feb 27, 2007, at 10:42 AM, Michael Bayer wrote:
these are all SA bugs/possible inaccuracies in API usage. you should
be able to execute the function as I described below, you should be
able to put the function into an INSERT statement as well. Ill test
these features later today.
On Feb 25, 2007, at 3:43 PM, Allen Bierbaum wrote:
I tried your idea but it doesn't seem to be valid code. Here is the
code I tried:
print Trying with a transaction.
conn = db.connect()
trans = conn.begin()
conn.execute(sa.func.AddGeometryColumn
('','gis_entity','fpos',-1,'LINESTRING',2))
trans.commit()
conn.close()
and here is the exception I get on the execute line:
Try with a transaction.
INFO:sqlalchemy.engine.base.Engine.0x..d0:BEGIN
Traceback (most recent call last):
File gis_type_test.py, line 73, in ?
conn.execute(sa.func.AddGeometryColumn
('','gis_entity','fpos',-1,'LINESTRING',2,
engine=db))
File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py,
line 258, in execute
return Connection.executors[type(object).__mro__[-2]](self,
object, *multiparams, **params)
KeyError: class 'sqlalchemy.sql._CompareMixin'
I am now running into a new problem as well though. I can use my
previous hack to insert the column but I can't add data to it because
I have been unable to call GeomFromText(..) on the insert.
I tried to get this working by defining my own type:
class GeometryType(sa.types.TypeEngine):
def __init__(self, SRID, typeName, dimension):
super(GeometryType, self).__init__()
self.mSrid = SRID
self.mType = typeName.upper()
self.mDim = dimension
def __repr__(self):
return %s:%s-%s(%s) % (self.__class__.__name__, self.mType,
self.mDim, self.mSrid)
def get_col_spec(self):
return GEOMETRY
def convert_bind_param(self, value, engine):
return 'GeomFromText(\'%s\',%s)'%(value, self.mSrid)
def convert_result_value(self, value, engine):
# Not used yet
return value
When I use this with my table and datamapper code, it looks like
everything is working fine but the generated SQL insert statement
fails with a exception:
sqlalchemy.exceptions.SQLError: (ProgrammingError) parse error -
invalid geometry
'INSERT INTO gis_entity (id, name, pos) VALUES (%(mId)s, %(mName)s,
%(mPos)s)' {'mName': 'New entity', 'mId': 1L, 'mPos':
GeomFromText('POINT(100 100)',-1)}
I know from using sql directly in pgadmin3 that this line works
correctly:
insert into gis_entity (id, name, pos) values (2, 'New entity',
GeomFromText('POINT(100 100)', -1));
Does anyone see how this varies from the sql statement issued by SA?
I have stared at it for 20 minutes and I don't see a difference.
Is there any way to see the raw SQL statement sent by SA to postgres?
I have turned up the debug output level to full but I still only can
see the format string and parameters used for making the sql
statement:
INFO:sqlalchemy.engine.base.Engine.0x..50:INSERT INTO gis_entity (id,
name, pos) VALUES (%(mId)s, %(mName)s, %(mPos)s)
INFO:sqlalchemy.engine.base.Engine.0x..50:{'mName': 'New entity',
'mId': 1L, 'mPos': GeomFromText('POINT(100 100)',-1)}
Does anyone see what I am doing wrong here?
Once I get this working, then I have to figure out how to get the
data
back out from postgis. What I want to end up with here is an SQL
statement like this:
select id, name, AsText(pos) as pos from gis_entity;
Note that pos is retrieved through a server side function ('AsText')
that unpacks the binary representation from the database into a
string
representation. Is there any way to do this with SA so the system
will always create queries in this form whenever it tries to retrieve
the value of pos?
I don't know if this makes a solution easier, but I am using mappers
for all my tables. What I would really like to have is a mapper that
would just automatically know to wrap all references to pos in
INSERT calls with a call to the server-side function 'GeomFromText'
and correspondingly wrap all references to pos in SELECT calls with
a call to the server-side function 'AsText'. Is it possible to do
this at the mapper level? If it is, then that could greatly simplify
everything I am trying to do here.
Thanks for you help.
-Allen
On 2/25/07, Michael Bayer [EMAIL PROTECTED] wrote:
if the
[sqlalchemy] Re: Auto-joins via FK
On 2/27/07, Rick Morrison [EMAIL PROTECTED] wrote: Shouldn't SA be able to figure out the following joins without needing the explicit join params? no, SA never tries to guess how you want to join things because then you'd have to add a way to override it when it guesses wrong and it's simpler to just make things explicit in the first place. the join function will automatically do FK-based where conditions, that's probably what you want. --~--~-~--~~~---~--~~ 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: Auto-joins via FK
the join() function, given two tables, will make a foreign key join
condition by default if you dont specify the onclause. but if you
arent using a join function somewhere, then nothing else is going
to generate any clauses for you.
On Feb 27, 2007, at 10:50 AM, Rick Morrison wrote:
Shouldn't SA be able to figure out the following joins without
needing the explicit join params?
I've got to be missing something dumb.
from sqlalchemy import *
metadata = BoundMetaData('sqlite:///:memory:')
ent = Table('entity', metadata,
Column('id', INT, Sequence('entity_id'),
primary_key=True, nullable=False),
Column('nm', VARCHAR(12), nullable=False)
)
loc = Table('location', metadata,
Column('id', INT, Sequence('location_id'),
primary_key=True, nullable=False),
Column('nm', VARCHAR(50))
)
el = Table('el_rel', metadata,
Column('typ', VARCHAR(12), nullable=False),
Column('ident', INT, ForeignKey(' entity.id'),
nullable=False),
Column('idloc', INT, ForeignKey('location.id'),
nullable=False)
)
metadata.create_all()
ent.insert ().execute({'id':1, 'nm':'Eone'}, {'id':2,
'nm':'Etwo'}, {'id':3, 'nm':'Ethree'})
loc.insert().execute({'id':1, 'nm':'Lone'}, {'id':2, 'nm':'Ltwo'},
{'id':3, 'nm':'Lthree'})
el.insert().execute({'typ':'TEST1', 'ident':1, 'idloc':1},
{'typ':'TEST2', 'ident':2, 'idloc':2})
testcases = [('2-way -ent', [ent.c.nm ], None),
('2-way -loc', [loc.c.nm], None),
('3-way -ent+loc', [ent.c.nm, loc.c.nm], None),
('3-way, explicit join', [ent.c.nm, loc.c.nm],
el.join(ent, ent.c.id==el.c.ident).join(loc,
loc.c.id==el.c.idloc))
]
for test_name, test_cols, test_from in testcases:
print
print test_name
q = select(test_cols, el.c.typ == 'TEST1')
if test_from:
q.append_from(test_from)
print str(q)
print q.execute().fetchall()
Thx,
Rick
--~--~-~--~~~---~--~~
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: contains_eager is somehow not loading all the instances.
if you run it with full blown logging on, i.e.:
import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.DEBUG)
logging.getLogger('sqlalchemy.orm').setLevel(logging.DEBUG)
the issue can be detected when you look at the mapper creating
instance keys for T (although this is clearly not a novice issue):
DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key
(class '__main__.T', (1,), None) not in session[]
DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key
(class '__main__.T', (None,), None) not in session[]
DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key
(class '__main__.T', (3,), None) not in session[]
DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key
(class '__main__.T', (None,), None) not in session[]
DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key
(class '__main__.T', (5,), None) not in session[]
so its not getting an identity key for every other row, which
indicates its looking at the wrong column in the result set. (on
each of those Nones, its going to skip that entity) looking at the
query:
SELECT ts.id, ts.dat, other.ts_id, other.other_dat
FROM ts LEFT OUTER JOIN other ON ts.id = other.ts_id
we can see that other has a column called ts_id, which looks
exactly like the label that would be made for id in table ts. so
thats whats happening here. so throwing on a use_labels=True to
the query (or changing the name of ts_id) produces the query:
SELECT ts.id AS ts_id, ts.dat AS ts_dat, other.ts_id AS other_ts_id,
other.other_dat AS other_other_dat
FROM ts LEFT OUTER JOIN other ON ts.id = other.ts_id
that gives the correct results.
not sure what SA can really do here to make this kind of issue easier
to catch, since the resultproxy itself is where its looking for col
label, col name, , etc. the generated labels are generally more
accurate. i tried playing around with ResultProxy to make it detect
an ambiguity of this nature, but i think it might not be possible
unless more flags/switches get passed from the statement to the
result (which id rather not do since it further marginalizes straight
textual queries), since if the select statement uses table/col labels
for each column, there still could be conflicts which dont matter,
such as the column names the normal eager loader generates:
'ts_id', 'ts_dat', 'other_4966_ts_id', 'other_4966_other_dat',
that result is from column ts_id attached to an Alias
other_4966. if we said dont allow any Column to be found twice in
the row, then that breaks (since it will match other_4966_ts_id on
its _label, ts_id on its name).
On Feb 27, 2007, at 12:09 PM, Dennis Muhlestein wrote:
from sqlalchemy import *
e=create_engine('sqlite://memory')
ts=Table('ts',e,
Column ( 'id',Integer,primary_key=True),
Column ( 'dat',Integer,nullable=False))
ts.create()
to_oneornone=Table('other',e,
Column ( 'ts_id', Integer,ForeignKey('ts.id'), primary_key=True,
nullable=False ),
Column ( 'other_dat', Integer, nullable=False ) )
to_oneornone.create()
class T(object): pass
T.mapper=mapper(T,ts)
class To(object):pass
To.mapper=mapper(To,to_oneornone,properties={'ts':relation
(T,backref=backref('other',uselist=False))})
s=create_session()
for x in range(10):
t=T()
t.dat=x
s.save(t)
if x % 2 == 0: # test every other T has an optional data
o=To()
o.other_dat=x
t.other=o
s.save(t)
s.flush()
s.clear()
somedata=s.query(T).options(eagerload('other')).select()
print 'Number results should be 10: ', len(somedata)
s.clear()
sel=select([ts,to_oneornone],
from_obj=[ts.outerjoin(to_oneornone)])
print Raw select also is 10: , len(sel.execute().fetchall() )
print Instances should also be 10: , len(s.query(T).options
(contains_eager('other')).instances(sel.execute()))
--~--~-~--~~~---~--~~
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: contains_eager is somehow not loading all the instances.
Thanks for taking a peek.
Interesting, it does indeed fix the issue to use labels. Now I have
another issue though, I have a case statement in my select which I was
specifying like this:
select ( ['case when yada yada yada end as something' ] ..
If use_labels = True, then the query breaks because the generated sql
has two as label parts two it.
if I delete the as something part, I think don't know
programatically what the label is though. I need to know that because
I order by it.
Isn't there a way to find out a column label from a query?
-Dennis
On Feb 27, 12:47 pm, Michael Bayer [EMAIL PROTECTED] wrote:
if you run it with full blown logging on, i.e.:
import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.DEBUG)
logging.getLogger('sqlalchemy.orm').setLevel(logging.DEBUG)
the issue can be detected when you look at the mapper creating
instance keys for T (although this is clearly not a novice issue):
DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key
(class '__main__.T', (1,), None) not in session[]
DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key
(class '__main__.T', (None,), None) not in session[]
DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key
(class '__main__.T', (3,), None) not in session[]
DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key
(class '__main__.T', (None,), None) not in session[]
DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key
(class '__main__.T', (5,), None) not in session[]
so its not getting an identity key for every other row, which
indicates its looking at the wrong column in the result set. (on
each of those Nones, its going to skip that entity) looking at the
query:
SELECT ts.id, ts.dat, other.ts_id, other.other_dat
FROM ts LEFT OUTER JOIN other ON ts.id = other.ts_id
we can see that other has a column called ts_id, which looks
exactly like the label that would be made for id in table ts. so
thats whats happening here. so throwing on a use_labels=True to
the query (or changing the name of ts_id) produces the query:
SELECT ts.id AS ts_id, ts.dat AS ts_dat, other.ts_id AS other_ts_id,
other.other_dat AS other_other_dat
FROM ts LEFT OUTER JOIN other ON ts.id = other.ts_id
that gives the correct results.
not sure what SA can really do here to make this kind of issue easier
to catch, since the resultproxy itself is where its looking for col
label, col name, , etc. the generated labels are generally more
accurate. i tried playing around with ResultProxy to make it detect
an ambiguity of this nature, but i think it might not be possible
unless more flags/switches get passed from the statement to the
result (which id rather not do since it further marginalizes straight
textual queries), since if the select statement uses table/col labels
for each column, there still could be conflicts which dont matter,
such as the column names the normal eager loader generates:
'ts_id', 'ts_dat', 'other_4966_ts_id', 'other_4966_other_dat',
that result is from column ts_id attached to an Alias
other_4966. if we said dont allow any Column to be found twice in
the row, then that breaks (since it will match other_4966_ts_id on
its _label, ts_id on its name).
On Feb 27, 2007, at 12:09 PM, Dennis Muhlestein wrote:
from sqlalchemy import *
e=create_engine('sqlite://memory')
ts=Table('ts',e,
Column ( 'id',Integer,primary_key=True),
Column ( 'dat',Integer,nullable=False))
ts.create()
to_oneornone=Table('other',e,
Column ( 'ts_id', Integer,ForeignKey('ts.id'), primary_key=True,
nullable=False ),
Column ( 'other_dat', Integer, nullable=False ) )
to_oneornone.create()
class T(object): pass
T.mapper=mapper(T,ts)
class To(object):pass
To.mapper=mapper(To,to_oneornone,properties={'ts':relation
(T,backref=backref('other',uselist=False))})
s=create_session()
for x in range(10):
t=T()
t.dat=x
s.save(t)
if x % 2 == 0: # test every other T has an optional data
o=To()
o.other_dat=x
t.other=o
s.save(t)
s.flush()
s.clear()
somedata=s.query(T).options(eagerload('other')).select()
print 'Number results should be 10: ', len(somedata)
s.clear()
sel=select([ts,to_oneornone],
from_obj=[ts.outerjoin(to_oneornone)])
print Raw select also is 10: , len(sel.execute().fetchall() )
print Instances should also be 10: , len(s.query(T).options
(contains_eager('other')).instances(sel.execute()))
--~--~-~--~~~---~--~~
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: contains_eager is somehow not loading all the instances.
the label is always tablename_columnname. youd have to show me
where you need that to be programmatic.
On Feb 27, 2007, at 2:29 PM, Dennis wrote:
Thanks for taking a peek.
Interesting, it does indeed fix the issue to use labels. Now I have
another issue though, I have a case statement in my select which I was
specifying like this:
select ( ['case when yada yada yada end as something' ] ..
If use_labels = True, then the query breaks because the generated sql
has two as label parts two it.
if I delete the as something part, I think don't know
programatically what the label is though. I need to know that because
I order by it.
Isn't there a way to find out a column label from a query?
-Dennis
On Feb 27, 12:47 pm, Michael Bayer [EMAIL PROTECTED] wrote:
if you run it with full blown logging on, i.e.:
import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.DEBUG)
logging.getLogger('sqlalchemy.orm').setLevel(logging.DEBUG)
the issue can be detected when you look at the mapper creating
instance keys for T (although this is clearly not a novice issue):
DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key
(class '__main__.T', (1,), None) not in session[]
DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key
(class '__main__.T', (None,), None) not in session[]
DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key
(class '__main__.T', (3,), None) not in session[]
DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key
(class '__main__.T', (None,), None) not in session[]
DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key
(class '__main__.T', (5,), None) not in session[]
so its not getting an identity key for every other row, which
indicates its looking at the wrong column in the result set. (on
each of those Nones, its going to skip that entity) looking at the
query:
SELECT ts.id, ts.dat, other.ts_id, other.other_dat
FROM ts LEFT OUTER JOIN other ON ts.id = other.ts_id
we can see that other has a column called ts_id, which looks
exactly like the label that would be made for id in table ts. so
thats whats happening here. so throwing on a use_labels=True to
the query (or changing the name of ts_id) produces the query:
SELECT ts.id AS ts_id, ts.dat AS ts_dat, other.ts_id AS other_ts_id,
other.other_dat AS other_other_dat
FROM ts LEFT OUTER JOIN other ON ts.id = other.ts_id
that gives the correct results.
not sure what SA can really do here to make this kind of issue easier
to catch, since the resultproxy itself is where its looking for col
label, col name, , etc. the generated labels are generally more
accurate. i tried playing around with ResultProxy to make it detect
an ambiguity of this nature, but i think it might not be possible
unless more flags/switches get passed from the statement to the
result (which id rather not do since it further marginalizes straight
textual queries), since if the select statement uses table/col labels
for each column, there still could be conflicts which dont matter,
such as the column names the normal eager loader generates:
'ts_id', 'ts_dat', 'other_4966_ts_id', 'other_4966_other_dat',
that result is from column ts_id attached to an Alias
other_4966. if we said dont allow any Column to be found twice in
the row, then that breaks (since it will match other_4966_ts_id on
its _label, ts_id on its name).
On Feb 27, 2007, at 12:09 PM, Dennis Muhlestein wrote:
from sqlalchemy import *
e=create_engine('sqlite://memory')
ts=Table('ts',e,
Column ( 'id',Integer,primary_key=True),
Column ( 'dat',Integer,nullable=False))
ts.create()
to_oneornone=Table('other',e,
Column ( 'ts_id', Integer,ForeignKey('ts.id'), primary_key=True,
nullable=False ),
Column ( 'other_dat', Integer, nullable=False ) )
to_oneornone.create()
class T(object): pass
T.mapper=mapper(T,ts)
class To(object):pass
To.mapper=mapper(To,to_oneornone,properties={'ts':relation
(T,backref=backref('other',uselist=False))})
s=create_session()
for x in range(10):
t=T()
t.dat=x
s.save(t)
if x % 2 == 0: # test every other T has an optional data
o=To()
o.other_dat=x
t.other=o
s.save(t)
s.flush()
s.clear()
somedata=s.query(T).options(eagerload('other')).select()
print 'Number results should be 10: ', len(somedata)
s.clear()
sel=select([ts,to_oneornone],
from_obj=[ts.outerjoin(to_oneornone)])
print Raw select also is 10: , len(sel.execute().fetchall() )
print Instances should also be 10: , len(s.query(T).options
(contains_eager('other')).instances(sel.execute()))
--~--~-~--~~~---~--~~
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,
[sqlalchemy] Print of returned object fails
I am not able to print the object returned by sqlAlchemy in this
instance:
clients=Table('clients',metadata, autoload=True)
s = clients.select(clients.c.client_id==client_id)
#print str(s)
result = s.execute()
client = result.fetchone()
print client
The error:
Traceback (most recent call last):
File db_interface/sqlAlchemyTest.py, line 61, in module
x=getClientData(1390)
File db_interface/sqlAlchemyTest.py, line 46, in getClientData
print client
File build/bdist.linux-i686/egg/sqlalchemy/engine/base.py, line
811, in __repr__
File build/bdist.linux-i686/egg/sqlalchemy/engine/base.py, line
672, in _get_col
File build/bdist.linux-i686/egg/sqlalchemy/databases/postgres.py,
line 81, in convert_result_value
IndexError: list index out of range
I'm using Postgresql 8.1.5 and Psycopg 1.99.13 (the latter is old, and
used intentionally).
Thank you,
~G~
--~--~-~--~~~---~--~~
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: contains_eager is somehow not loading all the instances.
Well, columnname isn't a simple column in the case of a case
statement... the label is turning out like this:
casewhenhas_testtrueandscoreisnullandgender1then1whenscoreisnullthen2elsescoreend
I haven't found a way to manually assign a label to a text clause yet,
but before I tried use_labels=True, I has appended as score to the
case clause and that worked.
On Feb 27, 2:44 pm, Michael Bayer [EMAIL PROTECTED] wrote:
the label is always tablename_columnname. youd have to show me
where you need that to be programmatic.
On Feb 27, 2007, at 2:29 PM, Dennis wrote:
Thanks for taking a peek.
Interesting, it does indeed fix the issue to use labels. Now I have
another issue though, I have a case statement in my select which I was
specifying like this:
select ( ['case when yada yada yada end as something' ] ..
If use_labels = True, then the query breaks because the generated sql
has two as label parts two it.
if I delete the as something part, I think don't know
programatically what the label is though. I need to know that because
I order by it.
Isn't there a way to find out a column label from a query?
-Dennis
On Feb 27, 12:47 pm, Michael Bayer [EMAIL PROTECTED] wrote:
if you run it with full blown logging on, i.e.:
import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.DEBUG)
logging.getLogger('sqlalchemy.orm').setLevel(logging.DEBUG)
the issue can be detected when you look at the mapper creating
instance keys for T (although this is clearly not a novice issue):
DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key
(class '__main__.T', (1,), None) not in session[]
DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key
(class '__main__.T', (None,), None) not in session[]
DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key
(class '__main__.T', (3,), None) not in session[]
DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key
(class '__main__.T', (None,), None) not in session[]
DEBUG:sqlalchemy.orm.mapper.Mapper:(T|ts) _instance(): identity key
(class '__main__.T', (5,), None) not in session[]
so its not getting an identity key for every other row, which
indicates its looking at the wrong column in the result set. (on
each of those Nones, its going to skip that entity) looking at the
query:
SELECT ts.id, ts.dat, other.ts_id, other.other_dat
FROM ts LEFT OUTER JOIN other ON ts.id = other.ts_id
we can see that other has a column called ts_id, which looks
exactly like the label that would be made for id in table ts. so
thats whats happening here. so throwing on a use_labels=True to
the query (or changing the name of ts_id) produces the query:
SELECT ts.id AS ts_id, ts.dat AS ts_dat, other.ts_id AS other_ts_id,
other.other_dat AS other_other_dat
FROM ts LEFT OUTER JOIN other ON ts.id = other.ts_id
that gives the correct results.
not sure what SA can really do here to make this kind of issue easier
to catch, since the resultproxy itself is where its looking for col
label, col name, , etc. the generated labels are generally more
accurate. i tried playing around with ResultProxy to make it detect
an ambiguity of this nature, but i think it might not be possible
unless more flags/switches get passed from the statement to the
result (which id rather not do since it further marginalizes straight
textual queries), since if the select statement uses table/col labels
for each column, there still could be conflicts which dont matter,
such as the column names the normal eager loader generates:
'ts_id', 'ts_dat', 'other_4966_ts_id', 'other_4966_other_dat',
that result is from column ts_id attached to an Alias
other_4966. if we said dont allow any Column to be found twice in
the row, then that breaks (since it will match other_4966_ts_id on
its _label, ts_id on its name).
On Feb 27, 2007, at 12:09 PM, Dennis Muhlestein wrote:
from sqlalchemy import *
e=create_engine('sqlite://memory')
ts=Table('ts',e,
Column ( 'id',Integer,primary_key=True),
Column ( 'dat',Integer,nullable=False))
ts.create()
to_oneornone=Table('other',e,
Column ( 'ts_id', Integer,ForeignKey('ts.id'), primary_key=True,
nullable=False ),
Column ( 'other_dat', Integer, nullable=False ) )
to_oneornone.create()
class T(object): pass
T.mapper=mapper(T,ts)
class To(object):pass
To.mapper=mapper(To,to_oneornone,properties={'ts':relation
(T,backref=backref('other',uselist=False))})
s=create_session()
for x in range(10):
t=T()
t.dat=x
s.save(t)
if x % 2 == 0: # test every other T has an optional data
o=To()
o.other_dat=x
t.other=o
s.save(t)
s.flush()
s.clear()
somedata=s.query(T).options(eagerload('other')).select()
print 'Number results should be 10: ', len(somedata)
s.clear()
sel=select([ts,to_oneornone],
[sqlalchemy] rev2362 fixes one thing and breaks another
hallo. AFAIsee rev 2362 fixes the lazy-load for polymorphic properties. but is seems to introduce another problem.. or maybe uncover it. table-inheritance, A-B-C-D, and A has reference to B. 2361: lazy-ref is loaded wrong ... File /home/az/src/hor-trunk/db/sawrap/tests/sa_gentestbase.py, line 109, in query1 klas.__name__+'.getby_'+idname+'():\n result= %(q)s\n expect= %(exp_single)s' % locals() AssertionError: A.getby_db_id(): result= A( db_id=1 linkA=cc name=anna ) expect= A( db_id=1 linkA=ben name=anna ) 2362: cannot compile mapper A: ... (inside obj-ctor / mapper compilation) ... File /usr/lib/python2.4/site-packages/sqlalchemy/orm/properties.py, line 341, in _create_polymorphic_joins raise exceptions.AssertionError(Could not find corresponding column for + str(c) + in selectable + str(self.mapper.select_table)) AssertionError: Could not find corresponding column for B.db_id in selectable SELECT bz4C.db_id, bz4C.name, CAST(NULL AS TEXT) AS dataD, bz4C.linkA_id, bz4C.atype, bz4C.dataC, bz4C.dataB FROM (SELECT A.linkA_id AS linkA_id, A.name AS name, A.db_id AS db_id, A.atype AS atype, B.dataB AS dataB, C.dataC AS dataC FROM A JOIN B ON B.db_id = A.db_id JOIN C ON C.db_id = B.db_id WHERE A.atype = ?) AS bz4C UNION ALL SELECT bz4B.db_id, bz4B.name, CAST(NULL AS TEXT) AS dataD, bz4B.linkA_id, bz4B.atype, CAST(NULL AS TEXT) AS dataC, bz4B.dataB FROM (SELECT A.linkA_id AS linkA_id, A.name AS name, A.db_id AS db_id, A.atype AS atype, B.dataB AS dataB FROM A JOIN B ON B.db_id = A.db_id WHERE A.atype = ?) AS bz4B UNION ALL SELECT D.db_id, A.name, D.dataD, A.linkA_id, A.atype, C.dataC, B.dataB FROM A JOIN B ON B.db_id = A.db_id JOIN C ON C.db_id = B.db_id JOIN D ON D.db_id = C.db_id FYI, This behaviour happens if A points to a heir of itself of level = max-2; e.g. in the case of A-B-C-D-E-F, A pointing to any of B,C,D would raise the above, and will not error for A,E,F. Or something alike. === ciao svil --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- _test-ABCD-t.py Description: application/python sa_gentestbase.py Description: application/python
