From 06602b4ec0102d89e3c80a966eec1388de71c0f3 Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Mon, 12 Jul 2021 20:32:46 +0200 Subject: [PATCH] provide wrapper function for DROP TABLE Use psycopg2 formatting to ensure correct quoting. --- nominatim/db/connection.py | 22 ++++++++++++++++--- nominatim/tools/exec_utils.py | 2 +- nominatim/tools/freeze.py | 2 +- nominatim/tools/refresh.py | 2 +- .../tools/special_phrases/sp_importer.py | 14 ++++-------- 5 files changed, 26 insertions(+), 16 deletions(-) diff --git a/nominatim/db/connection.py b/nominatim/db/connection.py index ac8d7c85..1eb3599b 100644 --- a/nominatim/db/connection.py +++ b/nominatim/db/connection.py @@ -8,6 +8,7 @@ import os import psycopg2 import psycopg2.extensions import psycopg2.extras +from psycopg2 import sql as pysql from nominatim.errors import UsageError @@ -37,6 +38,22 @@ class _Cursor(psycopg2.extras.DictCursor): return self.fetchone()[0] + def drop_table(self, name, if_exists=True, cascade=False): + """ Drop the table with the given name. + Set `if_exists` to False if a non-existant table should raise + an exception instead of just being ignored. If 'cascade' is set + to True then all dependent tables are deleted as well. + """ + sql = 'DROP TABLE ' + if if_exists: + sql += 'IF EXISTS ' + sql += '{}' + if cascade: + sql += ' CASCADE' + + self.execute(pysql.SQL(sql).format(pysql.Identifier(name))) + + class _Connection(psycopg2.extensions.connection): """ A connection that provides the specialised cursor by default and adds convenience functions for administrating the database. @@ -75,14 +92,13 @@ class _Connection(psycopg2.extensions.connection): return True - def drop_table(self, name, if_exists=True): + def drop_table(self, name, if_exists=True, cascade=False): """ Drop the table with the given name. Set `if_exists` to False if a non-existant table should raise an exception instead of just being ignored. """ with self.cursor() as cur: - cur.execute("""DROP TABLE {} "{}" - """.format('IF EXISTS' if if_exists else '', name)) + cur.drop_table(name, if_exists, cascade) self.commit() diff --git a/nominatim/tools/exec_utils.py b/nominatim/tools/exec_utils.py index f91c5654..72d252b7 100644 --- a/nominatim/tools/exec_utils.py +++ b/nominatim/tools/exec_utils.py @@ -141,6 +141,6 @@ def get_url(url): try: with urlrequest.urlopen(urlrequest.Request(url, headers=headers)) as response: return response.read().decode('utf-8') - except: + except Exception: LOG.fatal('Failed to load URL: %s', url) raise diff --git a/nominatim/tools/freeze.py b/nominatim/tools/freeze.py index cc1bf97e..ce0b5cde 100644 --- a/nominatim/tools/freeze.py +++ b/nominatim/tools/freeze.py @@ -29,7 +29,7 @@ def drop_update_tables(conn): tables = [r[0] for r in cur] for table in tables: - cur.execute('DROP TABLE IF EXISTS "{}" CASCADE'.format(table)) + cur.drop_table(table, cascade=True) conn.commit() diff --git a/nominatim/tools/refresh.py b/nominatim/tools/refresh.py index 97e2e037..6b18f67d 100644 --- a/nominatim/tools/refresh.py +++ b/nominatim/tools/refresh.py @@ -49,7 +49,7 @@ def load_address_levels(conn, table, levels): _add_address_level_rows_from_entry(rows, entry) with conn.cursor() as cur: - cur.execute('DROP TABLE IF EXISTS {}'.format(table)) + cur.drop_table(table) cur.execute("""CREATE TABLE {} (country_code varchar(2), class TEXT, diff --git a/nominatim/tools/special_phrases/sp_importer.py b/nominatim/tools/special_phrases/sp_importer.py index a26ea8a6..791f4dc3 100644 --- a/nominatim/tools/special_phrases/sp_importer.py +++ b/nominatim/tools/special_phrases/sp_importer.py @@ -248,20 +248,14 @@ class SPImporter(): Delete the place_classtype tables. """ LOG.warning('Cleaning database...') - # Array containing all queries to execute. - # Contains tuples of format (query, parameters) - queries_parameters = [] # Delete place_classtype tables corresponding to class/type which # are not on the wiki anymore. - for table in self.table_phrases_to_delete: - self.statistics_handler.notify_one_table_deleted() - query = SQL('DROP TABLE IF EXISTS {}').format(Identifier(table)) - queries_parameters.append((query, ())) - with self.db_connection.cursor() as db_cursor: - for query, parameters in queries_parameters: - db_cursor.execute(query, parameters) + for table in self.table_phrases_to_delete: + self.statistics_handler.notify_one_table_deleted() + db_cursor.drop_table(table) + def _convert_php_settings_if_needed(self, file_path): """ -- 2.39.5