SxWF8EfQ

1#coding: utf-8
2from sqlite3 import *
3from os import *
4from io import *
5from xml.dom import minidom
6
7def translate(s):
8    d = {'а': 'a',
9         'б': 'b',
10         'в': 'v',
11         'г': 'g',
12         'д': 'd',
13         'е': 'e',
14         'Ñ‘': 'e',
15         'ж': 'zh',
16         'з': 'z',
17         'и': 'i',
18         'й': 'i',
19         'к': 'k',
20         'л': 'l',
21         'м': 'm',
22         'н': 'n',
23         'о': 'o',
24         'п': 'p',
25         'Ñ€': 'r',
26         'с': 's',
27         'Ñ‚': 't',
28         'у': 'u',
29         'Ñ„': 'f',
30         'Ñ…': 'kh',
31         'ц': 'ts',
32         'ч': 'ch',
33         'ш': 'sh',
34         'щ': 'shch',
35         'ÑŠ': '',
36         'Ñ‹': 'y',
37         'ь': '',
38         'э': 'ye',
39         'ÑŽ': 'yu',
40         'я': 'ya'}
41    d2 = dict([(i.upper(), d[i].title()) for i in d])
42    for i in s:
43        if i in d:
44            s = s.replace(i, d[i])
45        if i in d2:
46            s = s.replace(i, d2[i])
47    return s
48
49create = path.exists('wino.db')
50if not create:
51    conn = connect('wino.db')
52    
53    c = conn.cursor()
54    #c.execute('''DROP TABLE IF EXISTS wino''')
55    #c.execute('''DROP TABLE IF EXISTS associates''')
56    #c.execute('''DROP TABLE IF EXISTS provides''')
57    #c.execute('''DROP TABLE IF EXISTS sales''')
58    #c.execute('''DROP TABLE IF EXISTS receipts''')    
59    
60    c.execute('''CREATE TABLE wino
61    (barcode integer primary key autoincrement,
62    name varchar,
63    year integer,
64    capacity real,
65    alcohol real,
66    sugar integer,
67    unique(name, year))''')   
68    
69    c.execute('''INSERT INTO wino (name, year, capacity, alcohol, sugar)
70    VALUES ('Шардоне', 2007, 0.75, 12.5, 4)''')
71    c.execute('''INSERT INTO wino (name, year, capacity, alcohol, sugar)
72    VALUES ('Шардоне', 2011, 0.75, 12.5, 4)''')
73    c.execute('''INSERT INTO wino (name, year, capacity, alcohol, sugar)
74    VALUES ('Каберне', 2009, 0.75, 13, 4)''')
75    c.execute('''INSERT INTO wino (name, year, capacity, alcohol, sugar)
76    VALUES ('Мерло', 2007, 0.75, 13, 4)''')
77    c.execute('''INSERT INTO wino (name, year, capacity, alcohol, sugar)
78    VALUES ('Мерло', 2009, 0.75, 13, 4)''')
79    
80    c.execute('''CREATE TABLE associates
81    (id integer primary key autoincrement,
82    surname varchar,
83    name varchar,
84    patronymic varchar,
85    date_birth date,
86    position varchar,
87    unique(surname, name, patronymic, date_birth))''')
88    
89    c.execute('''INSERT INTO associates (surname, name, patronymic, date_birth, position)
90    VALUES ('Шиян', 'Валерий', 'Игоревич', '08.12.1997', 'Программист')''')
91    c.execute('''INSERT INTO associates (surname, name, patronymic, date_birth, position)
92    VALUES ('Степанова', 'Елизавета', 'Владимировна', '27.08.1997', 'Программист')''')
93    c.execute('''INSERT INTO associates (surname, name, patronymic, date_birth, position)
94    VALUES ('Добровольская', 'Наталья', 'Юрьевна', '10.07.1971', 'Программист')''')
95    
96    c.execute('''CREATE TABLE provides
97    (id integer primary key autoincrement,
98    firm varchar unique,
99    country varchar,
100    treaty integer)''')
101    
102    c.execute('''INSERT INTO provides (firm, country, treaty)
103    VALUES ('Мерло', 'Италия', 1)''')
104    
105    c.execute('''CREATE TABLE sales
106    (id_check integer primary key autoincrement,
107    id_wino varchar,
108    quantity integer,
109    date_sale date,
110    id_associates integer)''')
111    
112    c.execute('''INSERT INTO sales (id_wino, quantity, date_sale, id_associates)
113    VALUES ('Темпранильо', 5, '06.06.2017', 1)''')
114    c.execute('''INSERT INTO sales (id_wino, quantity, date_sale, id_associates)
115    VALUES ('Корвина', 5, '08.06.2017', 1)''')
116    c.execute('''INSERT INTO sales (id_wino, quantity, date_sale, id_associates)
117    VALUES ('Молинара', 4, '11.06.2017', 1)''')
118    c.execute('''INSERT INTO sales (id_wino, quantity, date_sale, id_associates)
119    VALUES ('Кроатина', 4, '06.06.2017', 1)''')
120    c.execute('''INSERT INTO sales (id_wino, quantity, date_sale, id_associates)
121    VALUES ('Мерло', 3, '08.06.2017', 1)''')
122    
123    c.execute('''CREATE TABLE receipts
124    (id_invoice integer primary key autoincrement,
125    id_wino varchar,
126    quantity integer,
127    date_addmision date,
128    id_provider integer)''')
129    
130    c.execute('''INSERT INTO receipts (id_wino, quantity, date_addmision, id_provider)
131    VALUES ('Мерло', 2, '08.06.2017', 1)''')
132    
133    conn.commit()
134    c.close()
135    
136    conn.close()
137else:
138    conn = connect('wino.db')
139    
140    c = conn.cursor()     
141    
142    c.execute('''SELECT * FROM sqlite_master
143    WHERE type = "table"''')
144    res = c.fetchall() 
145    c.execute('''SELECT * FROM wino''')
146    res1 = c.fetchall()
147    print(res1)
148    c.execute('''SELECT * FROM associates''')
149    res2 = c.fetchall()
150    print(res2)
151    c.execute('''SELECT * FROM provides''')
152    res3 = c.fetchall()
153    print(res3)
154    c.execute('''SELECT * FROM sales''')
155    res4 = c.fetchall()
156    print(res4)
157    c.execute('''SELECT * FROM receipts''')
158    res5 = c.fetchall()
159    print(res5)
160    conn.commit()
161    c.close()
162    
163    conn.close()
164
165doc = minidom.Document()  
166root = doc.createElement('root')
167doc.appendChild(root)
168
169wines = doc.createElement('wines')
170for i in res1:
171    wino = doc.createElement('wino')
172    barcode = doc.createElement('barcode')
173    text = doc.createTextNode(translate(str(i[0])))
174    barcode.appendChild(text)
175    wino.appendChild(barcode)    
176    name = doc.createElement('name')
177    text = doc.createTextNode(translate(str(i[1])))
178    name.appendChild(text)
179    wino.appendChild(name)
180    year = doc.createElement('year')
181    text = doc.createTextNode(translate(str(i[2])))
182    year.appendChild(text)
183    wino.appendChild(year)
184    capacity = doc.createElement('capacity')
185    text = doc.createTextNode(translate(str(i[3])))
186    capacity.appendChild(text)
187    wino.appendChild(capacity)
188    alcohol = doc.createElement('alcohol')
189    text = doc.createTextNode(translate(str(i[4])))
190    alcohol.appendChild(text)
191    wino.appendChild(alcohol)
192    sugar = doc.createElement('sugar')
193    text = doc.createTextNode(translate(str(i[5])))
194    sugar.appendChild(text)
195    wino.appendChild(sugar)
196    wines.appendChild(wino)
197root.appendChild(wines)
198
199associates = doc.createElement('associates')
200for i in res2:
201    associate = doc.createElement('associate')
202    id_associate = doc.createElement('id_associate')
203    text = doc.createTextNode(translate(str(i[0])))
204    id_associate.appendChild(text)
205    associate.appendChild(id_associate)    
206    surname = doc.createElement('surname')
207    text = doc.createTextNode(translate(str(i[1])))
208    surname.appendChild(text)
209    associate.appendChild(surname)
210    name = doc.createElement('name')
211    text = doc.createTextNode(translate(str(i[2])))
212    name.appendChild(text)
213    associate.appendChild(name)
214    patronymic = doc.createElement('patronymic')
215    text = doc.createTextNode(translate(str(i[3])))
216    patronymic.appendChild(text)
217    associate.appendChild(patronymic)
218    date_birth = doc.createElement('date_birth')
219    text = doc.createTextNode(translate(str(i[4])))
220    date_birth.appendChild(text)
221    associate.appendChild(date_birth)
222    position = doc.createElement('position')
223    text = doc.createTextNode(translate(str(i[5])))
224    position.appendChild(text)
225    associate.appendChild(position)
226    associates.appendChild(associate)
227root.appendChild(associates)
228
229provides = doc.createElement('provides')
230for i in res3:
231    provider = doc.createElement('provider')
232    id_provider = doc.createElement('id_provider')
233    text = doc.createTextNode(translate(str(i[0])))
234    id_provider.appendChild(text)
235    provider.appendChild(id_provider)    
236    firm = doc.createElement('firm')
237    text = doc.createTextNode(translate(str(i[1])))
238    firm.appendChild(text)
239    provider.appendChild(firm)
240    country = doc.createElement('country')
241    text = doc.createTextNode(translate(str(i[2])))
242    country.appendChild(text)
243    provider.appendChild(country)
244    treaty = doc.createElement('treaty')
245    text = doc.createTextNode(translate(str(i[3])))
246    treaty.appendChild(text)
247    provider.appendChild(treaty)
248    provides.appendChild(provider)
249root.appendChild(provides)
250
251sales = doc.createElement('sales')
252for i in res4:
253    sale = doc.createElement('sale')
254    id_check = doc.createElement('id_check')
255    text = doc.createTextNode(translate(str(i[0])))
256    id_check.appendChild(text)
257    sale.appendChild(id_check)    
258    id_wino = doc.createElement('id_wino')
259    text = doc.createTextNode(translate(str(i[1])))
260    id_wino.appendChild(text)
261    sale.appendChild(id_wino)
262    quantity = doc.createElement('quantity')
263    text = doc.createTextNode(translate(str(i[2])))
264    quantity.appendChild(text)
265    sale.appendChild(quantity)
266    date_sale = doc.createElement('date_sale')
267    text = doc.createTextNode(translate(str(i[3])))
268    date_sale.appendChild(text)
269    sale.appendChild(date_sale)
270    id_associates = doc.createElement('id_associates')
271    text = doc.createTextNode(translate(str(i[4])))
272    id_associates.appendChild(text)
273    sale.appendChild(id_associates)    
274    sales.appendChild(sale)
275root.appendChild(sales)
276
277receipts = doc.createElement('receipts')
278for i in res5:
279    receipt = doc.createElement('receipt')
280    id_invoice = doc.createElement('id_invoice')
281    text = doc.createTextNode(translate(str(i[0])))
282    id_invoice.appendChild(text)
283    receipt.appendChild(id_invoice)    
284    id_wino = doc.createElement('id_wino')
285    text = doc.createTextNode(translate(str(i[1])))
286    id_wino.appendChild(text)
287    receipt.appendChild(id_wino)
288    quantity = doc.createElement('quantity')
289    text = doc.createTextNode(translate(str(i[2])))
290    quantity.appendChild(text)
291    receipt.appendChild(quantity)
292    date_addmision = doc.createElement('date_addmision')
293    text = doc.createTextNode(translate(str(i[3])))
294    date_addmision.appendChild(text)
295    receipt.appendChild(date_addmision)
296    id_provider = doc.createElement('id_provider')
297    text = doc.createTextNode(translate(str(i[4])))
298    id_provider.appendChild(text)
299    receipt.appendChild(id_provider)    
300    receipts.appendChild(receipt)
301root.appendChild(receipts)
302
303xml_str = doc.toprettyxml(indent = '')
304
305with open('sale.xml', 'w') as f:
306    f.write(xml_str)