def list_events(iid=None, rid=None, type=None, date=None): db = get_db_instance() data = [] try: with db.cursor() as cursor: # Read all records sql = "select * from {} where GCLOUD_ID like '%'" if iid: sql += ' and ID_INSTITUTION = {}'.format(iid) if rid: sql += ' and ID_REGION = {}'.format(rid) if type: sql += ' and TYPE = "{}"'.format(type) if date: sql += ' and DATE >= "{}"'.format(date) cursor.execute(sql.format(table_name)) result = cursor.fetchall() if result: for row in result: event = parse_event_obj(row) data.append(event) return data else: return None finally: db.close()
def get_institution_by_email(email): db = get_db_instance() institution = None try: with db.cursor() as cursor: # Read a single record sql = "select * from {} where EMAIL = '{}'" cursor.execute(sql.format(table_name, email)) result = cursor.fetchone() if result: institution = Institution(id=result[0], address=result[1], name=result[2], email=result[3], passwd=result[4], types=result[5], shelter=result[6], status=result[7], created_at=result[8], cpf_cnpj=[9], admin=result[10]) return institution else: return None finally: db.close()
def create_institution(address, name, email, passwd, types, shelter, status, cpf_cnpj): db = get_db_instance() institution = None try: with db.cursor() as cursor: # insert record sql = "INSERT INTO {} (ADDRESS, NAME, EMAIL, PASSWD, TYPES, SHELTER, STATUS, CPF_CNPJ) VALUES " \ "('{}', '{}', '{}','{}','{}',{},{},'{}')" cursor.execute( sql.format(table_name, address, name, email, passwd, types, shelter, status, cpf_cnpj)) created_id = db.insert_id() cursor.execute('commit') institution = Institution( id=created_id, address=address, name=name, email=email, passwd=passwd, types=types, shelter=shelter, status=status, cpf_cnpj=cpf_cnpj, created_at=datetime.now().strftime('%Y-%m-%d %H:%M:%S'), admin=False) return institution finally: db.close()
def delete_info(info_id): db = get_db_instance() try: with db.cursor() as cursor: # Remove a single record sql = "delete from {} where ID_INFO = {}" cursor.execute(sql.format(table_name, info_id)) cursor.execute('commit') return True finally: db.close()
def get_event(event_id): db = get_db_instance() event = None try: with db.cursor() as cursor: # Read a single record sql = "select * from {} where ID_EVENT = {}" cursor.execute(sql.format(table_name, event_id, type)) result = cursor.fetchone() if result: event = parse_event_obj(result) return event finally: db.close()
def check_free(date, calendar, type): db = get_db_instance() event = None try: with db.cursor() as cursor: # Read a single record sql = "select * from {} where ID_REGION = {} and DATE = '{}' and TYPE = '{}'" cursor.execute(sql.format(table_name, calendar.id, date, type)) result = cursor.fetchone() if not result: return True else: return False finally: db.close()
def get_calendar_by_region_and_type(region_id, type): db = get_db_instance() calendar = None try: with db.cursor() as cursor: # Read a single record sql = "select * from {} where ID_REGION = {} and TYPE like '{}'" cursor.execute(sql.format(table_name, region_id, type)) result = cursor.fetchone() calendar = Calendar(id=result[0], region_id=result[3], gcloud_id=result[2], type=result[1]) return calendar finally: db.close()
def get_calendar(calendar_id): db = get_db_instance() data = [] try: with db.cursor() as cursor: # Read a single record sql = "select * from {} where ID_CALENDAR = {}" cursor.execute(sql.format(table_name, calendar_id)) result = cursor.fetchone() if result: return Calendar(id=result[0], type=result[1], gcloud_id=result[2], region_id=result[3]) finally: db.close()
def create_calendar(region_id, gcloud_id, type): db = get_db_instance() calendar = None try: with db.cursor() as cursor: # insert record sql = "INSERT INTO {} (ID_REGION, GCLOUD_ID, TYPE) VALUES ({}, '{}', '{}')" cursor.execute(sql.format(table_name, region_id, gcloud_id, type)) created_id = db.insert_id() cursor.execute('commit') calendar = Calendar(id=created_id, region_id=region_id, gcloud_id=gcloud_id, type=type) return calendar finally: db.close()
def get_calendars_by_region(region_id): db = get_db_instance() data = [] try: with db.cursor() as cursor: # Read a single record sql = "select * from {} where ID_REGION = {}" cursor.execute(sql.format(table_name, region_id)) result = cursor.fetchall() for row in result: data.append({ 'calendar_id': row[0], 'type': row[1], 'gcloud_id': row[2] }) return data finally: db.close()
def create_region(region_name, address, population): db = get_db_instance() region = None try: with db.cursor() as cursor: # insert record sql = "INSERT INTO {} (NAME, ADDRESS, POPULATION) VALUES ('{}', '{}', {})" cursor.execute( sql.format(table_name, region_name, address, population)) created_id = db.insert_id() cursor.execute('commit') region = Region(id=created_id, address=address, name=region_name, population=population) return region finally: db.close()
def get_region(id): db = get_db_instance() region = None try: with db.cursor() as cursor: # Read a single record sql = "select * from {} where ID_REGION = {}" cursor.execute(sql.format(table_name, id)) result = cursor.fetchone() if result: region = Region(id=result[0], address=result[1], name=result[2], population=result[3]) return region else: return None finally: db.close()
def get_info(info_id): db = get_db_instance() info = None try: with db.cursor() as cursor: # Read a single record sql = "select * from {} where ID_INFO = {}" cursor.execute(sql.format(table_name, info_id)) result = cursor.fetchone() if result: info = Info(id=result[0], name=result[1], phone=result[2], link=result[3], image=result[4], description=result[5]) return info else: return None finally: db.close()
def create_information(name, phone, link, description, image): db = get_db_instance() info = None try: with db.cursor() as cursor: # insert record sql = "INSERT INTO {} (name, phone, link, description, image) VALUES " \ "('{}', '{}', '{}','{}', '{}')" cursor.execute( sql.format(table_name, name, phone, link, description, image)) created_id = db.insert_id() cursor.execute('commit') info = Info(id=created_id, name=name, phone=phone, link=link, description=description, image=image) return info finally: db.close()
def get_institutions(): db = get_db_instance() data = [] try: with db.cursor() as cursor: # Read all records sql = "select * from {} order by STATUS,CREATED_AT" cursor.execute(sql.format(table_name)) result = cursor.fetchall() if result: for row in result: data.append({ 'id': row[0], 'address': row[1], 'name': row[2], 'email': row[3], 'password': row[4], 'types': row[5].split(','), 'shelter': row[6], 'status': status_enum[str(row[7])], 'created_at': row[8].strftime('%Y-%m-%d %H:%M:%S'), 'cpf_cnpj': row[9], 'admin': row[10] }) return data else: return None finally: db.close()
def update_institution(institution, name, address, email, passwd, types, shelter, status, cpf_cnpj): from utils.password_utils import convert_md5 db = get_db_instance() try: with db.cursor() as cursor: # update a single record sql = "update {} set ".format(table_name) if name: sql += "NAME = '{}', ".format(name) institution.name = name if address: sql += "ADDRESS = '{}', ".format(address) institution.address = address if email: sql += "EMAIL = '{}', ".format(email) institution.email = email if passwd: sql += "PASSWD = '{}', ".format(convert_md5(passwd)) institution.passwd = convert_md5(passwd) if types: sql += "TYPES = '{}', ".format(types) institution.types = types if shelter: sql += "SHELTER = {}, ".format(shelter) institution.shelter = shelter if status: sql += "STATUS = {}, ".format(status_enum_reverse[status]) institution.status = status_enum_reverse[status] if cpf_cnpj: sql += "CPF_CNPJ = '{}', ".format(cpf_cnpj) institution.cpf_cnpj = cpf_cnpj sql = sql[:-2] + " " sql += "where ID_INSTITUTION = {}".format(institution.id) cursor.execute(sql) cursor.execute('commit') return institution finally: db.close()
def get_regions(): db = get_db_instance() data = [] try: with db.cursor() as cursor: # Read all records sql = "select * from {}" cursor.execute(sql.format(table_name)) result = cursor.fetchall() if result: for row in result: data.append({ 'id_region': row[0], 'name': row[2], 'address': row[1], 'population': row[3] }) return data else: return None finally: db.close()
def get_readiness_request(): """Test all used services and return readiness.""" result = { "API": "OK" } try: db = get_db_instance() if db: result['database'] = 'OK' db.close() except: result['database'] = 'ERROR' #TODO: google api ready code = 200 for key in result.keys(): if result[key] is not 'OK': code = 503 return jsonify(result), code
def get_infos(): db = get_db_instance() data = [] try: with db.cursor() as cursor: # Read all records sql = "select * from {}" cursor.execute(sql.format(table_name)) result = cursor.fetchall() if result: for row in result: data.append({ 'id': row[0], 'name': row[1], 'phone': row[2], 'link': row[3], 'image': row[4], 'description': row[5], }) return data else: return {} finally: db.close()
def create_event(date, period, type, gcloud_id, calendar, institution, region): db = get_db_instance() event = None try: with db.cursor() as cursor: # insert record sql = "INSERT INTO {} (DATE, PERIOD, GCLOUD_ID, TYPE, ID_CALENDAR, ID_INSTITUTION, ID_REGION)" \ " VALUES ('{}', '{}', '{}', '{}', {}, {}, {})" cursor.execute( sql.format(table_name, date, period, gcloud_id, type, calendar.id, institution.id, region.id)) created_id = db.insert_id() cursor.execute('commit') event = Event(id=created_id, date=date, period=period, type=type, gcloud_id=gcloud_id, calendar=calendar, institution=institution, region=region) return event finally: db.close()