t9cF8Vj9

1      ######################## Инструкция по применению ############################
2
3####   1. Создайте в каталоге с этим файлом (ftp.py) файл dirs2backup.txt            ####
4####   2. Добавьте в файл dirs2backup.txt список каталогов для копирования           ####
5####      в формате абсолютного пути UNIX (например, /var/www/html/images/),         ####
6####      разделяя их переводом строки. Закрывающий слеш в конце обязателен          ####  
7####   3. Укажите данные для соединения с FTP-сервером и БД MySQL                    ####
8####   4. Запустите файл из консоли командой 'python ftp.py &' и ожидайте завершения ####
9
10      ##############################################################################
11
12import os
13import io
14from ftplib import FTP
15import pymysql
16from pymysql.cursors import DictCursor
17
18################# FTP #####################
19
20ftp_host = '185.9.147.4'
21ftp_user = 'py'
22ftp_pass = '1N7i7B5s$V4w1B2y6'
23
24################  DB  #####################
25
26db_host = 'localhost'
27db_user = 'backup'
28db_pass = 'N5j6N6e39C0k4Z9x'
29db_name = 'backup'
30
31######################################
32
33connection = pymysql.connect(
34
35    host = db_host,
36    user = db_user,
37    password = db_pass,
38    charset='utf8mb4',
39    cursorclass=DictCursor
40)
41
42with connection.cursor() as cursor:
43
44    query = """
45    CREATE DATABASE IF NOT EXISTS backup;
46    """
47    cursor.execute(query)
48
49    query = """
50    ALTER DATABASE backup CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
51    """
52    cursor.execute(query)
53
54    query = """
55    USE backup;
56    """
57    cursor.execute(query)
58
59    query = """
60    CREATE TABLE IF NOT EXISTS transferred_log (
61    id MEDIUMINT NOT NULL AUTO_INCREMENT, filename VARCHAR(888), dirname VARCHAR(888), PRIMARY KEY (id)
62    )  ENGINE=INNODB DEFAULT CHARSET=utf8;
63    """
64    cursor.execute(query)
65
66    query = """
67    ALTER TABLE transferred_log CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
68    """
69    cursor.execute(query)
70
71    query = """
72    ALTER TABLE transferred_log MODIFY COLUMN `filename` CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
73    """
74    cursor.execute(query)
75
76connection.close()
77
78#############################################
79
80def DBconnect():
81
82    connection = pymysql.connect(
83
84        host = db_host,
85        user = db_user,
86        password = db_pass,
87        db = db_name,
88        charset='utf8mb4',
89        cursorclass=DictCursor
90    )
91
92    return connection
93
94######################################
95
96def isTransferred(filename, dirname):
97
98    transferred = 0
99
100    connection = DBconnect()
101
102    with connection.cursor() as cursor:
103
104        query = 'SELECT filename, dirname FROM transferred_log'
105
106        cursor.execute(query)
107
108        for row in cursor:
109
110            #print(row['filename'])
111
112            log_filename = row['filename']
113
114            log_dirname = row['dirname']
115
116            if log_filename == filename and log_dirname == dirname:
117
118                #print('found!')
119
120                transferred = 1
121
122                break
123   
124    connection.close()
125
126    if transferred == 1:
127
128        return 1
129
130    if transferred == 0:
131
132        return 0
133
134#############################################
135
136def chdir(dir, ftp): 
137
138    if directory_exists(dir, ftp) is False:
139
140        ftp.mkd(dir)
141
142    ftp.cwd(dir)
143
144######################################
145
146def directory_exists(dir, ftp):
147
148    filelist = []
149
150    ftp.retrlines('LIST',filelist.append)
151
152    for f in filelist:
153
154        if f.split()[-1] == dir and f.upper().startswith('D'):
155
156            return True
157
158    return False
159######################################
160
161def upload(filename, path):
162
163    #print(path)
164
165    dirname = path.split('/')
166
167    lastnum = len(dirname) - 2
168
169    dirname = dirname[lastnum]
170
171    #print(dirname)
172
173    ftp = FTP(host=ftp_host, user=ftp_user, passwd=ftp_pass)
174
175    ftp.encoding = "utf-8"
176
177    mkdir = chdir(dirname, ftp)
178
179    with open(path+filename, "rb") as file:
180
181        is_transferred = isTransferred(filename, dirname)        
182
183        if is_transferred == 0:
184
185            filename = filename.encode("utf-8", "surrogatepass").decode("utf-8", "surrogatepass")
186        
187            ftp.storbinary(f"STOR {filename}", file)
188
189            connection = DBconnect()
190
191            with connection.cursor() as cursor:
192
193                file = (filename, dirname)
194                
195                query = 'INSERT INTO `transferred_log` (`filename`, `dirname`) VALUES (%s, %s)'
196
197                cursor.execute(query, file)
198                connection.commit()
199                connection.close()
200
201        else:
202
203            print('already transferred!')
204
205
206with open('/var/www/www-root/data/www/python3/dirs2backup.txt', 'r') as f:
207
208    path_lines = f.read().splitlines()
209
210    for path in path_lines:
211
212        current_files = os.listdir(path=path)
213
214        for file in current_files:
215
216            if '.' in file:
217
218                print(file)
219
220                upload(file, path)
221
222print('done!')
223
224exit()