def randomProfile(category = None):
ids = db.session.query(Search.id).filter(Search.kind == category)
if category in ("bra"):
ids = ids.filter(not_(Search.id.startswith("0xx")))
elif category in ("cnae", "cbo", "wld"):
ids = ids.filter(not_(Search.id.startswith("xx")))
elif category in ("hs"):
ids = ids.filter(not_(Search.id.startswith("22")))
elif category in ("course_hedu", "course_sc"):
ids = ids.filter(not_(Search.id.startswith("00")))
if category == "bra":
ids = ids.filter(func.length(Search.id) == 9)
elif category in ("cnae", "hs", "course_hedu"):
ids = ids.filter(func.length(Search.id) == 6)
elif category == ("wld", "course_sc"):
ids = ids.filter(func.length(Search.id) == 5)
elif category == "cnae":
ids = ids.filter(func.length(Search.id) == 4)
ids = ids.order_by(Search.weight.desc()).limit(100).all()
rand = random.randrange(0, len(ids))
id = ids[rand][0]
return redirect(url_for("profiles.profiles", category=category, id=id))
def query(self):
tables = self.left.from_clause + self.right.from_clause
left_lt = self.config.linktab.alias('__left_linktab')
right_lt = self.config.linktab.alias('__right_linktab')
tables += [left_lt, right_lt]
columns = []
score_length = func.greatest(func.length(self.left.key),
func.length(self.right.key))
score_leven = func.levenshtein(self.left.key, self.right.key)
score_leven = cast(score_leven, Float)
score = 1 - (score_leven / score_length)
columns.append(score.label("score"))
for field in self.left.fields:
columns.append(field.column.label(field.column_ref))
for field in self.right.fields:
columns.append(field.column.label(field.column_ref))
q = select(columns=columns, from_obj=tables)
q = self.left.apply_filters(q)
q = self.right.apply_filters(q)
q = q.where(left_lt.c.key == self.left.key)
q = q.where(left_lt.c.view == self.left.name)
q = q.where(right_lt.c.key == self.right.key)
q = q.where(right_lt.c.view == self.right.name)
# TODO: make this levenshteinable
q = q.where(right_lt.c.fingerprint == left_lt.c.fingerprint)
q = q.limit(self.config.cutoff + 1)
q = q.order_by(score.desc())
q = q.distinct()
# print q
return q
def birth_time(cls):
hour = cast(func.extract("hour", cls.birth_datetime), String)
minute = cast(func.extract("minute", cls.birth_datetime), String)
hour = case([(func.length(hour) == 1, "0" + hour)], else_=hour)
minute = case([(func.length(minute) == 1, "0" + minute)], else_=minute)
return hour + ":" + minute
def rename_directory(db, user_id, old_api_path, new_api_path):
"""
Rename a directory.
"""
old_db_path = from_api_dirname(old_api_path)
new_db_path = from_api_dirname(new_api_path)
if old_db_path == '/':
raise RenameRoot('Renaming the root directory is not permitted.')
# Overwriting existing directories is disallowed.
if _dir_exists(db, user_id, new_db_path):
raise DirectoryExists(new_api_path)
# Set this foreign key constraint to deferred so it's not violated
# when we run the first statement to update the name of the directory.
db.execute('SET CONSTRAINTS '
'pgcontents.directories_parent_user_id_fkey DEFERRED')
# Update name column for the directory that's being renamed
db.execute(
directories.update().where(
and_(
directories.c.user_id == user_id,
directories.c.name == old_db_path,
)
).values(
name=new_db_path,
)
)
# Update the name and parent_name of any descendant directories. Do
# this in a single statement so the non-deferrable check constraint
# is satisfied.
db.execute(
directories.update().where(
and_(
directories.c.user_id == user_id,
directories.c.name.startswith(old_db_path),
directories.c.parent_name.startswith(old_db_path),
)
).values(
name=func.concat(
new_db_path,
func.right(directories.c.name, -func.length(old_db_path))
),
parent_name=func.concat(
new_db_path,
func.right(
directories.c.parent_name,
-func.length(old_db_path)
)
),
)
)
def birth_date(cls):
year = cast(cls.year_of_birth, String)
month = cast(cls.month_of_birth, String)
day = cast(cls.day_of_birth, String)
month = case([(month == "", "01")],
else_=case([(func.length(month) == 1, "0" + month)], else_=month))
day = case([(day == "", "01")],
else_=case([(func.length(day) == 1, "0" + day)], else_=day))
return year + "-" + month + "-" + day
def birth_date(cls):
year = cast(cls.year_of_birth, String)
month = cast(cls.month_of_birth, String)
day = cast(cls.day_of_birth, String)
month = case([(month == "", "01")],
else_=case([(func.length(month) == 1, "0" + month)], else_=month))
def delete_duplicate_mb_albums():
"""Identify and delete duplicate albums
Only delete those albums labeled by musicbrainz. We always keep
the biggest (in bytes) complete album songs / features are
preserved in offline directory. If the track times are sufficiently different from the
published track times, we skip and recommend user delete by hand. This is to avoid deleting
a good import while leaving behind an erroneous import. See the Track class in model.py
"""
#cannot figure out how to do this without a select :
s = select([album.c.mb_id, func.count(album.c.mb_id)]).group_by(
album.c.mb_id).having(func.count(album.c.mb_id) > 1)
dupes = session.execute(s).fetchall()
tt_std = 200. #hand set in matcher. But not so important..
import pg
dbmb = pg.connect('musicbrainz_db',
user=config.DEF_DBUSER,
passwd=config.DEF_DBPASS,
host=config.DEF_DBHOST)
for [mb_id, count] in dupes:
if len(mb_id.strip()) < 10:
continue
dupealbums = Album.query.filter(
func.length(Album.mb_id) > 10).filter_by(mb_id=mb_id)
#look up track times. This requires two queries. One to translate the mb_id (their published text key)
#into an mb_numeric_id (their internal key). Then the query against the mb_numeric_id
mb_numeric_id = dbmb.query(
"SELECT R.id FROM album as R, albummeta as AM WHERE R.gid = '%s' AND AM.id = R.id"
% mb_id).getresult()[0][0]
q = """SELECT T.length FROM track as T INNER JOIN albumjoin as AJ ON T.id = AJ.track
INNER JOIN artist as A ON T.artist = A.id WHERE AJ.album = %i ORDER BY AJ.sequence""" % mb_numeric_id
mbtrackresult = numpy.array(dbmb.query(q).getresult())
mbtimes = numpy.array(mbtrackresult[:, ]).flatten() / 1000.
bytes = list()
timeterms = list()
for a in dupealbums:
ttimes = numpy.array(map(lambda t: t.secs, a.tracks))
# df=abs(ttimes-mbtimes)
time_term = numpy.mean(
numpy.exp(-(mbtimes / 1000.0 - ttimes / 1000.0)**2 / tt_std))
currbytes = 0
for t in a.tracks:
currbytes += t.bytes
bytes.append(currbytes)
timeterms.append(time_term)
keepidx = numpy.argmax(numpy.array(bytes))
if timeterms[keepidx] < .9:
print 'Not deleting', dupealbums[
keepidx], 'because the time match is not very good. Do so by hand!'
print ' Times to delete:', numpy.array(
map(lambda t: t.secs, dupealbums[keepidx].tracks))
print ' Times from MBrZ:', mbtimes
else:
for (idx, a) in enumerate(dupealbums):
if idx <> keepidx:
print 'Deleting', a, timeterms[idx]
delete_album(a)
dbmb.close()
def __init__(self):
self._attrs_list_ybs = None
self._attrs_list_yb = None
self.max_year_query_ybs = db.session.query(func.max(Ybs.year))
self.attrs_query_ybs = db.session.query(
func.sum(Ybs.stat_val).label("stat_val"), Ybs.stat_id).filter(
func.length(Ybs.bra_id) == 1,
Ybs.year == self.max_year_query_ybs).group_by(Ybs.stat_id)
self.max_year_query_yb = db.session.query(func.max(Yb.year))
self.attrs_query_yb = db.session.query(
func.sum(Yb.population).label("population")).filter(
func.length(Yb.bra_id) == 1, Yb.year == self.max_year_query_yb)
def __init__(self, bra_id):
Location.__init__(self, bra_id)
self.max_year_query = db.session.query(func.max(
Yb.year)).filter(Yb.bra_id == bra_id)
self.attrs_query = Yb.query.filter(
Yb.year == self.max_year_query,
func.length(Yb.bra_id) == len(self.bra_id))
def _test_autoincrement(self, bind):
aitable = self.tables.aitable
ids = set()
rs = bind.execute(aitable.insert(), int1=1)
last = rs.inserted_primary_key[0]
self.assert_(last)
self.assert_(last not in ids)
ids.add(last)
rs = bind.execute(aitable.insert(), str1="row 2")
last = rs.inserted_primary_key[0]
self.assert_(last)
self.assert_(last not in ids)
ids.add(last)
rs = bind.execute(aitable.insert(), int1=3, str1="row 3")
last = rs.inserted_primary_key[0]
self.assert_(last)
self.assert_(last not in ids)
ids.add(last)
rs = bind.execute(aitable.insert(values={"int1": func.length("four")}))
last = rs.inserted_primary_key[0]
self.assert_(last)
self.assert_(last not in ids)
ids.add(last)
eq_(ids, set([1, 2, 3, 4]))
eq_(
list(bind.execute(aitable.select().order_by(aitable.c.id))),
[(1, 1, None), (2, None, "row 2"), (3, 3, "row 3"), (4, 4, None)],
)
def get_strongest_password(self):
"""Returns strongest person password. Information is fetched from table Login."""
session = self.__get_session()
have_lowercase_letter = session.query(Login.password.label('password'), literal(1).label('points'))\
.distinct(Login.password) \
.filter(func.upper(Login.password) != Login.password)
have_uppercase_letter = session.query(Login.password.label('password'), literal(2).label('points')) \
.distinct(Login.password) \
.filter(func.lower(Login.password) != Login.password)
have_number = session.query(Login.password.label('password'), literal(1).label('points')) \
.distinct(Login.password) \
.filter(Login.password.regexp('.*[0-9].*'))
have_8_letters_or_more = session.query(Login.password.label('password'), literal(5).label('points')) \
.distinct(Login.password) \
.filter(func.length(Login.password) >= 8)
have_special_character = session.query(Login.password.label('password'), literal(3).label('points')) \
.distinct(Login.password) \
.filter(Login.password.regexp('.*[|\^&+\-%*/=!>\%~@#$():;".,/+[\]{}].*'))
points = have_lowercase_letter.union_all(
have_uppercase_letter, have_number, have_8_letters_or_more,
have_special_character).subquery()
passwords_points = session.query(points.c.password)\
.select_from(points)\
.group_by(points.c.password)\
.order_by(desc(func.sum(points.c.points)))\
.limit(1)
return self.__get_query_result(passwords_points)
def cmd(send, msg, args):
"""Returns a random line from $nick.
Syntax: {command} (--channel <channel>) (nick)
"""
parser = arguments.ArgParser(args['config'])
parser.add_argument('--channel', action=arguments.ChanParser)
parser.add_argument('nick', nargs='*')
try:
cmdargs = parser.parse_args(msg)
except arguments.ArgumentException as e:
send(str(e))
return
quote = args['db'].query(Log.msg, Log.source)
nick = ' '.join(cmdargs.nick) if cmdargs.nick else ""
if nick:
quote = quote.filter(Log.source == nick)
else:
quote = quote.filter(Log.source != args['botnick'])
target = cmdargs.channels[0] if hasattr(
cmdargs, 'channels') else args['config']['core']['channel']
quote = quote.filter(
or_(Log.type == 'pubmsg', Log.type == 'privmsg', Log.type == 'action'),
Log.target == target,
func.length(Log.msg) > 5).order_by(func.random()).first()
if quote:
send("%s -- %s" % quote)
elif nick:
send("%s isn't very quotable." % nick)
else:
send("Nobody is very quotable :(")
def barData():
try:
results = db.session.query(Fortune500.Symbol,Fortune500.Revenues, Fortune500.Profits,\
Fortune500.Employees, Fortune500.Latitude, Fortune500.Longitude,\
Fortune500.Rank,Fortune500.Title).filter(func.length(Fortune500.Symbol) > 0).limit(10)
ticks = [result[0] for result in results]
revenue = [result[1] for result in results]
profit = [result[2] for result in results]
emp_cnt = [result[3] for result in results]
profitmgn = [(result[2]/result[1] * 100) for result in results]
revenue_pe = [(result[1]/result[3]) for result in results]
profit_pe = [(result[2]/result[3]) for result in results]
# Generate the plot trace
barData = {
"ticks": ticks,
"revenue": revenue,
"profit": profit,
"profitmgn": profitmgn,
"revenue_pe": revenue_pe,
"profit_pe": profit_pe,
"emp_cnt": emp_cnt
}
return jsonify(barData)
except exc.NoResultFound:
abort(404)
def send_survey_link():
update_time = dt.datetime(2016, 9, 27)
session = Session()
result = session.query(User) \
.filter(func.length(func.aes_decrypt(func.from_base64(User.phone), func.substring(User.salt, 1, 16))) < 12) \
.filter(User.phone != 'out') \
.filter(User.active == 1) \
.filter(not_(User.email.op('regexp')(r'._$'))) \
.all()
sms_sender = Message_Sender()
for row in result:
key = row.salt[:16]
crypto = aes.MyCrypto(key)
name = crypto.decodeAES(row.name)
phone = crypto.decodeAES(row.phone)
print name, phone
text = '''(광고) 홈마스터 설문조사참여하고 신세계 상품권 받으세요^^
https://goo.gl/kYNti3
~12.31'''
print sms_sender.send(sender=MAIN_CALL,
mtype='lms',
to=str(phone),
text=str(text))
def fix_countries():
"""Fix countries attributes with no ISO codes"""
from sqlalchemy.sql.expression import func
from .scheme.value import Value, ValueSource
from .app import db
from .countries import lookup
values = list(
db.session.query(Value).filter(~Value.external_object_id.in_(
db.session.query(Value.external_object_id).filter(
Value.type == ValueType.COUNTRY).filter(
func.length(Value.text) == 2))).filter(
Value.type == ValueType.COUNTRY).all())
added = 0
for v in tqdm(values):
new = lookup(v.text)
if new is not None:
added += 1
value = Value(
type=ValueType.COUNTRY,
text=new,
external_object_id=v.external_object_id,
)
db.session.add(value)
for source in v.sources:
value.sources.append(
ValueSource(platform=source.platform,
score_factor=source.score_factor))
db.session.commit()
print("Fixed {} countries out of {}".format(added, len(values)))
def test_insert_returning(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String(128)),
column("description", String(128)),
)
i = insert(table1,
values=dict(name="foo")).returning(table1.c.myid,
table1.c.name)
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES (:name) "
"RETURNING mytable.myid, mytable.name",
)
i = insert(table1, values=dict(name="foo")).returning(table1)
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES (:name) "
"RETURNING mytable.myid, mytable.name, "
"mytable.description",
)
i = insert(table1, values=dict(name="foo")).returning(
func.length(table1.c.name))
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES (:name) "
"RETURNING char_length(mytable.name) AS "
"length_1",
)
def test_update_returning(self):
table1 = table(
'mytable',
column('myid', Integer),
column('name', String(128)),
column('description', String(128)))
u = update(
table1,
values=dict(name='foo')).returning(table1.c.myid, table1.c.name)
self.assert_compile(u,
'UPDATE mytable SET name=:name OUTPUT '
'inserted.myid, inserted.name')
u = update(table1, values=dict(name='foo')).returning(table1)
self.assert_compile(u,
'UPDATE mytable SET name=:name OUTPUT '
'inserted.myid, inserted.name, '
'inserted.description')
u = update(
table1,
values=dict(
name='foo')).returning(table1).where(table1.c.name == 'bar')
self.assert_compile(u,
'UPDATE mytable SET name=:name OUTPUT '
'inserted.myid, inserted.name, '
'inserted.description WHERE mytable.name = '
':name_1')
u = update(table1, values=dict(name='foo'
)).returning(func.length(table1.c.name))
self.assert_compile(u,
'UPDATE mytable SET name=:name OUTPUT '
'LEN(inserted.name) AS length_1')
def test_update_returning(self):
table1 = table(
'mytable',
column('myid', Integer),
column('name', String(128)),
column('description', String(128)))
u = update(
table1,
values=dict(name='foo')).returning(table1.c.myid, table1.c.name)
self.assert_compile(u,
'UPDATE mytable SET name=:name OUTPUT '
'inserted.myid, inserted.name')
u = update(table1, values=dict(name='foo')).returning(table1)
self.assert_compile(u,
'UPDATE mytable SET name=:name OUTPUT '
'inserted.myid, inserted.name, '
'inserted.description')
u = update(
table1,
values=dict(
name='foo')).returning(table1).where(table1.c.name == 'bar')
self.assert_compile(u,
'UPDATE mytable SET name=:name OUTPUT '
'inserted.myid, inserted.name, '
'inserted.description WHERE mytable.name = '
':name_1')
u = update(table1, values=dict(name='foo'
)).returning(func.length(table1.c.name))
self.assert_compile(u,
'UPDATE mytable SET name=:name OUTPUT '
'LEN(inserted.name) AS length_1')
def test_insert_returning(self):
dialect = postgresql.dialect()
table1 = table(
"mytable", column("myid", Integer), column("name", String(128)), column("description", String(128))
)
i = insert(table1, values=dict(name="foo")).returning(table1.c.myid, table1.c.name)
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES " "(%(name)s) RETURNING mytable.myid, " "mytable.name",
dialect=dialect,
)
i = insert(table1, values=dict(name="foo")).returning(table1)
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES "
"(%(name)s) RETURNING mytable.myid, "
"mytable.name, mytable.description",
dialect=dialect,
)
i = insert(table1, values=dict(name="foo")).returning(func.length(table1.c.name))
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES " "(%(name)s) RETURNING length(mytable.name) " "AS length_1",
dialect=dialect,
)
def __init__(self, bra_id):
Location.__init__(self, bra_id)
self.max_year_query = db.session.query(
func.max(Yb.year)).filter(Yb.bra_id == bra_id)
self.attrs_query = Yb.query.filter(
Yb.year == self.max_year_query,
func.length(Yb.bra_id) == len(self.bra_id))
def _test_autoincrement(self, bind):
aitable = self.tables.aitable
ids = set()
rs = bind.execute(aitable.insert(), int1=1)
last = rs.inserted_primary_key[0]
self.assert_(last)
self.assert_(last not in ids)
ids.add(last)
rs = bind.execute(aitable.insert(), str1='row 2')
last = rs.inserted_primary_key[0]
self.assert_(last)
self.assert_(last not in ids)
ids.add(last)
rs = bind.execute(aitable.insert(), int1=3, str1='row 3')
last = rs.inserted_primary_key[0]
self.assert_(last)
self.assert_(last not in ids)
ids.add(last)
rs = bind.execute(aitable.insert(values={'int1': func.length('four')}))
last = rs.inserted_primary_key[0]
self.assert_(last)
self.assert_(last not in ids)
ids.add(last)
eq_(ids, set([1, 2, 3, 4]))
eq_(list(bind.execute(aitable.select().order_by(aitable.c.id))),
[(1, 1, None), (2, None, 'row 2'), (3, 3, 'row 3'), (4, 4, None)])
def test_insert_returning(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String(128)),
column("description", String(128)),
)
i = insert(table1,
values=dict(name="foo")).returning(table1.c.myid,
table1.c.name)
self.assert_compile(
i,
"INSERT INTO mytable (name) OUTPUT "
"inserted.myid, inserted.name VALUES "
"(:name)",
)
i = insert(table1, values=dict(name="foo")).returning(table1)
self.assert_compile(
i,
"INSERT INTO mytable (name) OUTPUT "
"inserted.myid, inserted.name, "
"inserted.description VALUES (:name)",
)
i = insert(table1, values=dict(name="foo")).returning(
func.length(table1.c.name))
self.assert_compile(
i,
"INSERT INTO mytable (name) OUTPUT "
"LEN(inserted.name) AS length_1 VALUES "
"(:name)",
)
def getInfo(admissionNumber):
return db.session.query(ClinicalNotes.infoText, ClinicalNotes.date)\
.select_from(ClinicalNotes)\
.filter(ClinicalNotes.admissionNumber == admissionNumber)\
.filter(func.length(ClinicalNotes.infoText) > 0)\
.order_by(desc(ClinicalNotes.date))\
.first()
def test_update_returning(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String(128)),
column("description", String(128)),
)
u = update(table1, values=dict(name="foo")).returning(
table1.c.myid, table1.c.name
)
self.assert_compile(
u,
"UPDATE mytable SET name=:name RETURNING "
"mytable.myid, mytable.name",
)
u = update(table1, values=dict(name="foo")).returning(table1)
self.assert_compile(
u,
"UPDATE mytable SET name=:name RETURNING "
"mytable.myid, mytable.name, "
"mytable.description",
)
u = update(table1, values=dict(name="foo")).returning(
func.length(table1.c.name)
)
self.assert_compile(
u,
"UPDATE mytable SET name=:name RETURNING "
"char_length(mytable.name) AS length_1",
)
def test_update_returning(self):
dialect = postgresql.dialect()
table1 = table(
'mytable',
column(
'myid', Integer),
column(
'name', String(128)),
column(
'description', String(128)))
u = update(
table1,
values=dict(
name='foo')).returning(
table1.c.myid,
table1.c.name)
self.assert_compile(u,
'UPDATE mytable SET name=%(name)s '
'RETURNING mytable.myid, mytable.name',
dialect=dialect)
u = update(table1, values=dict(name='foo')).returning(table1)
self.assert_compile(u,
'UPDATE mytable SET name=%(name)s '
'RETURNING mytable.myid, mytable.name, '
'mytable.description', dialect=dialect)
u = update(table1, values=dict(name='foo'
)).returning(func.length(table1.c.name))
self.assert_compile(
u,
'UPDATE mytable SET name=%(name)s '
'RETURNING length(mytable.name) AS length_1',
dialect=dialect)
def genotypes_for_records(vcf_id, query):
"""Return all genotypes which would appear on a row in a VCF (determined by
CHROM/POS/REF/ALT) if just one genotype on that row passes the selections in
`query'.
This is used to generate the list of genotypes to be transformed into
vcf.model._Records and then written to a VCF file.
"""
query = _annotate_query_with_types(query, spec(vcf_id))
with tables(db.engine, 'genotypes') as (con, gt):
keyfunc = func.concat(
gt.c.contig, ':', cast(gt.c.position, types.Unicode), '::',
gt.c.reference, '->', gt.c.alternates)
filtered_gts_q = select([keyfunc]).where(gt.c.vcf_id == vcf_id)
filtered_gts_q = _add_filters(filtered_gts_q, gt, query.get('filters'))
filtered_gts_q = _add_range(filtered_gts_q, gt, query.get('range'))
filtered_gts_q = filtered_gts_q.cte('filtered_gts')
records_q = select([gt]).where(
keyfunc.in_(select([filtered_gts_q]))).where(gt.c.vcf_id == vcf_id)
records_q = records_q.order_by(asc(func.length(gt.c.contig)),
asc(gt.c.contig),
asc(gt.c.position),
asc(gt.c.reference),
asc(gt.c.alternates),
asc(gt.c.sample_name))
genotypes = [dict(g) for g in con.execute(records_q).fetchall()]
return genotypes
def _add_ordering(sql_query, table, column_type, column_name, order):
# Special case for this column, which sorts contigs correctly:
if column_name == 'contig':
get_contig_num = cast(
text("SUBSTRING({} FROM '\d+')".format(table.c.contig)),
type_=Integer)
starts_with_chr = (text("SUBSTRING({} FROM '^chr(\d+)')"
.format(table.c.contig)) != literal(''))
starts_with_number = (text("SUBSTRING({} FROM '^\d+')"
.format(table.c.contig)) != literal(''))
# 10000 used here to mean "should be at the end of all the numbers",
# assuming we never hit a chromosome number >= 10000.
contig_num_col = case(
[(starts_with_chr, get_contig_num),
(starts_with_number, get_contig_num)],
else_=literal(10000)
)
contig_len_col = func.length(table.c.contig)
contig_col = table.c.contig
if order == 'desc':
contig_len_col = desc(contig_len_col)
contig_col = desc(contig_col)
return sql_query.order_by(contig_num_col, contig_len_col, contig_col)
sqla_type = vcf_type_to_sqla_type(column_type)
column = cast(table.c[column_name], type_=sqla_type)
column = {'asc': asc(column), 'desc': desc(column)}.get(order)
return sql_query.order_by(column)
def sequence(cls):
"""Required joins: Protein"""
from .protein import Protein
# SQL is 1 based
left = cls.position - 8
sequence = func.substr(
Protein.sequence, greatest(cls.position - 7, 1),
least(15 + least(left, 0),
func.length(Protein.sequence) - left))
left_padding = func.substr('-------', 1, greatest(-left, 0))
right_padding = func.substr(
'-------', 1,
greatest(cls.position + 8 - func.length(Protein.sequence), 0))
return left_padding.concat(sequence).concat(right_padding)
def find(name):
'''
Returns the most specific zone matching `name` or None.
'''
return Zone.query.filter(
Zone.name.in_(Zone._candidates(name))).order_by(
desc(func.length(Zone.name))).first()
def test_update_returning(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String(128)),
column("description", String(128)),
)
u = update(table1,
values=dict(name="foo")).returning(table1.c.myid,
table1.c.name)
self.assert_compile(
u,
"UPDATE mytable SET name=:name RETURNING "
"mytable.myid, mytable.name",
)
u = update(table1, values=dict(name="foo")).returning(table1)
self.assert_compile(
u,
"UPDATE mytable SET name=:name RETURNING "
"mytable.myid, mytable.name, "
"mytable.description",
)
u = update(table1, values=dict(name="foo")).returning(
func.length(table1.c.name))
self.assert_compile(
u,
"UPDATE mytable SET name=:name RETURNING "
"char_length(mytable.name) AS length_1",
)
def search_regions():
options = {}
regions = Region.query\
.filter(and_(
Region.name.like(u'%{0}%'.format(query)),
func.length(Region.id) < 7))
return (regions, options)
def test_insert_returning(self):
dialect = postgresql.dialect()
table1 = table('mytable',
column('myid', Integer),
column('name', String(128)),
column('description', String(128)),
)
i = insert(
table1,
values=dict(
name='foo')).returning(
table1.c.myid,
table1.c.name)
self.assert_compile(i,
'INSERT INTO mytable (name) VALUES '
'(%(name)s) RETURNING mytable.myid, '
'mytable.name', dialect=dialect)
i = insert(table1, values=dict(name='foo')).returning(table1)
self.assert_compile(i,
'INSERT INTO mytable (name) VALUES '
'(%(name)s) RETURNING mytable.myid, '
'mytable.name, mytable.description',
dialect=dialect)
i = insert(table1, values=dict(name='foo'
)).returning(func.length(table1.c.name))
self.assert_compile(i,
'INSERT INTO mytable (name) VALUES '
'(%(name)s) RETURNING length(mytable.name) '
'AS length_1', dialect=dialect)
def test_insert_returning(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String(128)),
column("description", String(128)),
)
i = insert(table1, values=dict(name="foo")).returning(
table1.c.myid, table1.c.name
)
self.assert_compile(
i,
"INSERT INTO mytable (name) OUTPUT "
"inserted.myid, inserted.name VALUES "
"(:name)",
)
i = insert(table1, values=dict(name="foo")).returning(table1)
self.assert_compile(
i,
"INSERT INTO mytable (name) OUTPUT "
"inserted.myid, inserted.name, "
"inserted.description VALUES (:name)",
)
i = insert(table1, values=dict(name="foo")).returning(
func.length(table1.c.name)
)
self.assert_compile(
i,
"INSERT INTO mytable (name) OUTPUT "
"LEN(inserted.name) AS length_1 VALUES "
"(:name)",
)
def search_regions():
options = {}
regions = Region.query\
.filter(and_(
Region.name.like(u'%{0}%'.format(query)),
func.length(Region.id) < 7))
return (regions, options)
def cmd(send, msg, args):
"""Returns a random line from $nick.
Syntax: {command} (--channel <channel>) (nick)
"""
parser = arguments.ArgParser(args['config'])
parser.add_argument('--channel', action=arguments.ChanParser)
parser.add_argument('nick', nargs='*')
try:
cmdargs = parser.parse_args(msg)
except arguments.ArgumentException as e:
send(str(e))
return
quote = args['db'].query(Log.msg, Log.source)
nick = ' '.join(cmdargs.nick) if cmdargs.nick else ""
if nick:
quote = quote.filter(Log.source == nick)
else:
quote = quote.filter(Log.source != args['botnick'])
target = cmdargs.channels[0] if hasattr(cmdargs, 'channels') else args['config']['core']['channel']
quote = quote.filter(or_(Log.type == 'pubmsg', Log.type == 'privmsg', Log.type == 'action'), Log.target == target,
func.length(Log.msg) > 5).order_by(func.random()).first()
if quote:
send("%s -- %s" % quote)
elif nick:
send("%s isn't very quotable." % nick)
else:
send("Nobody is very quotable :(")
def find_matches(dataset, text, filter=None, exclude=None):
entities = Entity.__table__
match_text = normalize(text, dataset)[:254]
# select text column and apply necesary transformations
text_field = entities.c.name
if dataset.normalize_text:
text_field = entities.c.normalized
if dataset.ignore_case:
text_field = func.lower(text_field)
text_field = func.left(text_field, 254)
# calculate the difference percentage
l = func.greatest(1.0, func.least(len(match_text), func.length(text_field)))
score = func.greatest(0.0, ((l - func.levenshtein(text_field, match_text)) / l) * 100.0)
score = func.max(score).label('score')
# coalesce the canonical identifier
id_ = func.coalesce(entities.c.canonical_id, entities.c.id).label('id')
# apply filters
filters = [entities.c.dataset_id==dataset.id,
entities.c.invalid==False]
if not dataset.match_aliases:
filters.append(entities.c.canonical_id==None)
if exclude is not None:
filters.append(entities.c.id!=exclude)
if filter is not None:
filters.append(text_field.ilike('%%%s%%' % filter))
q = select([id_, score], and_(*filters), [entities],
group_by=[id_], order_by=[score.desc()])
return Matches(q)
def _test_autoincrement(self, bind):
ids = set()
rs = bind.execute(aitable.insert(), int1=1)
last = rs.last_inserted_ids()[0]
self.assert_(last)
self.assert_(last not in ids)
ids.add(last)
rs = bind.execute(aitable.insert(), str1='row 2')
last = rs.last_inserted_ids()[0]
self.assert_(last)
self.assert_(last not in ids)
ids.add(last)
rs = bind.execute(aitable.insert(), int1=3, str1='row 3')
last = rs.last_inserted_ids()[0]
self.assert_(last)
self.assert_(last not in ids)
ids.add(last)
rs = bind.execute(aitable.insert(values={'int1': func.length('four')}))
last = rs.last_inserted_ids()[0]
self.assert_(last)
self.assert_(last not in ids)
ids.add(last)
eq_(list(bind.execute(aitable.select().order_by(aitable.c.id))),
[(1, 1, None), (2, None, 'row 2'), (3, 3, 'row 3'), (4, 4, None)])
def legislator_of(cls, region_id, assembly_id=None):
if not region_id:
return None
if not assembly_id:
assembly_id = current_parliament_id('assembly')
region = Region.query.filter_by(id=region_id).one()
original_region = region
legislator = None
while not legislator and region:
legislators = region.candidates\
.filter(Candidacy.assembly_id == assembly_id)\
.filter_by(is_elected=True)
try:
legislator = legislators.one()
except MultipleResultsFound as e:
legislator = guess_legislator(legislators, original_region,
assembly_id)
break
except NoResultFound as e:
region = region.parents.order_by(False)\
.order_by(func.length(Region.id).desc())\
.first()
return legislator
def test_insert_returning(self):
dialect = postgresql.dialect()
table1 = table(
'mytable',
column('myid', Integer),
column('name', String(128)),
column('description', String(128)),
)
i = insert(table1,
values=dict(name='foo')).returning(table1.c.myid,
table1.c.name)
self.assert_compile(i, 'INSERT INTO mytable (name) VALUES '
'(%(name)s) RETURNING mytable.myid, '
'mytable.name',
dialect=dialect)
i = insert(table1, values=dict(name='foo')).returning(table1)
self.assert_compile(i, 'INSERT INTO mytable (name) VALUES '
'(%(name)s) RETURNING mytable.myid, '
'mytable.name, mytable.description',
dialect=dialect)
i = insert(table1, values=dict(name='foo')).returning(
func.length(table1.c.name))
self.assert_compile(i, 'INSERT INTO mytable (name) VALUES '
'(%(name)s) RETURNING length(mytable.name) '
'AS length_1',
dialect=dialect)
def aset_kibd_act(self):
ses = self.request.session
req = self.request
params = req.params
url_dict = req.matchdict
pk_id = 'id' in params and int(params['id']) or 0
if url_dict['act']=='grid':
# defining columns
columns = []
columns.append(ColumnDT('id'))
columns.append(ColumnDT('units.kode'))
columns.append(ColumnDT('units.nama'))
columns.append(ColumnDT('kats.kode'))
columns.append(ColumnDT('no_register'))
#columns.append(ColumnDT('uraian'))
columns.append(ColumnDT('kats.uraian'))
#columns.append(ColumnDT('tahun'))
columns.append(ColumnDT('tgl_perolehan', filter=self._DTstrftime))
columns.append(ColumnDT('th_beli'))
columns.append(ColumnDT('harga'))
columns.append(ColumnDT('kondisi'))
query = DBSession.query(AsetKib).\
join(AsetKategori, Unit).\
filter(AsetKib.unit_id == Unit.id,
#AsetKib.unit_id == ses['unit_id'],
AsetKib.kategori_id==AsetKategori.id,
AsetKib.kib=='D',
func.substr(Unit.kode,1,func.length(ses['unit_kd']))==ses['unit_kd'],
or_(AsetKib.disabled=='0',AsetKib.disabled==None))
rowTable = DataTables(req, AsetKib, query, columns)
return rowTable.output_result()
def number_of_locations(self, bra_length):
if bra_length == 1 or bra_length == 3:
bra_query = db.session.query(func.count(Bra.id).label("total")).filter(
func.length(Bra.id) == bra_length)
elif bra_length == 7:
bra_query = db.session.query(func.count(Bra.id).label("total")).filter(
Bra.id.like(self.bra_id[:5] + '%'),
func.length(Bra.id) == bra_length)
else:
bra_query = db.session.query(func.count(Bra.id).label("total")).filter(
Bra.id.like(self.bra_id[:3] + '%'),
func.length(Bra.id) == bra_length)
bra = bra_query.first()
return bra.total
def test_insert_returning(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String(128)),
column("description", String(128)),
)
i = insert(table1, values=dict(name="foo")).returning(
table1.c.myid, table1.c.name
)
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES (:name) "
"RETURNING mytable.myid, mytable.name",
)
i = insert(table1, values=dict(name="foo")).returning(table1)
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES (:name) "
"RETURNING mytable.myid, mytable.name, "
"mytable.description",
)
i = insert(table1, values=dict(name="foo")).returning(
func.length(table1.c.name)
)
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES (:name) "
"RETURNING char_length(mytable.name) AS "
"length_1",
)
def get_instance(self, db):
"""
Decompresses the instance blob if necessary and returns it as string.
EDACC can store compressed and uncompressed instances. To distinguish
between them, we prepend the ASCII characters "LZMA" to a compressed instance.
"""
table = db.metadata.tables['Instances']
c_instance = table.c['instance']
c_id = table.c['idInstance']
# get prefix
instance_header = db.session.connection().execute(select([func.substring(c_instance, 1, 4)],
c_id == self.idInstance).select_from(
table)).first()[0]
data_length = db.session.connection().execute(select([func.length(c_instance)],
c_id == self.idInstance).select_from(
table)).first()[0]
if data_length > 32 * 1024 * 1024:
return "Instance too large for processing. Please use the EDACC GUI application."
if instance_header == 'LZMA': # compressed instance?
# get blob without LZMA prefix
instance_blob = db.session.connection().execute(select([func.substring(c_instance, 5)],
c_id == self.idInstance).select_from(
table)).first()[0]
return utils.lzma_decompress(instance_blob)
else:
return self.instance
def year_lengths(cls):
"""Get the total length of the articles, grouped by year.
Returns: OrderedDict of (year, length)
"""
return OrderedDict(
session.query(cls.year, func.sum(func.length(cls.text))).group_by(
cls.year).order_by(cls.year).all())
def __init__(self, bra_id, stat_id):
Location.__init__(self, bra_id)
self.stat_id = stat_id
self.attrs_query = Ybs.query.filter(
Ybs.stat_id == self.stat_id,
Ybs.bra_id.like(self.bra_id[:3] + '%'),
Ybs.year == self.max_year_query,
func.length(Ybs.bra_id) == len(self.bra_id))
def states_in_a_region(self):
bra_query = Bra.query.filter(Bra.id.like(self.bra_id + '%'),
func.length(Bra.id) == 3)
bra = bra_query.all()
states = []
for b in bra:
states.append(b.name())
return states
def __init__(self, bra_id, stat_id):
Location.__init__(self, bra_id)
self.stat_id = stat_id
self.attrs_query = Ybs.query.filter(
Ybs.stat_id == self.stat_id,
Ybs.bra_id.like(self.bra_id[:3] + '%'),
Ybs.year == self.max_year_query,
func.length(Ybs.bra_id) == len(self.bra_id))
def task2():
print("Task2()")
d_id = DB.session.query(Employee.dep_id,
func.max(func.length(Employee.name))).first()[0]
d_title = DB.session.query(
Department.title).filter(Department.id == d_id).one()[0]
print(d_title)
print("Task2() completed\n")
def search_typeahead_prefetch_interpro():
"""
Controller returning a small subset of GO terms (the short ones) to be used as the prefetched data for typeahead.js
:param term: partial search term
:return: JSON object compatible with typeahead.js
"""
interpro = Interpro.query.filter(
func.length(Interpro.description) < 7).order_by(
func.length(Interpro.description)).all()
return Response(json.dumps([{
'value': i.description,
'tokens': i.description.split() + [i.label],
'label': i.label
} for i in interpro]),
mimetype='application/json')
def __init__(self):
self._attrs_list_ybs = None
self._attrs_list_yb = None
self.max_year_query_ybs = db.session.query(
func.max(Ybs.year))
self.attrs_query_ybs = db.session.query(func.sum(Ybs.stat_val).label("stat_val"), Ybs.stat_id).filter(
func.length(Ybs.bra_id) == 1,
Ybs.year == self.max_year_query_ybs).group_by(Ybs.stat_id)
self.max_year_query_yb = db.session.query(
func.max(Yb.year))
self.attrs_query_yb = db.session.query(func.sum(Yb.population).label("population")).filter(
func.length(Yb.bra_id) == 1,
Yb.year == self.max_year_query_yb)
def states_in_a_region(self):
bra_query = Bra.query.filter(Bra.id.like(self.bra_id + '%'),
func.length(Bra.id) == 3)
bra = bra_query.all()
states = []
for b in bra:
states.append(b.name())
return states
def get_chat_filters(chat_id):
try:
return (SESSION.query(CustomFilters).filter(
CustomFilters.chat_id == str(chat_id)).order_by(
func.length(CustomFilters.keyword).desc()).order_by(
CustomFilters.keyword.asc()).all())
finally:
SESSION.close()
def uniqueDirectories(self):
"""Returns the abs_path, checked state and flags value for each of the directories, ordered by length(abs_path) ASC"""
query = self.session.query(
FileSystemMerge.abs_path, FileSystemMerge.checked,
FileSystemMerge.flags).filter(
FileSystemMerge.is_dir == True).order_by(
func.length(FileSystemMerge.abs_path))
for result in query:
yield result.abs_path, result.checked, result.flags
def contigs(vcf_id):
"""Return a sorted list of contig names found in the given vcf."""
with tables(db.engine, 'genotypes') as (con, genotypes):
q = (select([genotypes.c.contig])
.where(genotypes.c.vcf_id == vcf_id)
.group_by(genotypes.c.contig)
.order_by(func.length(genotypes.c.contig), genotypes.c.contig))
results = con.execute(q).fetchall()
return [contig for (contig,) in results]
def __init__(self, bra_id):
LocationSchool.__init__(self, bra_id)
self.sc_query = db.session.query(
func.sum(Ybsc.enrolled).label("enrolled"),
Course_sc).join(Course_sc).filter(
Ybsc.bra_id == self.bra_id,
func.length(Ybsc.course_sc_id) == 5,
not_(Ybsc.course_sc_id.like('xx%')),
Ybsc.year == self.max_year_query).group_by(Ybsc.course_sc_id)
def __init__(self, bra_id):
LocationSchool.__init__(self, bra_id)
self.sc_query = db.session.query(
func.sum(Ybsc.enrolled).label("enrolled"),
Course_sc).join(Course_sc).filter(
Ybsc.bra_id == self.bra_id,
func.length(Ybsc.course_sc_id) == 5,
not_(Ybsc.course_sc_id.like('xx%')),
Ybsc.year == self.max_year_query).group_by(Ybsc.course_sc_id)
def admin_home():
active_stations = Station.query.filter_by(online=True).count()
data_points_count = Data.query.count()
most_active = Station.query.filter(
or_(and_(func.length(Station.data_points) > 0))).first()
data_points = Data.query.order_by(Data.created_at.desc()).limit(10)
return render_template("admin_home.jinja", active_stations=active_stations,
total_data_count=data_points_count,
most_data=most_active.name,
data_points=data_points)
def rename_directory(db, user_id, old_api_path, new_api_path):
"""
Rename a directory.
"""
old_db_path = from_api_dirname(old_api_path)
new_db_path = from_api_dirname(new_api_path)
if old_db_path == '/':
raise RenameRoot('Renaming the root directory is not permitted.')
# Overwriting existing directories is disallowed.
if _dir_exists(db, user_id, new_db_path):
raise DirectoryExists(new_api_path)
# Set this foreign key constraint to deferred so it's not violated
# when we run the first statement to update the name of the directory.
db.execute('SET CONSTRAINTS '
'pgcontents.directories_parent_user_id_fkey DEFERRED')
# Update name column for the directory that's being renamed
db.execute(directories.update().where(
and_(
directories.c.user_id == user_id,
directories.c.name == old_db_path,
)).values(name=new_db_path, ))
# Update the name and parent_name of any descendant directories. Do
# this in a single statement so the non-deferrable check constraint
# is satisfied.
db.execute(directories.update().where(
and_(
directories.c.user_id == user_id,
directories.c.name.startswith(old_db_path),
directories.c.parent_name.startswith(old_db_path),
)).values(
name=func.concat(
new_db_path,
func.right(directories.c.name, -func.length(old_db_path))),
parent_name=func.concat(
new_db_path,
func.right(directories.c.parent_name,
-func.length(old_db_path))),
))
def get_total_instance_blob_size(self, db):
table = db.metadata.tables['Instances']
c_instance = table.c['instance']
c_id = table.c['idInstance']
instance_ids = [i.idInstance for i in self.get_instances(db)]
instance_sizes = db.session.connection().execute(select([func.length(c_instance)],
c_id.in_(instance_ids)).select_from(
table)).fetchall()
total_size = sum(i[0] for i in instance_sizes or [(0,)])
return total_size
