Use Python to connect to MySQL database using the pymysql module

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')
"""

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')
"""

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'}
"""

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()

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()

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()

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'}
"""