Dear All,
Our development team has been using SQL Alchemy with PostgreSQL (8.1.4) for the last couple of months – we are currently on version 0.2.8 of SQLAlchemy.
A colleague of mine has noticed an issue with the Arbitrary Mapper. He has tried posting to the mailing list but although subscribed doesn’t seem to be able to do it so I’m posting on his behalf.
His message is below:
--
I’ve defined an arbitrary and non primary mapper using an alised select (see code below) the alias name is ‘x’. The select does not use the primary key of the table so a primary key is defined when the mapper() function is called. Retrieving information, using this mapper, results in a KeyError. The traceback follows:
Traceback (most recent call last):
File "sqla_arbitrary.py", line 88, in ?
results = query.select()
File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 226, in select
File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 232, in select_whereclause
File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 305, in _select_statement
File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 269, in instances
File "build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py", line 627, in instances
File "build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py", line 993, in _instance
File "build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py", line 970, in _row_identity_key
File "build/bdist.linux-i686/egg/sqlalchemy/orm/session.py", line 449, in get_row_key
File "build/bdist.linux-i686/egg/sqlalchemy/orm/session.py", line 222, in get_row_key
File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 658, in __getitem__
File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 596, in _get_col
File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 581, in _convert_key
KeyError: 'name'
On instrumenting Session.get_row_key() (orm/session.py line 206)
def get_row_key(row, class_, primary_key, entity_name=None):
"""return an identity-map key for use in storing/retrieving an item from the identity map.
row - a sqlalchemy.dbengine.RowProxy instance or other map corresponding result-set
column names to their values within a row.
class_ - a reference to the object's class
primary_key - a list of column objects that will target the primary key values
in the given row.
entity_name - optional string name to further qualify the class
"""
print "Row: [%s]" % ( row.__repr__() )
print "Row Keys: [%s]" % ( row.keys() )
print "Row Values: [%s]" % ( row.values() )
return (class_, tuple([row[column] for column in primary_key]), entity_name)
get_row_key = staticmethod(get_row_key)
This produces the following output:
Row: [('matcher2', 2)]
Row Keys: [['x_name', 'x_revision']]
Row Values: [['matcher2', 2]]
As can be seen the row keys include the alias name, e.g. x_name . Since the primary key, defined for the mapper, is a column name that exists in the table this name cannot be found in the RowProxy object.
I think the problem is with aliased selects used in arbitrary mappers with defined primary keys. Is this a bug or am I doing something wrong?
The code that exhibits that problem is shown below:
from sqlalchemy import *
eng = create_engine( "postgres://user:[EMAIL PROTECTED]:9999/test" )
meta = BoundMetaData( eng )
sesh = create_session( bind_to = eng )
eng.echo = True
no_populate = True
try:
tab = Table( "sqla_arbitrary", \
meta, \
autoload = True )
except:
tab = Table( "sqla_arbitrary", \
meta, \
Column( "id", Integer, primary_key = True ), \
Column( "name", String ), \
Column( "revision", Integer ), \
Column( "description", String ) )
tab.create()
no_populate = False
class Revision( object ):
def __init__( self, name, desc, revision ):
self.name = name
self.description = desc
self.revision = revision
def __cmp__( self, other ):
if self.name == other.name and \
self.description == other.description and \
self.revision == other.revision:
return 0
return 1
class MaxRevision( object ):
def __cmp__( self, other ):
if self.name == other.name and \
self.revision and other.revision:
return 0
return 1
def __str__( self ):
return "# Name [%s], Revision: [%d] #" % ( self.name, self.revision )
def displayResults( title, results ):
print "\n%s\n" % ( title )
for r in results:
print "%s" % ( r )
print "\n\n"
# Create mapper
simple_mapper = mapper( Revision, tab )
# Some data
d1 = Revision( "matcher1", "My first matcher", 1 )
d2 = Revision( "matcher2", "My second matcher", 1 )
d3 = Revision( "matcher3", "My third matcher", 1 )
d4 = Revision( "matcher1", "My edited once first matcher", 2 )
d5 = Revision( "matcher1", "My editied twice first matcher", 3 )
d6 = Revision( "matcher2", "My editied one second matcher", 2 )
d7 = Revision( "matcher4", "My fourth matcher", 1 )
# Insert the data
if no_populate == False:
sesh.save( d1 )
sesh.save( d2 )
sesh.save( d3 )
sesh.save( d4 )
sesh.save( d5 )
sesh.save( d6 )
sesh.save( d7 )
sesh.flush()
# Create a select statement
sel = select( [ tab.c.name, func.max( tab.c.revision ).label( "revision" ) ], \
group_by = [ tab.c.name ] )
results = sel.execute()
displayResults( "SELECT STATEMENT", results )
# Create an arbitrary mapper
mapper_sel = select( [ tab.c.name, func.max( tab.c.revision ).label( "revision" ) ], group_by = [ tab.c.name ] ).alias( "x" )
print "Mapper Select: [", mapper_sel , "]\n\n"
# Read only mapper
max_mapper = mapper( MaxRevision, mapper_sel, primary_key = [ tab.c["name"] ], non_primary = True )
query = sesh.query( max_mapper )
results = query.select()
displayResults( "MAPPER", results )
Many thanks,
Ian Johnson
--
Any thoughts?
Regards,
Nik Vukovljak
This email and any attachment may contain confidential, privileged information for the sole use of the intended recipient. If you are not the intended recipient, do not disclose, reproduce, disseminate or otherwise use this communication. If you received this communication in error, please immediately notify the sender via email and delete the communication from your system.------------------------------------------------------------------------- Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys -- and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
