def categoryJSON(category_name):
""" JSON data for specific category """
# Create DB session
db_session = Session()
# Query all categories
category = db_session.query(Category).filter_by(name=category_name).one()
# Query items for corresponding category
items = (
db_session
.query(Item)
.filter_by(category_id=category.id)
.order_by(Item.title)
.all()
)
# Serialize current category and add corresponding serialized items
serialized_category = category.serialize
serialized_category["Items"] = []
for item in items:
serialized_category["Items"].append(item.serialize)
# Close DB session
db_session.close()
return jsonify(Category=serialized_category)
def _update_tasks(self, tasks):
session = Session()
while tasks:
nm_list = []
bt_list = []
for task in tasks[:100]:
if task.task_type in ("bt", "magnet"):
bt_list.append(task.id)
else:
nm_list.append(task.id)
for res in self.xunlei.get_task_process(nm_list, bt_list):
task = self.get_task(res['task_id'])
if not task: continue
task.status = res['status']
task.process = res['process']
if task.status == "failed":
task.invalid = True
if res['cid'] and res['lixian_url']:
task.cid = res['cid']
task.lixian_url = res['lixian_url']
if task.status in ("downloading", "finished"):
if not self._update_file_list(task):
task.status = "downloading"
session.add(task)
tasks = tasks[100:]
session.commit()
session.close()
def event(event_id):
content = {'events': 'test'}
success = {'status': 'event created'}
updateSuccess = {'status':'account updated'}
noEvent = {'error': 'User not found.'}
updateError = {'error': 'User not found/input validation failed.'}
error = {'error': "Error in JSON/SQL syntax"}
if request.method == 'POST':
data = request.json
if event.createEvent(data):
return success, status.HTTP_200_OK
else:
return error, status.HTTP_500_INTERNAL_SERVER_ERROR
if request.method == 'GET':
s = Session()
e = s.query(Event).filter_by(id=event_id).first()
if e:
return Event.asdict(e), status.HTTP_200_OK
else:
return noEvent, status.HTTP_404_NOT_FOUND
if request.method == 'POST':
data = request.json
if data:
s = Session()
u = s.query(Event).filter_by(id=event_id).update(data)
if u:
s.commit()
s.close()
return updateSuccess, status.HTTP_200_OK
else:
return updateError, status.HTTP_400_BAD_REQUEST
def test_16_skills(self):
session = Session()
race = session.query(Event).filter_by(name='Race for the Cure').first()
dace = EventSkills("Teaching/Tutoring", race.id)
lace = session.query(EventSkills).filter_by(event_id=race.id).first()
self.assertTrue(dace.skill == lace.skill)
session.close()
def create_token(user):
payload = {
# subject
'sub': user.id,
#issued at
'iat': datetime.utcnow(),
#expiry
'exp': datetime.utcnow() + timedelta(days=1)
}
s = Session()
token = jwt.encode(payload, app.secret_key, algorithm='HS256')
try:
if (user.permissions == 'volunteer'):
us = s.query(Volunteer).filter_by(id=user.id).first()
d = volunteer.Volunteer.asdict(us)
d['skills'] = VolunteerSkills.get_skills(us.id)
d['neighborhoods'] = VolunteerNeighborhoods.get_neighborhoods(us.id)
d['interests'] = VolunteerInterests.get_interests(us.id)
if (user.permissions == 'admin'):
us = s.query(Admin).filter_by(id=user.id).first()
d = admin.Admin.asdict(us)
if (user.permissions == 'organization'):
us = s.query(Organization).filter_by(id=user.id).first()
d =organization.Organization.asdict(us)
except:
return None
finally:
s.close()
m = {'token': str(token), 'user': d}
return m
def post_links():
session = Session()
data = json.loads(request.data)
for link in data['links']:
post = Post.get_or_create(session, link['reddit_id'])
subreddit = Subreddit.get_or_create(session, link['sub_name'])
user = User.get_or_create(session, link['authorname'])
post.subreddit_id = subreddit.id
post.author_id = user.id
post.domain = link['domain']
post.title = link['title']
post.url = link['url']
post.score = link['score']
post.downs = link['downs']
post.ups = link['ups']
post.is_self = link['is_self']
post.over_18 = link['over_18']
post.thumbnail = link['thumbnail']
post.created = float(link['created'])
post.scraped_time = Util.now()
session.add(post)
session.commit()
session.close()
return jsonify({'received':True})
def category(category_name):
""" Shows all items for a specific category """
# Create DB session
db_session = Session()
# Query all categories and find selected category
categories = db_session.query(Category).all()
for category in categories:
if category.name == category_name:
current_category = category
break
# Query items for corresponding category
items = (
db_session
.query(Item)
.filter_by(category_id=current_category.id)
.order_by(Item.title)
.all()
)
# Close DB session
db_session.close()
return render_template("category.html",
category_name=category_name,
categories=categories,
items=items)
def update_account(customer_id, body):
session = Session()
account = session.query(AccountDAO).filter(
AccountDAO.id == customer_id).first()
if account:
entities_updated = []
if 'customer_name' in body:
account.customer_name = body['customer_name']
entities_updated.append('customer_name, ')
if 'customer_address' in body:
account.customer_address = body['customer_address']
entities_updated.append('customer_address, ')
if 'customer_password' in body:
account.customer_password = body['customer_password']
entities_updated.append('customer_password, ')
if 'customer_email' in body:
account.customer_email = body['customer_email']
entities_updated.append('customer_email, ')
session.commit()
session.close()
return jsonify(
{'message': f'{" ".join(entities_updated)} were updated'}), 200
else:
session.close()
return jsonify(
{'message': f'There is no account with id {customer_id}'}), 404
def put_batch():
session = Session()
data = json.loads(request.data)
worker = data['id']
users = data['users']
users = filter(lambda u: u is not None, users)
usernames = [user['name'] for user in users]
for lock in session.query(UserLock).join(User) \
.filter(User.name.in_(usernames), UserLock.status == UserLock.STATUS_WIP, UserLock.locker == worker):
for u in users:
if u['name'] == lock.user.name:
if 'error' in u:
eId = str(uuid4())
lock.user.reddit_id = eId
else:
lock.user.update_from_json(u)
session.add(lock.user)
session.commit()
session.query(UserLock).join(User) \
.filter(User.reddit_id != None, User.name.in_(usernames), UserLock.status == UserLock.STATUS_WIP, UserLock.locker == worker) \
.update({'status': UserLock.STATUS_DONE, 'locker': None}, synchronize_session='fetch')
session.commit()
session.close()
return jsonify({'received':'true'})
def synchronize_get_data():
"""
Повертає json з працівниками відсортованими по відділах в яких є невідповідності в базах 1с, АД та Mysql.
Приклад об'єкту що повертаємо
{
"eCommerce": [
{
"department": "eCommerce",
"description": "1",
"fixed": "0",
"id": 3054,
"name": "Наталія",
"position": "Начальник відділу e-commerce",
"sid": "S-1-5-21-3915682675-1099836910-3992233257-3989",
"sname": "Сєрякова"
}
]
}
"""
result = {}
session = Session()
all_records = session.query(Department1c).all()
converter = Department1cSchema(many=True, only=['name'])
list_dep = converter.dump(all_records).data
for res in list_dep:
all_rcolission = session.query(Collision).filter(Collision.department == res['name']).all()
converter = CollisionSchema(many=True)
arg = converter.dump(all_rcolission).data
result[res['name']] = arg
session.close()
return jsonify(result)
def filter_statistic(_id):
"""
Повертає в яких відділах та містах було пройдене опитування.
:param _id: ід опитування
Приклад відповіді:
{
"city": [
"Луцьк",
"Львів"
],
"dep": [
"eCommerce",
"Інформаційні технології"
]
}
"""
session = Session()
response = {}
dep = []
city = []
for arg in session.query(distinct(
Statistics.department)).filter_by(fk_poll=_id).all():
dep.append(arg[0])
response.setdefault('dep', dep)
for arg in session.query(distinct(
Statistics.city)).filter_by(fk_poll=_id).all():
city.append(arg[0])
response.setdefault('city', city)
session.close()
return jsonify(response)
def create_item(request, redirect_url, user_id):
"""Creates a new item and add to database"""
# Check if form is complete
check_item_form_data(request, redirect_url)
# Create DB session
db_session = Session()
# Add new item to database
try:
new_item = Item(title=request.form.get("title"),
description=request.form.get("description"),
category_id=request.form.get("category"),
owner_id=user_id)
db_session.add(new_item)
db_session.commit()
except exc.SQLAlchemyError:
flash("There was an error. Please try again.", "danger")
return redirect(redirect_url)
# Close DB session
db_session.close()
flash("Item added!", "success")
def branch_list():
"""
Заповнення філіалу в БД
"""
users_list = []
if load_users_from_ad():
process_ad_response(ad.response, users_list,
(('userAccountControl', [], False),
('company', [], False)))
session = Session()
for arg in users_list:
if not session.query(Branch).filter(
Branch.name == arg['company']).all():
fk_city = session.query(
City.id).filter(City.name == arg['cityUa']).first()
branch = Branch(name=arg['company'],
name_en=arg['companyEn'],
zip_code=arg['postalCode'],
address_ua=arg['streetAddressUa'],
address_en=arg['street'],
address_ru=arg['streetAddress'],
fk_city=fk_city[0])
session.add(branch)
session.commit()
session.close()
def vacation():
"""
Заповнення відпусти за поточний рік всім працівникам
"""
session = Session()
date2 = datetime.strftime(datetime.now(), "%Y-%m-%d")
date1 = datetime.date(datetime.now())
result = session.query(
Worker.id, Worker.started_to_work,
Worker.finished_to_work).filter(Worker.status == 'active').all()
for arg in result:
if date1.year != arg[1].year and not session.query(Vacation).filter(
Vacation.year == date1.year).filter(
Vacation.fk_worker == arg[0]).first():
# Додати період +24 за поточний рік
vacation_new = Vacation(count=24,
used=0,
balance=24,
year=date1.year,
updated=date2[0:10],
fk_worker=arg[0])
session.add(vacation_new)
# нарахування відпустки якщо людина була прийнята на роботу цього року
elif not session.query(Vacation).filter(
Vacation.year == date1.year).filter(
Vacation.fk_worker == arg[0]).first():
vacation_user(arg[0])
session.commit()
session.close()
def spider_opened(self, spider):
db_session = Session()
db_spider = db_session.query(Spider).filter(
Spider.name == spider.name).one()
if db_spider and db_spider.use_proxies or db_spider.use_tor:
self.tor_renew_ip.add(spider.name)
db_session.close()
def vacation_user(_id):
"""
Нарахування відпустки працівнику за діючий рік якщо він прийнятий на роботу цього ж року
:param _id: ід працівника
"""
session = Session()
datenow = datetime.strftime(datetime.now(), "%Y-%m-%d")
date2 = datetime.strptime(str(datenow), "%Y-%m-%d").date()
result = session.query(
Worker.id, Worker.started_to_work, Worker.finished_to_work).filter(
Worker.status == 'active').filter(Worker.id == _id).first()
date = str(result[1].year) + '-12-31'
date1 = datetime.strptime(str(date), "%Y-%m-%d").date()
if result[1].year == date1.year:
if result[1].day < 10:
s1 = 2
elif result[1].day < 21:
s1 = 1
else:
s1 = 0
# кількість заробленої відпустки за цей рік
count = s1 + 2 * (date1.month - result[1].month)
vacation_new = Vacation(count=count,
used=0,
balance=count,
year=date2.year,
updated=date2,
fk_worker=result[0])
session.add(vacation_new)
session.commit()
session.close()
return count
def transactions2csv(dest):
s = Session()
statement = banks.get_transaction_log_statement()
result = s.execute(statement).fetchall()
s.close()
total = len(result)
header = True
with open(dest, 'w') as csvfile:
spamwriter = csv.writer(csvfile,
delimiter=';',
quotechar='"',
quoting=csv.QUOTE_NONNUMERIC)
for next in result:
if header:
spamwriter.writerow(next.keys())
header = False
row = list(next.values())
amount = row[0]
while amount > unit:
row[0] = unit
amount -= unit
spamwriter.writerow(row)
row[0] = amount
spamwriter.writerow(row)
def populate_db():
df = pd.read_csv('data/movies_metadata.csv')
df_sub = df[[
'adult', 'budget', 'original_language', 'overview', 'release_date',
'revenue', 'runtime', 'title'
]].copy()
# 3 - create a new session
session = Session()
# How to connect to existing database.
for index, row in df_sub.iterrows():
adult = row['adult']
budget = row['budget']
orlang = row['original_language']
overview = row['overview']
reldate = row['release_date']
revenue = row['revenue']
runtime = row['runtime']
title = row['title']
product_quantity = random.randint(1000, 5000)
unit_price = random.randint(100, 500) / 100
population_objects = ProductDAO(adult=adult,
budget=budget,
original_language=orlang,
overview=overview,
release_date=reldate,
revenue=revenue,
runtime=runtime,
title=title,
product_quantity=product_quantity,
unit_price=unit_price)
session.add(population_objects)
session.commit()
session.close()
print("Db Successfully populated")
def get_barh(lastUts):
session = Session()
query = session.query(Etape.ecart, Ligne.nom_ligne) \
.select_from(Etape).join(Trajet).join(Ligne) \
.filter(Etape.record_timestamp == lastUts)
df = pd.read_sql_query(query.statement, query.session.bind)
session.close()
dft = df.pivot_table(index="nom_ligne", values="ecart",
aggfunc=lambda x: np.mean(np.abs(x))) \
.sort_values(by="ecart")
data = go.Bar(y=dft.index,
x=dft['ecart'],
orientation='h',
marker={'color': 'rgba(210, 105, 30, 0.5)'})
layout = go.Layout(
title="Ecarts absolus moyens sur les lignes du réseau Irigo",
xaxis={
'title': "écart moyen absolu (secondes)",
},
yaxis={'title': "ligne"},
margin={'l': 300})
g = dcc.Graph(id='barh', figure={'data': [data], 'layout': layout})
return [g]
def test_14_neighborhoods(self):
session = Session()
race = session.query(Event).filter_by(name='Race for the Cure').first()
dace = EventNeighborhoods("Back Bay", race.id)
lace = session.query(EventNeighborhoods).filter_by(event_id=race.id).first()
self.assertTrue(dace.neighborhood == lace.neighborhood)
session.close()
class Admin(tornado.web.RequestHandler):
def __init__(self, application, request, **kwargs):
super(Admin, self).__init__(application, request, **kwargs)
_log.debug('admin init')
self.session = Session()
self.request_handler_actor = self.application.settings[
'request_handler_actor']
def __del__(self):
self.session.close()
# list existing bots
def get(self):
result = self.session.query(TurtlebotAgent).all()
jsonResult = json.dumps(result, cls=TurtlesEncoder)
self.write(jsonResult)
# add a new bot with name and location
def post(self):
if settings.SIMULATION == True:
initial_location = [settings.NEST_X, settings.NEST_Y]
robots_in_database = self.session.query(TurtlebotAgent.name).all()
if len(robots_in_database) == 0:
maximal_index = -1
else:
maximal_index = max(
[int(r[0].split('_')[1]) for r in robots_in_database])
turtlebot_name = 'robot_' + repr(maximal_index + 1)
if maximal_index == settings.MAXIMAL_NUMBER_OF_BOTS - 1:
self.set_status(204, "maximal number of bots already reached")
self.finish()
else:
try:
initial_location = json.loads(
self.get_body_argument(name='location'))
except:
self.set_status(400)
self.finish()
else:
turtlebot_name = self.get_body_argument(name='name')
robots_with_same_name = self.session.query(
TurtlebotAgent).filter_by(name=turtlebot_name).all()
if len(robots_with_same_name) > 0:
self.set_status(400, "the turtlebot name must be unique")
elif len(initial_location) != 2:
self.set_status(
400, "the initial location must be a 2d vector")
self.finish()
try:
new_agent = TurtlebotAgent(name=turtlebot_name,
location=Point(initial_location))
self.session.add(new_agent)
self.session.commit()
except Exception as e:
self.session.rollback()
self.set_status(400, 'error writing in the database')
def saveComments(comments):
session = Session()
commentIds = {comment['id'] for comment in comments}
commentsInDB = session.query(Comment.id).filter(
Comment.id.in_(commentIds)).all()
commentIdsInDB = {comment.id for comment in commentsInDB}
if commentIdsInDB is None:
commentIdsInDB = []
commentIdsInDB = set(commentIdsInDB)
# 添加新的评论
needAddCommentIds = commentIds - commentIdsInDB
needAddComments = list(
filter(lambda c: c['id'] in needAddCommentIds, comments))
session.add_all([Comment(**comment) for comment in needAddComments])
session.commit()
#更新旧的评论
needUpdateComments = list(
filter(
lambda c: c['id'] in commentIdsInDB and
(c['isDelete'] is True or c['isUpDelete'] is True), comments))
for comment in needUpdateComments:
session.query(Comment).filter(Comment.id == comment.get('id')).update({
'isDelete':
comment.get('isDelete'),
'isUpDelete':
comment.get('isUpDelete')
})
session.commit()
session.close()
def getDutyShiftChain(duty_date, duty_period):
session = Session()
try:
origin_duty = session.query(OriginDuty).options(
joinedload('member')).filter(
and_(OriginDuty.duty_date == duty_date,
OriginDuty.duty_period == duty_period.value)).first()
#print(origin_duty)
if origin_duty is not None:
shift_chain = DutyShiftChain(origin_duty)
if origin_duty.shift_duty_id is None:
return shift_chain
else:
shift_id = origin_duty.shift_duty_id
while shift_id is not None:
shift_duty = session.query(ShiftDuty).options(
joinedload('member')).filter(
ShiftDuty.shift_duty_id == shift_id).first()
#print(shift_duty)
shift_chain.addDutyShiftRecord(shift_duty)
shift_id = shift_duty.next_shift_duty_id
return shift_chain
except Exception as e:
print(e)
return
finally:
session.close()
def shiftDuty(duty_date, duty_period, member):
session = Session()
try:
shift_duty = ShiftDuty(duty_date, duty_period, member.member_id)
duty = session.query(OriginDuty).filter(
and_(OriginDuty.duty_date == duty_date,
OriginDuty.duty_period == duty_period.value)).first()
if duty is not None:
if duty.shift_duty_id is None:
shift_duty.previous_shift_duty_id = duty.duty_id
duty.shift_duty_id = shift_duty.shift_duty_id
session.add(shift_duty)
session.commit()
else:
shift_id = duty.shift_duty_id
while shift_id is not None:
pre_shift_duty = session.query(ShiftDuty).filter(
ShiftDuty.shift_duty_id == shift_id).first()
shift_id = pre_shift_duty.next_shift_duty_id
pre_shift_duty.next_shift_duty_id = shift_duty.shift_duty_id
shift_duty.previous_shift_duty_id = pre_shift_duty.shift_duty_id
session.add(shift_duty)
session.commit()
except Exception as e:
print(e)
session.rollback()
finally:
session.close()
def deleteShiftDutyRecord(shift_duty_date,
shift_duty_period=DutyPeriod.NightDuty):
session = Session()
duty_shift_chain = DBOperation.getDutyShiftChain(
shift_duty_date, shift_duty_period)
if duty_shift_chain is None:
print("无日期为{}的值班记录".format(shift_duty_date))
return
shift_chain_list = duty_shift_chain.shift_chain
shift_count = len(shift_chain_list) - 1
try:
if shift_count == 0:
print("can not delete origin duty.")
return
elif shift_count == 1:
session.query(OriginDuty).filter(
OriginDuty == shift_chain_list[-2]).update(
{OriginDuty.shift_duty_id: None})
else:
session.query(ShiftDuty).filter(
ShiftDuty == shift_chain_list[-2]).update(
{ShiftDuty.next_shift_duty_id: None})
session.delete(shift_chain_list[-1])
session.commit()
except Exception as e:
print(e)
session.rollback()
finally:
session.close()
def create(self, model):
session = Session()
session.add(model)
session.commit()
session.close()
def get_account(customer_email):
session = Session()
account = session.query(AccountDAO).filter(
AccountDAO.customer_email == customer_email).first()
# if customer:
# return jsonify({'test2': f'customer: {customer_email}'}), 200
# else:
# return jsonify({'Error': 'No customer found'}), 404
# session = Session()
# account_email = body['customer_email']
# account = session.query(AccountDAO).filter(AccountDAO.customer_email == account_email).first()
if account:
text_out = {
"customer_id": account.id,
"customer_name": account.customer_name,
"customer_address": account.customer_address,
"customer_email": account.customer_email,
"creation_time": account.creation_time
}
session.close()
return jsonify(text_out), 200
else:
session.close()
return jsonify({
'message':
f'There is no account with e-mail {customer_email}'
}), 404
def create_user(login_session, redirect_url):
"""Creates a new user and add to database"""
# Create db_session
db_session = Session()
# Check to see if user exists in database and add it otherwise
user_id = get_user_id(login_session['profile']['username'])
if not user_id:
# Add user to database
try:
new_user = User(username=login_session['profile']['username'])
db_session.add(new_user)
db_session.commit()
flash("User created", "success")
except exc.SQLAlchemyError:
flash("Failed to create user", "danger")
return redirect(redirect_url)
# Query created user
user = db_session.query(User).filter_by(
username=login_session['profile']['username']).one()
# Close DB session
db_session.close()
return user.id
def init_intermediary_table():
s = Session()
s.execute(drop_table_intermediary)
s.execute(create_table_intermediary)
s.execute(populate_table_intermediary)
s.commit()
s.close()
def init_cashflow_table():
s = Session()
s.execute(drop_table_cashflow)
s.execute(create_table_cashflow)
s.execute(populate_table_cashflow)
s.commit()
s.close()
def update_user_db_ad():
"""
Доповнення БД даними з АД
"""
session = Session()
records = session.query(Worker.sid,
Worker.fk_person).filter(Worker.sid != '').all()
for arg in records:
user = []
res = ad.search(search_base=BASE_DN,
search_filter="(objectSid={sid})".format(sid=arg[0]),
attributes=[
'mail', 'sn', 'givenName', 'pager',
'thumbnailPhoto', 'mobile', 'givenName-En',
'givenNameUa', 'cityUa', 'ipPhone', 'l',
'givenName-En', 'snEn', 'givenNameUa', 'snUa',
'objectSid'
])
if res:
for entry in ad.response:
if 'dn' in entry:
user.append(dict(entry['attributes']))
if user:
if user[0]['thumbnailPhoto']:
byte_string = user[0]['thumbnailPhoto']
encoded_data = base64.b64encode(byte_string)
photo = Document(name='фотографія',
file_type='',
file=encoded_data,
type_='',
fk_person=arg[1])
session.add(photo)
for resp in user[0]:
if not user[0][resp]:
user[0][resp] = ''
session.query(Person).filter_by(id=arg[1]).update({
"email":
user[0]['mail'],
"skype":
user[0]['pager']
})
session.query(Worker).filter_by(fk_person=arg[1]).update({
"name_en":
user[0]['givenName-En'],
"name_ru":
user[0]['givenName'],
"surname_en":
user[0]['snEn'],
"surname_ru":
user[0]['sn'],
"skype":
user[0]['pager'],
"ip_phone":
user[0]['ipPhone'],
"email":
user[0]['mail']
})
session.commit()
session.close()
def idOfPerson_family(_id):
"""
Повертає дані про сімю фізичної особи за id фізичної особи. В групу Family
:param _id: id фізичної особи
Приклад відповіді:
[
{
"date": "Sat, 01 Jan 2000 00:00:00 GMT",
"id": 11,
"name": "Іван",
"position": "дизайнер",
"sname": "Іванов",
"type": "чоловік",
"work_place": "Google"
}
]
"""
session = Session()
result = session.query(Family).filter(Family.fk_person == _id).all()
converter = FamilySchema(many=True,
only=[
'id', 'name', 'surname', 'birthday', 'type_',
'workplace', 'position'
])
dumps_data = converter.dump(result).data
session.close()
return jsonify(dumps_data)
def main(notify):
session = Session()
gen = session.query(User) \
.join(UserMeta) \
.filter(User.reddit_id != None, \
UserMeta.has_public_likes == UserMeta.YES, \
UserMeta.is_active != UserMeta.NO)
for user in gen:
try:
praw_user = r.get_redditor(user.name)
user.update_from_praw(praw_user)
for praw_post in praw_user.get_liked(limit=100):
update(session, user, praw_post, 1)
for praw_post in praw_user.get_disliked(limit=100):
update(session, user, praw_post, -1)
user.meta.has_public_likes = UserMeta.YES
except praw.requests.exceptions.HTTPError as e:
print str(e)
if '403' in str(e):
user.meta.has_public_likes = UserMeta.NO
elif '404' in str(e):
user.meta.is_active = UserMeta.NO
session.add(user.meta)
session.add(user)
session.commit()
session.close()
def update_item(item_id, request, redirect_url):
"""Update item in database"""
# Check if form is complete
check_item_form_data(request, redirect_url)
# Create DB session
db_session = Session()
# Query item in database by id
item = db_session.query(Item).filter_by(id=item_id).one()
# Update corresponding item in database
try:
item.title = request.form.get("title")
item.description = request.form.get("description")
item.category_id = request.form.get("category")
item.last_updated = datetime.utcnow()
db_session.add(item)
db_session.commit()
except exc.SQLAlchemyError:
flash("There was an error. Please try again.", "danger")
return redirect(redirect_url, item_id=item_id)
# Close DB session
db_session.close()
def add(self, message, latitude, longitude, time_limit=60):
post = Post(message, latitude, longitude, time_limit)
session = Session()
session.add(post)
session.commit()
session.close()
return post
def test_12_interests(self):
session = Session()
race = session.query(Event).filter_by(name='Race for the Cure').first()
dace = EventInterests("Youth", race.id)
lace = session.query(EventInterests).filter_by(event_id=race.id).first()
self.assertTrue(dace.interest == lace.interest)
session.close()
def init_balance_table():
s = Session()
s.execute(drop_table_balance)
s.execute(create_table_balance)
s.execute(populate_table_balance)
s.commit()
s.close()
def test_14_availability(self):
session = Session()
doey = session.query(Volunteer).filter_by(name='Joey Wood').first()
moey = VolunteerAvailability("Monday", doey.id)
joey = session.query(VolunteerAvailability).filter_by(volunteer_id=doey.id).first()
#self.assertTrue(moey.day == joey.day)
session.close()
def save_comments(self, comments):
session = Session()
comment_ids = {comment['id'] for comment in comments}
comments_in_db = session.query(Comment.id).filter(
Comment.id.in_(comment_ids)).all()
comment_ids_in_db = {comment.id for comment in comments_in_db}
if comment_ids_in_db is None:
comment_ids_in_db = []
comment_ids_in_db = set(comment_ids_in_db)
# 添加新的评论
need_add_comment_ids = comment_ids - comment_ids_in_db
need_add_comments = list(
filter(lambda c: c['id'] in need_add_comment_ids, comments))
session.add_all([Comment(**comment) for comment in need_add_comments])
# 更新旧的评论
need_update_comments = list(
filter(
lambda c: c['id'] in comment_ids_in_db and
(c['isDelete'] is True or c['isUpDelete'] is True), comments))
for comment in need_update_comments:
db_comment = session.query(Comment).filter(
Comment.id == comment.get('id'))
db_comment.isDelete = comment.get('isDelete')
db_comment.isUpDelete = comment.get('isUpDelete')
session.commit()
session.commit()
session.close()
def test_10_neighborhoods(self):
session = Session()
doey = session.query(Volunteer).filter_by(name='Joey Wood').first()
moey = VolunteerNeighborhoods("backbay", doey.id)
joey = session.query(VolunteerNeighborhoods).filter_by(volunteer_id=doey.id).first()
#self.assertTrue(moey.neighborhood == joey.neighborhood)
session.close()
def test_12_skills(self):
session = Session()
doey = session.query(Volunteer).filter_by(name='Joey Wood').first()
moey = VolunteerSkills("teaching", doey.id)
joey = session.query(VolunteerSkills).filter_by(volunteer_id=doey.id).first()
#self.assertTrue(moey.skill == joey.skill)
session.close()
def test_08_interests(self):
session = Session()
doey = session.query(Volunteer).filter_by(name='Joey Wood').first()
moey = VolunteerInterests("youth", doey.id)
joey = session.query(VolunteerInterests).filter_by(volunteer_id=doey.id).first()
#self.assertTrue(moey.interest == joey.interest)
session.close()
def username_exists(username): # returns false if username doesnt exist
session = Session()
if session.query(User).filter_by(username=username).first() == None:
session.close()
return False
session.close()
return True
def catalogJSON():
""" Full catalog API Endpoint """
# Create DB session
db_session = Session()
# Query all categories
categories = db_session.query(Category).all()
serialized_categories = []
for category in categories:
# Query all items for each category
items = (
db_session
.query(Item)
.filter_by(category_id=category.id)
.order_by(Item.title)
.all()
)
# Serialize current category and add corresponding serialized items
serialized_category = category.serialize
serialized_category["Items"] = []
for item in items:
serialized_category["Items"].append(item.serialize)
serialized_categories.append(serialized_category)
# Close DB session
db_session.close()
return jsonify(Categories=[
category for category in serialized_categories])
def get_skills(id): s = Session() result = [] q = s.query(VolunteerSkills).filter_by(volunteer_id=id) for sk in q: result.append(sk.skill) s.close() return result
def test_18_updating_email(self):
session = Session()
joey = session.query(User).filter_by(name='Joey Wood').first()
q = session.query(User).filter_by(id=joey.id)
q = q.update({"email":"*****@*****.**"})
joey = session.query(User).filter_by(id=joey.id).first()
self.assertTrue(joey.email == '*****@*****.**')
session.close()
def test_17_updating_name(self):
session = Session()
joey = session.query(User).filter_by(name='Joey Wood').first()
q = session.query(User).filter_by(id=joey.id)
q = q.update({"name":"Wood Joey"})
joey = session.query(User).filter_by(id=joey.id).first()
self.assertTrue(joey.name == 'Wood Joey')
session.close()
def get_interests(id):
s = Session()
result = []
q = s.query(VolunteerInterests).filter_by(volunteer_id=id)
for n in q:
result.append(n.interest)
s.close()
return result
def test_05_updating_email(self):
session = Session()
test = session.query(User).filter_by(name='Test Org').first()
q = session.query(User).filter_by(id=test.id)
q = q.update({"email":"*****@*****.**"})
test = session.query(User).filter_by(id=test.id).first()
self.assertTrue(test.email == '*****@*****.**')
session.close()
def test_06_updating_zip(self):
session = Session()
race = session.query(Event).filter_by(name='Race for the Cure').first()
q = session.query(Event).filter_by(id=race.id)
q = q.update({"zip":"02120"})
race = session.query(Event).filter_by(id=race.id).first()
self.assertTrue(race.zip == '02120')
session.close()
def test_07_updating_email(self):
session = Session()
mickey = session.query(User).filter_by(name='Mickey Mouse').first()
q = session.query(User).filter_by(id=mickey.id)
q = q.update({"email":"*****@*****.**"})
mickey = session.query(User).filter_by(id=mickey.id).first()
self.assertTrue(mickey.email == '*****@*****.**')
session.close()
def get_neighborhoods(id):
s = Session()
result = []
q = s.query(VolunteerNeighborhoods).filter_by(volunteer_id=id)
for n in q:
result.append(n.neighborhood)
s.close()
return result
def test_06_updating_name(self):
session = Session()
mickey = session.query(User).filter_by(name='Mickey Mouse').first()
q = session.query(User).filter_by(id=mickey.id)
q = q.update({"name":"Wood Joey"})
mickey = session.query(User).filter_by(id=mickey.id).first()
self.assertTrue(mickey.name == 'Wood Joey')
session.close()
def test_04_updating_name(self):
session = Session()
test = session.query(User).filter_by(name='Test Org').first()
q = session.query(User).filter_by(id=test.id)
q = q.update({"name":"Wood Joey"})
test = session.query(User).filter_by(id=test.id).first()
self.assertTrue(test.name == 'Wood Joey')
session.close()
def show(self, post_id):
session = Session()
post = session.query(Post).get(post_id)
session.close()
if post == None:
raise web.notfound()
else:
return post
def getOrgMember(self, id):
s = Session()
content = s.query(OrgMember).filter_by(id=id).first()
s.close()
if content:
return content
else:
raise ValueError("user does not exist")
def test_05_updating_name(self):
session = Session()
race = session.query(Event).filter_by(name='Race for the Cure').first()
q = session.query(Event).filter_by(id=race.id)
q = q.update({"name":"Wood Joey"})
race = session.query(Event).filter_by(id=race.id).first()
self.assertTrue(race.name == 'Wood Joey')
session.close()
def updateEvent(self, event_id, update_data):
session = Session()
try:
session.query(Event).filter_by(id=event_id).update(json.loads(update_data))
except:
session.rollback()
raise #exception of some sort
finally:
session.close()
def load_user(userid):
if not userid:
return None
session = Session()
user = session.query(Author).filter(Author.id == int(userid)).first()
session.close()
return user
def test_15_sign_up(self):
session = Session()
joey = session.query(Volunteer).filter_by(name='Joey Wood').first()
event = session.query(Event).filter_by(state='MA').first()
addEvent(event.id, joey.id)
attendee = session.query(Attendee).filter_by(userID=joey.id).first()
print(attendee.userID)
print(joey.id)
self.assertTrue(attendee.userID == joey.id)
session.close()
