Python

Python-数据存储(SQLite)

2024-09-10  本文已影响0人  阿凡提说AI

在Python中，你可以使用内置的sqlite3模块来操作SQLite数据库。以下是一些使用sqlite3模块进行数据存储的基础步骤：

1. 连接到SQLite数据库

import sqlite3
# 连接到SQLite数据库
# 如果数据库不存在，会自动在当前目录创建:
conn = sqlite3.connect('example.db')

2. 创建一个表

# 创建一个Cursor对象并通过它执行SQL语句
cursor = conn.cursor()
# 创建表
cursor.execute('''CREATE TABLE IF NOT EXISTS stocks
                  (date text, trans text, symbol text, qty real, price real)''')
# 提交事务
conn.commit()

3. 插入数据

# 插入数据
cursor.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
# 提交事务
conn.commit()

4. 查询数据

# 查询数据
cursor.execute('SELECT * FROM stocks')
rows = cursor.fetchall()
for row in rows:
    print(row)

5. 更新数据

# 更新数据
cursor.execute("UPDATE stocks SET price = 38.00 WHERE symbol = 'RHAT'")
# 提交事务
conn.commit()

6. 删除数据

# 删除数据
cursor.execute("DELETE FROM stocks WHERE symbol = 'RHAT'")
# 提交事务
conn.commit()

7. 使用上下文管理器确保关闭连接

# 使用with语句确保连接被正确关闭
with sqlite3.connect('example.db') as conn:
    cursor = conn.cursor()
    # 执行SQL操作...

8. 关闭Cursor和连接

# 关闭Cursor
cursor.close()
# 关闭连接
conn.close()

在操作数据库时，通常建议使用with语句来管理数据库连接，这样可以确保即使在发生异常的情况下，连接也能被正确关闭。
此外，sqlite3模块还支持使用Python的DB-API 2.0规范定义的参数替换，这可以防止SQL注入攻击：

# 使用参数替换来防止SQL注入
t = ('RHAT',)
cursor.execute('SELECT * FROM stocks WHERE symbol=?', t)
print(cursor.fetchone())

请确保在处理数据库时遵循良好的编程实践，包括但不限于使用参数化查询来防止SQL注入攻击，以及在事务中适当地提交和回滚更改。
创建一个学生表

import sqlite3

# 创建一个内存数据库进行操作
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# 创建表
create_table_query = """
CREATE TABLE IF NOT EXISTS Students (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER,
    email TEXT UNIQUE
);
"""
cursor.execute(create_table_query)

# 检查表是否创建成功
cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='Students';")
table_exists = cursor.fetchone()

table_exists

清空表：这将删除表中的所有行，但表结构会保留。

DELETE FROM Students;

删除表：这将删除整个表，包括表结构和所有数据。

DROP TABLE Students;

# 清空表
cursor.execute("DELETE FROM Students;")

# 检查表是否为空
cursor.execute("SELECT * FROM Students;")
is_table_empty = cursor.fetchall()

# 删除表
cursor.execute("DROP TABLE Students;")

# 检查表是否还存在
cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='Students';")
table_exists_after_drop = cursor.fetchone()

is_table_empty, table_exists_after_drop

内连接

# 创建 Customers 表
cursor.execute('''
CREATE TABLE IF NOT EXISTS Customers (
    CustomerID INTEGER PRIMARY KEY,
    CustomerName TEXT
);
''')

# 创建 Orders 表
cursor.execute('''
CREATE TABLE IF NOT EXISTS Orders (
    OrderID INTEGER PRIMARY KEY,
    CustomerID INTEGER,
    OrderDate TEXT,
    FOREIGN KEY(CustomerID) REFERENCES Customers(CustomerID)
);
''')

# 插入 Customers 表的示例数据
customers_data = [
    (1, 'John Doe'),
    (2, 'Jane Smith'),
    (3, 'Alice Johnson')
]
cursor.executemany("INSERT INTO Customers (CustomerID, CustomerName) VALUES (?, ?);", customers_data)

# 插入 Orders 表的示例数据
orders_data = [
    (101, 1, '2023-09-10'),
    (102, 2, '2023-09-11'),
    (103, 1, '2023-09-12')
]
cursor.executemany("INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES (?, ?, ?);", orders_data)

# 执行内连接查询
cursor.execute('''
SELECT Orders.OrderID, Customers.CustomerName 
FROM Orders 
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
''')
inner_join_results = cursor.fetchall()

inner_join_results 
#输出：[(101, ‘John Doe’), (102, ‘Jane Smith’), (103, ‘John Doe’)]

排序

# 创建 Employees 表
cursor.execute('''
CREATE TABLE IF NOT EXISTS Employees (
    EmployeeID INTEGER PRIMARY KEY,
    EmployeeName TEXT,
    Salary REAL
);
''')

# 插入 Employees 表的示例数据
employees_data = [
    (1, 'John Doe', 70000),
    (2, 'Jane Smith', 65000),
    (3, 'Alice Johnson', 72000),
    (4, 'Mike Brown', 63000)
]
cursor.executemany("INSERT INTO Employees (EmployeeID, EmployeeName, Salary) VALUES (?, ?, ?);", employees_data)

# 执行查询并按 Salary 降序排序
cursor.execute('SELECT EmployeeName, Salary FROM Employees ORDER BY Salary DESC;')
sorted_employees_by_salary = cursor.fetchall()

sorted_employees_by_salary

COUNT():返回表中行的数量，或者返回特定列中非NULL值的数量。
SUM():返回数值列的总和。
AVG():返回数值列的平均值。
MAX():返回数值列中的最大值，或日期/时间列中的最新值。
MIN():返回数值列中的最小值，或日期/时间列中的最早值。

# 使用聚合函数查询 Employees 表

# 1. 计算总行数
cursor.execute('SELECT COUNT(*) FROM Employees;')
total_rows = cursor.fetchone()[0]

# 2. 计算薪资总和
cursor.execute('SELECT SUM(Salary) FROM Employees;')
total_salary = cursor.fetchone()[0]

# 3. 计算平均薪资
cursor.execute('SELECT AVG(Salary) FROM Employees;')
average_salary = cursor.fetchone()[0]

# 4. 获取最高薪资
cursor.execute('SELECT MAX(Salary) FROM Employees;')
max_salary = cursor.fetchone()[0]

# 5. 获取最低薪资
cursor.execute('SELECT MIN(Salary) FROM Employees;')
min_salary = cursor.fetchone()[0]

total_rows, total_salary, average_salary, max_salary, min_salary

分组（GROUP BY）是SQL中用于将数据分成多个小组以便于聚合的语句。在分组后，可以使用聚合函数（如COUNT(), SUM(), AVG(), MAX(), MIN()等）对每个分组进行计算。

# 执行分组查询并计算每个部门的平均薪资，同时过滤出平均薪资大于50,000的部门
cursor.execute('''
    SELECT department, AVG(salary) as average_salary 
    FROM Employees 
    GROUP BY department 
    HAVING AVG(salary) > 50000;
''')

# 获取查询结果
grouped_results = cursor.fetchall()
grouped_results
#输出：[(‘HR’, 57000.0), (‘IT’, 67666.66666666667), (‘Marketing’, 75000.0)]

使用pymysql模块操作MySQL

import pymysql

# 连接到数据库
db = pymysql.connect(
    host='127.0.0.1',  # 数据库地址
    user='root',       # 数据库用户名
    password='123456', # 数据库密码
    database='test',   # 数据库名
    charset='utf8'     # 字符集
)

# 创建游标对象
cursor = db.cursor()

# 执行SQL语句
try:
    # 执行一个查询操作
    cursor.execute("SELECT * FROM your_table_name")
    
    # 获取所有结果
    results = cursor.fetchall()
    
    # 打印结果
    for row in results:
        print(row)
    
    # 提交事务
    db.commit()
except Exception as e:
    # 如果发生错误，回滚事务
    db.rollback()
    print(f"An error occurred: {e}")
finally:
    # 关闭游标和连接
    cursor.close()
    db.close()
上一篇 下一篇

猜你喜欢

热点阅读