def insert_many(cls,
objs: List[BaseDBModel],
t: _transaction = None,
ignore=False):
"""
批量插入接口
:param objs:
:param t:
:param ignore: 执行insert ignore语义
:return:
"""
cls._check_meta()
if not objs:
return None
_sql_tpls = set()
_params = []
for obj in objs:
if not isinstance(obj, cls):
raise Exception(
u'ERROR: Unknown type: {} in valid type: {}'.format(
type(obj), cls))
else:
_sql_obj = obj._insert_ignore_sql if ignore else obj._insert_sql
_sql_tpls.add(_sql_obj.sql)
_params.append(_sql_obj.param)
if len(_sql_tpls) == 1:
# 如果所有生成的sql一样，说明插入的对象一样
_sql_tpl = _sql_tpls.pop()
else:
# 如果生成的sql不一样，说明存在不同的插入对象
# 这种情况可能会存在插入Null值，请注意
_sql_tpl = cls._insert_ignore_sql if ignore else cls._insert_sql
_params = [obj.get_valid_fields(for_save=True) for obj in objs]
mydb = MyDBApi(config=cls._get_db_conf(), t=t)
return mydb.insert_many(_sql_tpl, _params)
def upsert(self, t: _transaction = None, *update_fields):
_valid_fields = self.get_valid_fields(for_save=True)
if not update_fields:
update_fields = list(_valid_fields.keys())
sql = self.__META__.get_upsert_sql_tpl().format(
update_fields=', '.join('{field}=%({field})s'.format(field=field)
for field in update_fields))
param = _valid_fields
mydb = MyDBApi(config=self._get_db_conf(), t=t)
return mydb.insert_one(sql, param)
def create(cls, sql: SQL = None, t: _transaction = None):
"""
:param sql:
:param t:
:return:
"""
cls._check_meta()
mydb = MyDBApi(config=cls._get_db_conf(), t=t)
return mydb.insert_one(sql.sql)
def drop(cls, ifexists: bool = False, t: _transaction = None):
"""
:param ifexists:
:param t:
:return:
"""
cls._check_meta()
mydb = MyDBApi(config=cls._get_db_conf(), t=t)
_sql = cls.__META__.get_drop_sql_tpl(ifexists=ifexists)
return mydb.delete(_sql)
def start_transaction(
cls,
db: Union[str, dict] = None,
pessimistic: bool = True,
on_commit_failure: List[callable] = None) -> _transaction:
cls._check_meta()
if isinstance(db, dict):
config = db
else:
config = cls._get_db_conf(db=db)
mydb = MyDBApi(config=config)
with mydb.start_transaction(pessimistic=pessimistic,
on_commit_failure=on_commit_failure) as _t:
yield _t
def delete(self, t: _transaction = None):
"""
删除当前对象对应的数据库记录
:param t:
:return:
"""
filters = self.pk_fields
f = []
param = {}
for key, val in filters.items():
f.append(u'{key}=%(f_{key})s'.format(key=key))
param[u'f_' + key] = val
sql = self.__META__.get_delete_sql_tpl().format(filter=' AND '.join(f))
mydb = MyDBApi(config=self._get_db_conf(), t=t)
return mydb.delete(sql, param)
def delete_many(cls, t: _transaction = None, **terms):
"""
批量查询接口
:param t:
:param terms: 删除过滤条件
:return:
"""
cls._check_meta()
if not terms:
raise Exception(u'ERROR: Unknow delete terms')
parsed = parse(**terms)
param = parsed['param']
delete_tpl = cls.__META__.get_delete_sql_tpl()
sql = delete_tpl.format(filter=parsed['filter'])
mydb = MyDBApi(config=cls._get_db_conf(), t=t)
return mydb.delete(sql, param)
def update(self, t: _transaction = None, **filters):
"""
使用当前对象的值去更新数据库记录
:param t:
:param filters: 当filters参数非空，函数表示用当前的值去更新所有符合filter条件的数据
:return:
"""
if not filters:
filters = self.pk_fields
sql_obj = self._update_sql
sql = sql_obj.sql
param = sql_obj.param
parsed = parse(**filters)
param.update(parsed['param'])
sql = sql.format(filter=parsed['filter'])
mydb = MyDBApi(config=self._get_db_conf(), t=t)
return mydb.insert_one(sql, param)
def _query_by_parsed_terms(cls,
return_columns=None,
db=None,
table=None,
t=None,
for_update=False,
parsed: ParsedResult = None):
cls._check_meta()
if not return_columns:
return_columns = cls.__META__.fields_with_tablename
if not for_update:
sql = cls.__META__.get_select_sql_tpl(db=db, table=table).format(
return_columns=', '.join(return_columns),
filter=parsed['filter'])
else:
sql = cls.__META__.get_for_update_sql_tpl(
db=db,
table=table).format(return_columns=', '.join(return_columns),
filter=parsed['filter'])
param = parsed['param']
mydb = MyDBApi(config=cls._get_db_conf(db=db), t=t)
return mydb.query_many(sql, param)
def count(cls,
return_columns='COUNT(1) as cnt',
db=None,
table=None,
join_table=None,
t: _transaction = None,
**terms) -> int:
cls._check_meta()
cnt_table = cls.__META__.get_full_table_name(db=db, table=table)
cnt_parsed = parse(**terms)
if join_table:
for join_t in join_table.keys():
join_parsed = parse_join(**join_table[join_t])
cnt_table += ' JOIN {} ON ({}) '.format(
join_t, join_parsed['filter'])
cnt_parsed['param'].update(join_parsed['param'])
_get_sql_tpl = cls.__META__.get_select_sql_tpl(db=db, table=cnt_table)
cnt_sql = _get_sql_tpl.format(return_columns=return_columns,
filter=cnt_parsed.filter)
config = cls._get_db_conf(db=db)
mydb = MyDBApi(config=config, t=t)
total_cnt = mydb.query(cnt_sql, cnt_parsed.param)[0]['cnt']
return int(total_cnt)
def insert(self, t: _transaction = None):
mydb = MyDBApi(config=self._get_db_conf(), t=t)
sql_obj = self._insert_sql
return mydb.insert_one(sql_obj.sql, param=sql_obj.param)
def dbi(self) -> MyDBApi:
return MyDBApi(config=self._get_db_conf())