python + SQLite 数据库 CRUD 操作_sqllite python curd-CSDN博客

本文介绍了如何使用Python的sqlite3库进行数据库操作，包括创建表、获取所有记录、添加、更新和删除操作，并强调了SQL注入防范。同时，提到了如何通过上下文管理器简化连接管理。

python 自带 SQLite 数据库，SQLite 支持多个用户同时读，但写操作只能一个时间一个用户，数据库文件单一。

以下代码实现数据库 CURD 操作，books 的数据内容为:
book_name book_author finished_read_or_not,

首先导入数据库 package:

import sqlite3

1 创建表

def create_book_table():
    connection = sqlite3.connect('data.db')
    cursor = connection.cursor()
    # name as primary key cannot be duplicated
    # cursor.execute('CREATE TABLE books(name text primary key, author text, read integer)')
    cursor.execute('CREATE TABLE IF NOT EXISTS books(name text primary key, author text, read integer)')
    connection.commit()
    connection.close()

2 列出全部记录

def get_all_books():
    connection = sqlite3.connect('data.db')
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM books")

    # cursor.fetchall() return a list of tuples: [(name,author,read),(name,author,read)]
    # to support the interface of app.py, change tuples into dicts
    books = [{'name': row[0], 'author': row[1], 'read': row[2]} for row in cursor.fetchall()]
    # connection.commit()  # Nothing to save to the disk, no need commit
    connection.close()
    return books

3 添加记录

(name, author) 是 tuple，这种写法是为了防止 sql injection attack:

def add_book(name, author):
    connection = sqlite3.connect('data.db')
    cursor = connection.cursor()

    # 不安全的写法，容易被攻击，例如 author： ", 0); DROP TABLE books;
    # f'INSERT INTO books VALUES("{name}", "", 0); DROP TABLE books;", 0)'
    # cursor.execute(f'INSERT INTO books VALUES("{name}", "{author}", 0)')

    # (name, author) is a tuple
    cursor.execute(f'INSERT INTO books VALUES(?, ?, 0)', (name, author))
    connection.commit()
    connection.close()

4 更新记录

(name,) 同样是 tuple，不能写成 (name), 也不能是 name

def mark_book_as_read(name):
    connection = sqlite3.connect("data.db")
    cursor = connection.cursor()
    cursor.execute('UPDATE books SET read=1 WHERE name=?', (name,))
    connection.commit()
    connection.close()

5 删除记录

def delete_book(name):
    connection = sqlite3.connect("data.db")
    cursor = connection.cursor()
    cursor.execute('DELETE FROM books WHERE name=?', (name,))
    connection.commit()
    connection.close()