async def main() -> None: db = Aerie(DATABASE_URL) # create tables await db.schema.drop_tables() await db.schema.create_tables() # create some users async with db.transaction() as tx: await tx.execute( insert(users).values([ { 'id': 1, 'name': 'One' }, { 'id': 2, 'name': 'Two' }, { 'id': 3, 'name': 'Three' }, ])) stmt = select(users).where(users.c.id == 2) result = await tx.execute(stmt) user_id2 = result.one() print('User with ID 2 has name: %s' % user_id2.name) # you can read db w/o transaction stmt = select(users).where(users.c.id == 3) user_id3 = await db.execute(stmt).one() print('User with ID 3 has name: %s' % user_id3.name)
async def main() -> None: db = Aerie(DATABASE_URL) # create tables await db.schema.drop_tables() await db.schema.create_tables() # create some users async with db.transaction() as tx: stmt = insert(users).values([ { 'id': 1, 'name': 'One' }, { 'id': 2, 'name': 'Two' }, { 'id': 3, 'name': 'Three' }, ]) await tx.execute(stmt) result = await tx.execute( text('select * from users where id = :user_id'), {'user_id': 2}) user_id2 = result.one() print('User with ID 2 has name: %s' % user_id2.name) # you can read db w/o transaction user_id3 = await db.execute('select * from users where id = :user_id', { 'user_id': 3 }).one() print('User with ID 3 has name: %s' % user_id3.name)
async def test_first(db: Aerie) -> None: async with db.session() as session: user = await session.query(User).first() assert user assert isinstance(user, User) assert user.id == 1 assert user.name == 'User One'
async def test_only_with_strings(db: Aerie) -> None: async with db.session() as session: user = await session.query(User).only('id').first() assert user assert user.id == 1 with pytest.raises(MissingGreenlet): assert user.name
async def test_to_string(db: Aerie) -> None: async with db.session() as session: query = session.query(User).where(User.id == 1) assert query.to_string( ) == 'SELECT users.id, users.name \nFROM users \nWHERE users.id = 1' assert str( query ) == 'SELECT users.id, users.name \nFROM users \nWHERE users.id = 1'
async def test_delete(db: Aerie) -> None: async with db.session() as session: address = Address(id=10, city='10', street='10') session.add(address) await session.flush([address]) assert await session.query(Address).where(Address.city == '10' ).exists() is True await session.query(Address).where(Address.city == '10').delete() assert await session.query(Address).where(Address.city == '10' ).exists() is False
async def test_having(db: Aerie) -> None: async with db.session() as session: results = await session.query(User).having(User.id == 1 ).group_by(User.id).all() assert len(results) == 1 assert results[0].id == 1
async def test_filter_by(db: Aerie) -> None: async with db.session() as session: results = await session.query(User).filter_by(name='User Two').all() assert len(results) == 1 assert results[0].name == 'User Two'
async def test_left_join(db: Aerie) -> None: async with db.session() as session: results = await session.query(User).left_join(Profile).order_by( Profile.last_name.desc()).all() assert len(results) == 3
async def test_exists(db: Aerie) -> None: async with db.session() as session: assert await session.query(User).where(User.id == 1).exists() is True assert await session.query(User).where(User.id == -1).exists() is False
async def test_count(db: Aerie) -> None: async with db.session() as session: assert await session.query(User).count() == 3
async def test_join(db: Aerie) -> None: async with db.session() as session: results = await session.query(User).join(User.profile).order_by( Profile.last_name.desc()).all() assert results[0].id == 2 assert len(results) == 2 # user 3 has no profile associated
async def test_slice(db: Aerie) -> None: async with db.session() as session: results = await session.query(User).slice(0, 2).all() assert len(results) == 2 assert results[0].id == 1
async def test_query_evaluates_to_all(db: Aerie) -> None: async with db.session() as session: users = await session.query(User) assert len(users) == 3 assert isinstance(users[0], User)
async def test_choices_dict(db: Aerie) -> None: async with db.session() as session: users = await session.query(User).choices_dict() assert len(users) == 3 assert users[0] == {'value': 1, 'label': 'User One'}
async def test_choices(db: Aerie) -> None: async with db.session() as session: users = await session.query(User).choices() assert len(users) == 3 assert users[0] == (1, 'User One')
async def test_dump(db: Aerie) -> None: async with db.session() as session: writer = io.StringIO() session.query(User).where(User.id == 1).dump(writer) # noqa assert writer.getvalue( ) == 'SELECT users.id, users.name \nFROM users \nWHERE users.id = 1'
async def test_order_by(db: Aerie) -> None: async with db.session() as session: results = await session.query(User).order_by(User.id.desc()).all() assert len(results) == 3 assert results[0].id == 3
async def test_offset(db: Aerie) -> None: async with db.session() as session: results = await session.query(User).offset(1).all() assert len(results) == 2 assert results[0].id == 2
async def test_prefetch_many_to_many(db: Aerie) -> None: async with db.session() as session: result = await session.query(User).prefetch(User.addresses).first() assert result assert len(result.addresses) == 1
async def test_preload_one_to_one(db: Aerie) -> None: async with db.session() as session: result = await session.query(User).preload(User.profile).first() assert result assert result.profile.first_name == 'User'
async def test_one_when_no_results(db: Aerie) -> None: async with db.session() as session: with pytest.raises(NoResultsError): await session.query(User).where(User.id == -1).one()
async def test_where(db: Aerie) -> None: async with db.session() as session: results = await session.query(User).where(User.id == 1).all() assert len(results) == 1 assert results[0].id == 1
async def test_one_or_none(db: Aerie) -> None: async with db.session() as session: user = await session.query(User).where(User.id == 1).one_or_none() assert user assert user.id == 1
async def test_one_or_none_when_many_results(db: Aerie) -> None: async with db.session() as session: with pytest.raises(TooManyResultsError): await session.query(User).one_or_none()
async def test_one_or_none_when_no_results(db: Aerie) -> None: async with db.session() as session: assert await session.query(User).where(User.id == -1 ).one_or_none() is None