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

Question

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

Answer 1

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

Answer 2

I didn't found anything in azure services that blocks the custom modifications when bot is deployed. And without the code that you are deploying in the Azure App Service it's hard to find the root cause of the problem you are facing.

You can customize the default canvas with JavaScript-based styling in the HTML code for the website where you deploy your bot.Generally we can easily customize the background color and the chat bubbles of your web chat. You can do so by simply adding the below code snipper in the style script of your index page.

const styleSet = window.WebChat.createStyleSet({ 
bubbleBackground: /*color for bot chat bubble*/, 
bubbleFromUserBackground: /*color for user chat bubble*/, 
rootHeight: '100%', 
rootWidth: '50%', 
backgroundColor: 'lightblue' /*background of web chat*/
});

You can configure how the chat canvas looks with some simple CSS and JavaScript styling options. But remember that you would need to add your Bot ID also. Check this customize the default canvas for more information. If you are still facing the same issue check is you are following the process mentioned in this deploy your bot in Azure document.