Python psycopg2.OperationalError() Examples

def init_db_connection(connect_str):
    """Initializes database connection using the specified Flask app.

    Configuration file must contain `SQLALCHEMY_DATABASE_URI` key. See
    https://pythonhosted.org/Flask-SQLAlchemy/config.html#configuration-keys
    for more info.
    """
    global engine
    while True:
        try:
            engine = create_engine(connect_str, poolclass=NullPool)
            print("Connection to db established!")
            break
        except psycopg2.OperationalError as e:
            print("Couldn't establish connection to db: {}".format(str(e)))
            print("Sleeping 2 seconds and trying again...")
            time.sleep(2) 

def execute(self, host, port='5432', user=None, password=None, database='postgres', ssl='disable', timeout='10'):

    try:
      with Timing() as timing:
        psycopg2.connect(host=host, port=int(port), user=user, password=password, database=database, sslmode=ssl, connect_timeout=int(timeout))

      code, mesg = '0', 'OK'

    except psycopg2.OperationalError as e:
      logger.debug('OperationalError: %s' % e)
      code, mesg = '1', str(e)[:-1]

    return self.Response(code, mesg, timing)

# }}}

# HTTP {{{ 

def wait_on_postgres(retries=5, migrate=True):
    """Block until Postgres is ready (optionally, run any migrations)

    Shamelessly appropriated from https://github.com/agconti/wait-for-postgres
    """
    dsn = os.environ.get('DATABASE_URL')

    @retry(retries, exceptions=(psycopg2.OperationalError,))
    def wait():
        con = psycopg2.connect(**psycopg2.extensions.parse_dsn(dsn))
        con.close()
        log.info('Postgres is ready!')

    wait()
    if migrate:
        log.info('Running database migrations, if any')
        with application.app_context():
            flask_migrate.upgrade(directory=MIGRATION_DIR) 

def _dataframe(self, sql, bindings, chunksize=None):
        with timer("dataframe:"):
            try:
                return pandas.read_sql_query(sql=sql, con=self._connection, params=bindings, chunksize=chunksize)
            except (sqlalchemy.exc.DBAPIError, Psycopg2OperationalError, SnowflakeProgrammingError) as e:
                if not self._transactions and (isinstance(e, Psycopg2OperationalError) or e.connection_invalidated):
                    logger.warning('Reconnect and retry due to invalid connection')
                    self.close()
                    return pandas.read_sql_query(sql=sql, con=self._connection, params=bindings, chunksize=chunksize)
                elif not self._transactions and (isinstance(e, SnowflakeProgrammingError) or e.connection_invalidated):
                    if hasattr(e, 'msg') and e.msg and "authenticate" in e.msg.lower():
                        logger.warning('Reconnect and retry due to unauthenticated connection')
                        self.close()
                        return pandas.read_sql_query(sql=sql, con=self._connection, params=bindings, chunksize=chunksize)
                    else:
                        logger.warning('Failed to execute db query with error type {}. Reason : {}'.format(type(e).__name__, e.msg))
                        raise
                else:
                    raise 

def __execute(self, sql, bindings):
        try:
            return self._connection.execute(sql, bindings)
        except (sqlalchemy.exc.DBAPIError, Psycopg2OperationalError, SnowflakeProgrammingError) as e:
            if not self._transactions and (isinstance(e, Psycopg2OperationalError) or e.connection_invalidated):
                logger.warning('Reconnect and retry due to invalid connection')
                self.close()
                return self._connection.execute(sql, bindings)
            elif not self._transactions and (isinstance(e, SnowflakeProgrammingError) or e.connection_invalidated):
                if hasattr(e, 'msg') and e.msg and "authenticate" in e.msg.lower():
                    logger.warning('Reconnect and retry due to unauthenticated connection')
                    self.close()
                    return self._connection.execute(sql, bindings)
                else:
                    logger.warning('Failed to execute db query with error type {}. Reason : {}'.format(type(e).__name__, e.msg))
                    raise
            else:
                raise 

def monitor():
    global conn, emergency
    import psycopg2
    while True:
        time.sleep(10)
        with db_lock:
            if conn:
                break
            try:
                conn = psycopg2.connect("dbname='{}' user='{}' host='{}' password='{}'".format(config.get('db_logger.database'),
                                                                                            config.get('db_logger.username'),
                                                                                            config.get('db_logger.host'),
                                                                                            config.get('db_logger.password')))
            except psycopg2.OperationalError as e:
                con = None
            else:
                break 

def __init__(self):

        self.log = open('.database_actions.log', 'a')
        
        self.database_name  = "odds_data"
        self.matches_table  = "matches"
        
        self.odds_cols      = ['1', 'X', '2']
        self.timestamp_col  = "timestamp"
        
        self.teams          = teams()

        try:
            self.connect()

        except psycopg2.OperationalError:
            self.create_database()
            self.connect()

        self.cursor = self.connection.cursor(cursor_factory = psycopg2.extras.DictCursor)
        self.create_table() 

def test_overwrite_schema(self):
        """
        Verifies option to overwrite connection schema
        """

        sql = "SELECT 1;"
        op = PostgresOperator(
            task_id='postgres_operator_test_schema_overwrite',
            sql=sql,
            dag=self.dag,
            autocommit=True,
            database="foobar",
        )

        from psycopg2 import OperationalError
        try:
            op.run(start_date=DEFAULT_DATE, end_date=DEFAULT_DATE,
                   ignore_ti_state=True)
        except OperationalError as e:
            assert 'database "foobar" does not exist' in str(e) 

def database_connect(autocommit=False):
    """
    Repeatedly try to connect to the downstream (API) database until successful.
    :return: A database connection object
    """
    while True:
        try:
            conn = psycopg2.connect(
                dbname=DATABASE_NAME,
                user=DATABASE_USER,
                password=DATABASE_PASSWORD,
                host=DATABASE_HOST,
                port=DATABASE_PORT,
                connect_timeout=5,
            )
            if autocommit:
                conn.set_session(autocommit=True)
        except psycopg2.OperationalError as e:
            log.exception(e)
            log.error('Reconnecting to database in 5 seconds. . .')
            time.sleep(5)
            continue
        break

    return conn 

def connect_to_master():
    citus_host    = environ.get('CITUS_HOST', 'master')
    postgres_pass = environ.get('POSTGRES_PASSWORD', '')
    postgres_user = environ.get('POSTGRES_USER', 'postgres')
    postgres_db   = environ.get('POSTGRES_DB', postgres_user)

    conn = None
    while conn is None:
        try:
            conn = psycopg2.connect("dbname=%s user=%s host=%s password=%s" %
                                    (postgres_db, postgres_user, citus_host, postgres_pass))
        except psycopg2.OperationalError as error:
            print("Could not connect to %s, trying again in 1 second" % citus_host)
            sleep(1)
        except (Exception, psycopg2.Error) as error:
            raise error
        
    conn.autocommit = True

    print("connected to %s" % citus_host, file=stderr)

    return conn

# main logic loop for the manager 

def connect_as_super_user(db_name, conf):
    db_connection = None
    try:
        db_connection = psycopg2.connect(database=db_name)
        db_connection.autocommit = True
    except psycopg2.OperationalError:
        LOG.info("could not connect as local superuser with current user, credentials required")

    if not db_connection:
        superuser, password = query_user_for_superuser_credentials()
        db_connection = get_db_connection(db_name, superuser, password,
                                          host=conf['postgres']['host'],
                                          port=int(conf['postgres']['port']))

    if not db_connection or db_connection.closed:
        raise Exception("failed connecting the database: " + db_name)

    return db_connection 

def openDB(self):
		self.log.info( "NSLookup Opening DB...")
		try:
			self.conn = psycopg2.connect(dbname=settings.DATABASE_DB_NAME, user=settings.DATABASE_USER,password=settings.DATABASE_PASS)
		except psycopg2.OperationalError:
			self.conn = psycopg2.connect(host=settings.DATABASE_IP, dbname=settings.DATABASE_DB_NAME, user=settings.DATABASE_USER,password=settings.DATABASE_PASS)

		# self.conn.autocommit = True
		self.log.info("opened")

		with self.conn.cursor() as cur:
			cur.execute('''SELECT tablename FROM pg_catalog.pg_tables WHERE tablename='%s';''' % self.mode["table"])
			rets = cur.fetchall()

		self.conn.commit()
		if rets:
			rets = rets[0]
		if not self.mode["table"] in rets:   # If the DB doesn't exist, set it up.
			self.log.warning("DB Not setup for %s.", self.mode["table"])
			if self.mode['failOnMissing']:
				raise ValueError
			else:
				# We can't preload the dict, since it doesn't exist, so disable preloading.
				pass 

def database_status():
    try:
        psycopg2.connect(
            dbname=os.environ['DB_NAME'],
            user=os.environ['DB_USER'],
            password=os.environ['DB_PASS'],
            host=os.environ['DB_HOST'],
            port=os.environ['DB_PORT'],
            sslmode=os.environ['DB_SSLMODE'],
            sslcert=os.environ['DB_SSLCERT'],
            sslkey=os.environ['DB_SSLKEY'],
            sslrootcert=os.environ['DB_SSLROOTCERT'],
        )
    except psycopg2.OperationalError:
        time.sleep(3)
        return False
    else:
        return True 

def test_invalid_login(self):
    """Tests if postgres server responds correctly to a invalid login attempt."""

    def postgresql_login():
      try:
        psycopg2.connect("postgres://scott:tiger@0.0.0.0:2504/")
      except psycopg2.OperationalError as e:
        return e
      return None

    options = {'enabled': 'True', 'port': 2504}
    postgresql_cap = postgresql.PostgreSQL(options, self.loop)

    server_coro = asyncio.start_server(
        postgresql_cap.handle_session, '0.0.0.0', 2504, loop=self.loop)
    self.server = self.loop.run_until_complete(server_coro)

    postgresql_task = self.loop.run_in_executor(None, postgresql_login)
    login_exception = self.loop.run_until_complete(postgresql_task)

    self.assertIsInstance(login_exception, psycopg2.OperationalError)
    self.assertEqual(
        str(login_exception),
        'FATAL:  password authentication failed for user "scott"\n') 

def connect():
    """ Return a connection to the database"""
    try:
        if (not hasattr(connect, "conn") or connect.conn is None or connect.conn.closed != 0):
            cattr = dict(
                user=DB_USER,
                password=DB_PASSWORD,
                database=DB_NAME
            )
            if DB_HOST is not None:
                cattr.update(dict(
                            host=DB_HOST,
                            port=DB_PORT,
                            ))
            connect.conn = psycopg2.connect(**cattr)
            connect.conn.set_client_encoding('UTF8')
        return connect.conn
    except psycopg2.OperationalError as pgoe:
        logger.error(pgoe.message)
        raise
    except Exception:
        logger.error("Failed to connect to database.")
        raise 

def insert_phash_link_many(self, link_tuples):
		try:
			with self.transaction() as cur:
				for item_1, item_2, distance in link_tuples:
					item_1, item_2 = min(item_1, item_2), max(item_1, item_2)
					cur.execute(''' INSERT INTO
									{table}_plink (item_1_link, item_2_link, distance)
								VALUES (%s, %s, %s)
								ON CONFLICT DO NOTHING
							'''.format(table=self.tableName), (item_1, item_2, distance))
		except (psycopg2.OperationalError, psycopg2.IntegrityError):
			for item_1, item_2, distance in link_tuples:
				item_1, item_2 = min(item_1, item_2), max(item_1, item_2)
				try:
					with self.transaction() as cur:
						cur.execute(''' INSERT INTO
										{table}_plink (item_1_link, item_2_link, distance)
									VALUES (%s, %s, %s)
									ON CONFLICT DO NOTHING
								'''.format(table=self.tableName), (item_1, item_2, distance))
				except (psycopg2.OperationalError, psycopg2.IntegrityError):
					self.log.error("Failure inserting link between %s and %s with distance %s. Skipping", distance, item_1, item_2) 

def _gather_metrics(self):
        stats = {}
        try:
            for inject_method in self._select_stats_to_emit:
                stats = inject_method(stats)
        except psycopg2.OperationalError as exc:
            logging.exception("METRICS: error while gathering metrics")
            stats = {
                "health": {
                    "health": 0,
                    "diagnosis": "Database error: {}".format(str(exc)),
                }
            }
        except Exception:
            logging.exception("METRICS: error while gathering metrics")
            stats = {
                "health": {
                    "health": 4,
                    "diagnosis": "Unable to retrieve metrics",
                }
            }
        finally:
            return stats 

def execute(self, host, port='5432', user=None, password=None, database='postgres', ssl='disable', timeout='10'):

    try:
      with Timing() as timing:
        psycopg2.connect(host=host, port=int(port), user=user, password=password, database=database, sslmode=ssl, connect_timeout=int(timeout))

      code, mesg = '0', 'OK'

    except psycopg2.OperationalError as e:
      logger.debug('OperationalError: %s' % e)
      code, mesg = '1', str(e)[:-1]

    return self.Response(code, mesg, timing)

# }}}

# HTTP {{{ 

def database_connection(database_url):
    parsed = urlparse(database_url)
    user = parsed.username
    password = parsed.password
    host = parsed.hostname
    port = parsed.port
    database = parsed.path.strip('/')

    try:
        connection = psycopg2.connect(
            host=host,
            port=port,
            user=user,
            password=password,
            database=database,
            cursor_factory=RealDictCursor)
    except psycopg2.OperationalError:
        raise UnableToConnectToDatabase
    connection.set_session(autocommit=True)

    return connection 

def wait_select_inter(conn):
    while 1:
        try:
            state = conn.poll()
            if state == POLL_OK:
                break
            elif state == POLL_READ:
                select([conn.fileno()], [], [])
            elif state == POLL_WRITE:
                select([], [conn.fileno()], [])
            else:
                raise conn.OperationalError(
                    "bad state from poll: %s" % state)
        except KeyboardInterrupt:
            conn.cancel()
            # the loop will be broken by a server error
            continue 

def wait_select(conn, timeout=5.0):
    end_time = time.monotonic() + timeout
    while time.monotonic() < end_time:
        time_left = end_time - time.monotonic()
        state = conn.poll()
        try:
            if state == psycopg2.extensions.POLL_OK:
                return
            if state == psycopg2.extensions.POLL_READ:
                select.select([conn.fileno()], [], [], min(timeout, time_left))
            elif state == psycopg2.extensions.POLL_WRITE:
                select.select([], [conn.fileno()], [], min(timeout, time_left))
            else:
                raise psycopg2.OperationalError("bad state from poll: %s" % state)
        except select.error as error:
            if error.args[0] != errno.EINTR:
                raise
    raise PglookoutTimeout("timed out in wait_select") 

def connect(self):


		if all([
			hasattr(self, '_psqlDbIpAddr'),
			hasattr(self, '_psqlDbName'),
			hasattr(self, '_psqlUserName'),
			hasattr(self, '_psqlUserPass'),
			]):
			pass
		else:
			try:
				# hook up login creds (overridden for tests)
				self._psqlDbIpAddr = settings.PSQL_IP
				self._psqlDbName   = settings.PSQL_DB_NAME
				self._psqlUserName = settings.PSQL_USER
				self._psqlUserPass = settings.PSQL_PASS
			except:
				print("WARNING: DB Credentials not available. Is this a test environment?")

		try:
			self.conn = psycopg2.connect(dbname  = self._psqlDbName,
										user     = self._psqlUserName,
										password = self._psqlUserPass)

		except psycopg2.OperationalError:
			self.conn = psycopg2.connect(host    = self._psqlDbIpAddr,
										dbname   = self._psqlDbName,
										user     = self._psqlUserName,
										password = self._psqlUserPass) 

def _connect(self, db_host, db_port, db_username, db_password, db_name, **kwargs):
    """
    Overrides _connect method in SqlConn for the simpler connection offered by
    the PostgreSQL adapter.

    Args:
      db_host: String for data base host.
      db_port: String for data base port.
      db_username: String for data base user.
      db_password: String for data base password corresponding to given user.
      db_name: String for table schema that is to be used.

    Returns:
      If the connection is successful, the active database connection is returned.

    Raises:
      DsConnError: Thrown in case of an error during connection.
    """
    connArgs = { 'host':     db_host
               , 'port':     int(db_port)
               , 'user':     db_username
               , 'password': db_password
               , 'dbname':   db_name
               }
    try:
      import psycopg2
      db            = psycopg2.connect(**connArgs)
      db.autocommit = True
      return db
    except psycopg2.OperationalError as err:
      raise DsConnError('OperationalError: ' + str(err.message))


  ### Convenience functions for common queries 

def read(self, query, params=None):
    """Helper function that converts results returned stored in a
    Psycopg cursor into a less temperamental list format. Note that
    there IS recursive retry logic here; when the connection to the
    database is dropped, the query will fail, prompting this method
    to re-connect and try the query again. This will continue trying
    to reconnect indefinitely. This is probably not ideal.

    Arguments:
      - query: The SQL query to be executed.
      - params: Any parameters to be substituted into the query. It's
          important to let Psycopg handle this rather than using Python
          string interpolation because it helps mitigate SQL injection.
    Returns:
      - A list of tuples, one for each row of results.

    """

    results = []
    try:
      with self.db.cursor() as cursor:
        if params is not None:
          cursor.execute(query, params)
        else:
          cursor.execute(query)
        for result in cursor:
          results.append(result)
      return results
    except psycopg2.OperationalError as e:
      print(f"ERROR with db query execution: {e}")
      print("Reconnecting.")
      self._attempt_connect()
      print("Sending query again.")
      return self.read(query, params) 

def wait_for_connection(conn_string):
    retry_limit = 20

    while retry_limit:
        try:
            psycopg2.connect(conn_string)
            return True
        except psycopg2.OperationalError:
            pass

        time.sleep(0.2)
        retry_limit -= 1

    assert retry_limit == 0
    raise Exception('too many retries for db at {conn_string}'.format(conn_string=conn_string)) 

def watcher_thread(conn_string, run_id_dict, handlers_dict, dict_lock, watcher_thread_exit):

    try:
        for notif in await_pg_notifications(
            conn_string,
            channels=[CHANNEL_NAME],
            timeout=POLLING_CADENCE,
            yield_on_timeout=True,
            exit_event=watcher_thread_exit,
        ):
            if notif is None:
                if watcher_thread_exit.is_set():
                    break
            else:
                run_id, index_str = notif.payload.split('_')
                if run_id not in run_id_dict:
                    continue

                index = int(index_str)
                with dict_lock:
                    handlers = handlers_dict.get(run_id, [])

                engine = create_engine(
                    conn_string, isolation_level='AUTOCOMMIT', poolclass=db.pool.NullPool
                )
                try:
                    res = engine.execute(
                        db.select([SqlEventLogStorageTable.c.event]).where(
                            SqlEventLogStorageTable.c.id == index
                        ),
                    )
                    dagster_event = deserialize_json_to_dagster_namedtuple(res.fetchone()[0])
                finally:
                    engine.dispose()

                for (cursor, callback) in handlers:
                    if index >= cursor:
                        callback(dagster_event)
    except psycopg2.OperationalError:
        pass 

def pg_is_responsive(ip, docker_setup):
    try:
        conn = psycopg2.connect(
            "host={} user={} password={} dbname={}".format(
                ip,
                docker_setup['postgres']['user'],
                docker_setup['postgres']['password'],
                'postgres'
            )
        )
        conn.close()
        return True
    except psycopg2.OperationalError as exp:
        return False 

def _start_connection(self):
        log('starting connection')
        try:
            self._conn = psycopg2.connect(self._conn_string)
            self._conn.autocommit = self._autocommit
        except OperationalError:
            log('failed to connect to {}'.format(self._conn_string))
            message = '''Failed to connect to a database at {}'''.format(self._conn_string)
            self.send_response(self.iopub_socket, 'stream',
                               {'name': 'stderr', 'text': message}) 

def main():
    # getting the env variables
    get_env('INFRABOX_VERSION')
    get_env('INFRABOX_DATABASE_DB')
    get_env('INFRABOX_DATABASE_USER')
    get_env('INFRABOX_DATABASE_PASSWORD')
    get_env('INFRABOX_DATABASE_HOST')
    get_env('INFRABOX_DATABASE_PORT')
    server_port = os.environ.get('INFRABOX_PORT', 8080)

    # Copied from review.py, could be changed over time
    conn = connect_db()
    conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)

    # When restarting or starting on a docker container, the port can be still used at the first try
    if not start_server(2, 5, server_port):
        return 1

    active_job_gauge = ActiveJobClusterGauge('job_active_current_count')
    rsc_gauge = AllocatedRscGauge('rsc_current_count', conn)
    all_job_gauge = AllJobNodeGauge('job_all_node_count')

    while running:
        try:
            active_job_gauge.update(conn)
            rsc_gauge.update(conn)
            all_job_gauge.update(conn)
        except psycopg2.OperationalError:
            # the db connection closed unexpectedly
            conn = connect_db()
            conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
        # experimental value
        time.sleep(1.3) 

def execute(self, query):
        retVal = False

        try:
            self.cursor.execute(query)
            retVal = True
        except (psycopg2.OperationalError, psycopg2.ProgrammingError), msg:
            logger.warn(("(remote) %s" % msg).strip())