SQLAlchemy how to escape a bind parameter inside of text()?

user_1391717 0
Viewed 426

How can I escape a : inside of a string passed to text() to prevent SQLAlchemy from treating it like a bindparameter?

conn.execute(text("select 'My favorite emoticon is :p' from dual")).fetchone()

Will result in:

sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) A value is required for bind parameter 'p'
(Background on this error at: http://sqlalche.me/e/14/cd3x)

'

It's a bit confusing because from the context of selecting a string from the database select 'foo :bar baz' a bindparameter doesn't make much sense here.

It looks like I can use a \ to escape this, but it says it is deprecated:

>>> conn.execute(text("select 'My favorite emoticon is \:p' from dual")).fetchone()
<stdin>:1: DeprecationWarning: invalid escape sequence \:
('My favorite emoticon is :p',)
sqlalchemy python
1 Answers

As mentioned in the docs:

For SQL statements where a colon is required verbatim, as within an inline string, use a backslash to escape

But remember that the backslash is also the escape character in Python string literals, so

text("select 'My favorite emoticon is \:p' from dual")

is incorrect because Python will want to interpret \: as an escape character. We need to use either a "raw string" (r"")

text(r"select 'My favorite emoticon is \:p' from dual")

or escape the backslash itself

text("select 'My favorite emoticon is \\:p' from dual")
user_21443900
Related