Python 操作mysql - 关系型数据库存储

文章目录

Python 操作mysql - 关系型数据库存储
- 简单介绍
- 连接数据库
- 创建表
- 插入数据
- 更新数据
- 删除数据
- 查询数据

简单介绍

关系型数据库是一种以“关系”的方式来组织和存储数据的数据库。它使用表（也称为“关系”）来表示数据，每个表由一组具有相同性质的列和多行数据组成。关系型数据库的主要特征包括：

数据结构化：数据以表的形式结构化存储，表由行和列组成。行代表记录，列代表字段。
主键：每张表都有一个主键，用于唯一标识表中的每一条记录。主键确保数据的唯一性。
外键：外键用于建立表与表之间的关系，连接不同表的数据。
数据完整性：关系型数据库可以通过定义约束（如主键、外键、唯一性等）来保证数据的完整性和有效性。
SQL（结构化查询语言）：关系型数据库使用SQL作为标准接口来进行数据查询、操作、更新和管理。通过SQL，用户可以轻松地执行复杂的数据操作。

常见的关系型数据库管理系统（RDBMS）包括：

MySQL
PostgreSQL
Oracle Database
Microsoft SQL Server
SQLite

关系型数据库在很多应用场景中被广泛使用，特别是对于需要复杂查询和事务处理的应用。

以mysql为例子简单使用pymysql 操作数据库

连接数据库

import  pymysql

def connect_mysql():
    
    # 执行SQL语句
    sql = "SELECT * FROM person"
    cursor.execute(sql)
    # 获取查询结果 fetchall()方法获取所有查询结果
    rows = cursor.fetchall()
    # 打印查询结果
    for row in rows:
        print(row)
    #  关闭数据库连接
    db.close()

# 主函数
if __name__ == '__main__':
    # 创建数据库连接
    db = pymysql.connect(host='localhost', user='root', password='111111', port=3306, db='test')
    # 创建游标 利用游标可以执行SQL语句
    cursor = db.cursor()
    connect_mysql()

创建表

def create_table():
    # 创建 数据表 students
    # 字段名 id 含义 学号 类型 varchar(20)
    # 字段名 name 含义 姓名 类型 varchar(20)
    # 字段名 age 含义 年龄 类型 int
    sql = """CREATE TABLE IF NOT EXISTS students (
            id varchar(20) NOT NULL,
            name varchar(20) NOT NULL,
            age int NOT NULL,
            PRIMARY KEY (id)
            )"""
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
        print("创建数据表 students 成功")
    except:
        # 发生错误时回滚
        db.rollback()
        print("创建数据表 students 失败")

# 主函数
if __name__ == '__main__':
    # 创建数据库连接
    db = pymysql.connect(host='localhost', user='root', password='111111', port=3306, db='test')
    # 创建游标 利用游标可以执行SQL语句
    cursor = db.cursor()
    create_table()

插入数据

插入过程中要注意事务的四个属性：

Atomicity 原子性事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
Consistency 一致性事务必须是数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
Isolation 隔离性一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
Durability 持久性一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

 # 创建数据库连接
    db = pymysql.connect(host='localhost', user='root', password='111111', port=3306, db='test')
    # 创建游标 利用游标可以执行SQL语句
 cursor = db.cursor()
def insert_data():
    # 插入数据
    # 插入一条数据 (1001, '张三', 20)
    sql = "INSERT INTO students (id, name, age) VALUES (%s,%s,%s)"
    try:
        # 执行SQL语句
        cursor.execute(sql, ('1001', '张三', 20))
        # 提交到数据库执行
        db.commit()
        print("插入数据成功")
    except:
        # 发生错误时回滚
        db.rollback()
        print("插入数据失败")


# 更加通用的插入数据方法
def insert_data_more():
    # 动态传入字典数据
    data = {
        'id': '1002',
        'name':'李四',
        'age': 21,
    }
    table_name ='students'
    keys = ','.join(data.keys())  # 字段名
    values = ','.join(['%s'] * len(data))  # 占位符
    sql = "INSERT INTO {table_name} ({keys}) VALUES ({values})".format(table_name=table_name, keys=keys, values=values)
    try:
        # 执行SQL语句
        cursor.execute(sql, tuple(data.values()))
        # 提交到数据库执行
        db.commit()
        print("插入数据成功")
    except:
        # 发生错误时回滚
        db.rollback()
        print("插入数据失败")

更新数据

 # 创建数据库连接
    db = pymysql.connect(host='localhost', user='root', password='111111', port=3306, db='test')
    # 创建游标 利用游标可以执行SQL语句
 cursor = db.cursor()

def update_data():
    # 更新数据
    # 更新 id 为 1001 的学生的年龄为 22
    sql = "UPDATE students SET age = %s WHERE id = %s"
    try:
        # 执行SQL语句
        cursor.execute(sql, (22, '1001'))
        # 提交到数据库执行
        db.commit()
        print("更新数据成功")
    except:
        # 发生错误时回滚
        db.rollback()
        print("更新数据失败")

在实际的数据抓取过程中，大部分情况下需要插入数据，但是我们关心的是会不会出现重复数据，如果出现了，我们希望更新数据而不是重复保存一次再实现一种去重的方法，如果数据存在，则更新数据；如果数据不存在，则插入数据。另外，这种做法支持灵活的字典传值。

def insert_or_update_data(data):
    # 动态传入字典数据
    table_name ='students'
    keys = ','.join(data.keys())  # 字段名
    values = ','.join(['%s'] * len(data))  # 占位符
    sql = "INSERT INTO {table_name} ({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE {update_sql}".format(  # 这里的ON DUPLICATE KEY UPDATE 代表如果主键重复，则更新数据
        table_name=table_name, keys=keys, values=values, update_sql=','.join(['{key} = %s'.format(key=key) for key in data.keys()]))
    # sql 语句完整版本：
    # INSERT INTO students (id, name, age) VALUES (%s,%s,%s) ON DUPLICATE KEY UPDATE id = %s, name = %s, age = %s
    try:
        # 执行SQL语句
        cursor.execute(sql, tuple(data.values()) * 2)
        # 提交到数据库执行
        db.commit()
        print("插入或更新数据成功")
    except:
        # 发生错误时回滚
        db.rollback()
        print("插入或更新数据失败")

删除数据

def delete_data():
    # 删除数据
    # 删除 id 为 1001 的学生
    sql = "DELETE FROM students WHERE id = %s"
    try:
        # 执行SQL语句
        cursor.execute(sql, ('1001',))
        # 提交到数据库执行
        db.commit()
        print("删除数据成功")
    except:
        # 发生错误时回滚
        db.rollback()
        print("删除数据失败")

查询数据

def select_data():
    # 查询数据
    # 查询 age 字段大于 20 的学生的姓名和年龄
    sql = "SELECT name, age FROM students WHERE age > %s"
    try:
        # 执行SQL语句
        cursor.execute(sql, (20))
        # 获取查询结果 fetchall()方法获取所有查询结果
        rows = cursor.fetchall()
        # 打印查询结果
        for row in rows:
            print(row)
    except:
        # 发生错误时回滚
        db.rollback()
        print("查询数据失败")