def apply_params(self, params): param_type = None param_count = 0 current_count = 0 db = db_connect(db_name) sql = db.cursor().execute for param in params: self.bar.setValue(self.bar.value() + 1) self.status.setText("Applying: %s" % param) if param_type is None or param_count == current_count: param = param.split() param_type = param[0] param_count = int(param[1]) current_count = 0 continue elif param_type == "change": param = param.split('|') columns = [col.split('=') for col in param[2].split(',')] columns = {col[0]: col[1] for col in columns} change_columns(db, param[1], param[0], **columns) elif param_type == "sql": sql(param) db.commit() current_count += 1 db.close()
def __init_database__(*args, **kwargs): s = args[0] if not s.__db_conn__: s.__db_conn__ = db_connect(s.__db_name__) s.__db_conn__.row_factory = lambda cursor, row: \ dict(( (col[0], row[idx]) for idx, col in enumerate(cursor.description) )) s.__db__ = s.__db_conn__.cursor() try: s.__db__.executescript(''' CREATE TABLE subscriptors ( session_id VARCHAR(256) NOT NULL PRIMARY KEY, subscription VARCHAR(512) NOT NULL, group_id INT NOT NULL ); ''') s.__db_conn__.commit() except: pass result = func(*args, **kwargs) s.__db_conn__.close() s.__db_conn__ = None else: result = func(*args, **kwargs) return result
def select_all_downloads(): con = db_connect(db_name) cur = con.cursor() query_select_dwsongs = "SELECT file_id FROM dwsongs" cur.execute(query_select_dwsongs) match = cur.fetchall() con.close() return match
def select_all_banned(): con = db_connect(db_name) cur = con.cursor() query_select_banned = "SELECT chat_id FROM banned" cur.execute(query_select_banned) match = cur.fetchall() con.close() return match
def select_all_users(): con = db_connect(db_name) cur = con.cursor() query_select_users = "SELECT chat_id FROM users_settings" cur.execute(query_select_users) match = cur.fetchall() con.close() return match
def select_dwsongs_top_downloaders(many): con = db_connect(db_name) cur = con.cursor() cur.execute( "SELECT chat_id, COUNT(chat_id) as cnt FROM dwsongs GROUP BY chat_id ORDER BY cnt DESC LIMIT ?", (many, ) ) match = cur.fetchall() con.close() return match
def delete_banned(chat_id): con = db_connect(db_name) cur = con.cursor() query_delete_banned = "DELETE FROM banned WHERE chat_id = ?" cur.execute( query_delete_banned, ( chat_id, ) ) con.commit() con.close()
def write_banned(chat_id): con = db_connect(db_name) cur = con.cursor() query_insert_banned = "INSERT INTO banned(chat_id) VALUES (?)" cur.execute( query_insert_banned, ( chat_id, ) ) con.commit() con.close()
def delete_dwsongs(file_id): con = db_connect(db_name) cur = con.cursor() query_delete_dwsongs = "DELETE FROM dwsongs WHERE file_id = ?" cur.execute( query_delete_dwsongs, ( file_id, ) ) con.commit() con.close()
def select_users_settings_date(c_month, c_year): con = db_connect(db_name) cur = con.cursor() cur.execute( "SELECT date FROM users_settings WHERE strftime('%m', date) = ? AND strftime('%Y', date) = ?", ( c_month, c_year ) ) match = cur.fetchall() con.close() return match
def write_dwsongs(link, file_id, quality, chat_id): con = db_connect(db_name) cur = con.cursor() query_insert_dwsongs = "INSERT INTO dwsongs(link, file_id, quality, chat_id) VALUES (?, ?, ?, ?)" cur.execute( query_insert_dwsongs, ( link, file_id, quality, chat_id ) ) con.commit() con.close()
def select_banned(chat_id): con = db_connect(db_name) cur = con.cursor() query_select_banned = "SELECT chat_id FROM banned WHERE chat_id = ?" cur.execute( query_select_banned, ( chat_id, ) ) match = cur.fetchone() con.close() return match
def select_dwsongs(link, quality): con = db_connect(db_name) cur = con.cursor() query_select_dwsongs = "SELECT file_id FROM dwsongs WHERE link = ? AND quality = ?" cur.execute( query_select_dwsongs, ( link, quality ) ) match = cur.fetchone() con.close() return match
def update_users_settings(user_settings, chat_id): con = db_connect(db_name) cur = con.cursor() query_update_users_settings = ( f"UPDATE users_settings SET {query_update_users_settings_str_input} WHERE chat_id = ?" ) c_settings = [] for c_setting in user_settings: if c_setting in users_settings_columns: c_settings.append(user_settings[c_setting]) c_settings.append(chat_id) cur.execute(query_update_users_settings, c_settings) con.commit() con.close()
def __init__(s, action_callback): if not TaskScheduler.__Sync_timer__: TaskScheduler.__Sync_timer__ = SolidTimer( 60 * 60 * 6, TaskScheduler.Sync_gmttime_by_http) TaskScheduler.__Sync_timer__.start() try_sync_thread = Thread(target=TaskScheduler.__Try_sync_datetime__) try_sync_thread.setDaemon(True) try_sync_thread.start() s.__callback__ = action_callback s.__action_timer__ = SolidTimer(1, TaskScheduler.__check_tasks__, [s]) s.__triggered__ = defaultdict(lambda: False) TaskScheduler.__DB_conn__ = db_connect('tasks.db') TaskScheduler.__DB_conn__.row_factory = s.__db_dict_factory__ s.__db__ = TaskScheduler.__DB_conn__.cursor() try: s.__db__.executescript(''' CREATE TABLE tasks ( id INTEGER NOT NULL PRIMARY KEY, hour INTEGER NOT NULL, minute INTEGER NOT NULL, action TEXT NOT NULL, args TEXT, enabled INTEGER NOT NULL ); CREATE TABLE interval_tasks ( id_task_start INTEGER NOT NULL REFERENCES tasks(id), id_task_end INTEGER NOT NULL REFERENCES tasks(id), PRIMARY KEY(id_task_start, id_task_end) ); ''') TaskScheduler.__DB_conn__.commit() except: pass s.__cache_intervals__ = s.get_interval_tasks() s.__cache_tasks__ = s.get_tasks() s.__action_timer__.start()
def select_users_settings(chat_id): con = db_connect(db_name) cur = con.cursor() query_select_users_settings = ( f"SELECT {query_insert_users_settings_str_fields} FROM users_settings WHERE chat_id = ?" ) cur.execute( query_select_users_settings, ( chat_id, ) ) match = cur.fetchone() con.close() return match
def write_users_settings(chat_id): con = db_connect(db_name) cur = con.cursor() query_insert_users_settings = ( f"INSERT INTO users_settings\ (chat_id, {query_insert_users_settings_str_fields}) \ VALUES (?, {query_insert_users_settings_str_input})" ) c_settings = [chat_id] c_settings += [ c_setting[2] for c_setting in bot_settings_config ] cur.execute(query_insert_users_settings, c_settings) con.commit() con.close()
def initialize_db(): query_create_table_dwsongs = ( "CREATE TABLE IF NOT EXISTS dwsongs(\ id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, \ link VARCHAR(255) NOT NULL, \ file_id VARCHAR(255) UNIQUE NOT NULL, \ quality VARCHAR(5) NOT NULL, \ date DATE DEFAULT (datetime('now', 'localtime')), \ chat_id INT NOT NULL, \ UNIQUE(link, quality))" ) query_create_table_users_settings = ( "CREATE TABLE IF NOT EXISTS users_settings(\ id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, \ chat_id INT UNIQUE NOT NULL, \ quality VARCHAR(5) NOT NULL, \ zips BOOLEAN NOT NULL, \ tracks BOOLEAN NOT NULL, \ lang VARCHAR(5) NOT NULL, \ date DATE DEFAULT (datetime('now', 'localtime')), \ source VARCHAR(8) NOT NULL, \ search_method VARCHAR(15) NOT NULL)" ) query_create_table_banned = ( "CREATE TABLE IF NOT EXISTS banned(\ id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, \ date DATE DEFAULT (datetime('now', 'localtime')), \ chat_id INT UNIQUE NOT NULL)" ) con = db_connect(db_name) cur = con.cursor() cur.execute(query_create_table_dwsongs) cur.execute(query_create_table_users_settings) cur.execute(query_create_table_banned) con.commit() con.close()
def convert(): db = db_connect(db_name) sql = db.cursor().execute pl = '' icons = [0, 1, 3, 4, 5, 2] viewed = 0 try: with open(file_name) as file: for index, t in enumerate(file.readlines()): t = t.rstrip().split('|') if pl != t[2]: pl = t[2] sql("INSERT INTO Playlists VALUES ('%s')" % pl) t[3] = icons[int(t[3])] if t[4] == "viewed": viewed += 1 elif t[4] == 'n' and t[3] == 2: t[4] = 'pause' t[10] = 0 if t[10] == '' else int(t[10]) t[9] = t[9].split('.') t[9].reverse() query = "INSERT INTO Titles VALUES " \ "('%s',%s,%s,'%s','%s','%s','%s','%s','%s','%s','%s',%s,0)" sql(query % (t[0], int(t[1]), index, pl, t[3], t[4], t[5], t[6], t[7], t[8], '.'.join(t[9]), t[10])) index = str(index + 1) sql("UPDATE Data SET value='%s' WHERE name='id'" % index) sql("UPDATE Data SET value='%s' WHERE name='viewed'" % str(viewed)) sql("UPDATE Data SET value='%s' WHERE name='added'" % index) sql('INSERT INTO Data VALUES("cur_pl","0")') db.commit() print("Converted %s titles" % index) except FileNotFoundError: raise FileNotFoundError("Файл '%s' не найден" % file_name) except Exception as e: input(str(e))
def _connection(self): if not self._connection_: uri = 'file:{0}'.format(self._config.DatabaseFile) self._connection_ = db_connect(uri, uri=True) self._connection_.row_factory = db_row return self._connection_