Python psycopg2.DatabaseError() Examples

def load_sqlite_table(database_path, table_name):
    """Returns (table, connection). table is a pandas DataFrame."""
    conn = sqlite3.connect(database_path)
    try:
        df = pd.read_sql("SELECT * FROM %s" % table_name, conn)
        #  print("\nLoading %s table from SQLite3 database." % table_name)
    except DatabaseError as e:
        if 'no such table' in e.args[0]:
            print("\nNo such table: %s" % table_name)
            print("Create the table before loading it. " +
                  "Consider using the create_sqlite_table function")
            raise DatabaseError
        else:
            print(e)
            raise Exception("Failed to create %s table. Unknown error." %
                            table_name)
    return df, conn 

def _nodb_connection(self):
        nodb_connection = super(DatabaseWrapper, self)._nodb_connection
        try:
            nodb_connection.ensure_connection()
        except (Database.DatabaseError, WrappedDatabaseError):
            warnings.warn(
                "Normally Django will use a connection to the 'postgres' database "
                "to avoid running initialization queries against the production "
                "database when it's not needed (for example, when running tests). "
                "Django was unable to create a connection to the 'postgres' database "
                "and will use the default database instead.",
                RuntimeWarning
            )
            settings_dict = self.settings_dict.copy()
            settings_dict['NAME'] = settings.DATABASES[DEFAULT_DB_ALIAS]['NAME']
            nodb_connection = self.__class__(
                self.settings_dict.copy(),
                alias=self.alias,
                allow_thread_sharing=False)
        return nodb_connection 

def drop_repslot(self, slot_name):
        """
        Drop a physical replication slot using the streaming connection
        :param str slot_name: Replication slot name
        """
        cursor = self._cursor()
        try:
            # In the following query, the slot name is directly passed
            # to the DROP_REPLICATION_SLOT command, without any
            # quoting. This is a characteristic of the streaming
            # connection, otherwise if will fail with a generic
            # "syntax error"
            cursor.execute('DROP_REPLICATION_SLOT %s' % slot_name)
            _logger.info("Replication slot '%s' successfully dropped",
                         slot_name)
        except psycopg2.DatabaseError as exc:
            if exc.pgcode == UNDEFINED_OBJECT:
                # A replication slot with the that name does not exist
                raise PostgresInvalidReplicationSlot()
            if exc.pgcode == OBJECT_IN_USE:
                # The replication slot is still in use
                raise PostgresReplicationSlotInUse()
            else:
                raise PostgresException(force_str(exc).strip()) 

def create_physical_repslot(self, slot_name):
        """
        Create a physical replication slot using the streaming connection
        :param str slot_name: Replication slot name
        """
        cursor = self._cursor()
        try:
            # In the following query, the slot name is directly passed
            # to the CREATE_REPLICATION_SLOT command, without any
            # quoting. This is a characteristic of the streaming
            # connection, otherwise if will fail with a generic
            # "syntax error"
            cursor.execute('CREATE_REPLICATION_SLOT %s PHYSICAL' % slot_name)
            _logger.info("Replication slot '%s' successfully created",
                         slot_name)
        except psycopg2.DatabaseError as exc:
            if exc.pgcode == DUPLICATE_OBJECT:
                # A replication slot with the same name exists
                raise PostgresDuplicateReplicationSlot()
            elif exc.pgcode == CONFIGURATION_LIMIT_EXCEEDED:
                # Unable to create a new physical replication slot.
                # All slots are full.
                raise PostgresReplicationSlotsFull()
            else:
                raise PostgresException(force_str(exc).strip()) 

def _nodb_connection(self):
        nodb_connection = super(DatabaseWrapper, self)._nodb_connection
        try:
            nodb_connection.ensure_connection()
        except (Database.DatabaseError, WrappedDatabaseError):
            warnings.warn(
                "Normally Django will use a connection to the 'postgres' database "
                "to avoid running initialization queries against the production "
                "database when it's not needed (for example, when running tests). "
                "Django was unable to create a connection to the 'postgres' database "
                "and will use the default database instead.",
                RuntimeWarning
            )
            settings_dict = self.settings_dict.copy()
            settings_dict['NAME'] = settings.DATABASES[DEFAULT_DB_ALIAS]['NAME']
            nodb_connection = self.__class__(
                self.settings_dict.copy(),
                alias=self.alias,
                allow_thread_sharing=False)
        return nodb_connection 

def _nodb_connection(self):
        nodb_connection = super()._nodb_connection
        try:
            nodb_connection.ensure_connection()
        except (Database.DatabaseError, WrappedDatabaseError):
            warnings.warn(
                "Normally Django will use a connection to the 'postgres' database "
                "to avoid running initialization queries against the production "
                "database when it's not needed (for example, when running tests). "
                "Django was unable to create a connection to the 'postgres' database "
                "and will use the default database instead.",
                RuntimeWarning
            )
            settings_dict = self.settings_dict.copy()
            settings_dict['NAME'] = settings.DATABASES[DEFAULT_DB_ALIAS]['NAME']
            nodb_connection = self.__class__(
                self.settings_dict.copy(),
                alias=self.alias,
                allow_thread_sharing=False)
        return nodb_connection 

def setUp(self):
        ConnectingTestCase.setUp(self)

        curs = self.conn.cursor()
        # Drop table if it already exists
        try:
            curs.execute("DROP TABLE table1")
            self.conn.commit()
        except psycopg2.DatabaseError:
            self.conn.rollback()
        try:
            curs.execute("DROP TABLE table2")
            self.conn.commit()
        except psycopg2.DatabaseError:
            self.conn.rollback()
        # Create sample data
        curs.execute("""
            CREATE TABLE table1 (
                id int PRIMARY KEY,
                name text)
        """)
        curs.execute("INSERT INTO table1 VALUES (1, 'hello')")
        curs.execute("CREATE TABLE table2 (id int PRIMARY KEY)")
        self.conn.commit() 

def _create_dominant_index_table(self, _product):
        try:
            self.dominant_index_cur.execute(
                "CREATE TABLE {}"
                "("
                "TradingDay char(8) PRIMARY KEY,"
                "OpenPrice double precision,"
                "HighPrice double precision,"
                "LowPrice double precision,"
                "ClosePrice double precision,"
                "Volume integer,"
                "OpenInterest double precision"
                ")".format(_product))
            self.dominant_index_con.commit()
        except psycopg2.DatabaseError as e:
            logging.error(e)
            self.dominant_index_con.rollback()
            sys.exit(1) 

def test_deadlock(self):
        self.thread1_error = self.thread2_error = None
        step1 = threading.Event()
        step2 = threading.Event()

        def task1():
            try:
                conn = self.connect()
                curs = conn.cursor()
                curs.execute("LOCK table1 IN ACCESS EXCLUSIVE MODE")
                step1.set()
                step2.wait()
                curs.execute("LOCK table2 IN ACCESS EXCLUSIVE MODE")
            except psycopg2.DatabaseError, exc:
                self.thread1_error = exc
                step1.set()
            conn.close() 

def _create_product_index_table(self, _product):
        try:
            self.product_index_cur.execute(
                "CREATE TABLE {}"
                "("
                "TradingDay char(8) PRIMARY KEY,"
                "OpenPrice double precision,"
                "HighPrice double precision,"
                "LowPrice double precision,"
                "ClosePrice double precision,"
                "Volume integer,"
                "OpenInterest double precision"
                ")".format(_product))
            self.product_index_con.commit()
        except psycopg2.DatabaseError as e:
            logging.error(e)
            self.product_index_con.rollback()
            sys.exit(1) 

def _create_instrument_day_data_table(self, _instrument):
        try:
            self.instrument_day_data_cur.execute(
                "CREATE TABLE {}"
                "("
                "TradingDay char(8) PRIMARY KEY,"
                "OpenPrice double precision,"
                "HighPrice double precision,"
                "LowPrice double precision,"
                "ClosePrice double precision,"
                "SettlementPrice double precision,"
                "Volume integer,"
                "OpenInterest double precision,"
                "PreSettlementPrice double precision"
                ")".format(_instrument))
            self.instrument_day_data_con.commit()
        except psycopg2.DatabaseError as e:
            logging.error(e)
            self.instrument_day_data_con.rollback()
            sys.exit(1) 

def _nodb_connection(self):
        nodb_connection = super(DatabaseWrapper, self)._nodb_connection
        try:
            nodb_connection.ensure_connection()
        except (DatabaseError, WrappedDatabaseError):
            warnings.warn(
                "Normally Django will use a connection to the 'postgres' database "
                "to avoid running initialization queries against the production "
                "database when it's not needed (for example, when running tests). "
                "Django was unable to create a connection to the 'postgres' database "
                "and will use the default database instead.",
                RuntimeWarning
            )
            settings_dict = self.settings_dict.copy()
            settings_dict['NAME'] = settings.DATABASES[DEFAULT_DB_ALIAS]['NAME']
            nodb_connection = self.__class__(
                self.settings_dict.copy(),
                alias=self.alias,
                allow_thread_sharing=False)
        return nodb_connection 

def test_serialisation_failure(self):
        self.thread1_error = self.thread2_error = None
        step1 = threading.Event()
        step2 = threading.Event()

        def task1():
            try:
                conn = self.connect()
                curs = conn.cursor()
                curs.execute("SELECT name FROM table1 WHERE id = 1")
                curs.fetchall()
                step1.set()
                step2.wait()
                curs.execute("UPDATE table1 SET name='task1' WHERE id = 1")
                conn.commit()
            except psycopg2.DatabaseError, exc:
                self.thread1_error = exc
                step1.set()
            conn.close() 

def _nodb_connection(self):
        nodb_connection = super()._nodb_connection
        try:
            nodb_connection.ensure_connection()
        except (Database.DatabaseError, WrappedDatabaseError):
            warnings.warn(
                "Normally Django will use a connection to the 'postgres' database "
                "to avoid running initialization queries against the production "
                "database when it's not needed (for example, when running tests). "
                "Django was unable to create a connection to the 'postgres' database "
                "and will use the first PostgreSQL database instead.",
                RuntimeWarning
            )
            for connection in connections.all():
                if connection.vendor == 'postgresql' and connection.settings_dict['NAME'] != 'postgres':
                    return self.__class__(
                        {**self.settings_dict, 'NAME': connection.settings_dict['NAME']},
                        alias=self.alias,
                        allow_thread_sharing=False,
                    )
        return nodb_connection 

def getPatientListNegative(idFile):
	conn = None
	try:
		conn = psycopg2.connect ("dbname=medinfo user=Muthu host=localhost") #connect to database
		print("Connected to database")    
		curs = conn.cursor()
		patientNumbers = sys.argv[1:]
		for number in patientNumbers:
			fileName = idFile[:-4] + "Neg" + str(number) + ".txt"
			fileNameQuery = "COPY (select distinct patient_id from patient_item where patient_id % 10 = " + "-" + str(number) +") TO '" + str(fileName) + "';"
			curs.execute(fileNameQuery)
			print("Patient ID file created to: " + fileName)
		conn.commit()
		curs.close()
		conn.close()
		print("Connection closed")	
	except psycopg2.DatabaseError as ex:
		print('I am unable to connect the database: ')
		print(ex)
		sys.exit(1)

#getPatientList("/Users/Muthu/Desktop/JonathanChen/CDSS_checkedout/MuthuAnalysis/PatientIDFiles/patientIDs.txt") 

def query_member(cluster, cursor, member, role, command, connect_parameters):
    import psycopg2
    try:
        if cursor is None:
            cursor = get_cursor(cluster, connect_parameters, role=role, member=member)

        if cursor is None:
            if role is None:
                message = 'No connection to member {0} is available'.format(member)
            else:
                message = 'No connection to role={0} is available'.format(role)
            logging.debug(message)
            return [[timestamp(0), message]], None

        cursor.execute(command)
        return cursor.fetchall(), [d.name for d in cursor.description]
    except (psycopg2.OperationalError, psycopg2.DatabaseError) as oe:
        logging.debug(oe)
        if cursor is not None and not cursor.connection.closed:
            cursor.connection.close()
        message = oe.pgcode or oe.pgerror or str(oe)
        message = message.replace('\n', ' ')
        return [[timestamp(0), 'ERROR, SQLSTATE: {0}'.format(message)]], None 

def _nodb_connection(self):
        nodb_connection = super(DatabaseWrapper, self)._nodb_connection
        try:
            nodb_connection.ensure_connection()
        except (DatabaseError, WrappedDatabaseError):
            warnings.warn(
                "Normally Django will use a connection to the 'postgres' database "
                "to avoid running initialization queries against the production "
                "database when it's not needed (for example, when running tests). "
                "Django was unable to create a connection to the 'postgres' database "
                "and will use the default database instead.",
                RuntimeWarning
            )
            settings_dict = self.settings_dict.copy()
            settings_dict['NAME'] = settings.DATABASES[DEFAULT_DB_ALIAS]['NAME']
            nodb_connection = self.__class__(
                self.settings_dict.copy(),
                alias=self.alias,
                allow_thread_sharing=False)
        return nodb_connection 

def run(self, sql,  isolated=False):
    try:
        if isolated:
            self.conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) 
        cursor = self.conn.cursor()
        rows = cursor.execute(sql)
        self.conn.commit()
        return True,  None
    except  psycopg2.ProgrammingError as e:
        self._error_message(e)
        return None,  e
    except psycopg2.InternalError as e:
        self._error_message(e)
        return None,  e
    except psycopg2.DatabaseError as  e:
        self._error_message(e)
        return None,  e                       

  ## Gibt die Spalten eines Abfrageergebnisses zurck
  # @param abfrage string gewnschte SQL-Query
  # @return result array 1-Dim Array der Spaltennamen 

def _nodb_connection(self):
        nodb_connection = super()._nodb_connection
        try:
            nodb_connection.ensure_connection()
        except (Database.DatabaseError, WrappedDatabaseError):
            warnings.warn(
                "Normally Django will use a connection to the 'postgres' database "
                "to avoid running initialization queries against the production "
                "database when it's not needed (for example, when running tests). "
                "Django was unable to create a connection to the 'postgres' database "
                "and will use the default database instead.",
                RuntimeWarning
            )
            settings_dict = self.settings_dict.copy()
            settings_dict['NAME'] = settings.DATABASES[DEFAULT_DB_ALIAS]['NAME']
            nodb_connection = self.__class__(
                self.settings_dict.copy(),
                alias=self.alias,
                allow_thread_sharing=False)
        return nodb_connection 

def create_table(connect_str_or_path, use_sqlite, command):
    if use_sqlite:
        sqlite_path = connect_str_or_path
        conn = sqlite3.connect(sqlite_path)
        cur = conn.cursor()
        cur.execute(command)
        conn.commit()
        cur.close()
    else:
        conn = None
        try:
            db_connect_str = connect_str_or_path
            conn = psycopg2.connect(db_connect_str)
            cur = conn.cursor()
            cur.execute(command)
            conn.commit()
            cur.close()
        except (Exception, psycopg2.DatabaseError) as error:
            if "already exists" not in str(error):
                print(error)
        finally:
            if conn is not None:
                conn.close() 

def connect(self, **kwargs):
        assert self.database
        cdb = self._connectData()
        cdb = dict((x, y) for (x, y) in cdb.iteritems() if y is not None)
        try:
            self.dbh = psycopg2.connect(**cdb)
        except psycopg2.DatabaseError:
            raise sqlerrors.DatabaseError("Could not connect to database", cdb)
        self.tempTables = sqllib.CaselessDict()
        c = self.cursor()
        c.execute("""
        select c.relname as tablename from pg_class c
        where c.relnamespace = pg_my_temp_schema()
          and c.relkind = 'r'::"char"
        """)
        for table, in c.fetchall():
            self.tempTables[table] = sqllib.Llist()
        self.closed = False
        return True 

def Defect_Dojo_Output(Title, Description):
    DD_Details = Load_Defect_Dojo_Configuration()

    if DD_Details:

        try:
            Impact = 'All Scrummage findings have the potential to cause significant damage to a business\' finances, efficiency and reputation. Therefore, findings should be investigated to assist in reducing this risk.'
            Mitigation = 'It is recommended that this issue be investigated further by the security team to determine whether or not further action needs to be taken.'
            DD_Connection = defectdojo.DefectDojoAPI(DD_Details[1], DD_Details[0], DD_Details[2], debug=False)
            Finding = DD_Connection.create_finding(Title, Description, 'Low', '', str(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S').strftime('%Y-%m-%d')), DD_Details[4], DD_Details[3], DD_Details[5], DD_Details[6], Impact, True, False, Mitigation)

            try:
                Finding = str(int(str(Finding)))
                logging.info(f"{Date()} Connectors Library - DefectDojo finding {Finding} created.")

            except:
                logging.info(f"{Date()} Connectors Library - Failed to create DefectDojo finding.")

        except (Exception, psycopg2.DatabaseError) as Error:
            logging.warning(Date() + str(Error)) 

def getPatientList(idFile):
	conn = None
	try:
		conn = psycopg2.connect ("dbname=medinfo user=Muthu host=localhost") #connect to database
		print("Connected to database")    
		curs = conn.cursor()
		patientNumbers = sys.argv[1:]
		for number in patientNumbers:
			fileName = idFile[:-4] + str(number) + ".txt"
			fileNameQuery = "COPY (select distinct patient_id from patient_item where patient_id % 10 = " + str(number) +") TO '" + str(fileName) + "';"
			curs.execute(fileNameQuery)
			print("Patient ID file created to: " + fileName)
		conn.commit()
		curs.close()
		conn.close()
		print("Connection closed")	
	except psycopg2.DatabaseError as ex:
		print('I am unable to connect the database: ')
		print(ex)
		sys.exit(1)

# Does the same as above, except for the negative numbers 

def task2():
            try:
                conn = self.connect()
                curs = conn.cursor()
                step1.wait()
                curs.execute("LOCK table2 IN ACCESS EXCLUSIVE MODE")
                step2.set()
                curs.execute("LOCK table1 IN ACCESS EXCLUSIVE MODE")
            except psycopg2.DatabaseError, exc:
                self.thread2_error = exc
                step2.set() 

def read(self, sql,  Message=False):
        datensatz = None
        if self.conn != None:
            try:
                column_names = []
                cursor = self.conn.cursor()
                column_names = self.cols(sql)
                cursor.execute(sql)
                self.conn.commit()
                rows = cursor.fetchall()
                
                if len(rows) > 0:
                    datensatz = {}
                    i = 0
                    
                    for col in column_names:
                        result = []
                        for row in rows:
                            result.append(row[i])
                        i = i + 1
                        datensatz.update({col.upper(): result})
                    cursor.close()  
                return datensatz,  None

            except  psycopg2.ProgrammingError as e:
                self._error_message(e)
                return None,  e
            except psycopg2.InternalError as e:
                self._error_message(e)
                return None,  e
            except psycopg2.DatabaseError as  e:
                self._error_message(e)
                return None,  e                

   # do stuff                

  ## Schliesst die DB-Verbindung 

def test_binary_protocol_error(connect):
    conn = await connect()
    s = socket.fromfd(conn._fileno, socket.AF_INET, socket.SOCK_STREAM)
    s.send(b'garbage')
    s.detach()
    cur = await conn.cursor()
    with pytest.raises(psycopg2.DatabaseError):
        await cur.execute('SELECT 1') 

def task2():
            try:
                conn = self.connect()
                curs = conn.cursor()
                step1.wait()
                curs.execute("UPDATE table1 SET name='task2' WHERE id = 1")
                conn.commit()
            except psycopg2.DatabaseError, exc:
                self.thread2_error = exc 

def _validate_connection_str(in_str):
    """
    Returns a psycopg2.Connection object if the connection string is valid
    :param in_str: The input connection string
    :return:       A connection object, if string is valid
    """
    try:
        return psycopg2.connect(in_str)
    except psycopg2.DatabaseError:
        raise IOError("Invalid connection string.") 

def assert_connected(cls):
        """
        Raises an exception if there is no connection to the database.

        """
        if cls._connection is None or cls._connection.closed != 0:
            raise psycopg2.DatabaseError(
                'There is no connection to a database, '
                'call connection.connect to create one.') 

def _handle_events(database, events):
    block_num, block_id = _parse_new_block(events)
    try:
        is_duplicate = _resolve_if_forked(database, block_num, block_id)
        if not is_duplicate:
            _apply_state_changes(database, events, block_num, block_id)
        database.commit()
    except psycopg2.DatabaseError as err:
        LOGGER.exception('Unable to handle event: %s', err)
        database.rollback()