八月 4, 2011

hoamon's sandbox
hoamon
hoamon's sandbox is about »

tag cloud

» Connect MS SQL Server with python-pymssql

Two years ago, i writed a blog: "How to connect MS SQL Server with Python in the Linux OS?". I used freetds + python-sybase in this Howto. Now i have to upgrade the linux server to x86_64 architecture(original in i386), then the old python-sybase package have always been failured. The newest version released at 2010 DEC, but i tried all the 39, 38, 36 versions that no one can compile accurately.

Fortunately, my underclassman talked to me about python-pymssql. And it has been packaged in the Ubuntu, so i just use the magic command:

$ sudo apt-get install python-pymssql

My Linux server takes the power back!!!

The usage likes python-sybase, below is the example:


1 #!/usr/bin/env python
2 import sys, datetime, pymssql
3 from types import IntType
4
5 DB = {
6 'ip': '127.0.0.1',
7 'port': '1433',
8 'user': 'user',
9 'password': 'password',
10 'database': 'database',
11 }
12
13 try:
14 Database = pymssql.connect(host=':'.join([DB['ip'], DB['port']]), user=DB['user'], password=DB['password'], database=DB['database'], as_dict=True)
15 except pymssql.OperationalError, msg:
16 print "Could not Connection SQL Server"
17 sys.exit()
18 else:
19 DBCursor = Database.cursor()
20
21 sql = "select * from data_table"
22 print('sql: %s' % sql)
23 DBCursor.execute(sql)
24
25 while 1:
26 row = DBCursor.fetchone()
27 if not row: break
28
29 for k, v in row.items():
30 if type(k) == IntType: continue
31
32 if k.lower() in ('some_date_field', ):
33 # change field type
34 if type(v) == datetime.date:
35 value = v
36 else:
37 try:
38 value = datetime.date(*time.strptime(v, '%Y/%m/%d')[:3])
39 except:
40 value = None
41 else:
42 value = unicode(str(v), 'cp950')
43
44 print('%s => %s'%(k, v))

三月 24, 2008

hoamon's sandbox
hoamon
hoamon's sandbox is about »

tag cloud

» 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(http://www.freetds.org/): TDS protocol library written in C
  • python-sybase(http://www.freetds.org/userguide/python.htm): 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 python setup.py 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!

biggo.com.tw

A Django site.