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 for Unix and Linux that 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')

 

推荐阅读:
自从Centos7/Redha
之前我们通过几个概念简单的介绍
自打从硬件方向研究性能优化起,
事出一个朋友问题:通过Dock

发表评论

电子邮件地址不会被公开。 必填项已用*标注

请补全下列算式: *

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据