温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

Python基于pymysql的数据库操作类的安装运行过程

发布时间:2021-10-11 18:28:47 来源:亿速云 阅读:138 作者:柒染 栏目:MySQL数据库

这期内容当中小编将会给大家带来有关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

 

三 基于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()

上述就是小编为大家分享的Python基于pymysql的数据库操作类的安装运行过程了,如果刚好有类似的疑惑,不妨参照上述分析进行理解。如果想知道更多相关知识,欢迎关注亿速云行业资讯频道。

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI