MSSQL in python 2.7

Question

Is there a module available for connection of MSSQL and python 2.7?

I downloaded pymssql but it is for python 2.6. Is there any equivalent module for python 2.7?

I am not aware of it if anyone can provide links.

Important note: in the meantime there is a pymssql module available. Don't miss to read the answer at the end of this page: https://stackoverflow.com/a/25749269/362951

Isn't 2.7 backwards compatible with 2.6? i.e. doesn't that module work in 2.6? — rplnt, Sep 06 '11 at 08:48
No while installing http://code.google.com/p/pymssql/downloads/detail?name=pymssql-1.9.908.win32-py2.6.exe&can=2&q= it check for python version and end the setup — Shashi, Sep 06 '11 at 08:51
There are snapshots for 2.7 http://build.damoxc.net/downloads/pymssql/snapshots/ ...if that helps. — rplnt, Sep 06 '11 at 08:53
I found one repository of python pakg hope it will usefull for all http://www.lfd.uci.edu/~gohlke/pythonlibs/ — Shashi, Dec 20 '12 at 06:52

score 58 · Accepted Answer · edited Mar 25 '17 at 22:46

You can also use pyodbc to connect to MSSQL from Python.

An example from the documentation:

import pyodbc
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=me;PWD=pass')
cursor = cnxn.cursor()
cursor.execute("select user_id, user_name from users")
rows = cursor.fetchall()
for row in rows:
    print row.user_id, row.user_name

The SQLAlchemy library (mentioned in another answer), uses pyodbc to connect to MSSQL databases (it tries various libraries, but pyodbc is the preferred one). Example code using sqlalchemy:

from sqlalchemy import create_engine
engine = create_engine("mssql://me:pass@localhost/testdb")
for row in engine.execute("select user_id, user_name from users"):
    print row.user_id, row.user_name

SaeX · Answer 2 · 2014-09-11T11:11:23.227

17

If you're coming across this question through a web search, note that pymssql nowadays does support Python 2.7 (and 3.3) or newer. No need to use ODBC.

From the pymssql requirements:

Python 2.x: 2.6 or newer. Python 3.x: 3.3 or newer.

See http://pymssql.org/.

edited Sep 11 '14 at 11:11

answered Sep 09 '14 at 16:03

SaeX

17,240
16
77
97

From the MSFT python MSSQL driver page ( https://learn.microsoft.com/en-us/sql/connect/python/python-driver-for-sql-server?view=sql-server-2017 ) "There are several python SQL drivers available. However, Microsoft places its testing efforts and its confidence in pyodbc driver. " I doubt this was the case in 2014 though:) – riffrazor Oct 23 '18 at 22:47

score 5 · Answer 3 · edited Mar 25 '17 at 22:42

5

Install pyodbc using pip as follows: pip install pyodbc

import pyodbc
cnxn = pyodbc.connect("DRIVER={SQL Server};SERVER=SOME-PC;DATABASE=my_db")
cursor = cnxn.cursor()


cursor.execute("insert into test_tb values(6, 'name')")

cursor.execute("select id, name from my_tb")
rows = cursor.fetchall()
for row in rows:
    print row.id, row.name

For details, see

https://github.com/mkleehammer/pyodbc/wiki

edited Mar 25 '17 at 22:42

Gord Thompson

116,920
32
215
418

answered Jun 09 '13 at 07:32

akila

51
1
1

This pip install no longer seems to work (Ubuntu 16.04, Python 2.7.): `--compile failed with error code 1 in ...` – Michael M Mar 25 '17 at 19:46
2

@MichaelMügge - `pip install pyodbc` works fine on Ubuntu 16.04 if you do `sudo apt install unixodbc-dev` first. – Gord Thompson Mar 25 '17 at 22:40
@GordThompson, Indeed. Important detail there. Thanks. – Michael M Mar 26 '17 at 04:24

score 3 · Answer 4 · answered Sep 06 '11 at 08:57

You can try out SQLAlchemy: The SQLAlchemy Object Relational Mapper presents a method of associating user-defined Python classes with database tables, and instances of those classes (objects) with rows in their corresponding tables.

You can refer following links: 1> http://www.sqlalchemy.org/docs/ 2> http://www.rmunn.com/sqlalchemy-tutorial/tutorial.html

MSSQL in python 2.7

4 Answers4

Linked