How to execute multiple insert/update queries in one SQL using Python/SQLAlchemy and MSSQL?

[Guoliang Li]

Hi all,

I’m new to python and SQLAlchemy, I'm trying to understand how to execute multiple insert/update queries in one SQL using Python/SQLAlchemy:

Requirement Execute multiple insert/update in one SQL:

DECLARE @age INT = 160

INSERT INTO TEST_TABLE VALUES ('QZ_TEST', @age + 1)

INSERT INTO TEST_TABLE VALUES ('QZ_TEST', 'not a number')

INSERT INTO ANOTHER_TABLE VALUES ('QZ_TEST', @age + 2)

Understand that this query looks ugly, but we do have many similar queries. (we're using a legacy database which is around 20 years old)

Python Code

Python: 2.7 - SQLAlchemy (1.0.8) - SQL SERVER 2012



def OdbcEngineSA(driver, conn_str):

def connect():

return pyodbc.connect(conn_str, autocommit=True, timeout=120)

return sqlalchemy.create_engine(driver + '://', creator=connect)



def get_db_connection():

return OdbcEngineSA('mssql', 'DSN=mssql;Server=server,15001;database=DEV;UID=user_abc;PWD=pw_')



def main():

db_connection = get_db_connection()

sql = """

DECLARE @age INT = 160

INSERT INTO TEST_TABLE VALUES ('QZ_TEST', @age + 1)

INSERT INTO TEST_TABLE VALUES ('QZ_TEST', 'not a number')

INSERT INTO ANOTHER_TABLE VALUES ('QZ_TEST', @age + 2)

"""

try:

db_connection.execute(sql)

db_connection.commit()

logger.info('db updated')

except Exception as e:

logger.error('Exception captured as expected: %s', e)

db_connection.rollback()

Please note that

INSERT INTO TEST_TABLE VALUES ('QZ_TEST', 'not a number')

will trigger an Error if I ran this single query with my SQL client: [S0001][245] Conversion failed when converting the varchar value 'not a number' to data type int.


I'm expecting an exception captured by Python, however, the Python code runs without any exception. There's no exception captured by Python even though I replaced the SQL with:

BEGIN TRY

DECLARE @age INT = 160

INSERT INTO TEST_TABLE VALUES ('QZ_TEST', @age + 1)

INSERT INTO TEST_TABLE VALUES ('QZ_TEST', 'not a number')

INSERT INTO ANOTHER_TABLE VALUES ('QZ_TEST', @age + 2)

END TRY

BEGIN CATCH

DECLARE @ErrorMessage NVARCHAR(4000)

DECLARE @ErrorSeverity INT

DECLARE @ErrorState INT



SELECT

@ErrorMessage = ERROR_MESSAGE(),

@ErrorSeverity = ERROR_SEVERITY(),

@ErrorState = ERROR_STATE()



RAISERROR (@ErrorMessage,

@ErrorSeverity, -- Level 16

@ErrorState

)

END CATCH

My Questions

· Am I using the correct method to execute the Query?

· If my code was fine, how can I capture the actual SQL exception from Python?



Many thanks in advance.

[Mike Bayer]

On 01/19/2016 03:01 AM, Guoliang Li wrote:
> Hi all,
>
> I’m new to python and SQLAlchemy, I'm trying to understand how to execute multiple insert/update queries in one SQL using Python/SQLAlchemy:
>
> Requirement Execute multiple insert/update in one SQL:
>
> DECLARE @age INT = 160
>
> INSERT INTO TEST_TABLE VALUES ('QZ_TEST', @age + 1)
>
> INSERT INTO TEST_TABLE VALUES ('QZ_TEST', 'not a number')
>
> INSERT INTO ANOTHER_TABLE VALUES ('QZ_TEST', @age + 2)
>
> Understand that this query looks ugly, but we do have many similar queries. (we're using a legacy database which is around 20 years old)

you can't do the "DECLARE @age" part with the DBAPI or SQLAlchemy very
easily (if at all), the "160" you'd pass in from the outside.

These three INSERT statements are all of a different format so you'd
break them up into individual execute() calls.

Otherwise, if you want to try to run that SQL exactly as written without
changing it, the best you can do is get a raw DBAPI cursor and see if
you can make it work, see
http://docs.sqlalchemy.org/en/rel_1_0/core/connections.html#working-with-raw-dbapi-connections
for how to get at that. It's not clear which DBAPI implementations
support multiple statements + variable declarations within the
cursor.execute() call.