def log_psycopg2_error(e):
if e:
if e.pgerror:
msg = str(e.pgerror)
else:
msg = str(e)
else:
msg = "Not specified"
logger.error("Database error:" + msg)
def select_sql(search_sql):
if search_sql is None or search_sql == "":
return None
try:
db = PostGISDatabase()
cursor = db.conn.cursor()
cursor.execute(search_sql)
return cursor
except psycopg2.Error as e:
logger.error(str(e))
return [sys.exc_info()[0]]
def build_gi_ds_table(test=False):
insert_sql = None
logger.info("Building table ac_gids")
logger.info("------------------------")
connection = open_connection(test=test)
out = False
try:
create_temp_table(connection)
cursor = get_address_rows(connection)
try:
if cursor is None:
return
logger.info("Inserting rows")
logger.info("----------------------")
insert_cursor = connection.cursor()
builder = http_shared.MimeBuilder("texttoonerow")
row_count = 0
gauge_count = 0
for row in cursor:
row_count += 1
gauge_count += 1
try:
street, house_number, locality, zip_code, orientation_number, orientation_number_character, locality_part, typ_so, nazev_mop, gid = row
house_number, record_number = http_shared.analyse_row(
typ_so, house_number)
district_number = http_shared.extract_dictrict_number(
nazev_mop)
row_label = compile_address.compile_address(
builder, street, house_number, record_number,
orientation_number, orientation_number_character,
zip_code, locality, locality_part, district_number)
insert_sql = "INSERT INTO {0} ({1}, {2}) VALUES ({3}, '{4}')".format(
'ac_gids', 'gid', 'address', gid, row_label)
insert_cursor.execute(insert_sql)
connection.commit()
if gauge_count >= 1000:
gauge_count = 0
logger.info(str(row_count) + " rows")
out = True
except psycopg2.Error as e:
log_psycopg2_error(e)
logger.error(
str(row_count) + " " + insert_sql + " failed. ")
exit_app()
out = False
pass
logger.info("Done - %d rows inserted." % row_count)
finally:
cursor.close()
# renameTempTable(connection)
logger.info("Building table ac_gids done.")
finally:
connection.close()
return out
def open_connection(test=False):
conf = DB_CONF
if test:
conf = DB_CONF_TEST
try:
conn = psycopg2.connect(host=conf["host"],
database=conf["database"],
port=conf["port"],
user=conf["user"],
password=conf["password"])
except psycopg2.Error as e:
logger.error(str(e))
conn = None
return conn
def get_address_rows(connection):
logger.info("Retrieving address rows")
cursor = connection.cursor()
try:
query = 'select {0}, cast({1} AS {11}), {2}, cast({3} AS {11}), cast({4} AS {11}), {5}, {6}, {7}, {8}, {9} from {10} '.format(
'nazev_ulice', 'cislo_domovni', 'nazev_obce', 'psc',
'cislo_orientacni', 'znak_cisla_orientacniho', 'nazev_casti_obce',
'typ_so', 'nazev_mop', 'gid', 'address_points', 'TEXT')
cursor.execute(query)
logger.info("Done.")
return cursor
except psycopg2.Error:
logger.error("Error:Selecting address rows failed.")
exit_app()
def _get_rows(connection_item):
sys.stdout.write("Retrieving records to be processes")
cursor_item = connection_item.cursor()
try:
query = 'select {0}, {1}, {2} from {3} group by {1}, {0}, {2} order by {1}, {0}, {2}'.format(
'nazev_ulice',
'nazev_casti_obce',
'nazev_obce',
'address_points',
)
cursor_item.execute(query)
print(" - done.")
return cursor_item
except psycopg2.Error:
print('Error: Selecting towns with no streets failed.')
logger.error('Selecting towns with no streets failed.')
exit_app()
def _get_ruian_version_date(test=False):
try:
connection = open_connection(test=test)
cursor = connection.cursor()
try:
query = 'select * from {0}'.format('ruian_dates')
cursor.execute(query)
row = cursor.fetchone()
result = row[1]
except psycopg2.Error as e:
logger.error(str(e))
return "ERROR:" + e.pgerror
finally:
cursor.close()
connection.close()
except psycopg2.Error as e:
result = "Error: Could connect to %s at %s:%s as %s\n%s" % (
DATABASE_NAME, DATABASE_HOST, PORT, USER_NAME, str(e))
logger.error(result)
return result
def load_sql_script_file(sql_file_name,
msg='load_sql_script_file',
exit_if_file_not_found=True):
logger.info(msg)
ignore = {'CREATE FUNCTION'} # extend this
def _filter(statement, allow=0):
ddl = [
t for t in statement.tokens
if t.ttype in (tokens.DDL, tokens.Keyword)
]
start = ' '.join(d.value for d in ddl[:2])
if ddl and start in ignore:
allow = 1
for tok in statement.tokens:
if allow or not isinstance(tok, sqlparse.sql.Comment):
yield tok
sql_file_name = os.path.join(get_ruian_services_sql_scripts_path(),
sql_file_name)
logger.info(" Loading SQL commands from {0}".format(sql_file_name))
if not os.path.exists(sql_file_name):
if exit_if_file_not_found:
logger.error("ERROR: File %s not found." % sql_file_name)
exit_app()
else:
logger.warning("ERROR: File %s not found." % sql_file_name)
return
in_file = codecs.open(sql_file_name, "r", "utf-8")
raw = in_file.read()
in_file.close()
statements = []
for stmt in sqlparse.split(raw):
sql = sqlparse.parse(stmt)[0]
tl = sqlparse.sql.TokenList([t for t in _filter(sql)])
statements.append(tl.value)
logger.info(" Loading SQL commands - done.")
return statements
def exec_complex_sql_script(sql_items, test=False):
logger.info(" Executing SQL statements {}".format(''))
out = False
connection = open_connection(test=test)
cursor = connection.cursor()
try:
for sql_item in sql_items:
logger.info(" Executing SQL statement: {}".format(sql_item))
if sql_item != '':
cursor.execute(sql_item)
connection.commit()
out = True
except psycopg2.Error as e:
log_psycopg2_error(e)
logger.error(" Executing SQL statements: {}".format(str(e)))
finally:
if cursor is not None:
cursor.close()
if connection is not None:
connection.close()
logger.info(" Executing SQL statements - done.")
return out
def build_towns_no_streets(test=False):
def _get_rows(connection_item):
sys.stdout.write("Retrieving records to be processes")
cursor_item = connection_item.cursor()
try:
query = 'select {0}, {1}, {2} from {3} group by {1}, {0}, {2} order by {1}, {0}, {2}'.format(
'nazev_ulice',
'nazev_casti_obce',
'nazev_obce',
'address_points',
)
cursor_item.execute(query)
print(" - done.")
return cursor_item
except psycopg2.Error:
print('Error: Selecting towns with no streets failed.')
logger.error('Selecting towns with no streets failed.')
exit_app()
print("Building table ac_townsnostreets")
print("------------------------")
connection = None
try:
connection = open_connection(test=test)
cursor = _get_rows(connection)
try:
if cursor is None:
return
print("Inserting rows")
print("----------------------")
insert_cursor = connection.cursor()
def insert_row(nazev_casti_obce_txt, nazev_obce_txt):
insert_sql = "INSERT INTO {0} ({1}, {2}) VALUES ('{3}', '{4}')".format(
'ac_townsnostreets', 'nazev_casti_obce', 'nazev_obce',
nazev_casti_obce_txt, nazev_obce_txt)
insert_cursor.execute(insert_sql)
connection.commit()
pass
row_count = 0
gauge_count = 0
last_nazev_casti_obce = None
last_nazev_obce = None
num_streets = 0
for row in cursor:
gauge_count += 1
try:
nazev_ulice, nazev_casti_obce, nazev_obce = row
if (last_nazev_casti_obce is None
or last_nazev_casti_obce == nazev_casti_obce) and (
last_nazev_obce is None
or last_nazev_obce == nazev_obce):
if nazev_ulice is not None and nazev_ulice != "":
num_streets = num_streets + 1
else:
if last_nazev_casti_obce != "":
if num_streets == 0:
insert_row(nazev_casti_obce, nazev_obce)
row_count += 1
num_streets = 0
last_nazev_casti_obce = None
last_nazev_obce = None
last_nazev_casti_obce = nazev_casti_obce
last_nazev_obce = nazev_obce
if gauge_count >= 1000:
gauge_count = 0
print(str(row_count) + " rows")
except psycopg2.Error as e:
log_psycopg2_error(e)
logger.error(str(row_count))
exit_app()
pass
print("Done - %d rows inserted." % row_count)
finally:
if cursor is not None:
cursor.close()
except psycopg2.Error as e:
log_psycopg2_error(e)
logger.error(str(e))
exit_app()
pass
finally:
if connection is not None:
connection.close()
