Connect to MSSql Server using Python in Linux

Origin
Because the customer requested to deploy mssql server 2008 on the application, and we were used to developing server programs in Linux, we had the need to connect mssql server 2008 with python in linux. This demand is very difficult. We have spent a lot of time on it, but it is still not done. Later, I took the vcc In the python-cn list and used ssh to directly perform the operations on the machine. I believe there will be a lot of people in the future, so I will write out the experience here, it is convenient for later users to have a guiding document when releasing applications in the future.
At the time of writing this article, we installed Ubuntu 8.04 on the experimental machine. If you are using other linux distributions, please change the shell command accordingly.

Install unixODBC
UnixODBC is ODBC driver manager under linux, Official Website: http://unixodbc.org. First, make sure that you have installed it. If you are in ubuntu/debian, run the following command:
Sudo apt-get install unixODBC-bin
You can install unixODBC with the graphical ODBC manager ODBCConfig, which looks like this:

If you are sure that it will work in the command line, you can not install unicODBC-bin. In addition, the subsequent operations are also completed through the command line, and the ODBCConfig is not used. This is only for the convenience of GUI lovers.

Install Freetds
Freetds is the most widely used ODBC driver in Linux. Official Website: http://www.freetds.org /. Because we use version 0.82, while ubuntu is still version 0.63, we recommend that you run sudo apt-get remove freetds-dev to delete the previously installed version and then download version 0.82 or later.
Pay attention to the configure parameters when compiling freetds. I guess the parameters are not correctly set at the time of compilation, so I have never been able to connect to mssql server. Vcc shares its compilation parameters as follows:
./Configure \
-- Prefix =/etc/freetds \
-- With-tdsver = 8.0 -- enable-msdblib \
-- Enable-dbmfix \
-- With-gnu-ld \
-- Enable-shared \
-- Enable-static
Execute configure and then run sudo make and sudo make install. wait patiently until the installation is complete.
After the installation is complete, run the tsql test. The test method is as follows:
Tsql-H mssql-server-ip-U username-P password
1> the prompt is successful.

Configure freetds
The configuration file of Freetds is located in/etc/freetds. conf. After it is opened, add the following section at the end:
138 [db4]
139 host = 192.168.18.4
140 port = 1433
141 tds version = 7.0
Here, the host is the address of mssql server, and the tds version specifies which tds version is used. According to vcc, the compatibility with 7.0 is better. I tested it and 8.0 is also acceptable.
After the test is completed, use tsql to test it. The test method is as follows:
Tsql-S db4-U username-P password
1. The identifier is successful.

Configure ODBC driver
Next, we need to configure an ODBC driver, which is stored in the/etc/odbcinst. ini file. After opening it with vi, write the following content:
1 [SQL Server]
2 Description = MS-SQLServer
3 Driver =/usr/local/freetds/lib/libtdsodbc. so
4 Driver64 =/usr/local/freetds/lib/libtdsodbc. so
5 Setup =/usr/lib/odbc/libtdsS. so
6 Setup64 =/usr/lib/odbc/libtdsS. so
7 UsageCount = 1
8 CPTimeout =
9 CPReuse =
10 FileUsage = 1
In this way, we configure a driver named SQL Server. If your odbcinst. ini is empty before, you may need a section like this:
12 [ODBC]
13 Trace = Yes
14 TraceFile =/tmp/SQL. log
15 ForceTrace = No
16 Pooling = Yes
Trace specifies whether to write logs. Writing logs Reduces Program performance. In the release version, remember to change the Trace value to No.

1 2 Next Page