在Ubuntu下从Python连接到Microsoft SQL Server数据库

Free tools are great, but the world ain’t all sunshine and rainbows. Sometimes, we may need to connect to a Microsoft SQL Server database from one of our Python applications running under Linux. Fortunately, there are ways to achieve this.

免费工具很棒，但是世界上并非只有阳光和彩虹。 有时，我们可能需要从Linux下运行的Python应用程序之一连接到Microsoft SQL Server数据库。 幸运的是，有一些方法可以实现这一目标。

I am assuming we got this:

我假设我们得到了：

• A Microsoft SQL Server installation running under Windows. I tested this using Microsoft SQL Server 2008 R2 Express, but hopefully this will work with other versions as well.
• Ubuntu Linux. I am using 12.04 LTS.

• 在Windows下运行的Microsoft SQL Server安装。 我使用Microsoft SQL Server 2008 R2 Express进行了测试，但希望它也可以与其他版本一起使用。
• Ubuntu Linux。 我正在使用12.04 LTS。

Without further ado, here are the steps you should follow to get this working.

事不宜迟，请按照以下步骤进行操作。

1. SQL Server设置 (1. SQL Server setup)

Your SQL Server installation must be setup to allow external connections. If the DB is not administered by you this might not be a problem, but in case you do have administrator level access and need to do it yourself, read here.

您必须将SQL Server安装设置为允许外部连接 。 如果数据库不是由您管理的，那么这可能不是问题，但是如果您确实具有管理员级别的访问权限，并且需要自己进行访问， 请阅读此处 。

Now you must have setup a port in which SQL Server is listening. Remember it.

现在，您必须设置一个SQL Server正在侦听的端口。 记住它。

Make sure you are not blocked by Windows firewall or such when you attempt to connect to the Windows computer. Attempting a telnet will help us check if there are connection problems. For example, try running telnet from Ubuntu and check the connection doesn’t fail.

当您尝试连接到Windows计算机时，请确保您没有被Windows防火墙等阻止。 尝试使用telnet将有助于我们检查是否存在连接问题。 例如，尝试运行telnet 从Ubuntu上检查连接是否没有失败。

Regarding authentication, I have only tried this with the sa login enabled (ie. not using Windows Authentication). You may read on how to do that here.

关于身份验证 ，我仅在启用sa login （即不使用Windows身份验证）的情况下尝试过此操作。 您可以在此处阅读如何操作。

2.在Ubuntu下安装所需的软件包 (2. Install required packages under Ubuntu)

These are the things we are going to need:

这些是我们将需要的东西：

• FreeTDS is is a set of libraries that allows programs to natively talk to Microsoft SQL Server databases. It’s what we usually call a driver.
• UnixODBC acts as a driver manager and is the implementation of the ODBC API.
• pyodbc is a Python 2.x and 3.x module that allows you to use ODBC to connect to almost any database.

• FreeTDS是一组库，允许程序与Microsoft SQL Server数据库进行本地对话。 这就是我们通常所说的驱动程序。
• UnixODBC充当驱动程序管理器，并且是ODBC API的实现。
• pyodbc是Python 2.x和3.x模块，允许您使用ODBC连接到几乎所有数据库。

From a terminal, run:

在终端上，运行：

sudo apt-get install unixodbc unixodbc-dev freetds-dev tdsodbc
sudo apt-get install unixodbc unixodbc-dev freetds-dev tdsodbc
 

From the Virtualenv of our Python application (if you are not using one, you should!) run pip install pyodbc.

在我们的Python应用程序的Virtualenv中（如果您不使用它，则应该使用！）运行pip install pyodbc 。

3.在FreeTDS的设置中设置服务器 (3. Setup server in FreeTDS’s settings)

Edit the file /etc/freetds/freetds.conf and replace placeholders appropriately. Note that we are calling our server sqlserver.

编辑文件/etc/freetds/freetds.conf并适当地替换占位符。 请注意，我们正在调用服务器sqlserver。

After this you can test the connection with this command:

之后，您可以使用以下命令测试连接：

tsql -S sqlserver -U <username> -P <password>
tsql -S sqlserver -U <username> -P <password>
 

Then run some SQL Server command to make sure everything works fine. For example you may run a DB query like this:

然后运行一些SQL Server命令以确保一切正常。 例如，您可以运行如下数据库查询：

If it worked, it will print the results of the query. Quit with Ctrl+D.

如果可行，它将打印查询结果。 使用Ctrl + D退出。

4.设置unixODBC以使用FreeTSD并添加数据源 (4. Setup unixODBC to use FreeTSD & add a data source)

First, run odbcinst -j to know where our configuration files are located. We will need to edit two files: the “drivers” and “system data source”. I assume they are /etc/odbcinst.ini and /etc/odbc.ini respectively, but the output of the command will tell you this.

首先，运行odbcinst -j来了解我们的配置文件的位置。 我们将需要编辑两个文件：“驱动程序”和“系统数据源”。 我假设它们分别是/etc/odbcinst.ini和/etc/odbc.ini，但是命令的输出将告诉您这一点。

Edit /etc/odbcinst.ini like this:

像这样编辑/etc/odbcinst.ini：

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
# Some installations may differ in the paths
#Driver = /usr/lib/odbc/libtdsodbc.so
#Setup = /usr/lib/odbc/libtdsS.so
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
CPTimeout =
CPReuse =
FileUsage = 1
[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
# Some installations may differ in the paths
#Driver = /usr/lib/odbc/libtdsodbc.so
#Setup = /usr/lib/odbc/libtdsS.so
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
CPTimeout =
CPReuse =
FileUsage = 1
 

If the paths for Driver and Setup do not work in your installation, you can find where these files are located by running find / -name “libtds*“.

如果驱动程序和安装程序的路径在您的安装中不起作用，则可以通过运行find / -name“ libtds *”来找到这些文件的位置。

Edit /etc/odbc.ini like this, to add a data source named sqlserverdatasource:

像这样编辑/etc/odbc.ini，以添加一个名为sqlserverdatasource的数据源：

Now you may test the connection to out data source works by running isql -v sqlserverdatasource .

现在，您可以通过运行isql -v sqlserverdatasource来测试到数据源工作的连接 。

5.从Python应用程序连接到我们的数据源 (5. Connect to our data source from a Python application)

翻译自: https://www.pybloggers.com/2012/06/connecting-to-a-microsoft-sql-server-database-from-python-under-ubuntu/