На работе однажды возникла задача написать программу на Python или Perl, которая должна работать с данными в базе MS SQL Server. Для начала, поскольку я не любитель GUI-клиентов к базам данных SQL, я решил найти подходящий консольный клиент.
Для подключения к базе данных попробовал воспользоваться консольным клиентом 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:
Пример подключения:
$ sqsh -S server.domain.tld -D database -U user -P password
Для выполнения запроса нужно сначала ввести его (можно в нескольких отдельных строках, нажимая Enter), а затем в новой строке указать команду go.
Клиент не понравился тем, что при выводе таблицы на экран не учитывает ширину данных, содержащихся в столбцах результата запроса, а использует для форматирования таблицы максимальную ширину столбца.
Ещё один недостаток: если очередной столбец не умещается в той же строке, где уместились предыдущие, этот столбец попадает в следующую строку. В результате одна строка с результатами запроса разбивается на множество строк, в каждой из которых находится по одному-трём столбцам.
Попробовал консольный клиент 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:
Как и в случае с sqsh, для выполнения запроса к базе данных, нужно сначала ввести сам запрос, а потом команду go.
Можно вводить любую команду, понимаемую сервером базы данных, либо одну из встроенных в клиент команд:
Этот клиент оказался более удобным - форматирование таблиц осуществляется по ширине содержимого колонок. Кроме того, он включает в себя команды для просмотра структуры базы данных, которые позволяют обойтись без написания запросов к таблицам TABLES и COLUMNS в базе данных INFORMATION_SCHEMA. Из недостатков можно отметить, что команды для просмотра структуры таблиц не позволяют указать имя таблицы с пробелами в названии.
Для доступа к 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 не пробовал.
Решил попробовать 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 <module>
''')
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 указывают на наличие проблем с кодировкой. Но понять, в чём эта проблема заключается и как её устранить, мне не удалось.
Для сравнения попробовал написать программу на 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.
Использованные материалы: