def do_select(self, args): argList = str2List(args) if self.selectDB is not None: selectTablePath = DBMS.findTableAndRecordPath(self.selectDB ,argList)[0] if selectTablePath is not None: DBMS.selectRecords(selectTablePath, argList) else: print '[Select Error] The table does not exist. Plz try "use --help" or "use -h" to get help.' else: print '[Select Error] Choose the DB first. Plz try "use --help" or "use -h" to get help.'
async def process_callback_button1(callback_query: types.CallbackQuery): global product product.category = callback_query.data DBMS.add_product(product) await bot.send_message( callback_query.message.chat.id, f"Товар {product.codename} на сумму {product.price} " f"добавлен в категорию {product.category}.")
def do_login(self, args): password = getpass.getpass() if DBMS.isAdmin(password): self.prompt = 'chrisql>>> ' self.authority = 'admin' print 'you are boss in PySQL!!' elif DBMS.isUser(password): self.authority = 'user_admin' print 'hello, visiter, wellcome to PySQL :)' else: print '[Error] Wrong Password :( Plz try again.'
async def process_message(message: types.Message, state: FSMContext): async with state.proxy() as data: data['text'] = message.text user_message = data['text'] product.category = user_message DBMS.add_category(product) await message.answer( f"Товар {product.codename} на сумму {product.price} " f"добавлен в категорию {product.category}.") await state.finish()
def do_insert(self, args): argList = args.split() if self.authority == 'admin': if self.selectDB is not None: selectTablePath, selectRecordPath = DBMS.findTableAndRecordPath(self.selectDB ,argList) if selectTablePath is not None: if 'values' in argList: argList.remove('values') if DBMS.MatchTableSetting(selectTablePath, argList, 'insert'): DBMS.insertTable(selectTablePath, argList) else: print '[Insert Error] Choose the DB first. Plz try "use --help" or "use -h" to get help.' else: print '[Error] authority not enough.'
def do_delete(self, args): argList = str2List(args) if self.authority == 'admin': if self.selectDB is not None: selectTablePath, selectRecordPath = DBMS.findTableAndRecordPath(self.selectDB ,argList) if selectTablePath is not None: if selectRecordPath is not None: DBMS.deleteRecord(selectRecordPath) else: print '[Delete Error] The record does not exist. Plz try "use --help" or "use -h" to get help.' else: print '[Delete Error] The table does not exist. Plz try "use --help" or "use -h" to get help.' else: print '[Delete Error] Choose the DB first. Plz try "use --help" or "use -h" to get help.' else: print '[Error] authority not enough.'
async def echo_product(message): global product global raw_message raw_message = message.text try: product = DBMS.product_exist(raw_message) except Exception as e: print("Возникла ошибка: ", e) await message.answer( f"Что-то пошло не так... Вводите доходы/расходы в виде НАЗВАНИЕ - СТОИМОСТЬ." ) else: if product.category: await message.answer( f"Товар {product.codename} на сумму {product.price} " f"добавлен в категорию {product.category}.") else: # Клавиатура с выбором. keyboard = InlineKeyboardMarkup(row_width=2) deposit = InlineKeyboardButton("Доход", callback_data="Доход") cost = InlineKeyboardButton("Расход", callback_data="Расход") keyboard.add(deposit, cost) # Далее обработка ответа. await message.answer('Это доход или расход', reply_markup=keyboard)
def main(): db = DBMS.DBMS() mail = Mail.Mail() while True: print("\n\n\n\n\n") multi = -1 while multi < 0 or multi > 5: multi = eval( input("0. Exit.\n" "1. Update from Database.\n" "2. Update from Form.\n" "3. Check Memberships.\n" "4. Display.\n" "5. Send Mails")) if multi == 1: db.update_from_db() elif multi == 2: db.update_from_form() elif multi == 3: db.membership() elif multi == 4: choice = -1 while choice != 2 and choice != 1: choice = eval(input("\n1. Individual.\n2. Group.")) db.membership(choice) elif multi == 5: mail.send_mail() else: return
def finder(conditions): DBMS.connect(Configuration.db_path) log_type = Configuration.doc['input'][conditions['logType']] if 'related' in log_type: pass types = DBMS.getTable(conditions['logType']) for i in range(0, len(types)): types[i] = types[i][1] # 构造的命令由两部分组成: # 1.有占位符的sql查询语句 # 2.括号内占位符所代表的变量名 command = 'SELECT * FROM ' + conditions['logType'] keys = list(conditions) data = list(conditions.values()) # 构造查询语句 # 判断是否为第一个 first = True for i in range(0, len(keys)): # 如果当前key上的value为None,跳过 if data[i] is None or data[i] == "": continue # 跳过名字 if not i: continue # 第一个出现的特殊条件用where作为前缀 if i != 0 and first is True: command += ' WHERE ' + keys[i] + " = '" + data[i] + "'" first = False continue # 其他所有出现的特殊条件都用and作为前缀 command += ' AND ' + keys[i] + " = '" + data[i] + "'" write_msg(command) lists = DBMS.search(command) result = [] for item in lists: # 字典key和value匹配 pack = dict(zip(types, item)) result.append(pack) bbb = DBMS.search_mis_clt(pack["pid"], pack.get("SendToHost","")) if bbb: result.append(dict(zip(types, bbb))) write_msg("NOTICE: Search complete, " + str(len(result)) + " eligible items") DBMS.disconnect() return result
def do_show(self, args): #show all DB/tables argList = str2List(args) if args == 'databases' or args == 'DATABASES': print "###################" for DB in os.listdir('./DB'): print DB print "###################" elif args == 'tables' or args == 'TABLES': print "###################" for table in os.listdir('./DB/default'): print table print "###################" elif argList[1] == 'columns' or argList[1] == 'COLUMNS': print "###################" if self.selectDB is not None: selectTablePath = DBMS.findTableAndRecordPath(self.selectDB, argList)[0] Column2IndexDict = DBMS.getAllColumn2IndexDict(selectTablePath) for column in Column2IndexDict.keys(): print column print "###################"
async def process_callback_button1(callback_query: types.CallbackQuery): global raw_message try: deposit = DBMS.add_deposit(raw_message) except Exception as e: await bot.send_message(callback_query.message.chat.id, f"Что-то пошло не так: {e}") else: await bot.send_message( callback_query.message.chat.id, f"Депозит от {deposit.name} " f"на сумму {deposit.money} был добавлен.")
def do_set(self, args): if self.authority == 'admin': match = re.search('attribute\s*\w*', args) if match is None: if re.search('primary\s*key\s*\w*', args): argList = str2List(args) columnName = argList.pop() tableConfContent, tablePrimaryKeyIndex = File.findContentAndPrimaryKeyIndex(self.tableConf, self.tableConfPath, columnName) if tablePrimaryKeyIndex >= 0: File.setPrimaryKeyColumn(self.tableConf, self.tableConfPath, tableConfContent, tablePrimaryKeyIndex) self.primaryKeyExist = True elif tablePrimaryKeyIndex == -999: print '[Setting Error] Primary key already set.' else: print '[Setting Error] No such column for primary key setting, try "set --help" or "set -h" to get help.' else: print '[Syntax Error] try "set --help" or "set -h" to get help.' else: tableConfContent = File.findContentAndPrimaryKeyIndex(None, self.tableConfPath, '')[0] DBMS.setRelation(match, str2List(args), self.tableConfPath, tableConfContent) else: print '[Error] authority not enough.'
async def process_callback_button1(callback_query: types.CallbackQuery): global product # Известные категории из БД. markup = InlineKeyboardMarkup(row_width=1) for items in DBMS.all_categories(): item = InlineKeyboardButton(items, callback_data=items) markup.add(item) # Выбор новой. item = InlineKeyboardButton('Новая категория', callback_data="new") markup.add(item) await bot.send_message(callback_query.message.chat.id, 'Выбери категорию:', reply_markup=markup)
def main(): parser = OptionParser(usage="usage: %prog [options] ", version="%prog 1.0") parser.add_option("-i", "--init", action="store_true", dest="init", default=False, help=u"初始化数据库") parser.add_option("-s", "--string", action="store", dest="search_string", default="", help=u"搜索字符串") (options, args) = parser.parse_args() if options.init: init() exit(0) Configuration.load('debug.yml') DBMS.create_table(Configuration.db_path) Sensor.sensor('20170225') Spider.spider(file_date='20170225', log_type='postran') conditions = parse_search_string(options.search_string) conditions["logType"] = 'postran' finder_result = Finder.finder(conditions) #finder_result = Finder.finder({'logType': 'postran', 'FileDate': '20140715', 'rrn': '141960021923', 'amount': '', # 'MrchId': ''}) Filter.filter_by_call(finder_result)
def sensor(file_date=None): """ 根据fileDate 检测系统中指定配置目录下的日志文件 如fileDate 为None,则检测所有文件 """ if not DBMS.connect(Configuration.db_path): return False for item in file_names(Configuration.log_path): portion = os.path.splitext(item) # 检测到配置目录下指定日期的文件 if file_date: if portion[1] == '.' + file_date: if DBMS.skip(portion[0], portion[1].replace('.', "")): continue DBMS.insert_dict_into_sql('sign', [{'logType': portion[0], 'FileDate': portion[1].replace('.', ""), 'Status': False}]) # 日期为缺省默认处理所有文件 else: if DBMS.skip(portion[0], portion[1].replace('.', "")): continue DBMS.insert_dict_into_sql('sign', [{'logType': portion[0], 'FileDate': portion[1].replace('.', ""), 'Status': False}]) DBMS.disconnect() return True
def spider(file_date=None, log_type=Configuration.logType): ''' TODO: 用Configuration.logType 作参数不合适 ''' if not DBMS.connect(Configuration.db_path): return False # 将文件名拆成名字和后缀 file_list = [] if isinstance(log_type, str): print(log_type, Configuration.logType) if log_type not in Configuration.logType: return False log_type = [log_type] for t in log_type: if file_date: if os.path.isfile(Configuration.log_path + t + '/' + t + '.' + file_date): Dealer.classification(Configuration.log_path + t + '/' + t + '.' + file_date) file_list.append((t, '.' + file_date)) else: print "ERROR: file " + Configuration.log_path + t + '/' + t + '.' + file_date + " don't exist." # 关键字提取 result = None for name in file_list: if name[0] in Configuration.doc[ 'input'] and 'type' in Configuration.doc['input'][name[0]]: if name[0] == 'mis_clt': result = unpacking_general.classifying_mis("".join( tuple(name))) else: result = unpacking_general.classifying("".join(tuple(name))) # 可以返回json或则入库 DBMS.delete_table(name[0]) DBMS.insert_dict_into_sql(name[0], result) DBMS.disconnect() return True
sql_ex_stats_query = """ SELECT "Имеется:", NULL, NULL, SUM(int_price) || " грн.", SUM(cat_price) || " грн.", COUNT(*) || " шт." FROM (%s) WHERE existence = '+' """ % sql_data_query coins_data = cursor.execute(sql_data_query).fetchall() all_stats = cursor.execute(sql_stats_query).fetchall() exis_stats = cursor.execute(sql_ex_stats_query).fetchall() headers = [ "Дата выпуска", "Краткое название", "Тираж", "Цена", "Каталог", "Наличие" ] DBMS.print_table(data=coins_data + exis_stats + all_stats, headers=headers, tablefmt="pretty") ''' with sqlite3.connect(os.path.join(DBMS.ARCHIVE, DBMS.prices_database)) as connection: connection.create_function("CONVERT", 1, convert) cursor = connection.cursor() # SUBSTR(day, 1, 7) sql_query = """ SELECT day, SUM(price) FROM prices WHERE release_date LIKE '%2016' GROUP BY day ORDER BY 1 """
ticks.append(i) labels.append(curr_year) elif months and curr_month != month and (not years or int(month) != 1): curr_month = month ticks.append(i) labels.append(rus_month[int(curr_month) - 1][:3] + '.') elif days and curr_day != day and (not months or int(day) != 1): curr_day = day ticks.append(i) labels.append(str(int(curr_day))) return ticks, labels coin_title = "_" dates, price = DBMS.get_dynamics("2021", coin_title, min_date="2017-06-01") ''' dates, price = [], [] with sqlite3.connect(os.path.join(DBMS.ARCHIVE, DBMS.prices_database)) as connection: cursor = connection.cursor() # SUBSTR(day, 1, 7) sql_query = """ SELECT day, SUM(price) FROM prices WHERE release_date LIKE '%2007' and day > '2009-01-01' GROUP BY day ORDER BY 1 """ data = cursor.execute(sql_query).fetchall()
def update_prices(update_dynamics: bool=False, show_process: bool=True, replace_current: bool=False, add_new: bool=True) -> None: ''' Обновление цен всех монет из базы данных ''' ''' Если сегодня обновляли ''' if not replace_current and os.path.exists(os.path.join(DBMS.ARCHIVE, DBMS.coins_database)): print("Цены на момент %s уже обновлены" % DBMS.today) return ''' Если папка отсутсвует, создаём ''' if not os.path.exists(DBMS.DIRECTORY): os.mkdir(DBMS.DIRECTORY) ''' Запрос страницы со списком недрагоценных монет ''' req = requests.get(not_precious, headers=header) ''' При удачном запросе ''' if req: soup = BeautifulSoup(req.text, 'lxml') ''' Обновляем цену каждой монеты из списка ''' for coin in soup.table.find_all(['tr'])[1:-1]: if len(coin.find_all(['td'])) == 5 and coin.a.text: ''' Парсим название и год выпуска монеты ''' title = utiles.title_formating(coin.a.text) year = coin.td.text[-4:] path = os.path.join(DBMS.DIRECTORY, year, title) print(f"{year} - «{title}»") ''' Если этой монеты пока нет в базе данных ''' if not os.path.exists(path): ''' Если необходимо, добавляем ''' if add_new: ''' Добавляем данные о монете''' add_coin( coin, year, title, show_process=show_process) if show_process: print("Данные загружены") ''' Записываем её динамику ''' parse_dynamics(coin, year, title, show_process=show_process) if show_process: print("Динамика загружена") print() else: ''' Парсим и чистим новую цену ''' new_price = re.sub(r'\D', '', coin.find('td', {'data-title': f'Цена {DBMS.today}'}).text) or '0' ''' Перезаписываем новую цену ''' with open(os.path.join(path, "Цена" + ".txt"), 'wt') as file: file.write(str(new_price)) if show_process: print("Стоимость обновлена") ''' Если необходимо обновлять динамику ''' if update_dynamics: parse_dynamics(coin, year, title, show_process=show_process) if show_process: print("Динамика дополнена") print() print(f'Цены на момент {DBMS.today} были обновлены') ''' После удачного обновления создаём копию базы ''' DBMS.pack_data() else: print("Сбой обновления цен")
def do_use(self, args): #assign which DB to use if args in DBMS.findAllDBs(): self.selectDB = './DB/' + args + '/' else: print '[Use Error] There is no such DB, check again!!'
import DBMS ############### ### Расходы ### ############### #%% Создаем сообщение. raw_message = 'Бананы - 150' # Проверка на наличие такого продукта в БД. product = DBMS.product_exist(raw_message) # Если БД пустая, то вернётся товар с полем category=None. print(product) #%% Добавляем категорию для данного товара в его поле. product.category = 'Продукты' # И записываем её в БД. # При записи также добавится сам товар и расход по нему. DBMS.add_category(product) #%% Ещё пример. raw_message = 'Бензин - 1000' product = DBMS.product_exist(raw_message) if not product.category: product.category = 'Автомобиль' DBMS.add_category(product) #%% Если категория данного товара нам уже известа,
import DBMS from bot_config import TOKEN class Statement(StatesGroup): answer = State() # Инициализация бота. bot = Bot(token=TOKEN) dp = Dispatcher(bot, storage=MemoryStorage()) dp.middleware.setup(LoggingMiddleware()) # Инициализация глобальных переменных. product = DBMS.Product("", 0, None) raw_message = "" @dp.message_handler(commands=['start']) async def send_welcome(message): # содание клавиатуры с кнопками markup = ReplyKeyboardMarkup(resize_keyboard=True) item1 = KeyboardButton("/start") item2 = KeyboardButton("/all_costs") item3 = KeyboardButton("/all_deposits") item4 = KeyboardButton("/help") markup.add(item1, item2, item3, item4) await message.answer( 'Привет! Я бот для ведения бюджета\n' 'Вводи свои покупки в виде: название - цена\n'
import Currency from telegram.ext import * from DBMS import * from sms import SMS import unidecode from bot_info import * # Enable logging logging.basicConfig( format='%(asctime)s - %(name)s - %(levelname)s - %(message)s', level=logging.INFO) logger = logging.getLogger(__name__) # DBMS database = DBMS() all_users = database.get_users() users_dict = {} for each in all_users: users_dict[each[0]] = list(each) all_users = users_dict print(all_users) prices = Currency.Currency() def start(update: Update, context: CallbackContext) -> None: person = update.message.from_user print( f' New Thread with {person.username} at : {get_duration(update.message.date)} ago'
def init(): Configuration.load('debug.yml') DBMS.create_table(Configuration.db_path)