Home

ODBC with Python (pyodbc) to MS SQL (SQL Server - Microsoft)

Tue, 2009-12-15 17:53 — dgtlmoon

Was having trouble figuring out pyodbc http://code.google.com/p/pyodbc/ with my ODBC configuration to talk to a MS SQL or "Sequel" as some people would have it

You'll need a couple of packages installed

tdsodbc - ODBC driver for connecting to MS SQL and Sybase SQL servers
odbcinst1debian1 - Support library for accessing odbc ini files
odbcinst - Helper program for accessing odbc ini files

so just

apt-get install tdsodbc odbcinst1debian1 odbcinst

My /etc/odbcinst.ini - this file configures your ODBC libraries, for my x86-64 AMD I had to use Driver64 =

[FreeTDS]
Description = FreeTDS driver
Driver      =
Driver64    = /usr/lib/odbc/libtdsodbc.so
Setup       =
Setup64     = /usr/lib/odbc/libtdsS.so
UsageCount  = 1
CPTimeout   =
CPReuse     =

My /etc/odbc.ini - this creates a DSN (data source name?) that you refer to from the application layer

[dsn]
Driver = FreeTDS
Server = 127.0.0.1
port = 3681
Database = databasename
TDS Version      = 6.0

And some python to explain it all

import pyodbc
cnxn = pyodbc.connect("DSN=dsn;UID=username;PWD=mypass")                     
cursor = cnxn.cursor()                                                            
cursor.execute('select 1')                                       
<pyodbc.Cursor object at 0x26157b0>                                  

a=cursor.fetchall()                                              
print 'pyodbc',a                                                 
pyodbc [(1, )]

0
Your rating: None