Python-> pyodbc-> unixodbc-> freetds-> SQL Server
1. Install freetds
1. Installation
# ./configure --prefix=/etc/freetds --with-tdsver=8.0 --enable-msdblib --enable-dbmfix --with-gnu-ld --enable-shared --enable-static
# make
# make install
2. Connect to the database
# tsql -H 192.168.0.204 -p 1433 -U sa
Password:
locale is "en_US.UTF-8"
locale charset is "UTF-8"
1>
Note: If the following errors occur
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20017 (severity 9):
Unexpected EOF from the server
OS error 115, "Operation now in progress"
Error 20002 (severity 9):
Adaptive Server connection failed
There was a problem connecting to the server
The connection may be related to the version by using the following methods:
# TDSVER=8.0 tsql -H 10.88.51.167 -p 1433 -U sa
Ii. Install unixodbc
1. Installation
# ./configure --prefix=/usr/local/unixODBC-2.3.0 --includedir=/usr/include --libdir=/usr/lib -bindir=/usr/bin --sysconfdir=/etc
# make
# make install
2. Configure ODBC
When configuring ODBC, we can add content directly to the two configurations. However, this method is not recommended. We recommend that you use the odbcinst command to install the driver information and data source information.
First, configure the ODBC driver
Create a file of TDS. Driver. template, enter the following content, and save the file:
[TDS]
Description = FreeTDS Driver for Linux & MSSQL on Win32
Driver = /usr/local/lib/libtdsodbc.so
Add Configuration
# odbcinst -i -d -f tds.driver.template
Then, configure the data source
Create a file named TDs. datasource. template, enter the following content, and save the file:
[10_88_51_167]
Description = Connection to windows virtual machine
Driver = TDS
Trace = No
Database = tempdb
Server = 10.88.51.167
Port = 1433
TDS_Version = 7.0
Add Configuration
# odbcinst -i -s -l -f tds.datasource.template
3. Connect to the database
# isql 10_88_51_167 sa password
+---------------------------------------+
| Connected! |
|
| SQL-statement |
| Help [tablename] |
| Quit |
|
+ --------------------------------------- +
SQL>
If this prompt appears, the connection is successful.
3. Install pyodbc
# python setup.py build
# python setup.py install
Note: If the following error occurs:
libodbc.so.1: open failed: No such file or directory
The Connection Library is not found. Please add:
# export LD_LIBRARY_PATH=/usr/local/lib
Add and then install
# python
Python 2.6 (r26:66714, Nov 10 2011, 09:56:55)
[GCC 4.1.2 20080704 (Red Hat 4.1.2-44)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>>
4. Connect to the database
# -*- coding: utf-8 -*-
import pyodbc
db = pyodbc.connect('DRIVER={TDS};Server=10.88.51.167,1433;DATABASE=tempdb;UID=sa;PWD=sa_DnMirr12;TDS_Version=8.0')
cursor = db.cursor()
recSet = cursor.execute('select top 10 name from sys.objects').fetchall()
for recOne in recSet:
print(recOne.name)
cursor.close()
db.close()
Remember to add tds_version. Otherwise, the data source cannot be found.
[root@localhost python]# python test.py
sysrscols
sysrowsets
sysallocunits
sysfiles1
syspriorities
sysfgfrag
sysphfg
sysprufiles
sysftinds
sysowners
After a long study, I hope it will help you.
V. References
http://hi.baidu.com/sunny_xinxue/blog/item/f1bf6c33df54dea35edf0eb9.html
http://stackoverflow.com/questions/6973371/freetds-problem-connecting-to-sql-server-on-mac-unexpected-eof-from-the-server
http://www.ibm.com/developerworks/cn/linux/database/odbc/
http://www.unixodbc.org/doc/FreeTDS.html#GettingIt
http://www.easysoft.com/support/kb/kb01004.html