async def test_mul():
async def init():
await db.execute(helper.Query(f'CREATE DATABASE `{t1}`;'))
async def clear():
await db.execute(helper.Query(f'DROP DATABASE `{t1}`;'))
t1 = 'trod_1'
async with db.BindContext(init=init, clear=clear):
async with db._impl.Executer.pool.acquire() as conn:
assert db.get_state().size == 1
assert db.get_state().freesize == 0
assert conn.echo is False
assert conn.db == 'trod'
assert conn.charset == 'utf8'
await db.select_db(t1)
async with db._impl.Executer.pool.acquire() as conn:
conn.db == t1
await db.execute(SETUP_QUERY)
await db.execute(
helper.Query("INSERT INTO `user` (`name`, `age`) VALUES (%s, %s);",
params=[('at7h', 22), ('gaven', 23), ('mejer', 24)]),
many=True,
)
users = await db.execute(
helper.Query("SELECT * FROM `user` WHERE `id` IN %s;",
params=[(26, 27, 28)]))
assert isinstance(users, db.FetchResult)
assert users.count == 3
assert users[0].name == 'at7h'
assert users[1].age == 23
assert users[2].name == 'mejer'
assert isinstance(users[2].created_at, datetime.datetime)
await db.execute(TEARDOWN_QUERY)
try:
await db.unbinding()
assert False, 'Should be raise UnboundError'
except err.UnboundError:
pass
async def clear():
await db.execute(helper.Query(f'DROP DATABASE `{t1}`;'))
async def init():
await db.execute(helper.Query(f'CREATE DATABASE `{t1}`;'))
async def test_sin():
async def init():
await db.execute(SETUP_QUERY)
async def clear():
await db.execute(TEARDOWN_QUERY)
async with db.BindContext(init=init, clear=clear, echo=True):
assert db.get_state().minsize == 1
assert db.get_state().maxsize == 15
assert db.get_state().size == 1
assert db.get_state().freesize == 1
async with db._impl.Executer.pool.acquire() as conn:
assert db.get_state().size == 1
assert db.get_state().freesize == 0
assert conn.echo is True
assert conn.db == 'trod'
assert conn.charset == 'utf8'
async with db._impl.Executer.pool.acquire() as conn:
assert db.get_state().size == 2
await db._impl.Executer.pool.clear()
try:
await db.binding(db.get_db_url())
assert False, 'Should be raise DuplicateBinding'
except err.DuplicateBinding:
pass
await db.unbinding()
assert db.get_state() is None
try:
await db.unbinding()
await db.execute(helper.Query("SELECT * FROM `user`;"))
assert False, 'Should be raise UnboundError'
except err.UnboundError:
pass
try:
await db.binding("postgres:/user:password@host:port/db")
assert False, 'Should be raise ValueError'
except ValueError:
pass
try:
await db.binding("postgres://*****:*****@host:port/db")
assert False, 'Should be raise UnsupportedError'
except err.UnsupportedError:
pass
try:
await db.binding(host='127.0.0.1', user='******', password='******')
assert False, 'Should be raise pymysql.err.OperationalError'
except pymysql.err.OperationalError:
pass
await db.binding(db.get_db_url(), maxsize=7, autocommit=True)
assert str(
db._impl.Executer.pool) == ("<Pool[1:7] for {}:{}/{}>".format(
db._impl.Executer.pool.connmeta.host,
db._impl.Executer.pool.connmeta.port,
db._impl.Executer.pool.connmeta.db))
assert db.get_state().maxsize == 7
assert db.is_bound() is True
try:
result = await db.execute('')
assert False, 'Should be raise ValueError'
except ValueError:
pass
result = await db.execute(
helper.Query("INSERT INTO `user` (`name`, `age`) VALUES (%s, %s);",
params=[('at7h', 22), ('gaven', 23), ('mejer', 24)]),
many=True,
)
assert isinstance(result, db.ExecResult)
assert result.affected == 3
assert result.last_id == 26
result = await db.execute(
helper.Query("INSERT INTO `user` (`name`, `age`) VALUES (%s, %s);",
params=['suwei', 35]), )
assert result.affected == 1
assert result.last_id == 29
assert str(result) == '(1, 29)'
users = await db.execute(
helper.Query("SELECT * FROM `user` WHERE `id` IN %s;",
params=[(78, 79)]), )
assert isinstance(users, list)
assert not users
users = await db.execute(helper.Query(
"SELECT * FROM `user` WHERE `id` IN %s;", params=[(78, 79)]),
rows=1)
assert users is None
users = await db.execute(helper.Query(
"SELECT * FROM `user` WHERE `id` IN %s;", params=[(26, 27, 28)]),
tdict=False)
assert isinstance(users, db.FetchResult)
assert isinstance(users[0], tuple)
assert users.count == 3
assert users[0][1] == 'at7h'
assert users[1][2] == 23
assert users[2][1] == 'mejer'
assert isinstance(users[2][3], datetime.datetime)
users = await db.execute(helper.Query(
"SELECT * FROM `user` WHERE `id` IN %s;", params=[(27, 28)]),
tdict=False,
rows=1)
assert isinstance(users, tuple)
assert len(users) == 4
assert users[0] == 27
assert users[1] == 'gaven'
assert users[2] == 23
users = await db.execute(
helper.Query("SELECT * FROM `user` WHERE `id` IN %s LIMIT 1;",
params=[(26, 27, 28)]),
tdict=False,
)
assert isinstance(users, db.FetchResult)
assert isinstance(users[0], tuple)
assert users.count == 1
assert users[0][0] == 26
assert users[0][1] == 'at7h'
users = await db.execute(helper.Query(
"SELECT * FROM `user` WHERE `id`=%s;", params=[100]),
tdict=False,
rows=1)
assert not users
users = await db.execute(helper.Query(
"SELECT * FROM `user` WHERE `id` IN %s;", params=[(26, 27, 28)]),
rows=1)
assert isinstance(users, util.tdict)
assert len(users) == 4
assert users.id == 26
assert users.name == 'at7h'
assert users.age == 22
users = await db.execute(
helper.Query("SELECT * FROM `user` WHERE `id` IN %s LIMIT 1;",
params=[(28, 29)]), )
assert isinstance(users, db.FetchResult)
assert users.count == 1
assert isinstance(users[0], util.tdict)
assert users[0].id == 28
assert users[0].name == 'mejer'
users = await db.execute(
helper.Query("SELECT * FROM `user` WHERE `id` IN %s;",
params=[(26, 27, 28)]))
assert isinstance(users, db.FetchResult)
assert users.count == 3
assert users[0].name == 'at7h'
assert users[1].age == 23
assert users[2].name == 'mejer'
assert isinstance(users[2].created_at, datetime.datetime)
users = await db.execute(
helper.Query("SELECT * FROM `user` WHERE `created_at` < %s ;",
params=[datetime.datetime.now(tz)]))
assert isinstance(users, db.FetchResult)
assert users.count == 4
assert isinstance(users[0], util.tdict)
assert users[0].name == 'at7h'
assert users[1].age == 23
assert users[2].name == 'mejer'
users = await db.execute(
helper.Query("SELECT * FROM `user` WHERE `created_at` >= %s ;",
params=[datetime.datetime.now(tz)]))
assert isinstance(users, db.FetchResult)
assert not users
users = await db.execute(helper.Query(
"SELECT * FROM `user` WHERE `created_at` >= %s ;",
params=[datetime.datetime.now(tz)]),
rows=1)
assert users is None
result = await db.execute(
helper.Query("DELETE FROM `user` WHERE `id`=%s;", params=[1]))
assert result.affected == 0
assert result.last_id == 0
assert repr(result) == 'ExecResult(affected: 0, last_id: 0)'
result = await db.execute(
helper.Query("DELETE FROM `user` WHERE `id`=%s;", params=[26]))
assert result.affected == 1
assert result.last_id == 0
assert repr(result) == 'ExecResult(affected: 1, last_id: 0)'
try:
await db.execute(
helper.Query("SELECT * FROM `user` WHER `id` IN %s;",
params=[(26, 27, 28)]))
assert False, "Should be raise pymysql.err.ProgrammingError"
except pymysql.err.ProgrammingError:
pass
try:
await db.execute(
helper.Query(
"INSERT INTO `user` (`name`, `age`) VALUS ('n', 1);", ), )
assert False, "Should be raise pymysql.err.ProgrammingError"
except pymysql.err.ProgrammingError:
pass
import datetime
import pytest
import pytz
import pymysql
from trod import db, err, util, _helper as helper
tz = pytz.timezone('Asia/Shanghai')
SETUP_QUERY = helper.Query(
"CREATE TABLE IF NOT EXISTS `user` ("
"`id` int(20) unsigned NOT NULL AUTO_INCREMENT,"
"`name` varchar(100) NOT NULL DEFAULT '' COMMENT 'username',"
"`age` int(20) NOT NULL DEFAULT '0' COMMENT 'user age',"
"`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,"
"PRIMARY KEY (`id`),"
"UNIQUE KEY `idx_name` (`name`)"
") ENGINE=InnoDB AUTO_INCREMENT=26 "
"DEFAULT CHARSET=utf8 COMMENT='user info table';")
TEARDOWN_QUERY = helper.Query("DROP TABLE `user`;")
@pytest.mark.asyncio
async def test_sin():
async def init():
await db.execute(SETUP_QUERY)
async def clear():
await db.execute(TEARDOWN_QUERY)
def test_expr():
age = t.Int(name='age')
name = t.Char(name='name')
phone = t.VarChar(name='phone')
e = (age > 10) | (name == 'test')
assert helper.parse(e) == helper.Query('((`age` > %s) OR (`name` = %s));',
(10, 'test'))
e = (name == 'test') | (age > 10)
assert helper.parse(e) == helper.Query('((`name` = %s) OR (`age` > %s));',
('test', 10))
e = age + 1
assert helper.parse(e) == helper.Query('(`age` + %s);', (1, ))
e = 1 + age
assert helper.parse(e) == helper.Query('(%s + `age`);', (1, ))
e = age + '20'
assert helper.parse(e) == helper.Query('(`age` + %s);', (20, ))
e = 20 + age
assert helper.parse(e) == helper.Query('(%s + `age`);', (20, ))
e = age * '2'
assert helper.parse(e) == helper.Query('(`age` * %s);', (2, ))
e = 2 * age
assert helper.parse(e) == helper.Query('(%s * `age`);', (2, ))
e = 1000 / age
assert helper.parse(e) == helper.Query('(%s / `age`);', (1000, ))
e = age / 2
assert helper.parse(e) == helper.Query('(`age` / %s);', (2, ))
e = age ^ name
assert helper.parse(e) == helper.Query('(`age` # `name`);', ())
e = name ^ age
assert helper.parse(e) == helper.Query('(`name` # `age`);', ())
e = name == 'at7h'
assert helper.parse(e) == helper.Query('(`name` = %s);', ('at7h', ))
e = name != 'at7h'
assert helper.parse(e) == helper.Query('(`name` != %s);', ('at7h', ))
e = name <= 'at7h'
assert helper.parse(e) == helper.Query('(`name` <= %s);', ('at7h', ))
e = name >= 'at7h'
assert helper.parse(e) == helper.Query('(`name` >= %s);', ('at7h', ))
e = age < 90
assert helper.parse(e) == helper.Query('(`age` < %s);', (90, ))
e = age > 20
assert helper.parse(e) == helper.Query('(`age` > %s);', (20, ))
e = name >> None
assert helper.parse(e) == helper.Query('(`name` IS %s);', (None, ))
e = name << ['at7h', 'mejer']
assert helper.parse(e) == helper.Query('(`name` IN %s);',
(('at7h', 'mejer'), ))
e = name % 'at'
assert helper.parse(e) == helper.Query('(`name` LIKE BINARY %s);',
('at', ))
e = name**'at'
assert helper.parse(e) == helper.Query('(`name` LIKE %s);', ('at', ))
e = age[slice(20, 30)]
assert helper.parse(e) == helper.Query('(`age` BETWEEN %s AND %s);', (
20,
30,
))
e = name.concat(10)
assert helper.parse(e) == helper.Query('(`name` || %s);', ('10', ))
e = name.binand('at7h')
assert helper.parse(e) == helper.Query('(`name` & %s);', ('at7h', ))
e = name.binor('at7h')
assert helper.parse(e) == helper.Query('(`name` | %s);', ('at7h', ))
e = name.in_(['at7h', 'mejor'])
assert helper.parse(e) == helper.Query('(`name` IN %s);',
(('at7h', 'mejor'), ))
e = name.nin_(['at7h', 'mejor'])
assert helper.parse(e) == helper.Query('(`name` NOT IN %s);',
(('at7h', 'mejor'), ))
e = name.exists(['at7h', 'mejor'])
assert helper.parse(e) == helper.Query('(`name` EXISTS %s);',
(('at7h', 'mejor'), ))
e = name.nexists(['at7h', 'mejor'])
assert helper.parse(e) == helper.Query('(`name` NOT EXISTS %s);',
(('at7h', 'mejor'), ))
e = name.isnull()
assert helper.parse(e) == helper.Query('(`name` IS %s);', (None, ))
e = name.isnull(False)
assert helper.parse(e) == helper.Query('(`name` IS NOT %s);', (None, ))
e = name.regexp('at.*')
assert helper.parse(e) == helper.Query('(`name` REGEXP %s);', ('at.*', ))
e = name.regexp('at.*', i=False)
assert helper.parse(e) == helper.Query('(`name` REGEXP BINARY %s);',
('at.*', ))
e = phone.like(177)
assert helper.parse(e) == helper.Query('(`phone` LIKE %s);', ('177', ))
e = phone.like(177, i=False)
assert helper.parse(e) == helper.Query('(`phone` LIKE BINARY %s);',
('177', ))
e = phone.contains(7867)
assert helper.parse(e) == helper.Query('(`phone` LIKE %s);', ('%7867%', ))
e = phone.contains(7867, i=False)
assert helper.parse(e) == helper.Query('(`phone` LIKE BINARY %s);',
('%7867%', ))
e = name.endswith('7h')
assert helper.parse(e) == helper.Query('(`name` LIKE %s);', ('%7h', ))
e = name.endswith('7h', i=False)
assert helper.parse(e) == helper.Query('(`name` LIKE BINARY %s);',
('%7h', ))
e = name.startswith('at')
assert helper.parse(e) == helper.Query('(`name` LIKE %s);', ('at%', ))
e = name.startswith('at', i=False)
assert helper.parse(e) == helper.Query('(`name` LIKE BINARY %s);',
('at%', ))
e = age.between(10, 30)
assert helper.parse(e) == helper.Query('(`age` BETWEEN %s AND %s);',
(10, 30))
e = age.nbetween(10, 30)
assert helper.parse(e) == helper.Query('(`age` NOT BETWEEN %s AND %s);',
(10, 30))
e = age.asc()
assert helper.parse(e) == helper.Query('`age` ASC ;', ())
e = age.desc()
assert helper.parse(e) == helper.Query('`age` DESC ;', ())
e = age.as_('a')
assert helper.parse(e) == helper.Query('`age` AS `a` ;', ())
e = (age > 10) & (name == 'test')
assert helper.parse(e) == helper.Query('((`age` > %s) AND (`name` = %s));',
(10, 'test'))
e = (name == 'test') & (age > 10)
assert helper.parse(e) == helper.Query('((`name` = %s) AND (`age` > %s));',
('test', 10))
e = (age >= '20') & name.in_(['at7h', 'mejor']) | phone.startswith('153')
assert helper.parse(e) == helper.Query(
'(((`age` >= %s) AND (`name` IN %s)) OR (`phone` LIKE %s));',
(20, ('at7h', 'mejor'), '153%'))