Pyodbc not returning error from SQL Server

Question

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?

@Parfait Thanks for the suggestion, that didn't change anything unfortunately. — gbeaven, Jan 10 '19 at 18:07
Remove the redundant `USE` line since you connect directly to this database per connection string. — Parfait, Jan 10 '19 at 18:49
@Parfait aha! Figured it was something silly... That works. Submit an answer and I'll mark it correct. Thanks a ton! — gbeaven, Jan 10 '19 at 18:51
@Parfait That does raise another question though... Why would the database redundancy cause the code to complete silently without error? I'm able to get the code working properly now, just trying to fully understand why it worked/didn't work. — gbeaven, Jan 10 '19 at 18:54

score 2 · Answer 1 · answered Sep 21 '21 at 13:06

2

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.

answered Sep 21 '21 at 13:06

TJB

787
1
8
29

This saved my bacon. Can anyone think of any other such messages that can be outputted but disabled? – silico-biomancer May 19 '23 at 04:29

Parfait · Accepted Answer · 2019-01-10T19:12:39.653

1

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)
            ''')

edited Jan 10 '19 at 19:12

answered Jan 10 '19 at 18:47

Parfait

104,375
17
94
125

"Usually only the first query will run." - Not in my experience. Yes there are some cases where an anonymous code block will fail or produce potentially confusing results (e.g., omitting `SET NOCOUNT ON;` where required) but that is more a function of the ODBC driver than it is of pyodbc itself. pyodbc just passes the SQL command text along to the ODBC Driver Manager and waits for the results to come back. – Gord Thompson Jan 10 '19 at 19:22
Good point @GordThompson. I didn't want to make absolute claims as some multi-line code blocks can run especially TSQL with its batch calls using `GO`. All depends on API, driver, even RDBMS. – Parfait Jan 10 '19 at 19:25
Agreed. And yes in this case (SQL Server) the `use [DATABASENAME]` needs to go first in its own `.execute`, followed by the rest of the anonymous code block. – Gord Thompson Jan 10 '19 at 19:34

Pyodbc not returning error from SQL Server

2 Answers2