計劃把 文章全部轉移至 這裡,而本 Blogger 站台的文章近 500 篇,我預計在 2014-12-31 前移轉完畢,完成後 將只作代轉服務,一律把舊連結如 轉成 ,敬請舊雨新知互相走告。


何岳峰 敬上

2008年3月24日 星期一

How to connect MS SQL Server with Python in the Linux OS?

Basically, the general method is using ODBC Interface. In the Unix environment, we can use unixODBC library, but i had tried in failure.

So... another method is using hard core TDS protocol of Sybase Server. because MicroSoft buy the MSSQL server source code from Sybase, so they use the same protocol. maybe there are some difference between the servers. but i could not found it as far.

and i wanted using Python to program, so the requirement is below here:
  • freetds( TDS protocol library written in C
  • python-sybase( python interface for controll freetds library
  • python-devel (using in compile python-sybase)
Installation in Ubuntu is so easy, try apt-get install python-devel first. and then install freetds and python-sybase. command is below here:

# tar -zxf freetds.tgz

# cd freetds

# ./configure --prefix=/usr/local/freetds

# make

# sudo make install

# tar -zxf python-sybase.0.37.tgz #PS i try 0.37, because 0.38 is having something wrong in my server

# cd python-sybase.0.37

# sudo SYBASE=/usr/local/freetds \
LD_LIBRARY_PATH=/usr/local/freetds/lib:${LD_LIBRARY_PATH} \
python install

OK! installation is done!

try the freetds library is OK!

# /usr/local/freetds/bin/tsql -H {ServerIP} -p 1433 -U {Username} -P {Password}

> EXEC sp_databases

> go

this 『EXEC sp_databases』 command will show the all databases that this {Username} can read.

> use testdb

> EXEC sp_tables

> go

this 『EXEC sp_tables』 command will show the all tables of testdb.

> EXEC sp_columns @table_name = 'users'

> go

this 『EXEC sp_columns @table_name = 'users'』 command will show the all fields of table 'users'.

if everything seen OK! you can try Sybase module in the python interpreter.

# SYBASE=/usr/local/freetds CFLAGS="-DHAVE_FREETDS" LD_LIBRARY_PATH=/usr/local/freetds/lib /usr/bin/python
 1 import Sybase

2 class DictCursor(Sybase.Cursor):

3 def row2dict(self, row):

4 d = {}

5 for i,elt in enumerate(row):

6 d[self.description[i][0]] = elt

7 return d

8 def fetchall(self):

9 rows = Sybase.Cursor.fetchall(self)

10 result = []

11 for row in rows:

12 result.append(self.row2dict(row))

13 return result

14 def fetchone(self):

15 return row2dict(Sybase.Cursor.fetchone(self))

16 def fetchmany(self):

17 rows = Sybase.Cursor.fetchmany(self)

18 result = []

19 for row in rows:

20 result.append(self.row2dict(row))

21 return result

22 class Connection(Sybase.Connection):

23 def dictcursor(self):

24 return DictCursor(self)

25 db = Connection(ip:port, account, password, dbname)

26 c = db.dictcursor()

27 sql = "select * from sometable"

28 c.execute(sql)

29 rows = c.fetchall()

30 for row in rows:

31 for k, v in row.items():

32 print k, v

33 print 'done'
OK! this is real done!

1 則留言:


Related Posts Plugin for WordPress, Blogger...