consider turning allow_null_pks on permanently

Let's say that the left table has a primary key, and the right table have a primary key and also a foreign key to the left table. If there is a row in the left table that doesn't have a row in the right table that reference it (with the foreign key) than: 1. If the mapper "allow_null_pks" attribute is set to False, querying for the object described by the row returns None. 1. If the mapper "allow_null_pks" attribute is set to True, querying for the object described by the row returns the object representing the row.

For example the code:

import sqlalchemy
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite://')
metadata = MetaData()
metadata.bind = engine

session = scoped_session(sessionmaker(bind = engine, autoflush = True, autocommit = False))

# TABLES
left_table = Table('left', metadata, 
    Column('left_id', Integer, primary_key=True),
    Column('left_data', String(50))
)

right_table = Table('right', metadata, 
    Column('right_id', Integer, primary_key=True),
    Column('left_id', Integer, ForeignKey(left_table.columns.left_id)),
    Column('right_data', String(50))
)

metadata.create_all()

engine.execute(left_table.insert(), ['left_data':'left_data1'}]({'left_id':1,))
engine.execute(left_table.insert(), ['left_data':'left_data2'}]({'left_id':2,))
engine.execute(left_table.insert(), ['left_data':'left_data3'}]({'left_id':3,))

engine.execute(right_table.insert(), ['left_id':1, 'right_data':'right_data1'}]({'right_id':1,))
engine.execute(right_table.insert(), ['left_id':2, 'right_data':'right_data2'}]({'right_id':2,))


my_outer_join = join(left_table, right_table, isouter = True)

# POJOS
class MyLeftOuterJoinedObject(object):

    def __repr__(self):
        return 'left_id: %s, right_id: %s, left_data: %s, right_data: %s' % (self.left_id, self.right_id, self.left_data, self.right_data)

# MAPPERS
my_mapper = session.mapper(MyLeftOuterJoinedObject, my_outer_join)
my_mapper.allow_null_pks = False

# MAIN

print MyLeftOuterJoinedObject.query().filter_by(left_id = 1).count()
print MyLeftOuterJoinedObject.query().filter_by(left_id = 1).first() 
print MyLeftOuterJoinedObject.query().filter_by(left_id = 3).count()
print MyLeftOuterJoinedObject.query().filter_by(left_id = 3).first()

prints:

1
left_id: 1, right_id: 1, left_data: left_data1, right_data: right_data1
1
None

But if changing the row "my_mapper.allow_null_pks = False" to "my_mapper.allow_null_pks = True", than it prints:

1
left_id: 1, right_id: 1, left_data: left_data1, right_data: right_data1
1
left_id: 3, right_id: None, left_data: left_data3, right_data: None

I'm not sure this is really a defect, but it something that should be warned. (In the code? In the tutorial?) Either way, in my own code I set allow_null_pks to True, because that is what I needed. I think that one should be aware of this option (instead of debugging sqlalchemy and finding out on his own).

Thanks. kobipe3@gmail.com

Comments (5)