Python操作数据库全面指南：从基础到高级实践

Python操作数据库全面指南：从基础到高级实践

Python作为一门强大的编程语言，提供了多种操作数据库的方式。本文将详细介绍Python中操作数据库的各种方法，包括SQLite、MySQL、PostgreSQL等关系型数据库，以及MongoDB等NoSQL数据库，涵盖常用模块和类，并提供详细的案例代码。

一、Python操作数据库概述

Python通过多种数据库适配器（DB-API）和ORM框架与数据库交互，主要方式包括：

直接使用数据库适配器（如sqlite3、psycopg2、PyMySQL）

使用ORM框架（如SQLAlchemy、Django ORM）

使用高级封装库（如dataset、records）

二、SQLite数据库操作

SQLite是Python内置的轻量级数据库，适合小型应用和原型开发。

1. 基本操作

import sqlite3
from sqlite3 import Error

def create_connection(db_file):
    """创建数据库连接"""
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        print(f"成功连接到SQLite数据库，版本: {
     
     sqlite3.version}")
        return conn
    except Error as e:
        print(e)
    
    return conn

def create_table(conn, create_table_sql):
    """创建表"""
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
    except Error as e:
        print(e)

def main():
    database = "pythonsqlite.db"
    
    # 创建项目表SQL
    sql_create_projects_table = """CREATE TABLE IF NOT EXISTS projects (
                                    id integer PRIMARY KEY,
                                    name text NOT NULL,
                                    begin_date text,
                                    end_date text
                                );"""
    
    # 创建任务表SQL
    sql_create_tasks_table = """CREATE TABLE IF NOT EXISTS tasks (
                                id integer PRIMARY KEY,
                                name text NOT NULL,
                                priority integer,
                                project_id integer NOT NULL,
                                status_id integer NOT NULL,
                                begin_date text NOT NULL,
                                end_date text NOT NULL,
                                FOREIGN KEY (project_id) REFERENCES projects (id)
                            );"""
    
    # 创建数据库连接
    conn = create_connection(database)
    
    if conn is not None:
        # 创建项目表
        create_table(conn, sql_create_projects_table)
        # 创建任务表
        create_table(conn, sql_create_tasks_table)
    else:
        print("无法创建数据库连接")
    
    conn.close()

if __name__ == '__main__':
    main()

2. CRUD操作

def create_project(conn, project):
    """创建新项目"""
    sql = '''INSERT INTO projects(name, begin_date, end_date)
             VALUES(?,?,?)'''
    cur = conn.cursor()
    cur.execute(sql, project)
    conn.commit()
    return cur.lastrowid

def create_task(conn, task):
    """创建新任务"""
    sql = '''INSERT INTO tasks(name, priority, project_id, status_id, begin_date, end_date)
             VALUES(?,?,?,?,?,?)'''
    cur = conn.cursor()
    cur.execute(sql, task)
    conn.commit()
    return cur.lastrowid

def select_all_projects(conn):
    """查询所有项目"""
    cur = conn.cursor()
    cur.execute("SELECT * FROM projects")
    rows = cur.fetchall()
    return rows

def select_task_by_priority(conn, priority):
    """按优先级查询任务"""
    cur = conn.cursor()
    cur.execute("SELECT * FROM tasks WHERE priority=?", (priority,))
    rows = cur.fetchall()
    return rows

def update_task(conn, task):
    """更新任务状态"""
    sql = '''UPDATE tasks
             SET status_id = ?
             WHERE id = ?'''
    cur = conn.cursor()
    cur.execute(sql, task)
    conn.commit()

def delete_task(conn, id):
    """删除任务"""
    sql = 'DELETE FROM tasks WHERE id=?'
    cur = conn.cursor()
    cur.execute(sql, (id,))
    conn.commit()

def test_crud_operations():
    database = "pythonsqlite.db"
    conn = create_connection(database)
    
    with conn:
        # 创建项目
        project = ('Python数据库教程', '2023-01-01', '2023-12-31')
        project_id = create_project(conn, project)
        
        # 创建任务
        task_1 = ('学习SQLite', 1, project_id, 1, '2023-01-02', '2023-01-05')
        task_2 = ('学习MySQL', 2, project_id, 1, '2023-01-06', '2023-01-10')
        create_task(conn, task_1)
        create_task(conn, task_2)
        
        # 查询数据
        print("所有项目:")
        print(select_all_projects(conn))
        
        print("\n优先级为1的任务:")
        print(select_task_by_priority(conn, 1))
        
        # 更新任务
        update_task(conn, (2, 1))
        
        # 删除任务
        delete_task(conn, 2)
        
        print("\n更新和删除后的任务:")
        print(select_task_by_priority(conn, 1))

if __name__ == '__main__':
    test_crud_operations()

三、MySQL数据库操作

MySQL是最流行的开源关系型数据库之一，Python通过PyMySQL或mysql-connector-python连接MySQL。

1. 安装PyMySQL

bash
pip install pymysql

2. 基本操作

import pymysql
from pymysql.cursors import DictCursor

class MySQLDatabase:
    def __init__(self, host, user, password, database):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.connection = None
    
    def connect(self):
        """连接到MySQL数据库"""
        try:
            self.connection = pymysql.connect(
                host=self.host,
                user=self.user,
                password=self.password,
                database=self.database,
                charset='utf8mb4',
                cursorclass=DictCursor
            )
            print("成功连接到MySQL数据库")
        except pymysql.MySQLError as e:
            print(f"连接错误: {
     
     e}")
    
    def close(self):
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()
            print