Python sqlite3.Cursor() Examples

def CheckCursorConstructorCallCheck(self):
        """
        Verifies that cursor methods check whether base class __init__ was
        called.
        """
        class Cursor(sqlite.Cursor):
            def __init__(self, con):
                pass

        con = sqlite.connect(":memory:")
        cur = Cursor(con)
        try:
            cur.execute("select 4+5").fetchall()
            self.fail("should have raised ProgrammingError")
        except sqlite.ProgrammingError:
            pass
        except:
            self.fail("should have raised ProgrammingError") 

def __init__(
        self,
        schema_path: str,
        cursor: Cursor = None,
        use_prelinked_entities: bool = True,
        variable_free: bool = True,
        use_untyped_entities: bool = False,
    ) -> None:
        self.cursor = cursor
        self.schema = read_dataset_schema(schema_path)
        self.columns = {column.name: column for table in self.schema.values() for column in table}
        self.dataset_name = os.path.basename(schema_path).split("-")[0]
        self.use_prelinked_entities = use_prelinked_entities
        self.variable_free = variable_free
        self.use_untyped_entities = use_untyped_entities

        # NOTE: This base dictionary should not be modified.
        self.base_grammar_dictionary = self._initialize_grammar_dictionary(
            deepcopy(GRAMMAR_DICTIONARY)
        ) 

def CheckCursorConstructorCallCheck(self):
        """
        Verifies that cursor methods check whether base class __init__ was
        called.
        """
        class Cursor(sqlite.Cursor):
            def __init__(self, con):
                pass

        con = sqlite.connect(":memory:")
        cur = Cursor(con)
        try:
            cur.execute("select 4+5").fetchall()
            self.fail("should have raised ProgrammingError")
        except sqlite.ProgrammingError:
            pass
        except:
            self.fail("should have raised ProgrammingError") 

def test_get_db_connection():
    """ Test PhotosDB.get_db_connection """
    import osxphotos
    import sqlite3

    photosdb = osxphotos.PhotosDB(dbfile=PHOTOS_DB)
    conn, cursor = photosdb.get_db_connection()

    assert isinstance(conn, sqlite3.Connection)
    assert isinstance(cursor, sqlite3.Cursor)

    results = conn.execute(
        "SELECT ZUUID FROM ZGENERICASSET WHERE ZFAVORITE = 1;"
    ).fetchall()
    assert len(results) == 1
    assert results[0][0] == "E9BC5C36-7CD1-40A1-A72B-8B8FAC227D51"  # uuid

    conn.close() 

def execute(cls, query, params=()):
        """
        This method executes a query with parameters.

        :param str query: The query.
        :param tuple params: The parameters.
        :rtype: sqlite3.Cursor
        """

        Model.lock.acquire()

        try:
            result = Model.db.execute(query, params)
        finally:
            Model.lock.release()

        return result 

def CheckCursorConstructorCallCheck(self):
        """
        Verifies that cursor methods check whether base class __init__ was
        called.
        """
        class Cursor(sqlite.Cursor):
            def __init__(self, con):
                pass

        con = sqlite.connect(":memory:")
        cur = Cursor(con)
        try:
            cur.execute("select 4+5").fetchall()
            self.fail("should have raised ProgrammingError")
        except sqlite.ProgrammingError:
            pass
        except:
            self.fail("should have raised ProgrammingError")
        with self.assertRaisesRegexp(sqlite.ProgrammingError,
                                     r'^Base Cursor\.__init__ not called\.$'):
            cur.close() 

def CheckCursorConstructorCallCheck(self):
        """
        Verifies that cursor methods check whether base class __init__ was
        called.
        """
        class Cursor(sqlite.Cursor):
            def __init__(self, con):
                pass

        con = sqlite.connect(":memory:")
        cur = Cursor(con)
        try:
            cur.execute("select 4+5").fetchall()
            self.fail("should have raised ProgrammingError")
        except sqlite.ProgrammingError:
            pass
        except:
            self.fail("should have raised ProgrammingError") 

def insert_system(cur, system_name, encoded_data=None):
    """Insert a system name into the cache.

    Args:
        cur (:class:`sqlite3.Cursor`):
            An sqlite3 cursor. This function is meant to be run within a :obj:`with` statement.

        system_name (str):
            The unique name of a system

        encoded_data (dict, optional):
            If a dictionary is provided, it will be populated with the serialized data. This is
            useful for preventing encoding the same information many times.

    """
    if encoded_data is None:
        encoded_data = {}

    if 'system_name' not in encoded_data:
        encoded_data['system_name'] = system_name

    insert = "INSERT OR IGNORE INTO system(system_name) VALUES (:system_name);"
    cur.execute(insert, encoded_data) 

def CheckCursorConstructorCallCheck(self):
        """
        Verifies that cursor methods check whether base class __init__ was
        called.
        """
        class Cursor(sqlite.Cursor):
            def __init__(self, con):
                pass

        con = sqlite.connect(":memory:")
        cur = Cursor(con)
        with self.assertRaises(sqlite.ProgrammingError):
            cur.execute("select 4+5").fetchall()
        with self.assertRaisesRegex(sqlite.ProgrammingError,
                                    r'^Base Cursor\.__init__ not called\.$'):
            cur.close() 

def delete(self, table_name: str, where: Optional[WhereQuery] = None) -> Optional[Cursor]:
        """
        Send a DELETE query to the database.

        :param str table_name: Table name of executing the query.
        :param where: |arg_select_where|
        :type where: |arg_where_type|
        """

        self.validate_access_permission(["w", "a"])
        self.verify_table_existence(table_name)

        query = "DELETE FROM {:s}".format(table_name)
        if where:
            query += " WHERE {:s}".format(where)

        return self.execute_query(query, logging.getLogger().findCaller()) 

def CheckCursorConstructorCallCheck(self):
        """
        Verifies that cursor methods check whether base class __init__ was
        called.
        """
        class Cursor(sqlite.Cursor):
            def __init__(self, con):
                pass

        con = sqlite.connect(":memory:")
        cur = Cursor(con)
        try:
            cur.execute("select 4+5").fetchall()
            self.fail("should have raised ProgrammingError")
        except sqlite.ProgrammingError:
            pass
        except:
            self.fail("should have raised ProgrammingError") 

def CheckCursorConstructorCallCheck(self):
        """
        Verifies that cursor methods check wether base class __init__ was called.
        """
        class Cursor(sqlite.Cursor):
            def __init__(self, con):
                pass

        con = sqlite.connect(":memory:")
        cur = Cursor(con)
        try:
            cur.execute("select 4+5").fetchall()
            self.fail("should have raised ProgrammingError")
        except sqlite.ProgrammingError:
            pass
        except:
            self.fail("should have raised ProgrammingError") 

def sqlite3(self, conn, sql, parameters=None, *args, **kwargs):
        """
        Reads input by querying from a sqlite database.

        >>> seq.sqlite3('examples/users.db', 'select id, name from users where id = 1;').first()
        [(1, 'Tom')]

        :param conn: path or sqlite connection, cursor
        :param sql: SQL query string
        :param parameters: Parameters for sql query
        :return: Sequence wrapping SQL cursor
        """

        if parameters is None:
            parameters = ()

        if isinstance(conn, (sqlite3api.Connection, sqlite3api.Cursor)):
            return self(conn.execute(sql, parameters))
        elif isinstance(conn, str):
            with sqlite3api.connect(conn, *args, **kwargs) as input_conn:
                return self(input_conn.execute(sql, parameters))
        else:
            raise ValueError(
                "conn must be a must be a file path or sqlite3 Connection/Cursor"
            ) 

def CheckCursorRegistration(self):
        """
        Verifies that subclassed cursor classes are correctly registered with
        the connection object, too.  (fetch-across-rollback problem)
        """
        class Connection(sqlite.Connection):
            def cursor(self):
                return Cursor(self)

        class Cursor(sqlite.Cursor):
            def __init__(self, con):
                sqlite.Cursor.__init__(self, con)

        con = Connection(":memory:")
        cur = con.cursor()
        cur.execute("create table foo(x)")
        cur.executemany("insert into foo(x) values (?)", [(3,), (4,), (5,)])
        cur.execute("select x from foo")
        con.rollback()
        with self.assertRaises(sqlite.InterfaceError):
            cur.fetchall() 

def query(self, q_string, echo=None, to_numpy=True):
        """
        Executes a SQL query string against the database.
        Echoes the result back if echo=True or self.echo=True.

        :param q_string: SQL query string
        :param echo: echo back result, overwrites self.echo
        :return: SQL result set or Cursor object
        """
        words = q_string.upper().split(" ")
        if "DROP" in words:
            raise UserWarning("DROP queries should be performed manually.")

        # Define cursor object
        try:
            self.cursor = self.connection.cursor()
        except AttributeError:
            raise Error("Connection to DB has not been established.")

        # Execute query
        try:
            self.cursor.execute(q_string)
            if self.auto_commit:
                self.connection.commit()
        except Error as e:
            err_str = 'Error in query:\n"""\n%s\n"""' % q_string
            raise Error(err_str) from e

        # Fetch or return cursor
        echo = self.echo if echo is None else echo
        if echo and "SELECT" in words:
            if to_numpy:
                return np.array(self.cursor.fetchall())
            else:
                return self.cursor.fetchall() 

def CheckCursorWrongClass(self):
        class Foo: pass
        foo = Foo()
        with self.assertRaises(TypeError):
            cur = sqlite.Cursor(foo) 

def CheckCursorRegistration(self):
        """
        Verifies that subclassed cursor classes are correctly registered with
        the connection object, too.  (fetch-across-rollback problem)
        """
        class Connection(sqlite.Connection):
            def cursor(self):
                return Cursor(self)

        class Cursor(sqlite.Cursor):
            def __init__(self, con):
                sqlite.Cursor.__init__(self, con)

        con = Connection(":memory:")
        cur = con.cursor()
        cur.execute("create table foo(x)")
        cur.executemany("insert into foo(x) values (?)", [(3,), (4,), (5,)])
        cur.execute("select x from foo")
        con.rollback()
        try:
            cur.fetchall()
            self.fail("should have raised InterfaceError")
        except sqlite.InterfaceError:
            pass
        except:
            self.fail("should have raised InterfaceError") 

def cursor(self, value):
        """
        Property setter for DB Cursor object

        :param value: sqlite.Cursor object
        :return: None
        """
        if not isinstance(value, sqlite3.Cursor):
            raise ValueError("cursor must be a sqlite3 Cursor object.")
        self._cursor = value 

def cursor(self):
        """
        Property getter for DB Cursor object

        :return: sqlite3.Cursor object
        """
        return self._cursor 

def insert_many_raw_text_table(cursor: sqlite3.Cursor, data, table_name='raw_text'):
    cursor.executemany(f"INSERT INTO {table_name} (article_title, p_num, value) VALUES (?, ?, ?)", data) 

def execute(self, sql: str, parameters: Iterable = None) -> Awaitable[sqlite3.Cursor]:
        parameters = parameters if parameters is not None else []
        return self.run(lambda conn: conn.execute(sql, parameters)) 

def CheckFakeCursorClass(self):
        # Issue #24257: Incorrect use of PyObject_IsInstance() caused
        # segmentation fault.
        class FakeCursor(str):
            __class__ = sqlite.Cursor
        cur = self.con.cursor(factory=FakeCursor)
        self.assertRaises(TypeError, sqlite.Row, cur, ()) 

def __init__(self, *args, **kwargs):
        sqlite.Cursor.__init__(self, *args, **kwargs)
        self.row_factory = dict_factory 

def CheckCursorWrongClass(self):
        class Foo: pass
        foo = Foo()
        try:
            cur = sqlite.Cursor(foo)
            self.fail("should have raised a ValueError")
        except TypeError:
            pass 

def CheckCursorWrongClass(self):
        class Foo: pass
        foo = Foo()
        try:
            cur = sqlite.Cursor(foo)
            self.fail("should have raised a ValueError")
        except TypeError:
            pass 

def __execute_sql(self, sql: str, *args) -> sqlite3.Cursor:
        sql = sql.strip().upper()
        cursor = self._connection.execute(sql, args)
        if not sql.startswith('SELECT'):
            self._connection.commit()
        return cursor 

def execute(self, sql, parameters=None):
            """
            Makes a debug() call and then proxies the call to parent.
            """

            if parameters:
                self._logger.debug("Executing '%s' with %s", sql, parameters)
                return sqlite3.Cursor.execute(self, sql, parameters)
            else:
                self._logger.debug("Executing '%s'", sql)
                return sqlite3.Cursor.execute(self, sql) 

def test_initdb(self):
        if exists(TEST_TEMP_DBFILE_PATH):
            os.remove(TEST_TEMP_DBFILE_PATH)
        self.assertIs(False, exists(TEST_TEMP_DBFILE_PATH))
        conn, curr = BukuDb.initdb()
        self.assertIsInstance(conn, sqlite3.Connection)
        self.assertIsInstance(curr, sqlite3.Cursor)
        self.assertIs(True, exists(TEST_TEMP_DBFILE_PATH))
        curr.close()
        conn.close() 

def query(self, sql, args=None):
        '''Returns the result of an arbitrary SQL statement.

        Arguments:

            `sql` (str):

                An SQL statement to be passed to the database. Use "?" for
                variables passed into the statement.

            `args` (tuple):

                Optional arguments which will replace the "?" placeholders in
                the `sql` argument.

        Returns:

            An :class:`sqlite3.Cursor` object with the query results.

        Example::

            >>> sql = r'SELECT SpectrumID, Name FROM Samples, Spectra ' +
            ...        'WHERE Spectra.SampleID = Samples.SampleID ' +
            ...        'AND Name LIKE "%grass%" AND MinWavelength < ?'
            >>> args = (0.5,)
            >>> cur = db.query(sql, args)
            >>> for row in cur:
            ...     print row
            ...
            (356, u'dry grass')
            (357, u'grass')
        '''
        if args:
            return self.cursor.execute(sql, args)
        else:
            return self.cursor.execute(sql) 

def cursor(self):
        return Cursor(self)