Install pymysql

pip install pymysql

Using pymysql

Use data query statements

• Query a data fetchone()

from pymysql import *

conn = connect(
    host='127.0.0.1',
    port=3306, user='root',
    password='123456',
    database='itcast',
    charset='utf8')

# Create cursors
c = conn.cursor()
# Execute sql statements
c.execute("select * from student")
# Query a row of data
result = c.fetchone()
print(result)
# Close cursor
c.close()
# Close database connection
conn.close()
"""
(1, 'Zhang San', 18, b'\x01')
"""

• Query multiple data fetchall()

from pymysql import *

conn = connect(
    host='127.0.0.1',
    port=3306, user='root',
    password='123456',
    database='itcast',
    charset='utf8')

# Create cursors
c = conn.cursor()
# Execute sql statements
c.execute("select * from student")
# Query for multiline data
result = c.fetchall()
for item in result:
    print(item)
# Close cursor
c.close()
# Close database connection
conn.close()
"""
(1, 'Zhang San', 18, b'\x01')
(2, 'Li Si', 19, b'\x00')
(3, 'Wang Wu', 20, b'\x01')
"""

• Change the default settings for cursors and return a dictionary value

from pymysql import *

conn = connect(
    host='127.0.0.1',
    port=3306, user='root',
    password='123456',
    database='itcast',
    charset='utf8')

# Create a cursor with the operation set to the dictionary type
c = conn.cursor(cursors.DictCursor)
# Execute sql statements
c.execute("select * from student")
# Query for multiline data
result = c.fetchall()
for item in result:
    print(item)
# Close cursor
c.close()
# Close database connection
conn.close()
"""
{'id': 1, 'name': 'Zhang San', 'age': 18, 'sex': b'\x01'}
{'id': 2, 'name': 'Li Si', 'age': 19, 'sex': b'\x00'}
{'id': 3, 'name': 'Wang Wu', 'age': 20, 'sex': b'\x01'}
"""

The same is true when returning a data. Return to the dictionary or tuple for personal needs.

Use Data Operating Statements

The operation of adding, deleting and updating statements is actually the same. Write only one as a demonstration.

from pymysql import *

conn = connect(
    host='127.0.0.1',
    port=3306, user='root',
    password='123456',
    database='itcast',
    charset='utf8')

# Create cursors
c = conn.cursor()
# Execute sql statements
c.execute("insert into student(name,age,sex) values (%s,%s,%s)",("Waiter",28,1))
# Submission of affairs
conn.commit()
# Close cursor
c.close()
# Close database connection
conn.close()

Unlike query statements, commit() must be used to commit transactions, otherwise the operation is invalid.

Writing database connection classes

• Ordinary Edition

MysqlHelper.py

from pymysql import connect,cursors

class MysqlHelper:
    def __init__(self,
                 host="127.0.0.1",
                 user="root",
                 password="123456",
                 database="itcast",
                 charset='utf8',
                 port=3306):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.charset = charset
        self._conn = None
        self._cursor = None

    def _open(self):
        # print("Connection Opened")
        self._conn = connect(host=self.host,
                             port=self.port,
                             user=self.user,
                             password=self.password,
                             database=self.database,
                             charset=self.charset)
        self._cursor = self._conn.cursor(cursors.DictCursor)

    def _close(self):
        # print("Connection closed")
        self._cursor.close()
        self._conn.close()

    def one(self, sql, params=None):
        result: tuple = None
        try:
            self._open()
            self._cursor.execute(sql, params)
            result = self._cursor.fetchone()
        except Exception as e:
            print(e)
        finally:
            self._close()
        return result

    def all(self, sql, params=None):
        result: tuple = None
        try:
            self._open()
            self._cursor.execute(sql, params)
            result = self._cursor.fetchall()
        except Exception as e:
            print(e)
        finally:
            self._close()
        return result

    def exe(self, sql, params=None):
        try:
            self._open()
            self._cursor.execute(sql, params)
            self._conn.commit()
        except Exception as e:
            print(e)
        finally:
            self._close()

This class encapsulates fetchone, fetchall and execute, eliminating the opening and closing of database connection and cursor opening and closing.
The following code is a small example of calling this class:

from MysqlHelper import *

mysqlhelper = MysqlHelper()
ret = mysqlhelper.all("select * from student")
for item in ret:
    print(item)
"""
{'id': 1, 'name': 'Zhang San', 'age': 18, 'sex': b'\x01'}
{'id': 2, 'name': 'Li Si', 'age': 19, 'sex': b'\x00'}
{'id': 3, 'name': 'Wang Wu', 'age': 20, 'sex': b'\x01'}
{'id': 5, 'name': 'Waiter', 'age': 28, 'sex': b'\x01'}
{'id': 6, 'name': 'Wahaha', 'age': 28, 'sex': b'\x01'}
{'id': 7, 'name': 'Wahaha', 'age': 28, 'sex': b'\x01'}
"""

• Context Manager Edition

mysql_with.py

from pymysql import connect, cursors

class DB:
    def __init__(self,
                 host='localhost',
                 port=3306,
                 db='itcast',
                 user='root',
                 passwd='123456',
                 charset='utf8'):
        # Establish connection
        self.conn = connect(
            host=host,
            port=port,
            db=db,
            user=user,
            passwd=passwd,
            charset=charset)
        # Create a cursor with the operation set to the dictionary type
        self.cur = self.conn.cursor(cursor=cursors.DictCursor)

    def __enter__(self):
        # ref cursor
        return self.cur

    def __exit__(self, exc_type, exc_val, exc_tb):
        # Submit the database and execute it
        self.conn.commit()
        # Close cursor
        self.cur.close()
        # Close database connection
        self.conn.close()

How to use:

from mysql_with import DB

with DB() as db:
    db.execute("select * from student")
    ret = db.fetchone()
    print(ret)

"""
{'id': 1, 'name': 'Zhang San', 'age': 18, 'sex': b'\x01'}
"""