ืคืจืืืกืืืจืื
ืื ืงืจื ืฉืืฉืจืช ืืืชืงืฃ ืขื ืืื ืืืจืืก ืืืคืจ, ืืฉืจ, ื"ืชืืื ื ืืื", ืืฉืืืจ ืืืงืืช ืืช ืงืืฆื ื-.ibd (ืงืืฆื ื ืชืื ืื ืืืืืืื ืฉื ืืืืืืช innodb), ืื ืืืงืืื ืืฆืคืื ืืืืืืื ืืช ืงืืฆื ื-.fpm ( ืงืืฆื ืืื ื). ืืืงืจื ืื, ื ืืชื ืืืืง ืืช .idb ื:
- ืืคืืฃ ืืฉืืืืจ ืืืืฆืขืืช ืืืื ืืืืจืืืื ืกืื ืืจืืืื. ืืืงืจืื ืืืื, ืืฉ ืืฆืืื
ืืคืื ; - ืืืืืืช ืืืฆืคื ืืช ืืืงืืช. ืืจืื ืืืืืจ ืืืืืืืช ืืืืืืช, ืฉืขืืืจื (ืืคื ืฉืืื ืชื) ืื ืืื ืืชืืงืคืื ืืกืคืืง ืืืืจืื RAM ืืืฆืคื ื ืืืื;
- ืืืื, ืืืืืืช ืืืฆืคื ืืช ืืืืืืื ืฉืื ื ืืชื ืืฉืืืจ.
ื ืืชื ืืื ืืงืืืข ืืืืื ืืคืฉืจืืช ืฉืืืืื ืืืืืืืช ืคืฉืื ืขื ืืื ืคืชืืืชื ืืื ืขืืจื ืืงืกื ืืงืืืื ืืจืฆืื (ืืืงืจื ืฉืื ืื UTF8) ืืคืฉืื ืืฆืคืืช ืืงืืืฅ ืขื ื ืืืืืชื ืฉื ืฉืืืช ืืงืกื, ืืืฉื:
ืืื ืื, ืืชืืืืช ืืงืืืฅ ื ืืชื ืืฆืคืืช ืืืกืคืจ ืจื ืฉื 0 ืืชืื, ืืืจืืกืื ืืืฉืชืืฉืื ืืืืืืจืืชื ืืืฆืคื ื ืืืกืืื (ืื ืคืืฅ ืืืืชืจ) ืืฉืคืืขืื ืืืจื ืืื ืื ืขืืืื.
ืืืงืจื ืฉืื, ืืชืืงืคืื ืืฉืืืจื ืืืจืืืช ืฉื 4 ืืชืื (1, 0, 0, 0) ืืกืืฃ ืื ืงืืืฅ ืืืฆืคื, ืื ืฉืคืฉื ืืช ืืืฉืืื. ืืื ืืืคืฉ ืงืืฆืื ืื ื ืืืขืื, ืืกืงืจืืคื ืืกืคืืง:
def opened(path):
files = os.listdir(path)
for f in files:
if os.path.isfile(path + f):
yield path + f
for full_path in opened("C:somepath"):
file = open(full_path, "rb")
last_string = ""
for line in file:
last_string = line
file.close()
if (last_string[len(last_string) -4:len(last_string)]) != (1, 0, 0, 0):
print(full_path)
ืื, ืืชืืจืจ ืืืฆืื ืงืืฆืื ืืฉืืืืื ืืกืื ืืจืืฉืื. ืืฉื ื ืืจืื ืืืจืื ืขืืืื ืืื ืืช, ืืื ืื ืฉื ืืฆื ืืืจ ืืกืคืืง. ืืื ืืืื ืืกืืจ, ืืื ืืชื ืฆืจืื ืืืขืช ืืื ื ืืืืืง ืืืืืืื ื(ืืืืื) ืขืื ืืงืจื ืฉื ืืืฆืชื ืืขืืื ืขื ืฉืืืื ืืชืืืฃ ืชืืืจ. ืืืฉ ืื ืืืจ ืื ืกืื ืืฉืื ืฉืื ื ืื ื ืืกืคื ืขืืืื ืืืฉื.
Wilds City, ืืืจืื ืืฆืขืจ, ืื ืืืื ืืขืืืจ ืืืงืจื ืืื, ืืืื ืืืืจ ืื ื ืืชื.
ืงืจืื ืืืชืจ ืืขื ืืื
ืืฉ ืืื ื ืฉื ืืืื ืืืคื ื 3 ืืืืฉืื ืฉืืื ื ืชืืื ืืช ืื ืืืืืช (ืืคืฉืจ ืฉืื ืืื, ืืืืื ืืืชืจ). ืืื ื ืืืืื:
CREATE TABLE `table_1` (
`id` INT (11),
`date` DATETIME ,
`description` TEXT ,
`id_point` INT (11),
`id_user` INT (11),
`date_start` DATETIME ,
`date_finish` DATETIME ,
`photo` INT (1),
`id_client` INT (11),
`status` INT (1),
`lead__time` TIME ,
`sendstatus` TINYINT (4)
);
ืืืงืจื ืื, ืขืืื ืืืืฅ:
id_point
int(11);id_user
int(11);date_start
ืชืืจืื ืฉืขื;date_finish
ืชืืจืื ืฉืขื.
ืืฆืืจื ืฉืืืืจ, ื ืขืฉื ืฉืืืืฉ ืื ืืชืื ืืชืื ืืืจ ืืชืื ืฉื ืงืืืฅ ื-.ibd, ืืืืืจ ืืื ืืืจืชื ืืฆืืจื ืงืจืื ืืืชืจ. ืืืืืื ืฉืืื ืืืฆืื ืืช ืื ืฉืื ืื ื ืฆืจืืืื, ืื ืื ื ืฆืจืืืื ืจืง ืื ืชื ืกืืื ื ืชืื ืื ืืื int ื-datatime, ืืืืืจ ืืชืืจ ืจืง ืืืชื, ืืื ืืคืขืืื ื ืชืืืืก ืื ืืกืืื ื ืชืื ืื ืืืจืื, ืฉืืืืืื ืืขืืืจ ืืชืงืจืืืช ืืืืืช ืืืจืืช.
ืืขืื 1: ืืฉืืืช ืขื ืืกืืืื DATETIME ื-TEXT ืืื ืขืจืื NULL, ืืื ืคืฉืื ืืืืืื ืืงืืืฅ, ืืืื ืื, ืื ื ืืชื ืืื ืืงืืืข ืืช ืืืื ื ืืฉืืืืจ ืืืงืจื ืฉืื. ืืขืืืืืช ืืืืฉืืช, ืขืจื ืืจืืจืช ืืืืื ืืื null, ืืืืง ืืืขืกืงื ืขืืื ืืืืช ืืืืืื ืขืงื ืืืืืจื innodb_flush_log_at_trx_commit = 0, ืื ืฉืืืื ืฆืืจื ืืืฉืงืืข ืืื ื ืืกืฃ ืืื ืืงืืืข ืืช ืืืื ื.
ืืขืื 2: ืืฉ ืืงืืช ืืืฉืืื ืฉืฉืืจืืช ืฉื ืืืงื ืืจื DELETE ืืืื ืืืื ืืงืืืฅ ibd, ืืื ืขื ALTER TABLE ืืืื ื ืฉืืื ืื ืืชืขืืื. ืืชืืฆืื ืืื, ืืื ื ืื ืชืื ืื ืืืื ืืืฉืชื ืืช ืืชืืืืช ืืงืืืฅ ืืขื ืกืืคื. ืื ืืชื ืืฉืชืืฉ ืืขืชืื ืงืจืืืืช ื- OPTIMIZE TABLE, ืื ืกืืืจ ืฉืชืชืงื ืืืขืื ืืื.
ืฉืื ืื, ืืจืกืช DBMS ืืฉืคืืขื ืขื ืืืืคื ืฉืื ื ืชืื ืื ืืืืืกื ืื, ืืืืชืื ืฉืืืืื ืื ืื ืชืขืืื ืขืืืจ ืืจืกืืืช ืขืืงืจืืืช ืืืจืืช. ืืืงืจื ืฉืื, ื ืขืฉื ืฉืืืืฉ ืืืจืกืช Windows ืฉื mariadb 10.1.24. ืืื ืื, ืืืจืืช ืฉื-mariadb ืืชื ืขืืื ืขื ืืืืืืช InnoDB, ืืืขืฉื ืื ืื
ื ืืชืื ืงืืฆืื
ื-python, ืกืื ื ืชืื ืื
ืืืืจ ืขืืื ืืืกืคืจ ืงืืฆื ibd, ืชืืื ืืืฆืื ืืช ืืืืจืื ืืืืื:
ืืชืจื ืืื, ืื ืชืืืง ืืช ืืงืืืฅ ืืืืืืช ืืคืชื ืืื, ืชืงืื ืืขืืงืจ ืืืืงืื ืืืืืื ืฉื ื ืชืื ืื. ื ืฉืชืืฉ ื-infimum ืืืืืง.
table = table.split("infimum".encode())
ืชืฆืคืืช ืืขื ืืื ืช: ืืืืืืืช ืขื ืืืืช ื ืชืื ืื ืงืื ื, ืืื infimum ื-supremum ืืฉ ืืฆืืืข ืืืกืคืจ ืืฉืืจืืช ืืืืืง.
- ืืืืช ืืืืงืืช ืขื ืฉืืจื 1
- ืืืืช ืืืืงื ืขื 2 ืฉืืจืืช
ื ืืชื ืืืื ืขื ืืืืช ืืขืจื ืืฉืืจืืช[0]. ืืืืจ ืฉืขืืื ืชื ืื, ืขืืืื ืื ืืฆืืืชื ืืืฆืื ืืช ื ืชืื ื ืืืืื ืืืืืืืื. ืกืืืจ ืืื ืื ืฉืืืืง ืื ืืฉืืฉ ืืืืกืื ืืื ืืงืกืื ืืืคืชืืืช.
ืืื ืืืืื[1] ืืืชืจืื ืืืชื ืืืขืจื ืืกืคืจื, ืืชื ืืืจ ืืืื ืืืืืื ืืืื ืืคืืกืื, ืืืืืจ:
ืืื ืื ืขืจืื int ืืืืืืกื ืื ืืืืจืืืช. ืืืืช ืืจืืฉืื ืืฆืืื ืื ืืืกืคืจ ืืืืื ืื ืฉืืืื. ืืืงืจื ืฉืื, ืื ืืืกืคืจืื ืืืืืืื. ืืชืื 3 ืืืชืื ืื ืืชืจืื, ืืชื ืืืื ืืงืืืข ืืช ืืืกืคืจ ืืืืฆืขืืช ืืคืื ืงืฆืื ืืืื. ืชึทืกืจึดืื:
def find_int(val: str): # example '128, 1, 2, 3'
val = [int(v) for v in val.split(", ")]
result_int = val[1]*256**2 + val[2]*256*1 + val[3]
return result_int
ืืืืืื, 128, 0, 0, 1 = 1ืื 128, 0, 75, 108 = 19308.
ืืืืื ืืื ืืคืชื ืจืืฉื ืขื ืืืืื ืืืืืืืืช, ืื ืืชื ืืืฆืื ืืืชื ืื ืืื
ืืืืจ ืืฉืืืื ืืื ืื ืชืื ืื ืืืืืืืช ืืืืืงื, ืืชืืจืจ ืฉืืืืืืงื DATETIME ืืืจืื ื-5 ืืชืื ืืืื ื-153 (ืืื ืื ืจืื ืืฆืืืข ืขื ืืจืืืืื ืฉื ืชืืื). ืืืืืื ืฉืืืื DATTIME ืืื '1000-01-01' ืขื '9999-12-31', ืื ื ืืืฉื ืฉืืกืคืจ ืืืชืื ืขืฉืื ืืืฉืชื ืืช, ืืื ืืืงืจื ืฉืื, ืื ืชืื ืื ื ืืคืืื ืืชืงืืคื ืฉืืื 2016 ื-2019, ืื ื ื ืื ืืกืคืืง 5 ืืชืื.
ืืื ืืงืืืข ืืช ืืืื ืืื ืฉื ืืืช, ื ืืชืื ืืคืื ืงืฆืืืช ืืืืืช. ืชึทืกืจึดืื:
day_ = lambda x: x % 64 // 2 # {x,x,X,x,x }
def hour_(x1, x2): # {x,x,X1,X2,x}
if x1 % 2 == 0:
return x2 // 16
elif x1 % 2 == 1:
return x2 // 16 + 16
else:
raise ValueError
min_ = lambda x1, x2: (x1 % 16) * 4 + (x2 // 64) # {x,x,x,X1,X2}
ืื ื ืืชื ืืื ืืืชืื ืคืื ืงืฆืื ืคืื ืงืฆืืื ืืืช ืขืืืจ ืืฉื ื ืืืืืืฉ, ืื ื ืืืฆืชื ืืคืจืืฅ ืืืชื. ืชึทืกืจึดืื:
ym_list = {'2016, 1': '153, 152, 64', '2016, 2': '153, 152, 128',
'2016, 3': '153, 152, 192', '2016, 4': '153, 153, 0',
'2016, 5': '153, 153, 64', '2016, 6': '153, 153, 128',
'2016, 7': '153, 153, 192', '2016, 8': '153, 154, 0',
'2016, 9': '153, 154, 64', '2016, 10': '153, 154, 128',
'2016, 11': '153, 154, 192', '2016, 12': '153, 155, 0',
'2017, 1': '153, 155, 128', '2017, 2': '153, 155, 192',
'2017, 3': '153, 156, 0', '2017, 4': '153, 156, 64',
'2017, 5': '153, 156, 128', '2017, 6': '153, 156, 192',
'2017, 7': '153, 157, 0', '2017, 8': '153, 157, 64',
'2017, 9': '153, 157, 128', '2017, 10': '153, 157, 192',
'2017, 11': '153, 158, 0', '2017, 12': '153, 158, 64',
'2018, 1': '153, 158, 192', '2018, 2': '153, 159, 0',
'2018, 3': '153, 159, 64', '2018, 4': '153, 159, 128',
'2018, 5': '153, 159, 192', '2018, 6': '153, 160, 0',
'2018, 7': '153, 160, 64', '2018, 8': '153, 160, 128',
'2018, 9': '153, 160, 192', '2018, 10': '153, 161, 0',
'2018, 11': '153, 161, 64', '2018, 12': '153, 161, 128',
'2019, 1': '153, 162, 0', '2019, 2': '153, 162, 64',
'2019, 3': '153, 162, 128', '2019, 4': '153, 162, 192',
'2019, 5': '153, 163, 0', '2019, 6': '153, 163, 64',
'2019, 7': '153, 163, 128', '2019, 8': '153, 163, 192',
'2019, 9': '153, 164, 0', '2019, 10': '153, 164, 64',
'2019, 11': '153, 164, 128', '2019, 12': '153, 164, 192',
'2020, 1': '153, 165, 64', '2020, 2': '153, 165, 128',
'2020, 3': '153, 165, 192','2020, 4': '153, 166, 0',
'2020, 5': '153, 166, 64', '2020, 6': '153, 1, 128',
'2020, 7': '153, 166, 192', '2020, 8': '153, 167, 0',
'2020, 9': '153, 167, 64','2020, 10': '153, 167, 128',
'2020, 11': '153, 167, 192', '2020, 12': '153, 168, 0'}
def year_month(x1, x2): # {x,X,X,x,x }
for key, value in ym_list.items():
key = [int(k) for k in key.replace("'", "").split(", ")]
value = [int(v) for v in value.split(", ")]
if x1 == value[1] and x2 // 64 == value[2] // 64:
return key
return 0, 0
ืื ื ืืืื ืฉืื ืืชื ืืืื ืืื n, ื ืืชื ืืชืงื ืืช ืื ืืืื ื ืืื.
ืืืืจ ืืื, ืคืื ืงืฆืื ืฉืืืืืจื ืืืืืืงื ืชืืจืื-ืฉืขื ืืืืจืืืช. ืชึทืกืจึดืื:
def find_data_time(val:str):
val = [int(v) for v in val.split(", ")]
day = day_(val[2])
hour = hour_(val[2], val[3])
minutes = min_(val[3], val[4])
year, month = year_month(val[1], val[2])
return datetime(year, month, day, hour, minutes)
ืืฆืืื ืืืืืช ืขืจืืื ืฉืืืืจืื ืขื ืขืฆืื ืืขืชืื ืงืจืืืืช ื-int, int, datetime, datetime , ื ืจืื ืฉืื ืื ืฉืืชื ืฆืจืื. ืืชืจื ืืื, ืจืฆืฃ ืืื ืืื ื ืืืืจ ืขื ืขืฆืื ืคืขืืืื ืืื ืฉืืจื.
ืืืืฆืขืืช ืืืืื ืจืืืืจื, ืื ื ืืืฆืืื ืืช ืื ืชืื ืื ืืืจืืฉืื:
fined = re.findall(r'128, d*, d*, d*, 128, d*, d*, d*, 153, 1[6,5,4,3]d, d*, d*, d*, 153, 1[6,5,4,3]d, d*, d*, d*', int_array)
ืฉืืื ืื ืฉืืืืคืืฉ ืืืืฆืขืืช ืืืืื ืื, ืื ื ืืชื ืืืื ืืงืืืข ืขืจืื NULL ืืฉืืืช ืื ืืจืฉืื, ืื ืืืงืจื ืฉืื ืื ืื ืงืจืืื. ืืื ืื ืื ื ืขืืืจืื ืขื ืื ืฉืืฆืื ื ืืืืค. ืชึทืกืจึดืื:
result = []
for val in fined:
pre_result = []
bd_int = re.findall(r"128, d*, d*, d*", val)
bd_date= re.findall(r"(153, 1[6,5,4,3]d, d*, d*, d*)", val)
for it in bd_int:
pre_result.append(find_int(bd_int[it]))
for bd in bd_date:
pre_result.append(find_data_time(bd))
result.append(pre_result)
ืืืขืฉื, ืื ืืื, ืื ืชืื ืื ืืืขืจื ืืชืืฆืืืช ืื ืื ืชืื ืื ืฉืื ื ืฆืจืืืื. ###ื .ื.###
ืื ื ืืืื ืฉืฉืืื ืื ืื ืืชืืืื ืืืืื, ืืื ืืืืจื ืืขืืงืจืืช ืฉื ืืืืืจ ืืื ืืื ืืข ืืคืขืืื ืืื ืืคืชืืจ ืืช ืื ืืืขืืืช ืฉืื. ืื ื ืืืฉื ืฉืืคืชืจืื ืื ืืื ืืืืชืจ ืืืื ืืืชืืื ืืืืื ืืช ืงืื ืืืงืืจ ืืขืฆืื
ืืืงืจืื ืืกืืืืื, ืืืืจ ื ืืชืื ืืงืืืฅ, ืชืืื ืืงืืืข ืืช ืืืื ื ืืืฉืืขืจ ืืืฉืืืจ ืืืชื ืืืืช ืืืฉืืืืช ืืกืื ืืจืืืืช ืืืงืืฉืืจืื ืืืขืื. ืื ืืืื ืืจืื ืืืชืจ ื ืืื ืืืืจืื ืคืืืช ืืขืืืช.
ืืงืืจ: www.habr.com