Python SQLAlchemy pyodbc.Error: ('HY000', 'The driver did not supply an error!')

Hi Everyone I Am working right now with Pandas and MSSQL. I have bene working ok but recently after an SQlalchemy update i am getting the following error when I am trying to upload information into the DB via df.to_sql

pyodbc.Error: ('HY000', 'The driver did not supply an error!')

My connection is defined as follows

engine = create_engine('mssql+pyodbc://Reporter:******@localhost:1433/SourcingDb?driver=SQL+Server')

df.to_sql('ProductivityDeck', con=engine, if_exists='replace', index= False)

and the full error I get is this one

Traceback (most recent call last):

File "C:\Python\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 1182, in _execute_context context) File "C:\Python\Python36\lib\site-packages\sqlalchemy\engine\default.py", line 470, in do_execute cursor.execute(statement, parameters) pyodbc.Error: ('HY000', 'The driver did not supply an error!')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:/Users/H212310/PycharmProjects/untitled2/UpdateCEDeckv2.py", line 23, in <module>
    df.to_sql('ProductivityDeck', con=engine, if_exists='replace', index= False)
  File "C:\Python\Python36\lib\site-packages\pandas\core\generic.py", line 2127, in to_sql
    dtype=dtype)
  File "C:\Python\Python36\lib\site-packages\pandas\io\sql.py", line 450, in to_sql
    chunksize=chunksize, dtype=dtype)
  File "C:\Python\Python36\lib\site-packages\pandas\io\sql.py", line 1149, in to_sql
    table.insert(chunksize)
  File "C:\Python\Python36\lib\site-packages\pandas\io\sql.py", line 663, in insert
    self._execute_insert(conn, keys, chunk_iter)
  File "C:\Python\Python36\lib\site-packages\pandas\io\sql.py", line 638, in _execute_insert
    conn.execute(*self.insert_statement(data, conn))
  File "C:\Python\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 945, in execute
    return meth(self, multiparams, params)
  File "C:\Python\Python36\lib\site-packages\sqlalchemy\sql\elements.py", line 263, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "C:\Python\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 1053, in _execute_clauseelement
    compiled_sql, distilled_params
  File "C:\Python\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 1189, in _execute_context
    context)
  File "C:\Python\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 1402, in _handle_dbapi_exception
    exc_info
  File "C:\Python\Python36\lib\site-packages\sqlalchemy\util\compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\Python\Python36\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "C:\Python\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 1182, in _execute_context  context)
  File "C:\Python\Python36\lib\site-packages\sqlalchemy\engine\default.py", line 470, in do_execute
    cursor.execute(statement, parameters)

I have tried to rollback to a lower version of SqlAlchemy, Update the ODBC driver in windows and upgrading the python installation and it keeps coming back.

Any ideas in how to fix this issue?

OK this was a "silent error for a known bug in pandas 23, downgrading to v22 makes the error go away and you can upload 1000+ rows.

this is being worked out on

https://github.com/pandas-dev/pandas/issues/21103

SQLAlchemy can't connect to an mssql database, If not specified in the URL, the default driver for the mssql+pyodbc dialect would be "SQL Server" [1]. That means you need to have a section� sqlalchemy_pyodbc_mssql extends the built-in SQLAlchemy PyODBC dialect in order to work around these limits in a manner consistent with PyMSSQL’s implementation. Most queries are passed as-is to PyODBC to be prepared and executed normally.

I had the same error ('HY000', 'The driver did not supply an error!'). The existing solutions on a few StackOverflow threads did not help.

In my case, the error came from pyodbc.connect().

I ended up reinstalling python and dependencies and the problem disappeared.

pyodbc InterfaceError: sqlalchemy.create_engine(mssql+pyodbc , Both options throw the same error when used in the create_engine() command. I am using Windows 10 and Windows Authentication, Python� Step 3: Proof of concept connecting to SQL using pyodbc. 03/01/2020; 2 minutes to read +3; In this article. This example is a proof of concept. The sample code is simplified for clarity, and doesn't necessarily represent best practices recommended by Microsoft.

pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};')

Just changing the driver worked for me!!

Connect to SQL Server (with pyodbc) getting error: sqlalchemy.exc , This is my first foray into python and SQL Alchemy, and I'm spinning my wheels. I' m running the code below and am able to connect to my DB� pyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') The above exception was the direct cause of the following exception:

Re: [sqlalchemy] Receiving this error: sqlalchemy.exc.DBAPIError , I guess pyodbc is calling str() on these, or perhaps. Pandas is I first ran this program in Python 3.7.3 and it gave me the same exact problem The pyodbc module requires Python 2.4 or greater (see README.txt, which is included with the pyodbc distribution). To build pyodbc, you need the Python libraries and header files, and a C++ compiler. When testing on RedHat, we used Python 2.5.1, the python-devel package and the gcc-c++ package.

pyodbc - sqlalchemy - Python documentation, Hostname Connections. Hostname-based connections are not preferred, however are supported. The ODBC driver name must be explicitly specified: engine =� pyodbc is an open source Python module that makes accessing ODBC databases simple. It implements the DB API 2.0 specification but is packed with even more Pythonic convenience. The easiest way to install is to use pip: pip install pyodbc Precompiled binary wheels are provided for most Python versions on Windows and macOS.

Cursor throws an error when trying to insert in SQL Server database , Python 3.6; pyodbc 4.0.23; Pandas 0.23.0; ODBC Driver 17 for SQL Server cursor.executemany(statement, parameters) sqlalchemy.exc. Reproduced with pyodbc 4.0.23 under 64-bit Python 3.6.4 on Windows 7 with SQLAlchemy 1.2.6. Also confirmed that it is specific to 4.0.23 (4.0.22 works) and is related to string parameter values. With pyodbc 4.0.23 this works:

Comments
  • Please show contents of your dataframe and its dtypes.
  • I could, but it got with any df of 1000+ rows, even one generated with rnd. Just went further and caught that it was a bug in Pandas, not in SqlAlchemy that is being worked out. Thanks a lot for the interest!
  • did you ever work around this? having the same error and not getting anywhere!
  • It was pandas version, try to downgrade and see if it works