python链接数据库增删改查，yaml

#!/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

改进：

# _*_coding:utf-8_*_
import os

import pymysql
import random
import yaml


# 读取yaml数据库配置文件
def read_yaml(key_type, yaml_name="sql_yaml.yaml"):
    # 获得当前文件的路径
    cu_path = os.path.dirname(os.path.realpath(__file__))
    # 获取yaml文件路径
    yaml_path = os.path.join(cu_path, yaml_name)
    # open方法打开直接读出来
    file = open(yaml_path, 'r', encoding='utf-8')
    str_data = file.read()  # 读出来是字符串
    data_dict = yaml.load(str_data)  # 用load方法转字典
    return data_dict[key_type]


# 执行sql命令
def exec_sql(sql_type, mysql):
    # 打开数据库连接
    db = pymysql.connect(
        host=read_yaml('host'),
        user=read_yaml("user"),
        password=read_yaml("password"),
        database=read_yaml("database")
    )
    """
    执行sql语句
    :param sql_type:
    :param sql:
    :return:
    """
    # 使用cursor()方法获取操作游标
    global result
    cursor = db.cursor()
    try:
        if sql_type == 'select_one':
            cursor.execute(mysql)
            result = cursor.fetchone()
        elif sql_type == 'select_list':
            cursor.execute(mysql)
            result = cursor.fetchall()
            # for row in result:
            #     id = row[0]
            #     name = row[1]
            #     # password = row[2]
            #     print(id, name)
        elif sql_type == 'update' or sql_type == 'del' or sql_type == 'insert':
            result = cursor.execute(mysql)
        db.commit()
        cursor.close()
        db.close()

        return result
    except Exception as e:
        print("执行sql语句报错：\n{0}".format(e))


if __name__ == '__main__':
    sql = "select name, age from user;"
    res = exec_sql('select_list', sql)
    print(res)
    # r = read_yaml('host')
    # print(r)

yaml文件配置：
host:
“”
user:
“”
password:
“”
database:
“” # 被连接的库的名字