python Bring their own SQLite database ,SQLite Support multiple users to read at the same time , But write operations can only be performed by one user at a time , Single database file .

The following code implements the database CURD operation ,books The data content of is :
book_name book_author finished_read_or_not,

Import database first package:

import sqlite3

1 Create table

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 List all records

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 Add records

(name, author) yes tuple, This way of writing is to prevent sql injection attack:

def add_book(name, author):
connection = sqlite3.connect('data.db')
cursor = connection.cursor()
# Unsafe writing , Vulnerable to attack , for example 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 Update record

(name,) The same is tuple, Can not write (name), Nor is 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 Delete record

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

The same applies to databases MySQL and PostgreSQL, In order to avoid repeated code of the above database connection and disconnection operations , It can be customized context manager Context manager , Be similar to with ... open ...