python访问MSSQLServer，需要用到pymssql包

pip install pymssql

报错，缺少VC编译环境

 building '_mssql' extension
  error: Microsoft Visual C++ 14.0 is required. Get it with "Microsoft Visual C++ Build Tools": http://landinghub.visualstudio.com/visual-cpp-build-tools

  ----------------------------------------
  Failed building wheel for pymssql
  Running setup.py clean for pymssql
Failed to build pymssql
Installing collected packages: pymssql
  Running setup.py install for pymssql ... error
    Complete output from command C:\Users\lisha\Anaconda3\python.exe -u -c "import setuptools, tokenize;__file__='C:\\Users\\lisha\\AppData\\Local\\Temp\\pip-build-z0jg8xo7\\pymssql\\setup.py';f=getattr(tokenize, 'open', open)(__file__);code=f.read().replace('\r\n', '\n');f.close();exec(compile(code, __file__, 'exec'))" install --record C:\Users\lisha\AppData\Local\Temp\pip-32t5apfg-record\install-record.txt --single-version-externally-managed --compile:
    setup.py: platform.system() => 'Windows'
    setup.py: platform.architecture() => ('64bit', 'WindowsPE')
    running install
    running build
    running build_ext
    cythoning _mssql.pyx to _mssql.c
    warning: _mssql.pyx:143:4: Exception already a builtin Cython type
    building '_mssql' extension
    error: Microsoft Visual C++ 14.0 is required. Get it with "Microsoft Visual C++ Build Tools": http://landinghub.visualstudio.com/visual-cpp-build-tools

    ----------------------------------------
Command "C:\Users\lisha\Anaconda3\python.exe -u -c "import setuptools, tokenize;__file__='C:\\Users\\lisha\\AppData\\Local\\Temp\\pip-build-z0jg8xo7\\pymssql\\setup.py';f=getattr(tokenize, 'open', open)(__file__);code=f.read().replace('\r\n', '\n');f.close();exec(compile(code, __file__, 'exec'))" install --record C:\Users\lisha\AppData\Local\Temp\pip-32t5apfg-record\install-record.txt --single-version-externally-managed --compile" failed with error code 1 in C:\Users\lisha\AppData\Local\Temp\pip-build-z0jg8xo7\pymssql\

解决这类错误，我们已经有经验了，到Unofficial Windows Binaries for Python Extension Packages就好了嘛

网页中搜索到pymssql，把相应版本的文件下载回来

pip install pymssql-2.1.3-cp36-cp36m-win_amd64.whl

安装过程很简单

Paste_Image.png

上代码：

# -*- codeing: utf-8 -*-

import pymssql


class MSSQL:
    """
    对pymssql的简单封装
    pymssql库，该库到这里下载：http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql
    使用该库时，需要在Sql Server Configuration Manager里面将TCP/IP协议开启

    用法：

    """

    def __init__(self,host,user,pwd,db):
        self.host = host
        self.user = user
        self.pwd = pwd
        self.db = db

    def __GetConnect(self):
        """
        得到连接信息
        返回: conn.cursor()
        """
        if not self.db:
            raise(NameError,"没有设置数据库信息")
        self.conn = pymssql.connect(host=self.host,user=self.user,password=self.pwd,database=self.db,charset="utf8")
        cur = self.conn.cursor()
        if not cur:
            raise(NameError,"连接数据库失败")
        else:
            return cur

    def ExecQuery(self,sql):
        """
        执行查询语句
        返回的是一个包含tuple的list，list的元素是记录行，tuple的元素是每行记录的字段

        调用示例：
                ms = MSSQL(host="localhost",user="sa",pwd="123456",db="PythonWeiboStatistics")
                resList = ms.ExecQuery("SELECT id,NickName FROM WeiBoUser")
                for (id,NickName) in resList:
                    print str(id),NickName
        """
        cur = self.__GetConnect()
        cur.execute(sql)
        resList = cur.fetchall()

        #查询完毕后必须关闭连接
        self.conn.close()
        return resList

    def ExecNonQuery(self,sql):
        """
        执行非查询语句

        调用示例：
            cur = self.__GetConnect()
            cur.execute(sql)
            self.conn.commit()
            self.conn.close()
        """
        cur = self.__GetConnect()
        cur.execute(sql)
        self.conn.commit()
        self.conn.close()

def main():
    ms = MSSQL(host="localhost",user="sa",pwd="supermap",db="test")

    ms.ExecNonQuery("create table a(a int, b int, c int)")
    ms.ExecNonQuery("insert into a values (1,2,3)")
    ms.ExecNonQuery("insert into a values (4,5,6)")

    resList = ms.ExecQuery("SELECT * from a")
    for (a,b,c) in resList:
        print(a,b,c)

if __name__ == '__main__':
    main()

需要注意的是：

读取数据的时候需要decode，写数据的时候需要encode，这样就可以避免烦人的中文报错问题。