使用sqlite3实现图书管理系统api

安装所需环境

pip install flask flask-restful sqlite3

然后，让我们创建一个Python文件，比如叫做library_api.py，并开始编写我们的API：

from flask import Flask, request, jsonify
from flask_restful import Api, Resource
import sqlite3

app = Flask(__name__)
api = Api(app)

# 数据库连接
def get_db_connection():
    conn = sqlite3.connect('library.db')
    conn.row_factory = sqlite3.Row
    return conn

# 初始化数据库
def init_db():
    conn = get_db_connection()
    conn.execute('''CREATE TABLE IF NOT EXISTS books
                    (id INTEGER PRIMARY KEY AUTOINCREMENT,
                     title TEXT NOT NULL,
                     author TEXT NOT NULL,
                     isbn TEXT UNIQUE,
                     publication_year INTEGER)''')
    conn.close()

init_db()

class BookList(Resource):
    def get(self):
        conn = get_db_connection()
        books = conn.execute('SELECT * FROM books').fetchall()
        conn.close()
        return jsonify([dict(book) for book in books])

    def post(self):
        new_book = request.get_json()
        conn = get_db_connection()
        try:
            cursor = conn.cursor()
            cursor.execute('INSERT INTO books (title, author, isbn, publication_year) VALUES (?, ?, ?, ?)',
                           (new_book['title'], new_book['author'], new_book['isbn'], new_book['publication_year']))
            conn.commit()
            return {"message": "Book added successfully", "id": cursor.lastrowid}, 201
        except sqlite3.IntegrityError:
            return {"message": "ISBN already exists"}, 400
        finally:
            conn.close()

class Book(Resource):
    def get(self, book_id):
        conn = get_db_connection()
        book = conn.execute('SELECT * FROM books WHERE id = ?', (book_id,)).fetchone()
        conn.close()
        if book is None:
            return {"message": "Book not found"}, 404
        return jsonify(dict(book))

    def put(self, book_id):
        updated_book = request.get_json()
        conn = get_db_connection()
        conn.execute('UPDATE books SET title = ?, author = ?, isbn = ?, publication_year = ? WHERE id = ?',
                     (updated_book['title'], updated_book['author'], updated_book['isbn'], 
                      updated_book['publication_year'], book_id))
        conn.commit()
        conn.close()
        return {"message": "Book updated successfully"}

    def delete(self, book_id):
        conn = get_db_connection()
        conn.execute('DELETE FROM books WHERE id = ?', (book_id,))
        conn.commit()
        conn.close()
        return {"message": "Book deleted successfully"}

api.add_resource(BookList, '/books')
api.add_resource(Book, '/books/<int:book_id>')

if __name__ == '__main__':
    app.run(debug=True)

这个API提供了以下端点：

GET /books: 获取所有图书
POST /books: 添加新图书
GET /books/: 获取特定图书
PUT /books/: 更新特定图书
DELETE /books/: 删除特定图书
运行 API：

python library_api.py

使用工具如 Postman 或 curl 来测试这些API端点。例如：

添加图书：

curl -X POST -H "Content-Type: application/json" -d '{"title":"To Kill a Mockingbird", "author":"Harper Lee", "isbn":"9780446310789", "publication_year":1960}' http://localhost:5000/books

获取所有图书：

curl http://localhost:5000/books

获取特定图书：

curl http://localhost:5000/books/1

更新图书：

curl -X PUT -H "Content-Type: application/json" -d '{"title":"To Kill a Mockingbird", "author":"Harper Lee", "isbn":"9780446310789", "publication_year":1961}' http://localhost:5000/books/1

删除图书：

curl -X DELETE http://localhost:5000/books/1