Use of pymysql Module for Connecting python to mysql Database

Installing pymysql

pip install pymysql

20 Using pymysql

21 Using Data Query Statements

Query 1 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 1 Row data 
result = c.fetchone()
print(result)
#  Close the cursor 
c.close()
#  Close the database connection 
conn.close()
"""
(1, ' Zhang 3', 18, b'\x01')
"""

Query multiple pieces of 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, ' Zhang 3', 18, b'\x01')
(2, ' Li 4', 19, b'\x00')
(3, ' Wang 5', 20, b'\x01')
"""

Change the default setting of the cursor, and the return value is 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': ' Zhang 3', 'age': 18, 'sex': b'\x01'}
{'id': 2, 'name': ' Li 4', 'age': 19, 'sex': b'\x00'}
{'id': 3, 'name': ' Wang 5', 'age': 20, 'sex': b'\x01'}
"""

When 1 piece of data is returned, it is also 1 sample. Returns a dictionary or time tuple to see what you need.

22 Using data manipulation statements

The operations of adding, deleting and updating statements are actually 1. 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 a cursor 
c = conn.cursor()
#  Execute sql Statement 
c.execute("insert into student(name,age,sex) values (%s,%s,%s)",(" Small 2",28,1))
#  Commit transaction 
conn.commit()
#  Close the cursor 
c.close()
#  Close the database connection 
conn.close()

Unlike the query statement, the transaction must be committed using commit (), otherwise the operation is invalid.

30 Writing Database Connection Classes

Ordinary version

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 is open ")
    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, execute, omitting the opening and closing of 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': ' Zhang 3', 'age': 18, 'sex': b'\x01'}
{'id': 2, 'name': ' Li 4', 'age': 19, 'sex': b'\x00'}
{'id': 3, 'name': ' Wang 5', 'age': 20, 'sex': b'\x01'}
{'id': 5, 'name': ' Small 2', '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 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 cursor 
    return self.cur

  def __exit__(self, exc_type, exc_val, exc_tb):
    #  Submit 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': ' Zhang 3', 'age': 18, 'sex': b'\x01'}
"""

Summarize

The above is the site to introduce the use of python connection mysql database pymysql module, I hope to help you, if you have any questions welcome to leave me a message, this site will reply to you in time!