Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about the installation and operation process of Python pymysql-based database operation class. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

A brief introduction

The modules for interaction between Python and MySQL are MySQLdb and PyMySQL (pymysql). MySQLdb is written in C, and Python3 no longer supports MySQLdb. PyMySQL is a pure Python-written MySQL client that aims to replace MySQLdb and can run in CPython, PyPy, IronPython, and Jython environments, and PyMySQL is released under the MIT license.

In the development of a project based on the Python language, in order to make the system compatible with Python3 in the future, we replaced MySQLdb with PyMySQL. Let's familiarize ourselves with the use of pymysql.

Second, installation mode

The source code https://github.com/PyMySQL/PyMySQL of pymsql is still being updated continuously.

Installation requirements:

Python-one of the following:

CPython > = 2.6 or > = 3.3

PyPy > = 4.0

IronPython 2.7

MySQL Server-one of the following:

MySQL > = 4.1 (tested with only 5.5 ~)

MariaDB > = 5.1

Installation

Pip install PyMySQL

Third, database interaction based on pymysql

#! / usr/bin/env python

# encoding: utf-8

"

Author: yangyi@youzan

Time: 11:34 on 2015-6-8

Func: pymysql-based database interaction class that supports transaction commit and rollback, returns the number of rows of result records, and the latest id of insert

"

Import pymysql

From warnings import filterwarnings

Filterwarnings ('ignore', category=pymysql.Warning)

CONNECT_TIMEOUT = 100

IP = 'localhost'

PORT = 3306

USER = 'root'

PASSSWORD =''

Class QueryException (Exception):

"

"

Class ConnectionException (Exception):

"

"

Class MySQL_Utils ():

Def _ _ init__ (

Self, ip=IP, port=PORT, user=USER, password=PASSSWORD

Connect_timeout=CONNECT_TIMEOUT, remote=False, socket='', dbname='test'):

Self.__conn = None

Self.__cursor = None

Self.lastrowid = None

Self.connect_timeout = connect_timeout

Self.ip = ip

Self.port = port

Self.user = user

Self.password = password

Self.mysocket = socket

Self.remote = remote

Self.db = dbname

Self.rows_affected = 0

Def _ init_conn (self):

Try:

Conn = pymysql.connect (

Host=self.ip

Port=int (self.port)

User=self.user

Db=self.db

Connect_timeout=self.connect_timeout

Charset='utf8', unix_socket=self.mysocket)

Except pymysql.Error as e:

Raise ConnectionException (e)

Self.__conn = conn

Def _ init_cursor (self):

If self.__conn:

Self.__cursor = self.__conn.cursor (pymysql.cursors.DictCursor)

Def close (self):

If self.__conn:

Self.__conn.close ()

Self.__conn = None

# specifically dealing with select statements

Def exec_sql (self, sql, args=None):

Try:

If self.__conn is None:

Self.__init_conn ()

Self.__init_cursor ()

Self.__conn.autocommit = True

Self.__cursor.execute (sql, args)

Self.rows_affected = self.__cursor.rowcount

Results = self.__cursor.fetchall ()

Return results

Except pymysql.Error as e:

Raise pymysql.Error (e)

Finally:

If self.__conn:

Self.close ()

# specifically dealing with dml statement delete,updete,insert

Def exec_txsql (self, sql, args=None):

Try:

If self.__conn is None:

Self.__init_conn ()

Self.__init_cursor ()

If self.__cursor is None:

Self.__init_cursor ()

Self.rows_affected=self.__cursor.execute (sql, args)

Self.lastrowid = self.__cursor.lastrowid

Return self.rows_affected

Except pymysql.Error as e:

Raise pymysql.Error (e)

Finally:

If self.__cursor:

Self.__cursor.close ()

Self.__cursor = None

# submit

Def commit (self):

Try:

If self.__conn:

Self.__conn.commit ()

Except pymysql.Error as e:

Raise pymysql.Error (e)

Finally:

If self.__conn:

Self.close ()

# rollback operation

Def rollback (self):

Try:

If self.__conn:

Self.__conn.rollback ()

Except pymysql.Error as e:

Raise pymysql.Error (e)

Finally:

If self.__conn:

Self.close ()

# applicable to self-incrementing id of primary keys that need to obtain inserted records

Def get_lastrowid (self):

Return self.lastrowid

# get the number of rows affected by dml operation

Def get_affectrows (self):

Return self.rows_affected

# automatically submit an instance initialized by MySQL_Utils after it is terminated

Def _ del__ (self):

Self.commit ()

The above is the installation and operation process of the Python pymysql-based database operation class shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.