23

What library should I use to connect to odbc from python on windows? Is there a good alternative for pywin32 when it comes to odbc?

I'm looking for something well-documented, robust, actively maintained, etc. pyodbc looks good -- are there any others?

rstackhouse
  • 2,238
  • 24
  • 28
user89021
  • 14,784
  • 16
  • 53
  • 65

6 Answers6

26

You already suggested pyodbc, and I am going to agree with you.

It has given me the least amount of issues in my experience; I've used pymssql and adodbapi, and when those threw exceptions/created issues, I swapped out the code and replaced it with pyodbc and it either fixed the problem, or gave better error messages so I could debug faster.

It's worth mentioning that I primarily use it to connect to MSSQL Server DBs.

Jason Coon
  • 17,601
  • 10
  • 42
  • 50
  • But that pyodbc comes with the expectation of having an ODBC driver and setup in the machines where its been used and run. right? If we dont have odbc and just wanna rely on pure sql drivers thats available on the machines, what would you recommend? – Ak777 Feb 17 '23 at 20:49
18

I use SQLAlchemy for all python database access. I highly recommend SQLAlchemy.

SA uses pyodbc under the hood when connecting to SQL server databases. It uses other DBAPI libraries to connect to other database, for instance cx_Oracle.

A simplistic example, using SQLAlchemy like you would normally use a DBAPI module:

import sqlalchemy

engine = sqlalchemy.create_engine('sqlite:///database.db')
for r in engine.execute('SELECT * FROM T'):
    print(r.OneColumn, r.OtherColumn)

But the real value of SQLAlchemy lies in its ORM and SQL expression language. Have a look, it is well worth the effort to learn to use.

codeape
  • 97,830
  • 24
  • 159
  • 188
  • SQLAlchemy made it very easy for me to switch between odbc and adodbapi without changing more than two lines of code. – Bård Apr 24 '09 at 14:01
7

Another alternative is pypyodbc which was written in pure Python. it can been seen as a re-implemenation of the pyodbc module – with only around 1800 lines code, which is good for maintenance.

Here's a Hello World sample of accessing mssql in Python.

leftaroundabout
  • 117,950
  • 5
  • 174
  • 319
pypyodbc
  • 1,119
  • 1
  • 12
  • 9
  • But that pyodbc comes with the expectation of having an ODBC driver and setup in the machines where its been used and run. right? If we dont have odbc and just wanna rely on pure sql drivers thats available on the machines, what would you recommend? – Ak777 Feb 17 '23 at 20:49
1

I use pyodbc at work and it has never failed me (we have varius dbs). It is robust and fast.

It is actively maintained and a python 3 version will come soon.

If you want "enterprise" software with payed support you can use mxODBC.

Steven Rumbalski
  • 44,786
  • 9
  • 89
  • 119
hugo24
  • 1,089
  • 13
  • 21
  • pyodbc not working for me on Windows python 3.1.2 :( – Christopher Mahan Feb 17 '11 at 01:31
  • there is no official build for python 3 :( im waiting for one also. There is an unofficial that u have to build yourself – hugo24 Feb 19 '11 at 01:59
  • It fails for me too. pyodbc comes with the expectation of having an ODBC driver and setup in the machines where its been used and run. right? If we dont have odbc and just wanna rely on pure sql drivers thats available on the machines, what would you recommend? – Ak777 Feb 17 '23 at 20:50
  • 1
    @Ak777 yes you need to install an odbc driver if you want to use pyodbc since pyodbc is just a wrapper for easy use of odbc. If you don't want to install odbc you could look into native libraries. For example for microsoft database you could use: pymssql. The best is to look at list of Included Dialects for sqlalchemy here https://docs.sqlalchemy.org/en/20/dialects. This is how I found about pymssql. – hugo24 Feb 18 '23 at 21:52
1

You can give turbodbc a spin. Since version 1.1.1, it officially supports Windows. There's a good chance that it is faster than pyodbc for what you do.

Michael Koenig
  • 364
  • 2
  • 9
-4

Python 3 is now supported by pyodbc!

rstackhouse
  • 2,238
  • 24
  • 28
mkleehammer
  • 72
  • 2