def selectQuery(table, **options): reqStart = datetime.datetime.now() print("*** Function selectQuery was called on '%s'" % reqStart) user_columns = options.get('columns') if user_columns: columns = ','.join('{column}'.format(column=column) for column in user_columns) else: columns = "*" like_str, equal_str, values = inputToQueryString(options.get('like'), options.get('equal')) where = joinClauses(like_str, equal_str, options.get('where','1=1')) limit = options.get('limit','100') order = options.get('order', 'oid desc') offset = options.get('offset','0') hits = options.get('hits', False) sum = options.get('sum',False) #query = "SELECT {columns} FROM {table} WHERE {where} ORDER BY {order} LIMIT {limit} OFFSET {offset}".format(columns=columns, table=table, where=where, limit=limit, order=order, offset=offset) query = "SELECT {columns} FROM {table} WHERE {where} LIMIT {limit} OFFSET {offset}".format(columns=columns, table=table, where=where, limit=limit, offset=offset) countQuery = "SELECT count(1) from (SELECT {columns} FROM {table} WHERE {where}) as count".format(columns=columns, table=table, where=where) print query connection = pgsql.PGSql() connection.connect() data = False if not hits: print("SQL SELECT START: %s" % datetime.datetime.now()) data = connection.query(query, False) print("SQL SELECT END: %s" % datetime.datetime.now()) if sum: print("SQL COUNT START: %s" % datetime.datetime.now()) total = connection.query(countQuery,False) print("SQL COUNT END: %s" % datetime.datetime.now()) connection.close() reqEnd = datetime.datetime.now() print('RESPONSE TIME: %s FOR DATA SQL QUERIES' % (str(reqEnd - reqStart))) if data and sum: return data,total elif data: return data elif hits: return total else: return False
def __init__(self): self.personclass = Person.Person() self.connection = pgsql.PGSql()
def __init__(self): self.connection = pgsql.PGSql() self.personclass = Person.Person() self.apiBaseUrl = 'http://' + cfg.host + ':' + cfg.apiport + '/api'
def get(self): connection = pgsql.PGSql() if request.args: for argument in request.args: ### ADDED GROUP BY TO AVOID DUPLICATES ### if argument == 'name': sqlSelect = ("SELECT oib OIB, establishment_name MUN_NAME, naziv MUNIC, concat(street,' ',street_number) ADDR, cide_establishment.id ESTABID, count(id_coordinated_inspection) CICOUNT, company_name FIRMA " "FROM cide_establishment " "LEFT JOIN cide_coordinated_inspection ON cide_coordinated_inspection.id_establishment = cide_establishment.id " "LEFT JOIN rpot_postanskiured ON rpot_postanskiured.id = cide_establishment.city_id " "WHERE establishment_name LIKE '%s' GROUP BY OIB,MUN_NAME,MUNIC,ADDR,ESTABID ORDER BY max(last_update) DESC NULLS LAST" % ((request.args.get(argument)).encode("utf-8")).replace('*', '%')) connection.connect() data = connection.query(sqlSelect) #data = connection.query(sql=sqlSelect) connection.close() continue elif argument == 'oib': sqlSelect = ("SELECT oib OIB, establishment_name MUN_NAME, naziv MUNIC, concat(street,' ',street_number) ADDR, cide_establishment.id ESTABID, count(id_coordinated_inspection) CICOUNT, company_name FIRMA " "FROM cide_establishment " "LEFT JOIN cide_coordinated_inspection ON cide_coordinated_inspection.id_establishment = cide_establishment.id " "LEFT JOIN rpot_postanskiured ON rpot_postanskiured.id = cide_establishment.city_id " "WHERE oib LIKE '%s' GROUP BY OIB,MUN_NAME,MUNIC,ADDR,ESTABID ORDER BY max(last_update) DESC NULLS LAST" % ((request.args.get(argument)).encode("utf-8")).replace('*', '%')) connection.connect() data = connection.query(sql=sqlSelect) connection.close() continue else: return Response('{"message":"parameter %s not available"}' % str(argument),mimetype='application/json') else: #sqlSelect = ("select oib OIB,establishment_name MUN_NAME,city_id MUNIC,concat(street,' ',street_number) ADDR, id ESTABID from cide_establishment GROUP BY OIB,MUN_NAME,MUNIC,ADDR,ESTABID") if 'ROLE_CIDE_ADMIN' in g.user[1] or 'ROLE_CIDE_COORDINATOR' in g.user[1]: sqlSelect = ("select oib OIB,establishment_name MUN_NAME,naziv MUNIC,concat(street,' ',street_number) ADDR,cide_establishment.id ESTABID,count(id_coordinated_inspection) CICOUNT, company_name FIRMA " "FROM cide_establishment " "LEFT JOIN cide_coordinated_inspection ON cide_coordinated_inspection.id_establishment = cide_establishment.id " "LEFT JOIN rpot_postanskiured ON rpot_postanskiured.id = cide_establishment.city_id " "GROUP BY OIB,MUN_NAME,MUNIC,ADDR,ESTABID " "ORDER BY max(last_update) DESC NULLS LAST") else: idpersonrole = self.personclass.getPersonRoleId(g.user) id_inspection_type_select = "select id_inspection_type from cide_person_role where id_person_role = %s" % idpersonrole[2][0][0] connection.connect() id_inspection_type = connection.query(id_inspection_type_select) connection.close() sqlSelect = ("select oib OIB,establishment_name MUN_NAME,naziv MUNIC,concat(street,' ',street_number) ADDR,cide_establishment.id ESTABID,count(cide_coordinated_inspection.id_coordinated_inspection) CICOUNT, company_name FIRMA " "FROM cide_establishment " "LEFT JOIN cide_coordinated_inspection ON cide_coordinated_inspection.id_establishment = cide_establishment.id " "LEFT JOIN rpot_postanskiured ON rpot_postanskiured.id = cide_establishment.city_id " "LEFT JOIN cide_specific_inspection ON cide_coordinated_inspection.id_coordinated_inspection = cide_specific_inspection.id_coordinated_inspection " "WHERE cide_specific_inspection.id_person_role in (select id_person_role from cide_person_role where id_inspection_type = %s ) " "GROUP BY OIB,MUN_NAME,MUNIC,ADDR,ESTABID " "ORDER BY max(cide_coordinated_inspection.last_update) DESC NULLS LAST" % id_inspection_type[0][0]) print sqlSelect connection.connect() data = connection.query(sqlSelect) connection.close() if data: returnDataList = [] #returnDataList.append({"count": connection.numresult}) for row in data: returnData = {} returnData['oib'] = (row['oib']) returnData['establishment_name'] = (row['mun_name']) returnData['establishment_municipality'] = (row['munic']) returnData['establishment_address'] = (row['addr']) returnData['id'] = (hashids.encode(row['estabid'])) returnData['ci_count'] = row['cicount'] returnData['establishment_operator'] = row['firma'] returnDataList.append(returnData) return Response(json.dumps(returnDataList,ensure_ascii=False), mimetype='application/json')
def __init__(self): self.connection = pgsql.PGSql()