python链接数据库增删改查

#!/usr/bin/python3

import pymysql
import random


# 数据库增加表
def add_table(mysql):
    # 打开数据库连接
    db = pymysql.connect(host="localhost", user="root", password="", database="testdb")
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    # 定义要执行的SQL语句

    # 执行SQL语句
    cursor.execute(mysql)
    # 关闭光标对象
    cursor.close()
    # 关闭数据库连接
    db.close()

# sql = """CREATE TABLE tables1 (
# id INT auto_increment PRIMARY KEY ,
# name CHAR(10) NOT NULL UNIQUE,
# age TINYINT NOT NULL
# )ENGINE=innodb DEFAULT CHARSET=utf8
# """
# add_table(sql)


# 向数据库表增加数据
def add_field(mysql, insert_data):
    # 连接database
    db = pymysql.connect(host="localhost", user="root", password="", database="testdb")
    # 得到一个可以执行SQL语句的光标对象
    cursor = db.cursor()
    try:
        # 批量执行多条插入SQL语句
        cursor.executemany(mysql, insert_data)
        # 提交事务
        db.commit()
    except Exception as e:
        # 有异常，回滚事务
        db.rollback()
    cursor.close()
    db.close()

#
# sql = "INSERT INTO USER(name, age) VALUES (%s, %s);"
# data = [("Alex", 36), ("Egon", 20), ("Yuan", 21), ('詹姆斯', '35')]
# add_field(sql, data)

# 删除
def delete_data(mysql, value):
    # 连接database
    db = pymysql.connect(host="localhost", user="root", password="", database="testdb")
    # 得到一个可以执行SQL语句的光标对象
    cursor = db.cursor()
    try:
        cursor.execute(mysql, value)
        # 提交事务
        db.commit()
    except Exception as e:
        # 有异常，回滚事务
        db.rollback()
    cursor.close()
    db.close()


# sql = "DELETE FROM USER1 WHERE name=%s;"
# delete_data(sql, 'sssss')

# 修改
def update_data(mysql, value):
    # 导入pymysql模块
    import pymysql
    # 连接database
    db = pymysql.connect(host="localhost", user="root", password="", database="testdb")
    # 得到一个可以执行SQL语句的光标对象
    cursor = db.cursor()

    try:
        # 执行SQL语句
        cursor.execute(mysql, value)
        # 提交事务
        db.commit()
    except Exception as e:
        # 有异常，回滚事务
        db.rollback()
    cursor.close()
    db.close()


# # 修改数据的SQL语句
# sql = "UPDATE USER SET age=%s WHERE id=%s;"
#
# update_data(sql, [20, 1])
# 查找
def select_data(mysql):
    # 打开数据库连接
    db = pymysql.connect(host="localhost", user="root", password="", database="testdb")
    # 使用cursor()方法获取操作游标
    cur = db.cursor()
    # 1.查询操作
    # 编写sql 查询语句  user 对应我的表名
    # sql = "select * from user"
    try:
        cur.execute(mysql)  # 执行sql语句
        if 'where' not in mysql:
            results = cur.fetchall()  # 获取查询的所有记录
            print("id", "name")
            # 遍历结果
            for row in results:
                id = row[0]
                name = row[1]
                print(id, name)
        else:
            cur = cur.fetchone()
            print(cur)
    except Exception as e:
        raise e
    finally:
        db.close()  # 关闭连接


sql = "select name, age from user where name='詹姆斯';"
select_data(sql)
           

参考https://www.cnblogs.com/chongdongxiaoyu/p/8951433.html