L4-深度分析Python数据库(SQLServer)访问中的连接

最新推荐文章于 2024-01-17 12:03:47 发布

原创最新推荐文章于 2024-01-17 12:03:47 发布 · 428 阅读

0 ·

CC 4.0 BY-SA版权

Python实战专栏收录该内容

18 篇文章

订阅专栏

本文详细介绍了如何使用Python的pymssql库进行数据库访问，包括环境准备、数据库连接和断开、查询和DML操作，以及游标管理等核心内容。特别关注了在执行多次查询时的连接和游标管理，避免数据覆盖问题。

1.环境准备

首先就是要安装包，直接使用pip命令安装即可.

pip install pymssql

2.Python-pymssql库的数据库访问分析

参考下图，描述了数据库连接在单次访问中的创建与关闭。
值得注意的是

当脚本类型是DML（增、删、改)时，是没有返回集、无法执行fetchall()操作、直接commit()。
关闭连接时，数据库连接和游标会被同时关闭(因为游标是基于连接对象创建)

3.代码功能说明

创建类，初始化、校验数据库连接实例
方法-创建并返回连接对象和游标对象
方法-获取查询脚本中的表名称
方法-查询数据并返回DataFrame对象
方法-提供DML数据操作，即增、删、改

import pymssql
import pandas as pd 

class MSSQL:
    def __init__(self,host,user,pwd,db):
        try:
            self.host = host
            self.user = user
            self.pwd = pwd
            self.db = db
            if not self.db:
                raise Exception('没有设置数据库信息') 
        except Exception as e:
            print(f'{e}')
        
	
    def getConn(self):
        # 每次都新建了一个连接？ 
        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 executeQuery(self,sql):
        # 创建新的游标对象。
        # 游标的生命周期：每次执行前创建、执行中偏移至尾部、执行后关闭
        cur=self.getConn()
        cur.execute(sql)
        resList = cur.fetchall()# 注意 这里是游标访问。被访问一次后，游标指向会下移至尾部，再次fetchall（）时，返回是空
        df_res=pd.DataFrame(resList)
        self.conn.close()
        return df_res
    
    def execDML(self,sql):
        cur=self.getConn()
        cur.execute(sql)
        self.conn.commit()
        self.conn.close()
    
    # 函数：整合为一,自动识别是查询脚本还是DML
    def executeSQL(self, sql):
        cur=self.getConn()
        cur.execute(sql)
        # 如果是执行的DML语句
        if sql.find('select')==-1:
            self.conn.commit()
            self.conn.close()
            return  
        # 如果是查询表的字段名，则返回列表
        elif sql.find('sys')!=-1:
            resList=cur.fetchall()
            self.conn.close()
            return resList
        # 否则，如果是select查询数据结果，则返回dataFrame 
        else:
            # partition()函数 ,去除前后空格 。如果用正则匹配呢？ 
            tb_name=sql.partition('from')[2].partition('where')[0].strip()
            resColumns=self.getColumns(tb_name)
            resList=cur.fetchall()
            df_res=pd.DataFrame(resList, columns=resColumns)
            self.conn.close()
            return df_res
            # 返回的一个列表，列表里的元素是元组 
            # [(2, None), (3, None), (None, None), (3, None), (31, None), (31, None), (31, None), (31, None), (31, None)]

    def getColumns(self, tbName):
        sql='select name from sys.columns where object_id=object_id(\'{}\')'.format(tbName)
        # [('id',), ('col2',)] .返回的元素
        resColumns=self.executeSQL(sql)
        # 处理成 ['id', 'col2']
        strColumns=[element[0] for element in resColumns]
        return strColumns

4.注意事项

思考：在executeSQL()函数中，为什么每次都要新建连接实例?

A connection can have only one cursor with an active query at any time

简单来讲，一个连接实例，只能完成一次脚本执行。
通过如下示例，来分析只创建一次连接、同时创建多个游标时，会得到什么样的返回结果。

######## 场景1 ########
conn=pymssql.connect('localhost', 'test', 'test', 'AdventureWorksDW2017')
cur1=conn.cursor(as_dict=True)
cur1.execute('select top 3 * from DimDate where DateKey=%s', '20050101')
cur2=conn.cursor(as_dict=True)
cur2.execute('select top 3 * from DimDate where DateKey=%s', '20050102')
# 同一个连接、不同游标连续提交查询 后，以最后一个游标结果为准，后者会覆盖前者
res_test=cur1.fetchall()
res_test2=cur2.fetchall()
print('场景1：同一个连接,连续执行，最后获取结果集。游标1：\n',res_test)
print('场景1：同一个连接,连续执行，最后获取结果集。游标2：\n',res_test2)
conn.close() 
'''
场景1：同一个连接,连续执行，最后获取结果集。游标1：
 [{'DateKey': 20050102, 'FullDateAlternateKey': '2005-01-02', 'DayNumberOfWeek': 1, 'EnglishDayNameOfWeek': 'Sunday', 'SpanishDayNameOfWeek': 'Domingo', 'FrenchDayNameOfWeek': 'Dimanche', 'DayNumberOfMonth': 2, 'DayNumberOfYear': 2, 'WeekNumberOfYear': 2, 'EnglishMonthName': 'January', 'SpanishMonthName': 'Enero', 'FrenchMonthName': 'Janvier', 'MonthNumberOfYear': 1, 'CalendarQuarter': 1, 'CalendarYear': 2005, 'CalendarSemester': 1, 'FiscalQuarter': 3, 'FiscalYear': 2005, 'FiscalSemester': 2}]
场景1：同一个连接,连续执行，最后获取结果集。游标2：
 []
'''

返回结果分析

会发现结果令人费解、surprising。cur1返回的居然是cur2的查询结果(20050102)，而不是(20050101).

导致原因

This happens because the underlying TDS protocol does not have
client side cursors. The protocol requires that the client flush the
results from the first query before it can begin another
query

解决办法-walkarounds

1、按照第3部分的代码，每次访问都新创建连接对象。
2、同一个连接，每个游标的返回结果都立即留存。参考下边示例。

######## 解决办法 ########

conn=pymssql.connect('localhost', 'test', 'test', 'AdventureWorksDW2017')
cur1=conn.cursor(as_dict=True)
cur1.execute('select top 3 * from DimDate where DateKey=%s', '20050101')
# 在这里，先存储第一个游标的结果
res_test=cur1.fetchall()
cur2=conn.cursor(as_dict=True)
cur2.execute('select top 3 * from DimDate where DateKey=%s', '20050102')
# 再获取第二个游标的结果 
res_test2=cur2.fetchall()
print('场景2：同一个连接,逐次获得游标的结果集。游标1:\n',res_test)
print('场景2：同一个连接,逐次获得游标的结果集。游标2:\n',res_test2)
conn.close() 
'''
场景2：同一个连接,逐次获得游标的结果集。游标1:
 [{'DateKey': 20050101, 'FullDateAlternateKey': '2005-01-01', 'DayNumberOfWeek': 7, 'EnglishDayNameOfWeek': 'Saturday', 'SpanishDayNameOfWeek': 'Sábado', 'FrenchDayNameOfWeek': 'Samedi', 'DayNumberOfMonth': 1, 'DayNumberOfYear': 1, 'WeekNumberOfYear': 1, 'EnglishMonthName': 'January', 'SpanishMonthName': 'Enero', 'FrenchMonthName': 'Janvier', 'MonthNumberOfYear': 1, 'CalendarQuarter': 1, 'CalendarYear': 2005, 'CalendarSemester': 1, 'FiscalQuarter': 3, 'FiscalYear': 2005, 'FiscalSemester': 2}]
场景2：同一个连接,逐次获得游标的结果集。游标2:
 [{'DateKey': 20050102, 'FullDateAlternateKey': '2005-01-02', 'DayNumberOfWeek': 1, 'EnglishDayNameOfWeek': 'Sunday', 'SpanishDayNameOfWeek': 'Domingo', 'FrenchDayNameOfWeek': 'Dimanche', 'DayNumberOfMonth': 2, 'DayNumberOfYear': 2, 'WeekNumberOfYear': 2, 'EnglishMonthName': 'January', 'SpanishMonthName': 'Enero', 'FrenchMonthName': 'Janvier', 'MonthNumberOfYear': 1, 'CalendarQuarter': 1, 'CalendarYear': 2005, 'CalendarSemester': 1, 'FiscalQuarter': 3, 'FiscalYear': 2005, 'FiscalSemester': 2}]
'''

5.其它

将游标的返回集格式设为列表或字典

游标结果默认返回的数据格式为：值为元组的列表对象。每个元组内部表示每行各列的数据，所有元素表示所有行。例如：[(2, ‘a’), (3, ‘b’)]。如果要修改为以字典格式返回，可在定义游标对象时，显示指定属性as_dict。

cur=conn.cursor(as_dict=True)

通过配置参数设定过滤条件
在execute()中，可以设定参数，来获取指定条件的数据。

res_test=cur.execute('select top 3 * from DimDate where DateKey=%s', '20050101')

partition函数来获取SQL脚本中的数据表名
在这里的样例中，SQL脚本比较简单，可以利用partition来切分到表名。但是当应对比较复杂的多重子查询时，会得到错误的表名，最终导致表头与数据集不匹配，返回值异常。