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