Newer
Older
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
from notanorm import SqliteDb
# https://github.com/AtakamaLLC/notanorm
# https://www.sqlitetutorial.net
# https://www.sqlitetutorial.net/sqlite-create-table/
# https://www.sqlite.org
# https://stackoverflow.com/questions/1309989/parameter-substitution-for-a-sqlite-in-clause
# https://www.sqlitetutorial.net/sqlite-autoincrement/
def main():
#fname = 'tests/profiles.db'
fname = ":memory:"
db = SqliteDb(fname)
table_station = """
CREATE TABLE station (
id INTEGER PRIMARY KEY,
date_time TEXT NOT NULL UNIQUE,
julian_day REAL NOT NULL UNIQUE,
latitude REAL NOT NULL ,
longitude REAL NOT NULL,
max_depth REAL,
bottom_depth REAL
);"""
# the id is actually the rowid AUTOINCREMENT column.
table_profile = """
CREATE TABLE profile (
id INTEGER PRIMARY KEY,
station_id INTEGER,
PRES REAL NOT NULL,
FOREIGN KEY (station_id)
REFERENCES station (id)
); """
print('Create table station')
#db.query("DROP DATABASE IF EXISTS '{}'".format(fname))
db.query(table_station)
print('Create table profile')
db.query(table_profile)
for pm in ("TEMP", "PSAL"):
print('\tUpdate table profile with new column {}'.format(pm))
addColumn = "ALTER TABLE profile ADD COLUMN {} REAL NOT NULL".format(pm)
db.query(addColumn)
print('Insert data in table station ')
db.insert("station", id = 1, date_time = "2022-04-06 12:00:00.000",
julian_day = 10.5, latitude = -10.2, longitude = 23.6)
db.insert("station", id = 2, date_time = "2022-04-07 17:00:00.000",
julian_day = 11.5, latitude = -10.2, longitude = 23.6, max_depth = 2001,
bottom_depth = 5032)
column = "id"
result_set = db.select('station', {column: 1})
for result in result_set:
print(result)
result_set = db.select('station', {column: 2})
for result in result_set:
print(result)
print('Insert data in profile table')
db.insert("profile", station_id = 1, PRES = 1, TEMP = 20, PSAL = 35)
db.insert("profile", station_id = 1, PRES = 2, TEMP = 21, PSAL = 35)
db.insert("profile", station_id = 1, PRES = 3, TEMP = 22, PSAL = 35)
db.insert("profile", station_id = 1, PRES = 3, TEMP = 22, PSAL = 35)
db.insert("profile", station_id = 2, PRES = 4, TEMP = 20, PSAL = 35)
db.insert("profile", station_id = 2, PRES = 5, TEMP = 21, PSAL = 35)
db.insert("profile", station_id = 2, PRES = 6, TEMP = 22, PSAL = 35)
db.insert("profile", station_id = 2, PRES = 7, TEMP = 21.5, PSAL = 35)
db.insert("profile", station_id = 2, PRES = 7, TEMP = 21.5, PSAL = 35)
print('Display join from station an profile tables')
column = "id"
# select profile join station
result_set = db.select('profile', {"station_id": 2},'station' )
for result in result_set:
print(result)
print('get sizes:')
st = db.query('SELECT COUNT(id) FROM station')
max_press = db.query('SELECT MAX(PRES) FROM profile')
print(st, max_press)
if __name__ == "__main__": main()