Django2.1连接使用SQL Server(linux版)

声明

本人示例环境：

• 系统版本：centos7.1 64位
• 数据库版本：SQL Server2012
• python版本：Python 3.6.3
• Django版本：Django2.1
• 连接数据库工具：pyodbc，Microsoft ODBC Driver

之前已经写过一篇《python3连接使用SQL Server数据库(linux版)》，但发现Django进行连接时还是比较繁琐和头疼的，所以现在写个专门针对 linux + Django + SQL Server 组合的文章，在这里并没有使用freetds驱动开源工具，而是使用了和Windows一样的Microsoft ODBC Driver 11 for linux版，微软专门为linux用户开发支持的SQL server 驱动

Microsoft ODBC Driver 11 for linux 下载地址[linux6和linux7通用，实测centos7可用]

wget http://download.microsoft.com/download/6/A/B/6AB27E13-46AE-4CE9-AFFD-406367CADC1D/Linux6/sqlncli-11.0.1790.0.tar.gz

如果你需要更高版本的驱动程序请转到官网下载，附官网地址(建议旧版安装成功后再选择性升级)

https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017

ODBC驱动安装

用于数据库连接的驱动

yum -y install unixODBC* 

安装微软ODBC for linux驱动

解压并验证安装条件

tar xvf sqlncli-11.0.1790.0.tar.gz
cd sqlncli-11.0.1790.0
./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 ..................................... OK
Checking required libs are installed ........................................ OK
unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
unixODBC Driver Manager version 2.3.0 installed ......................... FAILED
unixODBC Driver Manager configuration correct ...................... NOT CHECKED
Microsoft SQL Server ODBC Driver V1.0 for Linux already installed .. NOT CHECKED
See /tmp/sqlncli.10874.17476.965/install.log for more information about installation failures.

查看依赖包

[root@localhost sqlncli-11.0.1790.0]# ldd lib64/libsqlncli*
	linux-vdso.so.1 =>  (0x00007fff973fe000)
	libcrypto.so.10 => /lib64/libcrypto.so.10 (0x00007f2deb899000)
	libdl.so.2 => /lib64/libdl.so.2 (0x00007f2deb695000)
	librt.so.1 => /lib64/librt.so.1 (0x00007f2deb48c000)
	libssl.so.10 => /lib64/libssl.so.10 (0x00007f2deb21a000)
	libuuid.so.1 => /lib64/libuuid.so.1 (0x00007f2deb015000)
	libodbcinst.so.1 => /lib64/libodbcinst.so.1 (0x00007f2deae02000)
	libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f2deab1a000)
	libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f2dea8cd000)
	libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f2dea5c4000)
	libm.so.6 => /lib64/libm.so.6 (0x00007f2dea2c2000)
	libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f2dea0ac000)
	libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f2de9e8f000)
	libc.so.6 => /lib64/libc.so.6 (0x00007f2de9acc000)
	libz.so.1 => /lib64/libz.so.1 (0x00007f2de98b6000)
	/lib64/ld-linux-x86-64.so.2 (0x00007f2dec053000)
	libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f2de96b1000)
	libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f2de947e000)
	libltdl.so.7 => /lib64/libltdl.so.7 (0x00007f2de9274000)
	libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f2de9065000)
	libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f2de8e61000)
	libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f2de8c47000)
	libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f2de8a1f000)
	libpcre.so.1 => /usr/local/lib/libpcre.so.1 (0x00007f2de8802000)

==如果遇到没有找到的依赖环境组件，不用担心，依赖组件是向下兼容的，在这里我做个示例：==

如果你看到

libodbcinst.so.1 => not found

可以建立个此缺少环境组件名称的软连接并指向此环境组件的高版本，如：

cd /usr/lib64
ln -s libodbcinst.so.2.0.0 libodbcinst.so.1

完成后可以使用ldd再次验证，确认全部可行后再进行下一步

ldd lib64/libsqlncli*

正式安装

使用install安装命令进行安装

./install.sh install --force

声明文件敲空格输入YES安装

Enter YES to accept the license or anything else to terminate the installation: YES
 
Checking for 64 bit Linux compatible OS ..................................... OK
Checking required libs are installed ........................................ OK
unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
unixODBC Driver Manager version 2.3.0 installed ......................... FAILED
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
 
Install log created at /tmp/sqlncli.2486.13833.4245/install.log.
 
One or more steps may have an *. See README for more information regarding
these steps.
 

登录数据库测试,当现实>1字符样式的时候就表示登录成功，驱动安装完成

sqlcmd -S 192.168.88.81 -U sa -P '123456'
>1

配置unixOBDC

vim /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

数据库连接测试

[root@localhost lib64]# isql -v Mydata sa '123456'
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> 
 

至此可以linux和python3可以正常连接和使用SQL Server了，但如果使用Django连接还需要做一下操作

django 连接组件

django-pyodbc-azure组件安装 用于Django setting对sqlserver的支持，不安装的话会报ModuleNotFoundError: No module named 'sql_server'的错误

pip3 install django-pyodbc-azure

Django，setting配置如下：

'ENGINE': 'sql_server.pyodbc',

django 配置

进入Django项目目录中 vim setting.py,在数据库配置项中配置以下信息[基本和Windows上边配置雷同]

DATABASES = {
  'default': {
    'ENGINE': 'sql_server.pyodbc',      # odbc连接固定写法
    'NAME': 'Mydata',                   # 需要连接的数据库名称
    'USER': 'sa',                       # 数据库连接账户
    'PASSWORD': '123456',               # 数据库连接密码
    'HOST': '192.168.88.81',            # 数据库服务地址
    'PORT': '1433',                     # 数据库连接端口
    'OPTIONS':{
    'driver':'SQL Server Native Client 11.0',
    'MARS_Connection': True,
    }
  }
}
 

配置完成后需要重启uwsgi服务才生效，uwsgi服务这里不做阐述，可以参考博文《Django部署-uwsgi配置》