From d475cb999f56d1b48a8ee3300ed65b6bfd891c14 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc" Date: Fri, 23 Feb 2024 10:42:34 -0600 Subject: [PATCH] Upgrade from psycopg2 to psycopg3; drop python <= v3.5, add v3.8-v3.11 --- setup.py | 11 ++- src/pgwui_core/core.py | 152 ++++++++++++++++++++++++----------- src/pgwui_core/exceptions.py | 8 +- tox.ini | 8 +- 4 files changed, 121 insertions(+), 58 deletions(-) diff --git a/setup.py b/setup.py index 19ebaa8..5136dba 100644 --- a/setup.py +++ b/setup.py @@ -110,10 +110,12 @@ setup( # Specify the Python versions you support here. In particular, ensure # that you indicate whether you support Python 2, Python 3 or both. 'Programming Language :: Python :: 3', - 'Programming Language :: Python :: 3.4', - 'Programming Language :: Python :: 3.5', 'Programming Language :: Python :: 3.6', 'Programming Language :: Python :: 3.7', + 'Programming Language :: Python :: 3.8', + 'Programming Language :: Python :: 3.9', + 'Programming Language :: Python :: 3.10', + 'Programming Language :: Python :: 3.11', ], # What does your project relate to? @@ -134,7 +136,7 @@ setup( 'library', 'Postgres', 'PostgreSQL', - 'psycopg2', + 'psycopg3', 'Pyramid', 'software development', 'SQL', @@ -156,8 +158,9 @@ setup( # Run-time dependencies. install_requires=[ + 'attrs', 'markupsafe', - 'psycopg2', + 'psycopg', 'wtforms', ], diff --git a/src/pgwui_core/core.py b/src/pgwui_core/core.py index 7ad71d6..aa3d4bb 100644 --- a/src/pgwui_core/core.py +++ b/src/pgwui_core/core.py @@ -1,4 +1,4 @@ -# Copyright (C) 2013, 2014, 2015, 2018, 2020, 2021 The Meme Factory, Inc. +# Copyright (C) 2013, 2014, 2015, 2018, 2020, 2021, 2024 The Meme Factory, Inc. # http://www.karlpinc.com/ # This file is part of PGWUI_Core. @@ -61,8 +61,8 @@ from wtforms import ( PasswordField, FileField) -import psycopg2 -import psycopg2.extensions +import psycopg +import psycopg.errors from pgwui_core.constants import ( CHECKED, @@ -630,7 +630,7 @@ def escape_eol(string): def format_exception(ex): '''Return an exception formatted as suffix text for a log message.''' - if isinstance(ex, psycopg2.DatabaseError): + if isinstance(ex, psycopg.DatabaseError): diag = ex.diag msg = diag.message_primary if hasattr(diag, 'message_detail'): @@ -657,16 +657,16 @@ class SQLCommand(object): An SQL command that returns nothing Attributes: - stmt The statement, formatted for psycopg2 substitution + stmt The statement, formatted for psycopg3 substitution args Tuple of arguments used to substitute when executed. ''' def __init__(self, stmt, args, ec=None): ''' - stmt The statement, formatted for psycopg2 substitution + stmt The statement, formatted for psycopg3 substitution args Tuple of arguments used to substitute when executed. ec(ex) Produces the exception to raise an instance of on failure Input: - ex The exception raised by psycopg2 + ex The exception raised by psycopg3 ''' super(SQLCommand, self).__init__() self.stmt = stmt @@ -678,15 +678,15 @@ class SQLCommand(object): Execute the sql statement. Input: - cur A psycopg2 cursor + cur A psycopg3 cursor Side effects: Does something in the db. - Can raise a psycopg2 error + Can raise a psycopg3 error ''' try: cur.execute(self.stmt, self.args) - except psycopg2.DatabaseError as ex: + except psycopg.DatabaseError as ex: if self.ec is None: raise ex else: @@ -698,11 +698,11 @@ class LogSQLCommand(SQLCommand): def __init__(self, stmt, args, ec=None, log_success=None, log_failure=None): ''' - stmt The statement, formatted for psycopg2 substitution + stmt The statement, formatted for psycopg3 substitution args Tuple of arguments used to substitute when executed. ec(ex) Produces the exception to raise an instance of on failure Input: - ex The exception raised by psycopg2 + ex The exception raised by psycopg3 ''' super(LogSQLCommand, self).__init__(stmt, args, ec) self.log_success = log_success @@ -713,15 +713,15 @@ class LogSQLCommand(SQLCommand): Execute the sql statement. Input: - cur A psycopg2 cursor + cur A psycopg3 cursor Side effects: Does something in the db. - Can raise a psycopg2 error + Can raise a psycopg3 error ''' try: super(LogSQLCommand, self).execute(cur) - except (core_ex.UploadError, psycopg2.DatabaseError) as ex: + except (core_ex.UploadError, psycopg.DatabaseError) as ex: if self.log_failure: self.log_failure(ex) raise @@ -865,7 +865,7 @@ class UploadData(DBData): def mapper(st): st = do_trim(st) - # psycopg2 maps None to NULL + # psycopg3 maps None to NULL return None if st == null_rep else st self._mapper = mapper else: @@ -988,7 +988,7 @@ class UploadData(DBData): @attr.s class ParameterExecutor(): - '''Execute a parameterized pscopg2 statement + '''Execute a parameterized psycopg3 statement Must be mixed in with a DataLineProcessor. ''' def param_execute(self, insert_stmt, udl): @@ -999,37 +999,54 @@ class ParameterExecutor(): udl.lineno, 'Line has too few columns', 'Fewer columns than column headings', - f'The IndexError from psycopg2 is: ({exp})', + f'The IndexError from psycopg3 is: ({exp})', data=udl.raw) except UnicodeEncodeError as exp: - self.raise_encoding_error(exp, udl) + self.raise_encoding_error( + exp, udl, self.cur.connection.encoding, False) + except psycopg.errors.UntranslateableCharacter as exp: + self.raise_encoding_error( + exp, udl, self.ue.server_encoding(), True) - def raise_encoding_error(self, exp, udl): + def raise_encoding_error(self, exp, udl, encoding, server_side): errors = [] cnt = 1 - enc = psycopg2.extensions.encodings[self.cur.connection.encoding] + if server_side: + description = ("Data cannot be represented in the" + " character encoding of the database") + else: + description = ("Data cannot be represented in the database" + " connection's client-side character encoding") for col in udl.tuples: try: - col.encode(encoding=enc) - except UnicodeEncodeError as detailed_exp: + col.encode(encoding=encoding) + except UnicodeEncodeError as col_exp: + if server_side: + reported_error = str(exp) + else: + reported_error = str(col_exp) errors.append(core_ex.EncodingError( udl.lineno, - ("Data cannot be represented in the database's character" - " encoding"), + description, (f'The data ({col}) in column' - f' {cnt} contains an un-representable bit sequence;' + f' {cnt} contains the bit sequence' + f' ({col[col_exp.start:col_exp.end]}), in the bits' + f' numbered {col_exp.start + 1} through {col_exp.end},' + ' that are not able to' + f' be represented in the (Python) {encoding} character' + ' encoding;' ' the reported error is:'), - str(detailed_exp), + reported_error, data=udl.raw)) cnt += 1 if errors: raise core_ex.MultiDataLineError(errors) raise core_ex.EncodingError( udl.lineno, - ("Data cannot be represented in the database's character" - " encoding"), - ('Cannot discover which column contains an un-representable' - ' bit sequence, the reported error is:'), + description, + ('Cannot discover which column contains a' + ' bit sequence that cannot be represented in the (Python)' + f' {encoding} character encoding; the reported error is:'), str(exp), data=udl.raw) @@ -1044,7 +1061,7 @@ class DataLineProcessor(object): Attributes: ue UploadEngine instance uh UploadHandler instance - cur psycopg2 cursor + cur psycopg3 cursor Methods: eat(udl) Given an UploadDataLine instance put the line in the db. @@ -1074,7 +1091,7 @@ class NoOpProcessor(DataLineProcessor): ''' ue UploadEngine instance uh UploadHandler instance - cur psycopg2 cursor + cur psycopg3 cursor ''' super(NoOpProcessor, self).__init__(ue, uh) @@ -1092,7 +1109,7 @@ class ExecuteSQL(DataLineProcessor): ''' ue UploadEngine instance uh UploadHandler instance - cur psycopg2 cursor + cur psycopg3 cursor ''' super(ExecuteSQL, self).__init__(ue, uh) @@ -1399,7 +1416,7 @@ class DBConnector(object): Attributes: uh An UploadHandler instance. - cur A psycopg2 cursor instance + cur A psycopg3 cursor instance db Name of db to connect to user User to connect to db password Password to connect to db @@ -1409,6 +1426,9 @@ class DBConnector(object): Methods: run() Get a DataLineProcessor instance from the upload handler's factory and feed it by iterating over data. + server_encoding() + Return the python standard encoding of the server_encoding + used in the database. ''' def __init__(self, uh): @@ -1419,6 +1439,7 @@ class DBConnector(object): # Configuration and response management. self.uh = uh + self._server_encoding = None def call_alter_db(self, conn): ''' @@ -1503,7 +1524,7 @@ class DBConnector(object): errors.extend(ex.errors) except core_ex.PGWUIError as ex: errors.append(ex) - except psycopg2.DatabaseError as ex: + except psycopg.DatabaseError as ex: errors.append(core_ex.DBSetupError(ex)) else: try: @@ -1519,6 +1540,15 @@ class DBConnector(object): self.cur.close() return errors + def _get_client_encoding(self, conn): + '''Return the client-side encoding as a Python standard encoding + + Input: conn A psycopg connection + ''' + encoding = conn.info.encoding + conn.close() + return encoding + def call_with_connection(self, func): ''' Validate input, connect to the db, and do something with @@ -1538,6 +1568,14 @@ class DBConnector(object): Side effects: Raises errors, calls func(conn) ''' + return self._call_with_encoded_connection(func, None) + + def _call_with_encoded_connection(self, func, client_encoding): + + '''Validate input, connect to the db with a specific client + encoding, and do something with the connection. See + call_with_connection(). + ''' errors = [] havecreds = False response = {} @@ -1557,13 +1595,12 @@ class DBConnector(object): if not errors: registry = self.uh.request.registry try: - conn = psycopg2.connect( - database=self.db, + conn = psycopg.connect( + dbname=self.db, user=self.user, password=self.password, host=registry.settings['pgwui'].get('pg_host'), - port=registry.settings['pgwui'].get('pg_port')) - except psycopg2.OperationalError: + except psycopg.OperationalError: errors = [self.authfailerror_factory()] havecreds = False else: @@ -1573,6 +1610,18 @@ class DBConnector(object): self.uh.session.update({'havecreds': havecreds}) return (errors, response) + def server_encoding(self): + '''Return the server-side encoding, as a Python standard encoding. + ''' + # This does the lame and easy thing and gets the encoding + # from a new connection; by supplying '' as the client + # encoding, the server sets the client encoding to the server encoding. + if self._server_encoding is None: + encoding = self._call_with_encoded_connection( + self._get_client_encoding, '') + self._server_encoding = encoding + return self._server_encoding + def read_uh(self): '''Read data into the upload handler.''' self.uh.read() @@ -1641,7 +1690,7 @@ class NoTransactionEngine(DBConnector): Attributes: uh An UploadHandler instance. data An UploadData instance of the uploaded data - cur A psycopg2 cursor instance + cur A psycopg3 cursor instance db Name of db to connect to user User to connect to db password Password to connect to db @@ -1651,6 +1700,9 @@ class NoTransactionEngine(DBConnector): Methods: run() Get a DataLineProcessor instance from the upload handler's factory and feed it by iterating over data. + server_encoding() + Return the python standard encoding of the server_encoding + used in the database. ''' def __init__(self, uh): ''' @@ -1698,7 +1750,7 @@ class NoTransactionEngine(DBConnector): else: try: processor.eat(udl) - except psycopg2.DatabaseError as ex: + except psycopg.DatabaseError as ex: errors.append(core_ex.DBDataLineError(udl, ex)) except (core_ex.DataLineError, core_ex.DBError) as ex: errors.append(ex) @@ -1713,7 +1765,7 @@ class UnsafeUploadEngine(DBConnector): Attributes: uh An UploadHandler instance. data An UploadData instance of the uploaded data - cur A psycopg2 cursor instance + cur A psycopg3 cursor instance db Name of db to connect to user User to connect to db password Password to connect to db @@ -1723,6 +1775,9 @@ class UnsafeUploadEngine(DBConnector): Methods: run() Get a DataLineProcessor instance from the upload handler's factory and feed it by iterating over data. + server_encoding() + Return the python standard encoding of the server_encoding + used in the database. eat_old_line(udl, thunk) Trap errors raised by the db while running thunk. Report any errors as due to the udl UploadDataLine @@ -1759,7 +1814,7 @@ class UnsafeUploadEngine(DBConnector): else: try: conn.commit() - except psycopg2.DatabaseError as ex: + except psycopg.DatabaseError as ex: errors.append(core_ex.DBCommitError(ex)) conn.close() return errors @@ -1774,7 +1829,7 @@ class UnsafeUploadEngine(DBConnector): ''' try: result = thunk() - except psycopg2.DatabaseError as ex: + except psycopg.DatabaseError as ex: raise core_ex.DBDataLineError(udl, ex) else: return result @@ -1801,7 +1856,7 @@ class UnsafeUploadEngine(DBConnector): 'SAVEPOINT line_savepoint;') try: processor.eat(udl) - except psycopg2.DatabaseError as ex: + except psycopg.DatabaseError as ex: self.cur.execute( 'ROLLBACK TO line_savepoint;') errors.append(core_ex.DBDataLineError(udl, ex)) @@ -1825,7 +1880,7 @@ class UploadEngine(UnsafeUploadEngine): Attributes: uh An UploadHandler instance. - cur A psycopg2 cursor instance + cur A psycopg3 cursor instance db Name of db to connect to user User to connect to db password Password to connect to db @@ -1836,6 +1891,9 @@ class UploadEngine(UnsafeUploadEngine): Methods: run() Get a DataLineProcessor instance from the upload handler's factory and feed it by iterating over data. + server_encoding() + Return the python standard encoding of the server_encoding + used in the database. ''' def __init__(self, uh): diff --git a/src/pgwui_core/exceptions.py b/src/pgwui_core/exceptions.py index c90536c..02c8af6 100644 --- a/src/pgwui_core/exceptions.py +++ b/src/pgwui_core/exceptions.py @@ -181,10 +181,10 @@ class DataInconsistencyError(SetupError): class DBError(SetupError): - '''psycopg2 raised an error''' + '''psycopg3 raised an error''' def __init__(self, pgexc, e='process your request'): ''' - pgexc The psycopg2 exception object + pgexc The psycopg3 exception object e Description of what PG was doing ''' super(DBError, self).__init__( @@ -194,7 +194,7 @@ class DBError(SetupError): def html_blockquote(self, ex): ''' - Produce an html formatted message from a psycopg2 DatabaseError + Produce an html formatted message from a psycopg3 DatabaseError exception. ''' primary = html_escape(ex.diag.message_primary) @@ -227,7 +227,7 @@ class DBDataLineError(DBError): def __init__(self, udl, pgexc): ''' udl An UploadDataLine instance - pgexc The psycopg2 exception object + pgexc The psycopg3 exception object ''' super(DBDataLineError, self).__init__(pgexc) self.lineno = udl.lineno diff --git a/tox.ini b/tox.ini index dfccb6a..bfa6093 100644 --- a/tox.ini +++ b/tox.ini @@ -1,12 +1,14 @@ [tox] -envlist = py{34,35,36,37} +envlist = py{36,37,38,39,310,311} [testenv] basepython = - py34: python3.4 - py35: python3.5 py36: python3.6 py37: python3.7 + py38: python3.8 + py39: python3.9 + py310: python3.10 + py311: python3.11 deps = check-manifest cmarkgfm -- 2.34.1