linux python2.7 mssqlserver_Linux下Python连接MS SQL Server

上次说搞了SQL Server的主备模式，这次就说说用Python连接SQL Server吧。不过这次是全套头的开源解决方案哦。所需环境还是惯用的那一套 ：Ubuntu 1204 LTS 64bit.

SQLServer是一款实实在在的商业软件，m$出品，似乎和开源，免费之类的词语搭不上边。但既然有share folder的逆向工程Samba，就一定会有SQLServer的逆向工程。 FreeTDS

就是这个东西。

安装FreeTDS

官方的说法FreeTDS is a set of libraries forUnixandLinuxthat allows your programs to natively talk to Microsoft SQL Server and Sybase databases.(FreeTDS提供了一套*nix下访问SQL Server或者Sybase的lib.)好吧，在我的概念里，Sybase是一套“传说中的数据库”——只听过，没见过有什么人用。这次只讲SQL Server。比较2的一件事是，FreeTDS竟然有win版本。汗，win下搞个SQL Server的客户端是一个“下载打开+下一步到底”的过程。

Ubuntu下的FreeTDS是已经有现成的了：

apt-get install freetds-common freetds-bin freetds-dev

装好之后会在/etc/freetds下找到freetds.conf配置文件。正常情况下可以不需要修改，但建议在[global]下增加一个dump file = a.log选项，以便调试——作为一个逆向工程，详细点的log总没有错的。

如果安装了freetds-bin的话，你就可以在控制台下直接连接SQL Server：

$tsql -S HOST_NAME_YOURS-U USER_NAME

Password:

Msg 20009, Level 9, State -1, Server OpenClient, Line -1

Unable to connect: Adaptive Server is unavailable or does not exist

There was a problem connecting to the server

安装pymssql

说起来你同样可以通过apt-get的方式安装pymssql，但实测下来有个没法让我容忍的缺陷：在连接SQL Server 08版本之后，任何select操作都返回一个空串。只能在 官方站点

下载2.0的beta版本。

apt-get install cython python-dev

python setup.py build && python setup.py install

代码范例

pymssql还是很符合python 的db api规范的，很多代码几乎都可以拿来套用。

try:

import pymssql

except ImportError:

print ("Your environment didn't support pymssql module, please intall it!")

exit(255)

class DBConnection:

__connection = None

__cursor = None

def __init__(self, config):

self.connect(config)

def __del__(self):

if self.__cursor is not None: self.__cursor.close()

if self.__connection is not None: self.__connection.close()

def connect(self, config):

try:

self.__connection = pymssql.connect(

host = config['hostname'],

user = config['username'],

password = config['password'],

database = config['database']

)

except:

print ("Can't connect to host %s" % config['hostname'])

exit(255)

self.__cursor = self.__connection.cursor()

def read(self, sql):

try:

self.__cursor.execute(sql)

except:

print ("Execution error!")

exit(255)

return self.__cursor.fetchall()

def bathWrite(self, lSql):

try:

sql = ''

for line in lSql:

if line[:2].lower() != 'go':

sql += line

else:

self.__cursor.execute(sql + line[2:])

sql = ''

if sql != '' : self.__cursor.execute(sql)

self.__connection.commit()

return True

except :

self.__connection.rollback()

return False

def write(self, sql):

try:

self.__cursor.execute(sql)

self.__connection.commit()

return True

except:

print ("Execution error!")

return False

def testConnection(self): return self.read('SELECT 1+1;')

if __name__ == '__main__':

conf = {

'hostname': '192.168.1.1',

'database': 'master',

'username': 'sa',

'password': '1234',

}

c = DBConnection(conf)

print c.testConnection()

由于采用了事务化的处理模式，对于很多数据库的改变，pymssql类是没有办法操作的，只能采用更低级的_mssql类下的execute_non_query操作，不过事实上区别不大。

try:

import _mssql

except ImportError:

print ("Your environment didn't support pymssql module, please intall it!")

exit(255)

class DBManager:

__connection = None

def __init__(self, config):

self.connect(config)

def __del__(self):

if self.__connection is not None: self.__connection.close()

def connect(self, config):

try:

self.__connection = _mssql.connect(

server = config['hostname'],

user = config['username'],

password = config['password'],

port = 1433,

database = config['database'],

charset='UTF-8'

)

except:

print ("Can't connect to host %s" % config['hostname'])

exit(255)

def do(self, sql):

self.__connection.execute_non_query(sql)

def createDB(self, databaseName):

sql = "CREATE DATABASE %s" % databaseName

self.do(sql)

def dropDB(self, databaseName):

sql = "DROP DATABASE %s" % databaseName

self.do(sql)

def flushDB(self, databaseName):

self.dropDB(databaseName)

self.createDB(databaseName)

if __name__ == '__main__':

conf = {

'hostname': "this_host",

'database': 'master',

'username': "sa",

'password': "1234",

}

c = DBManager(conf)

c.flushDB('qwe')

anyShare

分享到：

新浪微博人人网百度贴吧开心网百度空间FaceBookGooglePlus