Python aiosqlite.connect() Examples

def __aenter__(self) -> "BaseSourceContext":
        self.__db = aiosqlite.connect(self.config.filename)
        self.db = await self.__db.__aenter__()
        self.db.row_factory = aiosqlite.Row
        # Create table for feature data
        await self.db.execute(
            "CREATE TABLE IF NOT EXISTS features ("
            "key TEXT PRIMARY KEY NOT NULL, "
            + (" REAL, ".join(self.FEATURE_COLS))
            + " REAL"
            ")"
        )
        # Create table for predictions
        await self.db.execute(
            "CREATE TABLE IF NOT EXISTS prediction ("
            "key TEXT PRIMARY KEY, " + "value TEXT, "
            "confidence REAL"
            ")"
        )
        return self 

def try_make_db() -> None:
    sqlite_db = get_db_path()
    if sqlite_db.exists():
        return

    with sqlite3.connect(sqlite_db) as conn:
        cur = conn.cursor()
        cur.execute(
            """CREATE TABLE posts (
            id INTEGER PRIMARY KEY,
            title TEXT,
            text TEXT,
            owner TEXT,
            editor TEXT,
            image BLOB)
        """
        )
        conn.commit() 

def try_make_db() -> None:
    sqlite_db = get_db_path()
    if sqlite_db.exists():
        return

    with sqlite3.connect(sqlite_db) as conn:
        cur = conn.cursor()
        cur.execute(
            """CREATE TABLE posts (
            id INTEGER PRIMARY KEY,
            title TEXT,
            text TEXT,
            owner TEXT,
            editor TEXT,
            image BLOB)
        """
        )
        conn.commit() 

def try_make_db(sqlite_db: Path) -> None:
    if sqlite_db.exists():
        return

    with sqlite3.connect(sqlite_db) as conn:
        cur = conn.cursor()
        cur.execute(
            """CREATE TABLE posts (
            id INTEGER PRIMARY KEY,
            title TEXT,
            text TEXT,
            owner TEXT,
            editor TEXT,
            image BLOB)
        """
        )
        conn.commit() 

def test_get_header_hashes(self):
        blocks = bt.get_consecutive_blocks(test_constants, 5, [], 9, b"0")
        db_path = Path("blockchain_test.db")
        if db_path.exists():
            db_path.unlink()
        connection = await aiosqlite.connect(db_path)
        coin_store = await CoinStore.create(connection)
        store = await BlockStore.create(connection)
        b: Blockchain = await Blockchain.create(coin_store, store, test_constants)

        for i in range(1, len(blocks)):
            await b.receive_block(blocks[i])
        header_hashes = b.get_header_hashes(blocks[-1].header_hash)
        assert len(header_hashes) == 6
        assert header_hashes == [block.header_hash for block in blocks]

        await connection.close()
        b.shut_down() 

def try_make_db() -> None:
    sqlite_db = get_db_path()
    if sqlite_db.exists():
        return

    with sqlite3.connect(sqlite_db) as conn:
        cur = conn.cursor()
        cur.execute(
            """CREATE TABLE posts (
            id INTEGER PRIMARY KEY,
            title TEXT,
            text TEXT,
            owner TEXT,
            editor TEXT,
            image BLOB)
        """
        )
        conn.commit() 

def database_update(sql_code, values=()):
    """A function that updates/inserts value/s to the database."""

    try:
        async with aiosqlite.connect("faithful_fleas/bot.db") as db:

            logger.info("Database connection made successfully.")
            await db.execute(sql_code, values)
            await db.commit()
            logger.info(f"SQL code executed successfully"
                        f"SQL_CODE: {sql_code}"
                        f"Values: {values}")

            return True

    except Exception as e:

        logging.error(f"An error occured in DATABASE_QUERY method,"
                      f"ERROR :\n {str(e)}")
        return False 

def try_make_db() -> None:
    sqlite_db = get_db_path()
    if sqlite_db.exists():
        return

    with sqlite3.connect(sqlite_db) as conn:
        cur = conn.cursor()
        cur.execute(
            """CREATE TABLE posts (
            id INTEGER PRIMARY KEY,
            title TEXT,
            text TEXT,
            owner TEXT,
            editor TEXT,
            image BLOB)
        """
        )
        conn.commit() 

def initial_blockchain(self):
        """
        Provides a list of 10 valid blocks, as well as a blockchain with 9 blocks added to it.
        """
        blocks = bt.get_consecutive_blocks(test_constants, 10, [], 10)
        db_path = Path("blockchain_test.db")
        if db_path.exists():
            db_path.unlink()
        connection = await aiosqlite.connect(db_path)
        store = await BlockStore.create(connection)
        coin_store = await CoinStore.create(connection)
        b: Blockchain = await Blockchain.create(coin_store, store, test_constants)
        for i in range(1, 9):
            result, removed, error_code = await b.receive_block(blocks[i])
            assert result == ReceiveBlockResult.ADDED_TO_HEAD
        yield (blocks, b)

        await connection.close() 

def database_query(sql_code, values=()):
    """A function which can be used to query the database."""

    try:
        async with aiosqlite.connect("faithful_fleas/bot.db") as db:

            logger.info("Database connection made successfully.")

            async with db.execute(sql_code, values) as cursor:
                data = await cursor.fetchall()
                logger.info(f"SQL code executed successfully"
                            f"SQL_CODE: {sql_code}"
                            f"Values: {values}")
            return data

    except Exception as e:

        logging.error(f"An error occured in DATABASE_QUERY method,"
                      f"ERROR :\n {str(e)}") 

def create_db_and_schema(report_folder):
        async with aiosqlite.connect(f"{report_folder}/witnessme.db") as db:
            await db.execute('''CREATE TABLE "hosts" (
                "id" integer PRIMARY KEY,
                "hostname" text,
                "ip" text,
                UNIQUE(hostname, ip)
            )''')

            await db.execute('''CREATE TABLE "services" (
                "id" integer PRIMARY KEY,
                "url" text,
                "screenshot" text,
                "port" integer,
                "scheme" text,
                "title" text,
                "server" text,
                "headers" text,
                "host_id" integer,
                "matched_sigs" text,
                "body" text,
                FOREIGN KEY(host_id) REFERENCES hosts(id),
                UNIQUE(port, host_id, scheme)
            )''')

            await db.commit() 

def _new_conn(self):
        db = await aiosqlite.connect(*self._conn_args, **self._conn_kwargs)
        try:
            async with db.cursor() as cur:
                for q in self._init_queries:
                    await cur.execute(q)
        except:
            await db.close()
            raise
        return db 

def __aenter__(self):
        if not self.connection:
            self.db = await aiosqlite.connect(f"{self.report_folder}/witnessme.db")
        else:
            self.db = self.connection
        return self 

def cmdloop(self):
        use_asyncio_event_loop()
        self.db = await aiosqlite.connect(self.db_path)

        try:
            while True:
                #with patch_stdout():
                text = await self.prompt_session.prompt(async_=True)
                command = shlex.split(text)
                if len(command):
                    # Apperently you can't call await on a coroutine retrieved via getattr() ??
                    # So this sucks now but thankfully we don't have a lot of commands
                    try:
                        if command[0] == 'exit':
                            await self.exit()
                            break
                        elif command[0] == 'show':
                            await self.show(command[1:])
                        elif command[0] == 'open':
                            await self.open(command[1:])
                        elif command[0] == 'hosts':
                            await self.hosts(command[1:])
                        elif command[0] == 'servers':
                            await self.servers(command[1:])
                        elif command[0] == 'scan':
                            await self.scan()
                    except Exception as e:
                        import traceback
                        traceback.print_exc()
                        print(f"Error calling command '{command[0]}': {e}")
        finally:
            await self.db.close() 

def init_db(app: web.Application) -> AsyncIterator[None]:
    sqlite_db = get_db_path()
    db = await aiosqlite.connect(sqlite_db)
    db.row_factory = aiosqlite.Row
    app["DB"] = db
    yield
    await db.close() 

def init_db(app: web.Application) -> AsyncIterator[None]:
    sqlite_db = get_db_path()
    db = await aiosqlite.connect(sqlite_db)
    db.row_factory = aiosqlite.Row
    app["DB"] = db
    yield
    await db.close() 

def init_db(app: web.Application) -> AsyncIterator[None]:
    sqlite_db = get_db_path()
    db = await aiosqlite.connect(sqlite_db)
    db.row_factory = aiosqlite.Row
    app["DB"] = db
    yield
    await db.close() 

def init_db(app: web.Application) -> AsyncIterator[None]:
    sqlite_db = app["DB_PATH"]
    db = await aiosqlite.connect(sqlite_db)
    db.row_factory = aiosqlite.Row
    app["DB"] = db
    yield
    await db.close() 

def db(db_path: Path) -> aiosqlite.Connection:
    conn = await aiosqlite.connect(db_path)
    conn.row_factory = aiosqlite.Row
    yield conn
    await conn.close() 

def init_db(app: web.Application) -> AsyncIterator[None]:
    sqlite_db = get_db_path()
    db = await aiosqlite.connect(sqlite_db)
    db.row_factory = aiosqlite.Row
    app["DB"] = db
    yield
    await db.close() 

def open(self):
        await self.close()
        path = Path(self.db_path)
        LOGGER.info("Ledger cache will be stored in %s", path)
        newDB = not path.exists()
        self.db = await aiosqlite.connect(str(path)).__aenter__()
        if newDB:
            await self.init_db() 

def test_lca(self):
        blocks = bt.get_consecutive_blocks(test_constants, 5, [], 9, b"0")
        db_path = Path("blockchain_test.db")
        if db_path.exists():
            db_path.unlink()
        connection = await aiosqlite.connect(db_path)
        coin_store = await CoinStore.create(connection)
        store = await BlockStore.create(connection)
        b: Blockchain = await Blockchain.create(coin_store, store, test_constants)
        for i in range(1, len(blocks)):
            await b.receive_block(blocks[i])

        assert b.lca_block.header_hash == blocks[3].header_hash
        block_5_2 = bt.get_consecutive_blocks(test_constants, 1, blocks[:5], 9, b"1")
        block_5_3 = bt.get_consecutive_blocks(test_constants, 1, blocks[:5], 9, b"2")

        await b.receive_block(block_5_2[5])
        assert b.lca_block.header_hash == blocks[4].header_hash
        await b.receive_block(block_5_3[5])
        assert b.lca_block.header_hash == blocks[4].header_hash

        reorg = bt.get_consecutive_blocks(test_constants, 6, [], 9, b"3")
        for i in range(1, len(reorg)):
            await b.receive_block(reorg[i])
        assert b.lca_block.header_hash == blocks[0].header_hash

        await connection.close()
        b.shut_down() 

def test_basic_reorg(self):
        blocks = bt.get_consecutive_blocks(test_constants, 100, [], 9)
        db_path = Path("blockchain_test.db")
        if db_path.exists():
            db_path.unlink()
        connection = await aiosqlite.connect(db_path)
        coin_store = await CoinStore.create(connection)
        store = await BlockStore.create(connection)
        b: Blockchain = await Blockchain.create(coin_store, store, test_constants)

        for i in range(1, len(blocks)):
            await b.receive_block(blocks[i])
        assert b.get_current_tips()[0].height == 100

        blocks_reorg_chain = bt.get_consecutive_blocks(
            test_constants, 30, blocks[:90], 9, b"2"
        )
        for i in range(1, len(blocks_reorg_chain)):
            reorg_block = blocks_reorg_chain[i]
            result, removed, error_code = await b.receive_block(reorg_block)
            if reorg_block.height < 90:
                assert result == ReceiveBlockResult.ALREADY_HAVE_BLOCK
            elif reorg_block.height < 99:
                assert result == ReceiveBlockResult.ADDED_AS_ORPHAN
            elif reorg_block.height >= 100:
                assert result == ReceiveBlockResult.ADDED_TO_HEAD
            assert error_code is None
        assert b.get_current_tips()[0].height == 119

        await connection.close()
        b.shut_down() 

def test_basic_blockchain(self):
        db_path = Path("blockchain_test.db")
        if db_path.exists():
            db_path.unlink()
        connection = await aiosqlite.connect(db_path)
        coin_store = await CoinStore.create(connection)
        store = await BlockStore.create(connection)
        bc1 = await Blockchain.create(coin_store, store, test_constants)
        assert len(bc1.get_current_tips()) == 1
        genesis_block = bc1.get_current_tips()[0]
        assert genesis_block.height == 0
        assert (bc1.get_next_difficulty(genesis_block)) == genesis_block.weight
        assert bc1.get_next_min_iters(bc1.genesis) > 0

        await connection.close()
        bc1.shut_down() 

def test_deadlock(self):
        blocks = bt.get_consecutive_blocks(test_constants, 10, [], 9, b"0")
        db_filename = Path("blockchain_test.db")

        if db_filename.exists():
            db_filename.unlink()

        connection = await aiosqlite.connect(db_filename)
        db = await BlockStore.create(connection)
        tasks = []

        for i in range(10000):
            rand_i = random.randint(0, 10)
            if random.random() < 0.5:
                tasks.append(asyncio.create_task(db.add_block(blocks[rand_i])))
            if random.random() < 0.5:
                tasks.append(
                    asyncio.create_task(db.get_block(blocks[rand_i].header_hash))
                )
        await asyncio.gather(*tasks)
        await connection.close()
        db_filename.unlink() 

def test_get_puzzle_hash(self):
        num_blocks = 20
        blocks = bt.get_consecutive_blocks(test_constants, num_blocks, [], 9)
        db_path = Path("blockchain_test.db")
        if db_path.exists():
            db_path.unlink()
        connection = await aiosqlite.connect(db_path)
        coin_store = await CoinStore.create(connection)
        store = await BlockStore.create(connection)
        b: Blockchain = await Blockchain.create(coin_store, store, test_constants)
        try:
            for i in range(1, len(blocks)):
                await b.receive_block(blocks[i])
            assert b.get_current_tips()[0].height == num_blocks
            unspent = await coin_store.get_coin_record(
                blocks[1].header.data.coinbase.name(), blocks[-1].header
            )
            unspent_puzzle_hash = unspent.coin.puzzle_hash

            coins = await coin_store.get_coin_records_by_puzzle_hash(
                unspent_puzzle_hash, blocks[-1].header
            )
            assert len(coins) == (num_blocks + 1) * 2
        except Exception as e:
            await connection.close()
            Path("blockchain_test.db").unlink()
            b.shut_down()
            raise e

        await connection.close()
        Path("blockchain_test.db").unlink()
        b.shut_down() 

def test_set_spent(self):
        blocks = bt.get_consecutive_blocks(test_constants, 9, [], 9, b"0")

        db_path = Path("fndb_test.db")
        if db_path.exists():
            db_path.unlink()
        connection = await aiosqlite.connect(db_path)
        db = await CoinStore.create(connection)

        # Save/get block
        for block in blocks:
            await db.new_lca(block)
            unspent = await db.get_coin_record(block.header.data.coinbase.name())
            unspent_fee = await db.get_coin_record(block.header.data.fees_coin.name())
            assert block.header.data.coinbase == unspent.coin
            assert block.header.data.fees_coin == unspent_fee.coin

            await db.set_spent(unspent.coin.name(), block.height)
            await db.set_spent(unspent_fee.coin.name(), block.height)
            unspent = await db.get_coin_record(block.header.data.coinbase.name())
            unspent_fee = await db.get_coin_record(block.header.data.fees_coin.name())
            assert unspent.spent == 1
            assert unspent_fee.spent == 1

        await connection.close()
        Path("fndb_test.db").unlink() 

def test_basic_coin_store(self):
        blocks = bt.get_consecutive_blocks(test_constants, 9, [], 9, b"0")

        db_path = Path("fndb_test.db")
        if db_path.exists():
            db_path.unlink()
        connection = await aiosqlite.connect(db_path)
        db = await CoinStore.create(connection)

        # Save/get block
        for block in blocks:
            await db.new_lca(block)
            unspent = await db.get_coin_record(block.header.data.coinbase.name())
            unspent_fee = await db.get_coin_record(block.header.data.fees_coin.name())
            assert block.header.data.coinbase == unspent.coin
            assert block.header.data.fees_coin == unspent_fee.coin

        await connection.close()
        Path("fndb_test.db").unlink() 

def respond_peers(
        self, request: introducer_protocol.RespondPeers
    ) -> OutboundMessageGenerator:
        if self.server is None or self.global_connections is None:
            return
        conns = self.global_connections
        for peer in request.peer_list:
            conns.peers.add(peer)

        # Pseudo-message to close the connection
        yield OutboundMessage(NodeType.INTRODUCER, Message("", None), Delivery.CLOSE)

        unconnected = conns.get_unconnected_peers(
            recent_threshold=self.config["recent_peer_threshold"]
        )
        to_connect = unconnected[: self._num_needed_peers()]
        if not len(to_connect):
            return

        self.log.info(f"Trying to connect to peers: {to_connect}")
        for peer in to_connect:
            asyncio.create_task(self.server.start_client(peer, None)) 

def _on_connect(self) -> OutboundMessageGenerator:
        """
        Whenever we connect to another node / wallet, send them our current heads. Also send heads to farmers
        and challenges to timelords.
        """
        tips: List[Header] = self.blockchain.get_current_tips()
        for t in tips:
            request = full_node_protocol.NewTip(t.height, t.weight, t.header_hash)
            yield OutboundMessage(
                NodeType.FULL_NODE, Message("new_tip", request), Delivery.RESPOND
            )
        # If connected to a wallet, send the LCA
        lca = self.blockchain.lca_block
        new_lca = wallet_protocol.NewLCA(lca.header_hash, lca.height, lca.weight)
        yield OutboundMessage(
            NodeType.WALLET, Message("new_lca", new_lca), Delivery.RESPOND
        )

        # Send filter to node and request mempool items that are not in it
        my_filter = self.mempool_manager.get_filter()
        mempool_request = full_node_protocol.RequestMempoolTransactions(my_filter)

        yield OutboundMessage(
            NodeType.FULL_NODE,
            Message("request_mempool_transactions", mempool_request),
            Delivery.RESPOND,
        )

        # Update farmers and timelord with most recent information
        async for msg in self._send_challenges_to_timelords(Delivery.RESPOND):
            yield msg
        async for msg in self._send_tips_to_farmers(Delivery.RESPOND):
            yield msg