dJ4E53sA

1
2from random import choice
3from string import ascii_lowercase
4import numpy as np
5import datetime
6import random
7import pymysql
8
9def get_db_connection():
10    db_connection = pymysql.connect(host = 'localhost',
11                                    user = 'root',
12                                    password = '1234',
13                                    db = 'mysql',
14                                    charset = 'utf8',
15                                    cursorclass = pymysql.cursors.DictCursor)
16    return db_connection
17
18def db_table_create():
19    db_connection = get_db_connection()
20    db_cursor = db_connection.cursor()
21    sql = '''CREATE TABLE IF NOT EXISTS testtable(
22                            date VARCHAR(30),
23                            english_string VARCHAR(30),
24                            russian_string VARCHAR(30),
25                            int_number INT(20),
26                            float_number FLOAT)'''
27    db_cursor.execute(sql)
28
29def unpack_line(line):
30    split_line = str.split(line, '||')
31    dt = split_line[0]
32    eng_str = split_line[1]
33    rus_str = split_line[2]
34    int_num = split_line[3]
35    float_num = split_line[4].split('\n')
36    return dt, eng_str, rus_str, int_num, float_num[0]
37
38def random_cyrillic_str(random_str_length):
39    ALPHABET = 'абвгдеёжзийклмнопрстуфхцчшщъыьэюя'
40    ALPHABET_LENGTH = str.__len__(ALPHABET) - 1
41    cyrillic_string = ""
42    for i in range(random_str_length):
43        temp_rand = random.randint(0, ALPHABET_LENGTH)
44        cyrillic_string += ALPHABET[temp_rand]
45    return cyrillic_string
46
47def random_date(today_date, delta):
48    return datetime.date.fromordinal(today_date - random.randint(0, delta))
49
50def delta_date(year_difference):
51    today_date = datetime.date.today()
52    late_date = datetime.date(today_date.year - year_difference, today_date.month, today_date.day)
53    today_date = datetime.datetime.toordinal(today_date)
54    late_date = datetime.datetime.toordinal((late_date))
55    delta = today_date - late_date
56    return today_date, delta
57
58def file_merger(file_quantity):
59    merged_file = open('./txt_folder/merged_file.txt', 'w')
60    yes_answer = False
61    sequence_to_delete = ''
62    deleted_str_counter = 0
63    answer = input('Do you like to delete something while merging? y\\n\n')
64    if answer == 'y':
65        sequence_to_delete = input('Input sequence you want to delete\n')
66        yes_answer = True
67    for file_counter in range(file_quantity):
68        temp_file = open('./txt_folder/test_file' + str(file_counter + 1) + '.txt', 'r')
69        lines = temp_file.readlines()
70        temp_file.close()
71        temp_file = open('./txt_folder/test_file' + str(file_counter + 1) + '.txt', 'w')
72        for line in lines:
73            if yes_answer:
74                if sequence_to_delete in line:
75                    deleted_str_counter += 1
76                else:
77                    merged_file.write(line)
78                    temp_file.write(line)
79            else:
80                merged_file.write(line)
81                temp_file.write(line)
82    if yes_answer:
83        print('Amount of deleted lines with \'' + sequence_to_delete + '\' sequence: ' + str(deleted_str_counter) + '.\n')
84    merged_file.close()
85    return deleted_str_counter
86
87def file_writer(file_quantity, line_quantity, year_difference, random_cyrillic_str_length, random_latin_str_length):
88    today_date, delta = delta_date(year_difference)
89    for file_counter in range(file_quantity):
90        temp_file = open('./txt_folder/test_file' + str(file_counter + 1) + '.txt', 'w')
91        for line_counter in range(line_quantity):
92            temp_file.write(str(random_date(today_date, delta)))
93            temp_file.write('||')
94            temp_file.write(''.join(choice(ascii_lowercase) for i in range(random_latin_str_length)))
95            temp_file.write('||')
96            temp_file.write(random_cyrillic_str(random_cyrillic_str_length))
97            temp_file.write('||')
98            temp_file.write(str(random.randrange(1, 10e7, 2)))
99            temp_file.write('||')
100            temp_file.write((str('{:.8f}'.format(random.random() + float(random.randint(1, 19))))))
101            temp_file.write('\n')
102        temp_file.close()
103
104def select_int_from_db():
105    db_connection = get_db_connection()
106    db_cursor = db_connection.cursor()
107    sql = '''SELECT int_number FROM testtable'''
108    db_cursor.execute(sql)
109    selected_int_from_db = []
110    db_dict_list = db_cursor.fetchall()
111    for item in db_dict_list:
112        selected_int_from_db.append(item['int_number'])
113    return selected_int_from_db
114
115def select_float_from_db():
116    db_connection = get_db_connection()
117    db_cursor = db_connection.cursor()
118    sql = 'SELECT float_number FROM testtable'
119    db_cursor.execute(sql)
120    selected_float_from_db = []
121    db_dict_list= db_cursor.fetchall()
122    for item in db_dict_list:
123        selected_float_from_db.append(item['float_number'])
124    return selected_float_from_db
125
126def selected_int_sum():
127    selected_int = select_int_from_db()
128    return sum(selected_int)
129
130def selected_float_median():
131    selected_float = select_float_from_db()
132    return np.median(selected_float)
133
134def folder_writer():
135    try:
136        os.makedirs('./txt_folder')
137    except OSError:
138        print('Folder already exist.')
139
140def db_date_entry(rows_left_to_import):
141    rows_imported = 0
142    db_connection = get_db_connection()
143    try:
144        db_cursor = db_connection.cursor()
145        if ('SELECT COUNT(*) from testtable') == 0:
146            pass
147        else:
148            sql = 'DROP TABLE testtable'
149        db_cursor.execute(sql)
150        db_table_create()
151        merged_file = open('./txt_folder/merged_file.txt', 'r')
152        while True:
153            line = merged_file.readline()
154            if line == "":
155                print('Rows imported:   ' + str(rows_imported) + '. Rows left to import:   ' + str(rows_left_to_import) + '.')
156                break
157            print('Rows imported:   ' + str(rows_imported) + '. Rows left to import:   ' + str(rows_left_to_import) + '.')
158            date_line, english_str, cyrillic_str, int_num, float_num = unpack_line(line)
159            sql_item = '''INSERT INTO testtable(date, english_string, russian_string, int_number, float_number)
160                            VALUES('%(date)s', '%(english_string)s', '%(russian_string)s', '%(int_number)s', '%(float_number)s')
161                            ''' % {'date': date_line, 'english_string': english_str, 'russian_string': cyrillic_str,
162                                   'int_number': int_num, 'float_number': float_num}
163            db_cursor.execute(sql_item)
164            db_connection.commit()
165            rows_left_to_import -= 1
166            rows_imported += 1
167    finally:
168        db_connection.close()
169    merged_file.close()
170
171
172
173if __name__ == '__main__':
174    FILE_QUANTITY = 10
175    LINE_QUANTITY = 10
176    TOTAL_ROW_QUANTITY = FILE_QUANTITY * LINE_QUANTITY
177    YEAR_DIFFERENCE = 5
178    RANDOM_LATIN_STR_LENGTH = 10
179    RANDOM_CYRILLIC_STR_LENGTH = 10
180
181
182    folder_writer()
183    file_writer(FILE_QUANTITY, LINE_QUANTITY, YEAR_DIFFERENCE, RANDOM_CYRILLIC_STR_LENGTH, RANDOM_LATIN_STR_LENGTH)
184    print("Merging files.")
185    print('Answer via console')
186    rows_left_to_import = (TOTAL_ROW_QUANTITY - file_merger(FILE_QUANTITY))
187    db_table_create()
188    db_date_entry(rows_left_to_import)
189    while True:
190        answer = input('Would you like to continue? y/n\n')
191        if answer == 'y':
192            answer = input("""Input what do you like to do?
193            merge
194            import to DateBase
195            take sum of integer number
196            take median of float numbers\n\n""")
197            if answer == 'merge':
198                rows_left_to_import -= file_merger(FILE_QUANTITY)
199            elif answer == 'import':
200                db_date_entry(rows_left_to_import)
201            elif answer == 'sum':
202                print('Sum of int number from Date Base: ' + str(selected_int_sum()))
203            elif answer == 'median' :
204                print('Median of float number from Date Base: ' + str(selected_float_median()))
205        else:
206            break