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
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
|
# -*- encoding: utf-8 -*-
"""
sqlite3 database module for MURSAT1 Mission Dashboard
"""
import sqlite3
class Db:
def __init__ (self):
self.conn = sqlite3.connect ('/usr/local/mmd/db/mmd.db')
self.conn.text_factory = str
self.conn.row_factory = sqlite3.Row
self.cursor = self.conn.cursor ()
self.cursor.execute ('PRAGMA foreign_keys = ON')
self.conn.commit ()
def sessionFind (self, session_id):
self.cursor.execute ('SELECT * FROM session WHERE id=?', (session_id,))
return self.cursor.fetchone ()
def sessionInit (self, session_id, email, expires, status):
try:
self.cursor.execute ('INSERT INTO session (id, email, expires, status) VALUES (?,?,?,?)', (session_id, email, expires, status,))
self.conn.commit ()
return True
except sqlite3.IntegrityError:
return False
def sessionDelete (self, session_id):
self.cursor.execute ('DELETE FROM session WHERE id=?', (session_id,))
self.conn.commit ()
def sessionUpdate (self, session_id, expires):
self.cursor.execute ('UPDATE session SET expires=? WHERE id=?', (expires, session_id,))
self.conn.commit ()
def sessionSetEmail (self, session_id, email):
self.cursor.execute ('UPDATE session SET email=? WHERE id=?', (email, session_id))
self.conn.commit ()
def sessionSetStatus (self, session_id, status):
self.cursor.execute ('UPDATE session SET status=? WHERE id=?', (status, session_id))
self.conn.commit ()
def sessionDeleteExpired (self, expires):
self.cursor.execute ('DELETE FROM session WHERE expires<?', (expires,))
self.conn.commit ()
return self.cursor.rowcount
#
# user related methods
#
def userCreate (self, user_id, firstname, lastname, email, callsign, password, code, regtimeout):
try:
self.cursor.execute ('INSERT INTO user (id, firstname, lastname, email, callsign, password, code, regtimeout) VALUES (?,?,?,?,?,?,?,?)',
(user_id, firstname, lastname, email, callsign, password, code, regtimeout))
self.conn.commit ()
return True
except sqlite3.IntegrityError:
return False
def userConfirm (self, code):
self.cursor.execute ('UPDATE user SET status="valid" WHERE code=?', (code, ))
self.conn.commit ()
def userCancel (self, code):
self.cursor.execute ('DELETE FROM user WHERE code=?', (code, ))
self.conn.commit ()
def userDelete (self, user_id):
self.cursor.execute ('DELETE FROM user WHERE id=?', (user_id,))
self.conn.commit ()
def userFindId (self, user_id):
self.cursor.execute ('SELECT * FROM user WHERE id=?', (user_id,))
return self.cursor.fetchone ()
def userFindEmail (self, email):
self.cursor.execute ('SELECT * FROM user WHERE email=?', (email,))
return self.cursor.fetchone ()
def userFindPending (self, code):
self.cursor.execute ('SELECT * FROM user WHERE code=? and status="pending"', (code,))
return self.cursor.fetchone ()
# location related methods
def locationCreate (self, location_id, name, longitude, latitude, user_id, is_default):
try:
self.cursor.execute ('INSERT INTO location (id, name, longitude, latitude, user_id, is_default) VALUES (?,?,?,?,?,?)', (location_id, name, longitude, latitude, user_id, is_default))
self.conn.commit ()
return True
except sqlite3.IntegrityError:
return False
def locationFindUserId (self, user_id):
self.cursor.execute ('SELECT * FROM location WHERE user_id=?', (user_id,))
return self.cursor.fetchone ()
def close (self):
self.cursor.close ()
self.conn.close ()
if __name__ == "__main__":
db = Db ()
print db.sessionFind ('1234567890')
db.sessionInit ('1234567890', 'jogi@mur.at', '1')
data = db.sessionFind ('1234567890')
print data['id'], data['email']
db.sessionDelete ('1234567890')
print db.sessionFind ('1234567890')
print db.userFindId ('1')
db.userCreate ('1', 'Jogi', 'Hofmüller', 'jogi@mur.at', '', '', '', '')
print db.userFindId ('1')
db.userDelete ('1')
# vim: tw=0 ts=2 expandtab
# EOF
|
