Python与数据库：SQLite、MySQL、PostgreSQL详解

Python与数据库：SQLite、MySQL、PostgreSQL详解

前言

大家好，我是第一程序员（名字大，人很菜）。作为一个非科班转码、正在学习Rust和Python的萌新，最近我开始学习Python与数据库的交互。说实话，一开始我对数据库的概念还很模糊，但随着学习的深入，我发现数据库是编程中非常重要的一部分。今天我想分享一下我对Python与数据库交互的学习心得，希望能给同样是非科班转码的朋友们一些参考。

一、SQLite数据库

1.1 SQLite简介

SQLite是一个轻量级的嵌入式数据库，它不需要单独的服务器进程，数据存储在一个文件中：

• 轻量级：核心库很小，适合嵌入式应用
• 无需配置：不需要安装和配置服务器
• 跨平台：支持多种操作系统
• ACID兼容：支持原子性、一致性、隔离性、持久性

1.2 使用sqlite3模块

Python内置了sqlite3模块，可以直接与SQLite数据库交互：

import sqlite3

# 连接到SQLite数据库
# 如果数据库不存在，会自动创建
conn = sqlite3.connect('example.db')

# 创建游标
cursor = conn.cursor()

# 创建表
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER,
    email TEXT UNIQUE
)
''')

# 插入数据
cursor.execute("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", ('Alice', 25, 'alice@example.com'))
cursor.execute("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", ('Bob', 30, 'bob@example.com'))

# 提交事务
conn.commit()

# 查询数据
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

# 更新数据
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (26, 'Alice'))
conn.commit()

# 删除数据
cursor.execute("DELETE FROM users WHERE name = ?", ('Bob',))
conn.commit()

# 关闭连接
conn.close()

1.3 使用SQLite的优势

• 无需安装：Python内置支持
• 易于使用：API简单直观
• 适合小型应用：适合数据量不大的应用
• 便于测试：适合单元测试和集成测试

二、MySQL数据库

2.1 MySQL简介

MySQL是一个流行的关系型数据库管理系统：

• 功能强大：支持复杂的SQL语句和事务
• 可扩展性：适合大型应用
• 社区活跃：有庞大的社区支持
• 开源免费：开源软件，免费使用

2.2 使用pymysql库

需要安装pymysql库来与MySQL数据库交互：

# 安装pymysql
# pip install pymysql

import pymysql

# 连接到MySQL数据库
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='password',
    database='test',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

# 创建游标
cursor = conn.cursor()

# 创建表
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INT,
    email VARCHAR(255) UNIQUE
)
''')

# 插入数据
cursor.execute("INSERT INTO users (name, age, email) VALUES (%s, %s, %s)", ('Alice', 25, 'alice@example.com'))
cursor.execute("INSERT INTO users (name, age, email) VALUES (%s, %s, %s)", ('Bob', 30, 'bob@example.com'))

# 提交事务
conn.commit()

# 查询数据
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

# 更新数据
cursor.execute("UPDATE users SET age = %s WHERE name = %s", (26, 'Alice'))
conn.commit()

# 删除数据
cursor.execute("DELETE FROM users WHERE name = %s", ('Bob',))
conn.commit()

# 关闭连接
conn.close()

2.3 使用MySQL的优势

• 适合大型应用：支持高并发和大数据量
• 功能丰富：支持复杂的SQL语句和存储过程
• 生态成熟：有丰富的工具和库
• 社区支持：有庞大的社区和文档

三、PostgreSQL数据库

3.1 PostgreSQL简介

PostgreSQL是一个功能强大的开源关系型数据库：

• 高级特性：支持复杂的数据类型和查询
• 可扩展性：支持自定义数据类型和函数
• 可靠性：具有强大的事务支持和数据完整性
• 开源免费：开源软件，免费使用

3.2 使用psycopg2库

需要安装psycopg2库来与PostgreSQL数据库交互：

# 安装psycopg2
# pip install psycopg2-binary

import psycopg2

# 连接到PostgreSQL数据库
conn = psycopg2.connect(
    host='localhost',
    user='postgres',
    password='password',
    database='test',
    port=5432
)

# 创建游标
cursor = conn.cursor()

# 创建表
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INTEGER,
    email VARCHAR(255) UNIQUE
)
''')

# 插入数据
cursor.execute("INSERT INTO users (name, age, email) VALUES (%s, %s, %s)", ('Alice', 25, 'alice@example.com'))
cursor.execute("INSERT INTO users (name, age, email) VALUES (%s, %s, %s)", ('Bob', 30, 'bob@example.com'))

# 提交事务
conn.commit()

# 查询数据
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

# 更新数据
cursor.execute("UPDATE users SET age = %s WHERE name = %s", (26, 'Alice'))
conn.commit()

# 删除数据
cursor.execute("DELETE FROM users WHERE name = %s", ('Bob',))
conn.commit()

# 关闭连接
conn.close()

3.3 使用PostgreSQL的优势

• 高级特性：支持JSON、数组等复杂数据类型
• 可扩展性：支持自定义数据类型和函数
• 可靠性：具有强大的事务支持和数据完整性
• 安全性：具有强大的安全特性

四、使用ORM框架

4.1 SQLAlchemy

SQLAlchemy是一个流行的Python ORM框架：

# 安装SQLAlchemy
# pip install SQLAlchemy

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# 创建数据库引擎
# SQLite
engine = create_engine('sqlite:///example.db')
# MySQL
# engine = create_engine('mysql+pymysql://root:password@localhost/test')
# PostgreSQL
# engine = create_engine('postgresql://postgres:password@localhost/test')

# 创建基类
Base = declarative_base()

# 定义模型
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(255), nullable=False)
    age = Column(Integer)
    email = Column(String(255), unique=True)

# 创建表
Base.metadata.create_all(engine)

# 创建会话
Session = sessionmaker(bind=engine)
session = Session()

# 插入数据
user1 = User(name='Alice', age=25, email='alice@example.com')
user2 = User(name='Bob', age=30, email='bob@example.com')
session.add(user1)
session.add(user2)
session.commit()

# 查询数据
users = session.query(User).all()
for user in users:
    print(user.id, user.name, user.age, user.email)

# 更新数据
user = session.query(User).filter_by(name='Alice').first()
user.age = 26
session.commit()

# 删除数据
user = session.query(User).filter_by(name='Bob').first()
session.delete(user)
session.commit()

# 关闭会话
session.close()

4.2 Django ORM

Django ORM是Django框架内置的ORM：

# models.py
from django.db import models

class User(models.Model):
    name = models.CharField(max_length=255)
    age = models.IntegerField()
    email = models.EmailField(unique=True)

    def __str__(self):
        return self.name

# 使用Django ORM
# 创建用户
user = User.objects.create(name='Alice', age=25, email='alice@example.com')

# 查询用户
users = User.objects.all()
for user in users:
    print(user)

# 更新用户
user = User.objects.get(name='Alice')
user.age = 26
user.save()

# 删除用户
user = User.objects.get(name='Alice')
user.delete()

五、数据库连接池

5.1 使用连接池

对于高并发应用，使用连接池可以提高性能：

# 安装SQLAlchemy
# pip install SQLAlchemy

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.pool import QueuePool

# 创建带有连接池的引擎
engine = create_engine(
    'mysql+pymysql://root:password@localhost/test',
    poolclass=QueuePool,
    pool_size=10,
    max_overflow=20,
    pool_pre_ping=True
)

# 创建会话
Session = sessionmaker(bind=engine)
session = Session()

# 使用会话
# ...

# 关闭会话
session.close()

六、Python与Rust的对比

作为一个同时学习Python和Rust的转码者，我发现对比学习是一种很好的方法：

6.1 数据库交互对比

• Python：有丰富的数据库驱动和ORM框架
• Rust：有diesel、sqlx等ORM框架
• 开发效率：Python开发效率高，Rust开发效率相对较低
• 性能：Rust性能优异，Python性能相对较低

6.2 学习心得

• Python的优势：开发效率高，生态丰富
• Rust的优势：性能优异，内存安全
• 相互借鉴：从Python学习快速开发，从Rust学习性能优化

七、实践项目推荐

7.1 数据库项目

• 用户管理系统：实现用户的增删改查
• 博客系统：实现文章的发布和管理
• 电商系统：实现商品和订单的管理
• 数据分析系统：实现数据的存储和分析

八、学习方法和技巧

8.1 学习方法

• 循序渐进：先学习基础的数据库操作，再学习高级特性
• 项目实践：通过实际项目来巩固知识
• 文档阅读：仔细阅读数据库和ORM框架的官方文档
• 社区交流：加入社区，向他人学习

8.2 常见问题和解决方法

• 连接问题：检查数据库服务是否运行，连接参数是否正确
• 性能问题：使用索引，优化SQL语句，使用连接池
• 安全问题：使用参数化查询，避免SQL注入
• 数据一致性：使用事务，确保数据的一致性

九、总结

Python与数据库的交互是编程中非常重要的一部分。作为一个非科班转码者，我深刻体会到学习数据库的重要性。

我的学习过程并不是一帆风顺的，遇到了很多困难和挫折，但通过不断地实践和学习，我逐渐掌握了Python与数据库交互的各种技巧。

保持学习，保持输出。虽然现在我还是个菜鸡，但我相信只要坚持，总有一天能成为真正的「第一程序员」！