On Nov 24, 2013, at 3:34 PM, Joseph Casale <jcas...@gmail.com> wrote:
> I have some sqlite tables such as:
>
> CREATE TABLE table_a (
> id INTEGER NOT NULL,
> table_b_id INTEGER NOT NULL,
> name VARCHAR,
> PRIMARY KEY (id),
> FOREIGN KEY(table_b_id) REFERENCES table_b (id) ON DELETE CASCADE
> );
>
> CREATE TABLE table_b (
> id INTEGER NOT NULL,
> table_c_id INTEGER NOT NULL,
> name VARCHAR,
> PRIMARY KEY (id),
> FOREIGN KEY(table_c_id) REFERENCES table_c (id) ON DELETE CASCADE
> );
>
> CREATE TABLE table_c (
> id INTEGER NOT NULL,
> name VARCHAR,
> PRIMARY KEY (id)
> );
>
> I was hoping to leverage the natural joins I thought might exist, but a query
> such as:
> query = session.query(table_a).join(table_b).join(table_c)
> doesn't return the join() tables for each record.
error message regarding “no foreign keys” ? I’m assuming you’re using
reflection. SQLite foreign key directives do reflect so I’d make sure they
are present on the Table objects, check table.foreign_keys.
works fine on this end:
from sqlalchemy import *
from sqlalchemy.orm import Session
e = create_engine("sqlite://", echo=True)
e.execute("""
CREATE TABLE table_c (
id INTEGER NOT NULL,
name VARCHAR,
PRIMARY KEY (id)
);
""")
e.execute("""
CREATE TABLE table_b (
id INTEGER NOT NULL,
table_c_id INTEGER NOT NULL,
name VARCHAR,
PRIMARY KEY (id),
FOREIGN KEY(table_c_id) REFERENCES table_c (id) ON DELETE CASCADE
);
""")
e.execute("""
CREATE TABLE table_a (
id INTEGER NOT NULL,
table_b_id INTEGER NOT NULL,
name VARCHAR,
PRIMARY KEY (id),
FOREIGN KEY(table_b_id) REFERENCES table_b (id) ON DELETE CASCADE
);
""")
m = MetaData()
m.reflect(e)
t1, t2, t3 = m.tables['table_a'], m.tables['table_b'], m.tables['table_c']
sess = Session()
print sess.query(t1).join(t2).join(t3)
output:
SELECT table_a.id AS table_a_id, table_a.table_b_id AS table_a_table_b_id,
table_a.name AS table_a_name
FROM table_a JOIN table_b ON table_b.id = table_a.table_b_id JOIN table_c ON
table_c.id = table_b.table_c_id
>
> Something like does:
> query = session.query(table_a, table_b, table_c).\
> join(table_b, table_b.id == table_a.table_b_id).\
> join(table_c, table_c.id == table_b.table_c_id).\
> all()
>
> I am pretty sure I am missing something, what requirements does the first
> query have
> that I am missing?
>
> Thanks!
> jlc
>
>
>
>
> --
> 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 http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
