【mysql】常见操作数据库模板 | python实现

在项目中，经常需要使用python操作数据库，这里写一个模板，总结一下各类常见的操作。

连接mysql

# 处理连接mysql
def sql_connect():
    # 建立数据库连接
    db = pymysql.connect(
                         host='172.22.xx.xx',		#填数据库的ip
                         port=3306,
                         user='root',
                         passwd='password',
                         db='db_name',  # 注意这里应该是 db 而不是 database
                         charset='utf8')
    # 创建一个游标对象 cursor
    cursor = db.cursor()
    return db,cursor

建表

table_name表不存在时建表——CREATE TABLE IF NOT EXISTS
执行操作语句使用try-catch完成。

• id为主键，不为空
• app_name不为空
• app_score 无上述要求

# 创建一个表
create_table_query = """
        CREATE TABLE IF NOT EXISTS {table_name} (
            id INT PRIMARY KEY NOT NULL,
            app_name VARCHAR(255) NOT NULL,
            app_score VARCHAR(255),
        )
        """
# 执行数据库建表操作
try:
    create_table_statement = create_table_query.format(table_name=table_name)
    print(f"create_table_statement为{create_table_statement}")
    # 执行sql
    cursor.execute(create_table_statement)
    db.commit()  # 提交数据库执行
except Exception as e:
    print(f'create_table_error:{e}')
    # 发生错误时回滚
    db.rollback()

插入 Insert

在增删改查中，对于参数都有两种写法，第一种是直接在sql模板中指明对应的变量，比如{app_name}等。

insert_template = "INSERT INTO {table_name} (app_name, so_name) VALUES ( '{app_name}', '{so_name}');"
try:
    insert_sql_statement = insert_template.format(table_name=table_name, app_name=app_name, so_name=so_name)
    print(f"insert_sql_statement 为{insert_sql_statement}")
    cursor.execute(insert_sql_statement)
    db.commit()     #提交数据库执行
    print("写入sql完成")
except Exception as e:
    print(f'写入sql失败,{e}')
    # 发生错误时回滚
    db.rollback()
finally:
    db.close()      #关闭数据库连接

第二种是通过%s表示参数。第一种参数传递方法直接将输入的 app_name 和 so_name 直接插入 SQL 语句中。这种做法容易导致 SQL 注入攻击，建议使用参数化查询来避免这个问题。

insert_template = "INSERT INTO {table_name} (app_name, so_name) VALUES (%s, %s);"
try:
    insert_sql_statement = insert_template.format(table_name=table_name)
    # 这里将app_name, so_name参数以元组方式传入
    cursor.execute(insert_sql_statement, (app_name, so_name))
    db.commit()
    print("写入sql完成")
except Exception as e:
    print(f'写入sql失败, {e}')
    db.rollback()
finally:
    db.close()

删除 Delete

下面代码用于删除pkg_version为指定值的一整条信息。
注意python中元组是通过逗号来定义的，即使只有一个元素也不能省略逗号。但是当定义一个包含多个元素的元组时，最后一个元素后面的逗号是可选的。

try:
    sql_delete = "DELETE FROM {table_name} WHERE pkg_version=%s"
    # 使用模板格式化删除语句
    delete_query = sql_delete.format(table_name=table_name)
    # 打印 SQL 语句以进行调试
    print(f"delete SQL: {delete_query}")
    # 执行删除操作 这里逗号不可省略
    cursor.execute(delete_query, ('example_version',))
    # 提交更改
    db.commit()
except Exception as e:
    print(f"An error occurred when delete_sql_none: {e}")
    db.rollback()  # 发生错误时回滚更改
finally:
    print('successful delete sql where pkg_version=None')

修改 Update

sql_update = "UPDATE {table_name} SET buildtools = %s WHERE id = %s"
try:
    # 格式化 SQL 更新语句
    sql = sql_update.format(table_name=table_name)
    # 打印 SQL 语句以进行调试
    # print(f"update SQL: {sql}")
    # 执行更新操作
    rows_affected = cursor.execute(sql, (build_system, id))
    # 提交更改
    db.commit()
    # print(f"Rows updated successfully: {rows_affected}")
except Exception as e:
    print(f"An error occurred: {e}")
    db.rollback()  # 发生错误时回滚更改
finally:
    db.close()      #关闭数据库连接

查找 Select

这里分为两个模板：查找所有数据、以及根据要求查找数据。

查找表中所有数据

在使用数据库数据时，需要先查找table_name中所有的数据，这里我习惯存入dataframe中，方便后面遍历整个表，获取某些需要的值。

try:
    # 执行SQL查询
    sql_template = "SELECT * FROM {table_name}"
    # 应该使用哪个变量来替换字符串中的{table_name} kv一致也不能省略
    select_sql = sql_template.format(table_name=table_name)
    # print("sql语句：", select_sql)
    cursor.execute(select_sql)
    # 获取列名
    columns = [desc[0] for desc in cursor.description]
    print(columns)
    # 获取所有记录列表
    results = cursor.fetchall()
    # 将结果转换为pandas DataFrame
    df = pd.DataFrame(results, columns=columns)
    rowNum = df.shape[0]
    print(f'转换为df对象完成,共{rowNum}行')
finally:
    db.close()      #关闭数据库连接
    return df

查找表中满足要求的数据

下面例子是查找表中pkg_name为example_name的数据个数，使用COUNT(*)统计结果。并使用result接收结果值。

# 查询数据的 SQL 语句
sql_template = "SELECT COUNT(*) FROM {table_name} WHERE pkg_name = %s"
select_sql = sql_template.format(table_name=table_name)
# print(f"sql语句为:{select_sql}")
try:
    # 执行查询操作
    cursor.execute(select_sql,('example_name',))
    # 获取查询结果
    result = cursor.fetchone()
except Exception as e:
    print(f"An error occurred: {e}")
    db.rollback()  # 发生错误时回滚更改
finally:
    # 返回计数结果
    return result[0]