Python高级编程与实践:Python数据库编程进阶

Python进阶编程：数据库编程实战

学习目标

通过本课程的学习，学员将掌握Python与数据库交互的基本方法，了解如何使用sqlite3和psycopg2库连接数据库，执行SQL查询，并处理查询结果。本课程将通过实际案例，帮助学员加深对数据库编程的理解和应用。

相关知识点

数据库编程实战

学习内容

1 数据库编程实战

1.1 使用sqlite3库进行数据库操作

在Python中，sqlite3库提供了与SQLite数据库交互的接口。SQLite是一个轻量级的数据库，非常适合用于小型应用或测试环境。本课程将介绍如何使用sqlite3库创建数据库、表，插入数据，查询数据，以及处理查询结果。

1.1.1 创建数据库和表

首先，我们需要导入sqlite3库，并创建一个数据库连接。如果数据库文件不存在，sqlite3.connect()方法会自动创建一个新的数据库文件。

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))')

# 关闭Cursor:
cursor.close()

# 提交事务:
conn.commit()

# 关闭Connection:
conn.close()

1.1.2 插入数据

接下来，我们可以在创建的表中插入数据。使用cursor.execute()方法执行SQL语句，插入数据后需要调用conn.commit()来提交事务。

# 重新连接数据库
conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# 插入一条记录:
cursor.execute('insert into user (id, name) values (\'1\', \'Michael\')')

# 获取插入的行数
print(cursor.rowcount)

# 关闭Cursor:
cursor.close()

# 提交事务:
conn.commit()

# 关闭Connection:
conn.close()

1.1.3 查询数据

查询数据是数据库操作中最常见的任务之一。使用cursor.execute()执行查询语句，然后通过cursor.fetchall()获取查询结果。

# 重新连接数据库
conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# 执行查询语句:
cursor.execute('select * from user where id=?', ('1',))

# 获取查询结果:
values = cursor.fetchall()

# 打印查询结果
print(values)

# 关闭Cursor:
cursor.close()

# 关闭Connection:
conn.close()

1.2 使用psycopg2库连接PostgreSQL数据库

psycopg2是Python中用于连接PostgreSQL数据库的库。PostgreSQL是一个功能强大的开源关系型数据库系统，支持复杂的查询、外键、触发器、视图等高级功能。本课程将介绍如何使用psycopg2库连接PostgreSQL数据库，执行SQL查询，并处理查询结果。

1.2.1 安装psycopg2

在使用psycopg2之前，需要先安装该库。可以使用pip命令进行安装：

%pip install psycopg2-binary

1.2.2 连接数据库

使用psycopg2.connect()方法连接到PostgreSQL数据库。需要提供数据库的连接信息，如数据库名、用户名、密码等。

import psycopg2

# 连接到PostgreSQL数据库
conn = psycopg2.connect(database="testdb", user="postgres", password="secret", host="127.0.0.1", port="5432")

# 创建一个Cursor:
cursor = conn.cursor()

# 执行一条SQL语句，创建表:
cursor.execute('CREATE TABLE student (id SERIAL PRIMARY KEY, name VARCHAR(255), age INT)')

# 提交事务:
conn.commit()

# 关闭Cursor:
cursor.close()

# 关闭Connection:
conn.close()

1.2.3 插入数据

在PostgreSQL中插入数据与SQLite类似，使用cursor.execute()方法执行SQL语句，插入数据后需要调用conn.commit()来提交事务。

# 重新连接数据库
conn = psycopg2.connect(database="testdb", user="postgres", password="secret", host="127.0.0.1", port="5432")
cursor = conn.cursor()

# 插入一条记录:
cursor.execute('INSERT INTO student (name, age) VALUES (%s, %s)', ('John Doe', 25))

# 获取插入的行数
print(cursor.rowcount)

# 提交事务:
conn.commit()

# 关闭Cursor:
cursor.close()

# 关闭Connection:
conn.close()

1.2.4 查询数据

查询数据时，使用cursor.execute()执行查询语句，然后通过cursor.fetchall()获取查询结果。

# 重新连接数据库
conn = psycopg2.connect(database="testdb", user="postgres", password="secret", host="127.0.0.1", port="5432")
cursor = conn.cursor()

# 执行查询语句:
cursor.execute('SELECT * FROM student WHERE age > %s', (20,))

# 获取查询结果:
values = cursor.fetchall()

# 打印查询结果
print(values)

# 关闭Cursor:
cursor.close()

# 关闭Connection:
conn.close()

1.3 SQL查询的执行与结果处理

在数据库编程中，SQL查询的执行和结果处理是非常重要的环节。本节将详细介绍如何编写高效的SQL查询，以及如何处理查询结果。

1.3.1 编写高效的SQL查询

编写高效的SQL查询可以显著提高数据库的性能。以下是一些编写高效SQL查询的技巧：

• 使用索引：索引可以加快查询速度，特别是在大数据量的情况下。在经常用于查询的列上创建索引。
• 避免使用SELECT *：只选择需要的列，减少数据传输量。
• 使用合适的连接类型：根据查询需求选择合适的连接类型，如内连接、外连接等。
• 优化子查询：子查询可能会导致性能问题，尽量使用连接或临时表来替代。

1.3.2 处理查询结果

处理查询结果时，需要注意以下几点：

• 使用fetchone：如果只需要获取一条记录，使用fetchone()方法。
• 使用fetchall：如果需要获取所有记录，使用fetchall()方法。
• 使用fetchmany：如果数据量较大，可以使用fetchmany(size)方法分批获取数据，避免一次性加载过多数据导致内存溢出。

# 重新连接数据库
conn = psycopg2.connect(database="testdb", user="postgres", password="secret", host="127.0.0.1", port="5432")
cursor = conn.cursor()

# 执行查询语句:
cursor.execute('SELECT * FROM student WHERE age > %s', (20,))

# 获取一条记录
one_record = cursor.fetchone()
print(one_record)

# 获取所有记录
all_records = cursor.fetchall()
print(all_records)

# 分批获取记录
batch_size = 10
batch_records = cursor.fetchmany(batch_size)
print(batch_records)

# 关闭Cursor:
cursor.close()

# 关闭Connection:
conn.close()