[sqlalchemy] Patterns for building with SQLAlchemy / asyncio
One "exit-hatch" for using the SQLAlchemy ORM with asyncio is to use executors. In this model, all database access is confined to a thread that is not the thread where the eventloop is running. However, it appears that sessions (thus, transactions) should not span more than one executor, meaning that all SQL work must be batched into the same executor – *right?*. For a web framework, this is problematic: during the request lifecycle there are many points at which the database might need to be accessed (auth middleware, request handling, etc.) – hence multiple entries into an executor and therefore incompatible with the idea of a session. A possible alternative then appears to be using something like `aiopg` and confining your work to SQLAlchemy core. However, my problem here is that I'm worried by using SQLAlchemy core, and mapping ResultProxy objects into POPO (plain old python objects, perhaps built on top of the `attrs` library), I'm just ultimately approximating the reinvention of an ORM (without unit of work, without relationships, etc.) – *right?*. Can someone help me square into my head whether I should be building an application on top of sqlalchemy.core (perhaps with something like `aiopg`), or whether there is a pattern I'm missing? Thanks, Devin -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Nested Exists Query produces join
I'm trying to do a Many-to-Many query for all rows in `AModel` that don't
have a relationship with every `BModel` row.
# The schema that looks like this:
class AModel(Base):
__tablename__ = 'amodels'
id = Column(Integer, primary_key=True)
relationship('BModel', secondary='abjoinmodels',
back_populates='relateds')
class BModel(Base):
__tablename__ = 'bmodels'
id = Column(Integer, primary_key=True)
relationship('AModel', secondary='abjoinmodels',
back_populates='relateds')
class ABJoinModel(Base):
__tablename__ = 'abjoinmodels'
a_id = Column(Integer, ForeignKey('amodels.id'), primary_key=True)
b_id = Column(Integer, ForeignKey('bmodels.id'), primary_key=True)
# And here's some mock data:
b_instances = [BModel(), BModel()]
a_instances = [
AModel(relateds=b_instances),
AModel(relateds=b_instances[0:1]),
AModel(),
]
dbsession.add_all(a_instances)
dbsession.flush()
# Now this query extracts all the rows appropriately:
# -- at least one relationship missing
# SELECT a.id
# FROM a
# WHERE EXISTS (
# SELECT 1
# FROM b
# WHERE NOT EXISTS (
# SELECT 1
# FROM ab_join
# WHERE a.id = ab_join.a_id AND b.id = ab_join.b_id
# )
# );
# -- produces 2, 3
# However, trying to map this to SQLAlchemy, I get this:
query = Query(AModel).filter(
Query(BModel).filter(
Query(AModel.relateds.prop.secondary).filter(
and_(
BModel.relateds.prop.secondary.c.a_id == AModel.id,
BModel.relateds.prop.secondary.c.b_id == BModel.id,
)
).exists()
).exists()
)
# which produces the following statement:
# SELECT amodels.id AS amodels_id
# FROM amodels
# WHERE EXISTS (SELECT 1
# FROM bmodels
# WHERE EXISTS (SELECT 1
# FROM abjoinmodels, amodels
# WHERE abjoinmodels.a_id = amodels.id AND abjoinmodels.b_id = bmodels.id))
Query.with_session(dbsession).all()
# []
And, therefore doesn't return ANY rows.
How should I be constructing this? I'm trying to generalize this, so I'd
also like to be able to do this same query on a One-to-Many mapping too
(where B.a_id = Column(Integer, ForeignKey('amodels.id'))).
Thanks!
Devin
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] DeclaredAttr
First, thanks for your very insightful response. I'm trying to reproduce
what you've provided, but I'm getting an `InvalidRequestError` when
querying on `RefClass`:
*InvalidRequestError: SQL expression, column, or mapped entity expected -
got ''*
Indeed, RefClass has no `__mapper__` attribute. Querying on any other
object works as intended though. I imagine there's a small detail I'm
missing, so I've highlighted the changes from yesterday's post.
from sqlalchemy import (
Column,
ForeignKey,
Integer,
create_engine,
)
from sqlalchemy.ext.declarative import (
AbstractConcreteBase,
declared_attr,
declarative_base,
has_inherited_table,
)
from sqlalchemy.orm import Session
Base = declarative_base()
class AClass(Base):
__tablename__ = 'aclass'
id = Column(Integer, primary_key=True)
class BClass(Base):
__tablename__ = 'bclass'
id = Column(Integer, primary_key=True)
class RefClass(AbstractConcreteBase, Base):
@declared_attr
def __tablename__(cls):
if cls.__name__ == 'RefClass':
return None
return cls.__name__.lower()
@declared_attr.cascading
def id(cls):
if cls.__name__ == 'RefClass':
return Column(Integer)
column_name = '{}.id'.format(cls.ref.__tablename__)
return Column(ForeignKey(column_name), primary_key=True)
@declared_attr
def __mapper_args__(cls):
if cls.__name__ == 'RefClass':
return {}
return {
'concrete': True,
'polymorphic_identity': cls.ref.__name__
}
class ARefClass(RefClass):
ref = AClass
class BRefClass(RefClass):
ref = BClass
engine = create_engine('sqlite://', echo=True)
Base.metadata.bind = engine
Base.metadata.create_all()
db = Session(engine)
Thanks!
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] DeclaredAttr
When using concrete table inheritance in the complete example below:
1. why must I return an empty `Column(Integer)` instead of `None`, or
receive the following error: *"ArgumentError: Mapper
Mapper|ARefClass|arefclass could not assemble any primary key columns for
mapped table 'arefclass'"*
2. why must I reference the class by name and not type in
`@declarad_attr`, unlike in a `classmethod`?
3. how does the `__tablename__` `declaredattr` actually behave any
differently than the `id` `declaradattr`?
from sqlalchemy import (
Column,
ForeignKey,
Integer,
create_engine,
)
from sqlalchemy.ext.declarative import (
AbstractConcreteBase,
declared_attr,
declarative_base,
has_inherited_table,
)
from sqlalchemy.orm import Session
Base = declarative_base()
class AClass(Base):
__tablename__ = 'aclass'
id = Column(Integer, primary_key=True)
class BClass(Base):
__tablename__ = 'bclass'
id = Column(Integer, primary_key=True)
class RefClass(AbstractConcreteBase, Base):
@declared_attr
def __tablename__(cls):
return cls.__name__.lower()
@declared_attr.cascading
def id(cls):
if cls.__name__ != 'RefClass':
column_name = '{}.id'.format(cls.ref.__tablename__)
return Column(ForeignKey(column_name), primary_key=True)
else:
# return # Fails as described in Q1
return Column(Integer)
class ARefClass(RefClass):
ref = AClass
class BRefClass(RefClass):
ref = BClass
engine = create_engine('sqlite://', echo=True)
Base.metadata.bind = engine
Base.metadata.create_all()
db = Session(engine)
Thanks!
Devin
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Self referential table, with composite key, and relationship(..., lazy='joined') is not eager loading as expected.
I'm going to link the docs for easy reference for any future readers: http://docs.sqlalchemy.org/en/latest/orm/self_referential.html?highlight=join_depth#configuring-self-referential-eager-loading I ended up using something link session.query(Game).options(joinedload(Game.opp)) because I didn't want to settle on one specific join_depth. Thank you for your suggestion. I was able to solve my issue because of fit. I am very happy with the abstractions available in SQLAlchemy to help with eager loading. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Self referential table, with composite key, and relationship(..., lazy='joined') is not eager loading as expected.
from sqlalchemy import Column, and_
from sqlalchemy import create_engine, Integer, String, Date, Enum
from sqlalchemy.orm import sessionmaker, relationship, remote, foreign
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.exc import DataError, IntegrityError
Base = declarative_base()
class Game(Base):
__tablename__ = 'game'
team = Column(String, primary_key=True, index=True)
opponent = Column(String, primary_key=True, index=True)
date = Column(Date, primary_key=True, index=True)
result = Column(Enum('win', 'loss', name='win_loss'), index=True)
points = Column(Integer)
opp = relationship(
'Game',
uselist=False,
lazy='joined',
primaryjoin=and_(
foreign(team) == remote(opponent),
foreign(opponent) == remote(team),
foreign(date) == remote(date)))
engine = create_engine('postgresql://buttons:buttons@localhost/ncaa', echo=True)
new_session = sessionmaker(engine)
# The following line produces a query like: select * from game
# The query does NOT contain a join.
new_session().query(Game).all()
The query on the last line off the example produces a simple "select * from
game" type query (the columns are spelled out of course). I was expecting
the lazy='joined' argument to result in a join. What am I doing wrong?
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
