Hi,
I am using sqlalchemy 1.1.5 with python 3.5 and postgresql (9.4).
I have encountered what I think is a bug, it occurs when:
* no schema is set in metadata or tables (schema = None)
* using schema_translate_map={None: 'other_schema_name'}.
It happens when joining an aliased table. Basically, when the statement is
rendered the alias ends up prefixed with "other_schema_name" in the
condition expression.
See below for code to reproduce it.
I don't know if this is an unintended setup; I use it in particular to run
each test session in its own, dedicated schema on a real database.
Otherwise I have no reason to set a schema name in my code. For now as a
workaround for the tests session I patch metadata.schema and tables schema
with a constant string, and then remap this constant name to the temporary
schema.
Here is the output of the script below:
ProgrammingError: (psycopg2.ProgrammingError) invalid reference to
FROM-clause entry for table "users_groups_1"
LINE 2: ...ers_groups_1 ON translated_schema_name.users.id = translated...
^
HINT: There is an entry for table "users_groups_1", but it cannot be
referenced from this part of the query.
[SQL: 'SELECT translated_schema_name.users.id AS users_id,
translated_schema_name.users.name AS users_name,
translated_schema_name.users.fullname AS users_fullname,
translated_schema_name.users.password AS users_password \nFROM
translated_schema_name.users JOIN translated_schema_name.users_groups AS
users_groups_1 ON translated_schema_name.users.id =
translated_schema_name.users_groups_1.user_id JOIN
translated_schema_name.groups ON translated_schema_name.groups.id =
translated_schema_name.users_groups_1.group_id JOIN
translated_schema_name.users_groups AS users_groups_2 ON
translated_schema_name.users.id =
translated_schema_name.users_groups_2.user_id JOIN
translated_schema_name.groups AS groups_1 ON
translated_schema_name.groups_1.id =
translated_schema_name.users_groups_2.group_id \nWHERE
translated_schema_name.groups.name = %(name_1)s AND
translated_schema_name.groups_1.name = %(name_2)s'] [parameters: {'name_2':
'other', 'name_1': 'some'}]
What is incorrectly generated:
JOIN translated_schema_name.users_groups AS users_groups_2 ON
translated_schema_name.users.id =
translated_schema_name.users_groups_2.user_id
The right expression should be: "users_groups_2.user_id"
Here is the code - just adjust the connection string to your local setup.
import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
engine = sa.create_engine('postgres://user:password@localhost:5432/testdb',
echo=True)
metadata = sa.MetaData()
Base = declarative_base(metadata=metadata)
SCHEMA_NAME = 'translated_schema_name'
class Group(Base):
__tablename__ = 'groups'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String)
user_groups = sa.Table(
'users_groups', metadata,
sa.Column('user_id', sa.Integer, sa.ForeignKey('users.id'), primary_key=
True),
sa.Column('group_id', sa.Integer, sa.ForeignKey('groups.id'),
primary_key=True),
)
class User(Base):
__tablename__ = 'users'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String)
fullname = sa.Column(sa.String)
password = sa.Column(sa.String)
groups = sa.orm.relationship(Group, secondary=user_groups)
GroupAlias = sa.orm.aliased(Group)
conn = engine.connect().execution_options(schema_translate_map={None:
SCHEMA_NAME})
with conn.begin() as tr:
conn.execute(sa.schema.CreateSchema(SCHEMA_NAME))
metadata.create_all(bind=conn)
session = sa.orm.Session(bind=conn)
query = session.query(User) \
.join(Group, User.groups) \
.join(GroupAlias, User.groups) \
.filter(Group.name == 'some', GroupAlias.name == 'other')
query.all() # Boom
tr.rollback()
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
