前言
本次博客主要讲解通过pymysql
来操作数据库,以及Mysql数据库DDL和DML基础
前提
:安装pymysql
pip install pymysql
一、python操作Mysql数据库
1、创建数据库连接
主要是通过pymysql库中的Connection()来建立数据库的连接。
1.1、Connection()介绍
Connection()
方法是 pymysql
模块中用于创建与 MySQL 数据库的连接的工厂方法,它可以接受以下参数:
host
:MySQL 服务器的主机名,默认为"localhost"
。port
:MySQL 服务器的端口号,默认为3306
。user
:连接 MySQL 的用户名,默认为"root"
。password
:连接 MySQL 的密码,默认为None
。database
:需要连接的数据库,默认为None
。charset
:连接使用的字符集,默认为"utf8mb4"
。autocommit
:是否自动提交事务,默认为False
。local_infile
:是否启用本地数据文件加载,默认为False
。ssl
:是否启用 SSL 连接。可以是一个字典,其中可以包含以下键值对:ca
:指定 SSL CA 文件的路径。cert
:指定 SSL 证书的路径。key
:指定 SSL 私钥的路径。cipher
:指定 SSL 加密算法。ssl_version
:指定 SSL 协议版本。默认为 None,可以使用ssl.PROTOCOL_TLS
或ssl.PROTOCOL_SSLv23
。check_hostname
:是否检查 SSL 证书的主机名,默认为True
。
cursorclass
:返回的游标类型,默认为None
,表示返回普通游标对象。如果指定为pymysql.cursors.DictCursor
,则返回字典游标,可以通过列名访问数据。
不需要记,常用的也就那么几个,需要用到的时候查就可以了。
1.2、建立连接案例
from pymysql import Connection
# 获取到MySql数据库的链接对象
conn = Connection(
host = 'localhost', # 主机名(或IP地址)
port = 3306, # 端口,默认3306
user = 'root', # 账户名
password = '密码', # 密码
autocommit=True # 设置自动提交
)
# 打印MySQl数据库软件信息
print(conn.get_server_info())
# 关闭到数据库的链接
conn.close()
注意:
conn.close()
方法用于关闭数据库连接,释放关联的所有资源。当你使用完连接后,通常会调用该方法来关闭连接,以避免浪费计算机资源和数据库连接数。如果你不手动关闭连接,那么该连接可能会一直处于打开状态,直到达到连接池中的最大连接数,这可能会阻止其他用户访问数据库。
此外,当你执行了修改、插入或删除数据等操作后,如果没有调用 conn.commit()
方法来提交事务,那么在你关闭连接时,所有未提交的修改都将自动回滚,以确保数据的完整性和一致性。因此,即使你没有显式地调用 conn.commit()
方法,也应该在完成所有操作后调用 conn.close()
方法来关闭连接。
总之,调用 conn.close()
方法是一个良好的编程习惯,可以在代码中防止资源泄漏和潜在的数据一致性问题。
2、常用的操作
在 pymysql
中,Connection()
方法用于创建与 MySQL 数据库的连接,通过该连接可以调用一些常用的方法来执行 SQL 查询和操作。常用的方法包括:
cursor()
方法:创建游标对象,用于执行 SQL 查询和操作。commit()
方法:提交数据库的事务。rollback()
方法:回滚数据库的事务。select_db()
方法:选择当前连接的默认数据库。ping()
方法:测试与 MySQL 服务器的连接是否仍然活动。close()
方法:关闭数据库连接。
下面是使用这些方法的一些示例代码:
import pymysql
# 建立数据库连接
conn = pymysql.connect(host='localhost', user='root', password='password', database='example')
# 创建游标对象
cursor = conn.cursor()
# 执行查询
cursor.execute("SELECT * FROM customers")
# 获取查询结果
result = cursor.fetchall()
# 输出查询结果
for row in result:
print(row)
# 提交事务
conn.commit()
# 关闭连接
conn.close()
在这个示例中,我们使用了 pymysql
模块的 connect()
方法来创建与 MySQL 数据库的连接,然后使用 cursor()
方法来创建游标对象。接着,我们使用 execute()
方法执行了一条查询语句,然后使用 fetchall()
方法获取查询结果。最后,我们使用 commit()
方法提交了事务,并使用 close()
方法关闭了数据库连接
下面我们就细细讲解一下常用的方法吧,一切的前提是要先连接上mysql
,所以下面的代码中就省略连接部分
2.1、选择数据库select_db()
from pymysql import Connection
# 获取到MySql数据库的链接对象
conn = Connection(
....
)
# 选择数据库
conn.select_db("test")
该方法实现的本质相当于以下 SQL 操作:USE test;
如果没有指定数据库,那么该连接将默认使用 MySQL 服务器上的 test 数据库。注意
,该方法只适用于连接到 MySQL 服务器。对于其他类型的数据库管理系统,可能需要使用不同的方法来选择默认数据库。
2.2、获取游标对象cursor()
- 游标对象本质上是
一种用于执行 SQL 查询、操作数据库的工具
,将查询的结果存储在游标对象中,在 Python代码中可以通过游标对象来访问这些结果。 - 使用游标对象的好处在于,它可以很好地支持批量数据查询和分批处理数据等操作
具体地说,使用游标对象可以方便地进行以下操作:
- 执行 SQL 查询语句:使用游标对象的
execute()
方法可以执行 SQL 查询操作,并将结果存储在游标对象中。 - 提交 SQL 事务:如果你需要在执行一系列 SQL 操作后提交事务,可以使用游标对象的
connection.commit()
方法来提交事务。 - 回滚 SQL 事务:如果你需要在执行 SQL 操作后回滚事务,可以使用游标对象的
connection.rollback()
方法来回滚事务。 - 获取 SQL 查询结果:游标对象的方法
fetchone()
、fetchmany()
和fetchall()
可以用于获取查询结果。fetchone()
方法用于获取查询结果的第一行数据;fetchmany()
方法用于获取指定数量的查询结果;fetchall()
方法用于获取所有的查询结果。
总之,通过使用游标对象,我们可以更加方便地执行 SQL 操作和管理数据,并且可以更好地控制事务的提交和回滚。
2.3、对象cursor()
常用的方法
execute()
方法:用于执行 SQL 语句,查询返回结果为0,而修改、删除、新增等操作返回执行的行数。fetchone()
方法:用于获取查询结果的第一行数据。fetchmany()
方法:用于获取指定数量的查询结果。fetchall()
方法:用于获取所有的查询结果。scroll()
方法:用于在查询结果中滚动游标位置,支持相对和绝对滚动。close()
方法:用于关闭游标,释放资源。rowcount
属性:返回最近一次 execute() 方法执行的结果的行数。execute_many()
方法:用于批量执行 SQL 语句。callproc()
方法:用于执行存储过程。nextset()
方法:用于移动到下一个结果集。
2.4、提交数据库的事务commit()
在使用关系型数据库时,事务管理是一个非常重要的问题,一个事务是指一组关联操作,这些操作要么全部成功,要么全部失败。而在数据库中,事务是由一组 SQL 语句组成的逻辑处理单元,这些语句要么全部执行成功,要么全部执行失败。在这个过程中, commit()
是用来提交事务,将数据库变更保存到数据库的一种机制。
在 pymysql
中,我们可以通过 commit()
方法来手动提交事务。在执行 SQL 语句时,如果正在处理事务,那么在事务完成之前,所有的数据库操作都将被保存在内存中或者缓存中,并没有立即写入到硬盘。当事务操作完成后,如果想要确保数据能够永久保存在硬盘中,就需要将其提交,这时就可以使用 commit()
方法来提交事务。
注意:
也可以在Connection()中设置autocommit=True来自动提交
2.5、回滚事务rollback()
在使用关系型的数据库时,事务管理是一个非常重要的问题。当多个 SQL 语句需要作为一个整体执行时,应该使用事务。这是因为,事务是一个逻辑处理单元,要么全部成功提交,要么全部回滚。因此,在执行这类复合操作时,如果出现错误或异常情况,需要进行回滚操作,以保证操作结果与预期一致。在 pymysql
中,我们可以使用 rollback()
方法来回滚事务。
下面是一个使用 commit()
和rollback()
方法的示例代码:
import pymysql
# 建立数据库连接
conn = pymysql.connect(host='localhost', user='root', password='password', database='example')
# 创建游标对象
cursor = conn.cursor()
try:
# 执行 SQL 操作
cursor.execute("INSERT INTO customers (name, email) VALUES ('John Doe', 'john@example.com')")
cursor.execute("INSERT INTO customers (name, email) VALUES ('Jane Doe', 'jane@example.com')")
# 提交事务
conn.commit()
except:
# 回滚事务
conn.rollback()
finally:
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
2.5、测试与 MySQL 服务器的连接是否仍然活动ping()
在使用 pymysql
连接 MySQL 数据库时,有些情况下需要确保与数据库的连接仍然有效,避免因为连接超时而断开连接。为了检测与数据库的连接是否有效,可以使用 ping()
方法来检查连接是否仍然存活。如果连接存活,则该方法返回值为 True
,否则为 False
。
下面是一个使用 ping()
方法的示例代码:
import pymysql
# 创建数据库连接
conn = pymysql.connect(
host='localhost',
user='root',
password='password',
database='example',
cursorclass=pymysql.cursors.DictCursor
)
# 检查连接是否存活
if conn.ping(reconnect=False):
print("Connection is alive")
else:
print("Connection is not alive")
# 关闭数据库连接
conn.close()
在这个示例中,我们首先使用 pymysql
创建了数据库连接。然后,我们使用 ping()
方法来检查连接是否存活。如果连接存活,则输出 “Connection is alive”,否则输出 “Connection is not alive”。最后,我们关闭了数据库连接。
需要注意的是,在使用 ping()
方法时,reconnect
参数默认为 True
,表示如果连接断开,则自动尝试重新建立连接。如果指定 reconnect=False
,则不会自动尝试重新建立连接。
3.6、关闭数据库连接
在 pymysql
中,conn.close()
方法用于关闭数据库连接,释放关联的所有资源。当你使用完连接后,通常会调用该方法来关闭连接,以避免浪费计算机资源和数据库连接数。如果你不手动关闭连接,那么该连接可能会一直处于打开状态,直到达到连接池中的最大连接数,这可能会阻止其他用户访问数据库。
此外,当你执行了修改、插入或删除数据等操作后,如果没有调用 conn.commit()
方法来提交事务,那么在你关闭连接时,所有未提交的修改都将自动回滚,以确保数据的完整性和一致性。因此,即使你没有显式地调用 conn.commit()
方法,也应该在完成所有操作后调用 conn.close()
方法来关闭连接。
总之,调用 conn.close()
方法是一个良好的编程习惯,可以在代码中防止资源泄漏和潜在的数据一致性问题。
二、SQL常用
重点介绍DDL(表的操作)
和DML(数据操作)
1、SQL的通用语法
- SQL语句可以单行或多行书写,以分号结尾。
- SQL语句可以使用
空格
/缩进
来增强语句的可读性。 - MySQL数据库的语句不区分大小写,关键字建议使用大写。
单行注释
:--
注释内容 或#
注释内容多行注释
:/*
注释内容*/
2、SQL分类
分类 | 全称 | 说明 |
---|---|---|
DDL | Data Definition Language | 数据定义语言,用来定义数据库对象(数据库,表,字段) |
DML | Data Manipulation Language | 数据操作语言,用来对数据库表中的数据进行增删改查 |
DQL | Data Query Language | 数据查询语言 ,用来对查询数据库中的表的记录 |
DCL | Data Control Language | 数据控制语言,用来创建数据库用户、控制数据库的访问权限 |
3、DDL(数据库和表的操作)
3.1、数据库操作 - 基本语法
- 查询:
查询所有数据库:
SHOW DATABASES
; 🔜 show databases;
查询当前数据库:
SELECT DATABASE()
; 🔜 show database();
- 创建
CREATE DATABASE
[IF NOT EXISTS
] 数据库名 [DEFAULT CHARSET
字符集 ] [COLLATE
排序规则 ] ;
🔜create database
[if not exists
] 数据库名 [default charset
字符集 ] [collate
排序规则 ] ;
- 删除
DROP DATABASE
[IF EXISTS
] 数据库名; 🔜drop database
[if exists
] 数据库名;
- 使用
USE
数据库名; 🔜use
数据库名;
3.2、DDL - 表操作 - 查询表
- 查询当前数据库所有表
SHOW TABLES;
🔜show tables;
- 查询表结构
DESC
表名; 🔜desc
表名
3.查询指定表的建表语句
SHOW CREATE TABLE
表名; 🔜show create table
表名;
3.3、DDL - 表操作 - 创建表
- 基本语法:
CREATE TABLE 表名(
字段1 字段1类型 [COMMENT 字段1注释],
字段2 字段2类型 [COMMENT 字段2注释],
字段3 字段3类型 [COMMENT 字段3注释],
......
字段n 字段n类型 [COMMENT 字段n注释]
) [COMMENT 表注释];
注意:[...]为可选参数,最后一个字段后面没有逗号
3.4、DDL - 表操作 - 数据类型
- 数值类型
类型 | 大小 | 有符号(SIGNED )范围 | 无符号(UNSIGNED )范围 | 描述 |
---|---|---|---|---|
TINYINT 🔜 tinyint | 1 byte | (-128,127) | (0,255) | 小整数值 |
SMALLINT 🔜 smallint | 2 bytes | (-32768,32767) | (0,65535) | 大整数值 |
MEDIUMINT 🔜 mediumint | 3 bytes | (-8388608,8388607) | (0,16777215) | 大整数值 |
INT或INTEGER 🔜 int或integer | 4 bytes | (-2147483648,2147483647) | (0,4294967295) | 大整数值 |
BIGINT 🔜 bigint | 8 bytes | (-263 ,263-1) | (0,264-1) | 极大整数值 |
FLOAT 🔜 float | 4 bytes | (-3.402823466 E+38,3.402823466351 E+38) | 0 和 (1.175494351 E-38,3.402823466 E+38) | 单精度浮点数值 |
DOUBLE 🔜 double | 8 bytes | (-1.7976931348623157 E+308,1.7976931348623157 E+308) | 0 和 (2.2250738585072014 E-308,1.7976931348623157 E+308) | 双精度浮点数值 |
DECIMAL 🔜 decimal | 依赖于M(精度)和D(标度)的值 | 依赖于M(精度)和D(标度)的值 | 小数值(精确定点数) |
- 字符串类型
类型 | 大小 | 描述 |
---|---|---|
CHAR 🔜 char | 0-255 bytes | 定长字符串 |
VARCHAR 🔜 varchar | 0-65535 bytes | 变长字符串 |
TINYBLOB 🔜 tinyblob | 0-255 bytes | 不超过255个字符的二进制数据 |
TINYTEXT 🔜 tinytext | 0-255 bytes | 短文本字符串 |
BLOB 🔜 blob | 0-65 535 bytes | 二进制形式的长文本数据 |
TEXT 🔜 text | 0-65 535 bytes | 长文本数据 |
MEDIUMBLOB 🔜 mediumblob | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT 🔜 mediumtext | 0-16 777 215 bytes | 中等长度文本数据 |
LONGBLOB 🔜 longblob | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
LONGTEXT 🔜 longtext | 0-4 294 967 295 bytes | 极大文本数据 |
- 日期类型:
类型 | 大小 | 范围 | 格式 | 描述 |
---|---|---|---|---|
DATE 🔜 date | 3 | 1000-01-01 至 9999-12-31 | YYYY-MM-DD | 日期值 |
TIME 🔜 time | 3 | '-838:59:59 至 838:59:59 | HH:MM:SS | 时间值或持续时间 |
YEAR 🔜 year | 1 | 1901 至 2155 | YYYY | 年份值 |
DATETIME 🔜 datetime | 8 | 1000-01-01 00:00:00 至 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP 🔜 timestamp | 4 | 1970-01-01 00:00:01 至 2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值,时间戳 |
3.5、DDL - 表操作 - 修改
- 添加字段
ALTER TABLE
表名ADD
字段名 类型(长度) [COMMENT
注释] [约束];
🔜alter table
表名add
字段名 类型(长度) [comment
注释] [约束];
- 修改数据类型
ALTER TABLE
表名MODIFY
字段名 新数据类型(长度);
🔜alter table
表名modify
字段名 新数据类型(长度);
- 修改字段名和字段类型
ALTER TABLE
表名CHANGE
旧字段名 新字段名 类型(长度) [COMMENT
注释] [约束];
🔜alter table
表名change
旧字段名 新字段名 类型(长度) [comment
注释] [约束];
- 删除字段
ALTER TABLE
表名DROP
字段名;
🔜alter table
表名drop
字段名;
- 修改表名
ALTER TABLE
表名RENAME TO
新表名;
🔜alter table
表名rename to
新表名;
3.6、DDL - 表操作 - 删除
- 删除表
DROP TABLE
[IF EXISTS
] 表名;
🔜drop table
[if exists
] 表名;
- 删除指定表,并重新创建该表
TRUNCATE TABLE
表名;
🔜truncate table
表名;
4、DML(数据操作)
- 添加数据(
INSERT
🔜insert
) - 修改数据(
UPDATE
🔜update
) - 删除数据(
DELETE
🔜delete
)
4.1、添加数据(INSERT
)
- 给指定字段添加数据
INSERT INTO
表名 (字段1,字段2,…)VALUES
(值1,值2,…);
🔜insert into
表名 (字段1,字段2,…)values
(值1,值2,…);
- 给全部字段添加数据
INSERT INTO
表名VALUES
(值1,值2,…);
🔜insert into
表名values
(值1,值2,…);
- 批量添加数据
INSERT INTO
表名 (字段1,字段2,…)VALUES
(值1,值2,…),(值1,值2,…),(值1,值2,…),;
🔜insert into
表名 (字段1,字段2,…)values
(值1,值2,…),(值1,值2,…),(值1,值2,…);
注意:
- 插入数据时,指定的字段顺序需要与值得顺序一一对应的。
- 字符串和日期型数据应该包含在引号中。
- 插入的数据大小,应该在字段的规定范围内。
4.2、修改数据(UPDATE
)
- 基本语法
UPDATE
表名SET
字段名1 = 值1,字段名2 = 值2,…[WHERE
条件];
🔜update
表名set
字段名1 = 值1,字段名2 = 值2,…[where
条件];
注意:修改语句的条件可以有,也可以没有,如果没有,则会修改整张表的所有数据
4.3、删除数据(DELETE
)
- 基本语法
DELETE FROM
表名 [WHERE
条件]
🔜delete from
表名 [where
条件]
注意:
delete
语句的条件可以有,也可以没有,如果没有,则会删除整张表的所有数据。delete
语句不能删除某一个字段的值(可以使用update
)