"text() construct doesn't define a bound parameter" with bindparams()

user_19167983 0
Viewed 85

Consider the following statement:

text("""
select
    url,
    (regexp_match(url, '(?<=comments/)([\w\d]{5,6})((/[\w\d]{0,}/)([\w\d]{5,6}))?'))[3] is not null as "isComment"
from submissions s
where
    (regexp_match(s.url, '(?<=comments/)([\w\d]{5,6})((/[\w\d]{0,}/)([\w\d]{5,6}))?'))[1] = (regexp_match(lower('\:sqlurl'), '(?<=comments\/)([\w\d]{5,6})((?:\/[\w\d]{0,}\/)([\w\d]{5,6}))?'))[1]
            """).bindparams(sqlurl=url).columns(url=String, isComment=Boolean)

When I go to execute it with execute I get

sqlalchemy.exc.ArgumentError: This text() construct doesn't define a bound parameter named 'sqlurl'

even though param :sqlurl is defined. thx

sqlalchemy python
1 Answers

Your parameter placeholder is not being recognized because it is embedded in a string literal.

import sqlalchemy as sa

engine = sa.create_engine("sqlite://")

with engine.connect() as conn:
    stmt = sa.text(r"SELECT '\:sqlurl' AS foo").bindparams(sqlurl="thing")
    # sqlalchemy.exc.ArgumentError: This text() construct doesn't define a bound parameter named 'sqlurl'

If you want to prepend a backslash to the parameter value you need to concatenate it with the raw parameter value itself.

import sqlalchemy as sa

engine = sa.create_engine("sqlite://")

with engine.connect() as conn:
    stmt = sa.text(r"SELECT '\' || :sqlurl AS foo").bindparams(sqlurl="thing")
    result = conn.execute(stmt).scalar()
    print(result)  # \thing
user_21443900
Related