Avoid Parameter Binding When Executing Query with SQLAlchemy

Question

I am using SQLALchemy to execute queries on Teradata. One of the queries I execute is a DDL statement to replace a stored procedure:

REPLACE PROCEDURE DEV_MIGRATION_TOOL.UNIT_TEST_NEW_STORED_PROCEDURE()
UNIT_TEST_NEW_STORED_PROCEDURE:
BEGIN
    DECLARE V_VAR VARCHAR(50);
    SELECT 'Hello World!'
    INTO :V_VAR;
END;

This SQL statement is assigned to a variable query and is executed by SQLALchemy with the session execute method:

def execute_sql_statement(self, query):
    """Generic method to execute a SQL statement on target environment."""
    self.target_environment.db_session.execute(query)
    self.target_environment.db_session.commit()

The problem I have it that SQLAlchemy assumes the :V_VAR variable is a parameter which should be supplied with a dictionary. See documentation here: http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.execute

result = session.execute("SELECT * FROM user WHERE id=:param", {"param":5})

In the current configuration, it triggers the error message:

2018-04-18 19:09:27,874 - migration_script - INFO - Execute object DDL statement on UAT environment. 2018-04-18 19:09:27,875 - migration_script - ERROR - (sqlalchemy.exc.InvalidRequestError) A value is required for bind parameter 'V_VAR' [SQL: "()\rUNIT_TEST_NEW_STORED_PROCEDURE:\rBEGIN\r DECLARE V_VAR VARCHAR(50);\r SELECT 'Hello World!'\r INTO ?;\rEND;"] [parameters: [{}]]

Would you know a way to avoid this error message so that my DDL statement above is executed without error?

Hoopoe

Answer 1

Session.execute() interprets plain SQL strings as if passed in a text() construct. Due to that you have to escape any colons that you do not want interpreted as beginning a placeholder:

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

So your DDL statement query should be:

"""
REPLACE PROCEDURE DEV_MIGRATION_TOOL.UNIT_TEST_NEW_STORED_PROCEDURE()
UNIT_TEST_NEW_STORED_PROCEDURE:
BEGIN
    DECLARE V_VAR VARCHAR(50);
    SELECT 'Hello World!'
    INTO \\:V_VAR;
END;
"""

^{Note that the backslash is properly escaped on purpose. Newer versions of Python will produce SyntaxWarnings for invalid escape sequences such as "\:", if not using raw string literals.}

Sherburne