def bigip(hagroup_id):
#TODO Check other nodes in cluster and update active node if
#primary is no longer primary
sql = "select hagroup.description, ltm.name, version " \
"from ltm left join hagroup on hagroup.ID = hagroup_ID "\
"where hagroup.ID = %s and active_ID = ltm.ID"
result = db.fetchall(sql, hagroup_id)
hostname = result[0]['name']
b = bigip_connect(hostname)
#Verify this LTM is active
state = b.System.Failover.get_failover_state()
if state == "FAILOVER_STATE_STANDBY":
sql = "select hagroup.description, ltm.name, version, ltm.ID " \
"from ltm left join hagroup on hagroup.ID = hagroup_ID "\
"where hagroup.ID = %s and active_ID != ltm.ID"
result = db.fetchall(sql, hagroup_id)
hostname = result[0]['name']
ltm_ID = result[0]['ID']
b = bigip_connect(hostname)
state = b.System.Failover.get_failover_state()
if not state == "FAILOVER_STATE_ACTIVE":
return False
#Update DB with new active status
else:
sql = "update hagroup set active_id = %s where ID=%s"
result = db.insert(sql, (ltm_ID, hagroup_id))
version = result[0]['version']
cluster_name = result[0]['description']
if version.startswith('BIG-IP_v11'):
return V11ltm(bigip_obj=b,
version=11,
cluster_name=cluster_name,
hostname=hostname)
else:
return V10ltm(bigip_obj=b, version=10)
def get_dictations_theme(theme_id: int,
show_all_dictations: bool = False
) -> list[Dictations]:
""" Выводим список диктантов по их темам """
row_dictation = None
if show_all_dictations:
row_dictation = db.fetchall("dictations ",
["id", "themes_id", "dictation"],
wanna_return=tuple,
order="ORDER BY dictation")
elif not show_all_dictations:
row_dictation = db.fetchall("dictations ",
["id", "themes_id", "dictation"],
wanna_return=tuple,
where=f"where themes_id={theme_id}",
order="ORDER BY dictation")
result_dict = {}
for d_id, th_id, dictation in row_dictation:
th_name = themes_module.get_theme(th_id).theme_name
if th_name in result_dict:
result_dict[th_name].append((dictation, d_id))
else:
result_dict[th_name] = [(dictation, d_id)]
dicts = [
Dictations(theme_name=str(key), dicts=list(value))
for key, value in result_dict.items()
]
return dicts
def get_wall_newspaper(chat_id, title):
"""Generate wall newspaper for chat by using ML models"""
pics = db.fetchall('images',
'group_id, image, caption, group_id'.split(', '))
paths = []
k = 0
for p in pics:
if p['group_id'] == chat_id:
fname = f'./toml/{k}.jpg'
caption = p['caption']
k += 1
paths.append({'name': fname, 'desc': caption})
bg = p['bg']
with open(fname, 'wb') as f:
f.write(p['image'])
subprocess.run(
f'python ./ML/cutimage/main.py --image {fname} --background ./backgrounds/{bg}.jpg '
f'--output_path ./output/{fname}',
shell=True)
gen_wns_command = f"python ./ML/paper/main.py --title '{title}' --bg ./ML/paper/gazette.jpg "
for p in paths:
p['name'] = p['name'][:-4] + '.png'
gen_wns_command += f"--images {p['name']} --descs {p['desc']} "
subprocess.run(gen_wns_command, shell=True)
def _load_categories(self):
"""Returns a reference list of expense categories from the database"""
categories = db.fetchall(
"category", "codename name is_base_expense aliases".split())
categories = self._fill_aliases(categories)
return categories
def prices():
query = 'SELECT DISTINCT driver FROM results WHERE year = 2014'
drivers = db.fetchall(query)
driver_prices = [(d[0], scoring.price(d[0])) for d in drivers]
return render_template('prices.html', prices=driver_prices)
def _load_categories(self) -> List[Category]:
"""Возвращает справочник категорий расходов из БД"""
categories = db.fetchall(
"category", "codename name is_base_expense aliases".split()
)
categories = self._fill_aliases(categories)
return categories
async def process_start_command(message: types.Message):
global status
id = str(message.from_user.id)
res = db.fetchall('instructors_instructor',
["telegram_id", "approved", "reg_finish"],
f" where telegram_id='{id}'")
if len(res):
for re in res:
if re['reg_finish'] is None and re['approved'] is None:
await message.reply(
"В прошлый раз вы не закончили регистрацию! http://tripwego.ru/instructors/?teleg_id="
+ id)
return
if re['approved'] is None:
await message.reply(
'Вы были зарегистрированны, но ваш статус не подтвержден, пожалуйста обратитесь в '
'поддержку по номеру телефона +7(985)269-46-64')
return
elif re['approved'] is False:
await message.reply(
'Вас не подтвердили, обратитесть пожалуйста в поддержку +7(985)269-46-64. '
'Возможно оператор запросит ваш индетификационный номер: '
+ id + '.')
return
await message.reply(
"Вы уже были зарегистрированы в нашем боте и сайте.")
return
await message.reply(
"Приветствуем нового инсруктора TripWeGo! Для дальнейшего нашего сотрудничества пройдите по "
"ссылке и зарегистрируйтесь! http://tripwego.ru/instructors/?teleg_id="
+ id)
db.insert_single_value('instructors_instructor',
"telegram_id, created_at, updated_at",
f"{id}, '{datetime.now()}', '{datetime.now()}'")
def get_list_ege_content_lite(
task_id: int) -> Tuple[List[EgeContentLite], int]:
"""
Возвращает список контента и его кол-во по номеру ЕГЭ задания, без выгрузки blob_data
"""
ege_content_row = db.fetchall("ege_content", [
"id", "tasks_id", "content_type", "telegram_file_id", "file_name",
"description", "file_extension"
],
wanna_return=tuple,
where=f"WHERE tasks_id={task_id}")
ege_content_lite = [
EgeContentLite(id=row_id,
task_id=task_id,
content_type=content_type,
telegram_file_id=telegram_file_id,
description=description if description else file_name +
file_extension)
for row_id, tasks_id, content_type, telegram_file_id, file_name,
description, file_extension in ege_content_row
]
content_count = len(ege_content_lite)
return ege_content_lite, content_count
def get(self, eventpin): #players will be linked via an eventpin
sql = "SELECT * FROM players WHERE event_pin=%s"
vals = (eventpin,)
myresult = fetchall(sql, vals)
if myresult is None:
return {'status': 'wrong pin'}, 404
else:
return myresult
def fetch_db_matrices() -> List[Dict[str, Union[np.ndarray, Any]]]:
result = []
temp = db.fetchall('matrices', ['value', 'name', 'coordinates'])
for i in temp:
d = {'value': i['value'],
'name': i['name'],
'coordinates': i['coordinates']}
result.append(d)
return result
async def delete_messages(order_id):
res = db.fetchall("instructors_fortelegrambot", ["msg_id", "teleg_id"],
f" where order_id = '{order_id}'")
for re in res:
try:
await bot.delete_message(chat_id=re["teleg_id"],
message_id=re["msg_id"])
except:
print("Ошибка в удалении сообщений после принятия заявки!")
def is_user_present(user_id: int) -> bool:
"""Функция определения наличия пользователя в системе (БД)"""
table = "users"
columns = ["id"]
filters = [{"id": str(user_id)}]
if len(db.fetchall(table, columns, filters)):
return True
return False
def fetch_db_chunks() -> List[Dict[str, Any]]:
result = []
temp = db.fetchall('chunks', ['value', 'count', 'is_target', 'width', 'length'])
for i in temp:
d = {'is_target': i['is_target'],
'count': i['count'],
'value': i['value'],
'width': i['width'],
'length': i['length']}
result.append(d)
return result
def _add_theme_exist_check(theme_grade: str, theme_name: str) -> None:
""" Проверяет на существование названия темы для определённого класса в БД """
exist_themes = db.fetchall("themes", ["themes_grade_number", "theme_name"],
wanna_return=tuple)
exist_themes = set(exist_themes)
if (theme_grade, theme_name) in exist_themes:
raise exceptions.ExistingEntry(
f"Ошибка!\n"
f"Такая тема для {theme_grade} класса уже есть\n\n"
f"Введите другую тему, или нажмите /cancel")
def get_team(db):
''' Get the user's team number '''
user = get_user()
db.execute("""
select R.onyen, R.name
from roll R, teams T
where T.onyen = R.onyen and
T.number = (select number from teams where onyen = %s)
order by R.name
""",
[user],
)
result = db.fetchall()
return result
def get_ege_tasks() -> List[EgeTask]:
""" Получить список заданий ЕГЭ """
row_ege_tasks = db.fetchall(
"ege", ["id", "tasks", "tasks_name"],
wanna_return=dict,
order="ORDER BY CAST(tasks AS UNSIGNED), tasks")
ege_tasks = [
EgeTask(id=ege_tasks["id"],
task=ege_tasks["tasks"],
task_name=ege_tasks["tasks_name"])
for ege_tasks in row_ege_tasks
]
return ege_tasks
def _add_dictation_exist_check(themes_id: int, dictation: str) -> None:
""" Проверяет на существование названия диктанта для этой темы """
exist_dictation = db.fetchall("dictations", ["themes_id", "dictation"],
wanna_return=tuple)
exist_dictation = set(exist_dictation)
if (themes_id, dictation) in exist_dictation:
logging.info(
f'Trying to add exist dictation: {dictation} in theme: {themes_id}'
)
raise exceptions.ExistingEntry(
f"Ошибка!\n"
f"Такой диктант для этой темы уже существует\n\n"
f"Введите другой диктант, или нажмите /cancel")
def get_students() -> list[Student]:
""" Вывести список студентов """
rows = db.fetchall("students",
["id", "full_name", "grade_number", "description"],
wanna_return=dict,
order="ORDER BY full_name")
students = [
Student(id=row["id"],
fullname=row["full_name"],
grade=row["grade_number"],
description=row["description"]) for row in rows
]
return students
def __init__(self, line_id):
db.execute("""
SELECT * FROM line_items
WHERE line_id = %s;""",
(line_id, ))
result = db.fetchone()
for k, v in result.items():
setattr(self, k, v)
db.execute("""
SELECT line_item_feature_id FROM line_item_features
WHERE line_id = %s;""",
(line_id, ))
self.features = [QuoteLineItemFeature(x['line_item_feature_id']) for x in db.fetchall()]
super().__init__(self.item_no)
def index():
q = request.args.get("q", '')
products = []
if q:
query = ' & '.join(re.split('\s+', q.strip()))
conn = db.get_conn()
products = db.fetchall(
conn, """
select *
from products p
where to_tsvector('english', title) @@ to_tsquery('english', {query})
limit 15
""".format(query=db.quote(query)))
return render_template('index.html', q=q, products=products)
def get_checklist_info(db, user, agenda_type):
""" Get the checklist information for the user.
Return a dictionary with the lecture date (not str) as the key with
a value of an array of each item checked"""
db.execute(
'''
select * from checklist as T1
where onyen=%(onyen)s
and agenda_type = %(agenda_type)s
and NOT EXISTS(select * from checklist as T2
where onyen=%(onyen)s
and T1.item = T2.item
and T1.time < T2.time)
''', dict(onyen=user, agenda_type=agenda_type))
checks = {}
grouping = lambda x: x.lecture_date
for lecture_date, group in itertools.groupby(db.fetchall(), grouping):
checks[lecture_date] = [g.item for g in group if g.checked]
return checks
def give_bonus(date, onyens, db):
""" Give students bonus's for checklist """
reason = f"Checklist worksheet_bonus for {date}"
db.execute(
'''
select onyen
from roll
where onyen in %(onyens)s
and onyen not in (select onyen from worksheet_bonus
where reason = %(reason)s)''',
dict(reason=reason, onyens=tuple(onyens)))
onyens = [rec.onyen for rec in db.fetchall()]
if len(onyens) == 0:
print(f"No one should get a bonus for {reason}")
for onyen in onyens:
set_bonus(onyen, reason) # pylint:disable=no-value-for-parameter
print(f"Set bonus for {onyen}: {reason}")
return onyens
def get_student_themes() -> list[StudentThemes]:
""" Вывести студентов и их темы, в соответствии с классом """
rows = db.fetchall(
"students st", ["st.id", "st.full_name", "th.theme_name"],
wanna_return=tuple,
join_on="left join themes th on th.themes_grade_number=st.grade_number",
order="ORDER BY st.full_name, th.theme_name")
result_dict = {}
for st_id, name, theme in rows:
if st_id in result_dict:
result_dict[st_id].append(theme)
else:
result_dict[st_id] = [name, theme]
students_and_themes = [
StudentThemes(id=key,
student_fullname=value[0],
theme_name=list(value[1:]))
for key, value in result_dict.items()
]
return students_and_themes
def load_w_data_fields(dbo):
tablename_staging = config.settings["db"]["tablename_s_data"]
tablename_working = config.settings["db"]["tablename_w_data_mapping_fields"]
## extract the fields of teh new production tble
fields = mdm.fields.get_fields(dbo)
fields = ['"' + x[0] +'"' for x in fields]
fields_string = ", ".join(fields)
## extract the mapping
mapping = mdm.fields.get_fields_mapping(dbo)
sql = 'drop table if exists %s' % tablename_working
db.execute(dbo, sql)
sql = 'create table %s (%s, primary key (source, id))' % (tablename_working, fields_string)
db.execute(dbo, sql)
sql = 'select json from %s' % tablename_staging
db.execute(dbo, sql)
rows = db.fetchall(dbo)
for row in rows:
doc = json.loads(row[0])
#doc1 = {}.fromkeys(fields)
doc = utils.dict_convert_lower(doc)
doc = utils.dict_rename_keys(doc, mapping)
doc_keys = doc.keys()
doc_fields = ['"' + x +'"' for x in doc_keys]
doc_fields_string = ", ".join(doc_fields)
question_marks = ", ?" * len(doc_keys)
values = tuple(doc.values())
sql_insert = "insert into %s (%s) values (%s)" % (tablename_working, doc_fields_string, question_marks[1:])
db.execute(dbo, sql_insert, values)
## export csv
db.sql2csv(dbo, tablename_working, tablename=tablename_working)
def get_worksheets_submitted(worksheets, onyens, db):
""" Return the subset of onyens that submitted all the workshsets """
db.execute(
'''
with _worksheets as (
select onyen, key, row_number() over
(partition by key order by key) as row_number
from post P, answer A
where A.postid = P.id
and onyen in %(onyens)s
and key in %(worksheets)s
and A.field = '_submit'
and A.value = 'Submit'
and key in ('worksheet-demo', 'worksheet00-pseudo')
)
select onyen, count(key) from _worksheets where row_number=1 group by onyen
''', dict(onyens=tuple(onyens), worksheets=tuple(worksheets)))
onyens = {rec.onyen: rec.count for rec in db.fetchall()}
onyens = [k for k in onyens if onyens[k] == len(worksheets)]
return onyens
def get_themes() -> list[Themes]:
""" Получает список тем """
rows = db.fetchall(
"themes", ["id", "themes_grade_number", "theme_name"],
wanna_return=tuple,
order=
"ORDER BY CAST(themes_grade_number AS UNSIGNED), themes_grade_number,"
"theme_name")
result_dict = {}
for th_id, grade, th_name in rows:
if grade in result_dict:
result_dict[grade].append((th_name, th_id))
else:
result_dict[grade] = [(th_name, th_id)]
themes = [
Themes(themes_grade_number=key, theme_names_ids=list(value))
for key, value in result_dict.items()
]
return themes
def __init__(self, quote_no=None):
self.items = []
self.projectName = "TEST PROJECT"
if not quote_no:
db.execute("""
INSERT INTO quotes(field)
VALUES ('test')
RETURNING quote_no;""")
self.quote_no = str(db.fetchone()['quote_no'])
else:
db.execute("""
SELECT * FROM quotes
WHERE quote_no = %s;""",
(quote_no, ))
for k, v in db.fetchone().items():
setattr(self, k, v)
self.quote_no = str(self.quote_no)
db.execute("""
SELECT * FROM line_items
WHERE quote_no = %s;""",
(self.quote_no,))
self.items = [QuoteLineItem(x['line_id']) for x in db.fetchall()]
def get_onyens_completed_checklist(date, db):
""" Return the onyens that have the checklist date with the
same count as jmajikes
"""
db.execute(
'''
select CL.onyen, count(CL.item)
from checklist as CL,
roll as R
where CL.onyen=R.onyen
and R.section != '000'
and agenda_type = 'student'
and CL.lecture_date = %(date)s
and R.onyen not like 'student%%'
and NOT EXISTS(select * from checklist as CL2
where CL.onyen = CL2.onyen
and checklist = 'student'
and CL.item = CL2.item
and CL.time < CL2.time)
group by CL.onyen''', dict(date=date))
cl_dict = {rec.onyen: rec.count for rec in db.fetchall()}
onyens = [k for k in cl_dict if cl_dict[k] == cl_dict['jmajikes']]
return onyens
def history():
args = flask.request.args
start_datetime = args.get('start_datetime', 'now')
start_offset = args.get('start_offset', '-7 days')
end_datetime = args.get('end_datetime', 'now')
end_offset = args.get('end_offset', '0 days')
rows = db.fetchall('''
select `timestamp`, download, upload, share
from speedtest
where datetime(`timestamp`) >= datetime(?, ?)
and datetime(`timestamp`) <= datetime(?, ?)
''', (start_datetime, start_offset, end_datetime, end_offset))
datetime_strs, downloads, uploads, share_links = zip(*rows)
datetimes = [dateutil.parser.parse(s) for s in datetime_strs]
fig = pgo.Figure()
fig.add_trace(
pgo.Scattergl(
x=datetimes,
y=downloads,
name='Download',
line_color='deepskyblue',
opacity=0.8))
fig.add_trace(
pgo.Scattergl(
x=datetimes,
y=uploads,
name='Upload',
line_color='dimgray',
opacity=0.8))
return flask.Response(fig.to_html())
def _load_categories(self) -> List[Category]:
"""Повертає довідник категорій витрат з БД"""
categories = db.fetchall(
"category", "codename name is_base_expense aliases".split())
categories = self._fill_aliases(categories)
return categories
""" Migrate the database to the latest version and dump the schema to file """
import subprocess
import db
if __name__ == "__main__":
db.migrate("./db/migrations")
dump = subprocess.check_output(
"pg_dump --schema-only --no-owner --no-privileges f1", shell=True
)
schema_migrations = db.fetchall(
"SELECT version FROM schema_migrations ORDER BY version ASC"
)
with open("./db/structure.sql", mode="wb") as f:
f.write(dump)
template = "INSERT INTO schema_migrations (version) VALUES (%s);\n"
for migration in schema_migrations:
f.write(str.encode(template % migration))
def _get_budget_limit() -> int:
"""Повертає денний ліміт витрат для основних базових витрат"""
return db.fetchall("budget", ["daily_limit"])[0]["daily_limit"]
import db
# create conncetion object
conn = db.connect('test.db')
db.version(conn)
# insert statement
query = "INSERT INTO student (name,rollnumber,age) VALUES ('Suresh',1004,29)"
db.insert(conn, query)
# select multiple records
query = "SELECT * from student"
result = db.fetchall(conn, query)
for r in result:
print(r)
# select one record
query = "SELECT * from student where id = 1"
result = db.fetchone(conn, query)
print(result)
# update a record
query = "UPDATE student SET age=30 WHERE id=2"
db.update(conn, query)
# delete a record
query = "DELETE from student WHERE id = 3"
db.delete(conn, query)
# close connection
db.close(conn)
def _load_categories(self) -> List[Category]:
"""Returns all existing categories stored in db"""
categories = db.fetchall(
"categories", ["id", "user_id","name", "max_ammount"]
)
return self._fill_fields(categories)
def available_items(self):
db.execute("""
SELECT item_no FROM items;""")
return [Item(x['item_no']) for x in db.fetchall()]
if group:
sql += " GROUP BY " + group
if order:
get_direction = lambda c: (c if isinstance(c, basestring)
else "DESC" if c else "ASC")
sql += " ORDER BY "
for i, col in enumerate(order):
name = col[0] if isinstance(col, (list, tuple)) else col
direction = "" if name == col else " " + get_direction(col[1])
sql += (", " if i else "") + name + direction
if limit:
sql += " LIMIT %s" % (", ".join(map(str, limit)))
return sql, args
if "__main__" == __name__:
import db
db.init(":memory:", "CREATE TABLE test (id INTEGER PRIMARY KEY, val TEXT)")
print("Inserted ID %s." % db.insert("test", val=None))
for i in range(5): print("Inserted ID %s." % db.insert("test", {"val": i}))
print("Fetch ID 1: %s." % db.fetch("test", id=1))
print("Fetch all up to 3, order by val: %s." % db.fetchall("test", order="val", limit=3))
print("Updated %s row where val is NULL." % db.update("test", {"val": "new"}, val=None))
print("Select where val IN [0, 1, 2]: %s." % db.fetchall("test", val=("IN", range(3))))
print("Delete %s row where val=0." % db.delete("test", val=0))
print("Fetch all, order by val: %s." % db.fetchall("test", order="val"))
db.execute("DROP TABLE test")
db.close()
