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()