Menu
I have a sql like:
DBSession().query(Model).filter(***)
and I want to explain this sql using SQLAlchemy.
231
SQLAlchemy supports MySQL starting with version 5.0. 2 through modern releases, as well as all modern versions of MariaDB.
Finally, you have the hostname or IP address of the database and the database name. These data are all you need to establish a connection. The port is optional, but SQLAlchemy is smart enough to know the MySQL database resides at port 3306. Finally, you create the connection object and invoke the connect method.
SQLAlchemy provides a nice “Pythonic” way of interacting with databases. So rather than dealing with the differences between specific dialects of traditional SQL such as MySQL or PostgreSQL or Oracle, you can leverage the Pythonic framework of SQLAlchemy to streamline your workflow and more efficiently query your data.
In MySQL, EXPLAIN can be used in front of a query beginning with SELECT , INSERT , DELETE , REPLACE , and UPDATE . For a simple query, it would look like the following: EXPLAIN SELECT * FROM foo WHERE foo. bar = 'infrastructure as a service' OR foo.
You want to compile your SQLAlchemy query into a string; use the correct dialect and use literal values for bind parameters
query = DBSession().query(Model).filter(***)
# you should have an engine reference used to create the DBSession object
sql = query.statement.compile(engine, compile_kwargs={"literal_binds": True})
You can then use that to ask for a MySQL explanation:
DBSession().execute(f'EXPLAIN {sql}')
You can prepare your explain sql string like this:
'EXPLAIN' + query.compile(
compile_kwargs={"literal_binds": True},
dialect=mysql.dialect()
)
Advantage is query has parameters filled in.
41
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
