Подключение к MS SQL Server из Debian Wheezy

На работе однажды возникла задача написать программу на Python или Perl, которая должна работать с данными в базе MS SQL Server. Для начала, поскольку я не любитель GUI-клиентов к базам данных SQL, я решил найти подходящий консольный клиент.

1. Консольный клиент sqsh

Для подключения к базе данных попробовал воспользоваться консольным клиентом sqsh из одноимённого пакета. Позволяет подключаться к базам данных Sybase и MS SQL Server. Использует настройки драйвера FreeTDS, однако не использует саму библиотеку FreeTDS.

Установка клиента:

# apt-get install sqsh

Вместе с клиентом установятся библиотеки FreeTDS.

Настройка FreeTDS проста. В файл /etc/freetds/freetds.conf нужно добавить секцию для подключения к серверу MS SQL, задав адрес сервера, порт подключения, версию протокола TDS и кодировку клиента:

[server.domain.tld]
        host = server.domain.tld
        port = 1433
        tds version = 7.0
        client charset = UTF-8

Ключи клиента sqsh:

• -S - имя или адрес сервера MS SQL,
• -D - имя базы данных на сервере,
• -U - имя пользователя для подключения к базе,
• -P - пароль пользователя.

Пример подключения:

$ sqsh -S server.domain.tld -D database -U user -P password

Для выполнения запроса нужно сначала ввести его (можно в нескольких отдельных строках, нажимая Enter), а затем в новой строке указать команду go.

Клиент не понравился тем, что при выводе таблицы на экран не учитывает ширину данных, содержащихся в столбцах результата запроса, а использует для форматирования таблицы максимальную ширину столбца.

Ещё один недостаток: если очередной столбец не умещается в той же строке, где уместились предыдущие, этот столбец попадает в следующую строку. В результате одна строка с результатами запроса разбивается на множество строк, в каждой из которых находится по одному-трём столбцам.

2. Консольный клиент sqlline на Java

Попробовал консольный клиент sqlline, написанный на Java. Для подключения к базам данных использует драйверы семейства JDBC, поэтому позволяет подключаться к любой базе данных, для которой имеется JDBC-драйвер. Для подключения к серверам Sybase и MS SQL Server используется драйвер jTDS.

Клиент и драйвер находятся в пакетах sqlline и libjtds-java. Установим их:

# apt-get install sqlline libjtds-java

Подключение к серверу базы данных не столь очевидно и осуществляется такой командой:

$ sqlline -u jdbc:jtds:sqlserver://server.domain.tld/database -n user -p password

Ключи клиента sqlline:

• -u - URL базы данных, где server - IP-адрес или доменное имя сервера, а database - имя базы данных,
• -n - имя пользователя для доступа к базе данных,
• -p - пароль пользователя.

Как и в случае с sqsh, для выполнения запроса к базе данных, нужно сначала ввести сам запрос, а потом команду go.

Можно вводить любую команду, понимаемую сервером базы данных, либо одну из встроенных в клиент команд:

• !quit - выйти из клиента,
• !tables - показать список таблиц,
• !table <таблица> - показать структуру указанной таблицы,
• !columns <таблица> - показать колонки указанной таблицы.

Этот клиент оказался более удобным - форматирование таблиц осуществляется по ширине содержимого колонок. Кроме того, он включает в себя команды для просмотра структуры базы данных, которые позволяют обойтись без написания запросов к таблицам TABLES и COLUMNS в базе данных INFORMATION_SCHEMA. Из недостатков можно отметить, что команды для просмотра структуры таблиц не позволяют указать имя таблицы с пробелами в названии.

3. Python и модуль pymssql

Для доступа к MS SQL из Python попробовал воспользоваться модулем pymssql, который в Debian находится в пакете python-pymssql. Он тоже использует лишь файл конфигурации драйвера FreeTDS, однако подключается к серверу базы данных без использования самой библиотеки FreeTDS.

Установка драйвера:

# apt-get install python-pymssql

Тестовая программа:

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import pymssql

db = pymssql.connect(host = 'server.domain.tld',
                     database = 'database',
                     user = 'user',
                     password = 'password',
                     charset = 'UTF-8')

query = db.cursor()
query.execute(u'''SELECT field1
                  FROM table1
                  WHERE field2 = 1
               ''')
s = '%s\n%d\n' % (pymssql.paramstyle, len(query.fetchall()))
for row in query.fetchall():
    s += u'%s\n' % row[0]
query.close()

db.close()

print s

Тестовая программа умеет подключаться и выполнять запрос, но при выполнении любого запроса возвращается пустой результат. При этом, если выполнить такой же запрос в консольном клиенте, результат возвращается.

Нашёл заявку в багтрекере Debian: Debian Bug report logs - #709210: python-pymssql: Valid select queries return no results.

Заявка не закрыта уже два месяца. Разработчики самого модуля pymssql не хотят исправлять ошибку, т.к. уже вышла новая версия этой библиотеки. Новую версию собрать при помощи stdeb не пробовал.

4. Python и модуль pyodbc

Решил попробовать ODBC-драйвер совместно с драйвером FreeTDS и модулем pyodbc. Для этого поставил пакеты из репозитория Debian: unixodbc, tdsodbc, odbcinst1debian2, python-pyodbc.

Установим необходимое:

# apt-get install unixodbc tdsodbc odbcinst1debian2 python-pyodbc

В отличие от модуля pymssql, для модуля pyodbc понадобится настроить не только FreeTDS, но и ODBC.

Настроил ODBC-драйвер TDS в соответствии с мануалом:
How to Configure Linux ODBC Connections for MS SQL или Connecting to a Microsoft SQL Server database from Python under Ubuntu.

Теперь нужно настроить ODBC-драйвер FreeTDS. Для этого в файл /etc/odbcinst.ini нужно вписать:

[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
CPTimeout       = 
CPReuse         =

Пути к динамическим библиотекам драйверов можно узнать, заглянув в список файлов в пакетах tdsodbc и odbcinst1debian2 соответственно:

$ dpkg -L tdsodbc
$ dpkg -L odbcinst1debian2

Сами эти пакеты можно найти, соответственно, по именам динамических библиотек (прямо как сепульки), следующими командами:

$ apt-file search libtdsodbc.so
$ apt-file search libtdsS.so

Далее нужно настроить DSN для доступа к интересующей нас базе данных. Добавим в файл /etc/odbc.ini описание DSN:

[server]
Driver = FreeTDS
Description = My cool MS SQL server
Trace = No
Server = server.domain.tld
Port = 1433
Database = database

В качестве имени секции используется произвольный текст, который и будет именем DSN'а.

В параметре Driver нужно указывать то значение, которое совпадает с названием секции в файле /etc/odbcinst.ini

В параметре Server нужно указывать то значение, которое совпадает с названием секции в файле /etc/freetds/freetds.conf

Если всё настроено правильно, тогда команда isql при указании этого DSN, имени пользователя и его пароля должна выводить следующий текст:

$ isql server user password
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit

Теперь время протестировать модуль pyodbc. Для этого я воспользовался несколько изменённой предыдущей тестовой программой:

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import pyodbc

db = pyodbc.connect('DSN=server;UID=user;PWD=password')

query = db.cursor()
query.execute(u'''SELECT field1
                  FROM table1
                  WHERE field2 = 1
               ''')
s = '%s\n%d\n' % (pyodbc.paramstyle, len(query.fetchall()))
for row in query.fetchall():
    s += u'%s\n' % row[0]
query.close()

db.close()

print s

При запуске она выдаёт следующую ошибку:

$ ./test2.py 
Traceback (most recent call last):
  File "./test2.py", line 13, in 
    ''')
pyodbc.ProgrammingError: ('42000', "[42000] [FreeTDS][SQL Server]Could not find stored procedure 'S'. (2812) (SQLExecDirectW)")

Как видно, она ругается на отсутствие какой-то хранимой процедуры с именем S.

Ссылки pyodbc Issue 110: Could not find stored procedure и UBUNTU+PYMSSQL+CYRILLIC указывают на наличие проблем с кодировкой. Но понять, в чём эта проблема заключается и как её устранить, мне не удалось.

5. Perl и модуль DBD::Sybase

Для сравнения попробовал написать программу на Perl с использованием модуля DBD::Sybase из пакета libdbd-sybase-perl.

Установка модуля:

# apt-get install libdbd-sybase-perl

Тестовая программа:

#!/usr/bin/perl

use warnings;
use strict;

use utf8;
use DBI qw(:sql_types);

my $dbh = DBI->connect("DBI:Sybase:server=server.domain.tld;database=database;charset=utf8", "user", "password")
  or die $!;
$dbh->{syb_enable_utf8} = 1;

my $sth = $dbh->prepare("SELECT field1
                         FROM table1
                         WHERE field2 = ?");
$sth->execute('1');
while (my ($field1) = $sth->fetchrow_array())
{
  print "$field1\n";
}
$sth->finish();

$dbh->disconnect();

Программа выдала ожидаемый результат. Понадобилось только задать кодировку подключения, задать у него флаг utf8 и передать в запрос параметр подходящего типа: строку вместо числа.

На самом деле, правда, флаг syb_enable_utf8 не работает. Исправлять не пробовал, потому что если данные не нужно обрабатывать на уровне символов, то это не имеет значения, поскольку на вывод в любом случае поступает строка utf8. Если же необходимо обрабатывать эти строки внутри Perl-программы, приходится вручную выставлять у строк флаг utf8 или, если не городить костыли, то исправить сам модуль.

Буду признателен, если кто-то сообщит о способах решения упомянутых мной проблем: соберёт работающий модуль pymssql в виде deb-пакета, решит проблему с кодировками в pyodbc или исправит работу флага mysql_enable_utf8.

Использованные материалы:
1. Debian Bug report logs - #709210: python-pymssql: Valid select queries return no results
2. How to Configure Linux ODBC Connections for MS SQL
3. Connecting to a Microsoft SQL Server database from Python under Ubuntu
4. pyodbc Issue 110: Could not find stored procedure
5. UBUNTU+PYMSSQL+CYRILLIC