Centos5 + Python + pyodbc + freetds + unixodbc + SQL Server

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