python连接sqlserver数据库

python连接sqlserver数据库

1、先pip安装pymssql

pip install pymssql

2、直接上代码

• 以下是封装了连接sqlserver的

#!/usr/bin/env python3
#-- coding: utf-8 --
# sqlserver的连接
import pymssql
from logger import logger


class MSSQL:
    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:
            logger.error("没有设置数据库信息")
            raise(NameError,"没有设置数据库信息")
        self.conn = pymssql.connect(host=self.host,user=self.user,password=self.pwd,database=self.db,charset=self.char)
        cur = self.conn.cursor()
        if not cur:
            logger.error("连接数据库失败")
            raise(NameError,"连接数据库失败")
        else:
            return cur

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

        """
        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="127.0.0.1:1433",user="sa",pwd="123456",db="dbName")
    findsql = 'select * from students'
    logger.info(findsql.encode('utf8'))
    resList = ms.ExecQuery(findsql)
    print(resList)

if __name__ == '__main__':
    main()

Q.E.D.