5byvvy5P

1import sqlite3
2from onetoone.cars import Car
3from onetoone.sellers import Seller
4
5def open_connection():
6    connect = sqlite3.connect('duombaze.db')
7    cursor = connect.cursor()
8    return connect, cursor
9
10def create_table_cars():
11    try:
12        connect, cursor = open_connection()
13
14        cursor.execute ("""CREATE TABLE IF NOT EXISTS cars (
15                    id integer PRIMARY KEY AUTOINCREMENT,
16                    marke text UNIQUE,
17                    modelis text,
18                    years integer,
19                    kaina integer)""")
20        connect.commit()
21
22    except sqlite3.DatabaseError as error:
23        print(error)
24    finally:
25        connect.close()
26
27def create_table_sellers():
28    try:
29        connect, cursor = open_connection()
30
31        cursor.execute ("""CREATE TABLE IF NOT EXISTS sellers (
32                    id integer PRIMARY KEY,
33                    marke text UNIQUE,
34                    seller_price integer,
35                    car_id integer,
36                    FOREIGN KEY(car_id) REFERENCES cars(id))""")
37        connect.commit()
38
39    except sqlite3.DatabaseError as error:
40        print(error)
41    finally:
42        connect.close()
43
44def db_query(query, query_parameters = None):
45    try:
46        connect, cursor = open_connection()
47        if query_parameters == None:
48            for row in cursor.execute(query):
49                print(row)
50        else:
51            cursor.execute(query, query_parameters)
52            connect.commit()
53    except sqlite3.DatabaseError as error:
54        print(error)
55    finally:
56        connect.close()
57
58#---------------------------------
59
60#----------Insert---------------
61
62car1 = Car(None, 'VW', 'Passat', 1999, 900)
63car2 = Car(None, 'BMW', 'e39', 1999, 1000)
64
65def create_car(car):
66    query = "INSERT into cars VALUES (?, ?, ?, ?, ?)"
67    query_parameters = (car.id, car.marke, car.modelis, car.years, car.kaina)
68    db_query(query, query_parameters)
69
70seller1 = Seller(None, 'VW', 1200, None,)
71seller2 = Seller(None, 'BMW', 1050, None,)
72
73def create_seller(seller):
74    connection, cursor = open_connection()
75    car = cursor.execute("SELECT id FROM cars WHERE marke = 'VW' ")
76    isloopo = []
77    for loop in car:
78        print(loop)
79        isloopo.append(loop)
80
81    print(isloopo[0])
82    connection.close()
83
84    query = "INSERT into sellers VALUES (?,?,?,?)"
85    query_parameters = (seller.id, seller.marke, seller.seller_price, isloopo)
86    db_query(query, query_parameters)
87
88
89#---------------------------------
90
91#-------------Get-----------------
92
93def get_cars():
94    query = "SELECT * from cars"
95    db_query(query)
96def get_sellers():
97    query = """SELECT * from sellers
98                JOIN cars ON cars.marke = sellers.marke"""
99    db_query(query)
100
101#---------------------------------
102
103#-------------Update--------------
104def update_car(car):
105    query = "UPDATE cars SET years = 2002 WHERE modelis = ?"
106    query_parameters = (car.modelis, )
107    db_query(query, query_parameters)
108
109def update_seller(seller):
110    query = "UPDATE sellers SET seller_price = 2013 WHERE modelis = ?"
111    query_parameters = (seller.marke,)
112    db_query(query, query_parameters)
113
114#---------------------------------
115
116#-------------Delete--------------
117def delete_book(car):
118    query = "DELETE FROM car WHERE title = ?"
119    query_parameters = (car.modelis,)
120    db_query(query, query_parameters)
121
122def delete_publishers(seller):
123    query = "DELETE FROM seller WHERE title = ?"
124    query_parameters = (seller.marke,)
125    db_query(query, query_parameters)
126#---------------------------------
127
128#------------Profit---------------
129
130def profit():
131    query = """SELECT SUM(seller_price-kaina) from sellers
132                JOIN cars ON cars.marke = sellers.marke"""
133    db_query(query)
134
135
136create_table_cars()
137create_car(car1)
138create_car(car2)
139create_table_sellers()
140create_seller(seller1)
141create_seller(seller2,car2)
142
143line = "------------------------------------------------"
144
145print(line+"\n                 | Preke |\n"+line)
146
147get_cars()
148print(line+"\n               | Pardavejas |\n"+line)
149get_sellers()
150print(line+"\n                | Profitas |\n"+line)
151profit()
152print(line)