Hi,
When I do this in Python:
metadata = MetaData()
person = Table('person', metadata, Column('name', String), Column('id',
Integer))
base_query = select(person.c).alias('person_alias').select()
query = base_query.where(base_query.c.id == 1)
I expect to get something like:
SELECT person_alias.name,
person_alias.id
FROM
(SELECT person.name AS name,
person.id AS id
FROM person) AS person_alias
WHERE person_alias.id = 1
But instead I get:
SELECT person_alias.name,
person_alias.id
FROM
(SELECT person.name AS name,
person.id AS id
FROM person) AS person_alias,
(SELECT person_alias.name AS name,
person_alias.id AS id
FROM
(SELECT person.name AS name,
person.id AS id
FROM person) AS person_alias)
WHERE id = 1
I've tried various combinations of correlated and uncorrelated subqueries,
and expressing the column in the condition in terms of the subquery or
underlying table (see attached file). In each case, the FROM clause is a
cross product (of the subquery with either itself or with the underlying
table) instead of just the subquery.
If the where() doesn't reference any columns, like this:
query = base_query.where(True)
Then I get what I'd expect:
SELECT person_alias.name,
person_alias.id
FROM
(SELECT person.name AS name,
person.id AS id
FROM person) AS person_alias
WHERE true
I'm running sqlalchemy 1.0.9 with Python 3.4.2 on Fedora 22. Any help or
insight would be greatly appreciated.
Thanks,
-- Laura
P.S. In my real-life application, the base queries are typically unions or
intersections.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
from sqlalchemy import Table, Column, Integer, String, MetaData, create_engine, select
from sqlalchemy.dialects import postgres
import sys
def print_query(query, pretty=True):
query_string=str(query.compile(compile_kwargs={"literal_binds": True}, dialect=postgres.dialect()))
if pretty:
print(sqlparse.format(query_string, reindent=True))
else:
print(query_string)
def print_examples(pretty):
metadata = MetaData()
person = Table('person', metadata, Column('name', String), Column('id', Integer))
base_query = select(person.c).alias('person_alias').select()
print("=== Trivial query ===")
query = base_query.where(True)
print_query(query, pretty)
print("\n=== Query with subquery condition ===")
query = base_query.where(base_query.c.id == 1)
print_query(query, pretty)
print("\n=== Query with corresponding_column condition ===")
query = base_query.where(base_query.corresponding_column(person.c.id) == 1)
print_query(query, pretty)
print("\n=== Query with underlying table condition ===")
query = base_query.where(person.c.id == 1)
print_query(query, pretty)
correlated_base_query = base_query.correlate(person)
print("\n=== Explicitly correlated query with subquery condition ===")
query = correlated_base_query.where(correlated_base_query.c.id == 1)
print_query(query, pretty)
print("\n=== Explicitly correlated query with corresponding_column condition ===")
query = correlated_base_query.where(correlated_base_query.corresponding_column(person.c.id) == 1)
print_query(query, pretty)
print("\n=== Explicitly correlated query with underlying table condition ===")
query = correlated_base_query.where(person.c.id == 1)
print_query(query, pretty)
uncorrelated_base_query = base_query.correlate(None)
print("\n=== Uncorrelated query with subquery condition ===")
query = uncorrelated_base_query.where(uncorrelated_base_query.c.id == 1)
print_query(query, pretty)
print("\n=== Uncorrelated query with corresponding_column condition ===")
query = uncorrelated_base_query.where(uncorrelated_base_query.corresponding_column(person.c.id) == 1)
print_query(query, pretty)
print("\n=== Uncorrelated query with underlying table condition ===")
query = uncorrelated_base_query.where(person.c.id == 1)
print_query(query, pretty)
if __name__ == '__main__':
pretty = False
try:
import sqlparse
pretty = True
except ImportError:
pass
print_examples(pretty)
sys.exit(0)
