PYTHON与MYSQL的交互

一、PYTHON模块安装与数据库连接

1.安装模块

要使用Python连接MySQL数据库，需要安装一个用于驱动MySQL的工具pymysql。

pymysql是一个用于连接与操作MySQL数据库的开源模块。 安装pymysql非常简单，在终端中输入代码：pip install pymysql即可。

如果无法安装或安装缓慢，可以换源进行加速：

1
pip install pymysql -i https://pypi.tuna.tsinghua.edu.cn/simple/

安装完pymysql模块后，下一步就是在 Python 程序中导入这个模块。

1
2
3
4
# TODO 导入pymysql
import pymysql
# 输出模块版本信息
print(pymysql.__version__)

2.连接数据库

在pymysql中连接数据库时，依旧需要指定地址、端口、账号与密码。

大部分情况下，只需要对某一个特定的数据库操作，此时还可以指定数据库名。

代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
import pymysql
conn = pymysql.connect(
# 指定主机名，如为本地库，也可以是localhost
    host = "127.0.0.1",
#端口号
    port = 3306,
#用户名
    user = "root",
#用户密码
    password = "123456",
#数据库名
    database ="test")
#当不再使用数据库时，为了减少内存占用，通过使用close方法可以断开本次连接。
conn.close()

二、游标创建与操作

游标（或光标）是一种处理数据的工具，它能够标注出一行或多行数据，方便修改或显示。

执行SQL查询时，若查询成功，会返回一到多行数据。这些数据称为结果集。在结果集生成的同时，SQL会创建一个游标，并处于结果集的第一行数据之前。在默认情况下，游标会直接读取该位置之后的所有行数据并输出。直到游标后没有新的数据，游标停止移动，并自动关闭。

1.游标的意义

游标可以帮我们记录读取数据时要开始的位置和要结束的位置。

除了一次性输出所有数据外，若想要结果集中的数据：

  • 第一次显示1行；

  • 第二次显示4~5行；

  • 第三次显示6行。

    这样交互式的输出。可以通过控制游标的移动规则来实现。

2.游标的移动

获得结果集后，可以控制游标读取3行数据后停止。

  • 第一次的规则为读取游标后三行。则游标从第一行开始，到第三行数据后停止。这时，游标经过的三行数据会被直接输出。
  • 移动后的游标此时停止在第四行数据之前。再次控制游标移动，这一次让游标读取2行数据后停止，就能获取结果集中的4~5行数据并输出。

3.创建与关闭游标

  • 为当下的连接创建一个游标，需要使用cursor。调用 cursor 时需要使用句点法，即连接变量名.cursor()，且不需要传递参数。
  • 创建游标也会占用系统资源，所以当停止使用游标时，需要使用close关闭。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
import pymysql

conn = pymysql.connect(
    host = "127.0.0.1",
    port = 3306,
    user = "root",
    password = "123456",
    database = "test")
#创建游标
cur = conn.cursor()
#关闭游标
cur.close()

conn.close()

4.对游标的操作

4.1第一步，利用游标执行SQL语句

执行SQL语句需要使用游标的execute()方法。

需要把SQL语句作为参数，传递给execute。

  • 要在Python中执行SQL，需要将SQL代码以字符串的形式传递。

  • 文档字符串的应用：SQL代码中可能存在单引号或双引号 ，这与Python字符串的符号规则相同。这可能造成引号无法正确识别并匹配的问题。

    为了避免引号匹配的错误，可以通过三个引号组成的多行字符串存储SQL代码。

    这样的字符串又称为文档字符串。

    文档字符串的引号可以由三个单引号或三个双引号组成，它能够保留字符串内的换行信息。

接下来，若想要获取表中的所有内容，可以使用 SELECT * FROM brand; 。

创建一个变量来存储这段SQL代码，为了避免出错使用文档字符串。

1
2
3
4
SQL = '''
SELECT *
FROM brand;
'''

执行SQL代码：

1
2
ret = cur.execute(SQL)
print(f"本次查询共获得了{ret}条信息")

数据库接收到SQL代码后会尝试查询。若查询成功，execute 返回查询出了多少行数据；

若SQL代码错误、查询失败，则程序会报错。将这一返回值存储到变量中并输出，方便查看结果。

4.2 fetch方法

SQL 执行成功后，游标会获得查询后的结果集。

在pymysql中，cursor 提供系列的 fetch 方法，用来控制游标从结果集中读取内容。

对于游标获取到的结果，我们可以通过三种方式抓取数据：

  1. 从当前位置开始获取一行数据，fetchone()；

  2. 从当前位置开始获取多行数据，fetchmany(n),n为获取的行数；

  3. 从当前位置开始获取所有的数据，fetchall()；

  • fetchone

    游标提供一个fetchone方法，能够从当前位置获取一行数据。

    该方法没有参数，只需要通过 游标变量.fetchone() 的方式调用。

    fetchone 执行结束后，会将获取到的数据返回为一个元组。

    该元组会存储这一行的每一个值，可以把它存储到变量data中。

    1
    2
    data = cur.fetchone()
    print(data)

    当 fetchone 执行结束后，游标会自动向下移动。这样，再次执行fetchone时，获取到的就是新的数据。

  • fetchmany

    若想从游标的当前位置获取指定行数的数据，可以使用fetchmany方法。

    fatchmany接收一个整数作为参数，用来指定要返回的行数；

    通过 游标名.fetchmany(要读取的行数) 的方式调用。

    同样的fetchmany会将读取到的数据返回为元组，但因为获取到的是多行数据。所以会以嵌套元组的形式存储。可以将这部分结果存储到变量中。

    1
    2
    data = cur.fetchmany(4)
    print(data)

  • fetchall

    若想从游标的当前位置开始获取所有的数据，可以使用fetchall方法。

    fetchall 不需要设定参数，它会控制游标移动直到所有数据读取结束。

    通过 游标名.fetchall() 的方式调用。

    大部分情况下 fetchall 会读取多行数据，这些数据也会以嵌套元组的形式存储。

    例如brand 表中有21条数据，若直接执行fetchall，就会获得存储了21条数据的嵌套元组。可以将这部分结果存储到变量中。

    1
    2
    data = cur.fetchall()
    print(data)

    在实际应用中，通常会将查询结果中的数据全部输出，即 fetchall 的是最常用的方法。

    因为结果存储在嵌套元组，在获取数据时需要先指定行所在的索引，再指定某一值所在的索引。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    data = ((200001, '香奶奶'),
            (200002, '雅诗绿黛'),
            (200003, '绿蔻'),
            (200004, '百世'),
            (200005, '三只仓鼠')
           )
    # 查询第4行
    row4 = data[3]
    print(row4)
    # 查询第4行的第2列
    row4c2 = data[3][1]
    print(row4c2)