Tutorial :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?

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


Solution:1

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.


Solution:2

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.


Solution:3

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.


Solution:4

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.


Solution:5

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.


Solution:6

Python 3 is now supported by pyodbc!


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »