Description
table(...) in the expression language does not accept a schema (presumably, intentionally). When wanting to use it for schemas other than the default on some dialects (Oracle, for example), the resulting quoted identifier will not work.
sqlalchemy.select(["*"]).select_from(sqlalchemy.table("someschema.SOMETABLE"))produces (with the oracle dialect)
SELECT *
FROM "someschema.SOMETABLE" which is invalid. However, it happens that because table is produced from TableClause, setting .schema on the resulting table allows a correct query.
the_table = sqlalchemy.table("SOMETABLE")
the_table.schema = "someschema"
sqlalchemy.select(["*"]).select_from(the_table)gives
SELECT *
FROM "someschema"."SOMETABLE" Possible Solution
Is it possible for TableClause to take a keyword-only schema parameter? Or would this cause breaking changes when used with sqlalchemy.Table when a table is bound to a schema by metadata?
If that is not possible, is it acceptable for table do slightly more work than the public_factory and accept this keyword-only argument itself - since that appears to be the intended entry-point for TableClause when not using .Table.
Or are either of these changes too fragile? I would be happy to work on a PR for this if any solution is acceptable. I think this is a fairly niche feature that is already implicitly supported by the expression-language "compiler" with .schema - but it would be nice to have on construction.
Alternatively
One can do as I do now by setting .schema after creating the TableClause with table.
Metadata
Metadata
Assignees
Labels
Type
Projects
Relationships
Development
Participants
Activity
zzzeek commentedon May 5, 2020
this should be possible, there might be a lot of cases to get working but then again maybe not. it's just one extra attribute, and because it's being added, there's no issue of backwards incompatibility. I don't see why it would interfere with the Table subclass, which would continue to accommodate "schema" and "name" in its own way.
looking at TableClause that is likely the reason it doesnt have any options, because we are with Python 2 compatibility for the next year at least, we would have to add **kw to the class, so likely there should be "schema = kw.pop('schema', None)" and then "if kw: raise ArgumentError".
sure !
a very effective workaround that is probably happening in the test suite too.
Fixes sqlalchemy#5309
Fixes: sqlalchemy#5309
Fixes: sqlalchemy#5309
sqla-tester commentedon May 6, 2020
Dylan Modesitt has proposed a fix for this issue in the master branch:
Add 'schema' parameter to table https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/1944
sqla-tester commentedon May 10, 2020
Dylan Modesitt has proposed a fix for this issue in the master branch:
Add 'schema' parameter to table https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/1944
sqla-tester commentedon May 15, 2020
Dylan Modesitt has proposed a fix for this issue in the rel_1_3 branch:
Add 'schema' parameter to table https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/1976
2 remaining items