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

 

# -*- 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()

 

为了搞清楚上面这些问题，我修改了上面脚本，执行后，我去查询数据库, 发现即使上面的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()) 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' )

               );

 

 

 

 

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

 

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

 

 

audit logout:records all new disconnect events since the trace started, such as when a client issues a disconnect command