[sqlalchemy] Re: sqlite setting foreign_keys=off temporarily
The first two things I would look into: 1. Check the sqlite install/version that SqlAlchemy uses. It is often NOT the same as the basic operating system install invoked in your terminal. Sometimes that version does not have the functionality you need. 2. Check the transactional isolation level in sqlalchemy and that you are committing if needed. IIRC, the sqlite client defaults to non-transactional but the python library defaults to transactional. I could be wrong on this. Someone else may be able to look through your code and give more direct answers. On Saturday, August 7, 2021 at 11:19:48 PM UTC-4 RexE wrote: > On startup of my program, my in-memory sqlite DB needs to turn off foreign > key enforcement temporarily (in order to insert data from a different > sqlite DB). However, it seems my command to set foreign_keys back on has no > effect. See the attached MRE. > > I expect this output: > after turning back on [(1,)] > > But I get this: > after turning back on [(0,)] > > Interestingly, if I comment out the insert statement (or put it before the > toggle) the code works fine. > > Any ideas? I tried replicating this in the sqlite CLI but it works as I > expect: > > SQLite version 3.35.4 2021-04-02 15:20:15 > Enter ".help" for usage hints. > sqlite> pragma foreign_keys; > 0 > sqlite> pragma foreign_keys=on; > sqlite> pragma foreign_keys; > 1 > sqlite> create table groups (id primary key); > sqlite> pragma foreign_keys=off; > sqlite> pragma foreign_keys; > 0 > sqlite> insert into groups default values; > sqlite> pragma foreign_keys=on; > sqlite> pragma foreign_keys; > 1 > > I'm using SQLAlchemy==1.3.22. > > 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/ce24e2db-b526-4f9b-bbcb-d0b2ead7b701n%40googlegroups.com.
Re: [sqlalchemy] Join multiple tables with association tables
*Is the relationship between Fact and Info meant to be many-to-many? And
likewise the relationship between Text and Info?*
You are right about that.
Your code did exactly what I wanted. Thank you so much!
I figured it could have to do somethin with a subquery but I'm just
starting with sql so it is quiet difficult for me to understand. Your
explanation really helped.
Thanks again,
Timo
Simon King schrieb am Dienstag, 10. August 2021 um 11:13:32 UTC+2:
> It's difficult to tell from your code what your intention is. Is the
> relationship between Fact and Info meant to be many-to-many? And likewise
> the relationship between Text and Info?
>
> Forgetting SQLAlchemy for a moment, what is the SQL that you want to
> produce?
>
>
> Does the script below do what you want?
>
>
> import sqlalchemy as sa
> import sqlalchemy.orm as saorm
> from sqlalchemy.ext.declarative import declarative_base
>
>
> Base = declarative_base()
>
>
> facts_info = sa.Table(
> "facts_info",
> Base.metadata,
> sa.Column(
> "fact_id", sa.Integer, sa.ForeignKey("fact.id"), primary_key=True
> ),
> sa.Column(
> "info_id", sa.Integer, sa.ForeignKey("info.id"), primary_key=True
> ),
> )
>
>
> info_text = sa.Table(
> "info_text",
> Base.metadata,
> sa.Column(
> "info_id", sa.Integer, sa.ForeignKey("info.id"), primary_key=True
> ),
> sa.Column(
> "text_id", sa.Integer, sa.ForeignKey("text.id"), primary_key=True
> ),
> )
>
>
> class Fact(Base):
> __tablename__ = "fact"
>
> id = sa.Column(sa.Integer, primary_key=True)
> fact = sa.Column(sa.String(500), nullable=False, unique=True)
> created_at = sa.Column(sa.DateTime)
> updated_at = sa.Column(sa.DateTime)
>
> info = saorm.relationship(
> "Info", secondary=facts_info, back_populates="facts"
> )
>
>
> class Info(Base):
> __tablename__ = "info"
>
> id = sa.Column(sa.Integer, primary_key=True)
> filename = sa.Column(sa.String(50))
> format = sa.Column(sa.String(10))
>
> facts = saorm.relationship(
> "Fact", secondary=facts_info, back_populates="info"
> )
> text = saorm.relationship(
> "Text", secondary=info_text, back_populates="info"
> )
>
>
> class Text(Base):
> __tablename__ = "text"
>
> id = sa.Column(sa.Integer, primary_key=True)
> text = sa.Column(sa.String(1000))
>
> # Relationships
> info = saorm.relationship(
> "Info", secondary=info_text, back_populates="text"
> )
>
>
> if __name__ == "__main__":
> engine = sa.create_engine("sqlite://", echo=True)
> Base.metadata.create_all(engine)
>
> Session = saorm.sessionmaker(bind=engine)
>
> session = Session()
>
> # two facts
> facts = [Fact(fact="factone"), Fact(fact="facttwo")]
> # three infos, first two are associated with both facts, third is
> # only linked to second fact
> infos = [
> Info(filename="infoone", facts=facts),
> Info(filename="infotwo", facts=facts),
> Info(filename="infothree", facts=facts[1:]),
> ]
> # three texts, first two linked to first info instance, third
> # linked to third info instance
> texts = [
> Text(text="textone", info=[infos[0]]),
> Text(text="texttwo", info=[infos[0]]),
> Text(text="textthree", info=[infos[2]]),
> ]
> session.add_all(facts + infos + texts)
> session.flush()
>
> # Joining to both facts_info and info_text in the same query
> # doesn't really make sense, because it would end up producing a
> # cartesian product between those tables. Instead we'll use a
> # subquery against facts_info to select the info ids we are
> # interested in.
> info_ids = (
> session.query(facts_info.c.info_id)
> .filter(facts_info.c.fact_id == 1)
> )
> query = (
> session.query(Info, Text)
> .filter(Info.id.in_(info_ids))
> .join(Info.text)
> )
>
> # Note that this only outputs Info objects that have at least one
> # text object associated with them. If you want to include Info
> # objects without a related Text object, change the
> # ".join(Info.text)" to ".outerjoin(Info.text)"
> for (info, text) in query.all():
> print("Info(filename=%r) Text(text=%r)" % (info.filename,
> text.text))
>
>
>
> Hope that helps,
>
> Simon
>
>
> On Mon, Aug 9, 2021 at 10:48 PM 'timbecks' via sqlalchemy <
> sqlal...@googlegroups.com> wrote:
>
>> I am trying to figure out the correct join query setup within SQLAlchemy,
>> but I can't seem to get my head around it.
>>
>> I have the following table setup (simplified, I left out the
>> non-essential fields):
>>
>> [image: Unbenannt.png]
>>
>> [image: Unbenannt2.png]
>>
>> The facts are associated to info, info is associated to text. Text and
>> facts aren't directly associated.
>>
>> I would like to join them all together but can't figure out to do so.
>>
>> In this
Re: [sqlalchemy] Join multiple tables with association tables
It's difficult to tell from your code what your intention is. Is the
relationship between Fact and Info meant to be many-to-many? And likewise
the relationship between Text and Info?
Forgetting SQLAlchemy for a moment, what is the SQL that you want to
produce?
Does the script below do what you want?
import sqlalchemy as sa
import sqlalchemy.orm as saorm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
facts_info = sa.Table(
"facts_info",
Base.metadata,
sa.Column(
"fact_id", sa.Integer, sa.ForeignKey("fact.id"), primary_key=True
),
sa.Column(
"info_id", sa.Integer, sa.ForeignKey("info.id"), primary_key=True
),
)
info_text = sa.Table(
"info_text",
Base.metadata,
sa.Column(
"info_id", sa.Integer, sa.ForeignKey("info.id"), primary_key=True
),
sa.Column(
"text_id", sa.Integer, sa.ForeignKey("text.id"), primary_key=True
),
)
class Fact(Base):
__tablename__ = "fact"
id = sa.Column(sa.Integer, primary_key=True)
fact = sa.Column(sa.String(500), nullable=False, unique=True)
created_at = sa.Column(sa.DateTime)
updated_at = sa.Column(sa.DateTime)
info = saorm.relationship(
"Info", secondary=facts_info, back_populates="facts"
)
class Info(Base):
__tablename__ = "info"
id = sa.Column(sa.Integer, primary_key=True)
filename = sa.Column(sa.String(50))
format = sa.Column(sa.String(10))
facts = saorm.relationship(
"Fact", secondary=facts_info, back_populates="info"
)
text = saorm.relationship(
"Text", secondary=info_text, back_populates="info"
)
class Text(Base):
__tablename__ = "text"
id = sa.Column(sa.Integer, primary_key=True)
text = sa.Column(sa.String(1000))
# Relationships
info = saorm.relationship(
"Info", secondary=info_text, back_populates="text"
)
if __name__ == "__main__":
engine = sa.create_engine("sqlite://", echo=True)
Base.metadata.create_all(engine)
Session = saorm.sessionmaker(bind=engine)
session = Session()
# two facts
facts = [Fact(fact="factone"), Fact(fact="facttwo")]
# three infos, first two are associated with both facts, third is
# only linked to second fact
infos = [
Info(filename="infoone", facts=facts),
Info(filename="infotwo", facts=facts),
Info(filename="infothree", facts=facts[1:]),
]
# three texts, first two linked to first info instance, third
# linked to third info instance
texts = [
Text(text="textone", info=[infos[0]]),
Text(text="texttwo", info=[infos[0]]),
Text(text="textthree", info=[infos[2]]),
]
session.add_all(facts + infos + texts)
session.flush()
# Joining to both facts_info and info_text in the same query
# doesn't really make sense, because it would end up producing a
# cartesian product between those tables. Instead we'll use a
# subquery against facts_info to select the info ids we are
# interested in.
info_ids = (
session.query(facts_info.c.info_id)
.filter(facts_info.c.fact_id == 1)
)
query = (
session.query(Info, Text)
.filter(Info.id.in_(info_ids))
.join(Info.text)
)
# Note that this only outputs Info objects that have at least one
# text object associated with them. If you want to include Info
# objects without a related Text object, change the
# ".join(Info.text)" to ".outerjoin(Info.text)"
for (info, text) in query.all():
print("Info(filename=%r) Text(text=%r)" % (info.filename,
text.text))
Hope that helps,
Simon
On Mon, Aug 9, 2021 at 10:48 PM 'timbecks' via sqlalchemy <
sqlalchemy@googlegroups.com> wrote:
> I am trying to figure out the correct join query setup within SQLAlchemy,
> but I can't seem to get my head around it.
>
> I have the following table setup (simplified, I left out the non-essential
> fields):
>
> [image: Unbenannt.png]
>
> [image: Unbenannt2.png]
>
> The facts are associated to info, info is associated to text. Text and
> facts aren't directly associated.
>
> I would like to join them all together but can't figure out to do so.
>
> In this example I would like to get all instaces of "Info" that are
> associated to Fact.id = 1 and all "Text" instances that are associated to
> that "Info" instance. I came up with
> select(Info, Text)
> .join(facts_info)
> .join(Facts)
> .join(info_text)
> .join(Text)
> here(Facts.id ==1)
>
> But it obviously gives me an error.
>
> --
> 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.
