Python sqlite3.Connection() 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 CheckConnectionConstructorCallCheck(self):
        """
        Verifies that connection methods check whether base class __init__ was
        called.
        """
        class Connection(sqlite.Connection):
            def __init__(self, name):
                pass

        con = Connection(":memory:")
        try:
            cur = con.cursor()
            self.fail("should have raised ProgrammingError")
        except sqlite.ProgrammingError:
            pass
        except:
            self.fail("should have raised ProgrammingError") 

def initialize_db(fname):
        """

        Args:
            fname (str): location of the database.

        Returns:
            db (sqlite3.Connection): a SQLite3 database with an embeddings table.

        """
        if path.dirname(fname) and not path.isdir(path.dirname(fname)):
            makedirs(path.dirname(fname))
        # open database in autocommit mode by setting isolation_level to None.
        db = sqlite3.connect(fname, isolation_level=None)
        c = db.cursor()
        c.execute('create table if not exists embeddings(word text primary key, emb blob)')
        return db 

def test_database_existing_censys_results_columns(
    connection: Connection_instance
) -> None:
    """
    Check column names of 'censys_results' table
    :param connection: sqlite3.Connection object
    :return: None
    """
    assert sorted(
        connection.execute("PRAGMA table_info(censys_results)").fetchall()
    ) == sorted(
        [
            (0, "id", "INTEGER", 0, None, 1),
            (1, "scan_data_id", "INTEGER", 0, None, 0),
            (2, "scan_information_id", "INTEGER", 0, None, 0),
            (3, "query", "TEXT", 0, None, 0),
            (4, "query_confidence", "TEXT", 0, None, 0),
            (5, "results_count", "INTEGER", 0, None, 0),
            (6, "results", "TEXT", 0, None, 0),
        ]
    ) 

def test_update_end_time_success(connection: Connection_instance) -> None:
    """
    Check if we can successfully update time
    of scan - time when scan was finished
    :param connection: sqlite3.Connection object
    :return: None
    """
    db.update_end_time()
    end_time_values = connection.execute(
        """
        SELECT * FROM scan_information 
        WHERE scan_information.id = (
            SELECT max(id) 
            FROM scan_information
        )
        """
    ).fetchall()
    end_time, end_duration = end_time_values[0][4:6]
    assert end_time == str(db.scan_end_time.time().strftime("%H:%M:%S"))
    assert end_duration == str(db.scan_duration) 

def test_update_results_count_success(connection: Connection_instance) -> None:
    """
    Check if we can successfully and correctly
    update final counters of scan (when scan
    will be finished)
    :param connection: sqlite3.Connection object
    :return: None
    """
    db.update_results_count(total_products=42, total_results=1337)
    end_results_values = connection.execute(
        """
        SELECT * FROM scan_information 
        WHERE scan_information.id = (
            SELECT max(id) 
            FROM scan_information
        )
        """
    ).fetchall()
    total_products, total_results = end_results_values[0][6:]
    assert total_products == 42
    assert total_results == 1337 

def read_check(id, type):
    try:
        conn = sqlite3.Connection('data/xuexi.db')
        cursor = conn.cursor()
        if cursor.execute('select * from read_history where id = "%s"' % id).fetchall() == []:
            cursor.execute('insert into read_history values("%s","%s")' % (id, type))
            conn.commit()
            logging.debug('new content %s %s' % (id, type))
            return True
        else:
            logging.debug('%s is in read history' % id)
            return False
    except Exception as error:
        logging.debug(error)
    finally:
        cursor.close()
        conn.close()

    return True 

def _InsertApplicationUsage(
      self, conn, event, bundle_id, app_version, app_path, now):
    """Insert usage data into application usage table.

    Args:
      conn: sqlite3.Connection object
      event: str
      bundle_id: str
      app_version: str
      app_path: str
      now: int
    """
    # this looks weird, but it's the simplest way to do an update or insert
    # operation in sqlite, and atomically update number_times, that I could
    # figure out.  plus we avoid using transactions and multiple SQL
    # statements in most cases.

    v = (app_version, app_path, now, event, bundle_id)
    q = conn.execute(APPLICATION_USAGE_TABLE_UPDATE, v)
    if q.rowcount == 0:
      number_times = 1
      v = (event, bundle_id, app_version, app_path, now, number_times)
      conn.execute(APPLICATION_USAGE_TABLE_INSERT, v) 

def test_add_shodan_scan_data_error(connection: Connection_instance) -> None:
    """
    Check if we can properly handle errors that will be raised
    with add scan data method
    :param connection: sqlite3.Connection object
    :return: None
    """
    connection_backup = db.connection
    db.connection = None

    def add_scan_data():
        db.add_shodan_scan_data(
            query={},
            results_count=0,
            results=[],
        )

    with raises(GrinderDatabaseAddScanDataError):
        add_scan_data()
    with raises(GrinderDatabaseException):
        add_scan_data()
    db.connection = connection_backup 

def test_add_basic_scan_data_error(connection: Connection_instance) -> None:
    """
    This test checks if we can properly
    handle all errors that will be raised in
    method of adding basic scan data into database
    :param connection: sqlite3.Connection object
    :return: None
    """
    connection_backup = db.connection
    db.connection = None

    def add_scan_data():
        db.add_basic_scan_data(
            vendor="pytest_vendor",
            product="pytest_product",
            script="pytest_script",
            vendor_confidence="pytest_confidence",
        )

    with raises(GrinderDatabaseAddBasicScanDataError):
        add_scan_data()
    with raises(GrinderDatabaseException):
        add_scan_data()
    db.connection = connection_backup 

def CheckConnectionConstructorCallCheck(self):
        """
        Verifies that connection methods check whether base class __init__ was
        called.
        """
        class Connection(sqlite.Connection):
            def __init__(self, name):
                pass

        con = Connection(":memory:")
        try:
            cur = con.cursor()
            self.fail("should have raised ProgrammingError")
        except sqlite.ProgrammingError:
            pass
        except:
            self.fail("should have raised ProgrammingError") 

def test_database_existing_scan_information_columns(
    connection: Connection_instance
) -> None:
    """
    Check column names of 'scan_information' table
    :param connection: sqlite3.Connection object
    :return: None
    """
    assert sorted(
        connection.execute("PRAGMA table_info(scan_information)").fetchall()
    ) == sorted(
        [
            (0, "id", "INTEGER", 0, None, 1),
            (1, "scan_name", "TEXT", 0, None, 0),
            (2, "scan_date", "TEXT", 0, None, 0),
            (3, "scan_start_time", "TEXT", 0, None, 0),
            (4, "scan_end_time", "TEXT", 0, None, 0),
            (5, "scan_duration", "TEXT", 0, None, 0),
            (6, "scan_total_products", "INT", 0, None, 0),
            (7, "scan_total_results", "INT", 0, None, 0),
        ]
    ) 

def CheckConnectionConstructorCallCheck(self):
        """
        Verifies that connection methods check whether base class __init__ was
        called.
        """
        class Connection(sqlite.Connection):
            def __init__(self, name):
                pass

        con = Connection(":memory:")
        try:
            cur = con.cursor()
            self.fail("should have raised ProgrammingError")
        except sqlite.ProgrammingError:
            pass
        except:
            self.fail("should have raised ProgrammingError") 

def close(self) -> None:
        """
        Commit and close the connection.

        .. seealso:: :py:meth:`sqlite3.Connection.close`
        """

        if self.__delayed_connection_path and self.__connection is None:
            self.__initialize_connection()
            return

        try:
            self.check_connection()
        except (SystemError, NullDatabaseConnectionError):
            return

        logger.debug("close connection to a SQLite database: path='{}'".format(self.database_path))

        self.commit()
        assert self.connection  # to avoid type check error
        self.connection.close()
        self.__initialize_connection() 

def commit(self) -> None:
        """
        .. seealso:: :py:meth:`sqlite3.Connection.commit`
        """

        try:
            self.check_connection()
        except NullDatabaseConnectionError:
            return

        logger.debug("commit: path='{}'".format(self.database_path))
        assert self.connection  # to avoid type check error

        try:
            self.connection.commit()
        except sqlite3.ProgrammingError:
            pass 

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 CheckConnectionConstructorCallCheck(self):
        """
        Verifies that connection methods check wether base class __init__ was called.
        """
        class Connection(sqlite.Connection):
            def __init__(self, name):
                pass

        con = Connection(":memory:")
        try:
            cur = con.cursor()
            self.fail("should have raised ProgrammingError")
        except sqlite.ProgrammingError:
            pass
        except:
            self.fail("should have raised ProgrammingError") 

def test_database_existing_shodan_results_columns(
    connection: Connection_instance
) -> None:
    """
    Check column names of 'shodan_results' table
    :param connection: sqlite3.Connection object
    :return: None
    """
    assert sorted(
        connection.execute("PRAGMA table_info(shodan_results)").fetchall()
    ) == sorted(
        [
            (0, "id", "INTEGER", 0, None, 1),
            (1, "scan_data_id", "INTEGER", 0, None, 0),
            (2, "scan_information_id", "INTEGER", 0, None, 0),
            (3, "query", "TEXT", 0, None, 0),
            (4, "query_confidence", "TEXT", 0, None, 0),
            (5, "results_count", "INTEGER", 0, None, 0),
            (6, "results", "TEXT", 0, None, 0),
        ]
    ) 

def create_model(dump, db_file=':memory:', N=2):
    """Create a semanticizer model from a wikidump and store it in a DB.

    Parameters
    ----------
    dump : string
        Filename of a Wikipedia dump, e.g.,
        'enwiki-20141106-pages-articles.xml.bz2'
    db_file : string
       (File)name of the sqlite3 DB. If `df_file` is `:memory:`, an in-memory
       db will be created, otherwise it is the filename of the
       disk-based db.

    Returns
    ------
    db : sqlite3.Connection
        The handle to the newly created db containing the model.
    """
    db = sqlite3.connect(db_file)
    _parse_stuff_to_db(dump, db, N=N)
    return db 

def test_database_existing_tables(connection: Connection_instance) -> None:
    """
    Check tables that currently exists
    in database (after creating)
    :param connection: sqlite3.Connection object
    :return: None
    """
    assert sorted(
        connection.execute("SELECT name FROM sqlite_master").fetchall()
    ) == sorted(
        [
            ("sqlite_sequence",),
            ("scan_information",),
            ("scan_data",),
            ("shodan_results",),
            ("censys_results",),
        ]
    ) 

def wallet_create(path, args):
    sql_db = sqlite3.Connection(os.path.join(path, "wallet.db"))
    persistence = SQLite3Persistence(sql_db)

    bcv_json = persistence.get_global("blockchain_view") or "[]"
    blockchain_view = BlockChainView.from_json(bcv_json)
    last_block = blockchain_view.last_block_index()

    # how much are we sending?
    total_sending = 0
    for p in args.payable:
        if len(p) == 2:
            total_sending += p[-1]

    if total_sending == 0:
        raise argparse.ArgumentTypeError("you must choose a non-zero amount to send")

    total = 0
    spendables = []
    for spendable in persistence.unspent_spendables(last_block, confirmations=1):
        spendables.append(spendable)
        total += spendable.coin_value
        if total >= total_sending:
            break

    print("found %d coins which exceed %d" % (total, total_sending))

    tx = create_tx(spendables, args.payable)
    with open(args.output, "wb") as f:
        tx.stream(f)
        tx.stream_unspents(f) 

def wallet_exclude(path, args):
    sql_db = sqlite3.Connection(os.path.join(path, "wallet.db"))
    persistence = SQLite3Persistence(sql_db)

    with open(args.path_to_tx, "rb") as f:
        if f.name.endswith("hex"):
            f = io.BytesIO(codecs.getreader("hex_codec")(f).read())
        tx = Tx.parse(f)

    for tx_in in tx.txs_in:
        spendable = persistence.spendable_for_hash_index(tx_in.previous_hash, tx_in.previous_index)
        if spendable:
            spendable.does_seem_spent = True
            persistence.save_spendable(spendable)
    persistence.commit() 

def wallet_balance(path, args):
    sql_db = sqlite3.Connection(os.path.join(path, "wallet.db"))
    persistence = SQLite3Persistence(sql_db)
    bcv_json = persistence.get_global("blockchain_view") or "[]"
    blockchain_view = BlockChainView.from_json(bcv_json)
    last_block = blockchain_view.last_block_index()
    total = 0
    for spendable in persistence.unspent_spendables(last_block, confirmations=1):
        total += spendable.coin_value
    print("block %d: balance = %s mBTC" % (last_block, satoshi_to_mbtc(total))) 

def test_init(self):
        with deepstar_path():
            self.assertEqual(type(Model.db), sqlite3.Connection) 

def __run_transaction_with_foreign_keys_disabled(self,
                                                     fun: Callable[[sqlite3.Connection, Any, Any], Any],
                                                     *args, **kwargs):
        foreign_keys_enabled, = self.connection.execute("pragma foreign_keys").fetchone()
        if not foreign_keys_enabled:
            raise sqlite3.IntegrityError("foreign keys are disabled, use `AIOSQLite.run` instead")
        try:
            self.connection.execute('pragma foreign_keys=off')
            return self.__run_transaction(fun, *args, **kwargs)
        finally:
            self.connection.execute('pragma foreign_keys=on') 

def __init__(self, *args,**kwargs):
        self.args = args
        sqlite3.Connection.__init__(self,*args,**kwargs) 

def _Close(self, conn):
    """Close database.

    Args:
      conn: sqlite3.Connection instance
    """
    conn.close() 

def set_sqlite_pragma(dbapi_connection, connection_record):
    # pylint: disable=unused-argument
    """
    SQLite supports FOREIGN KEY syntax when emitting CREATE statements for
    tables, however by default these constraints have no effect on the
    operation of the table.

    http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#foreign-key-support
    """
    if not isinstance(dbapi_connection, sqlite3.Connection):
        return
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.close() 

def __init__(self, location=':memory:'):
        self.conn: sqlite3.Connection = sqlite3.connect(location)
        self.conn.row_factory = sqlite3.Row 

def cache_connect(database=None):
    """Returns a connection object to a sqlite database.

    Args:
        database (str, optional): The path to the database the user wishes
            to connect to. If not specified, a default is chosen using
            :func:`.cache_file`. If the special database name ':memory:'
            is given, then a temporary database is created in memory.

    Returns:
        :class:`sqlite3.Connection`

    """
    if database is None:
        database = cache_file()

    if os.path.isfile(database):
        # just connect to the database as-is
        conn = sqlite3.connect(database)
    else:
        # we need to populate the database
        conn = sqlite3.connect(database)
        conn.executescript(schema)

    with conn as cur:
        # turn on foreign keys, allows deletes to cascade.
        cur.execute("PRAGMA foreign_keys = ON;")

    conn.row_factory = sqlite3.Row

    return conn