数据库操作与集成：使用Python与SQLite、MySQL、PostgreSQL等数据库

目录

引言

一、Python与SQLite的集成

1.1 SQLite简介

1.2 连接SQLite数据库

1.3 创建表

1.4 插入数据

1.5 查询数据

1.6 更新和删除数据

二、Python与MySQL的集成

2.1 MySQL简介

2.2 安装与配置

2.3 连接MySQL数据库

2.4 创建表与插入数据

2.5 查询、更新与删除数据

2.6 使用with语句管理连接

三、Python与PostgreSQL的集成

3.1 PostgreSQL简介

3.2 安装与配置

3.3 连接PostgreSQL数据库

3.4 创建表与插入数据

四、事务处理

五、总结

---

引言

在软件开发中，数据库是存储、检索和管理数据的关键组件。Python作为一种广泛使用的高级编程语言，通过其丰富的库和模块支持，能够轻松地与多种数据库进行集成，实现数据的读写操作。本文将详细介绍如何使用Python与SQLite、MySQL、PostgreSQL等数据库进行集成，并提供丰富的代码示例和案例，以帮助新手朋友快速上手。

一、Python与SQLite的集成

1.1 SQLite简介

SQLite是一个轻量级的数据库引擎，它完全包含在单个磁盘文件中，不需要一个独立的服务器进程或操作系统级别的安装。SQLite因其简单性、可靠性和高性能，在小型应用和嵌入式系统中非常流行。Python内置了sqlite3模块，可以非常方便地与SQLite数据库进行交互。

1.2 连接SQLite数据库

首先，我们需要使用sqlite3模块连接到SQLite数据库。如果数据库文件不存在，sqlite3将自动创建它。

import sqlite3  
  
# 连接到SQLite数据库  
# 如果文件不存在，会自动在当前目录创建:  
conn = sqlite3.connect('example.db')  
  
# 创建一个Cursor:  
cursor = conn.cursor()  
  
# 关闭连接  
# cursor.close()  
# conn.close()

1.3 创建表

接下来，我们创建一个表来存储数据。

# 创建一个表  
cursor.execute('''CREATE TABLE IF NOT EXISTS stocks  
               (date text, trans text, symbol text, qty real, price real)''')  
  
# 提交事务:  
conn.commit()

1.4 插入数据

向表中插入数据，可以通过execute方法执行SQL语句来实现。

# 插入一行记录  
cursor.execute("INSERT INTO stocks VALUES ('2023-01-01','BUY','RHAT',100,35.14)")  
  
# 插入多行记录  
cursor.executemany("INSERT INTO stocks VALUES (?,?,?,?,?)",  
                   [("2023-01-02", "SELL", "IBM", 1000, 45.00),  
                    ("2023-01-03", "BUY", "MSFT", 100, 72.00),  
                    ("2023-01-04", "SELL", "IBM", 500, 55.00)])  
  
# 提交事务:  
conn.commit()

1.5 查询数据

使用SQL查询语句可以检索数据库中的数据。

# 查询所有记录  
cursor.execute("SELECT * FROM stocks")  
print(cursor.fetchall())  
  
# 查询特定记录  
cursor.execute("SELECT * FROM stocks WHERE symbol='RHAT'")  
print(cursor.fetchall())

1.6 更新和删除数据

更新和删除数据同样使用execute方法执行SQL语句。

# 更新数据  
cursor.execute("UPDATE stocks SET price = 35.14 WHERE symbol = 'RHAT'")  
conn.commit()  
  
# 删除数据  
cursor.execute("DELETE FROM stocks WHERE symbol = 'IBM'")  
conn.commit()

二、Python与MySQL的集成

2.1 MySQL简介

MySQL是一种关系型数据库管理系统，广泛应用于各种应用系统中。与SQLite不同，MySQL需要安装在服务器上，并可以通过网络进行访问。

2.2 安装与配置

在Python中操作MySQL数据库，首先需要安装pymysql模块。

pip install pymysql

2.3 连接MySQL数据库

使用pymysql模块连接到MySQL数据库。

import pymysql  
  
# 连接数据库  
conn = pymysql.connect(host='localhost',  
                       user='user',  
                       password='passwd',  
                       database='mydb',  
                       charset='utf8mb4',  
                       cursorclass=pymysql.cursors.DictCursor)  
  
# 创建一个游标对象  
cursor = conn.cursor()  
  
# 关闭连接  
# cursor.close()  
# conn.close()

2.4 创建表与插入数据

与SQLite类似，我们可以通过执行SQL语句来创建表和插入数据。

# 创建表  
cursor.execute("CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), email VARCHAR(255))")  
conn.commit()  
  
# 插入数据  
cursor.execute("INSERT INTO users (name, email) VALUES (%s, %s)", ('John Doe', 'john.doe@example.com'))  
conn.commit()

2.5 查询、更新与删除数据

查询、更新和删除数据的操作也通过执行SQL语句来实现。

# 查询数据  
cursor.execute("SELECT * FROM users WHERE name = %s", ('John Doe',))  
results = cursor.fetchall()  
for row in results:  
    print(row)  
  
# 更新数据  
cursor.execute("UPDATE users SET email = %s WHERE name = %s", ('john.doe@newemail.com', 'John Doe'))  
conn.commit()  
  
# 删除数据  
cursor.execute("DELETE FROM users WHERE name = %s", ('John Doe',))  
conn.commit()

2.6 使用with语句管理连接

为了避免忘记关闭游标和连接，可以使用Python的with语句来自动管理资源。

with pymysql.connect(host='localhost',  
                     user='user',  
                     password='passwd',  
                     database='mydb',  
                     charset='utf8mb4',  
                     cursorclass=pymysql.cursors.DictCursor) as conn:  
    with conn.cursor() as cursor:  
        # SQL 查询  
        sql = "SELECT * FROM users"  
        cursor.execute(sql)  
          
        # 使用 fetchone() 方法获取单条数据.  
        result = cursor.fetchone()  
        print(result)

三、Python与PostgreSQL的集成

3.1 PostgreSQL简介

PostgreSQL是一种功能强大的开源对象-关系数据库系统，支持复杂的查询、事务完整性、多版本并发控制等高级功能。

3.2 安装与配置

在Python中操作PostgreSQL数据库，需要安装psycopg2模块。

pip install psycopg2

或者，如果使用的是Windows系统，可能需要安装psycopg2-binary：

pip install psycopg2-binary

3.3 连接PostgreSQL数据库

使用psycopg2模块连接到PostgreSQL数据库。

import psycopg2  
  
# 连接数据库  
conn = psycopg2.connect(database="testdb", user="postgres", password="password", host="127.0.0.1", port="5432")  
  
# 创建一个游标对象  
cursor = conn.cursor()  
  
# 关闭连接  
# cursor.close()  
# conn.close()

3.4 创建表与插入数据

# 创建表  
cursor.execute('''  
CREATE TABLE IF NOT EXISTS employees (  
    id serial PRIMARY KEY,  
    name varchar(100),  
    position varchar(100),  
    salary decimal(10, 2)  
)  
''')  
conn.commit()  
  
# 插入数据  
cursor.execute("INSERT INTO employees (name, position, salary) VALUES (%s, %s, %s)", ('John Doe', 'Developer', 75000))  
conn.commit()

3.5 查询、更新与删除数据

# 查询数据  
cursor.execute("SELECT * FROM employees")  
rows = cursor.fetchall()  
for row in rows:  
    print(row)  
  
# 更新数据  
cursor.execute("UPDATE employees SET salary = %s WHERE name = %s", (80000, 'John Doe'))  
conn.commit()  
  
# 删除数据  
cursor.execute("DELETE FROM employees WHERE name = %s", ('John Doe',))  
conn.commit()

四、事务处理

在数据库操作中，事务是确保数据一致性和完整性的重要手段。事务具有四个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability），简称ACID。

在Python中，无论是操作SQLite、MySQL还是PostgreSQL，都可以通过类似的方式来处理事务。

# 开始事务  
conn.begin()  
  
try:  
    # 执行数据库操作...  
    cursor.execute("...")  
    # 提交事务  
    conn.commit()  
except Exception as e:  
    # 出现异常时回滚事务  
    conn.rollback()  
    print(f"An error occurred: {e}")

五、总结

通过本文的介绍，我们了解了如何使用Python与SQLite、MySQL、PostgreSQL等数据库进行集成，实现数据的读写操作。