def rename_temp_table(connection_item):
logger.info("Renaming table _ac_gids to ac_gids.")
cursor_item = connection_item.cursor()
cursor_item.execute('drop table if exists ac_gids;')
cursor_item.execute('alter table _ac_gids rename to ac_gids;')
cursor_item.close()
logger.info("Done.")
def exec_sql_script_file(sql_file_name,
msg='exec_sql_script_file',
exit_if_file_not_found=True,
test=False):
logger.info(msg)
statements = load_sql_script_file(
sql_file_name=sql_file_name,
exit_if_file_not_found=exit_if_file_not_found)
return exec_complex_sql_script(sql_items=statements, test=test)
def create_temp_table(connection):
logger.info("Creating table ac_gids")
cursor = connection.cursor()
try:
cursor.execute('drop table if exists {0};'.format('ac_gids'))
cursor.execute(
'CREATE TABLE {0} ({1} integer NOT NULL, {2} text);'.format(
'ac_gids', 'gid', 'address'))
logger.info("Done.")
finally:
cursor.close()
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 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_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