2
engine = sqlalchemy.engine.create_engine('mysql://py:123@localhost/py', echo=True)
con = engine.connect()
res = con.execute("insert into user set name = %s", 'A')

How do I write this query to a (.sql) file (or how do I assign it to a variable)? I'd rather not do all the escaping myself.

XTF
  • 1,091
  • 1
  • 13
  • 31

1 Answers1

0

You can use literal_binds to produce an SQL statement with the values embedded:

import sqlalchemy as sa

# …

user = sa.Table("user", sa.MetaData(), autoload_with=engine)
user_insert = sa.insert(user).values(name="A")
compiled_text = str(
    user_insert.compile(compile_kwargs={"literal_binds": True})
)
print(compiled_text)
# INSERT INTO "user" (name) VALUES ('A')

However, be careful about using that with untrusted inputs as there is still the possibility of SQL injection issues.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • If this is insecure, doesn't that mean proper escaping is NOT being done? – XTF Apr 25 '21 at 16:33
  • SQLAlchemy does basic escaping, e.g. `O'Leary` → `'O''Leary'`, but the best choice for integrating data into an SQL statement is to pass the values as parameters and let the DBAPI layer *for that specific database* protect against the more esoteric injection cases (e.g., using encoded characters instead of literals). SQLAlchemy's `.compile()` method can also be used to generate the parameterized query and the parameter values separately. – Gord Thompson Apr 25 '21 at 17:00