Python sqlite3.OperationalError() Examples

def _DetectApplicationUsageTable(self, conn):
    """Detect whether the application usage table exists.

    Args:
      conn: sqlite3.Connection object
    Returns:
      True if the table exists, False if not.
    Raises:
      sqlite3.Error: if error occurs
    """
    try:
      conn.execute(APPLICATION_USAGE_TABLE_DETECT)
      exists = True
    except sqlite3.OperationalError, e:
      if e.args[0].startswith('no such table'):
        exists = False
      else:
        raise 

def create_webinfo_db(self):
        try:
            cursor = self.conn.cursor()
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS webinfo (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                time varchar(255),
                domain varchar(255),
                waf varchar(255) DEFAULT '',
                title varchar(255) DEFAULT '',
                apps varchar(255) DEFAULT '',
                server varchar(255) DEFAULT '',
                address varchar(255) DEFAULT '',
                ipaddr varchar(255) DEFAULT '',
                os varchar(255) DEFAULT '',
                md5 varchar(40) UNIQUE
                )
                """)
        except sqlite3.OperationalError as e:
            pass 

def extract(source, query):
    """Get records from source database.

    Args:
        source: File path to the source database where we want to extract the
        data from.
        query: The query string to be executed in order to retrieve relevant
        attributes as (datetime, url, time) from the source database according
        to the browser chosen.
    """
    try:
        conn = open_db(source)
        cursor = conn.cursor()
        cursor.execute(query)
        history = cursor.fetchall()
        conn.close()
        return history
    except sqlite3.OperationalError as op_e:
        raise Error('Could not perform queries on the source database: '
                    '{}'.format(op_e)) 

def _set_pragmas(self, conn):
        # `multiversion` is weird. It checks first whether another connection
        # from the BTree cache is available, and then switches to that, which
        # may have the handle of the DB_Env. If that happens, then we get
        # an error stating that you cannot set `multiversion` despite the
        # fact we have not done any operations and it's a brand new conn.
        if self._pragmas:
            cursor = conn.cursor()
            for pragma, value in self._pragmas:
                if pragma == 'multiversion':
                    try:
                        cursor.execute('PRAGMA %s = %s;' % (pragma, value))
                    except berkeleydb.OperationalError:
                        pass
                else:
                    cursor.execute('PRAGMA %s = %s;' % (pragma, value))
            cursor.close() 

def import_session(cookiefile, sessionfile):
    print("Using cookies from {}.".format(cookiefile))
    conn = connect(cookiefile)
    try:
        cookie_data = conn.execute(
            "SELECT name, value FROM moz_cookies WHERE baseDomain='instagram.com'"
        )
    except OperationalError:
        cookie_data = conn.execute(
            "SELECT name, value FROM moz_cookies WHERE host LIKE '%instagram.com'"
        )
    instaloader = Instaloader(max_connection_attempts=1)
    instaloader.context._session.cookies.update(cookie_data)
    username = instaloader.test_login()
    if not username:
        raise SystemExit("Not logged in. Are you logged in successfully in Firefox?")
    print("Imported session cookie for {}.".format(username))
    instaloader.context.username = username
    instaloader.save_session_to_file(sessionfile) 

def LogApplicationUsage(self, event, bundle_id, app_version, app_path):
    """Log application usage.

    Args:
      event: str, like "launch" or "quit"
      bundle_id: str
      app_version: str
      app_path: str
    """
    if bundle_id is None and app_version is None and app_path is None:
      return

    try:
      now = int(time.time())
      conn = self._Connect()
      if not self._DetectApplicationUsageTable(conn):
        self._CreateApplicationUsageTable(conn)
      self._InsertApplicationUsage(
          conn,
          event, bundle_id, app_version, app_path, now)
      conn.commit()
    except sqlite3.OperationalError, e:
      logging.error('Error writing %s event to database: %s', event, e)
      self._Close(conn) 

def sql_query(dbname, query):
    """
    Execute an SQL query over a database.
    :param dbname: filename of persistent store
    :type schema: str
    :param query: SQL query
    :type rel_name: str
    """
    import sqlite3
    try:
        path = nltk.data.find(dbname)
        connection =  sqlite3.connect(str(path))
        cur = connection.cursor()
        return cur.execute(query)
    except (ValueError, sqlite3.OperationalError):
        import warnings
        warnings.warn("Make sure the database file %s is installed and uncompressed." % dbname)
        raise 

def create_if_required(self):
    # called once to create db
      c = self.conn.cursor()
      try:
        c.execute('''create table imgs (
                          id integer primary key autoincrement,
                          filename text
                     )''')
        c.execute('''create table labels (
                          img_id integer,
                          x integer,
                          y integer
                     )''')
      except sqlite3.OperationalError:
        # assume table already exists? clumsy...
        pass 

def __init__(self, dbfile):
        if dbfile == ":memory:":
            raise ValueError("We don't support the sqlite :memory: database type. Just use the default volatile in-memory store.")
        self.dbfile = dbfile
        with sqlite3.connect(dbfile) as db:
            db.execute("PRAGMA foreign_keys=ON")
            try:
                db.execute("SELECT COUNT(*) FROM pyro_names").fetchone()
            except sqlite3.OperationalError:
                # the table does not yet exist
                self._create_schema(db)
            else:
                # check if we need to update the existing schema
                try:
                    db.execute("SELECT COUNT(*) FROM pyro_metadata").fetchone()
                except sqlite3.OperationalError:
                    # metadata schema needs to be created and existing data migrated
                    db.execute("ALTER TABLE pyro_names RENAME TO pyro_names_old")
                    self._create_schema(db)
                    db.execute("INSERT INTO pyro_names(name, uri) SELECT name, uri FROM pyro_names_old")
                    db.execute("DROP TABLE pyro_names_old")
            db.commit() 

def __load_affix(self,):
        """
        load affix form data base into self.affixdict, to speed up search
        """
        sql  =  u"select * FROM affix"
        try:
            self.cursor.execute(sql)
        except sqlite.OperationalError:
            print("Fatal Error can't execute query: file: %s"%self.file_path)
            return []
        if self.cursor:
            # get one row 
            for row in   self.cursor:
                   self.affixdict[row["affix"]] = row["flag"]  

            #print "affix", self.affixdict 

def get_attrib_by_id(self, idf, attribute):
        """ Get attribute value by id from the dictionary
        @param id :word identifier
        @type id: integer
        @param attribute :the attribute name
        @type attribute: unicode
        @return: The attribute
        value
        @rtype: mix.
        """
        # if the id exists and the attribute existe return the value, 
        sql = u"select * FROM %s WHERE id='%s'" % (self.table_name, idf)
        try:
            self.cursor.execute(sql)
            if self.cursor:
                for row in self.cursor:
                    return  row[attribute]                        
        except  sqlite.OperationalError:
            return False
        return False 

def get_original(self, primate_word):
        """
        look up for the original verb fo the given word
        @param primate_word: the derived word.
        @type primate_word: unicode.
        @return:  (verb, derivation type) .
        @rtype: tuple of unicode.
        """
        idlist = []
        sql = u"select verb, type FROM derivations WHERE derived = '%s'" % (primate_word)
        try:
            self.cursor.execute(sql)
            if self.cursor:
                for row in self.cursor:
                    idlist.append((row[0],row[1]))
                if idlist:
                    return idlist[0]
            
        except  sqlite.OperationalError:
            print("degat",  primate_word.encode('utf8'))
            return ('','') 
        return ('','') 

def get_entry_by_id(self, idf):
        """ Get dictionary entry by id from the dictionary
        @param id :word identifier
        @type id: integer
        @return: all attributes
        @rtype: dict
        """
        # lookup for a word
        sql = u"select * FROM %s WHERE id='%s'" % (self.table_name, idf)
        try:
            self.cursor.execute(sql)
            if self.cursor:
                return self.cursor.fetchall()            
        except  sqlite.OperationalError:
            return False
        return False 

def CheckLocking(self):
        """
        This tests the improved concurrency with pysqlite 2.3.4. You needed
        to roll back con2 before you could commit con1.
        """
        if sqlite.sqlite_version_info < (3, 2, 2):
            # This will fail (hang) on earlier versions of sqlite.
            # Determine exact version it was fixed. 3.2.1 hangs.
            return
        self.cur1.execute("create table test(i)")
        self.cur1.execute("insert into test(i) values (5)")
        try:
            self.cur2.execute("insert into test(i) values (5)")
            self.fail("should have raised an OperationalError")
        except sqlite.OperationalError:
            pass
        except:
            self.fail("should have raised an OperationalError")
        # NO self.con2.rollback() HERE!!!
        self.con1.commit() 

def CheckLocking(self):
        """
        This tests the improved concurrency with pysqlite 2.3.4. You needed
        to roll back con2 before you could commit con1.
        """
        if sqlite.sqlite_version_info < (3, 2, 2):
            # This will fail (hang) on earlier versions of sqlite.
            # Determine exact version it was fixed. 3.2.1 hangs.
            return
        self.cur1.execute("create table test(i)")
        self.cur1.execute("insert into test(i) values (5)")
        try:
            self.cur2.execute("insert into test(i) values (5)")
            self.fail("should have raised an OperationalError")
        except sqlite.OperationalError:
            pass
        except:
            self.fail("should have raised an OperationalError")
        # NO self.con2.rollback() HERE!!!
        self.con1.commit() 

def _do_db_query(self, params):
        '''Queries the database with custom SQL'''
        if not params:
            self._help_db_query()
            return
        try:
            results = self.query(params, include_header=True)
        except sqlite3.OperationalError as e:
            self.error(f"Invalid query. {type(e).__name__} {e}")
            return
        if type(results) == list:
            header = results.pop(0)
            if not results:
                self.output('No data returned.')
            else:
                self.table(results, header=header)
                self.output(f"{len(results)} rows returned")
        else:
            self.output(f"{results} rows affected.") 

def db_update_record(self, table_name, column, value):
        """Insert records into DB.

        Args:
            table_name (str): The name of the table.
            column (str): The column name in which the value is to be updated.
            value (str): The value to update in the column.
        """
        # escape any single quotes in value
        if isinstance(value, str):
            value = value.replace('\'', '\\')

        # only column defined in install.json can be updated
        if column in self.ij.params_dict:
            try:
                # value should be wrapped in single quotes to be properly parsed
                sql = f"UPDATE {table_name} SET {column} = '{value}'"
                cur = self.db_conn.cursor()
                cur.execute(sql)
            except sqlite3.OperationalError as e:
                raise RuntimeError(f'SQL update failed - SQL: "{sql}", Error: "{e}"') 

def query_db(self, hosts):
        result = []
        error = False
        for i in hosts:
            try:
                domain = parse_host(i)
                cursor = self.conn.cursor()
                sql = "select 1 from webinfo where domain = '{}' limit 1".format(domain)
                cursor.execute(sql)
                values = cursor.fetchall()
                if not values:
                    result.append(i)
                else:
                    console('CheckDB', i, 'In the db file\n')
                    # sys.stdout.write(Bcolors.OKGREEN + "{} In the db file\n".format(i) + Bcolors.ENDC)
            except sqlite3.OperationalError:
                return hosts
            except Exception as e:
                error = True
                logging.exception(e)
        self.commit()
        self.close()
        if error:
            return hosts
        else:
            return result 

def CheckOnConflictRollback(self):
        if sqlite.sqlite_version_info < (3, 2, 2):
            return
        con = sqlite.connect(":memory:")
        con.execute("create table foo(x, unique(x) on conflict rollback)")
        con.execute("insert into foo(x) values (1)")
        try:
            con.execute("insert into foo(x) values (1)")
        except sqlite.DatabaseError:
            pass
        con.execute("insert into foo(x) values (2)")
        try:
            con.commit()
        except sqlite.OperationalError:
            self.fail("pysqlite knew nothing about the implicit ROLLBACK") 

def CheckStatementFinalizationOnCloseDb(self):
        # pysqlite versions <= 2.3.3 only finalized statements in the statement
        # cache when closing the database. statements that were still
        # referenced in cursors weren't closed an could provoke "
        # "OperationalError: Unable to close due to unfinalised statements".
        con = sqlite.connect(":memory:")
        cursors = []
        # default statement cache size is 100
        for i in range(105):
            cur = con.cursor()
            cursors.append(cur)
            cur.execute("select 1 x union select " + str(i))
        con.close() 

def CheckAggrExceptionInStep(self):
        cur = self.con.cursor()
        try:
            cur.execute("select excStep(t) from test")
            val = cur.fetchone()[0]
            self.fail("should have raised an OperationalError")
        except sqlite.OperationalError, e:
            self.assertEqual(e.args[0], "user-defined aggregate's 'step' method raised error") 

def CheckFuncException(self):
        cur = self.con.cursor()
        try:
            cur.execute("select raiseexception()")
            cur.fetchone()
            self.fail("should have raised OperationalError")
        except sqlite.OperationalError, e:
            self.assertEqual(e.args[0], 'user-defined function raised exception') 

def _import_sql_data(data_dir):
    import sqlite3
    from sqlitedict import SqliteDict

    file_path = os.path.join(data_dir, DATA_FILE)

    # Find out what format we have
    with sqlite3.connect(file_path) as conn:
        try:
            conn.execute('select count(*) from zipgun_info')
            zipgun_info = SqliteDict(file_path, tablename='zipgun_info')
            version = zipgun_info.get('version', 0)
        except sqlite3.OperationalError:
            version = 0

    if version == 0:
        country_postal_codes = SqliteDict(file_path)
    elif version == 1:
        country_postal_codes = {}
        for country_code in zipgun_info['country_codes']:
            if country_code in country_postal_codes:
                raise ValueError('Duplicate entry found for {}'.format(
                    country_code))
            country_postal_codes[country_code] = SqliteDict(
                file_path, tablename='zg_{}'.format(country_code),
                journal_mode='OFF')
        zipgun_info.close()
    else:
        raise ValueError('Unknown data file version {}'.format(version))
    return country_postal_codes 

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 CheckScriptSyntaxError(self):
        con = sqlite.connect(":memory:")
        cur = con.cursor()
        raised = False
        try:
            cur.executescript("create table test(x); asdf; create table test2(x)")
        except sqlite.OperationalError:
            raised = True
        self.assertEqual(raised, True, "should have raised an exception") 

def CheckScriptErrorNormal(self):
        con = sqlite.connect(":memory:")
        cur = con.cursor()
        raised = False
        try:
            cur.executescript("create table test(sadfsadfdsa); select foo from hurz;")
        except sqlite.OperationalError:
            raised = True
        self.assertEqual(raised, True, "should have raised an exception") 

def CheckCollationIsUsed(self):
        if sqlite.version_info < (3, 2, 1):  # old SQLite versions crash on this test
            return
        def mycoll(x, y):
            # reverse order
            return -cmp(x, y)

        con = sqlite.connect(":memory:")
        con.create_collation("mycoll", mycoll)
        sql = """
            select x from (
            select 'a' as x
            union
            select 'b' as x
            union
            select 'c' as x
            ) order by x collate mycoll
            """
        result = con.execute(sql).fetchall()
        if result[0][0] != "c" or result[1][0] != "b" or result[2][0] != "a":
            self.fail("the expected order was not returned")

        con.create_collation("mycoll", None)
        try:
            result = con.execute(sql).fetchall()
            self.fail("should have raised an OperationalError")
        except sqlite.OperationalError, e:
            self.assertEqual(e.args[0].lower(), "no such collation sequence: mycoll") 

def CheckDeregisterCollation(self):
        """
        Register a collation, then deregister it. Make sure an error is raised if we try
        to use it.
        """
        con = sqlite.connect(":memory:")
        con.create_collation("mycoll", cmp)
        con.create_collation("mycoll", None)
        try:
            con.execute("select 'a' as x union select 'b' as x order by x collate mycoll")
            self.fail("should have raised an OperationalError")
        except sqlite.OperationalError, e:
            if not e.args[0].startswith("no such collation sequence"):
                self.fail("wrong OperationalError raised") 

def CheckCancelOperation(self):
        """
        Test that returning a non-zero value stops the operation in progress.
        """
        con = sqlite.connect(":memory:")
        progress_calls = []
        def progress():
            progress_calls.append(None)
            return 1
        con.set_progress_handler(progress, 1)
        curs = con.cursor()
        self.assertRaises(
            sqlite.OperationalError,
            curs.execute,
            "create table bar (a, b)") 

def CheckFailedOpen(self):
        YOU_CANNOT_OPEN_THIS = "/foo/bar/bla/23534/mydb.db"
        try:
            con = sqlite.connect(YOU_CANNOT_OPEN_THIS)
        except sqlite.OperationalError:
            return
        self.fail("should have raised an OperationalError")