1

I have complicated queries like

if schema_arg:
 dynamic_schema = f'{schema_arg}.'
else:
 dynamic_schema = ''
 query_to_compile = f"CREATE TABLE AS {dynamic_schema}{dynamic_table} AS SELECT * FROM ({query_first} UNION ALL {query_second}) GROUP BY f {field_1} {field_2} {field_3}"

Obviously, this isn't usually a good idea to use strings like this (there doesn't seem to be a good dialect-based SQL escaping). I tried the psycopg2.SQL() way but it failed.

Is the only way to use the object oriented way?

Does anyone have a script that can convert any complicated raw SQL into object oriented way for SQLAlchemy?

Improve this question
4
  • Sounds like you should be asking about "I tried the psycopg2.SQL() way but it failed", i.e. an XY problem. Commented Jan 26, 2021 at 7:12
  • No no, I don't want to use other libraries, there should be a way to do this in SQLAlchemy. They likely use something like psycopg2.SQL underneath it all which is interfering when I try to use psycopg2.SQL at the higher level Commented Jan 26, 2021 at 17:33
  • They don't, and the psycopg2 tools are separate and specifically for handling "raw" SQL snippets. If you want to use SQLA only, you're far better off using Core, though it does not support CREATE TABLE ... AS out of the box. The way SQLA does it is that it compiles the SQLA constructs down to raw SQL, applying escaping/quoting etc. See stackoverflow.com/questions/30575111/… Commented Jan 27, 2021 at 16:16
  • (There are of course some similarities between Core and psycopg2.SQL) Commented Jan 27, 2021 at 16:28

1 Answer 1

Reset to default
0

Hope I'm not underestimating your problem here... You can put your query into a text string and then have a SQLAlchemy engine run it. Here's an example...

common.qry_engine.execute('ALTER TABLE %s ADD COLUMN %s %s' %
                          ('`' + self.tbl.schema + '`.`' + self.tbl.name + '`',
                           '`' + self.outputs[new_col] + '`', data_type))

Here is more info: https://docs.sqlalchemy.org/en/14/core/tutorial.html#using-textual-sql (BTW, common is just the module where I create my connection to the DB)

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

That's exactly what I'm doing above. But I am pretty sure it's unsafe because I want some of the function arguments like "tblname" and "schema" to be escaped because they may be untrusted values.
For what it's worth, my strings are surrounded by back-quotes ('`'). From what I understand, this provides a modicum of SQL Injection protection b/c these will be treated as strings and, as such, can't be executed. I suspect that more can be done, though. People elsewhere have recommended Alembic for dynamic DDL although I've avoided it so far b/c it looks like a lot of overhead to learn for just a few statements.
Yeah I just feel like SQLAlchemy has nice docs but only for some parts of it. For the manual stuff, not so much except for literal values. Doing it the OO way is harder in complex queries. Likely because I should simplify my queries but sometimes you can't. I may try whitelisting some keywords. ensure the validation checks if they are alphanumeric (like fieldnames).
FWIW, I never use the OO/ORM components. Might be because I've been writing SQL for thirty years and Python for about five. To me,, the ORM seems too confining for the stuff I do. Seems like you're running into the same issues.
Yes exactly. Maybe I need to just search for an SQL Safe function that works for my thing. I bet it's within the source code of SQLAlchemy, but I'd need to dig.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.