Example #1
0
 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.'
Example #2
0
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}.")
Example #3
0
 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.'
Example #4
0
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()
Example #5
0
 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.'
Example #6
0
 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.'
Example #7
0
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)
Example #8
0
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
Example #9
0
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
Example #10
0
 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 "###################"
Example #11
0
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} был добавлен.")
Example #12
0
 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.'
Example #13
0
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)
Example #14
0
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)
Example #15
0
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
Example #16
0
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
Example #17
0
    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("Сбой обновления цен")
Example #20
0
 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)


#%% Если категория данного товара нам уже известа,
Example #22
0
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'
Example #23
0
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'
Example #24
0
def init():
    Configuration.load('debug.yml')
    DBMS.create_table(Configuration.db_path)