Fetching a returned row of a query on MS SQL Server with sqlalchemy and pyodbc

Question

I am trying to get the returned row from executing a row insertion. I tested the compiled sql directly on SQL Server, the query works fine and it returns a row correctly.

The problem is I am not able to get the returned row from the query. Here is my code and I am using pyodbc driver on Windows.

sql = """
    INSERT INTO mytable (id, name)
    OUTPUT Inserted.id, Inserted.name
    VALUES (...)
    """
result = db.execute(sql).fetchall()

This is the error I am getting:

ERROR:  Failed to save events: (Error) ('HY010', '[HY010] [Microsoft][ODBC SQL Server Driver]Function sequence error (0) (SQLFetch)') None None
Traceback (most recent call last):
  File "...\site-packages\sqlalchemy\engine\result.py", line 782, in fetchall
    l = self.process_rows(self._fetchall_impl())
  File "...\site-packages\sqlalchemy\engine\result.py", line 749, in _fetchall_impl
    return self.cursor.fetchall()
pyodbc.Error: ('HY010', '[HY010] [Microsoft][ODBC SQL Server Driver]Function sequence error (0) (SQLFetch)')

Any idea? Thanks in advance!

Unable to reproduce on Windows x64 with SQL Server Native Client 11.0 and pyodbc 3.0.7, not using sqlalchemy. Do you see similar behavior if sqlalchemy is removed from the mix? — Bryan
I'm seeing similar, but different behavior - the insert works, but sqlalchemy isn't returning any rows. I'm using sqlalchemy, and pymssql. — Marc

Tad Tenacious Tad Tenacious · Accepted Answer · 2016-12-01T16:22:16

You are not executing a query. You are writing to the database which should not return results. Instead, try the following:

# assuming db is your cursor
db.execute(sql) # inserts the data
result = db.execute('SELECT * FROM mytable').fetchall()

Fetching a returned row of a query on MS SQL Server with sqlalchemy and pyodbc

1 Answers