Linux 下使用 Python 连接 Windows 服务器上的数据库

1. 需求

一台运行着 Python3 的 Linux 服务器，一台运行着 MS SQL Server 2008 的 Windows 服务器。需求是通过 Python3 连接 MS SQL Server 2008 来完成一些数据库操作。

2. 尝试过程遇到的问题

在网上找过 python 连接 MS-SQL 的解决方案，找到了 pymssql，可惜只支持 Python2。所以 Python3 只能寻求其它包来解决。最终找到 ODBC 方案，通过使用 unixodbc 来连接数据库，而 MS-SQL 的 ODBC 驱动则由 FreeTDS （FreeTDS is a set of libraries for Unix and Linux that allows your programs to natively talk to Microsoft SQL Server and Sybase databases.）来提供。

3. 安装必要软件

# Install pre-requesite packages
sudo apt-get install unixodbc unixodbc-dev freetds-dev freetds-bin tdsodbc

4. 配置文件：

• /etc/odbc.ini
• /etc/odbcinst.ini
• /etc/freetds/freetds.conf

4.1 odbcinst.ini 配置

[FreeTDS]
Description = v0.91 with protocal v7.3
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so

4.2 odbc.ini 配置

[mySQLServer]
TDS_Version = 7.3
Driver = FreeTDS
Server = 192.168.1.66
Port = 1433
Database = DBname_To_Link

注意里面的 Driver 字段值 FreeTDS 跟 odbcinst.ini 中的配置项 FreeTDS 要一致，Database 字段这里如果不指定，那么可以在后面实际连接的时候指定。

4.3 freetds.conf 配置

文件最后添加：

[mySQLServer]
      host = 192.168.1.66
      port = 1433
      tds version = 7.3

注意配置项 mySQLServer 跟 odbc.ini 配置中的配置项 mySQLServer 要一致

参考摘录：
来源：http://stackoverflow.com/questions/33341510/how-to-install-freetds-in-linux

Point odbcinst.ini to the driver in /etc/odbcinst.ini:

[FreeTDS]
Description = v0.91 with protocol v7.2
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

Create your DSNs in odbc.ini:

[dbserverdsn]
Driver = FreeTDS
Server = dbserver.domain.com
Port = 1433
TDS_Version = 7.2

…and your DSNs in freetds.conf:

[global]

# TDS protocol version, use:


# 7.3 for SQL Server 2008 or greater (tested through 2014)


# 7.2 for SQL Server 2005


# 7.1 for SQL Server 2000


# 7.0 for SQL Server 7

tds version = 7.2
port = 1433


# Whether to write a TDSDUMP file for diagnostic purposes


# (setting this to /tmp is insecure on a multi-user system)

;   dump file = /tmp/freetds.log
;   debug flags = 0xffff


# 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


# A typical Microsoft server

[dbserverdsn]
host = dbserver.domain.com
port = 1433
tds version = 7.2

After completing this, you can test your connection by attempting to connect with tsql (to test the FreeTDS layer) and isql (for the unixODBC through FreeTDS stack).

5. 测试

5.1 测试 FreeTDS 连接是否正常

tsql  { -S servername [-I interface] | -H hostname -p port } -U username [-P password] [-o options]

5.2 测试 unixODBC 连接是否正常

isql DSN [UID [PWD]] [options]

6. 使用

方法一，使用 DSN 连接：

import pyodbc
# conn = pyodbc.connect("DSN=mySQLServer;UID=uid;PWD=password")    # -- 如果 odbc.ini 中指定了数据库名
conn = pyodbc.connect("DSN=mySQLServer;Database=DBname_To_Link;UID=uid;PWD=password")   # -- 如果 odbc.ini 中没有指定数据库名
cursor = conn.cursor()
cursor.execute("select name from Users")
for i in cursor:
    print(i)

方法二，使用 Driver 连接：

import pyodbc
conn = pyodbc.connect("Driver=FreeTDS;Server=192.168.1.66;Database=DBname_To_Link;UID=uid;PWD=password")   # -- 参数跟 odbc.ini 中的配置一致，注意这里 Driver 项无需花括号 {} 括起。
cursor = conn.cursor()
cursor.execute("select name from Users")
for i in cursor:
    print(i)

---

至此，Linux + Python + unixODBC + FreeTDS + SQLServer 完成配置。