· 5 years ago · Jul 06, 2020, 11:00 PM
1'''from dbf import Table
2import mysql.connector
3
4mydb = mysql.connector.connect(
5 auth_plugin='mysql_native_password',
6 user="root",
7 password="root",
8 database="katya",
9)
10mycursor = mydb.cursor(buffered=True)
11
12placenames = Table('C:/Users/work08/Downloads/Katerina/112012B1.DBF', codepage='cp866')
13placenames.open()
14
15quere = "create table
16
17for place in placenames:
18 print(place.regn, place.vr)
19sql = "INSERT INTO (id, Date) VALUES (" + next_id + ",'" + url_date + "')"
20mycursor.execute(sql)
21mydb.commit()'''
22
23from bs4 import BeautifulSoup
24from urllib import request
25from dbf import Table
26import mysql.connector
27import requests
28import rarfile
29import dbf
30import os.path
31import mysql.connector
32import parsing
33import csv
34import requests
35from requests.adapters import HTTPAdapter
36from requests.packages.urllib3.util.retry import Retry
37
38'''
39def dbf_to_csv(dbf_table_pth): # Input a dbf, output a csv, same name, same path, except extension
40 """Сам перевод dbf в csv"""
41 csv_fn = dbf_table_pth[:-4] + ".csv" # Set the csv file name
42 table = dbf.Table(dbf_table_pth, codepage='cp866') # table variable is a DBF object
43 with open(csv_fn, 'w', newline='', encoding='cp866') as f: # create a csv file, fill it with dbf content
44 writer = csv.writer(f, delimiter=',')
45 table.open(mode=dbf.READ_WRITE)
46 writer.writerow(table.field_names) # write the column name
47 if len(table.field_names) == 18:
48 for record in table: # write the rows
49 a = []
50 try:
51 a.append(record.regn)
52 a.append(record.plan)
53 a.append(record.num_sc)
54 a.append(record.a_p)
55 a.append(record.vr)
56 a.append(record.vv)
57 a.append(record.vitg)
58 a.append(record.ora)
59 a.append(record.ova)
60 a.append(record.oitga)
61 a.append(record.orp)
62 a.append(record.ovp)
63 a.append(record.oitgp)
64 a.append(record.ir)
65 a.append(record.iv)
66 a.append(record.iitg)
67 a.append(record.dt)
68 a.append(record.priz)
69 writer.writerow(a)
70 except dbf.FieldMissingError:
71 break
72
73 return csv_fn # return the csv name'''
74
75
76def check_for_existence_func(directory, file_name):
77 if os.path.isfile('{}/{}'.format(directory, file_name)):
78 return True
79 else:
80 return False
81
82
83def create_BD_file_func(month, year):
84 mydb = mysql.connector.connect(
85 auth_plugin='mysql_native_password',
86 user="root",
87 password="root",
88 database="database",
89 allow_local_infile=True
90 )
91 mycursor = mydb.cursor(buffered=True)
92 # sql = "create table template (id INT, vr INT, vt INT, itog INT, cq INT, cw INT, cr INT, mq INT," \
93 # " mw INT, mr INT, ir INT, ii INT, itogo INT);"
94
95 nums = parsing.take_table_func(month, year)
96 query = "create table if not exists 102018b1 like template"
97 mycursor.execute(query)
98 mydb.commit()
99 # with open(r'C:/Users/work08/Downloads/form101 (1)/form101/Scripts/csv3.csv', "w", newline='') as csv_file:
100 print("#1")
101 with open(r'csv3.csv', "w", newline='') as csv_file:
102 csv_file.seek(0)
103 write = csv.writer(csv_file, dialect='excel', delimiter=';')
104 arr_index = -1
105 for arr in nums:
106 arr_index += 1
107 rr_index = 0
108 if arr != "*":
109 for rr in arr:
110 '''разделение на активы пассивы'''
111 for str in rr:
112 str.append(arr_index)
113 str.append(rr_index)
114 print(str)
115 write.writerow(str)
116 rr_index = 1
117 else:
118 print(arr)
119 write.writerow("пусто")
120 print("#2")
121 q0 = "CREATE TABLE IF NOT EXISTS 102018b1 like template;"
122 # q00 = "ALTER TABLE " + filen + " DROP column id;"
123 q1 = "LOAD DATA LOCAL INFILE 'C:/Users/work08/Downloads/form101 (1)/form101/Scripts/csv3.csv' INTO TABLE 102018b1"
124 q2 = " CHARACTER SET cp866"
125 q3 = " FIELDS TERMINATED BY ';'"
126 q4 = " ENCLOSED BY '\"'"
127 q5 = " LINES TERMINATED BY '\\r\\n';"
128 querload = q1 + q2 + q3 + q4 + q5
129 quercommit = 'commit;'
130 mycursor.execute(q0)
131 mycursor.execute(querload)
132 mycursor.execute(quercommit)
133 mycursor.close()
134
135
136def remove_file_func():
137 pass
138
139
140def fill_DB_func():
141 pass
142
143
144def read_BD_file_func(month, year):
145 name = month + year + "b1"
146
147 mydb = mysql.connector.connect(
148 host="localhost",
149 user="root",
150 password="root",
151 database="database"
152 )
153
154 curs = mydb.cursor(buffered=True)
155 curs.execute("SELECT * FROM {}".format(name))
156 myresult = curs.fetchall()
157 curs.execute("DROP TABLE " + name)
158 mydb.commit()
159 # print(myresult)
160 section_index = 0
161 state_index = 0
162 result_table = []
163 section = []
164 state = []
165 lines = []
166 x = """тут код для считывания строки из бд"""
167 lines.append(x)
168 state.append(lines)
169
170
171 section.append(state)
172 result_table.append(section)
173 for x in myresult:
174 if x[14] != section_index:
175 section_index += 1
176 section.append(state)
177 result_table.append(section)
178 section = []
179 state = []
180 else:
181 if x[15] != state_index:
182
183 print(x)
184
185if __name__ == '__main__':
186 create_BD_file_func("10", "2018")
187 read_BD_file_func("10", "2018")
188
189
190def todb(url):
191 quote_page = 'https://www.cbr.ru/banking_sector/otchetnost-kreditnykh-organizaciy'
192 var = quote_page + url
193 date = var[93:-6] + var[89:-8] + "B1"
194 print(date)
195 mydb = mysql.connector.connect(
196 auth_plugin='mysql_native_password',
197 user="root",
198 password="root",
199 database="database",
200 )
201
202 curs = mydb.cursor(buffered=True)
203 curs.execute("SHOW TABLES;")
204 tables = curs.fetchall()
205
206 mydb.commit()
207
208 link = 'https://www.cbr.ru'
209 p = 'C:/Users/work08/Downloads/form101 (1)/form101/Data/vanya.rar'
210 r = requests.get(link + url, allow_redirects=True)
211 with open(p, 'wb') as f:
212 f.write(r.content)
213
214 session = requests.Session()
215 retry = Retry(connect=3, backoff_factor=0.5)
216 adapter = HTTPAdapter(max_retries=retry)
217 session.mount('http://', adapter)
218 session.mount('https://', adapter)
219
220 session.get(link + url)
221
222 # with zipfile.ZipFile(p, 'r') as rf:
223 rf = rarfile.RarFile(p)
224
225 flag = False
226 fil = ' '
227 for file in rf.namelist():
228 if 'B1' in file:
229 fil = file[:-6]
230 for table in tables:
231 if file[:-6] in table[0]:
232 flag = True
233 break
234 else:
235 flag = False
236 if flag:
237 print("Обновлений нет")
238 else:
239 print("Нужно обновить")
240 ql = "create table " + fil + "b1 like template;"
241 curs.execute(ql)
242 mydb.commit()
243
244 placenames = Table('C:/Users/work08/Downloads/Katerina/' + fil + 'B1.DBF',
245 codepage='cp866') ############################################ изменить путь
246 placenames.open()
247
248 for place in placenames:
249 print(place.regn, place.vr)
250 sql = "INSERT INTO (regn, plan, num_sc, a_p, vr, vv, vitg, ora" \
251 ", ova, oitga, orp, ovp, oitgp, ir, iv, iitg, dt, priz) VALUES ('" + place.regn + "','" + place.plan \
252 + "', '" + place.num_sc + "','" + place.a_p + "', '" + place.vr + "', '" + place.vv + "', '" \
253 + place.vitg + "', '" + place.ora + "', '" + place.ova + "', '" + place.oitga + "', '" + place.orp \
254 + "','" + place.ovp + "','" + place.oitgp + "','" + place.ir + "','" + place.iv + "','" + place.iitg \
255 + "','" + place.dt + "','" + place.priz + "')"
256
257 '''sql = "INSERT INTO (regn, plan, num_sc, a_p, vr, vv, vitg, ora" \
258 ", ova, oitga, orp, ovp, oitgp, ir, iv, iitg, dt, priz) VALUES (" + place.regn + ",'" + place.plan \
259 + "', '" + place.num_sc + "','" + place.a_p + "', " + place.vr + ", " + place.vv + ", " + place.vitg \
260 + "," + place.ora + "," + place.ova + "," + place.oitga + "," + place.orp + "," + place.ovp + "," + \
261 place.oitgp + "," + place.ir + "," + place.iv + "," + place.iitg + "," + place.dt + "," \
262 + place.priz + ")"'''
263
264 curs.execute(sql)
265 mydb.commit()
266
267
268'''quote_page = 'https://www.cbr.ru/banking_sector/otchetnost-kreditnykh-organizaciy/'
269page = request.urlopen(quote_page)
270soup = BeautifulSoup(page, 'html.parser')
271for link in soup.find_all('a'):
272 if '.rar' in link.get('href') and '101-' in link.get('href'):
273 cbr = link.get('href')
274 todb(cbr)
275 read_BD_file_func("10", "2018")
276 break'''