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

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

傻啦嘿哟

2280人浏览 · 2024-09-02 09:15:00

傻啦嘿哟 · 2024-09-02 09:15:00 发布

三、Python与PostgreSQL的集成

引言

在软件开发中，数据库是存储、检索和管理数据的关键组件。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}")