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
