-
Notifications
You must be signed in to change notification settings - Fork 1
/
dbi.py
102 lines (92 loc) · 3.38 KB
/
dbi.py
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
89
90
91
92
93
94
95
96
97
98
99
100
101
102
'''
DB interface.
Handles table creation, connection and cursor initialisation, loading and saving.
'''
import sys, sqlite3
conn = None
cur = None
def create_fuel():
'''
Create fuel table and index:
fuel_id integer, primary key and index
vehicle_id integer, use reg as look up into vdata
date text, date of fuelling
litres real, litres filled
ppl real, price per litre
trip real, distance between fills
odo integer, odometer reading
cost real, cost of fill
mpg real, calculated miles per gallon
notes text, free text
fuel_type text, type of fuel used (S/U/D)
'''
#print('create fuel')
global cur
cur.execute('''create table if not exists fuel (fuel_id integer, vehicle_id integer, date real, litres real, ppl real, trip real, odo integer, cost real, mpg real, notes text, fuel_type text, primary key (fuel_id asc))''')
cur.execute('''create unique index if not exists fuel_index on fuel (fuel_id)''')
cur.execute('''insert or replace into versions values (?,?)''', ['fuel', 3])
def create_vehicles():
'''
Create vehicle table and index:
vehicle_id integer, primary key and index
reg_no text, registration number
make text,
model text,
year integer,
purchase_price real,
purchase_date text,
fuel_cap real,
fuel_type text,
oil_cap real,
oil_type text,
tyre_cap real,
tyre_type text,
notes text,
'''
global cur
#print ('create vehicles')
cur.execute('''create table if not exists vehicles (vehicle_id integer, reg_no text, make text, model text, year integer, purchase_price real, purchase_date real, fuel_cap real, fuel_type text, oil_cap real, oil_type text, tyre_front_cap real, tyre_front_type text, tyre_rear_cap real, tyre_rear_type text, notes text, primary key(vehicle_id asc))''')
cur.execute('''create unique index if not exists vehicle_index on vehicles (vehicle_id)''')
cur.execute('''insert or replace into versions values (?,?)''', ['vehicles', 2])
def create_service():
'''
Create service table and index
service_id integer, primary key and index
vehicle_id integer, fkey into vehicles
date text, date of service item
cost real, cost of item
odo integer, optional
item text, description of item
notes text, free text
'''
global cur
#print('create service')
cur.execute('''create table if not exists service (service_id integer, vehicle_id integer, date real, cost real, odo integer, item text, notes text, primary key (service_id asc))''')
cur.execute('''create unique index if not exists service_index on service (service_id)''')
cur.execute('''insert or replace into versions values (?,?)''', ['service', 2])
def create_versions():
cur.execute('''create table if not exists versions (name text, version integer, primary key(name))''')
cur.execute('''create unique index if not exists version_index on versions (name)''')
def close():
'''
Commit and close DB connection
'''
global conn
conn.commit()
conn.close()
def init():
'''
Initialise db, connection and cursor.
Call table create functions.
Returns connection object
'''
global conn, cur
# open/create db file
conn = sqlite3.connect('ldc_fuel.db')
cur = conn.cursor()
create_versions()
create_fuel()
create_vehicles()
create_service()
conn.commit()
return conn