Python MariaDB 教程：连接、CRUD 操作、事务与错误处理

1. 引言

对于那些想用 Python 处理 MariaDB 的人

许多人想使用 Python 连接到 MariaDB 并操作数据。在本文中，我们将从基础到更高级主题解释使用 Python 处理 MariaDB 的一切。

您是否有这些问题？

• 您不知道如何从 Python 连接到 MariaDB
• 您想使用 Python 轻松地在 MariaDB 中创建、读取、更新和删除数据
• 您想了解常见错误及其修复方法

本文将教您什么

• 如何轻松从 Python 连接到 MariaDB
• 如何实现 CRUD（创建、读取、更新、删除）
• 常见错误及其解决方案
• 如何使用事务保护数据完整性

阅读本文后，您将能够使用 Python 流畅地处理 MariaDB。现在，让我们深入细节。

2. 环境设置（安装 MariaDB 和 Python）

什么是 MariaDB？

MariaDB 是一个开源的关系数据库管理系统（RDBMS），被广泛用作 MySQL 的继任者。在保持与 MySQL 高度兼容的同时，MariaDB 提升了性能并添加了新功能。

所需工具

要在 Python 中处理 MariaDB，您需要准备以下工具：

• Python（推荐版本 3.x 或更高）
• MariaDB（服务器）
• MariaDB Python 连接器（mariadb 库）

如何安装 MariaDB（按操作系统）

对于 Windows

1. 从 官方网站 下载 MariaDB 安装程序。
2. 运行安装程序并按照说明完成安装。
3. 安装后，通过运行以下命令验证 MariaDB 是否正常工作：

   mysql -u root -p
   

4. 如果您能成功登录，则安装完成。

对于 Mac

在 Mac 上，您可以使用 Homebrew 轻松安装 MariaDB。

1. 如果 Homebrew 未安装，请使用以下命令安装它：

   /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
   

2. 安装 MariaDB：

   brew install mariadb
   

3. 启动 MariaDB：

   brew services start mariadb
   

4. 验证安装：

   mysql -u root -p
   

对于 Linux (Ubuntu)

1. 更新软件包列表并安装 MariaDB：

   sudo apt update
   sudo apt install mariadb-server mariadb-client
   

2. 启动 MariaDB：

   sudo systemctl start mariadb
   

3. 启用 MariaDB（开机自动启动）：

   sudo systemctl enable mariadb
   

4. 运行安全安装脚本：

   sudo mysql_secure_installation
   

这允许您设置 root 密码并移除不必要的默认设置。

安装 Python 和所需库

要将 MariaDB 与 Python 集成，您需要安装 mariadb 库。

1. 使用 pip 安装 mariadb 库：

   pip install mariadb
   

2. 验证安装：

   import mariadb
   print("The MariaDB library was installed successfully.")
   

如果没有错误发生，则安装成功。

现在，您使用 Python 处理 MariaDB 的环境设置已完成。接下来，我们将解释如何从 Python 连接到 MariaDB。

3. 连接到 MariaDB

如何使用 Python 连接到 MariaDB

导入所需库

要连接到 MariaDB，请导入 Python 的 mariadb 库。

import mariadb

连接到数据库的基本代码

以下代码展示了使用 Python 连接到 MariaDB 的基本方法。

import mariadb

# Database connection settings
config = {
    "host": "localhost",   # Hostname of the MariaDB server
    "user": "root",        # MariaDB username
    "password": "password", # MariaDB password
    "database": "sample_db" # Database name to connect to
}

try:
    # Connect to MariaDB
    conn = mariadb.connect(**config)
    print("Connected to MariaDB!")

    # Create a cursor
    cursor = conn.cursor()

    # If the connection succeeds, close it
    cursor.close()
    conn.close()
except mariadb.Error as e:
    print(f"Connection error: {e}")

About the arguments of connect()

If the server is remote, specify an IP address or domain name in host.

A secure connection method using environment variables

Hardcoding sensitive information such as passwords directly in your code is a security risk. By using environment variables, you can manage them more safely.

Install python-dotenv

First, install a library to manage environment variables.

pip install python-dotenv

Create a .env file and write your connection settings

Create a .env file in your project folder and add your connection settings like this:

DB_HOST=localhost
DB_USER=root
DB_PASSWORD=password
DB_NAME=sample_db

Load environment variables in your Python script

To load the .env file in a Python script, write the following:

import mariadb
import os
from dotenv import load_dotenv

# Load the .env file
load_dotenv()

config = {
    "host": os.getenv("DB_HOST"),
    "user": os.getenv("DB_USER"),
    "password": os.getenv("DB_PASSWORD"),
    "database": os.getenv("DB_NAME")
}

try:
    conn = mariadb.connect(**config)
    print("Connected securely to MariaDB!")
    conn.close()
except mariadb.Error as e:
    print(f"Connection error: {e}")

Common connection errors and how to fix them

Below are some errors you may encounter when connecting Python to MariaDB, along with their solutions.

Access denied for user 'root'@'localhost'

Error message

mariadb.OperationalError: Access denied for user 'root'@'localhost' (using password: YES)

Causes

• The user’s credentials are incorrect
• MariaDB authentication settings are not configured properly

Solutions

1. Log in to MariaDB and check the user’s privileges.

   mysql -u root -p
   

2. Grant the required privileges to the user.

   GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
   FLUSH PRIVILEGES;
   

3. Try connecting again.

Can't connect to MySQL server on 'localhost'

Error message

mariadb.OperationalError: Can't connect to MySQL server on 'localhost' (10061)

Causes

• The MariaDB server is not running
• The host setting is incorrect

Solutions

1. Check whether the server is running.

   sudo systemctl status mariadb
   

2. If the server is stopped, start it.

   sudo systemctl start mariadb
   

3. Check that the host setting is correct.

Summary

In this section, we explained how to connect to MariaDB from Python.

• Basic connection method using mariadb.connect()
• A secure connection method using a .env file
• Common connection errors and how to fix them

4. Creating Databases and Tables

Creating a database

In MariaDB, you need to create a database to store data. Let’s look at how to create a database using Python.

Create a database using a MariaDB management tool

You can create a database using the MariaDB command line (or GUI tools such as MySQL Workbench).

CREATE DATABASE sample_db;

To view the list of databases you created, use the following command:

SHOW DATABASES;

Create a database using Python

To create a database using Python, run the following code:

import mariadb

# Database connection (no database specified)
config = {
    "host": "localhost",
    "user": "root",
    "password": "password"
}

try:
    conn = mariadb.connect(**config)
    cursor = conn.cursor()

    # Create the database
    cursor.execute("CREATE DATABASE IF NOT EXISTS sample_db")

    print("Created database 'sample_db'.")

    cursor.close()
    conn.close()
except mariadb.Error as e:
    print(f"Error occurred: {e}")

By adding IF NOT EXISTS, you can prevent errors if a database with the same name already exists.

Creating a table

After creating the database, the next step is to create a table to store data.

Basic table structure

A table typically has a structure like the following:

Create a table with SQL

If you create a table using MariaDB SQL commands, write the following:

USE sample_db;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

To view the list of tables, run:

SHOW TABLES;

Create a table using Python

Here is how to create the same table using a Python script.

import mariadb

# Database connection
config = {
    "host": "localhost",
    "user": "root",
    "password": "password",
    "database": "sample_db"
}

try:
    conn = mariadb.connect(**config)
    cursor = conn.cursor()

    # Table creation SQL
    table_creation_query = """
    CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
    """

    cursor.execute(table_creation_query)
    print("Created table 'users'.")

    cursor.close()
    conn.close()
except mariadb.Error as e:
    print(f"Error occurred: {e}")

By adding IF NOT EXISTS, you can prevent errors if the table already exists.

How to choose data types

When designing tables, it is important to choose appropriate data types. Below is a summary of commonly used data types in MariaDB.

For example, it is common to use VARCHAR(100) for the name field, but if you need a longer string, TEXT can be a good option.

How to check and drop existing tables

Check existing tables

To check the tables in a database, use the following SQL:

SHOW TABLES;

To view a table’s detailed structure, run the DESCRIBE command:

DESCRIBE users;

Drop a table

To drop a table, use DROP TABLE:

DROP TABLE users;

To drop a table from Python, run the following code:

cursor.execute("DROP TABLE IF EXISTS users")

Summary

In this section, we explained how to create databases and tables in MariaDB to store data.

• How to create a database in MariaDB
• How to create a table using Python
• How to choose appropriate data types
• How to check and drop existing tables

Now the basic setup for MariaDB is complete. In the next section, we will explain CRUD operations (Create, Read, Update, Delete) in detail.

5. CRUD Operations (Create, Read, Update, Delete)

After creating the MariaDB database and tables, the next step is to perform CRUD operations. CRUD stands for Create, Read, Update, and Delete, which are the basic operations of a database.

In this section, we will explain how to manipulate MariaDB data using Python.

Inserting data (INSERT)

To add a new record to the database, use the INSERT statement.

Insert data using SQL

To insert data using MariaDB SQL, execute the following command:

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

To verify the inserted data, use the SELECT statement:

SELECT * FROM users;

Insert data using Python

To insert data using Python, run the following code:

import mariadb

# Database connection settings
config = {
    "host": "localhost",
    "user": "root",
    "password": "password",
    "database": "sample_db"
}

try:
    conn = mariadb.connect(**config)
    cursor = conn.cursor()

    # Insert data
    insert_query = "INSERT INTO users (name, email) VALUES (?, ?)"
    data = ("Alice", "alice@example.com")
    cursor.execute(insert_query, data)

    # Save changes
    conn.commit()
    print("Data inserted successfully!")

    cursor.close()
    conn.close()
except mariadb.Error as e:
    print(f"Error occurred: {e}")

Key points:

• Using ? placeholders helps prevent SQL injection.
• If you do not execute conn.commit() , the data will not be saved to the database.

Retrieving data (SELECT)

To retrieve registered data, use the SELECT statement.

Retrieve data using SQL

To retrieve data using MariaDB SQL:

SELECT * FROM users;

To retrieve data with conditions, use the WHERE clause:

SELECT * FROM users WHERE email = 'alice@example.com';

Retrieve data using Python

To retrieve data using Python, run the following code:

import mariadb

config = {
    "host": "localhost",
    "user": "root",
    "password": "password",
    "database": "sample_db"
}

try:
    conn = mariadb.connect(**config)
    cursor = conn.cursor()

    # Retrieve data
    select_query = "SELECT id, name, email FROM users"
    cursor.execute(select_query)

    # Display retrieved data
    for (id, name, email) in cursor:
        print(f"ID: {id}, Name: {name}, Email: {email}")

    cursor.close()
    conn.close()
except mariadb.Error as e:
    print(f"Error occurred: {e}")

Key points:

• Execute SQL with cursor.execute(select_query) and retrieve data from the cursor object.
• You can process records one by one using a for loop.

Updating data (UPDATE)

To modify registered data, use the UPDATE statement.

Update data using SQL

To update data using MariaDB SQL, execute:

UPDATE users SET name = 'Alice Smith' WHERE email = 'alice@example.com';

Update data using Python

To update data using Python, run the following code:

import mariadb

config = {
    "host": "localhost",
    "user": "root",
    "password": "password",
    "database": "sample_db"
}

try:
    conn = mariadb.connect(**config)
    cursor = conn.cursor()

    # Update data
    update_query = "UPDATE users SET name = ? WHERE email = ?"
    data = ("Alice Smith", "alice@example.com")
    cursor.execute(update_query, data)

    # Save changes
    conn.commit()
    print("Data updated successfully!")

    cursor.close()
    conn.close()
except mariadb.Error as e:
    print(f"Error occurred: {e}")

Deleting data (DELETE)

To remove unnecessary data, use the DELETE statement.

Delete data using SQL

To delete data using MariaDB SQL, execute:

DELETE FROM users WHERE email = 'alice@example.com';

Delete data using Python

To delete data using Python, run the following code:

import mariadb

config = {
    "host": "localhost",
    "user": "root",
    "password": "password",
    "database": "sample_db"
}

try:
    conn = mariadb.connect(**config)
    cursor = conn.cursor()

    # Delete data
    delete_query = "DELETE FROM users WHERE email = ?"
    data = ("alice@example.com",)
    cursor.execute(delete_query, data)

    # Save changes
    conn.commit()
    print("Data deleted successfully!")

    cursor.close()
    conn.close()
except mariadb.Error as e:
    print(f"Error occurred: {e}")

Summary

In this section, we explained how to manipulate MariaDB data using Python.

• Insert data (INSERT)
• Retrieve data (SELECT)
• Update data (UPDATE)
• Delete data (DELETE)

You can now perform basic data operations in MariaDB using Python.

6. Transactions and Rollback (Ensuring Data Integrity)

在处理数据库时，使用 事务 来维护 数据完整性 非常重要。
特别是，在处理过程中发生错误时，能够 将数据恢复到其原始状态（回滚） 的能力对于维护一致性至关重要。

在本节中，我们将解释如何使用 Python 管理 MariaDB 中的事务。

什么是事务？

事务是一种机制，它 将一系列数据库操作组合成一个单一单元，如果每个过程都成功，则提交所有更改；如果发生错误，则回滚所有更改。

事务的特性

• ACID 属性
• 原子性 ：所有操作要么全部成功完成，要么都不应用。
• 一致性 ：数据完整性得到维护。
• 隔离性 ：并发事务不会相互干扰。
• 持久性 ：一旦提交，更改就会永久保存。

基本事务操作 (COMMIT 和 ROLLBACK)

MariaDB 事务可以使用以下命令控制：

在 Python 中使用事务

以下是使用 Python 管理 MariaDB 事务的基本示例。

在单个事务中管理多个操作

以下代码将 多个插入操作视为一个事务，并且仅在所有操作成功时才提交。

import mariadb

config = {
    "host": "localhost",
    "user": "root",
    "password": "password",
    "database": "sample_db"
}

try:
    conn = mariadb.connect(**config)
    cursor = conn.cursor()

    # Start transaction
    conn.start_transaction()

    # Insert data
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Bob", "bob@example.com"))
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Charlie", "charlie@example.com"))

    # Commit if all operations succeed
    conn.commit()
    print("Data added successfully.")

    cursor.close()
    conn.close()
except mariadb.Error as e:
    print(f"Error occurred: {e}")
    conn.rollback()  # Roll back if an error occurs

关键点

• 使用 conn.start_transaction() 开始事务。
• 使用 conn.commit() 最终确定更改（提交后无法撤销）。
• 如果发生错误，使用 conn.rollback() 取消更改并恢复原始状态。

基于事务的错误处理

数据库操作期间可能会发生错误。
例如，重复的电子邮件地址（UNIQUE 约束违反） 或 服务器超时 可能会发生。

带错误处理的事务管理

以下代码添加了逻辑，如果处理过程中发生错误，则 回滚更改并恢复原始状态。

import mariadb

config = {
    "host": "localhost",
    "user": "root",
    "password": "password",
    "database": "sample_db"
}

try:
    conn = mariadb.connect(**config)
    cursor = conn.cursor()

    # Start transaction
    conn.start_transaction()

    try:
        # First insert succeeds
        cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Dave", "dave@example.com"))

        # Second insert causes an error (duplicate email)
        cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Eve", "dave@example.com"))

        # Commit if all operations succeed
        conn.commit()
        print("Data inserted successfully.")

    except mariadb.Error as e:
        print(f"Error occurred during data processing: {e}")
        conn.rollback()  # Roll back on error
        print("Transaction rolled back.")

    cursor.close()
    conn.close()

except mariadb.Error as e:
    print(f"Database connection error: {e}")

总结

在本节中，我们解释了 事务的基础知识以及如何在 Python 中实现它们。

• 事务的重要性以及 ACID 属性
• 如何在 Python 中使用 commit() 和 rollback()
• 结合错误处理的事务管理

7. 错误处理（常见错误及解决方案）

在使用 Python 操作 MariaDB 时，可能会出现错误。
尤其是 数据库连接错误、SQL 语法错误和数据完整性错误 很常见，因此了解它们的原因和解决方案非常重要。

本节将介绍 常见错误的原因以及如何修复。

Access denied for user（身份验证错误）

错误信息

mariadb.OperationalError: Access denied for user 'root'@'localhost' (using password: YES)

原因

• 用户名或密码不正确
• MariaDB 用户没有相应的权限

解决方案

1. 登录 MariaDB 并检查用户权限

   mysql -u root -p
   

2. 授予用户权限

   GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
   FLUSH PRIVILEGES;
   

3. 重启 MariaDB

   sudo systemctl restart mariadb
   

Can't connect to MySQL server on 'localhost'（连接错误）

错误信息

mariadb.OperationalError: Can't connect to MySQL server on 'localhost' (10061)

原因

• MariaDB 服务器未运行
• host 设置不正确

解决方案

1. 检查 MariaDB 服务器是否在运行

   sudo systemctl status mariadb
   

2. 如果服务器已停止，启动它

   sudo systemctl start mariadb
   

3. 设置服务器自动启动

   sudo systemctl enable mariadb
   

Unknown database 'sample_db'（数据库不存在）

错误信息

mariadb.ProgrammingError: Unknown database 'sample_db'

原因

• 指定的数据库不存在
• 数据库名称有拼写错误

解决方案

1. 检查数据库列表

   SHOW DATABASES;
   

2. 如果不存在则创建数据库

   CREATE DATABASE sample_db;
   

Table doesn't exist（表不存在）

错误信息

mariadb.ProgrammingError: Table 'sample_db.users' doesn't exist

原因

• 指定的表不存在
• 未使用 USE sample_db; 选择数据库

解决方案

1. 检查表列表

   SHOW TABLES;
   

2. 如果不存在则创建表

   CREATE TABLE users (
       id INT AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(100) NOT NULL,
       email VARCHAR(100) UNIQUE NOT NULL,
       created_at DATETIME DEFAULT CURRENT_TIMESTAMP
   );
   

Duplicate entry（重复数据错误）

错误信息

mariadb.IntegrityError: Duplicate entry 'alice@example.com' for key 'users.email'

原因

• email 列具有 UNIQUE 约束，不能插入重复值

解决方案

• 在插入数据前检查重复项
• 使用 ON DUPLICATE KEY UPDATE

防止重复的 Python 代码

try:
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Alice", "alice@example.com"))
    conn.commit()
except mariadb.IntegrityError:
    print("Error: The email address already exists.")

或使用 ON DUPLICATE KEY UPDATE

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')
ON DUPLICATE KEY UPDATE name = 'Alice Updated';

Incorrect number of bindings（绑定数量不匹配）

错误信息

mariadb.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 2, and there are 1 supplied.

原因

• SQL 语句所需的参数数量与提供的参数数量不匹配

解决方案

• 确保占位符的数量与参数数量相匹配

错误代码

cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Alice"))

正确代码

cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Alice", "alice@example.com"))

摘要

在本节中，我们解释了在 Python 中使用 MariaDB 时常见的错误以及如何解决它们。

• Access denied for user（身份验证错误）
• Can't connect to MySQL server（连接错误）
• Unknown database（数据库不存在）
• Table doesn't exist（表不存在）
• Duplicate entry（重复数据错误）
• Incorrect number of bindings（参数数量不匹配）

8. 结论

在本文中，我们阐述了 如何使用 Python 操作 MariaDB，从基础概念到更高级的主题。
通过了解基本的数据库操作并正确实现错误处理和事务管理，您可以执行更安全、更高效的数据操作。

文章回顾

环境设置

• 如何安装 MariaDB（Windows / Mac / Linux）
• 如何为 Python 安装 MariaDB 连接器库（mariadb）

连接到 MariaDB

• 基本的数据库连接方法
• 使用环境变量进行安全连接
• 常见连接错误的解决方案

创建数据库和表

• 创建数据库（SQL / Python）
• 创建表并选择数据类型
• 检查并删除已有表

CRUD 操作（创建、读取、更新、删除）

• 使用 Python 进行基本数据操作
• 使用占位符防止 SQL 注入
• 正确的错误处理

事务和回滚

• 事务的重要性（ACID 属性）
• 错误时如何恢复数据（回滚）
• 通过禁用 AutoCommit 手动管理事务

错误处理（常见错误及解决方案）

• 身份验证错误（Access denied for user）
• 连接错误（Can't connect to MySQL server）
• 数据库或表未找到错误
• 重复数据错误（Duplicate entry）
• 处理死锁并实现重试逻辑

将 Python 与 MariaDB 结合时的关键点

1. 使用占位符

   cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Alice", "alice@example.com"))
   

→ 使用绑定变量而不是直接在 SQL 语句中嵌入值，以 防止 SQL 注入。

2. 正确管理事务
   使用 conn.commit() 显式提交更改。
   在出现错误时使用 conn.rollback() 保持数据完整性。

3. 实现全面的错误处理
   使用 try-except 块 提前捕获潜在错误。
   为 OperationalError、IntegrityError 等错误准备具体的处理策略。

4. 考虑性能
   批量插入（一次插入多条记录）
   适当的索引（提升查询性能）

进一步学习资源

如果您想深入了解 Python 与 MariaDB 的集成，请参考以下资源：

官方文档

• MariaDB Official Documentation

相关学习内容

• SQL Basics (W3Schools)
• Database Operations with Python (Real Python)

推荐书籍

• SQL, 2nd Edition: Database Operations from Scratch – 彻底学习 SQL 基础
• Effective Python: 90 Specific Ways to Improve Your Python Programs – 学习 Python 的最佳实践

摘要

通过将 MariaDB 与 Python 集成，您可以实现 基于脚本的数据管理 和 自动化。
通过实现不仅仅是基本的 CRUD 操作，还包括适当的 错误处理和事务管理，您可以构建一个更安全、更高效的系统。

作为下一步，考虑学习 高效数据管理 和 API 集成，以将您的知识应用于实际的数据库操作。