Re: [Sqlalchemy-users] select_by, get_by strangeness?

[Michael Bayer]

of course you are not misunderstanding them.  have you tried this test with any other database besides SQLite ?  Im looking at the two queries from get() and get_by(), one of which uses a limit, the queries/result sets are as follows: TEST ONE [2006-03-06 02:43:58,008] [engine]: SELECT users.user_name AS users_user_name, users.user_id AS users_user_id, phone_numbers_931b.type AS phone_numbers_931b_type, phone_numbers_931b.address_id AS phone_numbers_931b_address_id, phone_numbers_931b.phone_id AS phone_numbers_931b_phone_id, phone_numbers_931b.number AS phone_numbers_931b_number, addresses_14af.address_id AS addresses_14af_address_id, addresses_14af.user_id AS addresses_14af_user_id, addresses_14af.address AS addresses_14af_address FROM users LEFT OUTER JOIN addresses AS addresses_14af ON users.user_id = addresses_14af.user_id LEFT OUTER JOIN phone_numbers AS phone_numbers_931b ON addresses_14af.address_id = phone_numbers_931b.address_id WHERE users.user_id = ? [2006-03-06 02:43:58,010] [engine]: [1] [2006-03-06 02:43:58,011] [engine]: (u'user 1', 1, u'home', 1, 1, u'1111', 1, 1, u'a1 address') [2006-03-06 02:43:58,014] [engine]: (u'user 1', 1, u'work', 1, 2, u'22222', 1, 1, u'a1 address') [2006-03-06 02:43:58,017] [engine]: (u'user 1', 1, u'home', 2, 3, u'3333', 2, 1, u'a2 address') [2006-03-06 02:43:58,019] [engine]: (u'user 1', 1, u'work', 2, 4, u'44444', 2, 1, u'a2 address') TEST TWO [2006-03-06 02:43:58,033] [engine]: SELECT users.user_name AS users_user_name, users.user_id AS users_user_id, phone_numbers_931b.type AS phone_numbers_931b_type, phone_numbers_931b.address_id AS phone_numbers_931b_address_id, phone_numbers_931b.phone_id AS phone_numbers_931b_phone_id, phone_numbers_931b.number AS phone_numbers_931b_number, addresses_14af.address_id AS addresses_14af_address_id, addresses_14af.user_id AS addresses_14af_user_id, addresses_14af.address AS addresses_14af_address FROM (SELECT users.user_id AS users_user_id FROM users WHERE users.user_id = ? LIMIT 1) AS rowcount, users LEFT OUTER JOIN addresses AS addresses_14af ON users.user_id = addresses_14af.user_id LEFT OUTER JOIN phone_numbers AS phone_numbers_931b ON addresses_14af.address_id = phone_numbers_931b.address_id WHERE rowcount.users_user_id = users.user_id [2006-03-06 02:43:58,034] [engine]: [1] [2006-03-06 02:43:58,036] [engine]: (u'user 1', 1, u'home', 1, 1, u'1111', 1, 1, u'a1 address') without digging much further i am not seeing yet why the second query (from get_by) fails to produce the same rows as the first, and I often suspect SQLite as its frequently buggy.   i might not get to look at this again until tomorrow night. On Mar 5, 2006, at 5:13 PM, Robert Leftwich wrote: from sqlalchemy import * db  = engine.create_engine('sqlite', {'filename':':memory:'}, echo=True) users_table = Table('users', db,     Column('user_id', Integer, Sequence('user_id_seq', optional=True), primary_key = True),     Column('user_name', String(40)),      ) addresses_table = Table('addresses', db,                         Column('address_id', Integer, Sequence('address_id_seq', optional=True), primary_key = True),                         Column('user_id', Integer, ForeignKey("users.user_id")),                         Column('address', String(40)),                         ) phones_table = Table('phone_numbers', db,                         Column('phone_id', Integer, Sequence('phone_id_seq', optional=True), primary_key = True),                         Column('address_id', Integer, ForeignKey('addresses.address_id')),                         Column('type', String(20)),                         Column('number', String(10)),                         ) users_table.create() addresses_table.create() phones_table.create()      class User(object):     def __init__(self):         self.user_id = None     def __repr__(self):         return "User:" + repr(getattr(self, 'user_id', None)) + " " + repr(getattr(self, 'user_name', None)) + " " + str([repr(addr) for addr in self.addresses]) class Address(object):     def __repr__(self):         return "Address: " + repr(getattr(self, 'address_id', None)) + " " + repr(getattr(self, 'user_id', None)) + " " + repr(self.address) + str([repr(ph) for ph in self.phones]) class Phone(object):     def __repr__(self):         return "Phone: " + repr(getattr(self, 'phone_id', None)) + " " + repr(getattr(self, 'address_id', None)) + " " + repr(self.type) + " " + repr(self.number) Phone.mapper = mapper(Phone, phones_table, is_primary=True) Address.mapper = mapper(Address, addresses_table, properties={     'phones': relation(Phone.mapper, lazy=False)     }) User.mapper = mapper(User, users_table, properties={     'addresses' : relation(Address.mapper, lazy=False),     }) objectstore.clear() u1 = User() u1.user_name = 'user 1' a1 = Address() a1.address = 'a1 address' p1 = Phone() p1.type = 'home' p1.number = '1111' a1.phones.append(p1) p2 = Phone() p2.type = 'work' p2.number = '22222' a1.phones.append(p2) u1.addresses.append(a1) a2 = Address() a2.address = 'a2 address' p3 = Phone() p3.type = 'home' p3.number = '3333' a2.phones.append(p3) p4 = Phone() p4.type = 'work' p4.number = '44444' a2.phones.append(p4) u1.addresses.append(a2) objectstore.commit() objectstore.clear() a = User.mapper.get(1) print a objectstore.clear() b = User.mapper.select_by(user_id=1)[0] print b assert repr(a) == repr(b) objectstore.clear() c = User.mapper.select_by(user_name='user 1')[0] print c assert repr(a) == repr(b) == repr(c) objectstore.clear() d = User.mapper.get_by(user_name='user 1') print d assert repr(a) == repr(b) == repr(c) == repr(d)