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.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
