load subset of joined columns in sqlalchemy

Here is the corrected code:

from sqlalchemy
import create_engine
from sqlalchemy
import Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative
import declarative_base
from sqlalchemy.orm
import joinedload, Load, relationship, load_only

Base = declarative_base()

class Table1(Base):
   __tablename__ = 'table1'
global_id = Column(Integer, primary_key = True)
table1_val1 = Column(String)
table1_val2 = Column(String)
r1 = relationship('Table2', backref = 'r2')

class Table2(Base):
   __tablename__ = 'table2'
global_id = Column(Integer, ForeignKey('table1.global_id'), primary_key = True)
table2_val1 = Column(String)
table2_val2 = Column(String)

from sqlalchemy.orm
import sessionmaker
some_engine = create_engine('sqlite://')
Base.metadata.create_all(some_engine)
Session = sessionmaker(bind = some_engine)
session = Session()

session.add(Table1(table1_val1 = '1val1', table1_val2 = '1val2', r1 = [Table2(table2_val1 = '2val1', table2_val2 = '2val2')]))
session.commit() # expires the attribute from the session

query = session.query(Table1).options(
   # note that the 'load_only()'
   is applied to the 'joinedload'
   path, not # put on its own Load() path joinedload('r1', innerjoin = True).load_only('table2_val1'),
   Load(Table1).load_only('table1_val1'))

foo = query.all()
assert 'table1_val2'
not in foo[0].__dict__
assert 'table2_val2'
not in foo[0].r1[0].__dict__

Suggestion : 2

Now that we have two tables, we will see how to create queries on both tables at the same time. To construct a simple implicit join between Customer and Invoice, we can use Query.filter() to equate their related columns together. Below, we load the Customer and Invoice entities at once using this method −,Once we have our statement, it behaves like a Table construct. The columns on the statement are accessible through an attribute called c as shown in the below code −,Query.join() knows how to join between these tables because there’s only one foreign key between them. If there were no foreign keys, or more foreign keys, Query.join() works better when one of the following forms are used −,The actual SQL JOIN syntax is easily achieved using the Query.join() method as follows −

from sqlalchemy.orm
import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()

for c, i in session.query(Customer, Invoice).filter(Customer.id == Invoice.custid).all():
   print("ID: {} Name: {} Invoice No: {} Amount: {}".format(c.id, c.name, i.invno, i.amount))

The SQL expression emitted by SQLAlchemy is as follows −

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email, invoices.id
AS invoices_id, invoices.custid
AS invoices_custid, invoices.invno
AS invoices_invno, invoices.amount
AS invoices_amount
FROM customers, invoices
WHERE customers.id = invoices.custid

And the result of the above lines of code is as follows −

ID: 2 Name: Gopal Krishna Invoice No: 10 Amount: 15000
ID: 2 Name: Gopal Krishna Invoice No: 14 Amount: 3850
ID: 3 Name: Govind Pant Invoice No: 3 Amount: 10000
ID: 3 Name: Govind Pant Invoice No: 4 Amount: 5000
ID: 4 Name: Govind Kala Invoice No: 7 Amount: 12000
ID: 4 Name: Govind Kala Invoice No: 8 Amount: 8500
ID: 5 Name: Abdul Rahman Invoice No: 9 Amount: 15000
ID: 5 Name: Abdul Rahman Invoice No: 11 Amount: 6000

The SQL expression for join will be displayed on the console −

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers JOIN invoices ON customers.id = invoices.custid
WHERE invoices.amount = ?

We can iterate through the result using for loop −

result = session.query(Customer).join(Invoice).filter(Invoice.amount == 8500)
for row in result:
   for inv in row.invoices:
   print(row.id, row.name, inv.invno, inv.amount)

Suggestion : 3

When using subquery loading, the load of 100 objects will emit two SQL statements. The second statement will fetch a total number of rows equal to the sum of the size of all collections. An INNER JOIN is used, and a minimum of parent columns are requested, only the primary keys. So a subquery load makes sense when the collections are larger.,The alias argument can be more creatively used, in that it can be made to represent any set of arbitrary names to match up into a statement. Below it is linked to a select() which links a set of column objects to a string SQL statement:,As an example, we can load a User object and eagerly load only particular addresses into its .addresses collection just by filtering:,The default loader strategy for any relationship() is configured by the lazy keyword argument, which defaults to select - this indicates a “select” statement . Below we set it as joined so that the children relationship is eager loaded using a JOIN:

sql>>> jack.addresses
SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address,
addresses.user_id AS addresses_user_id
FROM addresses
WHERE ? = addresses.user_id
[5]
[<Address(u'jack@google.com')>, <Address(u'j25@yahoo.com')>]

sql >>> jack = session.query(User).\
   ...options(joinedload('addresses')).\
   ...filter_by(name = 'jack').all() #doctest: +NORMALIZE_WHITESPACE
SELECT addresses_1.id AS addresses_1_id, addresses_1.email_address AS addresses_1_email_address,
   addresses_1.user_id AS addresses_1_user_id, users.id AS users_id, users.name AS users_name,
   users.fullname AS users_fullname, users.password AS users_password
FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id
WHERE users.name = ? ['jack']

sql >>> jack = session.query(User).\
   ...options(subqueryload('addresses')).\
   ...filter_by(name = 'jack').all()
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname,
   users.password AS users_password
FROM users
WHERE users.name = ?
   ('jack', )
SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address,
   addresses.user_id AS addresses_user_id, anon_1.users_id AS anon_1_users_id
FROM(SELECT users.id AS users_id FROM users WHERE users.name = ? ) AS anon_1 JOIN addresses ON anon_1.users_id = addresses.user_id
ORDER BY anon_1.users_id, addresses.id('jack', )

# load the 'children'
collection using LEFT OUTER JOIN
class Parent(Base):
   __tablename__ = 'parent'

id = Column(Integer, primary_key = True)
children = relationship("Child", lazy = 'joined')

# load the 'children'
collection using a second query which
# JOINS to a subquery of the original
class Parent(Base):
   __tablename__ = 'parent'

id = Column(Integer, primary_key = True)
children = relationship("Child", lazy = 'subquery')

# set children to load lazily
session.query(Parent).options(lazyload('children')).all()

# set children to load eagerly with a join
session.query(Parent).options(joinedload('children')).all()

# set children to load eagerly with a second statement
session.query(Parent).options(subqueryload('children')).all()