-
Notifications
You must be signed in to change notification settings - Fork 0
/
database.py
199 lines (186 loc) · 7.55 KB
/
database.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
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
# DeepSeaRobotix - coYaght
# ========================
# In the *coYaght* system there is an Arduino Uno that takes measurements and controls its
# motors. The Arduino communicates with the Raspberry Pi 3B+ using a serial communications
# port. Through this port the system can reprogram the Arduino's firmware and command it to
# switch on/off the motors of the coYaght. At the same time, there are some sensors connected
# to it; a temperature sensor, a light sensor and a pressure sensor. Added to all these there
# is also a battery operated Real Time Clock to keep tracking of the date/time. The Arduino
# takes measurements of all these sensors every second and transmits them through the same
# serial communications port. All the results are stored in a database (MySQL) installed in
# the Raspberry Pi
#
# The following python module defines a class to control the updating of the local database
# and also the retrieval of its contents.
#
# Basic SQL commands:
# ===================
#
# For table creation:
# CREATE TABLE `measurements` (
# `id` bigint unsigned NOT NULL AUTO_INCREMENT,
# `DateStamp` DATETIME NOT NULL,
# `Temp` DECIMAL(5,2) NOT NULL,
# `Press` DECIMAL(8,2) NOT NULL,
# `Depth` DECIMAL(7,3) NOT NULL,
# `Lux` DECIMAL(7,2) NOT NULL,
# `Batt` DECIMAL(4,2) NOT NULL,
# PRIMARY KEY (`id`),
# UNIQUE KEY `DateStamp` (`DateStamp`))
# ENGINE InnoDB DEFAULT CHARSET=utf8;
#
# For inserting data into the table:
# INSERT INTO `measurements`(`DateStamp`, `Temp`, `Press`, `Depth`, `Lux`, `Batt`)
# VALUES ("2020-07-17 21:22:34", 34.2, 98.72, 0.015, 548.34, 10.95);
#
# For fetching values from the table:
# SELECT * FROM `measurements`;
#
#
# (C) 2020, The DeepSeaRobotix Team of 1st Junior High School of Gerakas
# Under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International
# (CC BY-NC-SA 4.0) license.
# For the full license document, please follow:
# https://creativecommons.org/licenses/by-nc-sa/4.0/legalcode
#
# Summary of the license:
# =======================
#
# * You are free to:
# Share — copy and redistribute the material in any medium or format
# Adapt — remix, transform, and build upon the material
# The licensor cannot revoke these freedoms as long as you follow the license terms.
# * Under the following terms:
# Attribution — You must give appropriate credit, provide a link to the license, and
# indicate if changes were made. You may do so in any reasonable manner,
# but not in any way that suggests the licensor endorses you or your use.
# NonCommercial — You may not use the material for commercial purposes.
# ShareAlike — If you remix, transform, or build upon the material, you must distribute
# your contributions under the same license as the original.
# * No additional restrictions — You may not apply legal terms or technological measures that
# legally restrict others from doing anything the license permits.
# * Notices:
# You do not have to comply with the license for elements of the material in the public
# domain or where your use is permitted by an applicable exception or limitation.
# No warranties are given. The license may not give you all of the permissions necessary
# for your intended use. For example, other rights such as publicity, privacy, or moral
# rights may limit how you use the material.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED,
# INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
# PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE
# FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR
# OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
# DEALINGS IN THE SOFTWARE.
#
# Import the needed modules
import sys
import pymysql
import threading
# The class that represents the MySQL Database is ArduinoDB
class ArduinoDB():
# Initialize the necessrary variables. Normally the password of the database should be
# defines in a more secure way than plain text in a variable. But for the purpose of this
# project we leave it that way
def __init__(self):
self.connectedflg = False # Not connected, yet
self.dbhost = "localhost"
self.dbuser = "coyaght"
self.passwd = "coYaght_Gerakas"
self.dbname = "coYaght"
self.db = None
self.cur = None
self.lastData = None
self.cond = threading.Condition() # Condition for synchronizing the threads when
# there are new data stored in the database
# First things first. The instance must get connected to the MySQL database and set the
# autocommit flag. For some reason, while MySQL has set this flag automatically, when
# connected through python it acts as if it is not. So, to be safe we set it here, again.
def connect(self):
if self.db == None:
try:
self.db = pymysql.connect(host=self.dbhost, #Host of the database
user=self.dbuser, #User to be used
passwd=self.passwd, #Password
db=self.dbname) #Database to be used
except:
self.db = None
return -1
try:
self.cur = self.db.cursor()
self.cur.execute("SET autocommit=1")
self.cond = threading.Condition()
except:
return -2
return 0
# When the system needs to release this resource it must disconnect from the database.
def disconnect(self):
if self.db != None:
try:
self.db.close()
self.db = None
self.cur = None
except:
self.db = None
self.cur = None
return -1;
return 0;
# Return the connection status of the instance
def isconnected(self):
return (self.cur != None)
# Insert one row of data into 'measurements' table in the MySQL database
def insert(self, indata):
if self.db == None:
return -1
query = "INSERT INTO `measurements`(" + \
"`DateStamp`, `Temp`, `Press`, `Depth`, `Lux`, `Batt`) VALUES (" + \
"\"{0:s}\", {1:.2f}, {2:.2f}, {3:.3f}, {4:.2f}, {5:.2f})".format(
indata[0], indata[1], indata[2], indata[3], indata[4], indata[5])
try:
self.cur.execute(query)
self.cond.acquire()
self.lastData = indata
self.cond.notify_all()
self.cond.release()
except:
return -2
return 0
# Return the last inserted data. It does not perform a SELECT request to the database. It
# only returns the local copy of the last stored data to avoid unnecessary transactions
def fetchLastData(self):
return self.lastData
# Fetches a set of rows by performing a SELECT request to the database. The SELECT can
# contain a Starting Date/Time, an Ending Date/Time, an optional LIMIT and an optional
# OFFSET
def fetchset(self, StartDate, EndDate, Limit = None, Offst = None):
if self.db == None:
return -1
query = "SELECT * FROM `measurements`"
if ((StartDate != None) or (EndDate != None)):
query += " WHERE "
if (StartDate != None):
query += "DateStamp >= \"" + StartDate + "\""
if ((StartDate != None) and (EndDate != None)):
query += " AND "
if (EndDate != None):
query += "DateStamp <= \"" + EndDate + "\""
if (Limit != None):
query += " LIMIT " + str(Limit)
if (Offst != None):
query += " OFFSET " + str(Offst)
try:
self.cur.execute(query)
except:
return -2
return self.cur.fetchall()
# Returns the last rows of data stored in the database
def fetchlast(self, Limit):
if self.db == None:
return -1
query = "(SELECT * FROM `measurements` ORDER BY `id` DESC LIMIT " + str(Limit) + \
") ORDER BY `id` ASC"
try:
self.cur.execute(query)
except:
return -2
return self.cur.fetchall()