Re: [sqlalchemy] SQLAlchemy 1.4 / 2.0 distribution
The problem is the part of my code base depends on third party library (aiopg) which should be compatible with SQLAlchemy 1.4 too but there some problems with it ( https://github.com/aio-libs/aiopg/issues/798#issuecomment-879815102 ) which make migration from 1.3 to 1.4 impossible for now =/ On Thursday, June 10, 2021 at 3:57:08 PM UTC+3 Mike Bayer wrote: > > > On Wed, Jun 9, 2021, at 7:21 PM, Marat Sharafutdinov wrote: > > The problem is that currently only the entire codebase can be migrated > from 1.3 to 1.4, even though it can be extremely difficult or too long to > do. > > I suggest the following way of pinning dependencies: > sqlalchemy==1.3 > sqlalchemy2==1.4 or sqlalchemy2==2.0 > > Then current codebase will continue to work because "sqlalchemy" package > stays at version 1.3 but it will be possible to use SQLAlchemy 1.4 / 2.0 > additionally to migrate not whole codebase but part by part by importing > "sqlalchemy2" package. > > > the entire migration process was designed around not having to do it like > this, and I spent months thinking about it. It would not be feasible for > two versions of sqlalchemy to exist at the same time because then the > entire import space would have to become "sqlalchemy2" and this is too > drastic of a change which would heavily impede migration. > > I think your assessment that "only the entire codebase can be migrated > from 1.3 to 1.4" is not true, most applications that run on 1.3 will run on > 1.4 with little to no changes and hundreds of users have already done so. > The 1.3-> 1.4 change is roughly comparable to the change that it has been > for other releases, like 1.0->1.1, 1.1->1.2 etc. > > > > On Thursday, June 10, 2021 at 2:10:48 AM UTC+3 Mike Bayer wrote: > > > hi there - > > having a separate project name on pypi doesn't solve any issue that isn't > already solved by using version pinning - the "sqlalchemy" name on pypi is > already at 1.4. the only way to install 1.3 is by requesting "pip install > sqlalchemy < 1.4". > > As you've probably seen, SQLAlchemy 1.4 /2.0 includes a very specific > upgrade path with step-by-step instructions at > https://docs.sqlalchemy.org/en/14/changelog/migration_20.html . > > > > On Wed, Jun 9, 2021, at 4:15 PM, Marat Sharafutdinov wrote: > > Currently I'm on SQLAlchemy 1.3 and there is a lot of work I have to do to > migrate to 1.4 / 2.0. I think it's good idea to distribute 1.4 / 2.0 > versions not only as "SQLAlchemy" project but as additional separate > "SQLAlchemy2" project too with initial 1.4 version and then 2.0. This will > give opportunity to have 1.3 and earlier version of SQLAlchemy which is > already in use and latest SQLAlchemy2 version to migrate gradually step by > step, in case of Flask for example, - one API method by another. Just one > additional way to migrate painlessly. > > > -- > 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+...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/730e4079-1751-4a9f-8f4a-f5fd7dde30f7n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/730e4079-1751-4a9f-8f4a-f5fd7dde30f7n%40googlegroups.com?utm_medium=email_source=footer> > . > > > > -- > 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+...@googlegroups.com. > > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/fa4b7c8f-ab62-466f-94f5-572ff6397328n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/fa4b7c8f-ab62-466f-94f5-572ff6397328n%40googlegroups.com?utm_medium=email_source=footer> > . > > > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlal
Re: [sqlalchemy] SQLAlchemy 1.4 / 2.0 distribution
The problem is that currently only the entire codebase can be migrated from 1.3 to 1.4, even though it can be extremely difficult or too long to do. I suggest the following way of pinning dependencies: sqlalchemy==1.3 sqlalchemy2==1.4 or sqlalchemy2==2.0 Then current codebase will continue to work because "sqlalchemy" package stays at version 1.3 but it will be possible to use SQLAlchemy 1.4 / 2.0 additionally to migrate not whole codebase but part by part by importing "sqlalchemy2" package. On Thursday, June 10, 2021 at 2:10:48 AM UTC+3 Mike Bayer wrote: > hi there - > > having a separate project name on pypi doesn't solve any issue that isn't > already solved by using version pinning - the "sqlalchemy" name on pypi is > already at 1.4. the only way to install 1.3 is by requesting "pip install > sqlalchemy < 1.4". > > As you've probably seen, SQLAlchemy 1.4 /2.0 includes a very specific > upgrade path with step-by-step instructions at > https://docs.sqlalchemy.org/en/14/changelog/migration_20.html . > > > > On Wed, Jun 9, 2021, at 4:15 PM, Marat Sharafutdinov wrote: > > Currently I'm on SQLAlchemy 1.3 and there is a lot of work I have to do to > migrate to 1.4 / 2.0. I think it's good idea to distribute 1.4 / 2.0 > versions not only as "SQLAlchemy" project but as additional separate > "SQLAlchemy2" project too with initial 1.4 version and then 2.0. This will > give opportunity to have 1.3 and earlier version of SQLAlchemy which is > already in use and latest SQLAlchemy2 version to migrate gradually step by > step, in case of Flask for example, - one API method by another. Just one > additional way to migrate painlessly. > > > -- > 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+...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/730e4079-1751-4a9f-8f4a-f5fd7dde30f7n%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/730e4079-1751-4a9f-8f4a-f5fd7dde30f7n%40googlegroups.com?utm_medium=email_source=footer> > . > > > -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/fa4b7c8f-ab62-466f-94f5-572ff6397328n%40googlegroups.com.
[sqlalchemy] SQLAlchemy 1.4 / 2.0 distribution
Currently I'm on SQLAlchemy 1.3 and there is a lot of work I have to do to migrate to 1.4 / 2.0. I think it's good idea to distribute 1.4 / 2.0 versions not only as "SQLAlchemy" project but as additional separate "SQLAlchemy2" project too with initial 1.4 version and then 2.0. This will give opportunity to have 1.3 and earlier version of SQLAlchemy which is already in use and latest SQLAlchemy2 version to migrate gradually step by step, in case of Flask for example, - one API method by another. Just one additional way to migrate painlessly. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/730e4079-1751-4a9f-8f4a-f5fd7dde30f7n%40googlegroups.com.
[sqlalchemy] Using CTE without JOIN
from sqlalchemy import Column, ForeignKey, Integer, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class Parent(Base):
__tablename__ = 'parents'
id = Column(Integer, primary_key=True)
class Child(Base):
__tablename__ = 'children'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey(Parent.id))
engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
parent_cte = session.query(Parent).cte('parent_cte')
query = session.query(Child).filter(Child.parent_id.in_(parent_cte)).all()
gives the following query:
SELECT children.id AS children_id, children.parent_id AS children_parent_id
FROM children
WHERE children.parent_id IN (SELECT parents.id FROM parents)
But I need appropriate CTE usage:
WITH parent_cte AS (
SELECT parents.id FROM parents
)
SELECT children.id AS children_id, children.parent_id AS children_parent_id
FROM children
WHERE children.parent_id IN (SELECT parent_cte.id FROM parent_cte)
How can I use CTE to make query go this way?
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/89759c32-5cf7-4ad7-ad67-02cea67e6407%40googlegroups.com.
[sqlalchemy] Join while filtering on M2M
from sqlalchemy import Column, ForeignKey, Integer, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
groups = relationship('UserGroup', lazy='selectin')
class Group(Base):
__tablename__ = 'groups'
id = Column(Integer, primary_key=True)
class UserGroup(Base):
__tablename__ = 'user_groups'
user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
group_id = Column(Integer, ForeignKey(Group.id), primary_key=True)
group = relationship(Group, lazy='joined', innerjoin=True)
engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
users = (
session.query(User)
.filter(User.groups.any(UserGroup.group.has(Group.id.in_((1, 2)
.all()
)
I need to get all users which are in groups with ids 1 or 2. This query
generates the following SQL:
SELECT users.id AS users_id
FROM users
WHERE EXISTS (SELECT 1
FROM user_groups
WHERE users.id = user_groups.user_id AND (EXISTS (SELECT 1
FROM groups
WHERE groups.id = user_groups.group_id AND groups.id IN (1, 2;
The question is how can "joined" relationship between UserGroup and Group
be involved?
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/76a7551d-200e-4fbc-920e-e313e1ec50d6%40googlegroups.com.
[sqlalchemy] Deletion of object with relationship(lazy='raise')
from sqlalchemy import Column, ForeignKey, Integer, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker
Base = declarative_base()
class Parent(Base):
__tablename__ = 'parents'
id = Column(Integer, primary_key=True)
class Child(Base):
__tablename__ = 'children'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey(Parent.id))
parent = relationship(Parent, lazy='raise', passive_deletes=True)
engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
# Add
parent = Parent()
session.add(parent)
session.flush()
child = Child(parent_id=parent.id)
session.add(child)
session.commit()
# Delete
child = session.query(Child).first()
session.delete(child)
session.commit()
Now I'm getting the following warning:
/.venv/lib/python3.8/site-packages/sqlalchemy/orm/relationships.py:2021:
SAWarning: On Child.parent, 'passive_deletes' is normally configured on one-
to-many, one-to-one, many-to-many relationships only.
If I change `parent` relationship as follows:
parent = relationship(Parent, lazy='raise', backref=backref('children',
passive_deletes=True))
then I would get the following exception:
sqlalchemy.exc.InvalidRequestError: 'Child.parent' is not available due to
lazy='raise'
How should it be made properly?
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/40f63feb-383e-4fee-8db6-21a757887c54%40googlegroups.com.
[sqlalchemy] Is it possible to use relationship(lazy='raise') after session.refresh?
from sqlalchemy import Column, ForeignKey, Integer, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import lazyload, relationship, sessionmaker
Base = declarative_base()
class Group(Base):
__tablename__ = 'groups'
id = Column(Integer, primary_key=True)
users = relationship('User', lazy='raise')
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
group_id = Column(Integer, ForeignKey('groups.id'))
engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
group = Group()
session.add(group)
session.commit()
group = session.query(Group).options(lazyload(Group.users)).first()
print('SUCCESS', group.users)
session.refresh(group)
print('FAILURE', group.users)
Is `session.expire(group)` is the only way to reload collections and
related items?
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/43ce8eb9-367d-43da-a371-0fa5e157e0de%40googlegroups.com.
Re: [sqlalchemy] Re: How to propagate column from joined subquery?
Thank you, Mike!
On Friday, May 25, 2018 at 6:36:34 PM UTC+3, Mike Bayer wrote:
>
> map Comment like this:
>
> from sqlalchemy.orm import query_expression
>
> class Comment(Base):
> __tablename__ = 'comment'
> id = Column(Integer, primary_key=True)
> news_id = Column(Integer, ForeignKey(News.id))
>
> is_read = query_expression()
>
>
> then you need to use options in your query to link the join to your
> subquery, as well as the is_read expression, to that object:
>
> from sqlalchemy.orm import contains_eager
>
> comment_read_query = (
> session.query(CommentRead).filter(
> CommentRead.comment_id == Comment.id,
> CommentRead.user_id == 1, # Interested in reads by specific user
> ).exists().label('is_read')
> )
> comment_query = session.query(Comment, comment_read_query).subquery()
> news = session.query(News).outerjoin(comment_query).options(
> contains_eager(News.comments, alias=comment_query).with_expression(
> Comment.is_read, comment_query.c.is_read)
> ).all()
>
>
>
>
> On Fri, May 25, 2018 at 11:28 AM, Marat Sharafutdinov <dec...@gmail.com
> > wrote:
> > The news has comments, the comments have readings by users.
> > I need to receive all the news with comments and readings of these
> comments
> > by a specific user.
> > The query is ok, but I can't get the value of the column `is_read` as an
> > attribute of the comment object.
> >
> > --
> > 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+...@googlegroups.com .
> > To post to this group, send email to sqlal...@googlegroups.com
> .
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
--
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] Re: How to propagate column from joined subquery?
The news has comments, the comments have readings by users. I need to receive all the news with comments and readings of these comments by a specific user. The query is ok, but I can't get the value of the column `is_read` as an attribute of the comment object. -- 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] How to propagate column from joined subquery?
Here is the code:
from sqlalchemy import Column, ForeignKey, Integer, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
engine = create_engine('postgresql://postgres@localhost/test_db')
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
class News(Base):
__tablename__ = 'news'
id = Column(Integer, primary_key=True)
comments = relationship('Comment')
class Comment(Base):
__tablename__ = 'comment'
id = Column(Integer, primary_key=True)
news_id = Column(Integer, ForeignKey(News.id))
class CommentRead(Base):
__tablename__ = 'comment_read'
comment_id = Column(Integer, ForeignKey(Comment.id), primary_key=True)
user_id = Column(Integer, ForeignKey(User.id), primary_key=True)
Base.metadata.create_all(engine)
# Initialize data
session.add_all([User(id=1), News(id=2)])
session.commit()
session.add(Comment(id=3, news_id=2))
session.commit()
session.add(CommentRead(comment_id=3, user_id=1))
session.commit()
# Make query
comment_read_query = (
session.query(CommentRead).filter(
CommentRead.comment_id == Comment.id,
CommentRead.user_id == 1, # Interested in reads by specific user
).exists().label('is_read')
)
comment_query = session.query(Comment, comment_read_query).subquery()
news = session.query(News).outerjoin(comment_query).all()
# How can I get `is_read` column value as comment attribute?
assert news[0].comments[0].is_read
--
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.
