What is the difference between conn.execute('some string') and conn.execute(text('some string')) in SQLAlchemy?

Question

What is the difference between conn.execute('some string') and conn.execute(text('some string')) in SQLAlchemy?

In the above, conn is obtained via conn = engine.connect(). And the engine is obtained via the create_engine method. The method text() is imported from sqlalchemy.sql`.

I see both conn.execute('some string') and conn.execute(text('some string')) occur in tutorials, but the difference is not explained. See for example here

Kind regards

Answer 1

This one is answered pretty well by the official documentation for text():

The advantages text() provides over a plain string are backend-neutral support for bind parameters, per-statement execution options, as well as bind parameter and result-column typing behavior, allowing SQLAlchemy type constructs to play a role when executing a statement that is specified literally.

Of those the one you might use more commonly is the backend-neutral support for bind parameters. PEP 249 — DB-API 2.0 spec specifies a bunch of different paramstyles an implementation can use. For example the sqlite3 module uses qmark, while psycopg2 uses format and pyformat. Using text() you can always just use the named style and SQLAlchemy will handle converting that to what your DB-API driver is using.

Another one you might run into is defining bind parameter behaviour when using an IN clause with a driver that does not support something like psycopg2s tuples adaptation. Traditionally you would have to format the required number of placeholders to your query, but recent enough versions of SQLAlchemy support "expanding" bind parameters that remove the need for manual handling and allow treating a sequence as a single parameter.