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.
>
> 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.
