【Python】基于pymysql的数据库操作类

一 简介
     Python和MySQL交互的模块有 MySQLdb 和 PyMySQL(pymysql),MySQLdb是基于C 语言编写的，而且Python3 不在支持MySQLdb 。PyMySQL是一个纯Python写的MySQL客户端，它的目标是替代MySQLdb，可以在CPython、PyPy、IronPython和Jython环境下运行,PyMySQL在MIT许可下发布。
    在开发基于Python语言的项目中，为了以后系统能兼容Python3,我们使用了PyMySQL替换了MySQLdb。下面我们来熟悉一下pymysql的使用。
 
二 安装方式
  pymsql的源码 https://github.com/PyMySQL/PyMySQL ，目前还在持续更新。

1. 安装要求:
   
2. Python -- one of the following:
   
3.      CPython >= 2.6 or >= 3.3
   
4.      PyPy >= 4.0
   
5.      IronPython 2.7
   
6. MySQL Server -- one of the following:
   
7.      MySQL >= 4.1 (tested with only 5.5~)
   
8.      MariaDB >= 5.1
9. 安装
10.   pip install PyMySQL

  相关文档 , 强烈建议大家仔细阅读一遍。其用法和MySQLdb相差无几，核心用法一致。这样使用pymysql替换mysqldb的成本极小。


三 基于pymysql的数据库交互

1. #!/usr/bin/env python
   
2. # encoding: utf-8
   
3. """
   
4. author: yangyi@youzan
   
5. time: 2015/6/8 上午11:34
   
6. func: 基于pymysql的数据库交互类，支持事务提交和回滚，返回结果记录行数，和insert的最新id
   
7. """
   
8. import pymysql
   
9. from warnings import filterwarnings
   
10. filterwarnings('ignore', category=pymysql.Warning)
    
11. CONNECT_TIMEOUT = 100
    
12. IP = 'localhost'
    
13. PORT = 3306
    
14. USER = 'root'
    
15. PASSSWORD = ''
    
16. 
    
17. class QueryException(Exception):
    
18.     """
    
19.     """
    
20. 
    
21. 
    
22. class ConnectionException(Exception):
    
23.     """
    
24.     """
25. 
    
26. class MySQL_Utils():
    
27.     def __init__(
    
28.             self, ip=IP, port=PORT, user=USER, password=PASSSWORD,
    
29.             connect_timeout=CONNECT_TIMEOUT, remote=False, socket='', dbname='test'):
    
30.         self.__conn = None
    
31.         self.__cursor = None
    
32.         self.lastrowid = None
    
33.         self.connect_timeout = connect_timeout
    
34.         self.ip = ip
    
35.         self.port = port
    
36.         self.user = user
    
37.         self.password = password
    
38.         self.mysocket = socket
    
39.         self.remote = remote
    
40.         self.db = dbname
    
41.         self.rows_affected = 0
    
42. 
    
43. 
    
44.     def __init_conn(self):
    
45.         try:
46.             conn = pymysql.connect(
    
47.                     host=self.ip,
    
48.                     port=int(self.port),
    
49.                     user=self.user,
    
50.                     db=self.db,
    
51.                     connect_timeout=self.connect_timeout,
    
52.                     charset='utf8', unix_socket=self.mysocket)
    
53.         except pymysql.Error as e:
    
54.             raise ConnectionException(e)
    
55.         self.__conn = conn
    
56. 
    
57. 
    
58.     def __init_cursor(self):
    
59.         if self.__conn:
    
60.             self.__cursor = self.__conn.cursor(pymysql.cursors.DictCursor)
    
61. 
    
62. 
    
63.     def close(self):
    
64.         if self.__conn:
    
65.             self.__conn.close()
    
66.             self.__conn = None
    
67. 
    
68.     #专门处理select 语句
69.     def exec_sql(self, sql, args=None):
    
70.         try:
    
71.             if self.__conn is None:
    
72.                 self.__init_conn()
    
73.                 self.__init_cursor()
    
74.             self.__conn.autocommit = True
    
75.             self.__cursor.execute(sql, args)
    
76.             self.rows_affected = self.__cursor.rowcount
    
77.             results = self.__cursor.fetchall()
    
78.             return results
    
79.         except pymysql.Error as e:
    
80.             raise pymysql.Error(e)
    
81.         finally:
    
82.             if self.__conn:
    
83.                 self.close()
    
84. 
    
85.     # 专门处理dml语句 delete，updete，insert 
86.     def exec_txsql(self, sql, args=None):
    
87.         try:
    
88.             if self.__conn is None:
    
89.                 self.__init_conn()
    
90.                 self.__init_cursor()
    
91.             if self.__cursor is None:
    
92.                 self.__init_cursor()
    
93. 
    
94. 
    
95.             self.rows_affected=self.__cursor.execute(sql, args)
96.             self.lastrowid = self.__cursor.lastrowid
97.             return self.rows_affected
98.         except pymysql.Error as e:
    
99.             raise pymysql.Error(e)
    
100.         finally:
     
101.             if self.__cursor:
     
102.                 self.__cursor.close()
     
103.                 self.__cursor = None
     
104. 
     
105.     # 提交
106.     def commit(self):
     
107.         try:
     
108.             if self.__conn:
     
109.                 self.__conn.commit()
     
110.         except pymysql.Error as e:
     
111.             raise pymysql.Error(e)
     
112.         finally:
     
113.             if self.__conn:
     
114.                 self.close()
     
115. 
     
116.     #回滚操作
117.     def rollback(self):
     
118.         try:
     
119.             if self.__conn:
     
120.                 self.__conn.rollback()
121.         except pymysql.Error as e:
     
122.             raise pymysql.Error(e)
     
123.         finally:
     
124.             if self.__conn:
     
125.                 self.close()
     
126.     # 适用于需要获取插入记录的主键自增id
     
127.     def get_lastrowid(self):
     
128.         return self.lastrowid

     #获取dml操作影响的行数
    def get_affectrows(self):
        return self.rows_affected
     #MySQL_Utils初始化的实例销毁之后，自动提交
     def __del__(self):
        self.commit()

四 小结
   前几天刚刚将我们的系统中的MySQLdb 替换为PyMySQL, 还未遇到问题。欢迎大家使用测试上述脚本，有问题欢迎和我讨论。 如果本文对您有帮助 ，可以赞助 一瓶饮料。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22664653/viewspace-2140569/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22664653/viewspace-2140569/