ReadInventory/database.py at main · spacecupcake1/ReadInventory · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
import sqlite3
from sqlite3 import Error

DATABASE = 'books.db'

def create_connection():
    try:
        conn = sqlite3.connect(DATABASE)
        return conn
    except Error as e:
        print(e)

def create_table():
    try:
        conn = create_connection()
        cursor = conn.cursor()
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS books (
                id INTEGER PRIMARY KEY,
                book_name TEXT,
                date_started DATE,
                date_ended DATE,
                rating INTEGER
            )
        ''')
        conn.commit()
        conn.close()
    except Error as e:
        print(e)

def insert_book(book_name, date_started, date_ended, rating):
    try:
        conn = create_connection()
        cursor = conn.cursor()
        cursor.execute('''
            INSERT INTO books (book_name, date_started, date_ended, rating)
            VALUES (?, ?, ?, ?)
        ''', (book_name, date_started, date_ended, rating))
        conn.commit()
        conn.close()
    except Error as e:
        print(e)

def fetch_all_books():
    try:
        conn = create_connection()
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM books')
        books = cursor.fetchall()
        conn.close()
        return books
    except Error as e:
        print(e)

def fetch_book(book_id):
    try:
        conn = create_connection()
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM books WHERE id=?', (book_id,))
        book = cursor.fetchone()
        conn.close()
        return book
    except Error as e:
        print(e)

def update_book(book_id, book_name, date_started, date_ended, rating):
    try:
        conn = create_connection()
        cursor = conn.cursor()
        cursor.execute('''
            UPDATE books
            SET book_name=?, date_started=?, date_ended=?, rating=?
            WHERE id=?
        ''', (book_name, date_started, date_ended, rating, book_id))
        conn.commit()
        conn.close()
    except Error as e:
        print(e)

def delete_book(book_id):
    try:
        conn = create_connection()
        cursor = conn.cursor()
        cursor.execute('DELETE FROM books WHERE id=?', (book_id,))
        conn.commit()
        conn.close()
    except Error as e:
        print(e)