ConDict

Check-in [ecc710b2d0]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:merge with testing
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:ecc710b2d0bc47718f5b2c2b9fe5b09ca439dde4
User & Date: zorro 2012-10-13 15:36:24
Context
2012-10-17
17:54
merge main functionality Leaf check-in: 45df65e986 user: zorro tags: trunk
2012-10-13
15:36
merge with testing check-in: ecc710b2d0 user: zorro tags: trunk
2012-10-12
12:21
default command = en-ru translate check-in: 53a4d2e45c user: alzay tags: testing
2012-09-18
18:52
tests done check-in: 2928daa896 user: zorro tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to aside.py.

1
2
3
4
5
6
7
8
9
10
11
12
13

14
15
16
17


18

19

20


21
22
23
24
25
26
27
..
36
37
38
39
40
41
42
43

44
45
46
47
48
49
50
51
52
..
54
55
56
57
58
59
60

61
62
63
64
65
66
67
68
69
70

71
72
73
#!/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)




|

|




<

>
|



>
>
|
>
|
>
|
>
>







 







|
>

|







 







>










>



1
2
3
4
5
6
7
8
9
10
11

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
..
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
..
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
#!/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)

Changes to condt.conf.

1
2


3
4
5
6
[database]
dbname=db.sqlite



[user]
default_user=zorro
test_count=10


>
>


|

1
2
3
4
5
6
7
8
[database]
dbname=db.sqlite
# developing mode (on/off, 1/0, yes/no)
debug=yes

[user]
default_user=test
test_count=10

Changes to condt.py.

3
4
5
6
7
8
9

10
11
12
13
14
15
16
17


18
19
20
21
22
23
24
25
26
27
28
29



30
31
32
33
34
35
36
..
58
59
60
61
62
63
64




65
66
67
68
69
70
71
72
73
74

75
76
77
78
79
80
81
82
83
84
85
86
87

88
89
90
91
92
93
94
..
97
98
99
100
101
102
103


104
105

106
107
108
109
110


111
112


113

114
115
116
117

118
119
120

121
122
123
124

125
126
127
128
129
130
131
132
...
136
137
138
139
140
141
142

143
144
145
146
147
148
149
150
151
152

153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171

172
173

174



175
176
177
178

179
180
181
182
183
184
185
186
187
188
189
190
191

192
193
194

195
196
197
198
199
200
201

202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217

218
219
220
221
222
223
224
225
226
227

228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245

246
247
248

249
250
251







252
253
254
255
256
257
258
259
260

261
262
263
264
265
266
267
...
279
280
281
282
283
284
285









286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327














328
329
330
331
332
333
334
335
336
337
338
339

340
341

342
343
344

345
346
347
348
349
350
351
...
356
357
358
359
360
361
362

363
364
365
366
367
368
369

370
371
372
373
374
375
376
377
378
...
386
387
388
389
390
391
392

393
394
395
396
397
398
399
400
401
402

403
404
405
406
407
408
409
410
411
412
413
414

415
416
417
418
419
420
421
...
432
433
434
435
436
437
438

439
440
441
442
443
444
445
446
447
448

449
450
451
452
453
454
455
456
457
458
459
460

461
462
463
464
465
466
467
468
469
470
471
472
473

474
475
476
477
478
479
480
...
481
482
483
484
485
486
487

488
489

490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509

510
511
512

513
514
515
516

517
518
519
520
521
522
523
524

525
526
527
528
529
530
531
532
533
534
535
536
537
538

539
540
541
542
543
544
545
546
547
548
549

550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575

576
577
578
579
580
581
582
583
584

585
586

587
588
589
590
591
592
593
594


595
596
597
598

599
600
601
602
603
604
605
...
609
610
611
612
613
614
615
616




























































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



































































>







|
>
>












>
>
>







 







>
>
>
>

|
|







>




|
<







>







 







>
>


>





>
>


>
>
|
>

|


>



>




>
|







 







>










>
|



<
<













>


>
|
>
>
>
|



>













>



>







>
|






<
<
|
|
|




>










>
|



|




<
<
|
|
|




>



>



>
>
>
>
>
>
>









>







 







>
>
>
>
>
>
>
>
>






|
|
|
|
<
<





|
|
<
<
<
<
<
<
|




|





|
<
|
|


>
>
>
>
>
>
>
>
>
>
>
>
>
>







<




>
|
|
>



>







 







>
|

|
<
|


>
|
<







 







>
|
|
|
|
|
|
|
|
|

>

<
<

|

<
<

<
<

>







 







>
|
|
|
|
|
|
|
|
|
|
>

<
<

|

<
<

<
<

>













>







 







>


>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>
|
|
<
>
|
<
<
<
>


<
|
<


<
>
|













>











>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>

<
<
<

<
<
|
|
>

|
>

|



|


>
>

|


>







 








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
..
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90

91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
...
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
...
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180


181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239


240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267


268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
...
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343


344
345
346
347
348
349
350






351
352
353
354
355
356
357
358
359
360
361
362

363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387

388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
...
411
412
413
414
415
416
417
418
419
420
421

422
423
424
425
426

427
428
429
430
431
432
433
...
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460


461
462
463


464


465
466
467
468
469
470
471
472
473
...
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503


504
505
506


507


508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
...
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564

565
566



567
568
569

570

571
572

573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628



629


630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
...
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727

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'

Changes to db.sqlite.

cannot compute difference between binary files

Changes to dict.csv.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
"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";"список пожеланий"

<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
1
























































"ENGLISH";"RUSSIAN"
























































Changes to main.py.

18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
    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)







|







18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
    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)

Changes to readme.

4
5
6
7
8
9
10




	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/











>
>
>
>
4
5
6
7
8
9
10
11
12
13
14
	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