def index(name='public'):
user = {'nickname': ''}
# posts = db.get_all_data()
posts = db.get_data_byname(name)
# posts = [ # fake array of posts
# {
# 'name': 'li',
# 'comment': 'Beautiful day in Portland!'
# },
# {
# 'name': 'de',
# 'comment': 'The Avengers movie was so cool!'
# }
# ]
form = CommentForm()
if form.validate_on_submit():
nowTime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') # 现在
# print("-------------------%s-------------", nowTime)
db.insert(form.name.data, form.comment.data, nowTime)
print("%s\t%s 发表了: %s" % (nowTime, form.name.data, form.comment.data))
return redirect('/index')
form2 = FindForm()
if form2.validate_on_submit():
name = form2.name.data
print(name)
return redirect('/index/%s' % name)
return render_template(
'index.html',
title='Home',
# user = user,
posts=posts,
form=form)
def add_section(elective_id, teacher_id, times, room_nbr, year, tri):
time_ids = []
for time_desc in times.split(", "):
time_id = query_one(
DB.ELECTIVE,
"SELECT time_id FROM elective_time WHERE time_short_desc=%s",
[time_desc])[0]
time_ids.append(time_id)
section_nbr = query_one(
DB.ELECTIVE,
"SELECT COUNT(*) FROM elective_section WHERE elective_id=%s",
[elective_id])[0] + 1
insert(
DB.ELECTIVE,
"INSERT INTO elective_section (elective_id, section_nbr, teacher_id, room_nbr, course_year, tri) VALUES (%s, %s, %s, %s, %s, %s)",
[elective_id, section_nbr, teacher_id, room_nbr, year, tri])
section_id = query_one(
DB.ELECTIVE,
"SELECT section_id FROM elective_section WHERE elective_id=%s AND section_nbr=%s",
[elective_id, section_nbr])[0]
data = []
for time_id in time_ids:
data.append([section_id, time_id])
insertmany(
DB.ELECTIVE,
"INSERT INTO elective_section_time_xref (section_id, time_id) VALUES (%s, %s)",
data)
def __init__(self, id=0, file=0, dummy=False):
if dummy == True:
return
if id == 0:
file.seek(0, os.SEEK_END)
self.timestamp = get_timestamp()
self.og_name = '.'.join(file.filename.split('.')[:-1])
self.size = file.tell()
self.type = file.filename.split('.')[-1]
self.id = make_hash(self.timestamp, self.og_name, self.size)
file.seek(0)
self.data = file.read()
db.insert(table='images',
values={
'id': self.id,
'og_name': self.og_name,
'type': self.type,
'size': self.size,
'data': self.data
},
norm=False)
return
else:
res = db.select(
table='images',
fields=['id', 'timestamp', 'og_name', 'type', 'size', 'data'],
params={'id': id},
norm=False)
self.id = id
self.timestamp = res
self.og_name = res.og_name.decode("utf-8")
self.type = res.type.decode("utf-8")
self.size = res.size
self.data = res.data
def add_club(name, max_nbr, type_cde, room_nbr, desc, advisor_id, day):
insert(DB.CLUBS,
"INSERT INTO club (name, max_nbr, club_type_cde, room, description, advisor_id, enrollment_count, course_year, tri_nbr ) "
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)",
(name, max_nbr, type_cde, room_nbr, desc, advisor_id, 0,
Config.CLUBS_CURRENT_COURSE_YEAR, Config.CLUBS_CURRENT_TRIMESTER))
return False
def form_test():
form = TestForm()
if form.validate_on_submit():
flash('Inserido o registro {}:'.format(form.string_t.data))
insert(form.string_t.data)
return redirect('list_db')
return render_template('form_test.html', title='Test', form=form)
def db_summ_metrics(c_time, id, unit): # суммаризация данных
cls_select = db.Performance
cls_insert = db.Performance_H
try:
interval = timedelta(**{unit: 1})
except TypeError:
pass
if unit == 'hours': # для часовых значений
time_start = c_time.replace(minute=00, second=00,
microsecond=0) - interval
time_end = time_start.replace(minute=59, second=59, microsecond=0)
elif unit == 'days': # для суточных значений
time_start = c_time.replace(
hour=00, minute=00, second=00, microsecond=0) - interval
time_end = time_start.replace(hour=23,
minute=59,
second=59,
microsecond=0)
cls_select = db.Performance_H
cls_insert = db.Performance_D
elif unit == 'weeks': # для недельных значений
time_start = c_time.replace(
hour=00, minute=00, second=00, microsecond=0) - interval
time_end = c_time.replace(hour=23, minute=59, second=59,
microsecond=0) - timedelta(days=1)
cls_select = db.Performance_D
cls_insert = db.Performance_W
elif unit == 'months': # для месячных значений
last_day_of_previous_month = c_time - timedelta(days=1)
time_start = last_day_of_previous_month.replace(day=1,
hour=00,
minute=00,
second=00,
microsecond=0)
time_end = last_day_of_previous_month.replace(hour=23,
minute=59,
second=59,
microsecond=0)
cls_select = db.Performance_W
cls_insert = db.Performance_M
result = select((p.resource.id, p.metric.id, p.col1, avg(float(p.col2)))
for p in cls_select if p.metric.metric_id == id
and between(p.time_end, time_start, time_end))[:]
tz_moscow = pytz.timezone('Europe/Moscow')
time_collect = datetime.now(tz_moscow)
for rid, mid, c1, c2 in result:
# вставка суммарных значений
db.insert(cls_insert,
resource=rid,
metric=mid,
time_collect=time_collect,
time_start=time_start,
time_end=time_end,
col1=c1,
col2=str(round(c2, 2)))
def test_aregister(self):
for test in self.success_test['register']:
with patch('app.db.insert', self.mocked_db_add):
#registering user that doesn't exist
if not test["exist"]:
self.assertEqual(len(self.initial_db) + 1, db.insert(test))
#registering user that does exist
else:
self.assertEqual(-1, db.insert(test))
def post(self):
data = request.get_json() or request.form
if data['importType'] == 'batch':
# Batch Import
return {
"reason": "Not Supported Temporarily..."
}
else:
# Single Import
courseId = data['courseId']
studentList = data['students']
studentCount = len(studentList)
for i in range(0, studentCount):
studentId = studentList[i]['id']
res = db.query(
"""
select 1 from User where school_id=%s limit 1;
""",
(studentId,)
)
if res:
isIn = db.query(
"""
select 1 from StudentCourse where student_id=%s and course_id=%s;
""",
(studentId, courseId)
)
if isIn:
studentList[i]['state'] = False
studentList[i]['reason'] = 'Already Involved.'
else:
db.insert(
"""
insert into StudentCourse (student_id, course_id, status)
values
(%s, %s, %s);
""",
(studentId, courseId, 0)
)
db.modify(
"""
update User set course_count=course_count+1 where school_id=%s;
""",
(studentId,)
)
studentList[i]['state'] = True
else:
studentList[i]['state'] = False
studentList[i]['reason'] = 'User not Found.'
resp = {
"state": "success",
"students": studentList
}
return resp
def registration():
if session.get('username', None) is not None:
return redirect('/')
form = forms.RegForm()
if form.validate_on_submit():
token = generate_access_token.random_access_token()
if not check_name(form.username.data):
flash(
"Only Latin characters and digits can be in the username, as well as '_', '-', '.'"
)
return render_template('registration.html',
title='Registration',
form=form,
user=session.get('username', 'Guest'))
if len(form.password.data) < 8:
flash("Password length is not enough")
return render_template('registration.html',
title='Registration',
form=form,
user=session.get('username', 'Guest'))
if form.password.data != form.repassword.data:
flash("Password mismatch")
return render_template('registration.html',
title='Registration',
form=form,
user=session.get('username', 'Guest'))
for i in db.fetch():
if form.username.data.lower() == i[1]:
flash(form.username.data.lower() + " is not available")
return render_template('registration.html',
title='Registration',
form=form,
user=session.get('username', 'Guest'))
elif form.email.data == i[2]:
flash(form.email.data.lower() + " have account")
return render_template('registration.html',
title='Registration',
form=form,
user=session.get('username', 'Guest'))
elif token == i[5]:
token = generate_access_token.random_access_token()
email.send_email_reg(form.email.data, form.username.data,
form.password.data)
db.insert(form.username.data.lower(), form.email.data,
generate_password_hash(form.password.data), token)
session['username'] = form.username.data
session['token'] = token
session['email'] = form.email.data
session['battles'] = 0
session['wins'] = 0
flash(form.username.data.lower() + " is registered")
return redirect('/')
return render_template('registration.html',
title='Registration',
form=form,
user=session.get('username', 'Guest'))
def read_message(nick, chat, content):
user_id = str(find_user(nick)["user_id"])
chat_id = str(get_chat_by_topic(chat)["chat_id"])
message_id = str(get_message_id_by_content(content)["message_id"])
print (message_id)
db.insert("""
UPDATE Members
SET last_read_message_id = %(message_id)s
WHERE user_id = %(user_id)s
AND chat_id = %(chat_id)s
""", chat_id=int(chat_id), user_id=int(user_id), message_id=int(message_id))
db._commit_db('update', 0)
def enroll_user(usr_id, club_id):
print(usr_id, club_id)
insert(DB.CLUBS, 'INSERT INTO club_user_xref (club_id, usr_id) VALUES (%s, %s) '
'ON DUPLICATE KEY UPDATE club_id=club_id', [club_id, usr_id])
enrollment_count = query_one(DB.CLUBS, "SELECT enrollment_count "
"FROM club "
"WHERE club_id = %s", club_id)
query = "UPDATE club SET enrollment_count = %s WHERE club_id = %s"
params = [enrollment_count[0] + 1, club_id]
update(DB.CLUBS, query, params)
def send_message(nick, chat, content):
user_id = str(find_user(nick)["user_id"])
chat_id = str(get_chat_by_topic(chat)["chat_id"])
db.insert("""
INSERT INTO Messages(chat_id, user_id, content)
VALUES (%(chat_id)s, %(user_id)s, %(content)s)
""", chat_id=int(chat_id), user_id=int(user_id), content=content)
db.insert("""
UPDATE Members
SET new_messages = %(message)s
WHERE user_id = %(user_id)s
AND chat_id = %(chat_id)s
""", chat_id=int(chat_id), user_id=int(user_id), message=content)
db._commit_db('insert', 0)
def create_elective(name, desc, course_id, prereq):
if not course_id:
if prereq:
insert(
DB.ELECTIVE,
"INSERT INTO elective (name, `desc`, course_id, prereq) VALUES (%s, %s, %s, %s)",
(name, desc, course_id, prereq))
else:
insert(
DB.ELECTIVE,
"INSERT INTO elective (name, `desc`, course_id) VALUES (%s, %s, %s)",
(name, desc, course_id))
else:
if prereq:
insert(
DB.ELECTIVE,
"INSERT INTO elective (name, `desc`, prereq) VALUES (%s, %s, %s)",
(name, desc, prereq))
else:
insert(DB.ELECTIVE,
"INSERT INTO elective (name, `desc`) VALUES (%s, %s)",
(name, desc))
return query_one(
DB.ELECTIVE,
"SELECT elective_id FROM elective WHERE name=%s AND `desc`=%s",
[name, desc])[0]
def add_reminder(
title: str,
date: str,
category: str,
frequency: int = 0
) -> Union[TemporaryReminder, PermanentReminder, Bookmark]:
"""
Adding reminder to db including getting parameters.
Returns modernized NamedTuple class which include all needful information about reminder.
It can be temporary or permanent reminders and even bookmarks classes.
"""
if category != 'book':
date = parse(date, fuzzy=True)
print(date, type(date))
reminder_add = db.insert(
'reminder', {
'name': title,
'date_time': date,
'category': category,
'for_each': frequency
})
print(reminder_add)
return _recognize_category(id=reminder_add[0],
title=title,
date=date,
category=category,
frequency=frequency)
def register_player(player):
""" Registeres the player in DB if valid and signs in """
if not isinstance(player, Player):
raise Exception('Invalid type supplied')
if Players.exists(player.username):
raise ValidationError('Username ' + player.username + ' exists')
fields = ('username', 'balance')
values = (player.username, Players.__initial_balance)
if hasattr(player, 'password'):
fields = fields + ('password')
values = values + (player.password)
db.insert('players', fields, values)
registered = Players.get_player(username=player.username)
registered.sign_in()
def save(self, recipeModel, ingredients):
if not isinstance(recipeModel, RecipeModel):
raise ValueError("recipeModel should be of type RecipeModel")
query = 'INSERT INTO Recipe (id, id_User, name, description, directives, rating) VALUES (%s, %s, %s, %s, %s, %s)'
recipeId = db.insert(query, self._mapper.to_tuple(recipeModel))
if recipeId:
for ingredient in ingredients:
recipeIngredientModel = None
if (isinstance(ingredient, RecipeIngredientModel)):
recipeIngredientModel = ingredient
recipeIngredientModel.id_Recipe = recipeId
else:
data = {
'id_Recipe': recipeId,
'id_Ingredient': ingredient['id_Ingredient'],
'id_QuantityUnit': ingredient['id_QuantityUnit'],
'totalQuantity': ingredient['totalQuantity']
}
recipeIngredientModel = RecipeIngredientModel(**data)
recipeIngredientDao.save(recipeIngredientModel)
return self.getById(recipeId)
else:
raise Exception("Could not save recipe")
def post(self):
j = request.json
u = get_dummy_user()
u_username = u[1]
if not j:
abort(400, 'Malformed request')
(desc, src) = unpack(j, 'description_text', 'src')
if desc == "" or src == "":
abort(400, 'Malformed request')
try:
size = (150, 150)
im = Image.open(BytesIO(base64.b64decode(src)))
im.thumbnail(size, Image.ANTIALIAS)
buffered = BytesIO()
im.save(buffered, format='PNG')
thumbnail = base64.b64encode(buffered.getvalue()).decode("utf-8")
except:
abort(400, 'Image Data Could Not Be Processed')
post_id = db.insert('POST').with_values(author=u_username,
description=desc,
published=str(time.time()),
likes='',
thumbnail=thumbnail,
src=src).execute()
return {
'post_id': post_id,
}
def post(self):
j = request.json
u = authorize(request)
u_username = u[1]
if not j:
abort(400, 'Malformed request')
(desc,src) = unpack(j,'description_text','src')
if desc == "" or src == "":
abort(400, 'Malformed request')
try:
size = (150,150)
im = Image.open(BytesIO(base64.b64decode(src)))
im.thumbnail(size, Image.ANTIALIAS)
buffered = BytesIO()
im.save(buffered, format='PNG')
thumbnail = base64.b64encode(buffered.getvalue()).decode("utf-8")
except:
abort(400,'Image Data Could Not Be Processed')
post_id=db.insert('POST').with_values(
author=u_username,
description=desc,
published=str(time.time()),
likes='',
thumbnail=thumbnail,
src=src
).execute()
return {
'post_id': post_id
}
def put(self):
u = authorize(request)
j = request.json
try:
id = int(request.args.get('id',None))
except:
abort(400, 'Malformed request')
if not j:
abort(400, 'Malformed request')
if not db.exists('POST').where(id=id):
abort(400, 'Malformed request')
(comment,) = unpack(j,'comment')
if comment == "":
abort(400, 'Malformed request')
comment_id = db.insert('COMMENT').with_values(
comment=comment,
author=u[1],
published=str(time.time())
).execute()
p = db.select('POST').where(id=id).execute()
comment_list = text_list_to_set(p[7],process_f=lambda x: int(x))
comment_list.add(comment_id)
comment_list = set_to_text_list(comment_list)
db.update('POST').set(comments=comment_list).where(id=id).execute()
return {
'message': 'success'
}
def post(self):
j = request.json
u = get_dummy_user()
u_username = u[1]
if not j:
abort(400, 'Malformed request')
(desc, title, subseddit) = unpack(j, 'text', 'title', 'subseddit')
src = j.get('image', None)
if desc == "":
abort(400, 'Malformed request')
thumbnail = ''
if src != None and src != "":
try:
thumbnail = shrink(src)
except:
abort(400, 'Image Data Could Not Be Processed')
post_id = db.insert('POST').with_values(author=u_username,
description=desc,
title=title,
published=str(time.time()),
likes='',
thumbnail=thumbnail,
src=src,
tag=subseddit).execute()
return {'post_id': post_id}
def save(self, ratingModel):
if not isinstance(ratingModel, RatingModel):
raise ValueError("ratingModel should be of type RatingModel")
query = 'INSERT INTO Rating (id, id_Recipe, id_User, value) VALUES (%s, %s, %s, %s)'
newItemId = db.insert(query, self._mapper.to_tuple(ratingModel))
return self.getById(newItemId)
def put(self):
u = authorize(request)
j = request.json
try:
id = int(request.args.get('id',None))
except:
abort(400, 'Malformed request')
if not j:
abort(400, 'Malformed request')
if not db.exists('POST').where(id=id):
abort(400, 'Malformed request')
(comment,) = unpack(j,'comment')
if comment == "":
abort(400, 'Malformed request')
comment_id = db.insert('COMMENT').with_values(
comment=comment,
author=u[1],
published=str(time.time())
).execute()
p = db.select('POST').where(id=id).execute()
comment_list = text_list_to_set(p[7],process_f=lambda x: int(x))
comment_list.add(comment_id)
comment_list = set_to_text_list(comment_list)
db.update('POST').set(comments=comment_list).where(id=id).execute()
return {
'message': 'success'
}
def insert_new():
""" Inserts new coin into coins and returns the id """
ss = random_alphanum(length=app.config['SERVER_SEED_LENGTH'])
ss_hash = sha256(ss.encode()).hexdigest()
cs = random_alphanum(length=app.config['CLIENT_SEED_LENGTH'])
nonce = 0
values = (ss, ss_hash, cs, nonce)
return db.insert('coins', Coins.__fields, values, 'id')
def __init__(self,
board=0,
author=0,
subject=0,
comment=0,
image=0,
thread=0,
id=0,
timestamp=0,
dummy=False):
if dummy == True:
return
if type(board) == str:
board = Board(board)
if id == 0:
self.author = author
self.board = board
self.subject = subject
self.comment = comment
self.image = image
self.thread = thread
self.id = random(8)
self.timestamp = get_timestamp()
if db.insert(table='posts',
values={
'id': self.id,
'board': self.board.name,
'author': self.author.id,
'timestamp': self.timestamp,
'subject': self.subject,
'text': self.comment,
'image': self.image,
'thread': self.thread
}):
return
else:
res = db.select(table='posts',
fields=[
'author', 'timestamp', 'board', 'thread',
'subject', 'text', 'image'
],
params={
'id': id,
'board': board.name
})
if not res:
return
self.id = int(id)
self.author = User(id=res.author)
self.board = board
self.timestamp = res.timestamp
self.thread = int(res.thread)
self.subject = res.subject
self.comment = res.text
if res.image != '0':
self.image = Image(id=res.image.decode("utf-8"))
def save(self, cartItemModel):
if not isinstance(cartItemModel, CartItemModel):
raise ValueError("cartItemModel should be of type CartItemModel")
query = 'INSERT INTO CartItem (id, id_Ingredient, id_Cart, multiplier, subCost) VALUES (%s, %s, %s, %s, %s)'
newItemId = db.insert(query, self._mapper.to_tuple(cartItemModel))
if newItemId:
return self.getById(newItemId)
else:
raise Exception("Could not save ingredient to cart")
def save(self, likeRecipeModel):
if not isinstance(likeRecipeModel, LikeRecipeModel):
raise ValueError("likeRecipeModel should be of type LikeRecipeModel")
query = 'INSERT INTO LikeRecipe (id, id_Recipe, id_User) VALUES (%s, %s, %s)'
newLikeRecipe = db.insert(query, self._mapper.to_tuple(likeRecipeModel))
if newLikeRecipe:
return self.getById(newLikeRecipe)
else:
raise Exception("Could not like this recipe")
def save(self, cartModel):
if not isinstance(cartModel, CartModel):
raise ValueError("cartModel should be of type cartModel")
query = 'INSERT INTO Cart (id, id_User, totalCost) VALUES (%s, %s, %s)'
cartId = db.insert(query, self._mapper.to_tuple(cartModel))
if cartId:
return self.getById(cartId)
else:
raise Exception("Could not save cart")
def save(self, commentModel):
if not isinstance(commentModel, CommentModel):
raise ValueError("commentModel should be of type CommentModel")
query = 'INSERT INTO Comment (id, id_Recipe, id_User, text) VALUES (%s, %s, %s, %s)'
newCommandId = db.insert(query, self._mapper.to_tuple(commentModel))
if newCommandId:
return self.getById(newCommandId)
else:
raise Exception("Could not save commend")
def save(self, userModel, autocommit=True):
if not isinstance(userModel, UserModel):
raise ValueError("userModel should be of type UserModel")
query = 'INSERT INTO User (id, username) VALUES (%s, %s)'
userId = db.insert(query, self._mapper.to_tuple(userModel), autocommit)
if userId:
return self.getById(userId)
else:
raise Exception("Could not save user")
def save(self, commandsModel):
if not isinstance(commandsModel, CommandModel):
raise ValueError("commandsModel should be of type CommandModel")
query = 'INSERT INTO Command (id, id_Cart, creationDate, arrivalDate) VALUES (%s, %s, %s, %s)'
newCommand = db.insert(query, self._mapper.to_tuple(commandsModel))
if newCommand:
return self.getById(newCommand)
else:
raise Exception("Could not add newCommand")
def __init__(self, id=0, ip=0, dummy=False):
if dummy == True:
return
if ip != 0 and id == 0:
self.ip = ip
self.first_login = get_timestamp()
self.id = make_hash(ip, self.first_login)
db.insert(table='users',
values={
'id': self.id,
'first_login': self.first_login
})
self.fng = 1
return
res = db.select(table='users',
fields=['banned', 'first_login', 'admin'],
params={'id': id})
self.id = id
self.ip = ip
self.first_login = res.first_login
self.banned = int(res.banned)
self.admin = int(res.admin)
def save(self, recipeIngredientModel):
if not isinstance(recipeIngredientModel, RecipeIngredientModel):
raise ValueError(
"recipeIngredientModel should be of type RecipeIngredientModel"
)
pass
query = 'INSERT INTO RecipeIngredient (id, id_Recipe, id_Ingredient, id_QuantityUnit, totalQuantity) VALUES (%s, %s, %s, %s, %s)'
newRecipeId = db.insert(query,
self._mapper.to_tuple(recipeIngredientModel))
if newRecipeId:
return self.getById(newRecipeId)
else:
raise Exception("Could not save ingredient to cart")
def post(self):
if not request.json:
abort(400,'Malformed Request')
(un,ps,em,n) = unpack(request.json,'username','password','email','name')
if db.exists('USER').where(username=un):
abort(409, 'Username Taken')
if ps == '':
abort(400, 'Malformed Request')
t = gen_token()
db_r = db.insert('USER').with_values(
curr_token=t,
username=un,
password=ps,
email=em,
name=n
)
db_r.execute()
return {
'token': t
}