def auth_user(login, password, type):
"""
@rtype : object
"""
session = Session()
users = session.query(User).order_by(User.id)
users_logins = []
for instance in users:
users_logins.append(instance.login)
for instance in users:
if login in users_logins and login == instance.login:
user = instance
if not check_pass(user, password):
return False
else:
return start_sys(user, session)
elif not login in users_logins:
if type == "guest":
default_room = session.query(Room).filter(Room.name == "default").one()
guest_perm = session.query(Perm).filter(Perm.name == "guest").one()
a = Associations()
a.user = User(login, login, None, 0)
u = a.user
a.perm = guest_perm
default_room.user.append(a)
session.add(a)
session.commit()
return start_sys(u, session)
else:
continue
session.commit()
session.close()
def test_content(self):
engine = Mikado.utilities.dbutils.connect(self.json)
sessionmaker = sqlalchemy.orm.sessionmaker(bind=engine)
session = sessionmaker()
# Simple tests based on the static content of the dictionary
self.assertEqual(session.query(Mikado.serializers.junction.Junction).count(), 371,
self.json["db_settings"])
self.assertEqual(session.query(Mikado.serializers.orf.Orf).count(), 80)
self.assertEqual(session.query(Mikado.serializers.blast_serializer.Target).count(), 38909)
self.assertEqual(session.query(Mikado.serializers.blast_serializer.Query).count(), 93)
self.assertEqual(session.query(Mikado.serializers.blast_serializer.Hit).count(), 344)
self.assertEqual(session.query(Mikado.serializers.blast_serializer.Hsp).count(), 410)
first_query = session.query(Mikado.serializers.blast_serializer.Query).limit(1).one()
astup = first_query.as_tuple()
self.assertTrue(astup._fields, ("query_id", "query_name", "query_length"))
self.assertIsInstance(astup.query_id, int)
self.assertIsInstance(astup.query_length, int)
self.assertIsInstance(astup.query_name, str)
first_target = session.query(
Mikado.serializers.blast_serializer.Target).limit(1).one()
astup = first_target.as_tuple()
self.assertTrue(astup._fields, ("target_id", "target_name", "target_length"))
self.assertIsInstance(astup.target_id, int)
self.assertIsInstance(astup.target_length, int)
self.assertIsInstance(astup.target_name, str)
def start_sys(user, session):
"""
This function return data for UI initialisation.
@param user
"""
user_rooms = []
rooms = session.query(Room).all()
for room in rooms:
room_users = []
for r_user in room.user:
room_users.append(session.query(User.name).filter(User.id == r_user.user_id).scalar())
for r_user in room.user:
if r_user.user_id == user.id:
the_room = dict(
room_name=room.name,
perm=session.query(
Perm.id,
Perm.name,
Perm.add_user,
Perm.create_room,
Perm.create_vote,
Perm.delete_room,
Perm.delete_user,
Perm.delete_vote,
Perm.make_secure,
Perm.make_unsecure,
Perm.voting,
Perm.edit_perm,
Perm.edit_perm_def,
)
.filter(Perm.id == r_user.perm_id)
.one()
.__dict__,
users=room_users,
secure=room.secure,
auth=room.auth,
)
user_rooms.append(the_room)
start_chat_system = {
"user_login": user.login,
"user_name": user.name,
"user_reg": user.registered,
"user_rooms": user_rooms,
}
return start_chat_system
def __init__(self, q): self.q = q self.columns = [ "chrome", "pos_stt", "ref", "alt", "FP", "gene", "sample_name", "panel_name", "barcode", "platform", "zygosity", ] self.query = session.query( Site_info.chrome, Site_info.pos_stt, Site_info.ref, Site_info.alt, Site_info.FP, Site_info.gene, Sample_info.sample_name, Panel_info.panel_name, Data_info.barcode, Data_info.platform, Site_data.zygosity, )\ .join(Site_data, Site_data.site_idx == Site_info.site_idx)\ .join(Data_info, Data_info.data_idx == Site_data.data_idx)\ .join(Sample_info, Sample_info.sample_idx == Data_info.sample_idx)\ .join(Panel_info, Panel_info.panel_idx == Data_info.panel_idx)\
def deleteAllCategories(self):
session = self.session()
for cat in session.query(Category):
session.delete(cat) #tasks get deleted cascade
session.commit()
def main():
db_conn = sqa.connect_db()
Session = sessionmaker(bind=db_conn)
session = Session()
res = session.query(sqa.UrlRecords).filter(sqa.UrlRecords.num_redir>='5').all()
for i in res:
print i.exp_url
def deleteCategory(self, p_CategoryId):
session = self.session()
query = session.query(Category).filter(Category.id==p_CategoryId)
category = query.first()
session.delete(category)
session.commit()
def getTasksByCategory(self, p_Category):
session = self.session()
tasks = []
for task in session.query(Task).filter_by(category_id=p_Category.id):
tasks.append(task)
return tasks
def getAllCategories(self):
session = self.session()
categories = []
for cat in session.query(Category):
categories.append(cat)
return categories
def deleteTask(self, p_TaskId):
session = self.session()
query = session.query(Task).filter(Task.id==p_TaskId)
task = query.first()
session.delete(task)
session.commit()
def upgrade():
session = sa.orm.session.Session(bind=op.get_bind())
if session.query(TagCategory).count() == 0:
category = TagCategory()
category.name = 'default'
category.color = 'default'
category.version = 1
category.default = True
session.add(category)
session.commit()
def editTask(self, p_TaskId, p_TaskText):
session = self.session()
query = session.query(Task).filter(Task.id==p_TaskId)
task = query.first()
task.tasktext = p_TaskText
session.update(task)
session.commit()
def editCategory(self, p_CategoryId, p_CategoryText):
session = self.session()
#this is probably not needed for an update...see SA docs again
query = session.query(Category).filter(Category.id==p_CategoryId)
cat = query.first()
cat.categorytitle = p_CategoryText
session.update(cat)
session.commit()
def get_or_create(session, model, **kwargs):
instance = session.query(model).filter_by(**kwargs).first()
if instance:
return instance
else:
params = dict((k, v) for k, v in kwargs.iteritems() if not isinstance(v, ClauseElement))
instance = model(**params)
session.add(instance)
session.flush()
session.commit()
return instance
def _validate_bs(self, key, b):
if b is None:
if session is not None:
# print "session is not None"
# print "querying for a b"
b = session.query(B).first()
# if b is not None:
# print "found a b"
return b
def query_SUB_instances( me, session, klas ):
m = me.mappers[ klas]
f = m.polymorphic_sub_only
if f is None: return ()
q = session.query( m.polymorphic_all )
#XXX neither of these work for concrete... may need another mapper
# session.query( klas).from_statement( q4sub)
# session.query( klas).select_from( q4sub)
# session.query( klas).with_polymorphic( '*', q4sub)
if isinstance( f, sqlalchemy.sql.Selectable):
return q.select_from( f) #allows furtner joins/filters
#return q.from_statement( f) #no furtner joins/filters
else:
return q.filter( f)
def test_connect_to_shm(self):
self.json["pick"]["run_options"]['shm'] = True
shutil.copy(self.json["db_settings"]["db"], "/dev/shm/")
self.json["pick"]["run_options"]['shm_db'] = os.path.join(
"/dev/shm/",
self.json["db_settings"]["db"])
connector = Mikado.utilities.dbutils.connect(self.json)
self.assertEqual(str(connector.url), "sqlite://")
engine = Mikado.utilities.dbutils.connect(self.json)
sessionmaker = sqlalchemy.orm.sessionmaker(bind=engine)
session = sessionmaker()
first_target = session.query(
Mikado.serializers.blast_serializer.Target).limit(1).one()
astup = first_target.as_tuple()
self.assertTrue(astup._fields, ("target_id", "target_name", "target_length"))
def _create_handles(self, handles):
if self.regressor is None:
score_keys = sorted(list(self.json_conf["scoring"].keys()))
else:
score_keys = self.regressor["scoring"].metrics
# Define mandatory output files
db_connection = functools.partial(
dbutils.create_connector,
self.json_conf,
self.logger)
engine = create_engine("{0}://".format(self.json_conf["db_settings"]["dbtype"]),
creator=db_connection)
session = sqlalchemy.orm.sessionmaker(bind=engine)()
score_keys = ["tid", "parent", "score"] + sorted(score_keys + ["source_score"])
metrics = Superlocus.available_metrics[3:]
metrics.extend(["external.{}".format(_.source) for _ in session.query(ExternalSource.source).all()])
metrics = Superlocus.available_metrics[:3] + sorted(metrics)
self.locus_metrics, self.locus_scores, self.locus_out = self.__create_step_handles(
handles[0], metrics, score_keys)
self._handles.append([self.locus_metrics,
self.locus_scores,
self.locus_out])
# Subloci
if handles[1][0]:
self.sub_metrics, self.sub_scores, self.sub_out = self.__create_step_handles(
handles[1], metrics, score_keys)
self._handles.append([self.sub_metrics, self.sub_scores, self.sub_out])
else:
self._handles.append([None, None, None])
# Monoloci
if handles[2][0]:
self.mono_metrics, self.mono_scores, self.mono_out = self.__create_step_handles(
handles[2], metrics, score_keys)
self._handles.append([self.mono_metrics, self.mono_scores, self.mono_out])
else:
self._handles.append([None, None, None])
return
def add_u_to_the_r(uname, room_name, perm_name):
session = Session()
new_u = Associations()
user = session.query(User.id).filter(User.name == uname).scalar()
room = session.query(Room.id).filter(Room.name == room_name).scalar()
perm = session.query(Perm.id).filter(Perm.name == perm_name).scalar()
new_u.user_id = user
new_u.perm_id = perm
new_u.room_id = room
session.add(new_u)
session.commit()
room_obj = session.query(Room).get(room)
room_users = []
the_room = {}
for r_user in room_obj.user:
room_users.append(session.query(User.name).filter(User.id == r_user.user_id).one()[0])
for r_user in room_obj.user:
if r_user.user_id == user:
the_room = dict(
room_name=room_obj.name,
perm=session.query(
Perm.id,
Perm.name,
Perm.add_user,
Perm.create_room,
Perm.create_vote,
Perm.delete_room,
Perm.delete_user,
Perm.delete_vote,
Perm.make_secure,
Perm.make_unsecure,
Perm.voting,
Perm.edit_perm,
Perm.edit_perm_def,
)
.filter(Perm.id == r_user.perm_id)
.one()
.__dict__,
users=room_users,
secure=room_obj.secure,
auth=room_obj.auth,
)
add_user_obj = {"user_name": uname, "room": the_room}
session.close()
return add_user_obj
def create_debt_item(token):
body = request.get_json()
new_name = body.get('name', None)
new_category = body.get('category', None)
new_amount = body.get('amount', None)
search = body.get('searchTerm')
try:
debt = Debt(name=new_name,
category=new_category,
amount=new_amount)
debt.insert()
selection = Debt.query.order_by(Debt.id).all()
current_debt = paginate_debt_items(request, selection)
return jsonify({
"success": True,
"create": debt.id,
"debt": debt.format(),
"total_debt": session.query(func.count(Debt.amount)),
"debt_items": current_debt
}), 201
except:
abort(422)
def __init__(self, q): self.q = q self.columns = [ "chrome", "pos_stt", "ref", "alt", "HGMD_stat", "HGMD_comment", "causal", "rsID", "gene", "file_name", "YAOMING_name", "FD_id", "FA_id", "zygosity", "info" ] self.query = session.query( Site_info.chrome, Site_info.pos_stt, Site_info.ref, Site_info.alt, Site_info.HGMD_stat, Site_info.HGMD_comment, Site_info.causal, Site_info.rsID, Site_info.gene, Sample_info.file_name, Sample_info.YAOMING_name, Sample_info.FD_id, Sample_info.FA_id, Site_sample.zygosity, Site_sample.info, )\ .join(Site_sample, Site_sample.site_idx == Site_info.site_idx)\ .join(Sample_info, Sample_info.sample_idx == Site_sample.sample_idx)\
def delete_cashflow_item(payload, id):
try:
cashflow_item = Cashflow.query.filter(
Cashflow.id == id).one_or_none()
if cashflow_item is None:
abort(404)
cashflow_item.delete()
selection = Cashflow.query.order_by(Cashflow.id).all()
current_cashflow = paginate_cashflow_items(request, selection)
return jsonify({
"success":
True,
"delete":
id,
"cashflow_items":
current_cashflow,
"total_cashflow":
session.query(func.count(Cashflow.amount))
}), 200
except:
abort(422)
def precipitation():
# Create our session (link) from Python to the DB
session = Session(engine)
# Starting from the most recent data point in the database.
date = dt.datetime(2017, 8, 23)
# Calculate the date one year from the last date in data set.
year_ago = date - dt.timedelta(days=365)
# Perform a query to retrieve the data and precipitation scores
data = session.query(Measurement.date, Measurement.prcp).\
filter(Measurement.date>year_ago).order_by(Measurement.date).all()
session.close()
precipitation_data = []
for date, prcp in data:
prcp_dict = {}
prcp_dict["date"] = date
prcp_dict["prcp"] = prcp
precipitation_data.append(prcp_dict)
return jsonify(precipitation_data)
def post(self, request):
dict = json.loads(request.body.decode())
cid = dict.get('cid')
score = dict.get('score')
#参数校验
if not all([
re.match(r'^\d+$', cid),
re.match(r'[1-9]\d{0,6}$|10000000', score)
]):
return http.HttpResponseForbidden("客户端编号和分数为数字,分数范围为1...10000000")
score = int(score)
#查询当前客户端有没有该用户
try:
rank = session.query(user_ranking).filter(
user_ranking.client_code == cid).first()
#没有就创建,有就修改
if not rank:
rank = user_ranking(client_code=cid, score=score)
session.add(rank)
session.commit()
else:
rank.score = score
session.commit()
except exc.DatabaseError as e:
logger.error(e)
return http.JsonResponse({
'code': RETCODE.DBERR,
'errmsg': '数据库操失败'
})
return JsonResponse({'code': RETCODE.OK, 'errmsg': 'ok'})
def main():
engine = sqlalchemy.create_engine(MYSQL_URL, encoding='UTF8', echo=True)
sqlalchemy.orm.session.Session = sqlalchemy.orm.sessionmaker(
bind=engine) # 创建sesstion类型
#1.dept_list=[Dept(dname="软件部"),Dept(dname="信息部"),Dept(dname="客服部")]
#1.company=Company(cid="c-002",cname='xiaohua',site='www.xiaohua.com',depts=dept_list)
session = sqlalchemy.orm.session.Session()
company = session.query(Company).join(Dept).filter(
Company.cid == Dept.cid).filter(Company.cid == "C-001").filter(
Dept.dname == "教材研发部").one()
print(company)
print(company.depts)
#3.company=session.query(Company).get("C-001")
#print(company)
#print(company.depts)
#2.dept=Dept(dname="测试部",cid="c-002')
#2.session.add(kept)
'''session.add(company)
session.commit()
for dept in dept_list:
print("新增部门编号did=%s"%dept.did)'''
session.close()
def before_flush(self, session, flush_context, instances):
from dino.db.element import Element
for element in session.new:
if isinstance(element, Element):
if session.query(element.__class__).filter_by(instance_name=element.instance_name).count() > 0:
raise ElementExistsError("Element already exists: %s" % element.element_name)
users = Table('users', metadata, autoload=True, autoload_with=conn, postgresql_ignore_search_path=True)
emails = Table('emails', metadata, autoload=True, autoload_with=conn, postgresql_ignore_search_path=True)
class User(object):
pass
class Email(object):
pass
usermapper = mapper(User, users)
emailmapper = mapper(Email, emails)
session = create_session()
mary = session.query(User, users.c.name)
print mary.users_age
# close it. if you're using connection pooling, the
# search path is still set up there, so you might want to
# revert it first
conn.close()
def query_BASE_instances( me, session, klas ):
m = me.mappers[ klas]
if m.plain is None: return ()
return session.query( m.plain )
def query_ALL_instances( me, session, klas ):
return session.query( klas)
