def getAll(**kwargs): """ option keywords can be 'macAddress' or 'dataId' :return: Array of Collect """ result = [] con = serverConnect() with con: cur = con.cursor() sql = """SELECT c.id id, c.id_data id_data , d.name dname, d.unit unit, c.mac_address mac, ca.name caname, \ c.date cdate, c.value cvalue FROM collect c \ JOIN data d ON d.id = c.id_data \ JOIN `caption` ca ON ca.mac_address = c.mac_address""" whereArgs = [] whereValues = () if 'macAddress' in kwargs or 'dataId' in kwargs: sql = sql + " WHERE " if ('macAddress' in kwargs): whereArgs.append("ca.mac_address = %s") whereValues = whereValues + (kwargs['macAddress'],) if ('dataId' in kwargs): whereArgs.append("c.id_data = %s") whereValues = whereValues + (kwargs['dataId'],) sql = sql + " and ".join(whereArgs) + " ORDER BY c.date DESC limit 20" cur.execute(sql,whereValues) rows = cur.fetchall() for row in rows: data = Data(row["id_data"], row["dname"], row["unit"]) caption = Caption(row["mac"], row["caname"]) result.append(Collect(row["id"], data, caption, row["cdate"], row["cvalue"])) cur.close() con.close() return result
def create(threshold): """ :param threshold: Threshold :return: Threshold error : 'pymysql.err.IntegrityError: (1062, "Duplicata du champ 'xxx' pour la clef 'PRIMARY'") id, data, caption, value, higher, lastDate, frequency, contacts """ connection = serverConnect() with connection.cursor() as cursor: sql = """INSERT INTO `threshold` (`id_data`, `mac_address`, `value`, `higher`, `last_date`, `frequency`) \ VALUES (%s, %s, %s, %s, %s, %s)""" cursor.execute(sql, (threshold.data.id, threshold.caption.macAddress, threshold.value, threshold.higher, threshold.lastDate, threshold.frequency)) threshold.id = int(connection.insert_id()) connection.commit() cursor.close() # type Contact for contact in threshold.contacts: with connection.cursor() as cursor: sql = "INSERT INTO `recipient` (`address`, `id_threshold`) VALUES (%s, %s)" cursor.execute(sql, (contact.email, threshold.id)) connection.commit() cursor.close() connection.close() return threshold
def delete(threshold): """ :param threshold: Threshold """ connection = serverConnect() with connection.cursor() as cursor: sql = "DELETE FROM `threshold` WHERE `id` = %s" cursor.execute(sql, (int(threshold.id))) connection.commit() cursor.close() connection.close()
def delete(data): """ :param data: Data """ connection = serverConnect() with connection.cursor() as cursor: sql = "DELETE FROM `data` WHERE `id` = %s" cursor.execute(sql, (int(data.id))) connection.commit() cursor.close() connection.close()
def delete(caption): """ :param caption: Caption """ connection = serverConnect() with connection.cursor() as cursor: sql = "DELETE FROM `caption` WHERE `mac_address` = %s" cursor.execute(sql, (caption.macAddress)) connection.commit() cursor.close() connection.close()
def delete(contact): """ :param contact: Contact """ connection = serverConnect() with connection.cursor() as cursor: sql = "DELETE FROM `contact` WHERE `address` = %s" cursor.execute(sql, (contact.email)) connection.commit() cursor.close() connection.close()
def delete(collect): """ :param collect: Collect """ connection = serverConnect() with connection.cursor() as cursor: sql = "DELETE FROM `collect` WHERE `id` = %s" cursor.execute(sql, collect.id) connection.commit() cursor.close() connection.close()
def update(data): """ :param data: Data """ connection = serverConnect() with connection.cursor() as cursor: sql = "UPDATE `data` SET `name` =%s, `unit` = %s WHERE `id` = %s" cursor.execute(sql, (data.name, data.unit, int(data.id))) connection.commit() cursor.close() connection.close()
def update(contact): """ :param contact: Contact """ connection = serverConnect() with connection.cursor() as cursor: sql = "UPDATE `contact` SET `firstname` =%s, `lastname` =%s WHERE `address` = %s" cursor.execute(sql, (contact.firstname, contact.lastname, contact.email)) connection.commit() cursor.close() connection.close()
def update(collect): """ :param collect: Collect """ connection = serverConnect() with connection.cursor() as cursor: sql = "UPDATE `collect` SET `id_data` =%s, `mac_address` =%s, `date` =%s, `value` =%s WHERE `id` = %s" cursor.execute(sql, (collect.data.id, collect.caption.macAddress, collect.date, collect.value, collect.id)) connection.commit() cursor.close() connection.close()
def create(contact): """ :param contact: Contact error : 'pymysql.err.IntegrityError: (1062, "Duplicata du champ '*****@*****.**' pour la clef 'PRIMARY'") """ connection = serverConnect() with connection.cursor() as cursor: sql = "INSERT INTO `contact` (`address`, `firstname`, `lastname`) VALUES (%s, %s, %s)" cursor.execute(sql, (contact.email, contact.firstname, contact.lastname)) connection.commit() cursor.close() connection.close()
def update(caption): """ :param caption: Caption """ connection = serverConnect() with connection.cursor() as cursor: sql = "UPDATE `caption` SET `name` =%s, `active` =%s WHERE `mac_address` = %s" cursor.execute(sql, (caption.name, caption.active, caption.macAddress)) connection.commit() cursor.close() connection.close()
def create(caption): """ :param caption: Caption error : 'pymysql.err.IntegrityError: (1062, "Duplicata du champ 'mac1' pour la clef 'PRIMARY'") """ connection = serverConnect() with connection.cursor() as cursor: sql = "INSERT INTO `caption` (`mac_address`, `name`, `active`) VALUES (%s, %s, %s)" cursor.execute(sql, (caption.macAddress, caption.name, caption.active)) connection.commit() cursor.close() connection.close()
def create(collect): """ :param collect: Collect error : 'pymysql.err.IntegrityError: (1062, "Duplicata du champ 'xxx' pour la clef 'PRIMARY'") """ connection = serverConnect() with connection.cursor() as cursor: sql = "INSERT INTO `collect` (`id_data`, `mac_address`, `date` , `value`) VALUES (%s, %s, %s, %s)" cursor.execute(sql, (collect.data.id, collect.caption.macAddress, collect.date,collect.value)) collect.id = int(connection.insert_id()) connection.commit() cursor.close() connection.close()
def create(data): """ :param data: Data :return: Data error : 'pymysql.err.IntegrityError: (1062, "Duplicata du champ 'xxx' pour la clef 'PRIMARY'") """ connection = serverConnect() with connection.cursor() as cursor: sql = "INSERT INTO `data` (`name`, `unit`) VALUES (%s, %s)" cursor.execute(sql, (data.name, data.unit)) data.id = int(connection.insert_id()) connection.commit() cursor.close() connection.close() return data
def getAll(): """ :return: Array of Data """ result = [] con = serverConnect() with con: cur = con.cursor() cur.execute("SELECT `id`, `name`, `unit` FROM `data`") rows = cur.fetchall() for row in rows: result.append(Data(row["id"], row["name"], row["unit"])) cur.close() con.close() return result
def getAll(): """ :return: Array of Contact """ result = [] con = serverConnect() with con: cur = con.cursor() cur.execute("SELECT `address`, `firstname`, `lastname` FROM `contact`") rows = cur.fetchall() for row in rows: result.append(Contact(row["address"], row["firstname"], row["lastname"])) cur.close() con.close() return result
def getByEmail(email): """ :param email: String :return: Contact """ con = serverConnect() result = None with con: cur = con.cursor() cur.execute("SELECT `address`, `firstname`, `lastname` FROM `contact` WHERE `address` = %s", email) row = cur.fetchone() if row is not None: result = Contact(row["address"], row["firstname"], row["lastname"]) cur.close() con.close() return result
def getById(id): """ :param id: Integer :return: Data """ con = serverConnect() result = None with con: cur = con.cursor() cur.execute( "SELECT `id`, `name`, `unit` FROM `data` WHERE `id` = %s", int(id)) row = cur.fetchone() if row is not None: result = Data(row["id"], row["name"], row["unit"]) cur.close() con.close() return result
def getByMacAddress(macAddress): """ :param macAddress: String :return: Caption """ con = serverConnect() result = None with con: cur = con.cursor() cur.execute( "SELECT `mac_address`, `name`, `active` FROM `caption` WHERE `mac_address` = %s", macAddress) row = cur.fetchone() if row is not None: result = Caption(row["mac_address"], row["name"], row["active"]) cur.close() con.close() return result
def getById(id): """ :param id: Integer :return: Collect """ con = serverConnect() result = None with con: cur = con.cursor() cur.execute("""SELECT c.id id, c.id_data id_data , d.name dname, d.unit unit, c.mac_address mac, ca.name caname, \ c.date cdate, c.value cvalue FROM collect c \ JOIN data d ON d.id = c.id_data \ JOIN `caption` ca ON ca.mac_address = c.mac_address WHERE c.id = %s""", int(id)) row = cur.fetchone() if row is not None: data = Data(row["id_data"], row["dname"], row["unit"]) caption = Caption(row["mac"], row["caname"]) result = Collect(row["id"], data, caption, row["cdate"], row["cvalue"]) cur.close() con.close() return result
def getAll(**kwargs): """ option keywords is active : type Boolean :return: Array of Caption """ result = [] con = serverConnect() with con: cur = con.cursor() sql = "SELECT `mac_address`, `name`, `active` FROM `caption`" if 'active' in kwargs: sql = sql + " where active = " + str( 1 if kwargs['active'] else 0) sql = sql + " order by name" cur.execute(sql) rows = cur.fetchall() for row in rows: result.append( Caption(row["mac_address"], row["name"], row["active"])) cur.close() con.close() return result
def update(threshold): """ :param threshold: Threshold """ connection = serverConnect() cursor = connection.cursor() sql = """UPDATE `threshold` SET `id_data`=%s, `mac_address`=%s, `value`=%s, \ `higher`=%s, `last_date`=%s, `frequency`=%s \ WHERE `id` = %s""" cursor.execute(sql, (threshold.data.id, threshold.caption.macAddress, threshold.value, threshold.higher, threshold.lastDate, threshold.frequency, threshold.id)) connection.commit() cursor.close() cursor = connection.cursor() sql = """SELECT address FROM recipient WHERE id_threshold = %s""" cursor.execute(sql, threshold.id) rows = cursor.fetchall() addresses = [] for row in rows: if not threshold.containsContact(row["address"]): cursor2 = connection.cursor() sql = "DELETE FROM recipient WHERE address = %s AND id_threshold = %s" cursor2.execute(sql, (row["address"], threshold.id)) connection.commit() cursor2.close() else: addresses.append(row["address"]) cursor.close() newContacts = threshold.getExtraEmails(addresses) for contact in newContacts: cursor2 = connection.cursor() sql = "INSERT INTO recipient (address,id_threshold) VALUES (%s, %s)" cursor2.execute(sql, (contact.email, threshold.id)) connection.commit() cursor2.close() connection.close()
def getById(id): """ :param id: Integer :return: Threshold """ con = serverConnect() result = None with con: cur = con.cursor() sql = """SELECT t.id id, d.id did, d.name dname, d.unit dunit, c.mac_address mac, c.name cname, \ t.value tvalue, t.higher higher, t.last_date last_date, t.frequency frequency \ FROM threshold t \ JOIN caption c ON c.mac_address = t.mac_address \ JOIN `data` d ON d.id = t.id_data WHERE t.id = %s""" cur.execute(sql, id) row = cur.fetchone() if row is not None: cur2 = con.cursor() sql2 = """SELECT c.address address, c.firstname firstname, c.lastname lastname \ FROM recipient r JOIN contact c on r.address = c.address \ WHERE r.id_threshold = %s""" cur2.execute(sql2, (row["id"])) rows2 = cur2.fetchall() contacts = [] for row2 in rows2: contacts.append( Contact(row2["address"], row2["firstname"], row2["lastname"])) data = Data(row["did"], row["dname"], row["dunit"]) caption = Caption(row["mac"], row["cname"]) result = Threshold(row["id"], data, caption, row["tvalue"], row["higher"], row["last_date"], row["frequency"], contacts) cur.close() con.close() return result