为什么需要将数据存入数据库？

数据库是存储结构化数据的核心工具，相比文件存储具有以下优势：

• 数据持久化存储
• 高效查询能力
• 数据一致性保证
• 多用户并发访问
• 数据安全性

Python操作数据库的基本步骤

1. 导入数据库模块
2. 建立数据库连接
3. 创建游标对象
4. 执行SQL语句
5. 提交事务
6. 关闭连接

SQLite数据库操作

import sqlite3

# 连接数据库（不存在则创建）
conn = sqlite3.connect('mydatabase.db')

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

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

# 提交更改
conn.commit()

# 插入单条数据
new_user = ('张三', 'zhangsan@example.com', 28)
cursor.execute("INSERT INTO users (name, email, age) VALUES (?, ?, ?)", new_user)

# 提交更改
conn.commit()
print(f"插入成功，最后插入的行ID: {cursor.lastrowid}")

# 批量插入数据
users = [
    ('李四', 'lisi@example.com', 32),
    ('王五', 'wangwu@example.com', 25),
    ('赵六', 'zhaoliu@example.com', 40)
]

cursor.executemany("INSERT INTO users (name, email, age) VALUES (?, ?, ?)", users)
conn.commit()
print(f"批量插入了 {len(users)} 条记录")

MySQL数据库操作

需要先安装MySQL驱动：pip install mysql-connector-python

import mysql.connector
from mysql.connector import Error

try:
    # 建立数据库连接
    conn = mysql.connector.connect(
        host='localhost',
        user='your_username',
        password='your_password',
        database='your_database'
    )
    
    if conn.is_connected():
        cursor = conn.cursor()
        
        # 插入数据
        insert_query = """
        INSERT INTO employees (name, department, salary)
        VALUES (%s, %s, %s)
        """
        
        employee_data = ('张明', '技术部', 8500)
        cursor.execute(insert_query, employee_data)
        conn.commit()
        print("数据插入成功！")
        
except Error as e:
    print(f"数据库错误: {e}")
    
finally:
    if conn and conn.is_connected():
        cursor.close()
        conn.close()

PostgreSQL数据库操作

需要先安装PostgreSQL驱动：pip install psycopg2

import psycopg2
from psycopg2 import OperationalError

try:
    # 建立数据库连接
    conn = psycopg2.connect(
        host="localhost",
        database="your_database",
        user="your_username",
        password="your_password",
        port="5432"
    )
    
    cursor = conn.cursor()
    
    # 插入数据
    insert_query = """
    INSERT INTO products (product_name, price, category)
    VALUES (%s, %s, %s)
    RETURNING product_id;
    """
    
    product_data = ('Python编程书籍', 99.9, '图书')
    cursor.execute(insert_query, product_data)
    
    # 获取返回的ID
    product_id = cursor.fetchone()[0]
    conn.commit()
    print(f"产品插入成功！产品ID: {product_id}")
    
except OperationalError as e:
    print(f"数据库连接错误: {e}")
    
finally:
    if conn:
        cursor.close()
        conn.close()

数据库操作最佳实践