async def execute(self, sql, args=None, batch=False):
sql = sql.strip()
if args:
args = tuple_formater(args)
async with self.conn_pool.get() as conn:
if not self.autocommit:
await conn.begin()
try:
async with conn.cursor(aiomysql.DictCursor) as cur:
if batch is True:
await cur.executemany(sql, args or ())
else:
await cur.execute(sql, args or ())
affected = cur.rowcount
last_id = cur.lastrowid
if not self.autocommit:
await conn.commit()
except BaseException:
if not self.autocommit:
await conn.rollback()
exc_type, exc_value, _ = sys.exc_info()
error = exc_type(exc_value)
raise error
return Dict(last_id=last_id, affected=affected)
class BaseLog:
Bcolors = Dict(
HEADER='\033[95m',
OKBLUE='\033[94m',
OKGREEN='\033[92m',
WARNING='\033[93m',
ERROR='\033[91m',
ENDC='\033[0m',
)
@staticmethod
def now_time():
return datetime.now().strftime(TIME_FORMAT)
@staticmethod
def info(message):
print(message)
@staticmethod
def error(message):
print(message)
@staticmethod
def warning(message):
print(message)
async def text(self, sql, args=None, rows=None, batch=False):
""" A coroutine that execute sql text """
is_fetch = True
if 'SELECT' in sql or 'select' in sql:
result = await self.executer.fetch(sql, args=args, rows=rows)
else:
is_fetch = False
result = await self.executer.execute(sql, args=args, batch=batch)
return Dict(is_fetch=is_fetch, data=result)
async def _show_create(cls):
""" do show table create """
show_create_sql = SQL.show.create.format(table_name=cls.__meta__.table)
result = await RequestClient().fetch(
show_create_sql, rows=1
)
return Dict(
table_name=result['Table'], create_syntax=result['Create Table']
)
def db_info(self):
""" Get the basic info of the database connection """
info_dict = Dict()
if self.Client is None:
return info_dict
info_dict.update(
info=RequestClient.get_conn_info(),
status=RequestClient.get_conn_status()
)
return info_dict
def format_(self):
if self.where.operator == self.OPERATORS['in']:
_where_stmt = '{col}{ope}{values}'.format(col=self.where.column,
ope=self.where.operator,
values=self.where.value)
_arg = None
else:
_where_stmt = '{col}{ope}%s'.format(col=self.where.column,
ope=self.where.operator)
_arg = '{}'.format(self.where.value)
return Dict(where=_where_stmt, arg=_arg, col=self.where.column)
async def _show_struct(cls):
""" do show table struct """
table_name = cls.__meta__.table
show_clo_sql = SQL.show.columns.format(
table_name=table_name, rows=1
)
show_idx_sql = SQL.show.indexs.format(
table_name=table_name, rows=1
)
result = Dict(
columns=await RequestClient().fetch(show_clo_sql),
indexs=await RequestClient().fetch(show_idx_sql)
)
return result
def __init__(self, column, operator, value):
if operator not in self.OPERATORS:
raise ValueError(f'Does not support the {operator} operator')
if isinstance(value, str):
value = f"'{value}'"
if operator in ['in', 'IN', 'exists', 'EXISTS']:
if not isinstance(value, (list, tuple)):
raise ValueError(
f'The value of the operator {operator} should be list or tuple'
)
value = tuple(value)
self.where = Dict(column=column,
operator=self.OPERATORS[operator],
value=value)
async def do_test():
user = User(id=5,
name='test5',
num=589223,
password='******',
sex=1,
age=45)
result = await User.add(user)
self.assertEqual(result, 5)
self.assertEqual((await User.get(5)).num, 589223)
new_num = 787878
update_data = Dict(num=new_num)
result = await User.update(
update_data, And(User.sex == user.sex, User.age == user.age))
self.assertEqual(result.affected, 1)
updated_user = await User.get(5)
self.assertEqual(updated_user.num, new_num)
def __init__(self, column, comment='', name=None):
if name is None:
(_, _, _, text) = traceback.extract_stack()[-2]
self.name = text[:text.find('=')].strip()
else:
self.name = name
self.comment = comment
if isinstance(column, list):
self.column = column
else:
self.column = [column]
self.column = [f'`{c}`' for c in self.column]
self.options = Dict(
key_name=self.name,
cols=','.join(self.column),
comment=self.comment
)
self.is_modify = False
def __new__(cls, name, bases, attrs):
if name in ['_Model', '_TrodModel']:
return type.__new__(cls, name, bases, attrs)
@dict_formatter
def build_meta():
meta = {}
for arg in TABLE_DEFAULT:
if arg == '__table__':
table_name = attrs.get(arg, None)
if table_name is None:
Logger.warning(
"Did not give the table name by '__table__', use the model name"
)
table_name = name
meta[arg.strip('_')] = table_name
else:
meta[arg.strip('_')] = attrs.get(arg, None) or TABLE_DEFAULT[arg]
if arg in attrs.keys():
attrs.pop(arg)
return meta
attrs['__meta__'] = build_meta()
primary_key = None
field_stmt_map, key_stmt_map = {}, {}
field_inst_map, index_inst_map = OrderedDict(), OrderedDict()
pk_stmt = None
if attrs['__meta__'].auto_pk:
pk_stmt, primary_key = BaseField.build_default_id()
for attr_name, attr_instance in attrs.items():
if primary_key and attr_name == primary_key:
raise DuplicateFieldNameError('Duplicate field name `id`')
if isinstance(attr_instance, BaseField):
field_name = attr_instance.name if attr_instance.name else attr_name
attr_instance.name = field_name
field_stmt_map[attr_name] = f'`{field_name}` {attr_instance.build()}'
if hasattr(attr_instance, 'primary_key') and attr_instance.primary_key:
if primary_key is not None:
raise DuplicatePKError(
f'Duplicate primary key found for field {attr_name}'
)
primary_key = attr_name
field_inst_map[attr_name] = attr_instance
elif isinstance(attr_instance, BaseIndex):
key_stmt_map[attr_name] = attr_instance.build()
index_inst_map[attr_name] = attr_instance
else:
if not (attr_name.endswith('__') and attr_name.endswith('__', 0, 2)):
raise InvalidFieldType('Invalid model field {}'.format(attr_name))
if not primary_key:
raise NoPKError(
f"Primary key not found for table `{attrs['__meta__'].table}`"
)
build_items = []
if pk_stmt is not None:
build_items.append(pk_stmt)
for field in field_inst_map:
build_items.append(field_stmt_map[field])
build_items.append(f'PRIMARY KEY(`{primary_key}`)')
for index in index_inst_map:
build_items.append(key_stmt_map[index])
attrs['__meta__'].coldef = ', '.join(build_items)
attrs['__table__'] = Dict(
field_dict=field_inst_map,
index_dict=index_inst_map,
pk=primary_key
)
# This will be very important
for field in field_inst_map:
attrs.pop(field)
for index in index_inst_map:
attrs.pop(index)
return type.__new__(cls, name, bases, attrs)
class _Generator:
_sql_template = SQL.select.complete
_render_data_default = Dict(where_clause='',
group_clause='',
order_clause='',
limit_clause='')
@property
@dict_formatter
def _tpl(self):
return self._render_data_default.copy()
def __init__(self, model, cols):
self._model = model
self._cols = cols
self._values = None
self._has_func = False
self._has_col = False
self._render_data = self._tpl
def _render_query(self):
if not self._cols:
raise ValueError('No column given the query')
if not isinstance(self._cols, list):
raise ValueError(
f'The query column must be a list or tuple, now is {self._cols}'
)
cols, func_cols = [], []
for _c in self._cols:
if isinstance(_c, Func):
func_cols.append(_c.func)
self._has_func = True
else:
cols.append('`{}`'.format(_c))
self._has_col = True
cols.extend(func_cols)
self._render_data.cols = ','.join(cols)
self._render_data.table_name = self._model.__meta__.table
def _render(self):
self._render_query()
return self._sql_template.format(**self._render_data)
def filter(self, where):
""" Query condition filter """
if not isinstance(where, (_Where, _Logic)):
raise ValueError('Invalid filter condition')
where_format = where.format_()
self._model._has_cols_checker(where_format.col)
self._render_data.where_clause = "WHERE {}".format(where_format.where)
self._values = where_format.arg
return self
def group_by(self, *cols):
""" Generate group by clause """
group_by_tpl = "GROUP BY {cols}"
if not cols:
raise ValueError("Group by can't have no field")
col_names = []
for _c in cols:
col_names.append(_get_col_type_name(_c))
self._model._has_cols_checker(col_names)
self._render_data.group_clause = group_by_tpl.format(
cols=','.join([c.join('``') for c in col_names]))
return self
def order_by(self, col=None, desc=False):
""" Generate order by clause """
order_by_tpl = "ORDER BY {col} {desc}"
desc = 'DESC' if desc else 'ASC'
if col is None:
col = self._model.__table__.pk
col = _get_col_type_name(col)
self._model._has_cols_checker(col)
self._render_data.order_clause = order_by_tpl.format(
col=col.join('``'), desc=desc)
return self
async def rows(self, limit=1000, offset=0):
""" Generate limit clause """
limit_tpl = 'LIMIT {limit} OFFSET {offset}'
self._render_data.limit_clause = limit_tpl.format(limit=limit,
offset=offset)
result = await RequestClient().fetch(self._render(), args=self._values)
if self._has_func:
return _do_format(result)
return Loader(self._model, result).load()
async def first(self):
""" Select first """
limit = 'LIMIT 1'
self._render_data.limit_clause = limit
result = await RequestClient().fetch(self._render(),
args=self._values,
rows=1)
if self._has_func:
return _do_format(result)
return Loader(self._model, result).load()
async def all(self):
""" Select all """
result = await RequestClient().fetch(self._render(), args=self._values)
if self._has_func:
return _do_format(result)
return Loader(self._model, result).load()
async def scalar(self):
""" return a count """
if self._has_col is True:
raise RuntimeError(
'Invalid call, Maybe you can try to call first()')
result = await RequestClient().fetch(self._render(),
args=self._values,
rows=1)
if len(result) == 1:
result = list(result.values())[0]
else:
result = _do_format(result)
return result
@classmethod
def alter(cls,
new_dict,
table_name,
modify_list=None,
add_list=None,
drop_list=None):
""" Table alter syntax generator """
alter_clause = []
if modify_list is not None:
for col in modify_list:
col = _get_col_type_name(col)
if col in list(new_dict.field_dict.keys()):
alter_clause.append("MODIFY COLUMN `{}` {}".format(
col, new_dict.field_dict[col].build()))
else:
raise RuntimeError(f"Modify column/key '{col}' not found")
if add_list is not None:
for col in add_list:
col = _get_col_type_name(col)
if col in new_dict.field_dict:
cols = list(new_dict.field_dict.keys())
col_seq = cols.index(col)
if col_seq == 0:
alter_clause.append("ADD COLUMN `{}` {}".format(
col, new_dict.field_dict[col].build()))
else:
alter_clause.append(
"ADD COLUMN `{}` {} AFTER `{}`".format(
col, new_dict.field_dict[col].build(),
cols[col_seq - 1]))
elif col in new_dict.index_dict:
if isinstance(new_dict.index_dict[col], Key):
alter_clause.append("ADD KEY `{}` {}".format(
col, new_dict.field_dict[col].build()))
elif isinstance(new_dict.index_dict[col], UniqueKey):
alter_clause.append("ADD UNIQUE KEY `{}` {}".format(
col, new_dict.field_dict[col].build()))
else:
raise ValueError("Add an invalid 'Key' type")
else:
raise RuntimeError(f"Add column/key '{col}' not found")
if drop_list is not None:
for col in drop_list:
col = _get_col_type_name(col)
alter_clause.append(f"DROP COLUMN `{col}`")
return SQL.alter.format(table_name=table_name,
clause=', '.join(alter_clause))
def format_(self):
return Dict(where=self._LOGIC.join(self._all_cdtns),
arg=self._args,
col=self._cols)
from trod.model.loader import Loader
from trod.types.index import Key, UniqueKey
from trod.utils import Dict, dict_formatter, _do_format
SQL = Dict(
create=
"CREATE TABLE `{tn}` ({cd}) ENGINE={eg} DEFAULT CHARSET={cs} COMMENT='{cm}';",
drop="DROP TABLE `{table_name}`;",
show=Dict(tables='SHOW TABLES',
status='SHOW TABLE STATUS',
create="SHOW CREATE TABLE `{table_name}`;",
columns="SHOW FULL COLUMNS FROM `{table_name}`;",
indexs="SHOW INDEX FROM `{table_name}`;"),
exist=
"SELECT table_name FROM information_schema.tables WHERE table_schema='{schema}' AND table_name='{table}'",
alter="ALTER TABLE `{table_name}` {clause};",
insert="INSERT INTO `{table_name}` ({cols}) VALUES ({values});",
delete="DELETE FROM `{table_name}` WHERE {condition};",
update_=Dict(complete="UPDATE `{table_name}` SET {kv} WHERE {condition};",
no_where="UPDATE `{table_name}` SET {kv}"),
select=Dict(
complete=
"SELECT {cols} FROM `{table_name}` {where_clause} {group_clause} {order_clause} {limit_clause}",
by_id="SELECT {cols} FROM `{table_name}` WHERE `{condition}`=%s;",
by_ids=
"SELECT {cols} FROM `{table_name}` WHERE `{condition}` IN {data};",
))
Auto = Dict(on_create='on_create', on_update='on_update')
class _Where: