CODEWITHSUNDEEP
pythonsqlsql-serverpython-3.xpyodbc
gbeaven
gbeaven

Reputation: 1800

Pyodbc not returning error from SQL Server

Ok, hopefully this is something really silly that I'm overlooking...

I'm using Python 3.7.2 and Pyodbc 4.0.24 on SQL Server 17 (14.0.2002.14)

On my SQL Server I create a table as such:

create table test(
    test1 varchar(5)
)

But when I try to throw an error (because the table already exists) by executing the same query using pyodbc I don't see any errors returned:

def createSQLTable():
    try:
        sql_conn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
                                    'Server=[SERVERNAME];'
                                    'Database=[DATABASENAME];'
                                    'Trusted_Connection=yes;')
        cursor = sql_conn.cursor()
        sql = '''set nocount on;
           use [DATABASENAME]
           create table test(test1 varchar(5));'''
        cursor.execute(sql)
    except Exception as e:
        print(e)
    finally:
        sql_conn.commit()
        cursor.close()
        sql_conn.close()

createSQLTable()

If I run the same TSQL on SQL Server I get the error message:

Msg 2714, Level 16, State 6, Line 1 There is already an object named 'test' in the database.

So how do I get Python/Pyodbc to throw the same or similar error?

Upvotes: 2

Views: 4050

Answers (2)

TJB
TJB

Reputation: 887

Sorry for the necro, but in case anyone else was searching for this, pyodbc is not returning an error because it is a getting a non-error related message first, e.g. (1) rows affected. I solved my issue by calling set nocount on at the start of the procedure without having to split the calls as per the accepted answer.

Upvotes: 6

Parfait
Parfait

Reputation: 107747

Pyodbc does not raise any exception since there is no error with USE [DATABASENAME] which is the first transaction type line in your multiple statement query.

Pyodbc like most Python DB-APIs does not support multiple transactional SQL statements in same execution call with some exceptions. Usually only the first query will run.

Consider sending them individually within same try block: 

cur.execute('set nocount on;')
cur.execute('use [DATABASENAME];')
cur.execute('create table test(test1 varchar(5));')

Or place lines in a stored procedure and run with either below:

cursor.execute("EXEC mystoredProc")

cursor.execute("{CALL mystoredProc}")

Or keep only relevant lines as USE is unnecessary with database connection specification.

cur.execute('''set nocount on
               create table test(test1 varchar(5)
            ''')

Upvotes: 3

Related Questions