Correct posture for Python to operate MySQL

There are three main databases using Python for MySQL: Python-MySQL (more familiar name may be MySQLdb), PyMySQL and SQLAlchemy. Python-MySQL has the oldest qualifications. The core is built on the C language, the interface is refined, and the performance is the best. The disadvantage is that the environment depends a lot and the installation is complicated. the upgrade has been stopped in the past two years. only Python 2 is supported and Python 3 is not supported. PyMySQL is generated to replace Python-MySQL. it is created in pure python and is compatible with Python-MySQL. it is easy to install and supports Python3. There are three main databases using Python for MySQL: Python-MySQL (more familiar name may be MySQLdb), PyMySQL and SQLAlchemy.

Python-MySQL has the oldest qualifications. The core is built on the C language, the interface is refined, and the performance is the best. The disadvantage is that the environment depends a lot and the installation is complicated. the upgrade has been stopped in the past two years. only Python 2 is supported and Python 3 is not supported.

PyMySQL is generated to replace Python-MySQL. it is created in pure python and is compatible with Python-MySQL. it is easy to install and supports Python3.

SQLAlchemy is an ORM framework. it does not provide underlying database operations, but relies on third-party libraries such as MySQLdb and PyMySQL. Currently, SQLAlchemy is widely used in Web programming.

This article describes how to use PyMySQL correctly. the sample code is selected from the actual project.

Install

Simple method:

pip install pymysql

If the network cannot be connected, you need to install it offline, for example:

pip install pymysql-x.x.x.tar.gz

Import

import pymysql

Connection

def connect_wxremit_db():    return pymysql.connect(host='10.123.5.28',                           port=3306,                           user='root',                           password='root1234',                           database='db_name',                           charset='latin1')

Query

def query_country_name(cc2):    sql_str = ("SELECT Fcountry_name_zh"                + " FROM t_country_code"                + " WHERE Fcountry_2code='%s'" % (cc2))    logging.info(sql_str)    con = mysql_api.connect_wxremit_db()    cur = con.cursor()    cur.execute(sql_str)    rows = cur.fetchall()    cur.close()    con.close()    assert len(rows) == 1, 'Fatal error: country_code does not exists!'    return rows[0][0]

Simple insert

def insert_file_rec(self, file_name, file_md5):        con = mysql_api.connect_wxremit_db()        cur = con.cursor()        try:            sql_str = ("INSERT INTO t_forward_file (Ffile_name, Ffile_md5)",                        + " VALUES ('%s', '%s')" % (file_name, file_md5))            cur.execute(sql_str)            con.commit()        except:            con.rollback()            logging.exception('Insert operation error')            raise        finally:            cur.close()            con.close()

Batch insert

remit_ids = [('1234', 'CAD'), ('5678', 'HKD')]con = mysql_api.connect_wxremit_db()        cur = con.cursor()        try:                cur.executemany("INSERT INTO t_order (Fremit_id, Fcur_type, Fcreate_time"                                                + " VALUES (%s, %s, now())", new_items)                assert cur.rowcount == len(remit_ids), 'my error message'                con.commit()        except Exception as e:                con.rollback()                logging.exception('Insert operation error')        finally:                cur.close()                con.close()

Update

    def update_refund_trans(self, remit_id):        con = mysql_api.connect_wxremit_db()        cur = con.cursor()        try:            sql_str = ("SELECT Fremit_id"                       + " FROM t_wxrefund_trans"                       + " WHERE Fremit_id='%s'" % remit_id                       + " FOR UPDATE")            logging.info(sql_str)            cur.execute(sql_str)            assert cur.rowcount == 1, 'Fatal error: The wx-refund record be deleted!'            sql_str = ("UPDATE t_wxrefund_trans"                        + " SET Fcheck_amount_flag=1"                        + ", Fmodify_time=now()"                        + " WHERE Fremit_id='%s'" % remit_id            logging.info(sql_str)            cur.execute(sql_str)            assert cur.rowcount == 1, 'The number of affected rows not equal to 1'            con.commit()        except:            con.rollback()            logging.exception('Update operation error')            raise        finally:            cur.close()            con.close()

PyMySQL is quite mature. like Python-MySQL, it is an optional installation component in many Linux releases.

The above is a detailed description of the correct posture for Python to operate MySQL. For more information, see other related articles in the first PHP community!