Re: [sqlalchemy] forcing (composite) primary key order?
yes use the PrimaryKeyConstraint() construct
https://docs.sqlalchemy.org/en/13/core/constraints.html?highlight=primarykeyconstraint#sqlalchemy.schema.PrimaryKeyConstraint
here you'd want to put it in your __table_args__ and remove primary_key=True
from each column
On Mon, Jun 1, 2020, at 6:16 PM, Jonathan Vanasco wrote:
> is it possible to force the order of primary keys?
>
> for example in this setup...
>
> class AcmeDnsServer2Domain(Base):
> __tablename__ = "acme_dns_server_2_domain"
> acme_dns_server_id = sa.Column(
> sa.Integer, sa.ForeignKey("acme_dns_server.id"), primary_key=True
> )
> domain_id = sa.Column(
> sa.Integer, sa.ForeignKey("domain.id"), primary_key=True
> )
>
> I want to influence the creation of the primary key to be
> ["acme_dns_server_id", "domain_id"]
>
> In several projects I've worked on, the order has been important. (it can
> affect query planning and caching)
>
> I thought I had seen something in the docs several years ago, but I just
> spend a few fruitless minutes trying to find it.
>
> --
> 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/6728fb39-f5d9-4c97-a8b3-7df7fd918efa%40googlegroups.com
>
> .
--
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/c3a48f3a-ad38-4881-9bb8-da8006d45cf3%40www.fastmail.com.
[sqlalchemy] forcing (composite) primary key order?
is it possible to force the order of primary keys?
for example in this setup...
class AcmeDnsServer2Domain(Base):
__tablename__ = "acme_dns_server_2_domain"
acme_dns_server_id = sa.Column(
sa.Integer, sa.ForeignKey("acme_dns_server.id"), primary_key=True
)
domain_id = sa.Column(
sa.Integer, sa.ForeignKey("domain.id"), primary_key=True
)
I want to influence the creation of the primary key to be
["acme_dns_server_id",
"domain_id"]
In several projects I've worked on, the order has been important. (it can
affect query planning and caching)
I thought I had seen something in the docs several years ago, but I just
spend a few fruitless minutes trying to find it.
--
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/6728fb39-f5d9-4c97-a8b3-7df7fd918efa%40googlegroups.com.
Re: [sqlalchemy] Using CTE without JOIN
thanks for the easy test. you want your IN to be against "SELECT * FROM cte" so
you need to tell it to select() from that CTE:
parent_cte = session.query(Parent).cte("parent_cte")
query = (
session.query(Child).filter(Child.parent_id.in_(parent_cte.select())).all()
)
if you just put the CTE in there it is interpreting that as the SELECT directly
and skipping the CTE part, ideally that would raise, however in 1.4 / master it
is in fact interpreting the CTE as a "SELECT" from that CTE (e.g. what you
expected), but I'm not sure I like it doing that since it is implicitly
generating a SELECT.
On Mon, Jun 1, 2020, at 5:47 PM, Marat Sharafutdinov wrote:
> 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 -
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/e422d73b-aa0a-480b-945c-81ebdcda6bd8%40www.fastmail.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.
