Python3连接SQLServer
在Win下使用SQLServer+IIS,在linux下使用PostgreSQL/MySQL,是再正常不过的事情,但偶尔也有特殊情况:比如单位的大环境是SQLServer,而我却希望在Linux下跑Django,并从单位的SQLServer读取生产数据。
环境
- SQL Server
- Win Server 2008R2 or 2012R2
- SQLServer 2014 or 2010
- App Server
- Debian 7
- Python 3.2.3
- Django 1.6
[========]
基本
这里使用ODBC的方法连接SqlServer:不同于Win平台成熟多样的SqlServer连接方法,在Linux下,ODBC虽然古老,但却最可靠。
APP ODBC DB Connector DB
APP方面,这里介绍下几个常用的python ODBC插件:
- pyodbc :开源,跨平台,性能一般,兼容Python2/3。
- mxODBC :商业化,跨平台,高性能,Python 2.5-2.7。
- adodbapi :开源,Windows平台,高性能,兼容Python2/3。
~都不完美,这里选择pyodbc,因为要兼容Python3。
ODBC的选择:
- UnixODBC:一个开源的odbc的实现,兼容诸多数据库驱动。
DB Connector的选择:
- FreeTDS:一个开源的表列数据流实现,允许Linux程序连接SQLServer或sybase。
- Microsoft ODBC Driver 11 for linux:微软官方的SQL驱动,闭源。
~将分别介绍两种DB Connector的配置。
UnixODBC的安装
编译UnixODBC
因为debian源内的unixODBC版本太过古老(2.2.14),这里采用源码编译安装2.3.2版本。
先卸载旧版本
# aptitude remove libodbc1 unixodbc unixodbc-dev
下载2.3.2版本的源码,并编译安装
# wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.2.tar.gz
# tar xf unixODBC-2.3.2.tar.gz
# cd unixODBC-2.3.2
编译并安装
# ./configure \
--disable-gui \
--disable-drivers \
--enable-iconv \
--with-iconv-char-enc=UTF8 \
--with-iconv-ucode-enc=UTF16LEmake && make install
其中:
- --disable-gui 表示不包含gnome和kde的配置界面
- --disable-drivers 表示不集成驱动。
- --enable-iconv 表示允许字符集转换
将 /usr/local/lib/目录添加到gnu的库文件寻找路径中,并重载gnu配置。
# echo "/usr/local/lib/" >> /etc/ld.so.conf.d/x86_64-linux-gnu.conf
# ldconfig
FreeTDS的安装和配置
若要使用Microsoft ODBC Driver 11 for linux作为DB Connector,这可以跳过此节。
编译并安装FreeTDS
这里我们依然先卸载debian源内的低版本FreeTDS。
# aptitude remove freetds-dev freetds-bin
下载稳定版本的FreeTDS源码
# wget ftp://ftp.freetds.org/pub/freetds/stable/freetds-stable.tgz
# tar xf freetds-stable.tgz
# cd freetds-0.91/
编译并安装FreeTDS
# ./configure --enable-msdblib --with-tdsver=7.1
# make && make install
其中:
- --with-tdsver=7.1表示连接的目标服务器数据库版本,这里有一份关于TDS版本的说明。
使用tsql -C命令检查安装。
# tsql -C
Compile-time settings (established with the "configure" script)
Version: freetds v0.91
freetds.conf directory: /usr/local/etc
MS db-lib source compatibility: yes
Sybase binary compatibility: no
Thread safety: yes
iconv library: yes
TDS version: 7.1
iODBC: no
unixodbc: yes
SSPI "trusted" logins: no
Kerberos: no
配置FreeTDS
配置FreeTDS很简单,编辑 /usr/local/etc/freetds.conf
文件,在尾部定义一个服务器。
[myserver]
host = 192.168.137.93
port = 1433
tds version = 8.0
使用tsql命令检测一下。
# tsql -S myserver -U sa -P Aa123456
locale is "zh_CN.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>
1> exit
配置unixOBDC
将unixOBDC和FreeTDS连接,相当于Windows下为ODBC数据源添加一个驱动。
编辑 /usr/local/etc/odbcinst.ini
,输入:
[FreeTDS]
Description=Open source FreeTDS driver
Driver=/usr/local/lib/libtdsodbc.so
UsageCount=1
Threading=1
添加一个数据源,编辑 /usr/local/etc/odbc.ini
[gdzc]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = myserver
Database = gdzc
- Driver 是指
/usr/local/etc/odbcinst.ini
内链接的驱动名。 - Servername 是指
/usr/local/etc/freetds.conf
内定义的服务器 - [gdzc] 是数据源名称,也就是DSN。
- Database 是具体的某个数据库
使用isql命令进行测试ODBC到数据库的链接。
~# isql -v gdzc sa Aa123456
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
Microsoft ODBC Driver 11 for linux的安装和配置
若要使用FreeTDS作为DB Connector,这可以跳过此节。
编译并安装Microsoft ODBC Driver 11
参考官方的安装手册
下载,并解压
# wget http://download.microsoft.com/download/6/A/B/6AB27E13-46AE-4CE9-AFFD-406367CADC1D/Linux6/sqlncli-11.0.1790.0.tar.gz
# tar -xzf sqlncli-11.0.1790.0.tar.gz
使用bash install.sh verify
命令验证一下
# bash install.sh verify
Microsoft SQL Server ODBC Driver V1.0 for Linux Installation Script
Copyright Microsoft Corp.
Starting install for Microsoft SQL Server ODBC Driver V1.0 for Linux
Checking for 64 bit Linux compatible OS ................................. FAILED
Checking required libs are installed ............................... NOT CHECKED
unixODBC utilities (odbc_config and odbcinst) installed ............ NOT CHECKED
unixODBC Driver Manager version 2.3.0 installed .................... NOT CHECKED
unixODBC Driver Manager configuration correct ...................... NOT CHECKED
Microsoft SQL Server ODBC Driver V1.0 for Linux already installed .. NOT CHECKED
!@#¥% 仔细查看脚本,原来脚本是为了RHEL编写的...
手动检查依赖使用ldd lib64/libsqlncli*
命令
# ldd lib64/libsqlncli*
linux-vdso.so.1 => (0x00007fff347ff000)
libcrypto.so.10 => not found
libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f605b3d3000)
librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f605b1ca000)
libssl.so.10 => not found
libuuid.so.1 => /lib/x86_64-linux-gnu/libuuid.so.1 (0x00007f605afc5000)
libodbcinst.so.1 => not found
!@#¥% 库也找不到?这还是要怪RHEL和debian的区别:库文件版本号的区别...
圆滑的解决方法..
cd /usr/lib/x86_64-linux-gnu/
ln -s libcrypto.so.1.0.0 libcrypto.so.10
ln -s libssl.so.1.0.0 libssl.so.10
cd /usr/local/lib/
ln -s libodbcinst.so.2.0.0 libodbcinst.so.1
export LD_LIBRARY_PATH=/usr/local/lib/:$LD_LIBRARY_PATH:
再次使用ldd lib64/libsqlncli*
命令验证,确认全部OK
。
安装方法也和官方有所不同,使用bash install.sh install --force
强制安装
# bash install.sh install --force
...
[YES]
Checking for 64 bit Linux compatible OS ................................. FAILED
Checking required libs are installed ............................... NOT CHECKED
unixODBC utilities (odbc_config and odbcinst) installed ............ NOT CHECKED
unixODBC Driver Manager version 2.3.0 installed .................... NOT CHECKED
unixODBC Driver Manager configuration correct ...................... NOT CHECKED
Microsoft SQL Server ODBC Driver V1.0 for Linux already installed .. NOT CHECKED
Microsoft SQL Server ODBC Driver V1.0 for Linux files copied ................ OK
Symbolic links for bcp and sqlcmd created ................................... OK
Microsoft SQL Server ODBC Driver V1.0 for Linux registered ........... INSTALLED
测试一下,依旧参照官方文档,使用sqlcmd -S localhost
:
# sqlcmd -S localhost
sqlcmd: error while loading shared libraries: libodbc.so.1: cannot open shared object file: No such file or directory
!@#¥% ....依旧需要圆润的解决
cd /usr/local/lib/
ln -s libodbc.so.2 libodbc.so.1
再测试一次
# sqlcmd -S localhost
terminate called after throwing an instance of 'std::runtime_error'
what(): locale::facet::_S_create_c_locale name not valid
!@#¥% ....命令控制台的locale问题,切到en_us.UTF8可破:使用dpkg-reconfigure locales
命令解决。
再再测试一次
sqlcmd -S localhost
Login timeout expired
这个就算正常了
配置unixOBDC
编辑/usr/local/etc/odbcinst.ini
,添加或确认有:
[SQL Server Native Client 11.0]
Description=Microsoft SQL Server ODBC Driver V1.0 for Linux
Driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0
Threading=1
UsageCount=1
添加一个数据源,编辑/usr/local/etc/odbc.ini
,输入:
[gdzc]
Driver=SQL Server Native Client 11.0
Description = ODBC connection via SQL Server Native Client 11.0
Trace=Yes
Server=192.168.137.93
Port=1433
Database = gdzc
- Driver 是指/usr/local/etc/odbcinst.ini 内链接的驱动名
- [gdzc] 是数据源名称,也就是DSN
- Database 是具体的某个数据库
使用isql
命令进行测试到数据源的链接。
# isql -v gdzc sa Aa123456
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
安装并测试pyodbc
在上述FreeTDS或Microsoft ODBC Driver 11 for linux两种配置二选一之后..
使用pip安装3.0.7版本的pyodbc
pip install https://pyodbc.googlecode.com/files/pyodbc-3.0.7.zip
简单的测试,使用odbc源gdzc,用户gdzc,密码Aa123456,数据库gdzc
import pyodbc
cnxn = pyodbc.connect('DRIVER={FreeTDS};DSN=gdzc;UID=gdzc;PWD=Aa123456;DATABASE=gdzc')
参考
- https://blog.afoolishmanifesto.com/posts/install-and-configure-the-ms-odbc-driver-on-debian/
- https://code.google.com/p/odbc/wiki/InstallingFreeTDS
- http://www.tryolabs.com/Blog/2012/06/25/connecting-sql-server-database-python-under-ubuntu/