北在南方

每天进步一点点

  • 博客访问: 6640800
  • 博文数量: 1020
  • 用 户 组: 普通用户
  • 注册时间: 2009-10-07 13:14
个人简介

MySQL DBA NoSQL DEVOPS

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(1020)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题

分类: MySQL

一 简介
     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. class QueryException(Exception):
  17.     """
  18.     """


  19. class ConnectionException(Exception):
  20.     """
  21.     """

  22. class MySQL_Utils():
  23.     def __init__(
  24.             self, ip=IP, port=PORT, user=USER, password=PASSSWORD,
  25.             connect_timeout=CONNECT_TIMEOUT, remote=False, socket='', dbname='test'):
  26.         self.__conn = None
  27.         self.__cursor = None
  28.         self.lastrowid = None
  29.         self.connect_timeout = connect_timeout
  30.         self.ip = ip
  31.         self.port = port
  32.         self.user = user
  33.         self.password = password
  34.         self.mysocket = socket
  35.         self.remote = remote
  36.         self.db = dbname
  37.         self.rows_affected = 0


  38.     def __init_conn(self):
  39.         try:
  40.             conn = pymysql.connect(
  41.                     host=self.ip,
  42.                     port=int(self.port),
  43.                     user=self.user,
  44.                     db=self.db,
  45.                     connect_timeout=self.connect_timeout,
  46.                     charset='utf8', unix_socket=self.mysocket)
  47.         except pymysql.Error as e:
  48.             raise ConnectionException(e)
  49.         self.__conn = conn


  50.     def __init_cursor(self):
  51.         if self.__conn:
  52.             self.__cursor = self.__conn.cursor(pymysql.cursors.DictCursor)


  53.     def close(self):
  54.         if self.__conn:
  55.             self.__conn.close()
  56.             self.__conn = None

  57.     #专门处理select 语句
  58.     def exec_sql(self, sql, args=None):
  59.         try:
  60.             if self.__conn is None:
  61.                 self.__init_conn()
  62.                 self.__init_cursor()
  63.             self.__conn.autocommit = True
  64.             self.__cursor.execute(sql, args)
  65.             self.rows_affected = self.__cursor.rowcount
  66.             results = self.__cursor.fetchall()
  67.             return results
  68.         except pymysql.Error as e:
  69.             raise pymysql.Error(e)
  70.         finally:
  71.             if self.__conn:
  72.                 self.close()

  73.     # 专门处理dml语句 delete,updete,insert 
  74.     def exec_txsql(self, sql, args=None):
  75.         try:
  76.             if self.__conn is None:
  77.                 self.__init_conn()
  78.                 self.__init_cursor()
  79.             if self.__cursor is None:
  80.                 self.__init_cursor()


  81.             self.rows_affected=self.__cursor.execute(sql, args)
  82.             self.lastrowid = self.__cursor.lastrowid
  83.             return self.rows_affected
  84.         except pymysql.Error as e:
  85.             raise pymysql.Error(e)
  86.         finally:
  87.             if self.__cursor:
  88.                 self.__cursor.close()
  89.                 self.__cursor = None

  90.     # 提交
  91.     def commit(self):
  92.         try:
  93.             if self.__conn:
  94.                 self.__conn.commit()
  95.         except pymysql.Error as e:
  96.             raise pymysql.Error(e)
  97.         finally:
  98.             if self.__conn:
  99.                 self.close()

  100.     #回滚操作
  101.     def rollback(self):
  102.         try:
  103.             if self.__conn:
  104.                 self.__conn.rollback()
  105.         except pymysql.Error as e:
  106.             raise pymysql.Error(e)
  107.         finally:
  108.             if self.__conn:
  109.                 self.close()
  110.     # 适用于需要获取插入记录的主键自增id
  111.     def get_lastrowid(self):
  112.         return self.lastrowid
     #获取dml操作影响的行数
    def get_affectrows(self):
        return self.rows_affected
     #MySQL_Utils初始化的实例销毁之后,自动提交
     def __del__(self):
        self.commit()
四 小结
   前几天刚刚将我们的系统中的MySQLdb 替换为PyMySQL, 还未遇到问题。欢迎大家使用测试上述脚本,有问题欢迎和我讨论。如果本文对您有帮助 ,可以赞助 一瓶饮料。




阅读(501) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册