From 6f6681ce67dc1a4c9cfe52cf61bc3ecfbaf2f5f1 Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Mon, 12 Jul 2021 21:08:20 +0200 Subject: [PATCH] add helper function for execute_values Make psycopg2's convenience function accessible through the cursor. --- nominatim/db/connection.py | 10 +++++++ nominatim/tokenizer/legacy_icu_tokenizer.py | 5 +--- nominatim/tokenizer/legacy_tokenizer.py | 6 ++-- nominatim/tools/postcodes.py | 33 ++++++++++----------- nominatim/tools/refresh.py | 5 ++-- 5 files changed, 32 insertions(+), 27 deletions(-) diff --git a/nominatim/db/connection.py b/nominatim/db/connection.py index 1eb3599b..1319ac16 100644 --- a/nominatim/db/connection.py +++ b/nominatim/db/connection.py @@ -26,6 +26,16 @@ class _Cursor(psycopg2.extras.DictCursor): super().execute(query, args) + + def execute_values(self, sql, argslist, template=None): + """ Wrapper for the psycopg2 convenience function to execute + SQL for a list of values. + """ + LOG.debug("SQL execute_values(%s, %s)", sql, argslist) + + psycopg2.extras.execute_values(self, sql, argslist, template=template) + + def scalar(self, sql, args=None): """ Execute query that returns a single value. The value is returned. If the query yields more than one row, a ValueError is raised. diff --git a/nominatim/tokenizer/legacy_icu_tokenizer.py b/nominatim/tokenizer/legacy_icu_tokenizer.py index 31e8c44b..7cd65ee7 100644 --- a/nominatim/tokenizer/legacy_icu_tokenizer.py +++ b/nominatim/tokenizer/legacy_icu_tokenizer.py @@ -9,8 +9,6 @@ import re from textwrap import dedent from pathlib import Path -import psycopg2.extras - from nominatim.db.connection import connect from nominatim.db.properties import set_property, get_property from nominatim.db.utils import CopyBuffer @@ -359,8 +357,7 @@ class LegacyICUNameAnalyzer: to_delete = existing_phrases - new_phrases if to_delete: - psycopg2.extras.execute_values( - cursor, + cursor.execute_values( """ DELETE FROM word USING (VALUES %s) as v(name, in_class, in_type, op) WHERE word = name and class = in_class and type = in_type and ((op = '-' and operator is null) or op = operator)""", diff --git a/nominatim/tokenizer/legacy_tokenizer.py b/nominatim/tokenizer/legacy_tokenizer.py index a68b69ec..c19dce2f 100644 --- a/nominatim/tokenizer/legacy_tokenizer.py +++ b/nominatim/tokenizer/legacy_tokenizer.py @@ -370,8 +370,7 @@ class LegacyNameAnalyzer: to_delete = existing_phrases - norm_phrases if to_add: - psycopg2.extras.execute_values( - cur, + cur.execute_values( """ INSERT INTO word (word_id, word_token, word, class, type, search_name_count, operator) (SELECT nextval('seq_word'), ' ' || make_standard_name(name), name, @@ -381,8 +380,7 @@ class LegacyNameAnalyzer: to_add) if to_delete and should_replace: - psycopg2.extras.execute_values( - cur, + cur.execute_values( """ DELETE FROM word USING (VALUES %s) as v(name, in_class, in_type, op) WHERE word = name and class = in_class and type = in_type and ((op = '-' and operator is null) or op = operator)""", diff --git a/nominatim/tools/postcodes.py b/nominatim/tools/postcodes.py index 3f8f4e38..d00fc97a 100644 --- a/nominatim/tools/postcodes.py +++ b/nominatim/tools/postcodes.py @@ -7,7 +7,7 @@ import gzip import logging from math import isfinite -from psycopg2.extras import execute_values +from psycopg2 import sql as pysql from nominatim.db.connection import connect @@ -52,27 +52,26 @@ class _CountryPostcodesCollector: with conn.cursor() as cur: if to_add: - execute_values(cur, - """INSERT INTO location_postcode - (place_id, indexed_status, country_code, - postcode, geometry) VALUES %s""", - to_add, - template="""(nextval('seq_place'), 1, '{}', - %s, 'SRID=4326;POINT(%s %s)') - """.format(self.country)) + cur.execute_values( + """INSERT INTO location_postcode + (place_id, indexed_status, country_code, + postcode, geometry) VALUES %s""", + to_add, + template=pysql.SQL("""(nextval('seq_place'), 1, {}, + %s, 'SRID=4326;POINT(%s %s)') + """).format(pysql.Literal(self.country))) if to_delete: cur.execute("""DELETE FROM location_postcode WHERE country_code = %s and postcode = any(%s) """, (self.country, to_delete)) if to_update: - execute_values(cur, - """UPDATE location_postcode - SET indexed_status = 2, - geometry = ST_SetSRID(ST_Point(v.x, v.y), 4326) - FROM (VALUES %s) AS v (pc, x, y) - WHERE country_code = '{}' and postcode = pc - """.format(self.country), - to_update) + cur.execute_values( + pysql.SQL("""UPDATE location_postcode + SET indexed_status = 2, + geometry = ST_SetSRID(ST_Point(v.x, v.y), 4326) + FROM (VALUES %s) AS v (pc, x, y) + WHERE country_code = {} and postcode = pc + """).format(pysql.Literal(self.country)), to_update) def _compute_changes(self, conn): diff --git a/nominatim/tools/refresh.py b/nominatim/tools/refresh.py index 6b18f67d..d4d75c31 100644 --- a/nominatim/tools/refresh.py +++ b/nominatim/tools/refresh.py @@ -5,7 +5,7 @@ import json import logging from textwrap import dedent -from psycopg2.extras import execute_values +from psycopg2 import sql as pysql from nominatim.db.utils import execute_file from nominatim.db.sql_preprocessor import SQLPreprocessor @@ -57,7 +57,8 @@ def load_address_levels(conn, table, levels): rank_search SMALLINT, rank_address SMALLINT)""".format(table)) - execute_values(cur, "INSERT INTO {} VALUES %s".format(table), rows) + cur.execute_values(pysql.SQL("INSERT INTO {} VALUES %s") + .format(pysql.Identifier(table)), rows) cur.execute('CREATE UNIQUE INDEX ON {} (country_code, class, type)'.format(table)) -- 2.39.5