17. Python 数据库操作之MySQL和SQLite实例

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用版 2核4GB 50GB
简介: 17. Python 数据库操作之MySQL和SQLite实例

1. 简介

数据库种类繁多,每种数据库的对外接口实现各不相同,为了方便对数据库进行统一的操作,大部分编程语言都提供了标准化的数据库接口,用户不需要了解每种数据的接口实现细节,只需要简单地配置,就能快速切换,操作不同的数据库,这样大大降低了编程的难度。


在Python Database API V2.0 中,规范了Python操作不同类型数据库的标准方法,以及组成部分,通过DB API接口可以使用相同的方法连接、操作不同的数据库。主要作用:兼容不同类型的数据库,降低编程难度。该API主要包括:数据库连接对象、数据库交互对象、数据库异常类。


使用DB API的流程如下:

  1. 安装数据库驱动程序。
  2. 引入数据库API模块。
  3. 获取与数据库的连接。
  4. 执行SQL语句和存储过程。
  5. 关闭数据库连接。

安装数据库驱动之后,就可以使用Python DB API规范的connect()函数连接数据库。调用connect()函数返回一个connection对象,通过connection对象可以连接数据库,然后访问数据库。

符合规范的数据驱动接口都支持connect对象及连接方法。参数说明如下:

  • user:登录数据库的用户名。
  • password:登录数据库的用户密码。
  • host:数据库服务器的主机名,本地数据库服务器一般为localhost。
  • database:数据库名称。
  • dsn:数据源名称。如果数据库支持则可以设置。

connect()函数返回一个连接对象,表示当前用户与数据库服务器建立的会话。通过连接对象支持的方法可以实现对数据库的读、写操作。connection对象包含的主要方法如下:

  • commit():提交事务。在事务提交之前,所有对数据库进行的修改操作都不同步到数据库,只有在提交事务之后,才同步到数据库。
  • rollback():回滚事务。恢复数据库到操作之前的数据状态。
  • cursor():获取游标对象,通过游标对象操作数据库。
  • close():关闭数据库连接。关闭后无法再进行操作,除非再次创建连接。

DB API操作数据的主要步骤如下:

  1. 使用connect()函数创建connection对象。
  2. 使用connection对象创建cursor对象。
  3. 使用cursor对象执行SQL语句,查询数据库,或者执行SQL命令,操作数据库。
  4. 使用cursor对象从结果集中获取数据。
  5. 处理获取的数据。
  6. 关闭cursor对象。
  7. 关闭connection对象。

2. 使用PyMySQL

安装PyMySQL:根据以下命令安装和查看:

'''Windows 按住win+R 输入 cmd,Mac 打开Terminal'''
pip install PyMySQL
pip install mysql-connector-python
'''
import pymysql # 查看是否导入成功判断是否安装驱动成功

连接数据库:根据DB API操作数据的主要步骤,连接MySQL数据库。

import pymysql # 导入PyMySQL模块
# 连接数据库
db = pymysql.connect(
    host = 'localhost',
    port = 3306,
    user = 'root',
    password = '88888888',
    db = 'sql_test1')
db.close() # 关闭数据库连接

建立数据表:连接数据库之后,可以使用execute()方法为数据库创建表。

import pymysql # 导入PyMySQL模块
# 连接数据库
db = pymysql.connect(
    host = 'localhost',
    port = 3306,
    user = 'root',
    password = '88888888',
    db = 'sql_test1')
cursor = db.cursor() # 使用cursor()方法创建一个游标对象cursor
cursor.execute('drop table if exists tb_new;') # 使用execute()方法执行SQL,如果存在则删除
# 使用预处理语句创建表
ct_sql = """ 
create table tb_new(
    id int not null auto_increment comment '自增id',
    user_name varchar(255) comment '用户名',
    primary key (id)
);
"""
cursor.execute(ct_sql) # 使用execute()方法执行SQL查询
cursor.close() # 关闭游标对象
db.close() # 关闭数据库连接

事务处理

事务就是一个数据库操作序列,当一个事务被提交后,数据库要确保该事务中的所有操作都完成,如果部分未完成,则事务中的所有操作都被回滚,恢复到事务执行前的数据状态,这样可以确保数据操作的一致性和完整性。

提供了两个基本方法:commit()rollback()

当执行事务时,可以使用数据连接对象的commit()方法进行提交,如果事务处理成功,则不可撤销;如果事务处理失败,可以使用数据库连接对象的rollback()方法进行回滚,恢复数据库在操作之前的状态。

插入数据:为了避免操作失败,可以使用try语句进行异常跟踪,如果发生异常,则回滚操作,恢复数据库在操作之前的数据状态。

import pymysql # 导入PyMySQL模块
# 连接数据库
db = pymysql.connect(
    host = 'localhost',
    port = 3306,
    user = 'root',
    password = '88888888',
    db = 'sql_test1')
cursor = db.cursor() # 使用cursor()方法创建一个游标对象cursor
try:
    sql = "insert into tb_new(id, user_name) values (10,'test');"
    cursor.execute(sql) # 使用execute()方法执行SQL查询
    db.commit() # 提交事务,同步数据库数据
except:
    db.rollback() # 如果发生错误则回滚事务
cursor.close() # 关闭游标对象
db.close() # 关闭数据库连接

使用executemany(sql, data)方法批量插入数据:

import pymysql # 导入PyMySQL模块
# 连接数据库
db = pymysql.connect(
    host = 'localhost',
    port = 3306,
    user = 'root',
    password = '88888888',
    db = 'sql_test1')
cursor = db.cursor() # 使用cursor()方法创建一个游标对象cursor
sql = "insert into tb_new(id, user_name) values (%s,%s);" # 定义要执行的SQL语句
# 定义数据列表
data = [
    (1,'listi'),
    (2,'wangwu'),
    (3,'zhaoliu')
]
try:
    cursor.executemany(sql,data) # 批量执行SQL查询
    db.commit() # 提交事务,同步数据库数据
except:
    db.rollback() # 如果发生错误则回滚事务
cursor.close() # 关闭游标对象
db.close() # 关闭数据库连接

查询记录:使用cursor对象的execute()方法执行查询后,通过4种方法从结果集中读取数据。

  • fetchall():获取结果集下所有行。
  • fetchmany(size=None):获取结果集中下面size条记录。如果size大于结果集中的行数,则返回cursor.arraysize记录。
  • fetchone():获取结果集的一行记录。
  • rowcount:只读属性,返回执行execute()方法后影响的行数。
import pymysql # 导入PyMySQL模块
# 连接数据库
db = pymysql.connect(
    host = 'localhost',
    port = 3306,
    user = 'root',
    password = '88888888',
    db = 'sql_test1')
cursor = db.cursor() # 使用cursor()方法创建一个游标对象cursor
# SQL查询语句
sql = "select * from tb_new"
try:
    cursor.execute(sql)
    results = cursor.fetchall()
    for row in results:
        id = row[0]
        user_name = row[1]
        print('id=%s,user_name=%s'%(id,user_name))
except:
    print('Error:unable to fetch data')
cursor.close() # 关闭游标对象
db.close() # 关闭数据库连接

更新记录:修改表中的数据,主要使用SQL的update语句实现。

import pymysql # 导入PyMySQL模块
# 连接数据库
db = pymysql.connect(
    host = 'localhost',
    port = 3306,
    user = 'root',
    password = '88888888',
    db = 'sql_test1')
cursor = db.cursor() # 使用cursor()方法创建一个游标对象cursor
# SQL更新语句
sql = """
update tb_new set user_name = 'zhangs' where id = 3;
"""
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
cursor.close() # 关闭游标对象
db.close() # 关闭数据库连接

删除记录:删除表中的数据,主要使用SQL的delete from语句实现。

import pymysql # 导入PyMySQL模块
# 连接数据库
db = pymysql.connect(
    host = 'localhost',
    port = 3306,
    user = 'root',
    password = '88888888',
    db = 'sql_test1')
cursor = db.cursor() # 使用cursor()方法创建一个游标对象cursor
# SQL删除语句
sql = """
delete from tb_new where id = 2;
"""
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
cursor.close() # 关闭游标对象
db.close() # 关闭数据库连接

2. 使用SQLite

SQLite是一种嵌入式数据库,由C语言编写,体积很小,经常被集成到各种应用程序中,在IOS和Android的App中都可以集成。

SQLite是一个基于文件的关系型数据库,数据库只是一个文件,最多能存储140TB的数据。没有独立的进程,所有的维护都来自程序本身。

判断是否适合使用SQLite的标准,除了下面3点外,可以选择SQLite。

  • 如果程序和数据分离,且它们通过互联网连接,那么不适合使用SQLite。
  • 高并发写入,不适合用SQLite。
  • 如果数据量非常大,不适合用SQLite。

在使用SQLite之前,需要了解下面几个概念:

  • 表是数据库中存放关系数据的集合,一个数据库里面通常都包含多个表,如学生表、班级表、学校表等,表和表之间通过键关联。
  • 要操作关系数据库,首先需要连接到数据库,一个数据库连接称为connection。
  • 连接到数据库后,需要打开游标,称之为cursor,通过cursor执行SQL语句,然后,获得执行结果。

使用SQLite步骤如下:

  1. 导入sqlite3数据库模块。
  2. 创建和打开数据库。
  1. 获取数据连接对象connection。方法如下:
  1. commit():事务提交。
  2. rollback():事务回滚。
  3. close():关闭一个数据库连接。
  4. cursor():创建一个游标。
  1. 使用连接对象connection的cursor()方法打开一个cursor对象。
  2. 调用游标对象cursor的方法,执行SQL命令,如查询、更新、删除、插入等操作。
  3. 使用游标对象的fetchone()fetchmany()fetchall()方法读取结果。
  1. 分别调用close()方法,关闭cursor、connection 对象,结束整个操作。
import sqlite3
conn = sqlite3.connect(r'/Users/guanfawang/Downloads/test.db') # 连接SQLite数据库。若不存在,则自动创建
cursor = conn.cursor()  # 创建一个cursor
try:
    cursor.execute('create table user_tb(id varchar(20) primary key,name varchar(20))') # 创建表
    cursor.execute("insert into user_tb(id,name) values(\'1\',\'Michael\')") # 插入一条记录
    conn.commit() # 提交事务
except:
    conn.rollback() # 回滚事务
print(cursor.rowcount) # 影响的行数:1
cursor.close() # 关闭cursor
conn.close() # 关闭数据库连接


插入数据

# 插入单行数据
cur.execute('insert into 数据表 values(%s)'%data)
cur.execute('insert into 数据表 values(?,?,?)',(值1,值2,值3))
cur.execute('insert into 数据表 values(字段1,字段2,字段3) values(值1,值2,值3);')
# 插入多行数据
data = [(1,'a'),(2,'b'),(3,'c')] # 多行样例
sql_insert = "insert into 数据表 values" 
sql_values = ""
for i in range(0,len(data)): # 根据列表下标索引,提取一行数据
    sql_values += '('
    sql_values += str(data[i]).strip('(').strip(')') 
    sql_values += '),'
sql_values = sql_values.strip(',') # 去掉最后一个逗号
sql_todo = sql_insert + sql_values # 拼接成插入语句
cur.execute(sql_todo)

更新、删除、查询数据

import sqlite3 # 导入模块
conn = sqlite3.connect('test.db') # 创建数据库
cur = conn.cursor() # 创建一个cur游标对象
try:
    cur.execute("update company set salary = 25000 where id = 1") # 更新数据
    cur.execute("delete from company where id = 1") # 删除数据
    conn.commit() # 提交事务
except:
    conn.rollback() # 事务回滚
# 查询记录
results = conn.execute('select id,name,address,salary from company where id = 1')
for row in results:
    print('id=',row[0])
    print('name=',row[1])
    print('address=',row[2])
    print('salary=',row[3])
cur.close() # 关闭游标
conn.close() # 关闭数据库连接

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
16天前
|
存储 关系型数据库 MySQL
探索MySQL:关系型数据库的基石
MySQL,作为全球最流行的开源关系型数据库管理系统(RDBMS)之一,广泛应用于各种Web应用、企业级应用和数据仓库中
|
13天前
|
关系型数据库 MySQL 网络安全
Mysql 数据库主从复制
在MySQL主从复制环境中,配置了两台虚拟机:主VM拥有IP1,从VM有IP2。主VM的`my.cnf`设置server-id为1,启用二进制日志;从VM设置server-id为2,开启GTID模式。通过`find`命令查找配置文件,编辑`my.cnf`,在主服务器上创建复制用户,记录二进制日志信息,然后锁定表并备份数据。备份文件通过SCP传输到从服务器,恢复数据并配置复制源,启动复制。检查复制状态确认运行正常。最后解锁表,完成主从同步,新用户在从库中自动更新。
989 7
Mysql 数据库主从复制
|
14天前
|
缓存 运维 关系型数据库
数据库容灾 | MySQL MGR与阿里云PolarDB-X Paxos的深度对比
经过深入的技术剖析与性能对比,PolarDB-X DN凭借其自研的X-Paxos协议和一系列优化设计,在性能、正确性、可用性及资源开销等方面展现出对MySQL MGR的多项优势,但MGR在MySQL生态体系内也占据重要地位,但需要考虑备库宕机抖动、跨机房容灾性能波动、稳定性等各种情况,因此如果想用好MGR,必须配备专业的技术和运维团队的支持。 在面对大规模、高并发、高可用性需求时,PolarDB-X存储引擎以其独特的技术优势和优异的性能表现,相比于MGR在开箱即用的场景下,PolarDB-X基于DN的集中式(标准版)在功能和性能都做到了很好的平衡,成为了极具竞争力的数据库解决方案。
|
4天前
|
分布式计算 大数据 关系型数据库
MaxCompute产品使用合集之如何实现类似mysql实例中的数据库功能
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
6天前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
28 3
|
11天前
|
SQL 存储 数据挖掘
深入了解SQLite3命令:小巧强大的数据库工具
SQLite3是轻量级数据库工具,适用于嵌入式设备和数据分析。它提供交互式shell,无需服务器,易于部署。常用命令如`.schema`显示表结构,`.mode`设置输出格式。示例包括创建数据库`mydatabase.db`,创建表`users`,插入数据并查询。注意动态类型系统、性能限制及SQL注入安全。适合轻量级数据存储和管理。
|
5天前
|
DataWorks 关系型数据库 MySQL
DataWorks操作报错合集之从OceanBase(OB)数据库调度数据到MySQL数据库时遇到连接报错,该怎么办
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
|
13天前
|
关系型数据库 数据库 RDS
利用DTS将自建mysql5.7版本数据库迁移至对应rds报错
利用DTS将自建mysql5.7版本数据库迁移至对应rds报错
39 0
|
2月前
|
SQL 关系型数据库 MySQL
Python怎么操作Mysql数据库
Python怎么操作Mysql数据库
69 0
|
2月前
|
SQL 关系型数据库 MySQL
python如何操作mysql数据库
python如何操作mysql数据库
37 0