Python pymysql操作MySQL详细

import pymysql
# 创建连接
con = pymysql.connect(host='localhost',port=3306,user='root',password='123456',database='test',charset='utf8'
)
# 创建游标
cursor = con.cursor()
# 执行新增SQL，返回受影响行数
row1 = cursor.execute("insert into user (username, password) values ('username3','password3')")
print(row1)
# 执行更新SQL，返回受影响行数
row2 = cursor.execute("update user set password = '123456' where id > 2;")
# 执行查询SQL
res = cursor.execute("SELECT * FROM user;")
result = cursor.fetchall()
for info in result:
 print(info[0], info[1])
# 提交，不然无法保存新增或者更新的数据
con.commit()
# 关闭游标
cursor.close()
# 关闭连接
con.close()

import pymysql

class MysqlHelper:
 def __init__(self, config):
  self.host = config["host"]
  self.port = config["port"]
  self.user = config["user"]
  self.password = config["password"]
  self.db = config["db"]
  self.charset = config["charset"]
  self.con = None
  self.cursor = None
 def create_con(self):
  """
  创建连接
  """
  try:
self.con = pymysql.connect(host=self.host, port=self.port, user=self.user, password=self.password,
  database=self.db, charset='utf8')
self.cursor = self.con.cursor()
return True
  except Exception as e:
print(e)
return False
 #
 def close_con(self):
  """
  关闭链接
  """
  if self.cursor:
self.cursor.close()
  if self.con:
self.con.close()
 # sql执行
 def execute_sql(self, sql):
  """
  执行插入/更新/删除语句
  """
  try:
self.create_con()
print(sql)
self.cursor.execute(sql)
self.con.commit()
  except Exception as e:
print(e)
  finally:
self.close_con()
 def select(self, sql, *args):
  """
  执行查询语句
  """
  try:
self.create_con()
print(sql)
self.cursor.execute(sql, args)
res = self.cursor.fetchall()
return res
  except Exception as e:
print(e)
return False
  finally:
self.close_con()

config = {
 "host": 'localhost',
 "port": 3306,
 "user": 'root',
 "password": '123456',
 "db": 'test',
 "charset": 'utf8'
}
db = MysqlHelper(config)
db.execute_sql("insert into user (username, password) values ('username4','password4')")
db.select("SELECT * FROM user;")