1
2
3
4 """
5 Manage the DATABASE interface
6
7 G{importgraph}
8 """
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28 import sqlite3
29 import sys
30 import os
31
32
33
34
36 """
37 Class which allow to access to the SQLite DataBase of PYCALCAR
38
39 G{classtree}
40
41 TABLE
42 =====
43 ABOUT
44 -----
45 Contains the different information for about windows
46 Columns: Action, Message
47
48 CONFIG
49 ------
50 Contains the parameters for the software
51 Columns: Lang, Screen, Action, Message
52
53 MONEY
54 -----
55 Contains the data of every money
56 Columns: Name, Year, Nation, Nb_unit, Unit0, Unit1, Unit2, Unit3, Unit4, Unit5, Unit6,
57 Unit7, unit8, Unit9, Unit1_to_0, Unit2_to_1, unit3_to_2, unit4_to_3, Unit5_to_4,
58 Unit6_to_5, unit7_to_6, Unit8_to_7, Unit9_to_8
59
60 RATE
61 ----
62 Contains the rate to convert form money1 to money2
63 Columns: Name1, Year1, Nation1, Name2, Year2, Nation2, Rate1_to_2
64 """
65
66
67
68
69
71 bdd_path = os.path.join(path, "01-BDD/PYCALCAR.sqlite")
72
73 self.bdd_pycalcar = sqlite3.connect(bdd_path)
74 self.bdd_pycalcar.text_factory = str
75 self.bdd_pycalcar.row_factory = sqlite3.Row
76
77
78
79
80
81
82 - def p_ins_money(self,name,year,nation,nb_unit,u0,u1,u2,u3,u4,u5,u6,u7,u8,u9, \
83 tx0,tx1,tx2,tx3,tx4,tx5,tx6,tx7,tx8):
84 """
85 Creation of a new money
86
87 PARAMETERS
88 ==========
89 name
90 ----
91 Money's name
92 year
93 ----
94 Money's year creation
95 nation
96 ------
97 Nation of the money
98 nb_unit
99 -------
100 Number of unit in the money
101 u0 => u9
102 --------
103 Name of the different units of the money
104 tx0 => tx8
105 ----------
106 Rate between the different units of the money
107
108 RETURNS
109 =======
110 None
111 """
112 bdd_cursor = self.bdd_pycalcar.cursor()
113 bdd_cursor.execute("INSERT INTO MONEY \
114 ( \
115 NAME, YEAR,\
116 NATION, NB_UNIT, UNIT0,\
117 UNIT1, UNIT2,\
118 UNIT3, UNIT4,\
119 UNIT5, UNIT6,\
120 UNIT7, UNIT8,\
121 UNIT9, UNIT1_TO_0,\
122 UNIT2_TO_1, UNIT3_TO_2,\
123 UNIT4_TO_3, UNIT5_TO_4,\
124 UNIT6_TO_5, UNIT7_TO_6,\
125 UNIT8_TO_7, UNIT9_TO_8 \
126 ) \
127 VALUES \
128 ( \
129 ?,?,\
130 ?,?,?,\
131 ?,?,\
132 ?,?,\
133 ?,?,\
134 ?,?,\
135 ?,?,\
136 ?,?,\
137 ?,?,\
138 ?,?,\
139 ?,?\
140 )", \
141 (name,year,nation,nb_unit,u0,u1,u2,u3,u4,u5,u6,u7,u8,u9,\
142 tx0,tx1,tx2,tx3,tx4,tx5,tx6,tx7,tx8))
143 bdd_cursor.close()
144 self.bdd_pycalcar.commit()
145
146
147
148
149
150
151 - def p_ins_rate(self,name1,year1,nation1,name2,year2,nation2,rate):
152 """
153 Creation of a new rate between devise
154
155 PARAMETERS
156 ==========
157 name1
158 -----
159 Name of the first money
160 year1
161 -----
162 Year of creation of the first money
163 nation1
164 -------
165 Nation of the first money
166 name2
167 -----
168 Name of the second money
169 year2
170 -----
171 Year of creation of the second money
172 nation2
173 -------
174 Nation of the second money
175 rate
176 ----
177 Rate to convert money 1 into money 2
178
179 RETURNS
180 =======
181 None
182 """
183 bdd_cursor = self.bdd_pycalcar.cursor()
184 bdd_cursor.execute("INSERT INTO RATE \
185 ( \
186 NAME1, YEAR1,\
187 NATION1, NAME2,\
188 YEAR2, NATION2,\
189 RATE1_TO_2\
190 ) \
191 VALUES \
192 ( \
193 ?,?,\
194 ?,?,\
195 ?,?,\
196 ?\
197 )", \
198 (name1,year1,nation1,name2,year2,nation2,rate))
199 bdd_cursor.close()
200 self.bdd_pycalcar.commit()
201
202
203
204
205
206
207 - def p_upd_money(self,old_name,old_year,old_nation,name,year,nation, \
208 nb_unit,u0,u1,u2,u3,u4,u5,u6,u7,u8,u9, \
209 tx0,tx1,tx2,tx3,tx4,tx5,tx6,tx7,tx8):
210 """
211 Update of a money's DATA
212
213 PARAMETERS
214 ==========
215 old_name
216 --------
217 Old name of the money
218 old_year
219 --------
220 Old year of creation of the money
221 old_nation
222 ----------
223 Old nation of the money
224 name
225 ----
226 New name for the money
227 year
228 ----
229 New year of creation for the money
230 nation
231 ------
232 New nation for the money
233 nb_unit
234 -------
235 Number of units in the money
236 u0 => u9
237 --------
238 Name of the different units of the money
239 tx0 => tx8
240 ----------
241 Rate between the different units of the money
242
243 RETURNS
244 =======
245 None
246 """
247 bdd_cursor = self.bdd_pycalcar.cursor()
248 bdd_cursor.execute("UPDATE MONEY \
249 SET NAME = ?, YEAR = ?, \
250 NATION = ?, UNIT0 = ?, \
251 UNIT1 = ?, UNIT2 = ?, \
252 UNIT3 = ?, UNIT4 = ?, \
253 UNIT5 = ?, UNIT6 = ?, \
254 UNIT7 = ?, UNIT8 = ?, \
255 UNIT9 = ?, UNIT1_TO_0 = ?, \
256 UNIT2_TO_1 = ?, UNIT3_TO_2 = ?, \
257 UNIT4_TO_3 = ?, UNIT5_TO_4 = ?, \
258 UNIT6_TO_5 = ?, UNIT7_TO_6 = ?, \
259 UNIT8_TO_7 = ?, UNIT9_TO_8 = ? \
260 WHERE NAME = ? AND \
261 YEAR = ? AND \
262 NATION = ?", \
263 (name,year,nation,u0,u1,u2,u3,u4,u5,u6,u7,u8,u9,\
264 tx0,tx1,tx2,tx3,tx4,tx5,tx6,tx7,tx8,old_name,old_year,old_nation))
265 bdd_cursor.close()
266 self.bdd_pycalcar.commit()
267
268 bdd_cursor = self.bdd_pycalcar.cursor()
269 bdd_cursor.execute("UPDATE RATE \
270 SET NAME1 = ?, YEAR1 = ?, \
271 NATION1 = ?\
272 WHERE NAME1 = ? AND \
273 YEAR1 = ? AND \
274 NATION1 = ?", \
275 (name,year,nation,old_name,old_year,old_nation))
276 bdd_cursor.close()
277 self.bdd_pycalcar.commit()
278
279 bdd_cursor = self.bdd_pycalcar.cursor()
280 bdd_cursor.execute("UPDATE RATE \
281 SET NAME2 = ?, YEAR2 = ?, \
282 NATION2 = ?\
283 WHERE NAME2 = ? AND \
284 YEAR2 = ? AND \
285 NATION2 = ?", \
286 (name,year,nation,old_name,old_year,old_nation))
287 bdd_cursor.close()
288 self.bdd_pycalcar.commit()
289
290
291
292
293
294
295
296
297 - def p_upd_rate(self, name1, year1, nation1, name2, year2, nation2, rate):
298 """
299 Update of money's rate
300
301 PARAMETERS
302 ==========
303 name1
304 -----
305 Name of the first money
306 year1
307 -----
308 Year of creation of the first money
309 nation1
310 -------
311 Nation of the first money
312 name2
313 -----
314 Name of the second money
315 year2
316 -----
317 Year of creation of the second money
318 nation2
319 -------
320 Nation of the second money
321 rate
322 ----
323 Rate to convert money 1 into money 2
324 RETURNS
325 =======
326 None
327 """
328 bdd_cursor = self.bdd_pycalcar.cursor()
329 bdd_cursor.execute("UPDATE RATE \
330 SET RATE1_TO_2 = ? \
331 WHERE NAME1 = ? AND \
332 YEAR1 = ? AND \
333 NATION1 = ? AND \
334 NAME2 = ? AND \
335 YEAR2 = ? AND \
336 NATION2 = ?", \
337 (rate,name1,year1,nation1,name2,year2,nation2))
338 bdd_cursor.close()
339 self.bdd_pycalcar.commit()
340
341
342
343
344
345
347 """
348 Delete a Money
349
350 PARAMETERS
351 ==========
352 name
353 ----
354 New name for the money
355 year
356 ----
357 New year of creation for the money
358 nation
359 ------
360 New nation for the money
361
362 RETURNS
363 =======
364 None
365 """
366 bdd_cursor = self.bdd_pycalcar.cursor()
367 bdd_cursor.execute("DELETE FROM MONEY \
368 WHERE NAME = ? AND \
369 YEAR = ? AND \
370 NATION = ?", \
371 (name,year,nation))
372
373 bdd_cursor.execute("DELETE FROM RATE \
374 WHERE (NAME1 = ? AND \
375 YEAR1 = ? AND \
376 NATION1 = ?)", \
377 (name,year,nation))
378
379 bdd_cursor.execute("DELETE FROM RATE \
380 WHERE (NAME2 = ? AND \
381 YEAR2 = ? AND \
382 NATION2 = ?)", \
383 (name,year,nation))
384
385 bdd_cursor.close()
386 self.bdd_pycalcar.commit()
387
388
389
390
391
392
393
394 - def p_del_rate(self,name1,year1,nation1,name2='',year2='',nation2=''):
395 """
396 Delete all rate attach to a devise which has been delete
397
398 PARAMETERS
399 ==========
400 name1
401 -----
402 Name of the first money
403 year1
404 -----
405 Year of creation of the first money
406 nation1
407 -------
408 Nation of the first money
409 name2
410 -----
411 Name of the second money
412 year2
413 -----
414 Year of creation of the second money
415 nation2
416 -------
417 Nation of the second money
418 rate
419 ----
420 Rate to convert money 1 into money 2
421
422 RETURNS
423 =======
424 None
425 """
426 bdd_cursor = self.bdd_pycalcar.cursor()
427 bdd_cursor.execute("DELETE FROM RATE \
428 WHERE (NAME1 = ? AND \
429 YEAR1 = ? AND \
430 NATION1 = ? AND\
431 NAME2 = ? AND \
432 YEAR2 = ? AND \
433 NATION2 = ?)", \
434 (name1,year1,nation1,name2,year2,nation2))
435
436 bdd_cursor.execute("DELETE FROM RATE \
437 WHERE (NAME1 = ? AND \
438 YEAR1 = ? AND \
439 NATION1 = ? AND\
440 NAME2 = ? AND \
441 YEAR2 = ? AND \
442 NATION2 = ?)", \
443 (name2,year2,nation2,name1,year1,nation1))
444
445 bdd_cursor.close()
446 self.bdd_pycalcar.commit()
447
448
449
450
451
452
454 """
455 Allow to read a money's DATA
456
457 PARAMETERS
458 ==========
459 None
460
461 RETURNS
462 =======
463 A list that contains the moneys's data
464 --------------------------------------
465 name, year, nation, number of unit,
466 unit0, unit1, unit2, unit3, unit4,
467 unit5, unit6, unit7, unit8, unit9
468
469 """
470 bdd_cursor = self.bdd_pycalcar.cursor()
471 bdd_cursor.execute("SELECT NAME, \
472 YEAR, \
473 NATION, \
474 NB_UNIT, \
475 ifnull(UNIT0,''),\
476 ifnull(UNIT1,''),\
477 ifnull(UNIT2,''),\
478 ifnull(UNIT3,''),\
479 ifnull(UNIT4,''),\
480 ifnull(UNIT5,''),\
481 ifnull(UNIT6,''),\
482 ifnull(UNIT7,''),\
483 ifnull(UNIT8,''),\
484 ifnull(UNIT9,'')\
485 FROM MONEY")
486 money_list = []
487 for r in bdd_cursor:
488 money_list.append(r)
489
490 bdd_cursor.close()
491
492 return money_list
493
494
495
496
497
498
499
501 """
502 Allow to read a money's DATA internal rate conversion
503
504 PARAMETERS
505 ==========
506 name
507 ----
508 New name for the money
509 year
510 ----
511 New year of creation for the money
512 nation
513 ------
514 New nation for the money
515
516 RETURNS
517 =======
518 A list that contains the rate between unit of a money
519 -----------------------------------------------------
520 unit1_to_0,unit2_to_1,unit3_to_2,unit4_to_3,
521 unit5_to_4,unit6_to_5,unit7_to_6,unit8_to_7,
522 unit9_to_8
523 """
524 bdd_cursor = self.bdd_pycalcar.cursor()
525 bdd_cursor.execute("SELECT ifnull(UNIT1_TO_0,0), \
526 ifnull(UNIT2_TO_1,0), \
527 ifnull(UNIT3_TO_2,0), \
528 ifnull(UNIT4_TO_3,0), \
529 ifnull(UNIT5_TO_4,0), \
530 ifnull(UNIT6_TO_5,0), \
531 ifnull(UNIT7_TO_6,0), \
532 ifnull(UNIT8_TO_7,0), \
533 ifnull(UNIT9_TO_8,0) \
534 FROM MONEY \
535 WHERE NAME = ? AND \
536 YEAR = ? AND \
537 NATION = ?", \
538 (name,year,nation))
539 rate_list = []
540 for r in bdd_cursor:
541 rate_list.append(r)
542
543 bdd_cursor.close()
544
545 return rate_list
546
547
548
549
550
551
552
554 """
555 Allow to read the different language available for Pycalcar
556
557 PARAMETERS
558 ==========
559 None
560
561 RETURNS
562 =======
563 A list that contains the different language available
564 """
565 bdd_cursor = self.bdd_pycalcar.cursor()
566 bdd_cursor.execute("SELECT DISTINCT LANG \
567 FROM CONFIG")
568 lang_list = []
569 for r in bdd_cursor:
570 lang_list.append(r)
571
572 bdd_cursor.close()
573
574 return lang_list
575
576
577
578
579
580
581
583 """
584 Allow to read the selected language for Pycalcar
585
586 PARAMETERS
587 ==========
588 None
589
590 RETURNS
591 =======
592 The selected language for software
593 """
594 bdd_cursor = self.bdd_pycalcar.cursor()
595 bdd_cursor.execute("SELECT MESSAGE \
596 FROM CONFIG \
597 WHERE LANG = 'ALL' AND \
598 SCREEN = 'ALL' AND\
599 ACTION = 'LANGUAGE'")
600 lang = []
601 for r in bdd_cursor:
602 lang.append(r)
603
604 bdd_cursor.close()
605
606 return lang[0][0]
607
608
609
610
611
612
613
615 """
616 Allow to set the selected language for Pycalcar
617
618 PARAMETERS
619 ==========
620 language
621 --------
622 The selected language by user
623
624 RETURNS
625 =======
626 None
627 """
628 bdd_cursor = self.bdd_pycalcar.cursor()
629 bdd_cursor.execute("UPDATE CONFIG \
630 SET MESSAGE = ?\
631 WHERE LANG = 'ALL' AND \
632 SCREEN = 'ALL' AND\
633 ACTION = 'LANGUAGE'", (language,))
634
635 bdd_cursor.close()
636 self.bdd_pycalcar.commit()
637
638
639
640
641
642
643
644 - def f_read_rate(self,name1,year1,nation1,name2,year2,nation2):
645 """
646 Allow to read money's rate conversion
647
648 PARAMETERS
649 ==========
650 name1
651 -----
652 Name of the first money
653 year1
654 -----
655 Year of creation of the first money
656 nation1
657 -------
658 Nation of the first money
659 name2
660 -----
661 Name of the second money
662 year2
663 -----
664 Year of creation of the second money
665 nation2
666 -------
667 Nation of the second money
668 rate
669 ----
670 Rate to convert money 1 into money 2
671
672 RETURNS
673 =======
674 A list that contains the rate for conversion between the selected moneys
675
676 """
677 bdd_cursor = self.bdd_pycalcar.cursor()
678 bdd_cursor.execute("SELECT rate1_to_2 \
679 FROM RATE \
680 WHERE NAME1 = ? AND \
681 YEAR1 = ? AND \
682 NATION1 = ? AND \
683 NAME2 = ? AND \
684 YEAR2 = ? AND \
685 NATION2 = ?", \
686 (name1,year1,nation1,name2,year2,nation2))
687 rate_list = []
688 for r in bdd_cursor:
689 rate_list.append(r)
690
691 bdd_cursor.close()
692
693 try:
694 return float(rate_list[0][0])
695 except:
696 return 0
697
698
699
700
701
703 """
704 Allow to know the available money for convertion end the rate to apply
705
706 PARAMETERS
707 ==========
708 name
709 ----
710 The name of source money
711 year
712 ----
713 The year of creation of the source money
714 nation
715 ------
716 The nation of the source money
717
718 RETURNS
719 =======
720 A list that contains the name, the year, the nation and the rate of available money
721
722 """
723 bdd_cursor = self.bdd_pycalcar.cursor()
724 bdd_cursor.execute("SELECT DISTINCT NAME2,YEAR2,NATION2,rate1_to_2 \
725 FROM RATE \
726 WHERE NAME1 = ? AND \
727 YEAR1 = ? AND \
728 NATION1 = ?", (name,year,nation))
729 combobox_list = []
730 for r in bdd_cursor:
731 combobox_list.append(r)
732
733 bdd_cursor.close()
734
735
736 return combobox_list
737
738
739
740
741
742
744 """
745 Allow to get the different messages in the selected language
746
747 PARAMETERS
748 ==========
749 language
750 --------
751 The language selected by user
752
753 RETURNS
754 =======
755 A list that contains the different messages by tuples
756
757 """
758 bdd_cursor = self.bdd_pycalcar.cursor()
759 bdd_cursor.execute("SELECT ACTION, MESSAGE \
760 FROM CONFIG \
761 WHERE LANG = ? AND \
762 SCREEN = 'ALL' AND \
763 ACTION LIKE 'MESSAGE%'", (language,))
764 message_list = []
765 for r in bdd_cursor:
766 message_list.append(r)
767
768 bdd_cursor.close()
769
770
771 return message_list
772
773
774
775
776
777
778
780 """
781 Allow to extract text of IHM
782
783 PARAMETERS
784 ==========
785 language
786 --------
787 The language selected by user
788
789 RETURNS
790 =======
791 Different lists that contains the interface's text
792
793 """
794 bdd_cursor = self.bdd_pycalcar.cursor()
795 bdd_cursor.execute("SELECT ACTION, MESSAGE \
796 FROM CONFIG \
797 WHERE LANG = ? AND \
798 Screen = 'Toolbar'", (language,))
799 toolbar_list = []
800 for r in bdd_cursor:
801 toolbar_list.append(r)
802
803
804 bdd_cursor.execute("SELECT ACTION, MESSAGE \
805 FROM CONFIG \
806 WHERE LANG = ? AND \
807 Screen = 'CALC'", (language,))
808 calc_list = []
809 for r in bdd_cursor:
810 calc_list.append(r)
811
812
813 bdd_cursor.execute("SELECT ACTION, MESSAGE \
814 FROM CONFIG \
815 WHERE LANG = ? AND Screen = 'CONV'", (language,))
816 conv_list = []
817 for r in bdd_cursor:
818 conv_list.append(r)
819
820
821 bdd_cursor.execute("SELECT ACTION, MESSAGE \
822 FROM CONFIG \
823 WHERE LANG = ? AND \
824 Screen = 'PARAM'", (language,))
825 param_list = []
826 for r in bdd_cursor:
827 param_list.append(r)
828
829
830 bdd_cursor.execute("SELECT ACTION, MESSAGE \
831 FROM ABOUT")
832 about_list = []
833 for r in bdd_cursor:
834 about_list.append(r)
835
836 bdd_cursor.close()
837
838 return toolbar_list, calc_list, conv_list, param_list, about_list
839
840
841
842
843
844
845
846
847 if __name__ == '__main__':
848 try:
849 bdd = SqlitePycalcar()
850 list_money = bdd.f_read_money_name()
851 a = raw_input("ALL OK")
852 except:
853 print "erreur0:", sys.exc_info()
854 a = raw_input("\nKO")
855