I'm having difficulty with table joins not using my foreign keys. A
corrected query would produce "modules.module_id =
module_extra_keys.module_id" in the WHERE clause. But in the below
test code, it does not. However, if I use the get_by() function of the
data_mapper the SQL is correct. Clearly, I'm doing something
wrong....but I've not figured it out yet. Help....
import sqlalchemy as sqla
db_con = sqla.create_engine('sqlite:///:memory:')
metadata = sqla.BoundMetaData(db_con)
modules = sqla.Table('modules', metadata,
sqla.Column('module_id',
sqla.Integer,
primary_key = True),
sqla.Column('module_name',
sqla.String(25),
nullable = False,
unique = True),
sqla.UniqueConstraint('module_name',
name = 'modules_idx1')
)
module_extra_keys = sqla.Table('module_extra_keys',
metadata,
sqla.Column('module_extra_key_id',
sqla.Integer,
nullable = False,
primary_key = True),
sqla.Column('module_id',
sqla.Integer,
sqla.ForeignKey('modules.module_id'),
nullable = False),
sqla.Column('module_extra_key_name',
sqla.String(25),
nullable = False),
sqla.UniqueConstraint('module_id',
'module_extra_key_name',
name = 'module_extra_key_idx1')
)
metadata.create_all()
session = sqla.create_session(db_con)
class Module(object):
"Model class for the Modules table"
def __init__(self, name):
self.module_name = name
class Module_extra_key(object):
"Model class for Module_extra_keys table"
def __init__(self, name):
self.module_extra_key_name = name
# Table modules
# Primary Key: module_id
module_mapper = sqla.mapper(Module, modules)
# Table module_extra_keys
# Primary Key: module_extra_key_id
# Foreign Key: modules(module_id)
module_extra_keys_mapper = sqla.mapper(Module_extra_key,
module_extra_keys)
module_mapper.add_property('extra_keys',
sqla.relation(Module_extra_key,
order_by = module_extra_keys.c.module_extra_key_name))
module_extra_keys_mapper.add_property('module',
sqla.relation(Module))
###########################################################################
# Add database data
module_1 = Module('module_one')
module_2 = Module('module_two')
module_2.extra_keys.append(Module_extra_key('key1'))
module_2.extra_keys.append(Module_extra_key('key2'))
session.save(module_1)
session.save(module_2)
session.flush()
query = session.query(Module_extra_key)
res = query.select_by(modules.c.module_name == 'module_two',
module_extra_keys.c.module_extra_key_name == 'key1')
assert(res[0].module_extra_key_name == 'key1') # Passes
# This doesn't produce a query to produce a correct result
res = query.select_by(modules.c.module_name == 'module_one',
module_extra_keys.c.module_extra_key_name == 'key1')
assert(res == []) # FAILS !!!
k = session.query(Module_extra_key).get_by(module_name = 'module_two',
\
module_extra_key_name = 'key1')
assert(k.module_extra_key_name == 'key1') # Passes
k = session.query(Module_extra_key).get_by(module_name = 'module_one',
\
module_extra_key_name = 'key1')
assert(k == None) # Passes
session.flush()
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
