python操作Mysql数据库和 Mysql数据库DDL和DML基础

原创已于 2023-06-29 19:12:29 修改 · 1.3k 阅读

10 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#数据库 #python #mysql

于 2023-06-21 13:39:22 首次发布

python 同时被 2 个专栏收录

1 篇文章

订阅专栏

sql数据库

1 篇文章

订阅专栏

本文介绍了如何使用Python的pymysql库连接和操作MySQL数据库，包括创建连接、选择数据库、游标操作、事务处理等。同时，讲解了SQL的通用语法，以及DDL（如创建、查询、修改和删除数据库及表）和DML（插入、更新和删除数据）的基础知识。

前言

本次博客主要讲解通过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.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	(-2⁶³ ,2^63-1)	(0，2^64-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	混合日期和时间值，时间戳