async def test_merge(db: DatabaseInterface, table: Table):
id_ = 100
async with db.get_session() as sess:
await sess.execute("insert into {} values ({}, 'test', '')".format(
table.__tablename__, id_))
async with db.get_session() as sess:
await sess.merge(table(id=id_))
async def test_update(db: DatabaseInterface, table: Table):
name = "test2"
async with db.get_session() as sess:
await sess.update(table).set(table.name, name).where(table.id > 10)
async with db.get_session() as sess:
results = await sess.select(table).where(table.id > 10).all()
async for result in results:
assert result.name == name
async def test_upsert(db: DatabaseInterface, table: Table):
async with db.get_session() as sess:
query = sess.insert.rows(table(id=1, name="upsert",
email="notupdated"))
query = query.on_conflict(table.id).update(table.name)
await query.run()
async with db.get_session() as sess:
res = await sess.select(table).where(table.id == 1).first()
assert table.name == "upsert"
assert table.email != "notupdated"
async def test_rollback(db: DatabaseInterface, table: Table):
sess = db.get_session()
try:
await sess.start()
await sess.execute('delete from {} where 1=1'.format(
table.__tablename__))
await sess.rollback()
finally:
await sess.close()
async with db.get_session() as sess:
res = await sess.cursor('select count(*) from test')
assert (await res.fetch_row())[0] > 0
async def test_create_table(db: DatabaseInterface):
async with db.get_ddl_session() as sess:
await sess.create_table(
table_name, Column.with_name("id", Integer(), primary_key=True),
Column.with_name("name", String(128)),
Column.with_name("balance", Real()))
async with db.get_session() as sess:
assert await sess.fetch("select * from {}".format(table_name)) is None
async def test_upsert_multiple_constriants(db: DatabaseInterface,
table: Table):
idx_name = "{}_email_idx".format(table.__tablename__)
async with db.get_ddl_session() as sess:
await sess.create_index(table.__tablename__,
idx_name,
"email",
"name",
unique=True)
for i in range(51, 53):
async with db.get_session() as sess:
query = sess.insert.rows(
table(id=51, name="test1", email="*****@*****.**"))
query = query.on_conflict(table.name, table.email).nothing()
await query.run()
async with db.get_session() as sess:
res = await sess.select(table).where(table.id == 52).first()
assert res is None
async def test_insert(db: DatabaseInterface, table: Table):
async with db.get_session() as sess:
name = kwargs["name"]
email = kwargs["email"]
rows = []
for i in range(50):
rows.append(table(id=i, name=name.format(i),
email=email.format(i)))
await sess.insert.rows(*rows)
async def test_alter_column_type(db: DatabaseInterface):
async with db.get_ddl_session() as sess:
await sess.alter_column_type(table_name, "age", Real())
async with db.get_session() as sess:
await sess.execute(
"insert into {} values (1, 'Drizzt Do''Urden', 1.5)".format(
table_name))
result = await sess.fetch("select age from {}".format(table_name))
assert result['age'] == 1.5
async def test_create_unique_index(db: DatabaseInterface):
if isinstance(db.dialect, sqlite3.Sqlite3Dialect):
num_indexes = 2 # sqlite3 does't index primary keys
else:
num_indexes = 3
async with db.get_ddl_session() as sess:
await sess.create_index(table_name, "index_age", "age", unique=True)
assert await get_num_indexes(db) == num_indexes
fmt = "insert into {} values ({{}}, 'test', 10);".format(table_name)
async with db.get_session() as sess:
await sess.execute(fmt.format(100))
with pytest.raises(DatabaseException):
await sess.execute(fmt.format(101))
async def test_select(db: DatabaseInterface, table: Table):
async with db.get_session() as sess:
res = await sess.select(table).where(table.id == 1).first()
for attr, value in kwargs.items():
assert getattr(res, attr, object()) == value.format(res.id)
async def test_fetch(db: DatabaseInterface, table: Table):
async with db.get_session() as sess:
res = await sess.fetch('select * from {}'.format(table.__tablename__))
for attr, value in kwargs.items():
assert res[attr] == value.format(res["id"])
async def test_truncate(db: DatabaseInterface, table: Table):
async with db.get_session() as sess:
await sess.truncate(table)
async with db.get_session() as sess:
res = await sess.select(table).first()
assert res is None
async def test_delete(db: DatabaseInterface, table: Table):
async with db.get_session() as sess:
await sess.delete(table).where(table.id == 1)
async with db.get_session() as sess:
res = await sess.select(table).where(table.id == 1).first()
assert res is None
async def test_drop_table(db: DatabaseInterface):
async with db.get_ddl_session() as sess:
await sess.drop_table(table_name)
async with db.get_session() as sess:
with pytest.raises(DatabaseException):
await sess.execute("select * from {}".format(table_name))