Python sqlite3.IntegrityError() Examples

def add_user(user, password, ut):
    hashed_pass = hash_pass(password)

    # Here we set the db file path.
    db = Path(os.path.join(gazee.DATA_DIR, gazee.DB_NAME))

    # Here we make the inital DB connection that we will be using throughout this function.
    connection = sqlite3.connect(str(db))
    c = connection.cursor()
    try:
        c.execute('INSERT INTO {tn} ({un}, {pw}, {ut}) VALUES (?,?,?)'.format(tn=gazee.USERS, un=gazee.USERNAME, pw=gazee.PASSWORD, ut=gazee.TYPE), (user, hashed_pass, ut,))
    except sqlite3.IntegrityError:
        logging.info("User %s Already Exists" % user)
        return False
    finally:
        connection.commit()
        connection.close()

    logging.info("User %s Added" % (user))
    return True 

def span(self, key):
        name = key.rstrip('/')
        self.logger.debug("Creating container %s", name)
        query = "INSERT into %s VALUES (?, '')"
        setdata = query % (self.table,)
        try:
            conn = sqlite3.connect(self.dburi)
            with conn:
                c = conn.cursor()
                self._create(c)
                c.execute(setdata, (name,))
        except sqlite3.IntegrityError as err:
            raise CSStoreExists(str(err))
        except sqlite3.Error:
            self.logger.exception("Error creating key %s", name)
            raise CSStoreError('Error occurred while trying to span container') 

def set(self, key, value, replace=False):
        self.logger.debug("Setting key %s to value %s (replace=%s)",
                          key, value, replace)
        if key.endswith('/'):
            raise ValueError('Invalid Key name, cannot end in "/"')
        if replace:
            query = "INSERT OR REPLACE into %s VALUES (?, ?)"
        else:
            query = "INSERT into %s VALUES (?, ?)"
        setdata = query % (self.table,)
        try:
            conn = sqlite3.connect(self.dburi)
            with conn:
                c = conn.cursor()
                self._create(c)
                c.execute(setdata, (key, value))
        except sqlite3.IntegrityError as err:
            raise CSStoreExists(str(err))
        except sqlite3.Error:
            self.logger.exception("Error storing key %s", key)
            raise CSStoreError('Error occurred while trying to store key') 

def insert_table(self, table_name, column_name_value_dict, max_retries=5):
        sqlstr, value_tuple = generate_insert_sqlstr_value_tuple(table_name=table_name, item=column_name_value_dict)
        cursor = self.conn.cursor()
        try:
            cursor.execute(sqlstr, value_tuple)
            self.conn.commit()
        except sqlite3.IntegrityError:
            logging.error("Duplicated")
            print ("Duplicated")
        except sqlite3.OperationalError:
            remaining_retries = max_retries - 1
            if remaining_retries > 0:
                sleep(randint(self.MIN_SLEEP, self.MAX_SLEEP))
                logging.error("database is locked, retrying...")
                print ("Retrying: %s, %s" % (sqlstr, value_tuple))
                self.insert_table(table_name=table_name, column_name_value_dict=column_name_value_dict,
                                  max_retries=remaining_retries)
            else:
                logging.error("database is locked, max-retry reached")
                raise 

def update_table(self, table_name, set_name_value_dict, where_name_value_dict, max_retries=5):
        # This is not single process and multi-thread program, so we are not using threading locks.
        sqlstr, value_tuple = generate_update_sqlstr_value_tuple(
            table_name=table_name, set_name_value_dict=set_name_value_dict,
            where_name_value_dict=where_name_value_dict)
        cursor = self.conn.cursor()
        try:
            cursor.execute(sqlstr, value_tuple)
            self.conn.commit()
        except sqlite3.IntegrityError:
            logging.error("Update Error")
            print ("Update Error")
        except sqlite3.OperationalError:
            remaining_retries = max_retries - 1
            if remaining_retries > 0:
                sleep(randint(self.MIN_SLEEP, self.MAX_SLEEP))
                logging.error("database is locked, retrying...")
                print ("Retrying: %s, %s" % (sqlstr, value_tuple))
                self.update_table(table_name=table_name, set_name_value_dict=set_name_value_dict,
                                  where_name_value_dict=where_name_value_dict, max_retries=remaining_retries)
            else:
                logging.error("database is locked, max-retry reached")
                raise 

def addData(self,data,table):
    try:
      #None is for the primary key which auto increments
      if type(data) is list or type(data) is tuple:
        self.c.execute('INSERT INTO %s VALUES (%s)'%(table,('?,'*len(data))[:-1]),data)
      elif type(data) is dict:
        keys = list(data.keys())
        #keys = data.keys()
        try:
          values = [data[k] for k in keys]
          #values = list(data.values())
          #values = data.values()
        except KeyError:
          raise DataBaseInvalidInput('sanitized column names don\'t match given column names')
        self.c.execute('INSERT INTO %s (%s) VALUES (%s)'%(table , ','.join(keys) , ','.join(['?']*len(data))) , values)
      else:
        raise Exception('invalid input type: %s'%type(data))
      id = self.c.lastrowid
      #remember to commit changes so we don't lock the db!
      self.conn.commit()
      return id
    except sqlite3.IntegrityError as e:
      raise DataBaseIntegrityError('%s' %e) 

def user(conn, config, User):
    try:
        time_ms = round(time.time()*1000)
        cursor = conn.cursor()
        user = [int(User.id), User.id, User.name, User.username, User.bio, User.location, User.url,User.join_date, User.join_time, User.tweets, User.following, User.followers, User.likes, User.media_count, User.is_private, User.is_verified, User.avatar, User.background_image]

        hex_dig = hashlib.sha256(','.join(str(v) for v in user).encode()).hexdigest()
        entry = tuple(user) + (hex_dig,time_ms,)
        old_hash = get_hash_id(conn, User.id)

        if old_hash == -1 or old_hash != hex_dig:
            query = f"INSERT INTO users VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
            cursor.execute(query, entry)
        else:
            pass

        if config.Followers or config.Following:
            table = uTable(config.Followers)
            query = f"INSERT INTO {table} VALUES(?,?)"
            cursor.execute(query, (config.User_id, int(User.id)))

        conn.commit()
    except sqlite3.IntegrityError:
        pass 

def insert(self, ip_address, log_msg, country=""):
        """
        Inserts a row into sqlite

        Args:
            ip_address: IP address to be inserted into sqlite
            log_msg: Reason as to why the IP is banned
            country: Country of where the IP is from
        """
        cursor = None

        try:
            cursor = self.sqlite_connection.cursor()
            cursor.execute(
                "INSERT INTO banned_ip(ip, time_banned, server_name, log_msg, country) VALUES (?, ?, ?, ?, ?)",
                (ip_address, time.time(), "Server-1", log_msg, country)
            )

            self.sqlite_connection.commit()
        except sqlite3.IntegrityError:
            print("IP already in the database")
        finally:
            if cursor is not None:
                cursor.close() 

def insert_many(conn, items, table_name):
    table_name = scrub(table_name)
    sql = "INSERT INTO {} ('name', 'price', 'quantity') VALUES (?, ?, ?)".format(
        table_name
    )
    entries = list()
    for x in items:
        entries.append((x["name"], x["price"], x["quantity"]))
    try:
        conn.executemany(sql, entries)
        conn.commit()
    except IntegrityError as e:
        print(
            '{}: at least one in {} was already stored in table "{}"'.format(
                e, [x["name"] for x in items], table_name
            )
        ) 

def insertHaveMask(self, channel, infohash, peer_id, havemask, timestamp = None):
        query = QUERIES['SELECT METADATA']
        if timestamp is None:
            timestamp = int(time.time())
        channel = bin2str(channel)
        infohash = bin2str(infohash)
        peer_id = bin2str(peer_id)
        res = self._db.fetchall(query, (infohash, channel))
        if len(res) != 1:
            raise MetadataDBException('No entry in the MetadataDB for %s, %s' % (channel[-10:], infohash))
        metadata_fk = res[0][0]
        insertQuery = QUERIES['INSERT HAVE MASK']
        try:
            self._db.execute_write(insertQuery, (metadata_fk,
             peer_id,
             havemask,
             timestamp))
        except sqlite3.IntegrityError as e:
            raise MetadataDBException(str(e)) 

def CheckLastRowIDInsertOR(self):
        results = []
        for statement in ('FAIL', 'ABORT', 'ROLLBACK'):
            sql = 'INSERT OR {} INTO test(unique_test) VALUES (?)'
            with self.subTest(statement='INSERT OR {}'.format(statement)):
                self.cu.execute(sql.format(statement), (statement,))
                results.append((statement, self.cu.lastrowid))
                with self.assertRaises(sqlite.IntegrityError):
                    self.cu.execute(sql.format(statement), (statement,))
                results.append((statement, self.cu.lastrowid))
        expected = [
            ('FAIL', 2), ('FAIL', 2),
            ('ABORT', 3), ('ABORT', 3),
            ('ROLLBACK', 4), ('ROLLBACK', 4),
        ]
        self.assertEqual(results, expected) 

def insert_or_update(self, table_name: str, data: Dict[str, Any]):
        try:
            await self.insert(table_name, data)
        except sqlite3.IntegrityError as e:
            # Hack to get primary key out of error message
            # Error : ` UNIQUE constraint failed: myTable.id `
            e = repr(e)
            replaces = "'`()"
            for s in replaces:
                e = e.replace(s, "")
            _key = e.split("UNIQUE constraint failed:")[-1]
            _key = _key.split(table_name + ".")[-1]

            _keyval = data.pop(_key)
            conditions = [[[_key, "=", _keyval]]]
            await self.update(table_name, data, conditions) 

def image_entry(self, d, identity=None):
        """Enters a single ase-db image into the fingerprint database.
        The ase-db ID with identity must be unique. If not, it will be skipped.

        This table can be expanded to contain atoms objects in the future.

        Parameters
        ----------
        d : ase-db object
            Database entry to parse.
        identity : str
            An identifier of the users choice.

        Returns
        -------
        ase_id : int
            The ase id collected.
        """
        ase_id = d.id

        try:
            self.c.execute("""INSERT INTO images (ase_id, identity)
            VALUES(?, ?)""", (ase_id, identity))
        except (IntegrityError):
            if self.verbose:
                print('ASE ID with identifier already defined: {} {}'.format(
                    ase_id, identity))

        return ase_id 

def save_pathways(self, pathways, broken_bonds=None):
        """Save enumerated pathways the ReactionNetwork database.
        More than two reactants or two products is not supported.

        Parameters
        ----------
        pathways : list
            Sorted pathways in the form [R1, R2, P1, P2].
        broken_bonds : list
            Comma separated strings of index associated with the
            two atoms whos bond is broken. List order must match
            pathways.
        """
        for i, path in enumerate(pathways):
            R, P = path
            P1, P2 = P
            R1, R2 = R

            if broken_bonds is not None:
                bbond = broken_bonds[i]
            else:
                bbond = None

            try:
                self.c.execute("""INSERT INTO reactions
                    (product1, product2, reactant1, reactant2, broken_bond)
                    VALUES(?, ?, ?, ?, ?)""",
                               (int(P1), int(P2), int(R1), int(R2), bbond))
            except (sqlite3.IntegrityError):
                pass 

def CheckExceptions(self):
        # Optional DB-API extension.
        self.assertEqual(self.cx.Warning, sqlite.Warning)
        self.assertEqual(self.cx.Error, sqlite.Error)
        self.assertEqual(self.cx.InterfaceError, sqlite.InterfaceError)
        self.assertEqual(self.cx.DatabaseError, sqlite.DatabaseError)
        self.assertEqual(self.cx.DataError, sqlite.DataError)
        self.assertEqual(self.cx.OperationalError, sqlite.OperationalError)
        self.assertEqual(self.cx.IntegrityError, sqlite.IntegrityError)
        self.assertEqual(self.cx.InternalError, sqlite.InternalError)
        self.assertEqual(self.cx.ProgrammingError, sqlite.ProgrammingError)
        self.assertEqual(self.cx.NotSupportedError, sqlite.NotSupportedError) 

def CheckIntegrityError(self):
        self.assertTrue(issubclass(sqlite.IntegrityError, sqlite.DatabaseError),
                        "IntegrityError is not a subclass of DatabaseError") 

def add(self, block, transactions):
        'Add transactions to the database.'

        # expand the database if necessary
        self.check_size()

        # check the merkle root of the transactions against the block
        block._check_merkle_root(util.get_merkle_root(transactions))

        # for each transaction...
        connections = dict()
        block_txns = [ ]
        for (txn_index, txn) in enumerate(transactions):

            # ...get the database to save to
            txid = txn.hash
            q = get_q(txid)
            connection = self.get_connection(self._N, q)
            connections[(self._N, q % self._N)] = connection

            # ...insert
            cursor = connection.cursor()
            txck = keys.get_txck(block._blockid, txn_index)
            row = (txck, keys.get_hint(txid), buffer(txn.binary()))
            try:
                cursor.execute(self.sql_insert, row)

            # (duplicates don't matter)
            except sqlite3.IntegrityError, e:
                if e.message != _KEY_DUP:
                    raise e

            # wrap up the transaction for the returned block
            block_txns.append(Transaction(self, row, txn))

        # commit the transactions to the databases 

def insert_one(conn, name, price, quantity, table_name):
    table_name = scrub(table_name)
    sql = "INSERT INTO {} ('name', 'price', 'quantity') VALUES (?, ?, ?)".format(
        table_name
    )
    try:
        conn.execute(sql, (name, price, quantity))
        conn.commit()
    except IntegrityError as e:
        raise mvc_exc.ItemAlreadyStored(
            '{}: "{}" already stored in table "{}"'.format(e, name, table_name)
        ) 

def parameter_entry(self, symbol=None, description=None):
        """Enters a unique parameter into the database.

        Parameters
        ----------
        symbol : str
            A unique symbol the entry can be referenced by. If None,
            the symbol will be the ID of the parameter
            as a string.
        description : str
            A description of the parameter.
        """
        if not symbol:
            self.c.execute("""SELECT MAX(pid) FROM parameters""")
            symbol = str(int(self.c.fetchone()[0]) + 1)

        # The symbol must be unique. If not, it will be skipped.
        try:
            self.c.execute("""INSERT INTO parameters (symbol, description)
            VALUES(?, ?)""", (symbol, description))
        except (IntegrityError):
            if self.verbose:
                print('Symbol already defined: {}'.format(symbol))

        # Each instance needs to be commited to ensure no overwriting.
        # This could potentially result in slowdown.
        self.con.commit() 

def fingerprint_entry(self, ase_id, param_id, value):
        """Enter fingerprint value to database for given ase and parameter ID.

        Parameters
        ----------
        ase_id : int
            The ase unique ID associated with an atoms object in the database.
        param_id : int or str
            The parameter ID or symbol associated with and entry in the
            paramters table.
        value : float
            The value of the parameter for the atoms object.
        """
        # If parameter symbol is given, get the ID
        if isinstance(param_id, str):
            self.c.execute("""SELECT pid FROM parameters
            WHERE symbol = ?""", (param_id,))
            param_id = self.c.fetchone()

            if param_id:
                param_id = param_id[0]
            else:
                raise(KeyError, 'parameter symbol not found')

        self.c.execute("""SELECT iid FROM images
        WHERE ase_id = ?""", (ase_id,))
        image_id = self.c.fetchone()[0]

        try:
            self.c.execute("""INSERT INTO fingerprints (image_id, param_id, value)
            VALUES(?, ?, ?)""", (str(image_id), int(param_id), float(value)))
        except(IntegrityError):
            if self.verbose:
                print('Fingerprint already defined: {}, {}, {}'.format(
                    image_id, param_id, value)) 

def parameter_entry(self, symbol=None, description=None):
        """Function for entering unique parameters into the database.

        Parameters
        ----------
        symbol : str
            A unique symbol the entry can be referenced by. If None, the symbol
            will be the ID of the parameter as a string.
        description : str
            A description of the parameter.
        """
        # If no symbol is provided, use the parameter ID (str).
        if not symbol:
            self.c.execute("""SELECT MAX(pid) FROM parameters""")
            symbol = str(int(self.c.fetchone()[0]) + 1)

        # The symbol must be unique. If not, it will be skipped.
        try:
            self.c.execute("""INSERT INTO parameters (symbol, description)
            VALUES(?, ?)""", (symbol, description))
        except(IntegrityError):
            if self.verbose:
                print('Symbol already defined: {}'.format(symbol))

        # Each instance needs to be commited to ensure no overwriting.
        # This could potentially result in slowdown.
        self.con.commit() 

def image_entry(self, asedb_entry=None, identity=None):
        """Enter a single ase-db image into the fingerprint database.

        This table can be expanded to contain atoms objects in the future.

        Parameters
        ----------
        d : object
            An ase-db object which can be parsed.
        identity : str
            An identifier of the users choice.

        Returns
        -------
        d.id : int
            The ase ID colleted for the ase-db object.
        """
        asedb_entry.toatoms()

        # ase-db ID with identity must be unique. If not, it will be skipped.
        try:
            self.c.execute("""INSERT INTO images (ase_id, identity)
            VALUES(?, ?)""", (asedb_entry.unique_id, identity))
        except(IntegrityError):
            if self.verbose:
                print('ASE ID with identifier already defined: {} {}'.format(
                    asedb_entry.id,
                    identity))

        return asedb_entry.id 

def store_in_db(g_id, g_name):
	conn = sqlite3.connect("gesture_db.db")
	cmd = "INSERT INTO gesture (g_id, g_name) VALUES (%s, \'%s\')" % (g_id, g_name)
	try:
		conn.execute(cmd)
	except sqlite3.IntegrityError:
		choice = input("g_id already exists. Want to change the record? (y/n): ")
		if choice.lower() == 'y':
			cmd = "UPDATE gesture SET g_name = \'%s\' WHERE g_id = %s" % (g_name, g_id)
			conn.execute(cmd)
		else:
			print("Doing nothing...")
			return
	conn.commit() 

def sql_add(elem, name, db_name):
    conn = sqlite3.connect(db_name)
    c = conn.cursor()
    col_name = name + 'Sequence'
    q = ("INSERT INTO " + str(name) + " (" + str(col_name)
         + ") VALUES ('" + elem + "')")
    try:
        c.execute(q)
        conn.commit()
    except sqlite3.IntegrityError:
        print("Already In Database", elem)
    c.close()
    conn.close() 

def follow(conn, Username, Followers, User):
    try:
        time_ms = round(time.time()*1000)
        cursor = conn.cursor()
        entry = (User, time_ms, Username,)
        table = fTable(Followers)
        query = f"INSERT INTO {table} VALUES(?,?,?)"
        cursor.execute(query, entry)
        conn.commit()
    except sqlite3.IntegrityError:
        pass 

def __visit(self, system, planet):
        if self.db is None:
            return
        try:
            now = EDTime.py_epoch_now()
            self.db.execute('UPDATE hotspots SET last_visit= CASE WHEN last_visit < ? THEN (?) ELSE (last_visit) END WHERE name=? and planet=?', (self.__replenished_margin(), now, system, planet))
            self.db.commit()
        except sqlite3.IntegrityError:
            pass 

def copy_to_table(self, table_from, table_to, condition):
        """
        Copies entries from "table_from" to "table_to" that satisfy the field condition.

        :param table_from: (String) name of table from which to copy values
        :param table_to: (string) name of table to which to copy values
        :param condition: (tuple) Tuple with two entries which specify the search condition. Example:
                                    ("id", "12345")     The first element is the column and the second is the value.
        """
        sql_command = "INSERT INTO {} SELECT * FROM {}".format(table_to, table_from)
        conditional = " WHERE {}=?".format(condition[0])
        try:
            self.cursor.execute(sql_command + conditional, (condition[1], ))
        except sqlite3.IntegrityError:
            pass 

def add_subtitle_download_data_to_db(self, download_data: tuple):
        update_sql = "INSERT INTO download_subs(subs_id, bytecode) VALUES(?, ?)"
        sub_id, byte_data = download_data
        # byte_data is a regular string here!!
        # The conversion to byte data happens at the moment it is being written to a file
        try:
            self.cursor.execute(update_sql, (int(sub_id), byte_data))
        except sqlite3.IntegrityError:
            pass 

def add_subtitle_search_data_to_db(self, subtitle_download_payload):
        update_sql = "INSERT INTO search_subs(subs_id, movie_id, file_name, path) VALUES(?, ?, ?, ?)"
        try:
            self.cursor.execute(update_sql, (int(subtitle_download_payload["IDSubtitleFile"]),
                                             subtitle_download_payload["imdbid"],
                                             subtitle_download_payload["file name"],
                                             subtitle_download_payload["movie directory"]))
        except sqlite3.IntegrityError:
            pass 

def CheckIntegrityError(self):
        self.assertTrue(issubclass(sqlite.IntegrityError, sqlite.DatabaseError),
                        "IntegrityError is not a subclass of DatabaseError")