Python sqlalchemy.engine.Connection() Examples

def bind(self):
        """Return the current "bind".

        In online mode, this is an instance of
        :class:`sqlalchemy.engine.Connection`, and is suitable
        for ad-hoc execution of any kind of usage described
        in :ref:`sqlexpression_toplevel` as well as
        for usage with the :meth:`sqlalchemy.schema.Table.create`
        and :meth:`sqlalchemy.schema.MetaData.create_all` methods
        of :class:`~sqlalchemy.schema.Table`,
        :class:`~sqlalchemy.schema.MetaData`.

        Note that when "standard output" mode is enabled,
        this bind will be a "mock" connection handler that cannot
        return results and is only appropriate for a very limited
        subset of commands.

        """
        return self.connection 

def test_removes_withdrawn_bids(
    connection: Connection, bid_model: RowProxy, auction_model_with_a_bid: dict, ends_at: datetime, event_bus_mock: Mock
) -> None:
    auction = Auction(
        id=auction_model_with_a_bid.id,
        title=auction_model_with_a_bid.title,
        starting_price=get_dollars(auction_model_with_a_bid.starting_price),
        ends_at=ends_at,
        bids=[Bid(bid_model.id, bid_model.bidder_id, get_dollars(bid_model.amount))],
        ended=False,
    )
    auction.withdraw_bids([bid_model.id])

    SqlAlchemyAuctionsRepo(connection, event_bus_mock).save(auction)

    assert connection.execute(select([func.count()]).select_from(bids)).scalar() == 0 

def test_AuctionsRepo_UponSavingAuction_ClearsPendingEvents(
    connection: Connection, another_bidder_id: int, auction_model_with_a_bid: RowProxy, event_bus_mock: Mock
) -> None:
    repo = SqlAlchemyAuctionsRepo(connection, event_bus_mock)
    auction = repo.get(auction_model_with_a_bid.id)
    auction.place_bid(another_bidder_id, auction.current_price + get_dollars("1.00"))

    repo.save(auction)

    assert len(auction.domain_events) == 0


# @pytest.mark.usefixtures("transaction")
# def test_AuctionsRepo_UponSavingAuction_PostsPendingEventsViaEventBus(
#     connection: Connection, another_bidder_id: int, auction_model_with_a_bid: RowProxy, event_bus_stub: EventBusStub
# ) -> None:
#     repo = SqlAlchemyAuctionsRepo(connection, event_bus_stub)
#     auction = repo.get(auction_model_with_a_bid.id)
#     auction.place_bid(another_bidder_id, auction.current_price + get_dollars("1.00"))
#
#     repo.save(auction)
#
#     event_bus_stub.events 

def auction_model_with_a_bid(
    connection: Connection, winning_bid_amount: Decimal, bidder_id: int, ends_at: datetime
) -> RowProxy:
    connection.execute(
        auctions.insert().values(
            {
                "id": 1,
                "title": "Cool socks",
                "starting_price": winning_bid_amount / 2,
                "current_price": winning_bid_amount,
                "ends_at": ends_at,
                "ended": False,
            }
        )
    )
    connection.execute(bids.insert().values({"amount": winning_bid_amount, "auction_id": 1, "bidder_id": bidder_id}))
    return connection.execute(auctions.select(whereclause=auctions.c.id == 1)).first() 

def test_create_drop_constructor_bound(self):
        for bind in (testing.db, testing.db.connect()):
            try:
                for args in (([bind], {}), ([], {"bind": bind})):
                    metadata = MetaData(*args[0], **args[1])
                    table = Table(
                        "test_table", metadata, Column("foo", Integer)
                    )
                    assert metadata.bind is table.bind is bind
                    metadata.create_all()
                    is_true(inspect(bind).has_table(table.name))
                    metadata.drop_all()
                    table.create()
                    table.drop()
                    is_false(inspect(bind).has_table(table.name))
            finally:
                if isinstance(bind, engine.Connection):
                    bind.close() 

def db_connection(module_scoped_container_getter) -> Connection:
    retries = 60
    while True:
        try:
            connection = get_connection()
            try:
                yield connection
            finally:
                if not connection.closed:
                    connection.close()
                break
        except Exception as e:
            print(str(e))
            if retries > 0:
                retries -= 1
                time.sleep(1)
                continue
            raise 

def bind(self):
        """Return the current "bind".

        In online mode, this is an instance of
        :class:`sqlalchemy.engine.Connection`, and is suitable
        for ad-hoc execution of any kind of usage described
        in :ref:`sqlexpression_toplevel` as well as
        for usage with the :meth:`sqlalchemy.schema.Table.create`
        and :meth:`sqlalchemy.schema.MetaData.create_all` methods
        of :class:`~sqlalchemy.schema.Table`,
        :class:`~sqlalchemy.schema.MetaData`.

        Note that when "standard output" mode is enabled,
        this bind will be a "mock" connection handler that cannot
        return results and is only appropriate for a very limited
        subset of commands.

        """
        return self.connection 

def get_single_auction(self, conn: Connection) -> GetSingleAuction:
        return SqlGetSingleAuction(conn) 

def auctions_repo(self, conn: Connection, event_bus: EventBus) -> AuctionsRepository:
        return SqlAlchemyAuctionsRepo(conn, event_bus) 

def assert_customer_with_given_email_exists(connection: Connection, email: str) -> None:
    assert connection.execute(customers.select().where(customers.c.email == email)).first() 

def __init__(self, connection: Connection) -> None:
        self._conn = connection 

def __init__(self, connection: Connection, event_bus: EventBus) -> None:
        self._conn = connection
        self._event_bus = event_bus 

def bidder_id(connection: Connection) -> int:
    return 1 

def bid_model(connection: Connection, auction_model_with_a_bid: RowProxy) -> RowProxy:
    return connection.execute(bids.select().where(bids.c.auction_id == auction_model_with_a_bid.id)).first() 

def test_gets_existing_auction(
    connection: Connection,
    auction_model_with_a_bid: RowProxy,
    bid_model: RowProxy,
    ends_at: datetime,
    event_bus_mock: Mock,
) -> None:
    auction = SqlAlchemyAuctionsRepo(connection, event_bus_mock).get(auction_model_with_a_bid.id)

    assert auction.id == auction_model_with_a_bid.id
    assert auction.title == auction_model_with_a_bid.title
    assert auction.starting_price == get_dollars(auction_model_with_a_bid.starting_price)
    assert auction.current_price == get_dollars(bid_model.amount)
    assert auction.ends_at == ends_at
    assert set(auction.bids) == {Bid(bid_model.id, bid_model.bidder_id, get_dollars(bid_model.amount))} 

def test_saves_auction_changes(
    connection: Connection,
    another_bidder_id: int,
    bid_model: RowProxy,
    auction_model_with_a_bid: RowProxy,
    ends_at: datetime,
    event_bus_mock: Mock,
) -> None:
    new_bid_price = get_dollars(bid_model.amount * 2)
    auction = Auction(
        id=auction_model_with_a_bid.id,
        title=auction_model_with_a_bid.title,
        starting_price=get_dollars(auction_model_with_a_bid.starting_price),
        ends_at=ends_at,
        bids=[
            Bid(bid_model.id, bid_model.bidder_id, get_dollars(bid_model.amount)),
            Bid(None, another_bidder_id, new_bid_price),
        ],
        ended=True,
    )

    SqlAlchemyAuctionsRepo(connection, event_bus_mock).save(auction)

    assert connection.execute(select([func.count()]).select_from(bids)).scalar() == 2
    proxy = connection.execute(select([auctions]).where(auctions.c.id == auction_model_with_a_bid.id)).first()
    assert proxy.current_price == new_bid_price.amount
    assert proxy.ended 

def example_auction(connection: Connection, another_user: str) -> Generator[int, None, None]:
    ends_at = datetime.now() + timedelta(days=3)
    result_proxy = connection.execute(
        auctions.insert(
            {"title": "Super aukcja", "starting_price": "0.99", "current_price": "1.00", "ends_at": ends_at}
        )
    )
    auction_id = result_proxy.lastrowid
    connection.execute(bids.insert({"auction_id": auction_id, "amount": "1.00", "bidder_id": another_user}))
    yield int(auction_id)
    connection.execute(bids.delete(bids.c.auction_id == auction_id))
    connection.execute(auctions.delete(auctions.c.id == auction_id)) 

def get_saga_data_repo(self, connection: Connection) -> SagaDataRepo:
        return SagaDataRepo(connection) 

def repo(connection: Connection) -> SagaDataRepo:
    return SagaDataRepo(connection) 

def test_saving_and_reading(
    repo: SagaDataRepo, connection: Connection, data: PayingForWonItemSagaData, json_repr: dict
) -> None:
    saga_uuid = uuid4()

    connection.execute(saga_data_table.insert(values={"uuid": saga_uuid, "json": json.dumps(json_repr)}))

    assert repo.get(saga_uuid, type(data)) == data

    connection.execute(saga_data_table.delete().where(saga_data_table.c.uuid == saga_uuid))

    repo.save(saga_uuid, data)

    row = connection.execute(saga_data_table.select(saga_data_table.c.uuid == saga_uuid)).first()
    assert json.loads(row.json) == json_repr 

def __init__(self, connection: Connection) -> None:
        self._connection = connection 

def test_create_drop_err_metadata(self):
        metadata = MetaData()
        Table("test_table", metadata, Column("foo", Integer))
        for meth in [metadata.create_all, metadata.drop_all]:
            assert_raises_message(
                exc.UnboundExecutionError,
                "MetaData object is not bound to an Engine or Connection.",
                meth,
            ) 

def test_create_drop_err_table(self):
        metadata = MetaData()
        table = Table("test_table", metadata, Column("foo", Integer))

        for meth in [table.create, table.drop]:
            assert_raises_message(
                exc.UnboundExecutionError,
                (
                    "Table object 'test_table' is not bound to an Engine or "
                    "Connection."
                ),
                meth,
            ) 

def test_create_drop_bound(self):

        for meta in (MetaData, ThreadLocalMetaData):
            for bind in (testing.db, testing.db.connect()):
                metadata = meta()
                table = Table("test_table", metadata, Column("foo", Integer))
                metadata.bind = bind
                assert metadata.bind is table.bind is bind
                metadata.create_all()
                assert table.exists()
                metadata.drop_all()
                table.create()
                table.drop()
                assert not table.exists()

                metadata = meta()
                table = Table("test_table", metadata, Column("foo", Integer))

                metadata.bind = bind

                assert metadata.bind is table.bind is bind
                metadata.create_all()
                assert table.exists()
                metadata.drop_all()
                table.create()
                table.drop()
                assert not table.exists()
                if isinstance(bind, engine.Connection):
                    bind.close() 

def test_clauseelement(self):
        metadata = MetaData()
        table = Table("test_table", metadata, Column("foo", Integer))
        metadata.create_all(bind=testing.db)
        try:
            for elem in [
                table.select,
                lambda **kwargs: sa.func.current_timestamp(**kwargs).select(),
                # func.current_timestamp().select,
                lambda **kwargs: text("select * from test_table", **kwargs),
            ]:
                for bind in (testing.db, testing.db.connect()):
                    try:
                        e = elem(bind=bind)
                        assert e.bind is bind
                        e.execute().close()
                    finally:
                        if isinstance(bind, engine.Connection):
                            bind.close()

                e = elem()
                assert e.bind is None
                assert_raises(exc.UnboundExecutionError, e.execute)
        finally:
            if isinstance(bind, engine.Connection):
                bind.close()
            metadata.drop_all(bind=testing.db) 

def pessimistic_connection_handling(some_engine: Engine) -> None:
    @event.listens_for(some_engine, "engine_connect")
    def ping_connection(  # pylint: disable=unused-variable
        connection: Connection, branch: bool
    ) -> None:
        if branch:
            # 'branch' refers to a sub-connection of a connection,
            # we don't want to bother pinging on these.
            return

        # turn off 'close with result'.  This flag is only used with
        # 'connectionless' execution, otherwise will be False in any case
        save_should_close_with_result = connection.should_close_with_result
        connection.should_close_with_result = False

        try:
            # run a SELECT 1.   use a core select() so that
            # the SELECT of a scalar value without a table is
            # appropriately formatted for the backend
            connection.scalar(select([1]))
        except exc.DBAPIError as err:
            # catch SQLAlchemy's DBAPIError, which is a wrapper
            # for the DBAPI's exception.  It includes a .connection_invalidated
            # attribute which specifies if this connection is a 'disconnect'
            # condition, which is based on inspection of the original exception
            # by the dialect in use.
            if err.connection_invalidated:
                # run the same SELECT again - the connection will re-validate
                # itself and establish a new connection.  The disconnect detection
                # here also causes the whole connection pool to be invalidated
                # so that all stale connections are discarded.
                connection.scalar(select([1]))
            else:
                raise
        finally:
            # restore 'close with result'
            connection.should_close_with_result = save_should_close_with_result 

def get_connection() -> Connection:
    return create_engine(
        'mysql+pymysql://root:example@127.0.0.1:13306/test?charset=utf8mb4'
    ).connect() 

def test_register(client: testing.FlaskClient, connection: Connection) -> None:
    response = client.post(
        "/register",
        headers={"Content-type": "application/json"},
        json={"email": "test+register@cleanarchitecture.io", "password": "Dummy123!"},
    )

    assert response.status_code == 200
    json_response_body = response.json.copy()
    assert isinstance(json_response_body["response"]["user"].pop("authentication_token"), str)
    assert isinstance(json_response_body["response"]["user"].pop("id"), str)
    assert json_response_body == {"meta": {"code": 200}, "response": {"user": {}}}
    assert_customer_with_given_email_exists(connection, "test+register@cleanarchitecture.io") 

def start_new_payment(payment_uuid: UUID, customer_id: int, amount: Money, description: str, conn: Connection) -> None:
    conn.execute(
        payments.insert(
            {
                "uuid": str(payment_uuid),
                "customer_id": customer_id,
                "amount": int(amount.amount) * 100,
                "currency": amount.currency.iso_code,
                "description": description,
                "status": PaymentStatus.NEW.value,
            }
        )
    ) 

def get_pending_payments(customer_id: int, conn: Connection) -> List[PaymentDto]:
    rows = conn.execute(
        payments.select((payments.c.customer_id == customer_id) & (payments.c.status == PaymentStatus.NEW.value))
    ).fetchall()
    return [PaymentDto.from_row(row) for row in rows]