pymssql的Connection相关特性浅析
关于python的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 pymssql
import logging
import os.path
import os
import base64
from 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 = line
decrypt_pwd = (cipher_suite.decrypt(encryptedpwd))
password_decrypted = bytes(decrypt_pwd).decode("utf-8") #convert to string
env_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： 如果忘记提交或回滚事务，那么脚本执行完成后会回滚吗？ 什么时候回滚呢？ 另外，它会阻塞其它会话吗？ 阻塞的时间有多长？
为了搞清楚上面这些问题，我修改了上面脚本，执行后，我去查询数据库, 发现即使上面的python脚本没有提交事务，但是不会阻塞其它会话（其实是因为事务已经回滚了），对应的会话已经不存在了。猜测是因为python脚本执行完成后，关闭了tcp层的连接而触发底层驱动关闭数据库连接（在关闭数据库连接之前，回滚了没有提交的事务）。 那么怎么验证呢？ 很简单，我们使用休眠函数sleep，在关闭数据库联机（dest_db_conn.close()) 前让其休眠100秒， 然后在这期间，我们就可以查看会话信息、查看未提交的事务，构造阻塞会话等等。如下所示：
那么为什么说是python执行完成后，关闭tcp连接触发了底层驱动做这个事情呢？ 你测试时，发现执行完脚本后，都会有一个audit logout，如下截图所示，另外，你也可以将上面脚本的休眠函数和关闭数据库连接注释掉，你会发现，即使不关闭数据库连接，python脚本执行完成后，事务也回滚了，数据库连接也关闭了。其实如果你进行了上面测试，第三个问题已经基本不用回答了。显然已经不言而喻了 #time.sleep(100)
audit logout:records all new disconnect events since the trace started, such as when a client issues a disconnect command
# -*- coding: utf-8 -*-
'''
-------------------------------------------------------------------------------------------
-- script name : trantest.py
-------------------------------------------------------------------------------------------
'''
import pymssql
import logging
import os.path
import os
import base64
from 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 = line
decrypt_pwd = (cipher_suite.decrypt(encryptedpwd))
password_decrypted = bytes(decrypt_pwd).decode("utf-8") #convert to string
env_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()
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()) diffsecond
from @tab
where 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 a
where spid = ( select case when isnumeric(value) = 0 then -1
else value
end
from @tab
where name in ( 'oldact_spid' )
);
#dest_db_conn.close()