kWL9Qi8L

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