Use Python to connect to MySQL database using the pymysql module

Install pymysql

pip install pymysql

2|0Using pymysql

2|1Use data query statements

Query a piece of data fetchone()

from pymysql import *

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

# Create a cursor c = conn.cursor()
# Execute SQL statement c.execute("select * from student")
# Query a row of data result = c.fetchone()
print(result)
# Close the cursor c.close()
# Close the database connection conn.close()
"""
(1, '张三', 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 a cursor c = conn.cursor()
# Execute SQL statement c.execute("select * from student")
# Query multiple rows of data result = c.fetchall()
for item in result:
  print(item)
# Close the cursor c.close()
# Close the database connection conn.close()
"""
(1, '张三', 18, b'\x01')
(2, 'Li Si', 19, b'\x00')
(3, '王五', 20, b'\x01')
"""

Change the default settings of the cursor, the return value is a dictionary

from pymysql import *

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

# Create a cursor and set the operation to dictionary type c = conn.cursor(cursors.DictCursor)
# Execute SQL statement c.execute("select * from student")
# Query multiple rows of data result = c.fetchall()
for item in result:
  print(item)
# Close the cursor c.close()
# Close the database connection conn.close()
"""
{'id': 1, 'name': '张三', 'age': 18, 'sex': b'\x01'}
{'id': 2, 'name': 'Li Si', 'age': 19, 'sex': b'\x00'}
{'id': 3, 'name': '王五', 'age': 20, 'sex': b'\x01'}
"""

The same is true when returning a piece of data. Return a dictionary or a tuple depending on your needs.

2|2Using data manipulation statements

The operations for executing add, delete, and update statements are actually the same. Just write 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 a cursor c = conn.cursor()
# Execute SQL statement c.execute("insert into student(name,age,sex) values (%s,%s,%s)",("小二",28,1))
# Commit the transaction conn.commit()
# Close the cursor c.close()
# Close the database connection conn.close()

Unlike query statements, transactions must be committed using commit(), otherwise the operation will be invalid.

3|0Write database connection class

Regular 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 need to open and close database connections and cursors.
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': '张三', 'age': 18, 'sex': b'\x01'}
{'id': 2, 'name': 'Li Si', 'age': 19, 'sex': b'\x00'}
{'id': 3, 'name': '王五', 'age': 20, 'sex': b'\x01'}
{'id': 5, 'name': '小等', '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 Version of 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 a connection self.conn = connect(
      host=host,
      port=port,
      db=db,
      user=user,
      passwd=passwd,
      charset=charset)
    # Create a cursor and set the operation to dictionary type self.cur = self.conn.cursor(cursor=cursors.DictCursor)

  def __enter__(self):
    # Return the cursor return self.cur

  def __exit__(self, exc_type, exc_val, exc_tb):
    # Commit the database and execute self.conn.commit()
    # Close the cursor self.cur.close()
    # Close the 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': '张三', 'age': 18, 'sex': b'\x01'}
"""

Summarize

The above is the introduction of using Python to connect to MySQL database using the pymysql module. I hope it will be helpful to everyone. If you have any questions, please leave me a message and I will reply to you in time!

You may also be interested in:

Python module explains the connection and use of Redis database
Python uses the sqlite3 module built-in database
Python parses the method of operating the database with the pymysql module
Python uses sqlalchemy module to connect to database operation example
Detailed explanation of how to connect to SQL Server database based on Pymssql module in Python
Tutorial on writing database modules in Python
Python MySQLdb module connects to operate mysql database instance
Introduction to Python bsddb module operating Berkeley DB database
Various database operation modules and connection examples commonly used in Python
Share a Python module that is very useful when encountering databases

<<: How to build a K8S cluster and install docker under Hyper-V

>>: Nested display implementation of vue router-view