Eqwz9dpF

1import sqlite3
2from Books import books
3
4
5# Create books table --------------
6def create_books_table():
7    connection = sqlite3.connect("books.db")
8
9    connection_cursor = connection.cursor()
10
11    connection_cursor.execute(""" CREATE TABLE IF NOT EXISTS books (
12                                            id integer PRIMARY KEY,
13                                            books_title text,
14                                            author text,
15                                            publish_date text,
16                                            publisher text,
17                                            selling_price double
18                                            )""")
19
20    connection.commit()
21
22    connection.close()
23
24
25#create_books_table()
26
27# Create publishers table --------------
28def create_publishers_table():
29    connection = sqlite3.connect("books.db")
30
31    connection_cursor = connection.cursor()
32
33    connection_cursor.execute(""" CREATE TABLE IF NOT EXISTS publishers (
34                                            id_pub integer PRIMARY KEY,
35                                            publisher_name text,
36                                            book_title text,
37                                            author text,
38                                            printed_quantity integer,
39                                            printing_price double
40                                            )""")
41
42    connection.commit()
43
44    connection.close()
45
46
47#create_publishers_table()
48
49# Create books record --------------
50def create_book():
51    connection = sqlite3.connect("books.db")
52
53    connection_cursor = connection.cursor()
54
55    connection_cursor.execute(""" INSERT INTO books (books_title, author, publish_date, publisher, selling_price)
56                                VALUES ('Haris Poteris', 'Jr. Rowland', '2016.07.10', 'Knygu vaga', 10)""")
57
58    connection.commit()
59
60    connection.close()
61#create_book()
62
63# Create publisher record --------------
64def create_publisher():
65    connection = sqlite3.connect("books.db")
66
67    connection_cursor = connection.cursor()
68
69    connection_cursor.execute("""
70                        INSERT INTO publishers (publisher_name, book_title, author, printed_quantity, printing_price)
71                                VALUES ('Knygu vaga', 'Haris Poteris', 'Jr. Rowland', 1500, 7)""")
72
73    connection.commit()
74
75    connection.close()
76#create_publisher()
77
78# Select books table-------------
79def get_books():
80    connection = sqlite3.connect("books.db")
81
82    connection_cursor = connection.cursor()
83    connection_cursor.execute(""" SELECT * FROM books """)
84    connection.commit()
85    rows = []
86    for row in connection_cursor.execute(""" SELECT * FROM books """):
87        print(row)
88        rows.append(row)
89
90    connection.close()
91    return rows
92
93get_books()
94
95# Select publisher table-------------
96def get_publishers():
97    connection = sqlite3.connect("books.db")
98
99    connection_cursor = connection.cursor()
100    connection_cursor.execute(""" SELECT * FROM publishers """)
101    connection.commit()
102    rows = []
103    for row in connection_cursor.execute(""" SELECT * FROM publishers """):
104        print(row)
105        rows.append(row)
106
107    connection.close()
108    return rows
109
110get_publishers()
111# Update books record-------------
112def update_books():
113    try:
114        connection = sqlite3.connect("books.db")
115
116        connection_cursor = connection.cursor()
117
118        connection_cursor.execute("""
119                                    UPDATE books SET books_title = "Haris poteris 2" WHERE books_title = "Haris poteris" """)
120
121        connection.commit()
122        print(connection_cursor().fetchall())
123
124        connection.close()
125    except:
126        print(sqlite3.Error)
127    finally:
128        connection.close()
129#update_books()
130
131# Update publisher record-------------
132def update_publisher():
133    try:
134        connection = sqlite3.connect("books.db")
135
136        connection_cursor = connection.cursor()
137
138        connection_cursor.execute("""UPDATE publishers SET printing_price=5 WHERE id_pub=1 """)
139
140        connection.commit()
141
142        connection.close()
143    except:
144        print(sqlite3.Error)
145    finally:
146        connection.close()
147update_publisher()
148# Delete books record-------------
149def delete_books():
150    connection = sqlite3.connect("books.db")
151
152    connection_cursor = connection.cursor()
153
154    connection_cursor.execute(""" DELETE FROM books WHERE id=1 """)
155
156    connection.commit()
157
158    connection.close()
159#delete_books()
160
161# Delete publishers record-------------
162def delete_publishers():
163    connection = sqlite3.connect("books.db")
164
165    connection_cursor = connection.cursor()
166
167    connection_cursor.execute(""" DELETE FROM publishers WHERE id_pub=2 """)
168
169    connection.commit()
170
171    connection.close()
172#delete_publishers()
173#END
174
175def books_profit():
176    connection = sqlite3.connect("books.db")
177
178    connection_cursor = connection.cursor()
179
180    connection_cursor.execute("""
181    SELECT (selling_price - printing_price) * printed_quantity AS  FROM books JOIN publishers ON movies.id = boxoffice.movie_id;""")
182
183    connection.commit()
184
185    connection.close()
186
187#books_profit