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

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

简单介绍

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

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

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

• MySQL
• PostgreSQL
• Oracle Database
• Microsoft SQL Server
• SQLite

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

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

连接数据库

import  pymysqldef 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 含义 年龄 类型 intsql = """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()

插入数据

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

1. Atomicity 原子性 事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
2. Consistency 一致性 事务必须是数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
3. Isolation 隔离性 一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
4. 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 的学生的年龄为 22sql = "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 = %stry:# 执行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("查询数据失败")