Python 数据库操作基础指南

随着互联网的不断发展，数据成为了重要的资产，对于企业来说，数据是难以替代的重要财富，而在 Python 中，与数据库进行交互是其中一个重要的功能。Python 作为一种高级编程语言，自然而然具备了与数据库交互的能力。通过 Python，我们可以访问和操作多种数据库类型，包括但不限于 MySQL、SQLite、PostgreSQL 等等，Python 的 DB-API 还可以让你在某种程度上“常用”各类数据库，这样的能力在数据多元化的今天非常重要。

一、安装 SQLAlchemy

在开始用 Python 操作数据库之前，我们需要通过 pip 安装一个 Python 的 ORM 库 SQLAlchemy。

    pip install SQLAlchemy

ORM 是 Object Relationship Mapping 的简称，也就是对象关系映射。ORM 把数据库的表结构映射到对象上，操作数据库表就变成了操作类和对象。

二、连接数据库

Python 和数据库连接的方式有许多，我们在这里介绍两种最常用的方法，分别是 SQLite 和 MySQL 的连接。

1. SQLite 连接

SQLite 是一款轻型的关系型数据库，Python 所有版本默认都已安装了这款数据库。以下是一个 Python 使用 SQLite 的示例代码：

    import sqlite3
 
    # 连接到 SQLite 数据库，文件名是 test.db，如果文件不存在，会自动在当前目录创建:
    conn = sqlite3.connect('test.db')
 
    # 创建一个 Cursor（游标）:
    cursor = conn.cursor()
 
    # 执行一条 SQL 语句，创建 user 表:
    cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')
 
    # 继续执行一条 SQL 语句，插入一条记录:
    cursor.execute('insert into user (id, name) values ('1', 'Michael')')
 
    # 通过 rowcount 获得插入的行数:
    print(cursor.rowcount)
 
    # 关闭 Cursor（游标）:
    cursor.close()
 
    # 提交事务:
    conn.commit()
 
    # 关闭 Connection（连接）:
    conn.close()

2. MySQL 连接

MySQL 是最流行的关系型数据库之一，提供了多种编程语言的 API，这里以 Python 的 PyMySQL 和 MySQL Connector 为例：

在使用 PyMySQL 之前，需要先安装这个模块，使用如下命令安装：

    pip install PyMySQL

PyMySQL 示例代码如下：

    import pymysql
 
    # 打开数据库连接
    db = pymysql.connect("localhost","testuser","test123","TESTDB" )
 
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
 
    # 使用 execute()  方法执行 SQL 查询 
    cursor.execute("SELECT * FROM EMPLOYEE")
 
    # 使用 fetchone() 方法获取单条数据.
    data = cursor.fetchone()
 
    print ("Database version : %s " % data)
 
    # 关闭数据库连接
    db.close()

MySQL Connector 使用：

    import mysql.connector
 
    # 打开数据库连接
    conn = mysql.connector.connect(user='root', password='yourpassword', database='test')
 
    # 创建游标对象
    cursor = conn.cursor()
 
    # 执行 SQL 查询
    cursor.execute('SELECT * FROM test')
 
    # 获取所有结果集
    result = cursor.fetchall()
 
    # 关闭游标和连接
    cursor.close()
    conn.close()

三、操作数据库

1. 插入数据

插入数据是数据库操作的基本操作之一，下面是一段示例代码：

    import pymysql
 
    # 打开数据库连接
    db = pymysql.connect("localhost","testuser","test123","TESTDB" )
 
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
 
    # SQL 插入语句
    sql = "INSERT INTO EMPLOYEE(FIRST_NAME, 
            LAST_NAME, AGE, SEX, INCOME) 
            VALUES ('%s', '%s', '%s', '%s', '%s')" % 
            ('Mac', 'Mohan', 20, 'M', 2000)
    try:
        # 执行 sql 语句
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
    except:
        # 如果发生错误则回滚
        db.rollback()
 
    # 关闭数据库连接
    db.close()

2. 查询数据

查询数据也是数据库操作中常见的操作之一，以下是 Python 操作 MySQL 数据库查询语句的示例代码：

    import mysql.connector
     
    # 打开数据库连接
    conn = mysql.connector.connect(user='root', password='yourpassword', database='test')
     
    # 创建游标对象
    cursor = conn.cursor()
     
    # 执行 SQL 查询
    cursor.execute('SELECT * FROM test')
     
    # 获取所有结果集
    results = cursor.fetchall()
     
    # 循环遍历结果集并打印
    for row in results:
        print(row)
     
    # 关闭游标和连接
    cursor.close()
    conn.close()

3. 更新数据

更新数据也是数据库操作中常见的操作之一，以下是 Python 操作 MySQL 更新数据的示例代码：

    import mysql.connector
     
    # 打开数据库连接
    conn = mysql.connector.connect(user='root', password='yourpassword', database='test')
     
    # 创建游标对象
    cursor = conn.cursor()
     
    # SQL 更新语句
    sql = "UPDATE employee SET age = age + 1 WHERE SEX = '%c'" % ('M')
     
    try:
        # 执行 SQL 语句
        cursor.execute(sql)
        # 提交到数据库执行
        conn.commit()
    except:
        # 发生错误时回滚
        conn.rollback()
     
    # 关闭游标和连接
    cursor.close()
    conn.close()

4. 删除数据

删除数据也是数据库操作中常见的操作之一，以下是 Python 操作 MySQL 删除数据的示例代码：

    import mysql.connector
     
    # 打开数据库连接
    conn = mysql.connector.connect(user='root', password='yourpassword', database='test')
     
    # 创建游标对象
    cursor = conn.cursor()
     
    # SQL 删除语句
    sql = "DELETE FROM employee WHERE AGE > '%d'" % (20)
     
    try:
        # 执行 SQL 语句
        cursor.execute(sql)
        # 提交修改
        conn.commit()
    except:
        # 发生错误时回滚
        conn.rollback()
     
    # 关闭游标和连接
    cursor.close()
    conn.close()

总结：

在 Python 中操作数据库是很常见的任务，Python 提供了多种操作数据库的方法，我们可以选择最合适的方法来完成我们的任务。SQLite、MySQL 或 PostgreSQL 这些数据库系统都可以通过 Python 轻松连接，提供了各种方便的操作方式，同时 ORM 也是连接和操作数据库的一个有效手段。无论你是要开发 Web、移动应用还是数据分析，Python 都能提供完整的工具链以及巨大的优势，操作数据库是其中一个非常重要的技能。