wfidWitC

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    '''Connect to the database'''
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    '''Create new Table to the datebase'''
24    sql = '''CREATE TABLE IF NOT EXISTS testtable(
25                            date VARCHAR(30),
26                            english_string VARCHAR(30),
27                            russian_string VARCHAR(30),
28                            int_number INT(20),
29                            float_number FLOAT)'''
30    db_cursor.execute(sql)
31
32def unpack_line(line):
33    '''String splitting into tokens separated by ||
34       returns values from input string'''
35    split_line = str.split(line, '||')
36    dt = split_line[0]
37    eng_str = split_line[1]
38    rus_str = split_line[2]
39    int_num = split_line[3]
40    float_num = split_line[4].split('\n')
41    return dt, eng_str, rus_str, int_num, float_num[0]
42
43def random_cyrillic_str(random_str_length):
44    '''Formation random Cyrillic string with 'random_str_lengh' number of symbols
45       returns formed string'''
46    ALPHABET = 'абвгдеёжзийклмнопрстуфхцчшщъыьэюя'
47    ALPHABET_LENGTH = str.__len__(ALPHABET) - 1
48    cyrillic_string = ""
49    for i in range(random_str_length):
50        temp_rand = random.randint(0, ALPHABET_LENGTH)
51        cyrillic_string += ALPHABET[temp_rand]
52    return cyrillic_string
53
54def random_date(today_date, delta):
55    '''Returns random data for last delta years'''
56    return datetime.date.fromordinal(today_date - random.randint(0, delta))
57
58def delta_date(year_difference):
59    '''Returns today date and delta = number of days
60       the difference between today and x years earlier'''
61    today_date = datetime.date.today()
62    late_date = datetime.date(today_date.year - year_difference, today_date.month, today_date.day)
63    today_date = datetime.datetime.toordinal(today_date)
64    late_date = datetime.datetime.toordinal((late_date))
65    delta = today_date - late_date
66    return today_date, delta
67
68def file_merger(file_quantity):
69    '''Merging all files from 'txt_folder'
70       while merging may delete some custom sequences
71       returns number of deleted rows'''
72    merged_file = open('./txt_folder/merged_file.txt', 'w')
73    yes_answer = False
74    sequence_to_delete = ''
75    deleted_str_counter = 0
76    answer = input('Do you like to delete something while merging? y\\n\n')
77    if answer == 'y':
78        sequence_to_delete = input('Input sequence you want to delete\n')
79        yes_answer = True
80    for file_counter in range(file_quantity):
81        temp_file = open('./txt_folder/test_file' + str(file_counter + 1) + '.txt', 'r')
82        lines = temp_file.readlines()
83        temp_file.close()
84        temp_file = open('./txt_folder/test_file' + str(file_counter + 1) + '.txt', 'w')
85        for line in lines:
86            if yes_answer:
87                if sequence_to_delete in line:
88                    deleted_str_counter += 1
89                else:
90                    merged_file.write(line)
91                    temp_file.write(line)
92            else:
93                merged_file.write(line)
94                temp_file.write(line)
95    if yes_answer:
96        print('Amount of deleted lines with \'' + sequence_to_delete + '\' sequence: ' + str(deleted_str_counter) + '.\n')
97    merged_file.close()
98    return deleted_str_counter
99
100def file_writer(FILE_QUANTITY, LINE_QUANTITY, YEAR_DIFFERENCE, RANDOM_CYRILLIC_STR_LENGTH, RANDOM_LATIN_STR_LENGTH):
101    '''Create and fills files with the following row-structure:
102                            random date for the last 'YEAR_DIFFERENCE' years
103                            string with random latin letters 'RANDOM_LATIN_STR_LENGTH' length
104                            string with random cyrrilic letters 'RANDOM_CYRILLIC_STR_LENGTH' length
105                            random number in range from 1 to 100 000 000
106                            random number with 8 decimal places in range from 1 to 20'''
107    today_date, delta = delta_date(YEAR_DIFFERENCE)
108    for file_counter in range(FILE_QUANTITY):
109        temp_file = open('./txt_folder/test_file' + str(file_counter + 1) + '.txt', 'w')
110        for line_counter in range(LINE_QUANTITY):
111            temp_file.write(str(random_date(today_date, delta)))
112            temp_file.write('||')
113            temp_file.write(''.join(choice(ascii_lowercase) for i in range(RANDOM_LATIN_STR_LENGTH)))
114            temp_file.write('||')
115            temp_file.write(random_cyrillic_str(RANDOM_CYRILLIC_STR_LENGTH))
116            temp_file.write('||')
117            temp_file.write(str(random.randrange(1, 10e7, 2)))
118            temp_file.write('||')
119            temp_file.write((str('{:.8f}'.format(random.random() + float(random.randint(1, 19))))))
120            temp_file.write('\n')
121        temp_file.close()
122
123def select_int_from_db():
124    '''Takes out a field 'int_number' from datebase with sql-inquiry, returns it values'''
125    db_connection = get_db_connection()
126    db_cursor = db_connection.cursor()
127    sql = '''SELECT int_number FROM testtable'''
128    db_cursor.execute(sql)
129    selected_int_from_db = []
130    db_dict_list = db_cursor.fetchall()
131    for item in db_dict_list:
132        selected_int_from_db.append(item['int_number'])
133    return selected_int_from_db
134
135def select_float_from_db():
136    '''Takes out a field 'float_number' from datebase with sql-inquiry, returns it values'''
137    db_connection = get_db_connection()
138    db_cursor = db_connection.cursor()
139    sql = 'SELECT float_number FROM testtable'
140    db_cursor.execute(sql)
141    selected_float_from_db = []
142    db_dict_list= db_cursor.fetchall()
143    for item in db_dict_list:
144        selected_float_from_db.append(item['float_number'])
145    return selected_float_from_db
146
147def selected_int_sum():
148    '''Calculate sum of all 'int_number' from datebase'''
149    selected_int = select_int_from_db()
150    return sum(selected_int)
151
152def selected_float_median():
153    '''Calculate median of all 'float_numbe' from datebase'''
154    selected_float = select_float_from_db()
155    return np.median(selected_float)
156
157def folder_writer():
158    '''Create folder in directory'''
159    try:
160        os.makedirs('./txt_folder')
161    except OSError:
162        print('Folder already exist.')
163
164def db_date_entry(rows_left_to_import):
165    '''Enters date from 'merged_file' into the datebase table with sql-inquiry
166       shows process of entering '''
167    rows_imported = 0
168    db_connection = get_db_connection()
169    try:
170        db_cursor = db_connection.cursor()
171        if ('SELECT COUNT(*) from testtable') == 0:
172            pass
173        else:
174            sql = 'DROP TABLE testtable'
175        db_cursor.execute(sql)
176        db_table_create()
177        merged_file = open('./txt_folder/merged_file.txt', 'r')
178        while True:
179            line = merged_file.readline()
180            if line == "":
181                print('Rows imported:   ' + str(rows_imported) + '. Rows left to import:   ' + str(rows_left_to_import) + '.')
182                break
183            print('Rows imported:   ' + str(rows_imported) + '. Rows left to import:   ' + str(rows_left_to_import) + '.')
184            date_line, english_str, cyrillic_str, int_num, float_num = unpack_line(line)
185            sql_item = '''INSERT INTO testtable(date, english_string, russian_string, int_number, float_number)
186                            VALUES('%(date)s', '%(english_string)s', '%(russian_string)s', '%(int_number)s', '%(float_number)s')
187                            ''' % {'date': date_line, 'english_string': english_str, 'russian_string': cyrillic_str,
188                                   'int_number': int_num, 'float_number': float_num}
189            db_cursor.execute(sql_item)
190            db_connection.commit()
191            rows_left_to_import -= 1
192            rows_imported += 1
193    finally:
194        db_connection.close()
195    merged_file.close()
196
197
198
199if __name__ == '__main__':
200    FILE_QUANTITY = 10
201    LINE_QUANTITY = 10
202    TOTAL_ROW_QUANTITY = FILE_QUANTITY * LINE_QUANTITY
203    YEAR_DIFFERENCE = 5
204    RANDOM_LATIN_STR_LENGTH = 10
205    RANDOM_CYRILLIC_STR_LENGTH = 10
206
207
208    folder_writer()
209    file_writer(FILE_QUANTITY, LINE_QUANTITY, YEAR_DIFFERENCE, RANDOM_CYRILLIC_STR_LENGTH, RANDOM_LATIN_STR_LENGTH)
210    print("Merging files.")
211    print('Answer via console')
212    rows_left_to_import = (TOTAL_ROW_QUANTITY - file_merger(FILE_QUANTITY))
213    db_table_create()
214    db_date_entry(rows_left_to_import)
215    while True:
216        answer = input('Would you like to continue? y/n\n')
217        if answer == 'y':
218            answer = input("""Input what do you like to do?
219            merge
220            import to DateBase
221            take sum of integer number
222            take median of float numbers\n\n""")
223            if answer == 'merge':
224                rows_left_to_import -= file_merger(FILE_QUANTITY)
225            elif answer == 'import':
226                db_date_entry(rows_left_to_import)
227            elif answer == 'sum':
228                print('Sum of int number from Date Base: ' + str(selected_int_sum()))
229            elif answer == 'median' :
230                print('Median of float number from Date Base: ' + str(selected_float_median()))
231        else:
232            break