python和mysql存储过程的一些事儿

         最近因为要提升产品效率,不得不蹚了蹚MySQL存储过程的水.只能说水不算深,效果也很棒.在这里做个记录,免得忘了.

         1. Python调用存储过程的方式

         SqlAlchemy的session是可以直接调用的.因为session本身封装了个数据库回话,底层其实也是调用的依赖的数据库殷勤的方法.所以可以直接以execute的方式去调用存储过程

session.execute("call procedure_name(param1, param2, ..., param_return);")

         但是! 这种调用方式有坑.通过我的实际验证,现象是当存储过程较为复杂,消耗较大内存时,这种调用方式容易造成内存的占用居高不下~因为时间比较紧,具体原因还没有搞清楚.

         推荐直接使用数据库引擎方法去调用.

 args = (param1, param2, ..., param_return)
 res = cursor.callproc('procedure_name', args)
 # res即为args中传入的参数, 
 # 真正的返回值为传入的存储过程 OUT 参数, 会在存储过程内赋值, 以下标获取即可

        2. MySQL的XML方法

        MySQL解析xml只有俩方法,  ExtractValue(xml, xpath)和UpdateXML(xml, xpath, new_xml). 获取xml的属性主要依靠    ExtractValue方法.但是该方法只能获取xml的属性值,就是说想要获取xml的某些片段,只能依赖MySql的字符串方法,比如substring_index(),拼接字符串方法concat()等等.

        我们常见的场景比如判断xml中有无某个Key, ExtractValue也是不支持的,会直接返回空字符串让人摸不着头脑.可以用于判断Key是否存在的方法有使用正则匹配REGEXP

select @xml REGEXP 'key_name'; 

        字符串方法InStr(str, sub_str)

select instr(@xml, 'key_name');

        字符串方法locate(str, sub+str)

select locate(@xml, 'key_name');

         以上三种方法都是可以的,在1M左右大小的xml时,REGEXP的匹配时间是60ms左右, InStr的时间在10ms以上,而locate的则稳定在个位毫秒数.所以推荐使用locate方法.

         UpdateXML是用于替换xml片段的,比如......某些没用的片段,可以直接替换成空字符串~   

         此外, 有几个坑要避免,

         <1>.  当xml非常大的时候, 解析速度会变慢.解决办法是提前拆好~

         <2>.  当xml非常大的时候,不要尝试去用replace全局替换某个字符或者标签.比如xml的namespace啥的,慢到令人窒息!解决方法,用ET提前替换好......

         附上MySQL xml function 文档 https://dev.mysql.com/doc/refman/5.7/en/xml-functions.html

         附上MySQL string function 文档 https://dev.mysql.com/doc/refman/5.7/en/string-functions.html   

         3. MySQL的JSON方法

         MySQL对Json的支持比XML好一万倍!

         附上MySQL json function 文档 https://dev.mysql.com/doc/refman/5.7/en/json-functions.html

         通过这些方法,可以在存储过程里使用列表和字典~对绝大多数的应用程序员来说是一件很开心的事儿了.

         

         最后!为啥这玩意这么麻烦还要去搞.因为使用存储过程可以极大的减少数据库交互次数,而且SQL效率本身强于一切ORM,而存储过程是编译过得SQL.你品,你细品!总结,合格的程序员还是需要会写一首SQL的.