def get_inbox(host='local',read_status=0,table='loggers',limit=200,
resource="sms_data"):
db, cur = dbio.connect(host=host, resource=resource)
if table == 'loggers':
tbl_contacts = '%s_mobile' % table[:-1]
elif table == 'users':
tbl_contacts = 'mobile_numbers'
else:
raise ValueError('Error: unknown table', table)
while True:
try:
query = ("select inbox_id,ts_sms,sim_num,sms_msg from "
"(select inbox_id,ts_sms,mobile_id,sms_msg from smsinbox_%s "
"where read_status = %d order by inbox_id desc limit %d) as t1 "
"inner join (select mobile_id, sim_num from %s) as t2 "
"on t1.mobile_id = t2.mobile_id ") % (table, read_status, limit,
tbl_contacts)
# print query
a = cur.execute(query)
out = []
if a:
out = cur.fetchall()
return out
except MySQLdb.OperationalError:
print ('9.',)
time.sleep(20)
def get_db_dataframe(query):
"""
- Description.
Args:
Args (str): Args.
Returns:
Returns.
Raises:
MySQLdb.OperationalError: Error in database connection.
"""
try:
db, cur = dbio.connect(host='local')
df = psql.read_sql_query(query, db)
# df.columns = ['ts','id','x','y','z','m']
# change ts column to datetime
# df.ts = pd.to_datetime(df.ts)
db.close()
return df
except KeyboardInterrupt:
print("Exception detected in accessing database")
sys.exit()
except psql.DatabaseError:
print("Error getting query %s" % (query))
return None
except:
print(sys.exc_info()[0])
def test_connect_use_resource_sms_data_exp_success(self):
# args = {
# "resource": "sms_data",
# "host": "local"
# }
# args = ("","","sms_data",1)
status = dbio.connect(resource="sms_data")
self.assertIsNotNone(status)
def get_all_outbox_sms_from_db(table='',send_status=5,gsm_id=5,limit=10,
resource="sms_data"):
"""
**Description:**
-The function that get all outbox message that are not yet send.
:param table: Table name and **Default** to **users** table .
:param send_status: **Default** to **5**.
:param gsm_id: **Default** to **5**.
:param limit: **Default** to **10**.
:type table: str
:type send_status: str
:type gsm_id: int
:type limit: int
:returns: List of message
"""
if not table:
raise ValueError("No table definition")
sc = mem.server_config()
host = sc['resource']['smsdb']
while True:
try:
db, cur = dbio.connect(host=host, resource=resource)
query = ("select t1.stat_id,t1.mobile_id,t1.gsm_id,t1.outbox_id,"
"t2.sms_msg from "
"smsoutbox_%s_status as t1 "
"inner join (select * from smsoutbox_%s) as t2 "
"on t1.outbox_id = t2.outbox_id "
"where t1.send_status < %d "
"and t1.send_status >= 0 "
"and t1.gsm_id = %d "
"limit %d ") % (table[:-1],table,send_status,gsm_id,limit)
a = cur.execute(query)
out = []
if a:
out = cur.fetchall()
db.close()
return out
except MySQLdb.OperationalError:
print ('10.',)
time.sleep(20)
def get_surficial_markers(host=None, from_memory=True):
"""
- Description.
Args:
Args (str): Args.
Returns:
Returns.
Raises:
MySQLdb.OperationalError: Error in database connection.
"""
mc = memory.get_handle()
sc = memory.server_config()
if from_memory:
return mc.get("surficial_markers")
if not host:
print("Host defaults to datadb")
host = sc["resource"]["datadb"]
query = ("select m2.marker_id, m3.marker_name, m4.site_id from "
"(select max(history_id) as history_id, "
"marker_id from marker_history as m1 "
"group by m1.marker_id "
") as m2 "
"inner join marker_names as m3 "
"on m2.history_id = m3.history_id "
"inner join markers as m4 "
"on m2.marker_id = m4.marker_id ")
engine = dbio.connect(resource="sensor_data", conn_type=0)
surficial_markers = psql.read_sql_query(query, engine)
mc.set("surficial_markers", surficial_markers)
return surficial_markers
def test_connect_use_host_sb_local_exp_success(self):
status = dbio.connect(connection="sb_local")
self.assertIsNotNone(status)
