Python数据库操作

数据处理能力 / Python
字数统计: 3.2k   |   阅读时长≈ 13 分钟

数据库是数据分析、数据清洗重要的数据来源，也是数据共享平台。因此学会如何从数据库获取数据、如何将数据存放到数据库还是蛮重要的。
企业中的数据库各种各样，但是其原理都大同小异，因此我们这里主要介绍Python读写MySQL数据库的方法，不涉及过多数据库的 SQL 语句相关内容。

mysql数据库管理系统是由服务（server）与 客户端（client）程序组成的，数据库里的数据由server管理维护，我们对数据的任何访问都需要经过服务程序处理。
客户端程序则是我们与MySQL服务程序交流的桥梁，客户端程序有两大类：带交互界面的和不带交互界面的。
之前大家上课使用的workbench属于有界面的MySQL客户端程序，而我们今天通过python代码编写的程序属于无界面的客户端程序。

这两类程序本质上是一样的，只是使用场景有一些区别。

MySQL客户端连接服务器，有一些基本要求：

  • 服务端程序是启动的
  • 有服务端软件的IP地址、端口号、拥有一些权限的账号密码

除了MySQL之外还有很多种数据库，这些数据库跟MySQL类似，一般也得需要这些信息才能连接访问。
客户端与数据库服务程序之间的通信需要一定的协议、约定，这个已经有人给写好了，因此我们只需安装相应的库就可以使用了。
各个编程语言都有连接数据库的开发库：如Java里的jdbc ，在 Python 3 里面主要使用的是 mysql-connector-python 、 pymysql 和 sqlalchemy 这三个库。

1、mysql-connector-python

mysql-connector-python 是 MySQL 官方提供的 Python 数据库连接器。它允许 Python 应用程序连接到 MySQL 数据库服务器并执行 SQL 查询。该连接器符合 Python 数据库 API 规范（也被称为 DB-API），因此它提供了标准的数据库操作方法，如连接、查询、执行命令等。

以下是使用 mysql-connector-python 的一些基本步骤：

1.1、安装

首先，你需要使用 pip 安装 mysql-connector-python：

1
# pip install mysql-connector-python

1.2、连接到数据库

接下来，你可以使用以下代码连接到 MySQL 数据库：

1
2
3
4
5
6
7
8
9
10
import mysql.connector

cnx = mysql.connector.connect(
    host="localhost",  # 数据库服务器地址  
    user="yourusername",  # 数据库用户名  
    password="yourpassword",  # 数据库密码  
    database="yourdatabase"  # 要连接的数据库名  
)

cursor = cnx.cursor()  # 创建一个游标对象用于执行查询

1.3、执行查询

使用游标对象执行 SQL 查询：

1
2
3
4
5
6
7
# 执行一个简单的查询  
query = "SELECT * FROM your_table"
cursor.execute(query)

# 获取查询结果  
for (id, name, age) in cursor:
    print(f"ID: {id}, Name: {name}, Age: {age}")

1.4、执行命令

如果你需要执行修改数据的命令（如 INSERT, UPDATE, DELETE），可以这样做：

1
2
3
4
5
6
7
# 执行 INSERT 命令  
insert_query = "INSERT INTO your_table (name, age) VALUES (%s, %s)"
val = ("John Doe", 30)
cursor.execute(insert_query, val)

# 提交更改到数据库  
cnx.commit()

1.5、关闭连接

完成数据库操作后，记得关闭游标和连接：

1
2
cursor.close()  # 关闭游标  
cnx.close()  # 关闭连接

注意事项

确保你的 MySQL 服务器正在运行，并且可以从你的 Python 应用程序所在的位置访问。
替换上面代码中的 yourusername, yourpassword, yourdatabase, your_table 等占位符为实际的值。
在执行修改数据的命令（如 INSERT, UPDATE, DELETE）后，记得调用 cnx.commit() 来提交更改，否则更改不会被保存到数据库中。
异常处理也很重要，应该考虑在代码中加入 try...except 块来处理可能出现的数据库错误。

mysql-connector-python 还提供了许多高级功能，如连接池、SSL 支持、事务处理等，可以根据具体需求查阅官方文档来了解更多信息。

2、pymysql

pymysql 是一个纯 Python 实现的 MySQL 客户端库，用于连接和操作 MySQL 数据库。它基于 Python 的 DB-API 2.0 规范，因此与标准的 Python 数据库接口兼容，使得开发者可以轻松地与 MySQL 数据库进行交互。

pymysql 的使用方式与 mysql-connector-python 类似，但它是作为第三方库开发的，而不是 MySQL 官方提供的。由于它是纯 Python 实现的，所以安装和使用相对简单，并且不需要额外的依赖。

以下是使用 pymysql 进行数据库操作的基本步骤：

2.1、安装

首先，你需要使用 pip 安装 pymysql：

1
# pip install pymysql

2.2、连接到数据库

创建一个连接对象，并指定数据库连接参数：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
import pymysql

# 创建连接  
connection = pymysql.connect(
    host='localhost',  # 数据库服务器地址  
    user='yourusername',  # 数据库用户名  
    password='yourpassword',  # 数据库密码  
    database='yourdatabase',  # 要连接的数据库名  
    charset='utf8mb4',  # 字符集，根据需要设置  
    cursorclass=pymysql.cursors.DictCursor  # 返回字典而不是元组  
)

try:
    with connection.cursor() as cursor:
        # 执行 SQL 查询  
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
        cursor.execute(sql, ('webmaster@python.org',))
        result = cursor.fetchone()
        print(result)
        # 使用with语句，文件会在这里自动关闭，无需显式调用 cursor.close()
finally:
    connection.close()

2.3、执行查询和命令

使用游标对象执行 SQL 查询或命令：

1
2
3
4
5
6
7
8
9
10
11
12
13
with connection.cursor() as cursor:
    # 执行查询  
    sql = "SELECT * FROM `some_table`"
    cursor.execute(sql)
    results = cursor.fetchall()  # 获取所有结果  
    for row in results:
        print(row)

        # 执行命令（如 INSERT, UPDATE, DELETE）  
    sql = "INSERT INTO `some_table` (`column1`, `column2`) VALUES (%s, %s)"
    val = ('value1', 'value2')
    cursor.execute(sql, val)
    connection.commit()  # 提交更改

注意事项

确保你的 MySQL 服务器正在运行，并且可以从你的 Python 应用程序所在的位置访问。
替换上面代码中的 yourusername, yourpassword, yourdatabase, some_table, column1, column2 等占位符为实际的值。
使用 with 语句可以确保游标和连接在操作完成后被正确关闭，即使在发生异常时也是如此。
在执行修改数据的命令后，记得调用 connection.commit() 来提交更改，否则更改不会被保存到数据库中。
pymysql 还支持其他高级功能，如事务处理、批量插入等，具体可以查阅官方文档或相关教程。

pymysql 由于其纯 Python 实现和相对简单的 API，在 Python 社区中广受欢迎，并经常用于构建 Web 应用程序和其他需要与 MySQL 数据库交互的项目。

2.4、示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
# 创建连接对象
mydb = pymysql.connect(
    host="localhost",  # 数据库主机地址
    user="root",  # 数据库用户名
    passwd="1234",  # 数据库密码
    port=3306  # 端口号      
)

# 创建游标对象
mycursor = mydb.cursor(cursor=pymysql.cursors.DictCursor)
# 使用数据库
mycursor.execute("""
use manage;
""")

# 查询
mycursor.execute('''
select * from schedule limit 10
''')

# 接收数据
df = pd.DataFrame(mycursor)
print(df)

# 关闭游标
mycursor.close()
# 关闭数据库连接
mydb.close()

pd.read_sql("select * from schedule where item = '睡觉'", con=mydb)  # 使用Dataframe对象操作数据库时，最好使用SQLAlchemy库！
---------------------------------------------------------------------------

NameError                                 Traceback (most recent call last)

Cell In[1], line 2
      1 # 创建连接对象
----> 2 mydb = pymysql.connect(
      3     host="localhost",  # 数据库主机地址
      4     user="root",  # 数据库用户名
      5     passwd="1234",  # 数据库密码
      6     port=3306  # 端口号      
      7 )
      9 # 创建游标对象
     10 mycursor = mydb.cursor(cursor=pymysql.cursors.DictCursor)


NameError: name 'pymysql' is not defined

这句话的意思是：“其他DBAPI2对象没有被测试。请考虑使用SQLAlchemy。”

解释如下：

DBAPI2：这是Python的数据库API的规范版本2。它定义了一组通用的接口，使得Python程序员可以更容易地与各种数据库进行交互。
对象：在这里，对象指的是与数据库交互时可能使用的各种类和方法。
未测试：意味着这些DBAPI2对象没有经过充分的测试或验证，可能存在未知的问题或不稳定的行为。
SQLAlchemy：是一个流行的Python ORM（对象关系映射）工具，它提供了对数据库操作的高级抽象，使得开发者可以更简洁、更安全地与数据库进行交互。

整句话的建议是，如果你正在考虑使用那些没有被充分测试的DBAPI2对象进行数据库操作，那么最好考虑使用SQLAlchemy这样的更高级、更稳定的库。
SQLAlchemy经过了广泛的测试和使用，提供了更多的功能和更好的安全性，因此通常是一个更好的选择。

3、sqlalchemy

SQLAlchemy 是一个流行的 Python SQL 工具包和对象关系映射（ORM）系统，它为应用程序开发者提供了一套全面的企业级持久性模型。
它支持多种数据库后端，并且提供了灵活的方式来构建和管理 SQL 语句和对象关系。
SQLAlchemy 的核心功能

对象关系映射（ORM）：
    将数据库表映射到 Python 类。
    将表中的行映射到类的实例。
    将表中的列映射到类的属性。
SQL 表达式语言：
    允许开发者以 Pythonic 的方式构建 SQL 查询。
    提供了一套完整的 SQL 构造器，可以构建复杂的查询。
事务和会话管理：
    管理数据库事务的开始、提交和回滚。
    提供会话对象来管理对象的生命周期。
关系：
    支持一对一、一对多、多对多等关系。
    提供关系加载策略，如连接加载、子查询加载等。
数据库引擎：
    提供了与多种数据库（如 MySQL、PostgreSQL、SQLite 等）的接口。
    允许开发者配置连接池和其他数据库特定的设置。
扩展：
    提供了大量的扩展来支持更多高级功能，如水平分片、异步操作、全文搜索等。

1
# pip install sqlalchemy

3.1、MySQL数据读取

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
from sqlalchemy import create_engine
import pandas as pd

MYSQL_HOST = 'localhost'
MYSQL_PORT = '3306'
MYSQL_USER = 'root'
MYSQL_PASSWORD = '1234'
MYSQL_DB = 'manage'

engine = create_engine('mysql+pymysql://%s:%s@%s:%s/%s?charset=utf8'
                       % (MYSQL_USER, MYSQL_PASSWORD, MYSQL_HOST, MYSQL_PORT, MYSQL_DB))

sql = "SELECT * FROM schedule where item = '睡觉' limit 10"

df = pd.read_sql(sql, engine)

# pd.set_option('display.unicode.ambiguous_as_wide', True) #设置列名对齐
# pd.set_option('display.unicode.east_asian_width', True) #设置列名对齐
print(df)
         dt      time item  duration
0  20220401   1:00:00   睡觉       0.5
1  20220401   1:30:00   睡觉       0.5
2  20220401  13:00:00   睡觉       0.5
3  20220401  13:30:00   睡觉       0.5
4  20220401   2:00:00   睡觉       0.5
5  20220401   2:30:00   睡觉       0.5
6  20220401   3:00:00   睡觉       0.5
7  20220401   3:30:00   睡觉       0.5
8  20220401   4:00:00   睡觉       0.5
9  20220401   4:30:00   睡觉       0.5

3.2 MySQL数据写入

1
df.to_sql('ceshi', con=engine, if_exists='replace', index=False)
10

4、MySQL常用操作函数封装

注意：python3以上版本可以直接使用sqlalchemy库来进行Dataframe操作。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
# driver = {'host': 'localhost', 'port': 3306, 'user': '', 'pwd': '', 'db': ''}


def insert_mysql_data(sql, data, driver):
    """
    用于批量插入MySQL数据
    :param sql:MySQL数据插入语句
    :param data:列表数据[[a1,b1],[a2,b2]]
    :param driver:数据库引擎
    :return:无返回值
    """
    ## 连接MySQL数据库
    host, port, user, pwd, db = driver['host'], driver['port'], driver['user'], driver['pwd'], driver['db']
    db = pymysql.connect(host=host, port=port, user=user,
                         password=pwd, database=db)
    cursor = db.cursor()
    ## 批量写入数据
    cursor.executemany(sql, data)
    db.commit()
    print("MySQL数据插入成功！")
    ## 关闭游标和数据库
    cursor.close()
    db.close()


def update_mysql_data(sql, driver):
    """
    用于更新MySQL数据
    :param sql:MySQL数据更新语句
    :param driver:MySQL连接参数
    :return:无返回值
    """
    ## 连接MySQL数据库
    host, port, user, pwd, db = driver['host'], driver['port'], driver['user'], driver['pwd'], driver['db']
    db = pymysql.connect(host=host, port=port, user=user,
                         password=pwd, database=db)
    cursor = db.cursor()
    ## 更新MySQL数据
    cursor.execute(sql)
    db.commit()
    print("MySQL数据更新成功！")
    ## 关闭游标和数据库
    cursor.close()
    db.close()


def get_mysql_data(sql, driver):
    """
    用于获取MySQL数据
    :param sql:MySQL数据查询语句
    :param driver:MySQL连接参数
    :return:查询数据结果，字典类型
    """
    ## 连接MySQL数据库
    host, port, user, pwd, db = driver['host'], driver['port'], driver['user'], driver['pwd'], driver['db']
    db = pymysql.connect(host=host, port=port, user=user,
                         password=pwd, database=db)
    cursor = db.cursor()
    ## 创建游标对象
    cursor.execute(sql)
    results = cursor.fetchall()
    # 获取表结构  cursor.description
    fields = [field[0] for field in cursor.description]
    # 序列化成字典：zip把两迭代对象合并成2维元组,然后用dict转化为字典。
    data = [dict(zip(fields, result)) for result in results]
    ## 关闭游标和数据库
    cursor.close()
    db.close()
    return data
打赏
  • 版权声明： 本博客所有文章除特别声明外，著作权归作者所有。转载请注明出处！

扫一扫，分享到微信

微信分享二维码
  • Copyrights © 2022-2024 归一
  • 访问人数: | 浏览次数:

请我喝杯咖啡吧~

支付宝
微信