Common ways to connect to odbc from python on windows?

Question

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?

What about pyodbc? http://code.google.com/p/pyodbc/

Any others? Which one is good documented, robust, actively maintained, what are you guys using?

Answer 1

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.

Answer 2

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.

Answer 3

i use pyodbc at works and it never failed me (we have varius dbs). Its robust and fast.

Its maintained and a python 3 version will come soon.

If u want "enterprise" software with payed support u have: http://www.egenix.com/products/python/mxODBC/