Module sqlite_pycalcar
[hide private]
[frames] | no frames]

Source Code for Module sqlite_pycalcar

  1  #!/usr/bin/env python 
  2  # -*-coding:utf-8 -* 
  3   
  4  """ 
  5          Manage the DATABASE interface 
  6           
  7          G{importgraph} 
  8  """ 
  9   
 10  #       Pycalcar 
 11  #       Copyright (C) 2013 GALODE A. 
 12  # 
 13  #       This file is part of Pycalcar. 
 14  #  
 15  #       Pycalcar is free software: you can redistribute it and/or modify 
 16  #       it under the terms of the GNU General Public License as published by 
 17  #       the Free Software Foundation, either version 3 of the License, or 
 18  #       (at your option) any later version. 
 19  #  
 20  #       Pycalcar is distributed in the hope that it will be useful, 
 21  #       but WITHOUT ANY WARRANTY; without even the implied warranty of 
 22  #       MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the 
 23  #       GNU General Public License for more details. 
 24  # 
 25  #       You should have received a copy of the GNU General Public License 
 26  #       along with Pycalcar.  If not, see <http://www.gnu.org/licenses/> 
 27   
 28  import sqlite3 
 29  import sys 
 30  import os 
 31   
 32   
 33   
 34   
35 -class SqlitePycalcar:
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 # Init # 69 #===================================================#
70 - def __init__(self, path) :
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 # Creation d'une nouvelle monnaie # 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 # Creation de nouveaux taux de conversion # 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 # MAJ d'une monnaie # 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 # MAJ de taux de conversion entre monnaies # 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 # Effacement dune monnaie # 345 #===================================================#
346 - def p_del_money(self, name, year, nation):
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 # Effacement des taux lies a une monnaie # 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 # Recuperation des unites d'une monnaie # 452 #===================================================#
453 - def f_read_money_name(self):
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 # Recuperation des taux interne dune monnaie # 499 #===================================================#
500 - def f_read_money_rate(self, name, year, nation):
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 # Read the allow languages # 552 #===================================================#
553 - def f_read_lang(self):
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 # Read the selected language # 581 #===================================================#
582 - def f_config_get_language(self):
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 # Set the software language # 613 #===================================================#
614 - def p_config_set_language(self, language):
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 # Recuperation des taux de conversion entre monnaie # 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 # Donnees pour alimenter les combobox # 701 #===================================================#
702 - def f_read_conv_combox(self,name,year,nation):
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 # Recuperation des messages # 742 #===================================================#
743 - def f_read_message(self, language):
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 # Texte pour l'IHM # 778 #===================================================#
779 - def f_read_menu_text(self, language = "Francais"):
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 # Main de la classe # 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