Python与数据库交互全解析：SQLite和MySQL的无缝整合


参考资源链接：[传智播客&黑马程序员PYTHON教程课件汇总](https://wenku.csdn.net/doc/6412b749be7fbd1778d49c25?spm=1055.2635.3001.10343)
# 1. Python与数据库交互的基础知识

## 简介
Python作为一种高级编程语言，以其简洁、易读性而广受欢迎。与数据库的交互是其在数据处理领域的应用之一。掌握Python与数据库交互的基本知识，能够为数据操作提供强大的工具箱，无论是数据查询、数据更新，还是复杂的事务处理。

## 基本概念
要开始使用Python操作数据库，首先需要理解数据库、驱动程序和API的概念。数据库是存储数据的系统，驱动程序作为数据库与Python之间的桥梁，而API（应用程序接口）定义了应用程序如何与数据库进行交互。

## 数据库交互流程
一般来说，Python与数据库交互的流程可以分为以下几个步骤：
1. 导入适当的数据库驱动模块（如`sqlite3`或`mysql.connector`）。
2. 创建数据库连接，并根据需要创建游标对象。
3. 执行SQL语句进行数据查询或修改。
4. 处理查询结果或捕获并处理异常。
5. 关闭游标和连接以释放资源。

import sqlite3

# 连接到SQLite数据库
# 数据库文件是test.db，如果文件不存在，会自动在当前目录创建:
conn = sqlite3.connect('test.db')

# 创建一个Cursor:
cursor = conn.cursor()

# 执行一条SQL语句，创建user表:
cursor.execute('CREATE TABLE user (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)')

# 继续执行一条SQL语句，插入一条记录:
cursor.execute('INSERT INTO user (name, age) VALUES (\'Alice\', 21)')

# 关闭Cursor:
cursor.close()
# 提交事务:
conn.commit()
# 关闭Connection:
conn.close()

以上代码展示了如何使用Python的`sqlite3`模块与SQLite数据库进行基本的交互。通过这些步骤，您可以开始构建自己的数据库应用并将其应用于各种数据处理场景中。

# 2. SQLite数据库简介及Python操作

### 2.1 SQLite基础

#### 2.1.1 SQLite的安装和配置

SQLite是一个轻量级的数据库，它不需要单独的服务器进程，而是将数据库存储在一个单一的文件中。这对于轻量级应用或需要快速搭建原型的应用来说非常方便。

安装SQLite很简单。大多数Linux发行版和macOS都预装了SQLite。如果在Windows上，可以从SQLite的官方网站下载预编译的二进制文件或通过包管理器安装。

例如，在Ubuntu上，你可以使用以下命令安装SQLite：

sudo apt-get update
sudo apt-get install sqlite3 libsqlite3-dev

在Python中，你可以使用`sqlite3`模块来操作SQLite数据库。这个模块是Python标准库的一部分，不需要额外安装。

#### 2.1.2 SQLite的数据类型和表的操作

SQLite的数据类型非常灵活。它使用的是"动态类型"系统，允许在创建表时不必显式指定列的数据类型。

创建表的SQL语句如下：

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL,
    password TEXT NOT NULL,
    email TEXT NOT NULL
);

在上面的SQL语句中，我们定义了一个名为`users`的表，其中包含四个字段。`id`字段设置为自动增长的主键。

要对表进行操作（如插入、查询、更新和删除数据），你可以使用如下SQL语句：

插入数据：

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

查询数据：

SELECT * FROM users WHERE username = 'Alice';

更新数据：

UPDATE users SET password = 'newpassword' WHERE username = 'Alice';

删除数据：

DELETE FROM users WHERE id = 1;

接下来，我们将深入了解如何使用Python操作SQLite数据库。

### 2.2 Python中操作SQLite

#### 2.2.1 使用sqlite3模块进行数据库操作

Python的`sqlite3`模块允许你直接与SQLite数据库进行交云。以下是如何使用`sqlite3`模块进行基本数据库操作的示例：

连接到数据库：

import sqlite3

# 连接到SQLite数据库
# 数据库文件是 test.db，如果文件不存在，会自动生成
conn = sqlite3.connect('test.db')

创建一个表：

cursor = conn.cursor()

# 创建 table
cursor.execute('''
CREATE TABLE IF NOT EXISTS stock (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL
)
''')

# 提交事务
conn.commit()

插入数据：

def insert_stock(name, price):
    cursor.execute("INSERT INTO stock (name, price) VALUES (?, ?)", (name, price))
    conn.commit()

# 调用函数插入股票数据
insert_stock('Google', 500.2)
insert_stock('Apple', 600.5)

查询数据：

def query_stocks():
    cursor.execute("SELECT * FROM stock")
    for row in cursor.fetchall():
        print(row)

# 调用函数查询所有股票数据
query_stocks()

删除数据：

def delete_stock(stock_id):
    cursor.execute("DELETE FROM stock WHERE id = ?", (stock_id,))
    conn.commit()

# 调用函数删除ID为1的股票数据
delete_stock(1)

#### 2.2.2 SQL语句的执行和异常处理

执行SQL语句可能会引发异常，因此需要进行适当的错误处理。以下是使用try-except块进行异常处理的示例：

try:
    cursor.execute("SELECT * FROM stock WHERE price > ?", (550,))
    for row in cursor.fetchall():
        print(row)
except sqlite3.Error as error:
    print("Error while connecting to sqlite", error)
finally:
    cursor.close()

#### 2.2.3 Python中高级的SQLite操作

在Python中，`sqlite3`模块还支持一些高级操作，比如设置事务的隔离级别、使用`row_factory`来改变返回行的格式等。

设置事务的隔离级别：

conn.isolation_level = None  # None表示关闭自动事务管理

使用`row_factory`改变返回的行类型：

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

# 设置行工厂函数
conn.row_factory = dict_factory

# 执行查询
cursor.execute("SELECT * FROM stock WHERE name = ?", ('Google',))
stock = cursor.fetchone()
print(stock['name'], stock['price'])

在本章节中，我们介绍了SQLite的基础知识，并通过实际Python代码示例展示了如何在Python中操作SQLite数据库。下一章节，我们将探索Python如何与功能更强大的MySQL数据库进行交互。

# 3. MySQL数据库简介及Python操作

## 3.1 MySQL基础

### 3.1.1 MySQL的安装和配置

在开始使用MySQL数据库之前，首先需要完成安装和配置。MySQL是一个流行的开源关系型数据库管理系统，它使用结构化查询语言（SQL）来管理数据。以下是安装和配置MySQL数据库的基本步骤：

1. **下载MySQL安装包**：前往MySQL官方网站或者使用包管理工具下载适用于你操作系统的MySQL安装包。

2. **安装MySQL服务**：
- Windows环境下，直接运行安装程序，并按照向导进行安装。
- Linux环境下，通常可以使用包管理器安装，例如在Ubuntu中可以使用命令 `sudo apt-get install mysql-server`。

3. **配置MySQL**：
- 安装后，启动MySQL服务。
- 对于Linux系统，服务启动命令可能是 `sudo systemctl start mysqld`。
- 配置文件通常位于 `/etc/mysql/my.cnf` 或者 `/etc/my.cnf`。

4. **安全配置**：
- 运行 `mysql_secure_installation` 命令来设置root密码，删除匿名用户，限制root用户远程登录等安全设