Home

Tags

Настраиваем pyodbc в linux ubuntu

2013-01-22 python pyodbc sql mssql linux

Доступ к MSSQL через pyodbc в linux с поддержкой кириллицы.
Все действия производились в ubuntu server 12.04 64bit

Устанавливаем unixodbc, freetds и pyodbc

sudo aptitude install unixodbc unixodbc-dev freetds-dev tdsodbc python-dev
sudo easy_install pyodbc


Настраиваем конфигурацию:
/etc/odbcinst.ini
[ODBC]
Trace = Yes
TraceFile = /tmp/odbc.log

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
UsageCount = 1


/etc/odbc.ini, (dsn записи)
[srv-db4]
Description=srv-db4
Driver=FreeTDS
Servername=srv-db4

[srv-db2]
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Servername=srv-db2


/etc/freetds/freetds.conf + список серверов
[global]
tds version = 8.0
port = 1433

# Command and connection timeouts
; timeout = 10
; connect timeout = 10

# If you get out-of-memory errors, it may mean that your client
# is trying to allocate a huge buffer for a TEXT field.
# Try setting ‘text size’ to a more reasonable limit
text size = 64512

try server login = yes
try domain login = no
client charset = UTF-8

[srv-db4]
host = 192.168.1.1
port = 1433
tds version = 8.0

[srv-db2]
host = srv-db2


Скрипт для проверки:
# coding: utf8

import pyodbc

cnx = pyodbc.connect("DSN=srv-db4;DATABASE=database;UID=username;PWD=password")

cursor = cnx.cursor()
cursor.execute("select top 5 * from table;")
for row in cursor:
    print row


Так же можно все параметры указать в pyodbc.connect
pyodbc.connect("DRIVER=FreeTDS;SERVER=192.168.1.1;PORT=1433;DATABASE=db_name;UID=user_name;PWD=password;TDS_Version=8.0;ClientCharset=UTF8;")