用pymysql实现对数据库的增删改查

import pymysql

connection = pymysql.connect(
  host='localhost',
  port=3306,
  database='your_database',
  user='your_user',
  password='your_password',
  charset='utf8',
)
# 获取游标
cursor = connection.cursor()


# 新建一个表
def create_table():
  sql1 = """
  
  CREATE TABLE `user` (
  
  `id` int(11) NOT NULL AUTO_INCREMENT,
  
  `name` varchar(20) NOT NULL,
  
  `age` int(11) NOT NULL,
  
  `gender` varchar(10) NOT NULL,
  
  PRIMARY KEY (`id`)
  
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  """
  cursor.execute(sql1)

# 插入数据
def insert_data():
  sql2 = """
  INSERT INTO `user`(`name`, `age`, `gender`) VALUES (%s, %s, %s);
  """
  values = ('张三', 18, '男')
  # 执行SQL插入语句
  cursor.execute(sql2, values)
  # 提交事务，确保插入的数据被永久保存到数据库中
  connection.commit()

# 查询数据
def select_data():
  sql3 = """
  SELECT * FROM user WHERE name = %s;
  """
  values = ('张三',)
  cursor.execute(sql3,values)
  # 获取所有查询结果
  results = cursor.fetchall()
  # 打印所有
  # print(results)
  # 打印查询结果
  for row in results:
    print(row)

# 更新数据
def update_data():
  sql4 = """
  UPDATE user SET age = %s WHERE name = %s;
  """
  values = (20, '张三')
  cursor.execute(sql4, values)
  connection.commit()

# 删除数据
def delete_data():
  sql5 = """
  DELETE FROM user WHERE name = %s;
  """
  values = ('张三',)
  cursor.execute(sql5, values)
  connection.commit()

delete_data()