pymssql的Connection相关特性浅析-python黑洞网

本站消息

站长简介/公众号

出租广告位,需要合作请联系站长

齐天大圣

1103

文章

888994

访问

+关注

分类

暂无分类

日期归档

2023-05(8)

2023-06(1)

pymssql的Connection相关特性浅析

发布于2020-01-01 13:01 阅读(1387) 评论(0) 点赞(13) 收藏(1)

关于Python的pymssql模块,之前研究时总结了“pymssql默认关闭自动模式开启事务行为浅析”这篇博客，但是在测试过程中又发现了几个问题，下面对这些问题做一些浅析，如有不足或不正确的地方，敬请指出。

1： pymssql的commit函数可以提交两次或多次

Connection.commit()：

Commit current transaction. You must call this method to persist your data if you leave autocommit at its default value, which is False

我们知道pymssql模块里面有commit函数表示提交事务，由于某个特殊原因，测试过程中发现执行多次commit都OK，不会报错，如下代码所示。

 # -*- coding: utf-8 -*-'''---------------------------------------------------------------------------------------------  Script Name     :   TranTest.py-------------------------------------------------------------------------------------------'''import pymssqlimport loggingimport os.pathimport osimport base64from cryptography.fernet import Fernet     key=bytes(os.environ.get('key'),encoding="utf8")cipher_suite = Fernet(key)with open('/home/konglb/python/conf/ms_db_conf.bin', 'rb') as file_object:    for line in file_object:        encryptedpwd = linedecrypt_pwd = (cipher_suite.decrypt(encryptedpwd))password_decrypted = bytes(decrypt_pwd).decode("utf-8") #convert to stringenv_db_user=os.environ.get('db_user')db_user=base64.b64decode(bytes(env_db_user, encoding="utf8"))  dest_db_conn = pymssql.connect(host=os.environ.get('db_host'),                               user=bytes.decode(db_user),                               password=password_decrypted,                               database='master',                               charset="utf8"); sub_cursor = dest_db_conn.cursor(as_dict=True)  sub_cursor.execute('SELECT COUNT(*) AS RecordNum FROM msdb.dbo.sysmail_account')result_rows =sub_cursor.fetchone() print(result_rows["RecordNum"])dest_db_conn.commit()dest_db_conn.commit()dest_db_conn.close()

其实我们用SQL Profile跟踪一下就会知道，多执行一次commit，相当于在SQL Server数据库多执行了一次下面SQL，显然不会出现什么问题，但是也没有什么用处，所以这个应该只提交一次就OK了。这个问题，其实一开始对于我来说还有点震惊。了解过原理后，其实发现也就那么一回事。如果你是驱动的开发者而言，也不可能让第二次commit报错，如果这样的话，那么程序的健壮性就有问题了。

BEGIN TRAN

COMMIT TRAN;

2: pymssql的close函数可以关闭多次？

Connection.close() ：Close the connection

关于pymssql中的close函数表示关闭数据库连接，第一次执行就已经关闭了数据库连接，执行第二次close没有报任何错误，但是如果在连接关闭后，再执行查询之类的操作，就会报“pymssql.InterfaceError: Connection is closed”这类错误，如下所示，简单修改上面代码，就可以测试、验证：

dest_db_conn.commit()
dest_db_conn.close()
sub_cursor.execute('SELECT COUNT(*) AS RecordNum FROM msdb.dbo.sysmail_account')
dest_db_conn.close()

#python TranTest.py  Traceback (most recent call last):  File "TranTest.py", line 45, in <module>    sub_cursor.execute('SELECT COUNT(*) AS RecordNum FROM msdb.dbo.sysmail_account')  File "src/pymssql.pyx", line 448, in pymssql.Cursor.execute  File "src/pymssql.pyx", line 238, in pymssql.Connection._conn.__get__pymssql.InterfaceError: Connection is closed.

个人猜测驱动程序已经关闭数据库链接了，第二次执行close函数时，可能驱动底层检测到数据库连接已经关闭，直接退出了，不做任何操作。但是如果数据库连接关闭后，再去执行相关SQL，此时就会报“Connection is closed”这类错误了。

3：如果忘记提交或回滚事务，那么脚本执行完成后会回滚吗？什么时候回滚呢？另外，它会阻塞其它会话吗？阻塞的时间有多长？

# -*- coding: utf-8 -*-'''---------------------------------------------------------------------------------------------  Script Name     :   TranTest.py-------------------------------------------------------------------------------------------'''import pymssqlimport loggingimport os.pathimport osimport base64from cryptography.fernet import Fernet     key=bytes(os.environ.get('key'),encoding="utf8")cipher_suite = Fernet(key)with open('/home/konglb/python/conf/ms_db_conf.bin', 'rb') as file_object:    for line in file_object:        encryptedpwd = linedecrypt_pwd = (cipher_suite.decrypt(encryptedpwd))password_decrypted = bytes(decrypt_pwd).decode("utf-8") #convert to stringenv_db_user=os.environ.get('db_user')db_user=base64.b64decode(bytes(env_db_user, encoding="utf8"))  dest_db_conn = pymssql.connect(host=os.environ.get('db_host'),                               user=bytes.decode(db_user),                               password=password_decrypted,                               database='master',                               charset="utf8"); sub_cursor = dest_db_conn.cursor(as_dict=True)   sub_cursor.execute("UPDATE TEST SET NAME='KKK' WHERE ID=100")  #dest_db_conn.commit()#dest_db_conn.close()dest_db_conn.close()

为了搞清楚上面这些问题，我修改了上面脚本，执行后，我去查询数据库, 发现即使上面的Python脚本没有提交事务，但是不会阻塞其它会话（其实是因为事务已经回滚了），对应的会话已经不存在了。猜测是因为Python脚本执行完成后，关闭了TCP层的连接而触发底层驱动关闭数据库连接（在关闭数据库连接之前，回滚了没有提交的事务）。

那么怎么验证呢？很简单，我们使用休眠函数sleep，在关闭数据库联机（dest_db_conn.close()) 前让其休眠100秒，

dest_db_conn = pymssql.connect(host=os.environ.get('db_host'),
                               user=bytes.decode(db_user),
                               password=password_decrypted,
                               database='master',
                               charset="utf8");

sub_cursor = dest_db_conn.cursor(as_dict=True)

sub_cursor.execute("UPDATE TEST SET NAME='KKK' WHERE ID=100")

time.sleep(100)

dest_db_conn.close()

然后在这期间，我们就可以查看会话信息、查看未提交的事务，构造阻塞会话等等。如下所示：

SELECT * FROM sys.sysprocesses WHERE loginame='xxx' DECLARE @tab TABLE    (      NAME VARCHAR(100) ,      value VARCHAR(200)    );INSERT  INTO @tab        EXEC ( 'DBCC OPENTRAN WITH TABLERESULTS'            );SELECT  NAME ,        CAST(value AS DATETIME) startDate ,        GETDATE() currentDate ,        DATEDIFF(s, CAST(value AS DATETIME), GETDATE()) diffsecondFROM    @tabWHERE   NAME IN ( 'OLDACT_STARTTIME' );SELECT  spid ,        blocked ,        DB_NAME(sp.dbid) AS DBName ,        program_name ,        waitresource ,        lastwaittype ,        sp.loginame ,        sp.hostname ,        A.[text] AS [TextData] ,        SUBSTRING(A.text, sp.stmt_start / 2,                  ( CASE WHEN sp.stmt_end = -1 THEN DATALENGTH(A.text)                         ELSE sp.stmt_end                    END - sp.stmt_start ) / 2) AS [current_cmd]FROM    sys.sysprocesses AS sp        OUTER APPLY sys.dm_exec_sql_text(sp.sql_handle) AS AWHERE   spid = ( SELECT CASE WHEN ISNUMERIC(value) = 0 THEN -1                             ELSE value                        END                 FROM   @tab                 WHERE  NAME IN ( 'OLDACT_SPID' )               );

那么为什么说是Python执行完成后，关闭TCP连接触发了底层驱动做这个事情呢？你测试时，发现执行完脚本后，都会有一个Audit Logout，如下截图所示，另外，你也可以将上面脚本的休眠函数和关闭数据库连接注释掉，你会发现，即使不关闭数据库连接，Python脚本执行完成后，事务也回滚了，数据库连接也关闭了。其实如果你进行了上面测试，第三个问题已经基本不用回答了。显然已经不言而喻了

#time.sleep(100)
#dest_db_conn.close()