async def method_patch(self, request: Request, body: dict,
session: DBSession, msg_id: int, token: dict, *args,
**kwargs) -> BaseHTTPResponse:
try:
db_msg = session.get_msg_by_id(msg_id)
except DBMsgNotExistsException:
raise SanicMsgNotFoundException
if token.get('uid') is not db_msg.sender_id:
return await self.make_response_json(status=403)
req_model = ReqUpdateMsgDTO(body)
try:
db_msg = msg_queries.update_msg(session, db_msg.id, req_model)
except DBMsgNotExistsException:
raise SanicMsgNotFoundException
try:
session.commit()
except (DBIntegrityException, DBDataException):
raise SanicDBException
res_model = ResGetMsgDTO(db_msg)
return await self.make_response_json(body=res_model.dump(), status=200)
def full_delete_user(session: DBSession, uid: int):
db_user = session.get_user_by_id(uid)
if db_user is None or db_user.is_delete is True:
raise DBUserNotExistsException
session.delete(db_user)
async def method_delete(self, request: Request, body: dict,
session: DBSession, uid: int, token: str, *args,
**kwargs) -> BaseHTTPResponse:
if token.get('uid') != uid:
return await self.make_response_json(status=403)
try:
user_queries.delete_user(session, uid)
except DBUserNotExistsException:
raise SanicUserNotFoundException("User not found")
msgs = msg_queries.get_msgs(session, uid)
for msg in msgs:
try:
user = user_queries.get_user(session, user_id=msg.recipient_id)
except DBUserNotExistsException:
user = None
if uid is msg.sender_id and user is None:
msg.is_delete = True
try:
user = user_queries.get_user(session, user_id=msg.sender_id)
except DBUserNotExistsException:
user = None
if uid is msg.recipient_id and user is None:
msg.is_delete = True
try:
session.commit()
except (DBIntegrityException, DBDataException):
raise SanicDBException
return await self.make_response_json(body={}, status=200)
def get_all():
"""Return list of all categories"""
session = DBSession()
result = session.query(Categories).all()
session.close()
return result
def get(id):
"""Get user by id"""
session = DBSession()
user = session.query(Users).get(id)
session.close()
return user
def get(id):
"""Return category by id"""
session = DBSession()
category = session.query(Categories).get(id)
session.close()
return category
def get_all():
"""Return list of all posts."""
session = DBSession()
posts = session.query(Posts).filter(Posts.is_deleted == False). \
order_by(Posts.id.desc()).all()
session.close()
return posts
def get(id):
"""Return post by it's id."""
session = DBSession()
post = session.query(Posts).filter(Posts.id == id,
Posts.is_deleted == False).one()
session.close()
return post
def get_author(id):
"""Return author id by post's id."""
session = DBSession()
post = session.query(Posts).filter(Posts.id == id). \
options(load_only('author_id')).one()
session.close()
return post.author_id
def get_all_by_category(id):
"""Return list of all posts in the category by it's id."""
session = DBSession()
posts = session.query(Posts).join(Posts.categories). \
filter(Categories.id == id, Posts.is_deleted == False). \
order_by(Posts.id.desc()).all()
session.close()
return posts
def update(new_post, categories):
"""Update post.
Arguments:
new_post -- Posts object
categories - list of categories
"""
session = DBSession()
# get data
post = session.query(Posts).filter(Posts.id == new_post.id,
Posts.is_deleted == False).one()
# update data
if new_post.header is not None:
post.header = new_post.header
if new_post.content is not None:
post.content = new_post.content
if categories:
post.categories.clear()
for c in categories:
category = session.query(Categories).get(c)
post.categories.append(category)
post.modification_date = datetime.now().replace(
microsecond=0).isoformat(' ')
session.commit()
session.close()
def get_user(session: DBSession,
*,
username: str = None,
user_id: int = None) -> UserModel:
db_employee = None
if username is not None:
db_employee = session.get_user_by_username(username)
elif user_id is not None:
db_employee = session.get_user_by_id(user_id)
if db_employee is None or db_employee.is_delete is True:
raise DBUserNotExistsException
return db_employee
def validate(username, password):
"""Validate user.
If successfull - returns user's id, else - None.
"""
session = DBSession()
user = session.query(Users).filter(Users.username == username).first()
session.close()
if user is not None and user.password == password:
return user.id
else:
return None
def create_user(session: DBSession, user: ReqCreateUserDTO,
password: str) -> UserModel:
if session.get_user_by_username(user.username) is not None:
raise DBUserExistsException
new_user = UserModel(
username=user.username,
password=password,
first_name=user.first_name,
last_name=user.last_name,
)
session.add_model(new_user)
return new_user
def add(user):
"""Add new user"""
session = DBSession()
session.add(user)
session.commit()
session.close()
def add(category):
"""Add new category"""
session = DBSession()
session.add(category)
session.commit()
session.close()
def create_msg(session: DBSession, message: ReqCreateMsgDTO,
sender_id: int) -> MessageModel:
recip = get_user(session, username=message.recipient)
if recip is None or recip.is_delete is True:
raise DBUserNotExistsException
new_msg = MessageModel(
message=message.message,
sender_id=sender_id,
recipient_id=recip.id,
)
session.add_model(new_msg)
return new_msg
def delete_msg(session: DBSession, msg_id: int):
db_msg = session.get_msg_by_id(msg_id)
if db_msg is None or db_msg.is_delete is True:
raise DBMsgNotExistsException
db_msg.is_delete = True
def update_user(session: DBSession, uid: int,
user: ReqUpdateUserDTO) -> UserModel:
db_user = session.get_user_by_id(uid)
for attr in user.fields:
if hasattr(user, attr):
setattr(db_user, attr, getattr(user, attr))
return db_user
async def method_post(self, request: Request, body: dict,
session: DBSession, *args,
**kwargs) -> BaseHTTPResponse:
req_model = ReqCreateUserDTO(body)
hash_pass = generate_hash(req_model.password)
try:
db_user = user_query.create_user(session, req_model, hash_pass)
except DBUserExistsException:
raise SanicUserConflictException("User already exist")
try:
session.commit()
except (DBIntegrityException, DBDataException):
raise SanicDBException
response_model = ResGetUserDTO(db_user)
return await self.make_response_json(body=response_model.dump(),
status=201)
async def method_delete(self, request: Request, body: dict,
session: DBSession, msg_id: int, token: dict,
*args, **kwargs):
try:
db_msg = msg_queries.get_msg(session, msg_id)
except DBMsgNotExistsException:
raise SanicMsgNotFoundException("Message not found")
if token.get('uid') is not db_msg.sender_id:
return await self.make_response_json(status=403)
msg_queries.delete_msg(session, msg_id)
try:
session.commit()
except (DBIntegrityException, DBDataException):
raise SanicDBException
return await self.make_response_json(body={}, status=200)
def update_msg(session: DBSession, msg_id: int,
message: ReqUpdateMsgDTO) -> MessageModel:
db_msg = session.get_msg_by_id(msg_id)
if db_msg is None or db_msg.is_delete is True:
raise DBMsgNotExistsException
db_msg.message = message.message
return db_msg
async def method_post(self, request: Request, body: dict,
session: DBSession, token: dict, *args,
**kwargs) -> BaseHTTPResponse:
req_model = ReqCreateMsgDTO(body)
try:
db_msg = message_queries.create_msg(session, req_model,
token.get('uid'))
except DBUserNotExistsException:
raise SanicUserNotFoundException("User not found")
try:
session.commit()
except (DBIntegrityException, DBDataException):
raise SanicDBException
res_model = ResGetMsgDTO(db_msg)
return await self.make_response_json(body=res_model.dump(), status=201)
async def method_patch(self, request: Request, body: dict,
session: DBSession, uid: int, token: dict, *args,
**kwargs) -> BaseHTTPResponse:
if token.get('uid') != uid:
return await self.make_response_json(status=403)
req_model = ReqUpdateUserDTO(body)
try:
user = user_queries.update_user(session, uid, req_model)
except DBUserNotExistsException:
raise SanicUserNotFoundException("User not found")
try:
session.commit()
except (DBIntegrityException, DBDataException):
raise SanicDBException
res_model = ResGetUserDTO(user)
return await self.make_response_json(body=res_model.dump(), status=200)
def delete(id):
"""Delete post by id"""
session = DBSession()
post = session.query(Posts).filter(Posts.id == id,
Posts.is_deleted == False).one()
# actually just set is_deleted flag to True
post.is_deleted = True
post.deletion_date = datetime.now().replace(microsecond=0).isoformat(' ')
session.commit()
session.close()
def add(post, categories):
"""Add new post and return it's id.
Arguments:
post - Posts object
categories - list of categories id
"""
session = DBSession()
session.add(post)
# add categories to the post
for id in categories:
category = session.query(Categories).get(id)
post.categories.append(category)
id = post.id
session.commit()
session.close()
return id
def restore(post_id):
"""Restore deleted post by id"""
session = DBSession()
post = session.query(Posts).filter(Posts.id == post_id,
Posts.is_deleted == True).one()
post.modification_date = datetime.now().replace(
microsecond=0).isoformat(' ')
post.deletion_date = None
post.is_deleted = False
session.commit()
session.close()
def get_custom_by_category(category_id, quantity, from_id=None, newer=False):
"""Return list of posts by custom rules in the selected category.
Arguments:
category_id -- id of the category
quantity -- quantity of posts
from_id -- start from this post
newer --
if False - returns older posts
if True - return newer posts
"""
session = DBSession()
# start from last post if no from_id passed
if from_id is None:
posts = session.query(Posts).join(Posts.categories). \
filter(Categories.id == category_id, Posts.is_deleted == False). \
order_by(Posts.id.desc()). \
limit(int(quantity)).all()
# start from custom post
else:
# get older posts
if not newer:
posts = session.query(Posts).join(Posts.categories). \
filter(Categories.id == category_id,
Posts.id <= int(from_id),
Posts.is_deleted == False). \
order_by(Posts.id.desc()). \
limit(int(quantity)). \
all()
# get newer posts
else:
posts = session.query(Posts).join(Posts.categories). \
filter(Categories.id == category_id,
Posts.id >= int(from_id),
Posts.is_deleted == False). \
order_by(Posts.id). \
limit(int(quantity)). \
from_self(). \
order_by(Posts.id.desc()). \
all()
session.close()
return posts
def get_first_last():
"""Return id of the first and the last post
Returns dictionary, keys : first_id, last_id.
"""
session = DBSession()
first_id = session.query(Posts).filter(Posts.is_deleted == False). \
order_by(Posts.id).first().id
last_id = session.query(Posts).filter(Posts.is_deleted == False). \
order_by(Posts.id.desc()).first().id
session.close()
return {'first_id': first_id, 'last_id': last_id}
return a_all_list
def op_del(self):
pass
def op_chg(self):
pass
if __name__ == '__main__':
engine = create_engine(
'mysql+pymysql://cdn_user:[email protected]:3306/cdn?charset=utf8'
)
Base.metadata.create_all(engine)
DBSession = sessionmaker(bind=engine)
session = DBSession()
op = operation(session)
data_dic = {
'cname': [['www.baidu.com', 'a.shifen.com', 0, '8.8.8.8'],
['www.sina.com', 'w.sina.com', 0, '114.114.8.8'],
['www.tent.com', 'tt.asdf.com', 0, '110.0.110.8'],
['www.ali.com', 'ali.ali.com', 0, '123.123.123.8']],
'a': [['a.shifen.com', '192.168.2.3', '8.8.8.8', '192.168.2.3', 2],
['w.sina.com', '192.168.6.16', '114.114.8.8', '192.168.2.3', 2],
['tt.asdf.com', '192.127.8.3', '110.0.110.8', '192.168.2.3', 2],
['ali.ali.com', '192.26.2.3', '123.123.123.8', '192.168.2.3', 2]]
}
# op.op_add(data_dic)