Python +SQL Server 框架及返回结果处理

Python + SQL Server 返回结果处理
如何搭建python + sql server 框架以及对数据库查结果值的使用。

config 文件夹下 sqlconfig.ini

[sqlserver]
host = 123.123.0.123
user = user-name
pwd = password
db = DB-name

config 文件夹下sqlconfig.py

import configparser
import os

_ini_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'sqlconfig.ini').replace('\\', '/')
_conf = configparser.ConfigParser()
_conf.read(_ini_path, encoding='utf-8')
host = _conf.get('sqlserver', 'host')
user = _conf.get('sqlserver', 'user')
pwd = _conf.get('sqlserver', 'pwd')
db = _conf.get('sqlserver', 'db')
_conf.clear()

common文件夹下sqlclient.py

from configs.sqlconfig import *
import pymssql
import logging

class SqlClient:
    def __init__(self, host=host, user=user, pwd=pwd, db=db):
        self.host = host
        self.user = user
        self.pwd = pwd
        self.db = db        # 默认连接VATVerification8020数据库

    def __getConnect(self):
        """建立SQL连接"""
        try:
            self.conn = pymssql.connect(host=self.host, user=self.user, password=self.pwd, database=self.db, charset="utf8")
            cur = self.conn.cursor()
        except pymssql.OperationalError:
            pass
        except Exception as e:
            _logger.error('444:' % e)
        else:
            return cur

    def query(self, sql):
        """
        :param sql: 查询语句
        :return: 查询结果
        """
        cur = self.__getConnect()
        try:
            cur.execute(sql)
            resList = cur.fetchall()
            self.conn.close()
            _logger.info('000')
            return resList
        except AttributeError:
            logging.error('1:%s' % 'connect sqlserver failed! SQL:{sql}'.format(sql=sql))
        except pymssql.ProgrammingError:
            logging.error('2:%s' % 'db error! SQL:{sql}'.format(sql=sql))


    def executeSQL(self, sql):
        """
        :param sql: 非查询语句
        :return: None
        """
        cur = self.__getConnect()
        cur.execute(sql)
        self.conn.commit()
        self.conn.close()

_sqlClient = SqlClient()

if __name__ == "__main__":
    result = _sqlClient.query("select name from user")
    print(type(result))
    print(result[1])
    print(type(result[1]))
    rr=  list(str(x) for (x,) in result)
    print(rr[1])

执行结果：

从结果来看，sql查询的结果是个list,每个结果值是个 元组（tuple），如果想取具体的值可以使用 list(str(x) for (x,) in result) 对返回结果进行转换，把list的元素转换成str,在组成一个list.然后就可以对list 的数据进行对比…等其他操作啦。