def post(self):
"""报名工作"""
parser = reqparse.RequestParser()
parser.add_argument('userId', type=int)
parser.add_argument('jobId', type=int)
parser.add_argument('message', type=str)
args = parser.parse_args()
if Job.query.filter(
and_(Job.id == args['jobId'],
Job.recruitNum <= Job.signNum)).first():
raise NoNumberError()
elif Job_Signup.query.filter(
and_(Job_Signup.jobId == args['jobId'],
Job_Signup.userId == args['userId'])).first():
raise HaveSignError()
else:
stu = Student.query.filter(
and_(Student.isDel == 0,
Student.uId == args['userId'])).first_or_404()
form = Job_Signup()
form.userId = args['userId']
form.stuId = stu.id
form.jobId = args['jobId']
form.message = args['message']
form.add()
job = Job.query.filter_by(id=args['jobId']).first_or_404()
job.signNum += 1
job.update()
return {"msg": "报名成功"}, 200
def get_projects():
page = request.args.get('currentPage', 1, type=int)
per_page = request.args.get('pageSize', 10, type=int)
id = request.args.get('id')
pro_name = request.args.get('pro_name')
condition = (Project.status==1)
if id:
condition = and_(Project.id==id)
if pro_name:
condition = and_(Project.pro_name.like('%{0}%'.format(pro_name)))
pagination = Project.query.filter(condition).paginate(page, per_page,error_out=False)
projects = pagination.items
prev = None
if pagination.has_prev:
prev = url_for('api.get_projects', page=page-1)
next = None
if pagination.has_next:
next = url_for('api.get_projects', page=page+1)
return jsonify({
'code': 1,
'projects': [project.to_json() for project in projects],
'prev': prev,
'next': next,
'count': pagination.total
})
def get(self):
"""搜索工作"""
parser = reqparse.RequestParser()
parser.add_argument('key', type=str, location='values')
parser.add_argument('type', type=int, location='values')
args = parser.parse_args()
if args['key'] is None:
args['key'] = ""
jobs = Job.query.filter(
and_(Job.isDel == 0,
Job.tittle.like('%' + args['key'] + '%'))).all()
if jobs is None:
jobs = Job.query.filter(
and_(Job.isDel == 0, Job.recruitNum > Job.signNum)).all()
if args.type == 1:
newjobs = queryToDict(jobs)
res = [[], [], [], []]
for job in newjobs:
job['creatTime'] = get_from_timestamp(job['creatTime'])
res[0] = newjobs
for i in range(3):
if job['status'] == i + 1:
res[i + 1].append(job)
return res, 200
return make_result(jobs), 200
def update_deployments(refdes, dnum, uid, launch, recover, lat, lon, cruise, depth, session):
# print
# print
# print refdes, dnum, uid, launch, recover, lat, lon, cruise, depth
deployments = session.query(Deployment).filter(and_(Deployment.sensor_uid == uid,
Deployment.deployment == dnum)).all()
deployments += session.query(Deployment).filter(and_(Deployment.mooring_uid == uid,
Deployment.deployment == dnum)).all()
# for each in deployments:
# print 'depl', each
if deployments:
for d in deployments:
d.launch_date = launch
d.recover_date = recover
d.latitude = lat
d.longitude = lon
d.cruise = cruise
d.depth = depth
session.add(d)
else:
d = Deployment(refdes=refdes, deployment=dnum, launch_date=launch, recover_date=recover,
latitude=lat, longitude=lon, cruise=cruise, depth=depth, mooring_uid=uid)
session.add(d)
session.flush()
def get_advanced_search_query(employer_id, params, status):
skills = params.get('skills')
locations = params.get('locations')
role = params.get('role')
name = params.get('name')
salary = params.get('salary')
query = DBSession.query(Candidate.id).filter(Candidate.status == status)
if employer_id:
query = query.outerjoin(V_CANDIDATE_CURRENT_EMPLOYERS,
and_(V_CANDIDATE_CURRENT_EMPLOYERS.c.candidate_id == Candidate.id,
V_CANDIDATE_CURRENT_EMPLOYERS.c.employer_id == employer_id)) \
.filter(V_CANDIDATE_CURRENT_EMPLOYERS.c.candidate_id == None)
if locations:
query = query.join(PreferredLocation, Candidate.id == PreferredLocation.candidate_id)
country_filter = set([c['country_iso'] for c in locations])
city_filter = [and_(City.name == loc['city'], City.country_iso == loc['country_iso']) for loc in locations]
city_ids = DBSession.query(City.id).filter(or_(*city_filter)).all()
query = query.filter(or_(PreferredLocation.city_id.in_(city_ids),
PreferredLocation.country_iso.in_(country_filter)))
if salary or role:
query = query.join(TargetPosition)
if salary:
query = query.filter(TargetPosition.minimum_salary <= salary)
if role:
role = get_by_name_or_raise(Role, role)
query = query.filter(TargetPosition.role_id == role.id)
if name and employer_id:
name = name.lower()
employer_ids = func.array_agg(Offer.employer_id, type_=ARRAY(TEXT)).label('employer_ids')
offer_query = DBSession.query(Offer.candidate_id, employer_ids).filter(Offer.accepted != None) \
.group_by(Offer.candidate_id).subquery()
query = query.outerjoin(offer_query, offer_query.c.candidate_id == Candidate.id).filter(
or_(cast(Candidate.id, TEXT).startswith(name),
and_(
or_(func.lower(Candidate.first_name).startswith(name),
func.lower(Candidate.last_name).startswith(name)),
or_(
offer_query.c.employer_ids.any(str(employer_id)),
Candidate.anonymous == False
)
)
)
)
query = query.group_by(Candidate.id)
if skills:
query = query.join(CandidateSkill).join(Skill).filter(Skill.name.in_(skills)) \
.having(func.count(Skill.name) == len(skills))
return query
def _entry_query(self, session, entry):
db_entry = session.query(EntryListEntry).filter(and_(
EntryListEntry.list_id == self._db_list(session).id,
or_(
EntryListEntry.title == entry['title'], and_(
EntryListEntry.original_url,
EntryListEntry.original_url == entry[
'original_url'])))).first()
return db_entry
def _to_bool_clause(constraint):
if constraint is not None:
if isinstance(constraint, dict):
return and_(column(k) == v for k, v in constraint.items())
else:
return or_(and_(_convert_predicate(predicate)
for predicate in conjunction_clause)
for conjunction_clause in constraint)
else:
return text('')
def _entry_query(self, session, entry):
db_entry = session.query(EntryListEntry).filter(and_(
EntryListEntry.list_id == self._db_list(session).id,
or_(
EntryListEntry.title == entry['title'], and_(
EntryListEntry.original_url,
EntryListEntry.original_url == entry[
'original_url'])))).first()
return db_entry
def immediate_children(self):
"""
Get immediate children of the organization
Reference:
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
http://www.sitepoint.com/hierarchical-data-database/
Generated SQL Sample:
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1
ORDER BY node.lft;
"""
s_node = aliased(Organization, name='s_node')
s_parent = aliased(Organization, name='s_parent')
sub_tree = db.session.query(s_node.id, (func.count(s_parent.name) - 1).label('depth')). \
filter(and_(between(s_node.lft, s_parent.lft, s_parent.rgt), s_node.id == self.id)) \
.group_by(s_node.id, s_node.lft).order_by(s_node.lft).subquery()
t_node = aliased(Organization, name='t_node')
t_parent = aliased(Organization, name='t_parent')
t_sub_parent = aliased(Organization, name='t_sub_parent')
# Postgres does not support label as (func.count(t_parent.name) - (sub_tree.c.depth + 1)).label('xxx')
# And have the field in having clause will cause issue.
query = (
db.session.query(
t_node.id, t_node.name,
(func.count(t_parent.name) - (sub_tree.c.depth + 1))).filter(
and_(
between(t_node.lft, t_parent.lft, t_parent.rgt),
between(t_node.lft, t_sub_parent.lft,
t_sub_parent.rgt),
t_node.id !=
self.id, # Exclude current node --> itself
t_sub_parent.id == sub_tree.c.id)).group_by(
t_node.id, t_node.name, t_node.lft,
'depth').having(
(func.count(t_parent.name) -
(sub_tree.c.depth + 1)) <= 1).order_by(
t_node.lft))
return id_query_to_obj(Organization, query)
def _search_on_extension(self, query):
return query.outerjoin(
LineExtension,
and_(LineExtension.line_id == LineFeatures.id,
LineFeatures.commented == 0)).outerjoin(
Extension,
and_(
LineExtension.extension_id == Extension.id,
LineExtension.line_id == LineFeatures.id,
Extension.commented == 0,
))
def _to_bool_clause(constraint):
if constraint is not None:
if isinstance(constraint, dict):
return and_(column(k) == v for k, v in constraint.items())
else:
return or_(
and_(
_convert_predicate(predicate)
for predicate in conjunction_clause)
for conjunction_clause in constraint)
else:
return text('')
def get(self):
"""查看我的申请详情"""
parser = reqparse.RequestParser()
parser.add_argument('useId', type=int, location='values')
parser.add_argument('jobId', type=int, location='values')
args = parser.parse_args()
res = db.session.query(Job.id, Job.tittle, Job.place, Job.reward, Job_Signup.status). \
filter(Job.isDel == 0). \
filter(and_(Job_Signup.jobId == Job.id, Job_Signup.jobId == args['jobId'])). \
filter(and_(Job_Signup.isDel == 0, Job_Signup.userId == args['useId'])). \
all()
return make_result(res), 200
def get_filter(self, column):
"""
Obtém o filtro de acordo com o range de datas passados na coluna column.
:param column: coluna para filtrar. Essa coluna deve ser do tipo datetime.
:return: filtro do sqlalchemy
"""
if self.use_equal:
filter_ = and_(column >= self.min_date, column <= self.max_date)
else:
filter_ = and_(column > self.min_date, column < self.max_date)
return filter_
def filter_query_for_content_label_as_path(
self,
query: Query,
content_label_as_file: str,
is_case_sensitive: bool = False,
) -> Query:
"""
Apply normalised filters to found Content corresponding as given label.
:param query: query to modify
:param content_label_as_file: label in this
FILE version, use Content.get_label_as_file().
:param is_case_sensitive: Take care about case or not
:return: modified query
"""
file_name, file_extension = os.path.splitext(content_label_as_file)
label_filter = Content.label == content_label_as_file
file_name_filter = Content.label == file_name
file_extension_filter = Content.file_extension == file_extension
if not is_case_sensitive:
label_filter = func.lower(Content.label) == \
func.lower(content_label_as_file)
file_name_filter = func.lower(Content.label) == \
func.lower(file_name)
file_extension_filter = func.lower(Content.file_extension) == \
func.lower(file_extension)
return query.filter(
or_(
and_(
Content.type == ContentType.File,
file_name_filter,
file_extension_filter,
),
and_(
Content.type == ContentType.Thread,
file_name_filter,
file_extension_filter,
),
and_(
Content.type == ContentType.Page,
file_name_filter,
file_extension_filter,
),
and_(
Content.type == ContentType.Folder,
label_filter,
),
))
def filter_query_for_content_label_as_path(
self,
query: Query,
content_label_as_file: str,
is_case_sensitive: bool = False,
) -> Query:
"""
Apply normalised filters to found Content corresponding as given label.
:param query: query to modify
:param content_label_as_file: label in this
FILE version, use Content.get_label_as_file().
:param is_case_sensitive: Take care about case or not
:return: modified query
"""
file_name, file_extension = os.path.splitext(content_label_as_file)
label_filter = Content.label == content_label_as_file
file_name_filter = Content.label == file_name
file_extension_filter = Content.file_extension == file_extension
if not is_case_sensitive:
label_filter = func.lower(Content.label) == \
func.lower(content_label_as_file)
file_name_filter = func.lower(Content.label) == \
func.lower(file_name)
file_extension_filter = func.lower(Content.file_extension) == \
func.lower(file_extension)
return query.filter(or_(
and_(
Content.type == ContentType.File,
file_name_filter,
file_extension_filter,
),
and_(
Content.type == ContentType.Thread,
file_name_filter,
file_extension_filter,
),
and_(
Content.type == ContentType.Page,
file_name_filter,
file_extension_filter,
),
and_(
Content.type == ContentType.Folder,
label_filter,
),
))
def get_questions(connection: Connection,
survey_id: str,
auth_user_id: [str, None]=None,
email: [str, None]=None) -> ResultProxy:
"""
Get all the questions for a survey identified by survey_id ordered by
sequence number restricted by auth_user.
:param connection: a SQLAlchemy Connection
:param survey_id: the UUID of the survey
:param auth_user_id: the UUID of the user
:param email: the user's e-mail address
:return: an iterable of the questions (RowProxy)
"""
table = question_table.join(survey_table)
conds = [question_table.c.survey_id == survey_id]
if auth_user_id is not None:
if email is not None:
raise TypeError('You cannot specify both auth_user_id and email')
conds.append(survey_table.c.auth_user_id == auth_user_id)
elif email is not None:
table = table.join(auth_user_table)
conds.append(auth_user_table.c.email == email)
else:
raise TypeError('You must specify either auth_user_id or email')
questions = connection.execute(
select([question_table]).select_from(table).where(
and_(*conds)).order_by('sequence_number asc'))
return questions
def parent(self):
if self.lft is None or self.rgt is None:
return None
return db.session.query(Organization).filter(
and_(Organization.lft < self.lft,
Organization.rgt > self.rgt)).order_by(desc(
Organization.lft)).first()
def add_participants(username, trip_id):
if not request.json or 'participants' not in request.json:
return make_response(jsonify({'error': 'Missing required parameter'}),
422)
participants = request.json.get('participants')
e = create_engine("sqlite:///trip_communicator.db")
Ses = sessionmaker(bind=e)
session = Ses()
trip = session.query(Trip).filter(
and_(Trip.trip_id == trip_id, Trip.owner_name == username)).first()
if trip is not None:
for p in participants:
participant = add_participant_from_response(session, p, trip)
if participant is not None:
session.add(participant)
commit_and_close(session)
return make_response(jsonify({'Response': 'OK'}), 201)
commit_and_close(session)
return make_response(
jsonify(
{'Error': 'User is not the owner of trip or trip doesn\'t exist'}),
403)
def update_order(items, request_):
"""Update cart."""
if request.form.get("submit"):
chosen_goods = [
(item, int(request_.form[f"{item.slug}_cart_qty"]))
for item in items
if (request_.form.get(f"{item.slug}_cart_qty") and item.available
and int(request.form[f"{item.slug}_cart_qty"]))
]
else:
chosen_goods = [(item, int(request_.form[f"{item.slug}_cart_qty"]))
for item in items
if request_.form.get(f"{item.slug}_cart_qty")]
for item, quantity in chosen_goods:
ordered_item = OrderItem.query.filter(
and_(
OrderItem.order == item.order,
OrderItem.product == item.product,
OrderItem.shop == item.shop_id,
)).first()
if ordered_item:
ordered_item.quantity = quantity
db.session.commit()
return chosen_goods
def show_goods_by_category(cat, request_):
"""Show goods by selected category on request."""
products = Product.query.filter_by(category=cat.id).all()
if products:
items = set()
for product in products:
infos = ProductInfo.query.filter(
and_(
ProductInfo.product == product.id,
ProductInfo.shop.in_(opened_shops()),
ProductInfo.quantity > 0,
)).all()
if infos:
pr_info: ProductInfo
for pr_info in infos:
items.add(
Item(
order=None,
name=pr_info.name,
shop_id=pr_info.shop_rel.id,
shop_name=pr_info.shop_rel.title,
stock_qty=pr_info.quantity,
quantity=None,
status=None,
price=price_to_str(pr_info.price_rrc),
agg_price=None,
discount_price=None,
discount=None,
product=pr_info.product,
available=pr_info.shop_rel.shop_manager.is_active,
slug=pr_info.slug,
))
items = sort_items(request_, items)
return items
def lamoda_parsing():
source = _get_or_create_lamoda_source()
categories = Category.query.filter(
and_(
Category.parent_id.notin_([0, 10, 20, 30, 40, 50, 60, 70, 80, 90]),
Category.id.notin_([101, 102, 103]))).all()
for category in categories:
logging.info('Parsing category {}'.format(category))
parser = LamodaCategoryParser(category=category, source=source)
for page in range(1, PAGES_COUNT + 1):
products = parser.get_products(page)
product_count = 0
for product in products:
try:
db.session.add(product)
db.session.commit()
logger.info('New product {} added'.format(product))
product_count += 1
except Exception as exception:
db.session.rollback()
logger.error(
'{}: Received parsing top level error: {}'.format(
category, exception))
logger.info('For category "{}" received {} products!'.format(
category, product_count))
def find_deployment(refdes, deployment):
# print 'find_deployment(%r, %r)' % (refdes, deployment)
# attempt to find an exact match
record = session.query(Deployment).filter(and_(Deployment.refdes == refdes,
Deployment.deployment == deployment)).first()
if record:
# print 'found exact'
return record
parts = refdes.split('-', 2)
mooring = parts[0]
node = None
if len(parts) > 1:
node = parts[1]
# no exact match, attempt to find the node
if node is not None:
record = session.query(Deployment).filter(Deployment.refdes == '-'.join((mooring, node))).first()
if record:
# print 'found node'
return record
# no node, find the mooring
record = session.query(Deployment).filter(Deployment.refdes == mooring).first()
# if record:
# print 'found mooring'
return record
def get_products():
gender = request.args.get('gender', None)
results_per_page = int(request.args.get('results_per_page', 30))
page = int(request.args.get('page', 1))
get_all = bool(request.args.get('all', False))
_filter = m.Product.uv_card.isnot(None)
if gender:
_filter = and_(_filter, m.Product.gender == gender)
if get_all:
products = m.Product.query.filter(_filter).all()
else:
products = m.Product.query.filter(_filter).limit(
results_per_page).offset(results_per_page * (page - 1)).all()
response = {
'products': [product.full_serialize() for product in products],
'count': len(products)
}
if not get_all:
response['page'] = page
return JsonResponse(response)
def get_item(startDate=None, endDate=None):
""" Get item posting from the data store """
logger.info('Getting items from database.')
results_list = []
session = DB_SESSION()
if startDate is not None and endDate is not None:
results = (session.query(Item).filter(
and_(
Item.date_created >= datetime.datetime.strptime(
startDate, "%Y-%m-%dT%H:%M:%S"),
Item.date_created <= datetime.datetime.strptime(
endDate, "%Y-%m-%dT%H:%M:%S"))))
else:
results = session.query(Item).all()
for result in results:
results_list.append(result.to_dict())
logger.info(result.to_dict())
session.close()
return results_list, 200
def value_combo_exists(value, **kwargs):
""" Queries the database for any values in the unique store that alrdeady exist with given value and metadata combo
"""
kwargs['unique_value'] = {kwargs['field_name']: value}
del kwargs['field_name']
filters = [(getitem(Unique, arg_name) == arg_val) for arg_name, arg_val in kwargs.items()]
return DB.session.query(Unique.query.exists().where(and_(*filters))).scalar()
def _entry_query(self, session, entry, approved=None):
query = session.query(PendingListEntry).filter(PendingListEntry.list_id == self._db_list(session).id). \
filter(or_(PendingListEntry.title == entry['title'],
and_(PendingListEntry.original_url, PendingListEntry.original_url == entry['original_url'])))
if approved:
query = query.filter(PendingListEntry.approved == True)
return query.first()
def table(self, claus):
from sqlalchemy.sql import select, and_
if claus.name in self._query.tables:
return self._query.tables[claus.name]
tables = {}
for tb in claus.tables:
if tb not in self._query.tables:
self._query.connector.reflect(only=[tb])
tables[tb] = self._query.tables[tb]
dbtype = self._query.connector.type
binder = TBBinder(dbtype, tables)
columns = [binder.column(_) for _ in claus.columns]
where = []
for whereclause in claus.whereclauses:
if isinstance(whereclause, Clause):
whereclause = binder.bind(whereclause)
where.append(whereclause)
tbobj = select(columns)
for tb in tables.values():
tbobj = tbobj.select_from(tb)
tbobj = tbobj.where(and_(*where))
tbobj = tbobj.alias(claus.name)
self._query.tables[claus.name] = tbobj
return tbobj
def add_to_order(items):
"""Add items chosen by user to his cart."""
user = items.pop()
if items:
order = Order.query.filter_by(
user=user, status=OrderStateChoices.new.name).first()
if not order:
order = Order(user, OrderStateChoices.new.name)
db.session.add(order)
db.session.commit()
db.session.flush()
db.session.refresh(order)
cart = items[0][0].order
cart_goods = OrderItem.query.filter_by(order=cart).all()
for item, quantity in items:
ordered_item = OrderItem.query.filter(
and_(
OrderItem.order == order.id,
OrderItem.product == item.product,
OrderItem.shop == item.shop_id,
)).first()
if ordered_item:
ordered_item.quantity += quantity
db.session.commit()
else:
for good in cart_goods:
if good.product == item.product and good.shop == item.shop_id:
good.order = order.id
db.session.commit()
items = [item[0] for item in items]
delete_from_cart_or_order(items)
def find_deployment(refdes, deployment):
# print 'find_deployment(%r, %r)' % (refdes, deployment)
# attempt to find an exact match
record = session.query(Deployment).filter(
and_(Deployment.refdes == refdes,
Deployment.deployment == deployment)).first()
if record:
# print 'found exact'
return record
parts = refdes.split('-', 2)
mooring = parts[0]
node = None
if len(parts) > 1:
node = parts[1]
# no exact match, attempt to find the node
if node is not None:
record = session.query(Deployment).filter(
Deployment.refdes == '-'.join((mooring, node))).first()
if record:
# print 'found node'
return record
# no node, find the mooring
record = session.query(Deployment).filter(
Deployment.refdes == mooring).first()
# if record:
# print 'found mooring'
return record
def _find_entry(self, entry, session=None):
"""Finds `MovieListMovie` corresponding to this entry, if it exists."""
for id_name in SUPPORTED_IDS:
if entry.get(id_name):
log.debug('trying to match movie based off id %s: %s', id_name, entry[id_name])
res = (self._db_list(session).movies.join(MovieListMovie.ids).filter(
and_(
MovieListID.id_name == id_name,
MovieListID.id_value == entry[id_name]))
.first())
if res:
log.debug('found movie %s', res)
return res
# Fall back to title/year match
if entry.get('movie_name') and entry.get('movie_year'):
name, year = entry['movie_name'], entry['movie_year']
else:
name, year = split_title_year(entry['title'])
if not name:
log.verbose('no movie name to match, skipping')
return
log.debug('trying to match movie based of name: %s and year: %d', name, year)
res = (self._db_list(session).movies.filter(func.lower(MovieListMovie.title) == name.lower())
.filter(MovieListMovie.year == year).first())
if res:
log.debug('found movie %s', res)
return res
def __real_base_query(self, workspace: Workspace=None):
result = self.get_canonical_query()
# Exclude non displayable types
if not self._force_show_all_types:
result = result.filter(Content.type.in_(self.DISPLAYABLE_CONTENTS))
if workspace:
result = result.filter(Content.workspace_id==workspace.workspace_id)
# Security layer: if user provided, filter
# with user workspaces privileges
if self._user and not self._disable_user_workspaces_filter:
user = DBSession.query(User).get(self._user_id)
# Filter according to user workspaces
workspace_ids = [r.workspace_id for r in user.roles \
if r.role>=UserRoleInWorkspace.READER]
result = result.filter(or_(
Content.workspace_id.in_(workspace_ids),
# And allow access to non workspace document when he is owner
and_(
Content.workspace_id == None,
Content.owner_id == self._user_id,
)
))
return result
def _find_entry(self, entry, session=None):
"""Finds `MovieListMovie` corresponding to this entry, if it exists."""
# Match by supported IDs
for id_name in MovieListBase().supported_ids:
if entry.get(id_name):
log.debug('trying to match movie based off id %s: %s', id_name,
entry[id_name])
res = (self._db_list(session).movies.join(
MovieListMovie.ids).filter(
and_(MovieListID.id_name == id_name,
MovieListID.id_value == entry[id_name])).first())
if res:
log.debug('found movie %s', res)
return res
# Fall back to title/year match
if not entry.get('movie_name'):
self._parse_title(entry)
if entry.get('movie_name'):
name = entry['movie_name']
year = entry.get('movie_year') if entry.get('movie_year') else None
else:
log.warning('Could not get a movie name, skipping')
return
log.debug('trying to match movie based of name: %s and year: %s', name,
year)
res = (self._db_list(session).movies.filter(
func.lower(MovieListMovie.title) == name.lower()).filter(
MovieListMovie.year == year).first())
if res:
log.debug('found movie %s', res)
return res
def _find_entry(self, entry, session=None):
"""Finds `MovieListMovie` corresponding to this entry, if it exists."""
for id_name in MovieListBase().supported_ids:
if entry.get(id_name):
log.verbose('trying to match movie based off id %s: %s', id_name, entry[id_name])
res = (self._db_list(session).movies.join(MovieListMovie.ids).filter(
and_(
MovieListID.id_name == id_name,
MovieListID.id_value == entry[id_name]))
.first())
if res:
log.verbose('found movie %s', res)
return res
# Fall back to title/year match
if not entry.get('movie_name'):
self._parse_title(entry)
if entry.get('movie_name'):
name = entry['movie_name']
year = entry.get('movie_year') if entry.get('movie_year') else None
else:
log.warning('Could not get a movie name, skipping')
return
log.verbose('trying to match movie based of name: %s and year: %s', name, year)
res = (self._db_list(session).movies.filter(func.lower(MovieListMovie.title) == name.lower())
.filter(MovieListMovie.year == year).first())
if res:
log.verbose('found movie %s', res)
return res
def get_movie_by_id(list_id, movie_id, session=None):
log.debug('fetching movie with id %d from list id %d', movie_id, list_id)
return (
session.query(MovieListMovie)
.filter(and_(MovieListMovie.id == movie_id, MovieListMovie.list_id == list_id))
.one()
)
def _entry_query(self, session, entry, approved=None):
query = session.query(PendingListEntry).filter(PendingListEntry.list_id == self._db_list(session).id). \
filter(or_(PendingListEntry.title == entry['title'],
and_(PendingListEntry.original_url, PendingListEntry.original_url == entry['original_url'])))
if approved:
query = query.filter(PendingListEntry.approved == True)
return query.first()
def __real_base_query(self, workspace: Workspace = None):
result = self.get_canonical_query()
# Exclude non displayable types
if not self._force_show_all_types:
result = result.filter(Content.type.in_(self.DISPLAYABLE_CONTENTS))
if workspace:
result = result.filter(
Content.workspace_id == workspace.workspace_id)
# Security layer: if user provided, filter
# with user workspaces privileges
if self._user and not self._disable_user_workspaces_filter:
user = DBSession.query(User).get(self._user_id)
# Filter according to user workspaces
workspace_ids = [r.workspace_id for r in user.roles \
if r.role>=UserRoleInWorkspace.READER]
result = result.filter(
or_(
Content.workspace_id.in_(workspace_ids),
# And allow access to non workspace document when he is owner
and_(
Content.workspace_id == None,
Content.owner_id == self._user_id,
)))
return result
def get_entry_by_id(list_id, entry_id, session=None):
log.debug('fetching entry with id %d from list id %d', entry_id, list_id)
return (
session.query(EntryListEntry)
.filter(and_(EntryListEntry.id == entry_id, EntryListEntry.list_id == list_id))
.one()
)
def get_entry_by_title(list_id, title, session=None):
entry_list = get_list_by_id(list_id=list_id, session=session)
if entry_list:
return (
session.query(EntryListEntry)
.filter(and_(EntryListEntry.title == title, EntryListEntry.list_id == list_id))
.first()
)
def login():
username = request.form.get('username')
password = request.form.get('pwd')
# print(username)
user = emp.query.filter(and_(emp.username == username, emp.pwd == password))
if user.count() > 0:
return redirect(url_for('blue.userList'))
return render_template('login.html')
def get_entry_by_title(list_id, title, session=None):
entry_list = get_list_by_id(list_id=list_id, session=session)
if entry_list:
logger.debug('fetching entry with title `{}` from list id {}', title,
list_id)
return (session.query(PendingListEntry).filter(
and_(PendingListEntry.title == title,
PendingListEntry.list_id == list_id)).first())
def getEntityFact(self, conceptOID, reportOID, order_, session):
return GenericDao().getOneResult(
EntityFact,
and_(EntityFact.conceptOID == conceptOID,
EntityFact.reportOID == reportOID,
EntityFact.order_ == order_),
session,
raiseNoResultFound=False)
def get_movie_identifier(identifier_name, identifier_value, movie_id=None, session=None):
db_movie_id = session.query(MovieListID).filter(
and_(MovieListID.id_name == identifier_name,
MovieListID.id_value == identifier_value,
MovieListID.movie_id == movie_id)).first()
if db_movie_id:
log.debug('fetching movie identifier %s: %s', db_movie_id.id_name, db_movie_id.id_value)
return db_movie_id
def get_movie_identifier(identifier_name, identifier_value, movie_id=None, session=None):
db_movie_id = session.query(MovieListID).filter(
and_(MovieListID.id_name == identifier_name,
MovieListID.id_value == identifier_value,
MovieListID.movie_id == movie_id)).first()
if db_movie_id:
log.debug('fetching movie identifier %s: %s', db_movie_id.id_name, db_movie_id.id_value)
return db_movie_id
def get_users_with_filters(*filters):
general_filters = [User.login_enabled == True,
User.roles_rel.any(
and_(RoleXUser.role_name == RolesTypes.founder, RoleXUser.is_primary == True))]
notif_filters = filters[0]
if len(filters) > 1:
notif_filters = or_(*filters)
return DBSession.query(User).filter(*(general_filters + [notif_filters])).all()
def get_movie_by_title(list_id, title, session=None):
movie_list = get_list_by_id(list_id=list_id, session=session)
if movie_list:
log.debug('searching for movie %s in list %d', title, list_id)
return session.query(MovieListMovie).filter(
and_(
func.lower(MovieListMovie.title) == title.lower(),
MovieListMovie.list_id == list_id)
).first()
def get_entry_by_title(list_id, title, session=None):
entry_list = get_list_by_id(list_id=list_id, session=session)
if entry_list:
log.debug('fetching entry with title `%s` from list id %d', title, list_id)
return (
session.query(PendingListEntry)
.filter(and_(PendingListEntry.title == title, PendingListEntry.list_id == list_id))
.first()
)
def delete(id):
user = g.user
post = models.Post.query.filter(and_(models.Post.id == id ,models.Post.author == user)).all()
if post == None :
flash('Post not found')
return redirect(url_for('index'))
db.session.delete(post[0])
db.session.commit()
flash('Your post has been delete')
return redirect(url_for('managerpost'))
def delete_rows(engine, relation, rows):
_rows = list(rows)
if not _rows:
return
row_chunks = list(chunks(_rows, cfg.settings.get('row_delete_chunk_size',
60)))
for chunk in row_chunks:
whereclause = or_(and_(column(k) == row[k] for k in row)
for row in chunk)
del_query = delete(table(relation['name'])).where(whereclause)
_execute(engine, del_query)
def list(self):
candidate_count = func.count(Candidate.id).label("candidate_count")
last_used = func.max(Candidate.created).label("last_used")
codes_query = DBSession.query(InviteCode.code, InviteCode.description, InviteCode.created, candidate_count,
last_used).outerjoin(Candidate, and_(Candidate.invite_code_id == InviteCode.id)) \
.group_by(InviteCode.code, InviteCode.description, InviteCode.created)
def serializer(result):
return [r._asdict() for r in result]
return run_paginated_query(self.request, codes_query, serializer=serializer)
def api_schema_permission_delete(self):
schemaid = int(request.POST.get('schemaid'))
permid = int(request.POST.get('permid'))
RelModel = get_model('role_perm_rel')
flag = RelModel.filter(and_(RelModel.c.scheme == schemaid, RelModel.c.permission == permid)).remove()
ret = {}
ret['status'] = 'OK'
if not flag:
ret['status'] = 'ERR'
ret['msg'] = '删除权限失败,inner error'
return json(ret)
def joke_in_category(self, joke_id, category):
self.storage.connect()
jokes_categories = meta.tables["joke_category"]
query = sqlalchemy.select([jokes_categories.c.id]).where(
and_(jokes_categories.c.joke_id == joke_id, jokes_categories.c.subcategory_id == category["id"])
)
results = self.storage.execute(query)
self.storage.disconnect()
for result in results:
print result["id"]
return True
return False
def get_or_create_deployment(refdes, deployment, uid, session=None):
if session is None:
session = Session()
d = session.query(Deployment).filter(and_(Deployment.refdes == refdes,
Deployment.deployment == deployment,
Deployment.sensor_uid == uid)).first()
if d is None:
d = Deployment(refdes=refdes, deployment=deployment, sensor_uid=uid)
return d
def get_port_rates_dataframe(session, refdes_id, start, end):
now = datetime.utcnow()
if start is None:
start = now - timedelta(days=1)
if end is None:
end = now
query = session.query(PortCount).filter(and_(PortCount.reference_designator_id == refdes_id,
PortCount.collected_time >= start,
PortCount.collected_time < end)).order_by(PortCount.collected_time)
counts_df = pd.read_sql_query(query.statement, query.session.bind, index_col='collected_time')
counts_df['rate'] = counts_df.byte_count / counts_df.seconds
return counts_df
def get_one_by_label_and_parent(
self,
content_label: str,
content_parent: Content=None,
) -> Content:
"""
This method let us request the database to obtain a Content with its name and parent
:param content_label: Either the content's label or the content's filename if the label is None
:param content_parent: The parent's content
:param workspace: The workspace's content
:return The corresponding Content
"""
workspace = content_parent.workspace if content_parent else None
query = self._base_query(workspace)
parent_id = content_parent.content_id if content_parent else None
query = query.filter(Content.parent_id == parent_id)
file_name, file_extension = os.path.splitext(content_label)
return query.filter(
or_(
and_(
Content.type == ContentType.File,
Content.label == file_name,
Content.file_extension == file_extension,
),
and_(
Content.type == ContentType.Thread,
Content.label == file_name,
),
and_(
Content.type == ContentType.Page,
Content.label == file_name,
),
and_(
Content.type == ContentType.Folder,
Content.label == content_label,
),
)
).one()
def get_revision_join(cls):
"""
Return the Content/ContentRevision query join condition
:return: Content/ContentRevision query join condition
:rtype sqlalchemy.sql.elements.BooleanClauseList
"""
return and_(Content.id == ContentRevisionRO.content_id,
ContentRevisionRO.revision_id == DBSession.query(
ContentRevisionRO.revision_id)
.filter(ContentRevisionRO.content_id == Content.id)
.order_by(ContentRevisionRO.revision_id.desc())
.limit(1)
.correlate(Content))
def api_org_role_delete(self):
orgid = int(request.POST.get('orgid'))
roleid = int(request.POST.get('roleid'))
orgroleid = int(request.POST.get('orgroleid'))
OrgroleModel = get_model('orgrole')
OrgroleObj = OrgroleModel.get(orgroleid)
ret = {}
ret['status'] = 'OK'
OrgroleObj.delete()
count = OrgroleModel.filter(and_(OrgroleModel.c.role == roleid, OrgroleModel.c.organization == orgid)).count()
if count:
ret['status'] = 'ERR'
ret['msg'] = '删除组织角色失败'
return json(ret)
def api_schema_perm_update(self):
value = request.POST.get('value')
schema = request.POST.get('scheme')
permid = request.POST.get('permid')
selectedRoleList = _json.loads(value)
#----delete the origin role_perm_rel
RPRModel = get_model('Role_Perm_Rel')
do_(RPRModel.table.delete().where(and_(RPRModel.c.scheme == schema, RPRModel.c.permission == permid)))
#----batch add the role_perm_rel
for s in selectedRoleList:
roleid = s.split('_')[0]
RPRModel(role=roleid, permission=permid, scheme=schema).save()
return json({"status":"OK", "value":value})
