Python教程-Python 与数据库交互的 15 个 SQL 查询技巧！

当你刚开始学习 Python 与数据库交互时，可能会觉得有点不知所所措。但别担心，通过本文，你将学会如何使用 Python 进行基本的 SQL 查询，并掌握一些高级技巧。让我们一步步来，从简单的查询到更复杂的操作。

1. 连接到数据库

首先，你需要连接到数据库。这里以 SQLite 为例，因为它是轻量级且易于上手的。

import sqlite3

# 连接到 SQLite 数据库（如果不存在则会创建）

conn = sqlite3.connect('example.db')

# 创建一个游标对象

cursor = conn.cursor()

2. 创建表

在开始查询之前，我们需要一个表。假设我们要创建一个存储用户信息的表。

# 创建表

cursor.execute('''

CREATE TABLE IF NOT EXISTS users (

id INTEGER PRIMARY KEY,

name TEXT NOT NULL,

age INTEGER NOT NULL,

email TEXT UNIQUE NOT NULL

)

''')

3. 插入数据

接下来，我们可以向表中插入一些数据。

# 插入数据

cursor.execute('''

INSERT INTO users (name, age, email) VALUES (?, ?, ?)

''', ('Alice', 30, 'alice@example.com'))

cursor.execute('''

INSERT INTO users (name, age, email) VALUES (?, ?, ?)

''', ('Bob', 25, 'bob@example.com'))

# 提交事务

conn.commit()

4. 查询所有记录

现在，让我们查询表中的所有记录。

# 查询所有记录

cursor.execute('SELECT * FROM users')

rows = cursor.fetchall()

for row in rows:

print(row)

5. 查询特定记录

你可以通过条件来查询特定的记录。

# 查询特定记录

cursor.execute('SELECT * FROM users WHERE age > 25')

rows = cursor.fetchall()

for row in rows:

print(row)

6. 使用参数化查询

为了避免 SQL 注入攻击，建议使用参数化查询。

# 参数化查询

age_threshold = 25

cursor.execute('SELECT * FROM users WHERE age > ?', (age_threshold,))

rows = cursor.fetchall()

for row in rows:

print(row)

7. 更新记录

你可以更新表中的记录。

# 更新记录

cursor.execute('UPDATE users SET age = ? WHERE name = ?', (31, 'Alice'))

conn.commit()

8. 删除记录

你也可以删除表中的记录。

# 删除记录

cursor.execute('DELETE FROM users WHERE name = ?', ('Bob',))

conn.commit()

9. 使用 JOIN 查询

如果你有多个表，可以使用 JOIN 来查询相关数据。

# 假设有一个 orders 表

cursor.execute('''

CREATE TABLE IF NOT EXISTS orders (

id INTEGER PRIMARY KEY,

user_id INTEGER,

product TEXT,

FOREIGN KEY (user_id) REFERENCES users (id)

)

''')

# 插入订单数据

cursor.execute('INSERT INTO orders (user_id, product) VALUES (?, ?)', (1, 'Laptop'))

conn.commit()

# 使用 JOIN 查询

cursor.execute('''

SELECT users.name, orders.product

FROM users

JOIN orders ON users.id = orders.user_id

''')

rows = cursor.fetchall()

for row in rows:

print(row)

10. 使用聚合函数

聚合函数可以帮助你处理和汇总数据。

# 使用聚合函数

cursor.execute('SELECT COUNT(*) FROM users')

count = cursor.fetchone()[0]

print(f'Total number of users: {count}')

11. 分组和排序

你可以使用 GROUP BY 和 ORDER BY 来分组和排序数据。

# 分组和排序

cursor.execute('''

SELECT age, COUNT(*)

FROM users

GROUP BY age

ORDER BY age DESC

''')

rows = cursor.fetchall()

for row in rows:

print(row)

12. 使用子查询

子查询可以在查询中嵌套另一个查询。

# 使用子查询

cursor.execute('''

SELECT *

FROM users

WHERE id IN (SELECT user_id FROM orders)

''')

rows = cursor.fetchall()

for row in rows:

print(row)

13. 使用事务管理

事务管理可以帮助你确保数据的一致性和完整性。

# 事务管理

try:

cursor.execute('INSERT INTO users (name, age, email) VALUES (?, ?, ?)', ('Charlie', 28, 'charlie@example.com'))

cursor.execute('INSERT INTO orders (user_id, product) VALUES (?, ?)', (3, 'Phone'))

conn.commit()

except Exception as e:

conn.rollback()

print(f'Error: {e}')

14. 使用上下文管理器

上下文管理器可以自动管理资源，如关闭数据库连接。

# 使用上下文管理器

with sqlite3.connect('example.db') as conn:

cursor = conn.cursor()

cursor.execute('SELECT * FROM users')

rows = cursor.fetchall()

for row in rows:

print(row)

15. 使用 ORM 框架

对于更复杂的项目，可以考虑使用 ORM 框架，如 SQLAlchemy。

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import sessionmaker, relationship

Base = declarative_base()

class User(Base):

__tablename__ = 'users'

id = Column(Integer, primary_key=True)

name = Column(String, nullable=False)

age = Column(Integer, nullable=False)

email = Column(String, unique=True, nullable=False)

class Order(Base):

__tablename__ = 'orders'

id = Column(Integer, primary_key=True)

user_id = Column(Integer, ForeignKey('users.id'))

product = Column(String, nullable=False)

user = relationship("User")

# 创建数据库引擎

engine = create_engine('sqlite:///example.db')

# 创建表

Base.metadata.create_all(engine)

# 创建会话

Session = sessionmaker(bind=engine)

session = Session()

# 插入数据

new_user = User(name='David', age=27, email='david@example.com')

session.add(new_user)

session.commit()

# 查询数据

users = session.query(User).all()

for user in users:

print(user.name, user.age, user.email)

实战案例：用户管理系统

假设你要开发一个简单的用户管理系统，需要实现以下功能：

1. 添加用户：允许管理员添加新用户。2. 查询用户：允许管理员按条件查询用户。3. 更新用户信息：允许管理员更新用户的年龄和邮箱。4. 删除用户：允许管理员删除用户。

def add_user(name, age, email):

with sqlite3.connect('example.db') as conn:

cursor = conn.cursor()

cursor.execute('INSERT INTO users (name, age, email) VALUES (?, ?, ?)', (name, age, email))

conn.commit()

def query_users(age_threshold):

with sqlite3.connect('example.db') as conn:

cursor = conn.cursor()

cursor.execute('SELECT * FROM users WHERE age > ?', (age_threshold,))

rows = cursor.fetchall()

return rows

def update_user(user_id, new_age, new_email):

with sqlite3.connect('example.db') as conn:

cursor = conn.cursor()

cursor.execute('UPDATE users SET age = ?, email = ? WHERE id = ?', (new_age, new_email, user_id))

conn.commit()

def delete_user(user_id):

with sqlite3.connect('example.db') as conn:

cursor = conn.cursor()

cursor.execute('DELETE FROM users WHERE id = ?', (user_id,))

conn.commit()

# 示例操作

add_user('Eve', 32, 'eve@example.com')

print(query_users(30))

update_user(1, 33, 'alice_new@example.com')

delete_user(2)

总结

通过本文，你学会了如何使用 Python 进行基本的 SQL 查询，包括连接数据库、创建表、插入数据、查询记录、更新和删除记录等。此外，你还了解了如何使用参数化查询、JOIN 查询、聚合函数、分组和排序、子查询、事务管理和 ORM 框架。最后，我们通过一个实战案例展示了如何将这些知识应用于实际项目中。希望这些内容对你有所帮助！

更多相关技术内容咨询欢迎前往并持续关注好学星城论坛了解详情。

想高效系统的学习Python编程语言，推荐大家关注一个微信公众号：Python编程学习圈。每天分享行业资讯、技术干货供大家阅读，关注即可免费领取整套Python入门到进阶的学习资料以及教程，感兴趣的小伙伴赶紧行动起来吧。

发表于 2024-10-30 09:51
阅读 ( 29 )
分类：Python开发

Python教程-Python 与数据库交互的 15 个 SQL 查询技巧！

你可能感兴趣的文章

相关问题

0 条评论

作家榜 »