this is a bug
https://bitbucket.org/zzzeek/sqlalchemy/issues/3924/schema_translate_map-is-running-on-aliases
is added.
On 02/28/2017 12:05 PM, Bertrand Mathieu wrote:
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.
|
importsqlalchemy assa
fromsqlalchemy.ext.declarative importdeclarative_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'
classGroup(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),
)
classUser(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})
withconn.begin()astr:
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 sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
