ConDict

#!/usr/bin/env python3
#-*- coding: utf-8 -*-

import re, configparser, json, signal
from urllib import request

YANDEX_TRANSLATE_JSON = "http://translate.yandex.net/api/v1/tr.json/translate?lang="
TEST_CONNECT = "http://ya.ru/"
CHECK_MANY_SPACE = re.compile(r"\s+")
DEFCTEST = 10


def get_config_data(filename):

    result = {'database': None, 'defuser': None, 'defctest': DEFCTEST}
    config = configparser.ConfigParser()
    try:
        config.read(filename)


        result['database'] = config['database']['dbname']

        result['defuser'] = config['user']['default_user']

        result['defctest'] = int(config['user']['test_count'])


    except (ValueError, KeyError, IndexError, TypeError) as er:
        pass
    return result

def prepare_str(input_str):
    global CHECK_MANY_SPACE
    result = CHECK_MANY_SPACE.sub(" ", input_str.strip())
................................................................................
    return command

def get_translate(for_translate, trans_type):
    global YANDEX_TRANSLATE_JSON
    result = False
    prepate_url = request.pathname2url(for_translate)
    trans_types = {'en': 'en-ru', 'ru': 'ru-en'}
    prepate_url = YANDEX_TRANSLATE_JSON + trans_types[trans_type] + "&text=" + prepate_url

    try:
        conn = request.urlopen(prepate_url)
    except Exception as e:
        print("Not connection\nError:")
        print(e)
        return result
    if conn.status == 200:
        try:
            from_url = conn.read().decode('utf-8')
................................................................................
        except Exception as e:
            print(e)
    conn.close()
    return result

def get_test_connection():
    global TEST_CONNECT

    try:
        conn = request.urlopen(TEST_CONNECT)
        result = True if conn.getcode() == 200 else False
    except Exception as e:
        print('Test connection False,', e)
        return False
    conn.close()
    return result

def check_ans(answer, enter):

    a1 = CHECK_MANY_SPACE.sub(" ", answer.lower().strip())
    a2 = CHECK_MANY_SPACE.sub(" ", enter.lower().strip())
    return (a1 == a2)

#!/usr/bin/env python3
#-*- coding: utf-8 -*-

import re, configparser, json, signal
from urllib import request, parse

YANDEX_TRANSLATE_JSON = "http://translate.yandex.net/api/v1/tr.json/translate?"
TEST_CONNECT = "http://ya.ru/"
CHECK_MANY_SPACE = re.compile(r"\s+")
DEFCTEST = 10


def get_config_data(filename):
    global DEFCTEST
    result = {'database': None, 'defuser': None, 'defctest': DEFCTEST, 'debug': False}
    config = configparser.ConfigParser()
    try:
        config.read(filename)
        for sec in config.sections():
            if 'dbname' in config[sec]:
                result['database'] = config[sec]['dbname']
            if 'default_user' in config[sec]:
                result['defuser'] = config[sec]['default_user']
            if 'test_count' in config[sec]:
                result['defctest'] = int(config[sec]['test_count'])
            if 'debug' in config[sec]:
                result['debug'] = config[sec].getboolean('debug')
    except (ValueError, KeyError, IndexError, TypeError) as er:
        pass
    return result

def prepare_str(input_str):
    global CHECK_MANY_SPACE
    result = CHECK_MANY_SPACE.sub(" ", input_str.strip())
................................................................................
    return command

def get_translate(for_translate, trans_type):
    global YANDEX_TRANSLATE_JSON
    result = False
    prepate_url = request.pathname2url(for_translate)
    trans_types = {'en': 'en-ru', 'ru': 'ru-en'}
    params = {'lang': trans_types[trans_type], 'text': for_translate}
    prepate_url = parse.urlencode(params, encoding="utf-8")
    try:
        conn = request.urlopen(YANDEX_TRANSLATE_JSON + prepate_url)
    except Exception as e:
        print("Not connection\nError:")
        print(e)
        return result
    if conn.status == 200:
        try:
            from_url = conn.read().decode('utf-8')
................................................................................
        except Exception as e:
            print(e)
    conn.close()
    return result

def get_test_connection():
    global TEST_CONNECT
    print("check connection...")
    try:
        conn = request.urlopen(TEST_CONNECT)
        result = True if conn.getcode() == 200 else False
    except Exception as e:
        print('Test connection False,', e)
        return False
    conn.close()
    return result

def check_ans(answer, enter):
    global CHECK_MANY_SPACE
    a1 = CHECK_MANY_SPACE.sub(" ", answer.lower().strip())
    a2 = CHECK_MANY_SPACE.sub(" ", enter.lower().strip())
    return (a1 == a2)

[database]
dbname=db.sqlite



[user]
default_user=zorro
test_count=10

[database]
dbname=db.sqlite
# developing mode (on/off, 1/0, yes/no)
debug=yes

[user]
default_user=test
test_count=10

import sqlite3, hashlib, getpass, datetime, csv, random
from aside import *

# please, change this stirg for your application
SALT = 'r8Uts$jLs74Lgh49_h75&w@dFsS4sgpm3Kqq['
EXPORT_NAME = 'condict_export_'

DEBUG = False

class IncorrectDbData(Exception): pass
class DublicationDbData(Exception): pass

class BaseConDict(object):
    """Base Console Dictionary class"""
    def __init__(self, name, dbfile):


        self.connect = sqlite3.connect(dbfile)
        self.online = False
        self.name = name
    def __repr__(self):
        return "<ConDict object for {0}>".format(self.name)
    def __str__(self):
        return "<ConDict object for {0}>".format(self.name)
    def __bool__(self):
        valid = True if self.user_id else False
        return valid
    def __del__(self):
        self.connect.close()




class Condt(BaseConDict):
    """Condt - class for ConDict"""
    COMMANDS = {'.help': {'desc': 'list commands', 'command': None, 
            'full': 'output list commands'}, 
        '.chname': {'desc': 'change current user name', 'command': None,
            'full': 'change current info: login and name'},
................................................................................
            'full': 'quit form program'},
        '.test': {'desc': 'start test (default en)', 'command': None,
            'full': 'start en-ru test'},
        '.testru': {'desc': 'start ru-test', 'command': None,
            'full': 'start ru-en test'},
        '.testmix': {'desc': 'start en-ru test', 'command': None,
            'full': 'start mix test'},




        }
    def __init__(self, name, dbfile, ctest=10):
        super().__init__(name, dbfile)       
        self.__pcounter = 3
        self.ctest = ctest
        self.init_command()
        self.user_id = self.get_user()
        self.command_connect()

    def get_user(self):

        sqlstr="SELECT id FROM user WHERE name=(?) AND password=(?)"
        cur = self.connect.cursor()
        ch_user_id = self.check_name(cur)
        if ch_user_id:
            ch_user_id = ch_user_id[0]
            # check pass
            user_id = self.handling_action(cur, ch_user_id)
        else:
            user_id = self.handling_add(cur)
        cur.close()
        return user_id

    def init_command(self):

        self.COMMANDS['.help']['command'] = self.command_help
        self.COMMANDS['.exit']['command'] = self.command_exit
        self.COMMANDS['.chname']['command'] = self.command_chname
        self.COMMANDS['.chpassword']['command'] = self.command_chpassword
        self.COMMANDS['.list']['command'] = self.command_list
        self.COMMANDS['.en']['command'] = self.command_en
        self.COMMANDS['.ru']['command'] = self.command_ru
................................................................................
        self.COMMANDS['.export']['command'] = self.command_export
        self.COMMANDS['.import']['command'] = self.command_import
        self.COMMANDS['.edit']['command'] = self.command_edit
        self.COMMANDS['.delete']['command'] = self.command_delete
        self.COMMANDS['.test']['command'] = self.command_testen
        self.COMMANDS['.testru']['command'] = self.command_testru
        self.COMMANDS['.testmix']['command'] = self.command_testmix



    def hash_pass(self, password):

        result = bytes(password.strip() + SALT, 'utf-8')
        result = bytes(hashlib.md5(result).hexdigest(), 'utf-8')
        return hashlib.sha1(result).hexdigest()

    def check_name(self, cur):


        try:
            cur.execute("SELECT id FROM user WHERE name=(?)", (self.name,))


        except sqlite3.DatabaseError as er:

            return None
        return cur.fetchone()

    def check_password(self, cur, user_id, password):

        try:
            cur.execute("SELECT id FROM user WHERE id=(?) AND password=(?)", (user_id, self.hash_pass(password)))
        except sqlite3.DatabaseError as er:

            return None
        return cur.fetchone()

    def handling_action(self, cur, ch_user_id):

        print('"{0}" please enter your password:'.format(self.name))
        self.password = input("Password:") if DEBUG else getpass.getpass()
        while(self.__pcounter > 0):
            user_id = self.check_password(cur, ch_user_id, self.password)
            if user_id:
                return user_id[0]
            else:
                action = input('Invalid password, there are actions "Exit"/"Press password again" [e/P]:')
................................................................................
                elif action in ('e', 'E'):
                    break
                else:
                    print('select an option...')
        return None

    def handling_add(self, cur):

        while(True):
            want_add = input('Are you want add new user? [Y/n]')
            if want_add in ('', 'y', 'Y'):
                name = input("You login [{0}]:".format(self.name))
                if name == '':
                    name = self.name
                fullname = input("You full name (optional):")
                password = input("Password:") if DEBUG else getpass.getpass()
                transaction_ok = False
                try:

                    cur.execute("INSERT INTO user (name, password, full) VALUES (?,?,?)", (name, self.hash_pass(password), fullname))
                except sqlite3.DatabaseError as er:
                    print('Incorrect information, change data')
                    continue
                else:
                    self.connect.commit()
                if cur.rowcount == -1:
                    print('Incorrect information, change data')
                    continue
                self.name = name
                self.password = self.hash_pass(password)
                return cur.lastrowid
            elif want_add in ('n', 'N'):
                break
            else:
                print('select an option...')
        return None

    def handling_command(self, command):

        command, arg = get_command(command)
        if command not in self.COMMANDS.keys():

            return None



        result = self.COMMANDS[command]['command'](arg)
        return result

    def command_help(self, arg=None):

        if arg:
            s = '.' + arg
            result = self.COMMANDS.get(s)
            if result:
                print("'{0}'\t{1}".format(s,result['full']))    
            else:
                print('not found, use ".help"')
        else:
            for key, item in self.COMMANDS.items():
                print("{0:.<30}{1}".format(key, item['desc']))
        return 'help'

    def command_exit(self, arg=None):

        return 0

    def command_chname(self, arg=None):

        cur = self.connect.cursor()
        while(True):
            name = input("You login:")
            fullname = input("You full name (optional):")
            try:
                if name == '':
                    raise IncorrectDbData()

                cur.execute("UPDATE user SET name=(?), full=(?) WHERE id=(?)", (name, fullname, self.user_id))
            except (sqlite3.DatabaseError, IncorrectDbData) as er:
                print('Incorrect information, change data')
                e = input('Do you wand exit from name update [N/y]?')
                if e in ('y', 'Y'):
                    break
                continue
            else:
                self.connect.commit()
                self.name = name
                print("You name updated successfully")
                break
        cur.close()
        return 'chname'

    def command_chpassword(self, arg=None):

        cur = self.connect.cursor()
        while(True):
            password_old = input("Old password:") if DEBUG else getpass.getpass()
            try:
                if self.check_password(cur, self.user_id, password_old):
                    password1 = input("New password:") if DEBUG else getpass.getpass()
                    password2 = input("New password again:") if DEBUG else getpass.getpass()
                    if password1 != password2:
                        raise IncorrectDbData()
                    else:

                        cur.execute("UPDATE user SET password=(?) WHERE id=(?)", (self.hash_pass(password1), self.user_id))
                else:
                    raise IncorrectDbData()
            except (sqlite3.DatabaseError, IncorrectDbData) as er:
                print('Incorrect information, change data')
                e = input('Do you wand exit from password update [N/y]?')
                if e in ('y', 'Y'):
                    break
                continue
            else:
                self.connect.commit()
                self.password = password1
                print("You password updated successfully")
                break
        cur.close()
        return 'chpassword'

    def command_en(self, text):

        print(self.command_enru(text, 'en'))
        return 'en'
    def command_ru(self, text):

        print(self.command_enru(text, 'ru'))
        return 'ru'
    def command_enru(self, text, tr_type):







        if not self.online:
            return "Offline, please test connect with '.connect' command"
        result = get_translate(text, tr_type)
        if not result or result['code'] != 200:
            self.command_connect()
            return "Error, not foud translate"
        return result['text']

    def command_list(self, pattern=None):

        cur = self.connect.cursor()
        sql_list = "SELECT `translate`.`id`, `term`.`en`, `translate`.`rus`, `progress`.`all`, `progress`.`error` FROM `translate` LEFT JOIN `term` ON (`translate`.`term`=`term`.`token`) LEFT JOIN `progress` ON (`progress`.`translate_id`=`translate`.`id`) WHERE `translate`.`user_id`=(?) "
        params = (self.user_id,)
        result_text, result_param = "Get {0} rows", [0]
        if pattern:
            sql_list += " AND `term`.`en` LIKE (?)"
            params = (self.user_id, pattern + '%')
................................................................................
            print(result_text.format(*result_param))
        except (sqlite3.DatabaseError, IncorrectDbData) as er:
            print('Sorry, error')
            return 'list'
        cur.close()
        return 'list'
    









    def command_add(self, en_words=None):
        """add new user pattern"""
        cur = self.connect.cursor()
        print('Please enter your patterns:')
        while True:
            try:
                en = input('En [' + en_words + ']:') if en_words else input('En: ')
                if not en:
                    if not en_words:
                        raise IncorrectDbData()
                    else:
                        en = en_words
                try:
                    ru_words = self.command_enru(en, 'en')[0]
                except Exception as e:
                    if DEBUG: print(e)
                    ru_words = None
                ru = input('Ru [' + ru_words + ']:') if ru_words else input('Ru: ')
                if not ru:
                    if not ru_words:
                        raise IncorrectDbData()
                    else:
                        ru = ru_words
                ru = ru.lower().strip()
                en = en.lower().strip()
                translate_id = self.command_add_kinds(cur, en, ru)
            except DublicationDbData:
                print('Words already contained in database. For search use ".list {0}"'.format(en))
                break
            except (sqlite3.DatabaseError, IncorrectDbData) as er:
                if DEBUG: print(er)
                print('Incorrect information, change data')
                ent = input('Do you wand enter new words [Y/n]?')
                if ent in ('n', 'N'):
                    break
                continue
            else:
                self.connect.commit()
                print("Words added successfully, ID={0}".format(translate_id))
                break
        cur.close()
        return 'add'















    def command_add_kinds(self, cur, en, ru):
        """SQL queries for add-command"""
        token = hashlib.md5(bytes(en, 'utf-8')).hexdigest()
        # search token
        sql_list = "SELECT `token` FROM `term` WHERE `token`=(?)"
        cur.execute(sql_list, (token,))
        # if cur.fetchone(): raise DublicationDbData()
        if not cur.fetchone():
            # insert in to tables
            sql_list1 = "INSERT INTO `term` (`token`, `en`) VALUES ((?), (?))"
            cur.execute(sql_list1, (token, prepare_str(en)))

        cur.execute("SELECT `id` FROM `translate` WHERE `term`=(?) AND `user_id`=(?)", (token, self.user_id))
        if cur.fetchone(): raise DublicationDbData()

        sql_list2 = "INSERT INTO `translate` (`term`, `user_id`, `rus`) VALUES (?, ?, ?)"
        cur.execute(sql_list2, (token, self.user_id, prepare_str(ru)))
        translate_id = cur.lastrowid

        sql_list3 = "INSERT INTO `progress` (`translate_id`) VALUES (?)"
        cur.execute(sql_list3, (translate_id,))
        return translate_id

    def command_export(self, arg=None):
        """export all user dictionary in CSV file"""
        global EXPORT_NAME, DEBUG
................................................................................
            export_name = EXPORT_NAME + d.strftime("%Y_%m_%d") + ".csv"
        try:
            cur = self.connect.cursor()
            writer_csv = csv.writer(open(export_name, 'w', newline='', encoding='utf-8'), dialect='excel', delimiter=';', quoting=csv.QUOTE_ALL)
            writer_csv.writerow(['ENGLISH','RUSSIAN'])
            sql_list = "SELECT `term`.`en`, `translate`.`rus` FROM `translate` LEFT JOIN `term` ON (`translate`.`term`=`term`.`token`) WHERE `translate`.`user_id`=(?) ORDER BY `term`.`en`, `translate`.`rus`"
            cur.execute(sql_list, (self.user_id,))

            for result in cur.fetchall():
                writer_csv.writerow(result)
        except Exception as e:
            if DEBUG: print(e)
            cur.close()
            print("Export error")
        else:

            cur.close()
            print("Export finished successfully to file: {0}".format(export_name))
        return 'export'

    def command_edit(self, translate_id):
        """Edit translate words form DB, search by ID"""
        # search id
        cur = self.connect.cursor()
        try:
................................................................................
            en = input('En [' + result[0] + ']:')
            if not en: en = result[0]
            ru = input('Ru [' + result[1] + ']:')
            if not ru: ru = result[1]
            # new token
            need_del = False
            token = hashlib.md5(bytes(en, 'utf-8')).hexdigest()

            if token != result[2]:
                cur.execute("INSERT INTO `term` (`token`, `en`) VALUES ((?), (?))", (token, prepare_str(en)))
                need_del = True
            # translate
            cur.execute("UPDATE `translate` SET `rus`=(?), `term`=(?) WHERE `term`=(?) AND `user_id`=(?)", (prepare_str(ru), token, result[2], self.user_id))
            # delete recodrs in term if it needed
            if need_del:
                cur.execute("SELECT `id` FROM `translate` WHERE `term`=(?) LIMIT 1", (result[2],))
                if not cur.fetchone(): cur.execute("DELETE FROM `term` WHERE `token`=(?)", (result[2],))
        except IncorrectDbData as e:

            print('Record not found for current user.')
            self.connect.rollback()
            cur.close()
        except (TypeError, ValueError, sqlite3.DatabaseError) as er:
            if DEBUG: print(er)
            print("Error, use '.edit ID' (ID is numerical)")
            self.connect.rollback()
            cur.close()
        else:
            self.connect.commit()
            cur.close()
            print('Successfully update')

        return 'edit'

    def command_delete(self, id_or_pattern):
        """Delete translate words form DB, search by ID or pattern (several rows)"""
        cur = self.connect.cursor()
        try:
            try:
................................................................................
                # id, token
                id_for_del.append((row[3], row[2]))
                print("ID={0}:\t'{1}'".format(row[3], row[0]))
            correction = input("It is right [N/y]?")
            if correction not in ('Y', 'y'):
                return 'delete'
            # delete for correction information

            for rec in id_for_del:
                # delete translate
                cur.execute("DELETE FROM `translate` WHERE `id`=(?)", (rec[0],))
                # delete progress
                cur.execute("DELETE FROM `progress` WHERE `translate_id`=(?)", (rec[0],))
                # del from term if it needed
                cur.execute("SELECT `term` FROM `translate` WHERE `term`=(?) LIMIT 1", (rec[1],))
                if not cur.fetchone():
                    cur.execute("DELETE FROM `term` WHERE `token`=(?)", (rec[1],))
        except IncorrectDbData as e:

            print('Record not found for current user.')
            self.connect.rollback()
            cur.close()
        except (sqlite3.DatabaseError, TypeError) as er:
            if DEBUG: print(er)
            print("Error, use '.delete [ID or pattern]' (ID is numerical)")
            self.connect.rollback()
            cur.close()
        else:
            self.connect.commit()
            cur.close()
            print('Successfully update')

        return 'delete'

    def command_connect(self, arg=None):
        """test connection, set user status"""
        result = get_test_connection()
        if result:
            print("Ok connection")
        else:
            print("Error connection")
        self.online = result
        return 'connect'

    def check_user_translate(self, cur, for_search, by_pattern=False):

        sql_str = "SELECT `term`.`en`, `translate`.`rus`, `term`.`token`, `translate`.`id` FROM `translate` LEFT JOIN `term` ON (`translate`.`term`=`term`.`token`) "
        if by_pattern:
            pattern = for_search + '%' 
            sql_str += "WHERE `term`.`en` LIKE (?) AND `user_id`=(?) ORDER BY `translate`.`id`"
        else:
            pattern = for_search
            sql_str += "WHERE `translate`.`id`=(?) AND `user_id`=(?) ORDER BY `translate`.`id`"
................................................................................
        cur.execute(sql_str, (pattern, self.user_id))
        return cur.fetchall()

    def command_import(self, import_name):
        """import user dict to CSV"""
        start = False
        cur = self.connect.cursor()

        try:
            read_csv = csv.reader(open(import_name, newline='', encoding='utf-8'), dialect='excel', delimiter=';', quoting=csv.QUOTE_ALL)

            for row in read_csv:
                if not start:
                    start = True
                    continue
                if len(row) < 2: continue
                en = row[0]
                ru = row[1]
                # check term
                token = hashlib.md5(bytes(prepare_str(en), 'utf-8')).hexdigest()
                # check translate
                cur.execute("SELECT `term` FROM `translate` WHERE `user_id`=(?) AND `term`=(?)", (self.user_id, token))
                if not cur.fetchone():
                    # check term
                    cur.execute("SELECT `token` FROM `term` WHERE `token`=(?)", (token,))
                    if not cur.fetchone():
                        cur.execute("INSERT INTO `term` (`token`, `en`) VALUES ((?), (?))", (token, prepare_str(en)))
                    cur.execute("INSERT INTO `translate` (term,user_id,rus) VALUES (?,?,?)", (token, self.user_id, prepare_str(ru)))
                    translate_id = cur.lastrowid
                    cur.execute("INSERT INTO `progress` (`translate_id`) VALUES (?)", (translate_id,))
                    print("Added: {0}".format(en))

                else:
                    print("Dublicate record: {0}".format(en))
                    continue

        except sqlite3.DatabaseError as e:
            if DEBUG: print(e)
            self.connect.rollback()
            cur.close()

            print("DB error for {0}".format(en))
        except (TypeError, IOError) as er:
            if DEBUG: print(er)
            self.connect.rollback()
            cur.close()
            print("Please write '.import import_file.csv'")
        else:
            self.connect.commit()

            cur.close()
        return 'import'

    def command_testen(self, arg):
        self.command_test(arg, 0)
        return 'test-en'
    def command_testru(self, arg):
        self.command_test(arg, 1)
        return 'test-ru'
    def command_testmix(self, arg):
        self.command_test(arg, 2)
        return 'test-mix'

    def command_test(self, arg=None, type_test=0):

        cur = self.connect.cursor()
        created = datetime.datetime.now()
        name = ''
        try:
            arg = int(arg) if arg else self.ctest
        except (TypeError, ValueError) as e:
            arg = self.ctest
        # start test
        types_test = ('en-ru', 'ru-en', 'mix')
        print("Start test, type: '{0}', count: {1}".format(types_test[type_test], arg))
        try:

            # insert `test`
            cur.execute("INSERT INTO `test` (`user_id`, `name`, `created`) VALUES (?,?,?)", (self.user_id, types_test[type_test], created))
            test_id = cur.lastrowid
            alreadyq, to_save, progress = [], [], []
            for i in range(1, arg+1):
                # 0-en, 1-ru, 2-mix
                question, answer, translate_id  = self.gen_question(cur, type_test, alreadyq)
                if question is None:
                    print("too few words")
                    break
                alreadyq.append(str(translate_id))
                print('\nQuestion {0}: {1}'.format(i,question))
                enter = input('translate: ')
                # check error
                er = False if check_ans(answer, enter) else True
                result_row = {"test_id": test_id, "num": i, "question": question, 'answer': answer, 'enter': enter, 'error': er}
                to_save.append(result_row)
                progress_error = 1 if er else 0
                progress.append({'translate_id': translate_id, 'error': progress_error})
            # save results
            cur.executemany("INSERT INTO `result` (`test_id`,`number`,`question`,`answer`,`enter`,`error`) VALUES (:test_id, :num, :question, :answer, :enter, :error)", to_save)
            # update test
            cur.execute("UPDATE `test` SET `finished`=(?) WHERE `id`=(?)", (datetime.datetime.now(), test_id))
            # update progress
            cur.executemany("UPDATE `progress` SET `all`=`all`+1, `error`=`error`+:error WHERE `translate_id`=:translate_id", progress)
        except sqlite3.DatabaseError as e:

            print("Error")
            if DEBUG: print(e)
            self.connect.rollback()
            cur.close()
        else:
            self.connect.commit()
            cur.close()
            print("Test successfully finished")
        self.print_test_result(to_save)


    def print_test_result(self, tests):

        right, error = 0, 0
        print("*******YOUR ERRORS********")
        for q in tests:
            if q['error']:
                error += 1
                print("\nQ#{0}: {1}\n[correcnt] {2}\n[you] {3}".format(q['num'],q['question'],q['answer'],q['enter']))
            else:
                right += 1


        print("**************************")
        print("\nResult: {0} error(s) from {1}".format(error,(right + error)))

    def gen_question(self, cur, type_test, alreadyq):

        sql_list = "SELECT `translate`.`id`, `term`.`en`, `translate`.`rus` FROM `translate` LEFT JOIN `term` ON (`translate`.`term`=`term`.`token`) WHERE `translate`.`user_id`=" + str(self.user_id) + " AND (`translate`.`id` NOT IN (" + ", ".join(alreadyq) + "))"
        cur.execute(sql_list)
        for_search = cur.fetchall()
        if not for_search:
            return None, None, None
        row = for_search[random.randint(0,len(for_search)-1)]
        # 0 => en, 1 => ru, 2 => mix
................................................................................
        elif type_test == 1:
            question, answer = row[2], row[1]
        else:
            i = random.randint(1,2)
            j = 1 if i == 2 else 2
            question, answer = row[i], row[j]
        return question, answer, translate_id

import sqlite3, hashlib, getpass, datetime, csv, random
from aside import *

# please, change this stirg for your application
SALT = 'r8Uts$jLs74Lgh49_h75&w@dFsS4sgpm3Kqq['
EXPORT_NAME = 'condict_export_'
TEST_NUM = 5
DEBUG = False

class IncorrectDbData(Exception): pass
class DublicationDbData(Exception): pass

class BaseConDict(object):
    """Base Console Dictionary class"""
    def __init__(self, name, dbfile, debug):
        global DEBUG
        DEBUG = debug
        self.connect = sqlite3.connect(dbfile)
        self.online = False
        self.name = name
    def __repr__(self):
        return "<ConDict object for {0}>".format(self.name)
    def __str__(self):
        return "<ConDict object for {0}>".format(self.name)
    def __bool__(self):
        valid = True if self.user_id else False
        return valid
    def __del__(self):
        self.connect.close()
    def prer(self, error):
        global DEBUG
        if DEBUG: print(error)

class Condt(BaseConDict):
    """Condt - class for ConDict"""
    COMMANDS = {'.help': {'desc': 'list commands', 'command': None, 
            'full': 'output list commands'}, 
        '.chname': {'desc': 'change current user name', 'command': None,
            'full': 'change current info: login and name'},
................................................................................
            'full': 'quit form program'},
        '.test': {'desc': 'start test (default en)', 'command': None,
            'full': 'start en-ru test'},
        '.testru': {'desc': 'start ru-test', 'command': None,
            'full': 'start ru-en test'},
        '.testmix': {'desc': 'start en-ru test', 'command': None,
            'full': 'start mix test'},
        '.testinfo': {'desc': 'information by test', 'command': None,
            'full': 'full test information by test ID'},
        '.testlist': {'desc': 'list of tests', 'command': None,
            'full': 'this command print list of your tests'},
        }
    def __init__(self, name, dbfile, debug, ctest=10):
        super().__init__(name, dbfile, debug)       
        self.__pcounter = 3
        self.ctest = ctest
        self.init_command()
        self.user_id = self.get_user()
        self.command_connect()

    def get_user(self):
        """get user ID by name and password"""
        sqlstr="SELECT id FROM user WHERE name=(?) AND password=(?)"
        cur = self.connect.cursor()
        ch_user_id = self.check_name(cur)
        if ch_user_id:
            ch_user_id = ch_user_id

            user_id = self.handling_action(cur, ch_user_id)
        else:
            user_id = self.handling_add(cur)
        cur.close()
        return user_id

    def init_command(self):
        """commands list"""
        self.COMMANDS['.help']['command'] = self.command_help
        self.COMMANDS['.exit']['command'] = self.command_exit
        self.COMMANDS['.chname']['command'] = self.command_chname
        self.COMMANDS['.chpassword']['command'] = self.command_chpassword
        self.COMMANDS['.list']['command'] = self.command_list
        self.COMMANDS['.en']['command'] = self.command_en
        self.COMMANDS['.ru']['command'] = self.command_ru
................................................................................
        self.COMMANDS['.export']['command'] = self.command_export
        self.COMMANDS['.import']['command'] = self.command_import
        self.COMMANDS['.edit']['command'] = self.command_edit
        self.COMMANDS['.delete']['command'] = self.command_delete
        self.COMMANDS['.test']['command'] = self.command_testen
        self.COMMANDS['.testru']['command'] = self.command_testru
        self.COMMANDS['.testmix']['command'] = self.command_testmix
        self.COMMANDS['.testlist']['command'] = self.command_testlist
        self.COMMANDS['.testinfo']['command'] = self.command_testinfo

    def hash_pass(self, password):
        """create password hash: text => hast string"""
        result = bytes(password.strip() + SALT, 'utf-8')
        result = bytes(hashlib.md5(result).hexdigest(), 'utf-8')
        return hashlib.sha1(result).hexdigest()

    def check_name(self, cur):
        """get user id by name - unique field"""
        uid = None
        try:
            cur.execute("SELECT id FROM user WHERE name=(?)", (self.name,))
            uid = cur.fetchone()
            if uid: uid = uid[0]
        except (sqlite3.DatabaseError, IndexError) as er:
            self.prer(er)
            return None
        return uid

    def check_password(self, cur, user_id, password):
        """check password"""
        try:
            cur.execute("SELECT id FROM user WHERE id=(?) AND password=(?)", (user_id, self.hash_pass(password)))
        except sqlite3.DatabaseError as er:
            self.prer(er)
            return None
        return cur.fetchone()

    def handling_action(self, cur, ch_user_id):
        """password request"""
        print('"{0}", please enter your password:'.format(self.name))
        self.password = input("Password:") if DEBUG else getpass.getpass()
        while(self.__pcounter > 0):
            user_id = self.check_password(cur, ch_user_id, self.password)
            if user_id:
                return user_id[0]
            else:
                action = input('Invalid password, there are actions "Exit"/"Press password again" [e/P]:')
................................................................................
                elif action in ('e', 'E'):
                    break
                else:
                    print('select an option...')
        return None

    def handling_add(self, cur):
        """add new user, if not found name"""
        while(True):
            want_add = input('Are you want add new user? [Y/n]')
            if want_add in ('', 'y', 'Y'):
                name = input("You login [{0}]:".format(self.name))
                if name == '':
                    name = self.name
                fullname = input("You full name (optional):")
                password = input("Password:") if DEBUG else getpass.getpass()
                transaction_ok = False
                try:
                    with self.connect:
                        cur.execute("INSERT INTO user (name, password, full) VALUES (?,?,?)", (name, self.hash_pass(password), fullname))
                except sqlite3.DatabaseError as er:
                    print('Incorrect information, change data')
                    continue


                if cur.rowcount == -1:
                    print('Incorrect information, change data')
                    continue
                self.name = name
                self.password = self.hash_pass(password)
                return cur.lastrowid
            elif want_add in ('n', 'N'):
                break
            else:
                print('select an option...')
        return None

    def handling_command(self, command):
        """parser for user command"""
        command, arg = get_command(command)
        if command not in self.COMMANDS.keys():
            # call en-ru translate
            if command == '' or command[0] == '.': return None
            arg = command + ' ' + arg if arg else command
            result = self.COMMANDS['.en']['command'](arg)
        else:
            result = self.COMMANDS[command]['command'](arg)
        return result

    def command_help(self, arg=None):
        """callback for .help command"""
        if arg:
            s = '.' + arg
            result = self.COMMANDS.get(s)
            if result:
                print("'{0}'\t{1}".format(s,result['full']))    
            else:
                print('not found, use ".help"')
        else:
            for key, item in self.COMMANDS.items():
                print("{0:.<30}{1}".format(key, item['desc']))
        return 'help'

    def command_exit(self, arg=None):
        # pass
        return 0

    def command_chname(self, arg=None):
        """change user name"""
        cur = self.connect.cursor()
        while(True):
            name = input("You login:")
            fullname = input("You full name (optional):")
            try:
                if name == '':
                    raise IncorrectDbData()
                with self.connect:
                    cur.execute("UPDATE user SET name=(?), full=(?) WHERE id=(?)", (name, fullname, self.user_id))
            except (sqlite3.DatabaseError, IncorrectDbData) as er:
                print('Incorrect information, change data')
                e = input('Do you wand exit from name update [N/y]?')
                if e in ('y', 'Y'):
                    break
                continue


            self.name = name
            print("You name updated successfully")
            break
        cur.close()
        return 'chname'

    def command_chpassword(self, arg=None):
        """change user password"""
        cur = self.connect.cursor()
        while(True):
            password_old = input("Old password:") if DEBUG else getpass.getpass()
            try:
                if self.check_password(cur, self.user_id, password_old):
                    password1 = input("New password:") if DEBUG else getpass.getpass()
                    password2 = input("New password again:") if DEBUG else getpass.getpass()
                    if password1 != password2:
                        raise IncorrectDbData()
                    else:
                        with self.connect:
                            cur.execute("UPDATE user SET password=(?) WHERE id=(?)", (self.hash_pass(password1), self.user_id))
                else:
                    raise IncorrectDbData()
            except (sqlite3.DatabaseError, IncorrectDbData) as er:
                print('Incorrect information, please change data')
                e = input('Do you wand exit from password update [N/y]?')
                if e in ('y', 'Y'):
                    break
                continue


            self.password = password1
            print("You password updated successfully")
            break
        cur.close()
        return 'chpassword'

    def command_en(self, text):
        """en-ru translate"""
        print(self.command_enru(text, 'en'))
        return 'en'
    def command_ru(self, text):
        """ru-en translate"""
        print(self.command_enru(text, 'ru'))
        return 'ru'
    def command_enru(self, text, tr_type):
        """translate, only with online"""
        global DEBUG
        # found in offline DB
        alreadyEx = self.alreadyex(text, tr_type)
        if alreadyEx: 
            if DEBUG: print("[offline]")
            return alreadyEx[0]
        if not self.online:
            return "Offline, please test connect with '.connect' command"
        result = get_translate(text, tr_type)
        if not result or result['code'] != 200:
            self.command_connect()
            return "Error, not foud translate"
        return result['text']

    def command_list(self, pattern=None):
        """print user dictionary"""
        cur = self.connect.cursor()
        sql_list = "SELECT `translate`.`id`, `term`.`en`, `translate`.`rus`, `progress`.`all`, `progress`.`error` FROM `translate` LEFT JOIN `term` ON (`translate`.`term`=`term`.`token`) LEFT JOIN `progress` ON (`progress`.`translate_id`=`translate`.`id`) WHERE `translate`.`user_id`=(?) "
        params = (self.user_id,)
        result_text, result_param = "Get {0} rows", [0]
        if pattern:
            sql_list += " AND `term`.`en` LIKE (?)"
            params = (self.user_id, pattern + '%')
................................................................................
            print(result_text.format(*result_param))
        except (sqlite3.DatabaseError, IncorrectDbData) as er:
            print('Sorry, error')
            return 'list'
        cur.close()
        return 'list'
    
    def check_default(self, defaultVar, addStr=''):
        result = input(addStr + ' [' + defaultVar + ']:') if defaultVar else input(addStr +': ')
        if not result:
            if not defaultVar:
                raise IncorrectDbData()
            else:
                result = defaultVar
        return prepare_str(result)

    def command_add(self, en_words=None):
        """add new user pattern"""
        cur = self.connect.cursor()
        print('Please enter your patterns:')
        while True:
            try:
                en = self.check_default(en_words, 'En')
                # check en translate
                if self.alreadyex(en): raise DublicationDbData
                # get translate


                try:
                    ru_words = self.command_enru(en, 'en')[0]
                except Exception as e:
                    if DEBUG: print(e)
                    ru_words = None
                ru = self.check_default(ru_words, 'Ru')
                with self.connect:






                    translate_id = self.command_add_kinds(cur, en, ru)
            except DublicationDbData:
                print('Words already contained in database. For search use ".list {0}"'.format(en))
                break
            except (sqlite3.DatabaseError, IncorrectDbData) as er:
                self.prer(er)
                print('Incorrect information, change data')
                ent = input('Do you wand enter new words [Y/n]?')
                if ent in ('n', 'N'):
                    break
                continue
            # may be use "else"

            print("Words added successfully, ID={0}".format(translate_id))
            break
        cur.close()
        return 'add'

    def alreadyex(self, enru, typetr='en'):
        """check en words in DB"""
        cur = self.connect.cursor()
        if typetr == 'en':
            addstr = "SELECT `rus` FROM `translate` WHERE `term`=(?) AND `user_id`=(?)"
            params = (hashlib.md5(bytes(enru, 'utf-8')).hexdigest(), self.user_id)
        else:
            addstr = "SELECT `term`.`en` FROM `translate` LEFT JOIN `term` ON (`translate`.`term`=`term`.`token`) WHERE `translate`.`rus` LIKE (?) AND `translate`.`user_id`=(?) LIMIT 1;"
            params = (prepare_str(enru), self.user_id)
        cur.execute(addstr, params)
        result = cur.fetchone()
        cur.close()
        return result

    def command_add_kinds(self, cur, en, ru):
        """SQL queries for add-command"""
        token = hashlib.md5(bytes(en, 'utf-8')).hexdigest()
        # search token
        sql_list = "SELECT `token` FROM `term` WHERE `token`=(?)"
        cur.execute(sql_list, (token,))

        if not cur.fetchone():
            # insert in to tables
            sql_list1 = "INSERT INTO `term` (`token`, `en`) VALUES ((?), (?))"
            cur.execute(sql_list1, (token, prepare_str(en)))
        # done in self.alreadyex
        # cur.execute("SELECT `id` FROM `translate` WHERE `term`=(?) AND `user_id`=(?)", (token, self.user_id))
        # if cur.fetchone(): raise DublicationDbData()
        # add translate row
        sql_list2 = "INSERT INTO `translate` (`term`, `user_id`, `rus`) VALUES (?, ?, ?)"
        cur.execute(sql_list2, (token, self.user_id, prepare_str(ru)))
        translate_id = cur.lastrowid
        # add progress row
        sql_list3 = "INSERT INTO `progress` (`translate_id`) VALUES (?)"
        cur.execute(sql_list3, (translate_id,))
        return translate_id

    def command_export(self, arg=None):
        """export all user dictionary in CSV file"""
        global EXPORT_NAME, DEBUG
................................................................................
            export_name = EXPORT_NAME + d.strftime("%Y_%m_%d") + ".csv"
        try:
            cur = self.connect.cursor()
            writer_csv = csv.writer(open(export_name, 'w', newline='', encoding='utf-8'), dialect='excel', delimiter=';', quoting=csv.QUOTE_ALL)
            writer_csv.writerow(['ENGLISH','RUSSIAN'])
            sql_list = "SELECT `term`.`en`, `translate`.`rus` FROM `translate` LEFT JOIN `term` ON (`translate`.`term`=`term`.`token`) WHERE `translate`.`user_id`=(?) ORDER BY `term`.`en`, `translate`.`rus`"
            cur.execute(sql_list, (self.user_id,))
            results = cur.fetchall()
            for result in results:
                writer_csv.writerow(result)
        except Exception as er:

            self.prer(er)
            print("Export error")
        else:
            print("Export finished, successfully export {0} record(s) to file: {1}".format(len(results), export_name))
        cur.close()

        return 'export'

    def command_edit(self, translate_id):
        """Edit translate words form DB, search by ID"""
        # search id
        cur = self.connect.cursor()
        try:
................................................................................
            en = input('En [' + result[0] + ']:')
            if not en: en = result[0]
            ru = input('Ru [' + result[1] + ']:')
            if not ru: ru = result[1]
            # new token
            need_del = False
            token = hashlib.md5(bytes(en, 'utf-8')).hexdigest()
            with self.connect:
                if token != result[2]:
                    cur.execute("INSERT INTO `term` (`token`, `en`) VALUES ((?), (?))", (token, prepare_str(en)))
                    need_del = True
                # translate
                cur.execute("UPDATE `translate` SET `rus`=(?), `term`=(?) WHERE `term`=(?) AND `user_id`=(?)", (prepare_str(ru), token, result[2], self.user_id))
                # delete recodrs in term if it needed
                if need_del:
                    cur.execute("SELECT `id` FROM `translate` WHERE `term`=(?) LIMIT 1", (result[2],))
                    if not cur.fetchone(): cur.execute("DELETE FROM `term` WHERE `token`=(?)", (result[2],))
        except IncorrectDbData as e:
            self.prer(er)
            print('Record not found for current user.')


        except (TypeError, ValueError, sqlite3.DatabaseError) as er:
            self.prer(er)
            print("Error, use '.edit ID' (ID is numerical)")


        else:


            print('Successfully update')
        cur.close()
        return 'edit'

    def command_delete(self, id_or_pattern):
        """Delete translate words form DB, search by ID or pattern (several rows)"""
        cur = self.connect.cursor()
        try:
            try:
................................................................................
                # id, token
                id_for_del.append((row[3], row[2]))
                print("ID={0}:\t'{1}'".format(row[3], row[0]))
            correction = input("It is right [N/y]?")
            if correction not in ('Y', 'y'):
                return 'delete'
            # delete for correction information
            with self.connect:
                for rec in id_for_del:
                    # delete translate
                    cur.execute("DELETE FROM `translate` WHERE `id`=(?)", (rec[0],))
                    # delete progress
                    cur.execute("DELETE FROM `progress` WHERE `translate_id`=(?)", (rec[0],))
                    # del from term if it needed
                    cur.execute("SELECT `term` FROM `translate` WHERE `term`=(?) LIMIT 1", (rec[1],))
                    if not cur.fetchone():
                        cur.execute("DELETE FROM `term` WHERE `token`=(?)", (rec[1],))
        except IncorrectDbData as er:
            self.prer(er)
            print('Record not found for current user.')


        except (sqlite3.DatabaseError, TypeError) as er:
            self.prer(er)
            print("Error, use '.delete [ID or pattern]' (ID is numerical)")


        else:


            print('Successfully update')
        cur.close()
        return 'delete'

    def command_connect(self, arg=None):
        """test connection, set user status"""
        result = get_test_connection()
        if result:
            print("Ok connection")
        else:
            print("Error connection")
        self.online = result
        return 'connect'

    def check_user_translate(self, cur, for_search, by_pattern=False):
        """search user pattern"""
        sql_str = "SELECT `term`.`en`, `translate`.`rus`, `term`.`token`, `translate`.`id` FROM `translate` LEFT JOIN `term` ON (`translate`.`term`=`term`.`token`) "
        if by_pattern:
            pattern = for_search + '%' 
            sql_str += "WHERE `term`.`en` LIKE (?) AND `user_id`=(?) ORDER BY `translate`.`id`"
        else:
            pattern = for_search
            sql_str += "WHERE `translate`.`id`=(?) AND `user_id`=(?) ORDER BY `translate`.`id`"
................................................................................
        cur.execute(sql_str, (pattern, self.user_id))
        return cur.fetchall()

    def command_import(self, import_name):
        """import user dict to CSV"""
        start = False
        cur = self.connect.cursor()
        uniqCount, dublCount = 0, 0
        try:
            read_csv = csv.reader(open(import_name, newline='', encoding='utf-8'), dialect='excel', delimiter=';', quoting=csv.QUOTE_ALL)
            with self.connect:
                for row in read_csv:
                    if not start:
                        start = True
                        continue
                    if len(row) < 2: continue
                    en = row[0]
                    ru = row[1]
                    # check term
                    token = hashlib.md5(bytes(prepare_str(en), 'utf-8')).hexdigest()
                    # check translate
                    cur.execute("SELECT `term` FROM `translate` WHERE `user_id`=(?) AND `term`=(?)", (self.user_id, token))
                    if not cur.fetchone():
                        # check term
                        cur.execute("SELECT `token` FROM `term` WHERE `token`=(?)", (token,))
                        if not cur.fetchone():
                            cur.execute("INSERT INTO `term` (`token`, `en`) VALUES ((?), (?))", (token, prepare_str(en)))
                        cur.execute("INSERT INTO `translate` (term,user_id,rus) VALUES (?,?,?)", (token, self.user_id, prepare_str(ru)))
                        translate_id = cur.lastrowid
                        cur.execute("INSERT INTO `progress` (`translate_id`) VALUES (?)", (translate_id,))
                        print("Added: {0}".format(en))
                        uniqCount += 1
                    else:
                        print("Dublicate record: {0}".format(en))

                        dublCount += 1
        except sqlite3.DatabaseError as er:



            self.prer(er)
            print("DB error for {0}".format(en))
        except (TypeError, IOError) as er:

            self.prer(er)

            print("Please write '.import import_file.csv'")
        else:

            print("Successfully import {0} new record(s), with {1} duplicates.".format(uniqCount, dublCount))
        cur.close()
        return 'import'

    def command_testen(self, arg):
        self.command_test(arg, 0)
        return 'test-en'
    def command_testru(self, arg):
        self.command_test(arg, 1)
        return 'test-ru'
    def command_testmix(self, arg):
        self.command_test(arg, 2)
        return 'test-mix'

    def command_test(self, arg=None, type_test=0):
        """start user test"""
        cur = self.connect.cursor()
        created = datetime.datetime.now()
        name = ''
        try:
            arg = int(arg) if arg else self.ctest
        except (TypeError, ValueError) as e:
            arg = self.ctest
        # start test
        types_test = ('en-ru', 'ru-en', 'mix')
        print("Start test, type: '{0}', count: {1}".format(types_test[type_test], arg))
        try:
            with self.connect:
                # insert `test`
                cur.execute("INSERT INTO `test` (`user_id`, `name`, `created`) VALUES (?,?,?)", (self.user_id, types_test[type_test], created))
                test_id = cur.lastrowid
                alreadyq, to_save, progress = [], [], []
                for i in range(1, arg+1):
                    # 0-en, 1-ru, 2-mix
                    question, answer, translate_id  = self.gen_question(cur, type_test, alreadyq)
                    if question is None:
                        print("too few words")
                        break
                    alreadyq.append(str(translate_id))
                    print('\nQuestion {0}: {1}'.format(i,question))
                    enter = input('translate: ')
                    # check error
                    er = False if check_ans(answer, enter) else True
                    result_row = {"test_id": test_id, "num": i, "question": question, 'answer': answer, 'enter': enter, 'error': er}
                    to_save.append(result_row)
                    progress_error = 1 if er else 0
                    progress.append({'translate_id': translate_id, 'error': progress_error})
                # save results
                cur.executemany("INSERT INTO `result` (`test_id`,`number`,`question`,`answer`,`enter`,`error`) VALUES (:test_id, :num, :question, :answer, :enter, :error)", to_save)
                # update test
                cur.execute("UPDATE `test` SET `finished`=(?) WHERE `id`=(?)", (datetime.datetime.now(), test_id))
                # update progress
                cur.executemany("UPDATE `progress` SET `all`=`all`+1, `error`=`error`+:error WHERE `translate_id`=:translate_id", progress)
        except sqlite3.DatabaseError as er:
            self.prer(er)
            print("Error")



        else:


            print("Test successfully finished, ID={}.".format(test_id))
            self.print_test_result(to_save, test_id, False)
        cur.close()

    def print_test_result(self, tests, test_id, print_right=False):
        """print test info"""
        right, error = 0, 0
        print("*******YOUR RESULT********")
        for q in tests:
            if q['error']:
                error += 1
                print("Q#{0} (Error): {1}\n[correct] {2}\n[you] {3}\n".format(q['num'],q['question'],q['answer'],q['enter']))
            else:
                right += 1
                if print_right:
                    print("Q#{0}: {1}\n[correct] {2}\n[you] {3}\n".format(q['num'],q['question'],q['answer'],q['enter']))
        print("**************************")
        print("Test ID={2}. Result: {0} error(s) from {1}".format(error,(right + error), test_id))

    def gen_question(self, cur, type_test, alreadyq):
        """genaration question for any test"""
        sql_list = "SELECT `translate`.`id`, `term`.`en`, `translate`.`rus` FROM `translate` LEFT JOIN `term` ON (`translate`.`term`=`term`.`token`) WHERE `translate`.`user_id`=" + str(self.user_id) + " AND (`translate`.`id` NOT IN (" + ", ".join(alreadyq) + "))"
        cur.execute(sql_list)
        for_search = cur.fetchall()
        if not for_search:
            return None, None, None
        row = for_search[random.randint(0,len(for_search)-1)]
        # 0 => en, 1 => ru, 2 => mix
................................................................................
        elif type_test == 1:
            question, answer = row[2], row[1]
        else:
            i = random.randint(1,2)
            j = 1 if i == 2 else 2
            question, answer = row[i], row[j]
        return question, answer, translate_id

    def command_testlist(self, arg=None):
        """print user tests"""
        global TEST_NUM
        cur = self.connect.cursor()
        try:
            arg = int(arg) if arg else TEST_NUM
            sql_list = "SELECT `test`.`id`, `test`.`name`, `test`.`created`, `test`.`finished` FROM `test` WHERE `test`.`user_id`=(?) ORDER BY `test`.`created` DESC, `test`.`finished` DESC LIMIT (?);"
            cur.execute(sql_list, (self.user_id, arg))
            i = 1
            print("Your tests:")
            for row in cur.fetchall():
                created = datetime.datetime.strptime(row[2], "%Y-%m-%d %H:%M:%S.%f")
                finished = datetime.datetime.strptime(row[3], "%Y-%m-%d %H:%M:%S.%f")
                cur.execute("SELECT COUNT(*) FROM `result` WHERE `result`.`test_id`=(?) AND `result`.`error`=1", (row[0],))
                results = cur.fetchone()
                error = results[0] if results else 0
                cur.execute("SELECT COUNT(*) FROM `result` WHERE `result`.`test_id`=(?)", (row[0],))
                results = cur.fetchone()
                allres = results[0] if results else 0
                print("{0}. ID={1}, type: {2}, {5} error(s) from {6}\n   created: {3}, finished: {4}\n".format(i, row[0], row[1], created.strftime("%d.%m.%Y %H:%M:%S"), finished.strftime("%d.%m.%Y %H:%M:%S"), error, allres))
                i +=1
        except (ValueError, TypeError) as er:
            self.prer(er)
            print("Error, use <.testlist N> (N - number)")
        except sqlite3.DatabaseError as er:
            self.prer(er)
            print("Error")
        cur.close()
        return 'tetslist'

    def command_testinfo(self, arg=None):
        cur = self.connect.cursor()
        try:
            test_id = int(arg)
            # test info
            sql_list = "SELECT `test`.`id`, `test`.`name`, `test`.`created`, `test`.`finished` FROM `test` WHERE `test`.`user_id`=(?) AND `test`.`id`=(?);"
            cur.execute(sql_list, (self.user_id, test_id))
            test = cur.fetchone()
            if not test:
                print("Empty test")
                return "test_info"
            created = datetime.datetime.strptime(test[2], "%Y-%m-%d %H:%M:%S.%f")
            finished = datetime.datetime.strptime(test[3], "%Y-%m-%d %H:%M:%S.%f")
            print("ID={0}, type: {1}, created: {2}, finished: {3}\n".format(test[0], test[1], created.strftime("%d.%m.%Y %H:%M:%S"), finished.strftime("%d.%m.%Y %H:%M:%S")))
            # results
            cur.execute("SELECT `result`.`number`, `result`.`question`, `result`.`answer`, `result`.`enter`, `result`.`error` FROM `result` WHERE `result`.`test_id`=(?) ORDER BY `result`.`number`;", (test_id,))
            for_print = []
            for row in cur.fetchall():
                for_print.append({'num': row[0], 'question': row[1], 'answer': row[2], 'enter': row[3], 'error': row[4]})
            self.print_test_result(for_print, test_id, True)
        except (ValueError, TypeError) as er:
            self.prer(er)
            print("Error, use <.tesinfo ID> (ID - number)")
        except sqlite3.DatabaseError as er:
            self.prer(er)
            print("Error")
        cur.close()
        return 'testinfo'

"ENGLISH";"RUSSIAN"
"ability";"способность"
"accidentally";"случайно"
"achieve";"достижения"
"appropriate";"соответствующие"
"arrangement";"расположение"
"can you play the guitar?";"вы можете играть на гитаре?"
"compared";"по сравнению"
"congestion";"перегруженность"
"consistency";"последовательность"
"conversation";"разговор"
"decide";"решение"
"derive";"извлечь"
"dialogue";"диалог"
"discuss";"обсудить"
"distinguished names";"различающиеся имена"
"essential";"важно"
"exceed";"превышать"
"exception";"исключение"
"explicit";"явный"
"famous";"известный"
"fashion";"мода"
"gauge";"измеритель"
"guitar";"гитара"
"hi";"привет"
"hierarchical";"иерархический"
"how do you do?";"как поживаете?"
"immediately";"немедленно"
"impose";"навязать"
"in conjuction";"в сочетании"
"inherit";"наследовать"
"interact";"взаимодействовать"
"invoke";"ссылаться"
"judo";"дзюдо"
"lack";"недостаток"
"leaf";"лист"
"mail";"почта"
"mistake";"ошибка"
"objectives";"цели"
"odd";"странно"
"persist";"сохраняться"
"prerequisite";"предпосылка"
"previously";"ранее"
"provisioning";"обеспечения"
"purpose";"цель"
"raise";"поднять"
"represent";"представлять"
"restaurant";"ресторан"
"riding a bike";"езда на велосипеде"
"sailing";"парусный спорт"
"sentences";"предложения"
"shortage";"дефицит"
"skill";"мастерство"
"to belong";"принадлежать"
"together";"вместе"
"vocabulary";"словарь"
"wish list";"список пожеланий"

"ENGLISH";"RUSSIAN"

    config = get_config_data(CONF_NAME)
    if not config['database'] or not os.path.exists(config['database']):
        print("Not fount SQLite database")
        return 1
    # get name
    user = config['defuser'] if config['defuser'] else input("User name:")
    # create object
    account = Condt(user, config['database'], config['defctest'])
    if not account:
        print('Validation error, by...')
        return 0
    print(WELCOM)
    while (True):
        conn_status = 'online' if account.online else 'offline'
        prefix = PREFIX.format(account.name, conn_status)

    config = get_config_data(CONF_NAME)
    if not config['database'] or not os.path.exists(config['database']):
        print("Not fount SQLite database")
        return 1
    # get name
    user = config['defuser'] if config['defuser'] else input("User name:")
    # create object
    account = Condt(user, config['database'], config['debug'], config['defctest'])
    if not account:
        print('Validation error, by...')
        return 0
    print(WELCOM)
    while (True):
        conn_status = 'online' if account.online else 'offline'
        prefix = PREFIX.format(account.name, conn_status)

	http://www.sqlite.org/
- Python3 
	http://www.python.org/
- Yandex translate 
	http://api.yandex.ru/translate/doc/dg/reference/translate.xml
- Fossil 
	http://www.fossil-scm.org/

	http://www.sqlite.org/
- Python3 
	http://www.python.org/
- Yandex translate 
	http://api.yandex.ru/translate/doc/dg/reference/translate.xml
- Fossil 
	http://www.fossil-scm.org/

Test user:
    login: test
    password: 123