[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.