def testnoneToString(self): self.assertEqual(HTTPShared.noneToString(None), "", "None must be converted to string.") self.assertEqual(HTTPShared.noneToString("34"), "34", "String must be converted to string.") self.assertEqual(HTTPShared.noneToString(34), "34", "Int must be converted to string.") self.assertEqual(HTTPShared.noneToString([34, "44"]), ["34", "44"], "List must be converted to list of strings.") self.assertEqual(HTTPShared.noneToString(("34", None, 15, "ff")), ("34", "", "15", "ff"), "Tuple must be converted to tuple strings.") pass
def testnoneToString(self): self.assertEqual(HTTPShared.noneToString(None), "", "None must be converted to string.") self.assertEqual(HTTPShared.noneToString("34"), "34", "String must be converted to string.") self.assertEqual(HTTPShared.noneToString(34), "34", "Int must be converted to string.") self.assertEqual(HTTPShared.noneToString([34, "44"]), ["34", "44"], "List must be converted to list of strings.") self.assertEqual(HTTPShared.noneToString( ("34", None, 15, "ff")), ("34", "", "15", "ff"), "Tuple must be converted to tuple strings.") pass
def _findCoordinatesByAddress(dictionary): if dictionary.has_key("districtNumber") and dictionary["districtNumber"] != "": dictionary["districtNumber"] = "Praha " + dictionary["districtNumber"] first = True con = psycopg2.connect(host=DATABASE_HOST, database=DATABASE_NAME, port= PORT, user=USER_NAME, password=PASSWORD) cur = con.cursor() query = "SELECT latitude, longitude, gid, nazev_obce, nazev_casti_obce, nazev_ulice, cislo_domovni, typ_so, cislo_orientacni, znak_cisla_orientacniho, psc, nazev_mop FROM " + TABLE_NAME + " WHERE " for key in dictionary: if dictionary[key] != "": if first: query += ITEM_TO_DBFIELDS[key] + " = '" + dictionary[key] + "'" first = False else: query += " AND " + ITEM_TO_DBFIELDS[key] + " = '" + dictionary[key] + "'" query += "LIMIT 25" cur.execute(query) rows = cur.fetchall() coordinates = [] for row in rows: if (row[0] is not None) and (row[1] is not None): (houseNumber, recordNumber) = HTTPShared.analyseRow(row[7], numberToString(row[6])) coordinates.append((str("{:10.2f}".format(row[0])).strip(),str("{:10.2f}".format(row[1])).strip(), row[2], row[3], noneToString(row[4]), noneToString(row[5]), houseNumber, recordNumber, numberToString(row[8]), noneToString(row[9]), numberToString(row[10]), numberValue(noneToString(row[11])))) else: pass #co se ma stat kdyz adresa nema souradnice? return coordinates
def _findCoordinates(ID): con = psycopg2.connect(host=DATABASE_HOST, database=DATABASE_NAME, port= PORT, user=USER_NAME, password=PASSWORD) cur = con.cursor() cur.execute("SELECT latitude, longitude, gid, nazev_obce, nazev_casti_obce, nazev_ulice, cislo_domovni, typ_so, cislo_orientacni, znak_cisla_orientacniho, psc, nazev_mop FROM " + TABLE_NAME + " WHERE gid = "+ str(ID)) row = cur.fetchone() if row and row[0] is not None and row[1] is not None: (houseNumber, recordNumber) = HTTPShared.analyseRow(row[7], numberToString(row[6])) c = (str("{:10.2f}".format(row[1])).strip(), str("{:10.2f}".format(row[0])).strip(), row[2], row[3], noneToString(row[4]), noneToString(row[5]), houseNumber, recordNumber, numberToString(row[8]), noneToString(row[9]), numberToString(row[10]), numberValue(noneToString(row[11]))) return [c] else: return []
def _findAddress(ID): con = psycopg2.connect(host=DATABASE_HOST, database=DATABASE_NAME, port= PORT, user=USER_NAME, password=PASSWORD) cur = con.cursor() cur.execute("SELECT nazev_ulice, cislo_domovni, typ_so, cislo_orientacni, znak_cisla_orientacniho, psc, nazev_obce, nazev_casti_obce, nazev_mop FROM " + TABLE_NAME + " WHERE gid = "+ str(ID)) row = cur.fetchone() if row: (houseNumber, recordNumber) = HTTPShared.analyseRow(row[2], numberToString(row[1])) a= numberValue(noneToString(row[8])) return Address(noneToString(row[0]),houseNumber,recordNumber,numberToString(row[3]), noneToString(row[4]),numberToString(row[5]),noneToString(row[6]),noneToString(row[7]),a) else: return None
def _findCoordinatesByAddress(dictionary): if dictionary.has_key( "districtNumber") and dictionary["districtNumber"] != "": dictionary["districtNumber"] = "Praha " + dictionary["districtNumber"] first = True con = psycopg2.connect(host=DATABASE_HOST, database=DATABASE_NAME, port=PORT, user=USER_NAME, password=PASSWORD) cur = con.cursor() query = "SELECT latitude, longitude, gid, nazev_obce, nazev_casti_obce, nazev_ulice, cislo_domovni, typ_so, cislo_orientacni, znak_cisla_orientacniho, psc, nazev_mop FROM " + TABLE_NAME + " WHERE " for key in dictionary: if dictionary[key] != "": if first: query += ITEM_TO_DBFIELDS[key] + " = '" + dictionary[key] + "'" first = False else: query += " AND " + ITEM_TO_DBFIELDS[key] + " = '" + dictionary[ key] + "'" query += "LIMIT 25" cur.execute(query) rows = cur.fetchall() coordinates = [] for row in rows: if (row[0] is not None) and (row[1] is not None): (houseNumber, recordNumber) = HTTPShared.analyseRow(row[7], numberToString(row[6])) coordinates.append( (str("{:10.2f}".format(row[0])).strip(), str("{:10.2f}".format(row[1])).strip(), row[2], row[3], noneToString(row[4]), noneToString(row[5]), houseNumber, recordNumber, numberToString(row[8]), noneToString(row[9]), numberToString(row[10]), numberValue(noneToString(row[11])))) else: pass #co se ma stat kdyz adresa nema souradnice? return coordinates
def _findAddress(ID): con = psycopg2.connect(host=DATABASE_HOST, database=DATABASE_NAME, port=PORT, user=USER_NAME, password=PASSWORD) cur = con.cursor() cur.execute( "SELECT nazev_ulice, cislo_domovni, typ_so, cislo_orientacni, znak_cisla_orientacniho, psc, nazev_obce, nazev_casti_obce, nazev_mop FROM " + TABLE_NAME + " WHERE gid = " + str(ID)) row = cur.fetchone() if row: (houseNumber, recordNumber) = HTTPShared.analyseRow(row[2], numberToString(row[1])) a = numberValue(noneToString(row[8])) return Address(noneToString(row[0]), houseNumber, recordNumber, numberToString(row[3]), noneToString(row[4]), numberToString(row[5]), noneToString(row[6]), noneToString(row[7]), a) else: return None
def _findCoordinates(ID): con = psycopg2.connect(host=DATABASE_HOST, database=DATABASE_NAME, port=PORT, user=USER_NAME, password=PASSWORD) cur = con.cursor() cur.execute( "SELECT latitude, longitude, gid, nazev_obce, nazev_casti_obce, nazev_ulice, cislo_domovni, typ_so, cislo_orientacni, znak_cisla_orientacniho, psc, nazev_mop FROM " + TABLE_NAME + " WHERE gid = " + str(ID)) row = cur.fetchone() if row and row[0] is not None and row[1] is not None: (houseNumber, recordNumber) = HTTPShared.analyseRow(row[7], numberToString(row[6])) c = (str("{:10.2f}".format(row[1])).strip(), str("{:10.2f}".format(row[0])).strip(), row[2], row[3], noneToString(row[4]), noneToString(row[5]), houseNumber, recordNumber, numberToString(row[8]), noneToString(row[9]), numberToString(row[10]), numberValue(noneToString(row[11]))) return [c] else: return []
def buildGIDsTable(): logger.info("Building table ac_gids") logger.info("------------------------") connection = psycopg2.connect(host=config.databaseHost, database=config.databaseName, port=config.databasePort, user=config.databaseUserName, password=config.databasePassword) try: createTempTable(connection) cursor = getAddressRows(connection) try: if cursor == None: return logger.info("Inserting rows") logger.info("----------------------") insertCursor = connection.cursor() builder = HTTPShared.MimeBuilder("texttoonerow") row_count = 0 gaugecount = 0 for row in cursor: row_count += 1 gaugecount += 1 try: street, houseNumber, locality, zipCode, orientationNumber, orientationNumberCharacter, localityPart, typ_so, nazev_mop, gid = row houseNumber, recordNumber = HTTPShared.analyseRow( typ_so, houseNumber) districtNumber = HTTPShared.extractDictrictNumber( nazev_mop) rowLabel = compileaddress.compileAddress( builder, street, houseNumber, recordNumber, orientationNumber, orientationNumberCharacter, zipCode, locality, localityPart, districtNumber) insertSQL = "INSERT INTO ac_gids (gid, address) VALUES (%s, '%s')" % ( gid, rowLabel) insertCursor.execute(insertSQL) connection.commit() if gaugecount >= 1000: gaugecount = 0 logger.info(str(row_count) + " rows") except psycopg2.Error as e: logPsycopg2Error(e) logger.error( str(row_count) + " " + insertSQL + " failed. ") exitApp() pass logger.info("Done - %d rows inserted." % row_count) finally: cursor.close() #renameTempTable(connection) logger.info("Building table ac_gids done.") finally: connection.close() pass
def p(name, defValue = ""): return HTTPShared.getQueryParam(queryParams, name, defValue)
houseNumber = "" recordNumber = numberToString(cislo_domovni) else: pass return houseNumber, recordNumber def itemToStr(item): if item == None: return "" else: return str(item) builder = HTTPShared.MimeBuilder("texttoonerow") ID_VALUE = 'id' def getAutocompleteOneItemResults(ruianType, nameToken, maxCount=10): if ruianType == "": ruianType == ID_VALUE nameToken = nameToken.lower() joinSeparator = ", " if ruianType == ID_VALUE: searchSQL = "select gid from gids where cast(gid as text) ilike '" + nameToken + "%'" searchSQL += " limit " + str(maxCount)