API tools faq
paste
Advertisement
Guest User

Untitled

a guest
May 17th, 2018
160
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 8.70 KB | None | 0 0
raw download clone embed print report
  1. #HERE IT COMES; THE TRUTH IS HERE
  2. import pymysql
  3. import urllib.request, json
  4. from sqlalchemy.sql.expression import false
  5.  
  6.  
  7. def checkandinsert_zipcode(input_zipcode):
  8.     cnx1 = pymysql.connect(user='root', password='Ettema128611', host='127.0.0.1', database='databasenormala/s')
  9.     cursor1 = cnx1.cursor()
  10.     cursor1.execute("SELECT zipcode_key FROM tabel_zipcode WHERE zipcode = (%s)",input_zipcode)
  11.     cnx1.commit()
  12.  
  13.     if cursor1.rowcount ==0:
  14.         cursor1.execute("INSERT INTO tabel_zipcode (zipcode) VALUES (%s)", (str(input_zipcode)))
  15.         cnx1.commit()
  16.     else:
  17.         return false
  18.     cursor1.close()
  19.     cnx1.close()
  20.    
  21. def checkandinsert_city(input_city):
  22.     cnx1 = pymysql.connect(user='root', password='Ettema128611', host='127.0.0.1', database='databasenormala/s')
  23.     cursor1 = cnx1.cursor()
  24.     cursor1.execute("SELECT city_key FROM tabel_city WHERE city = (%s)",input_city)
  25.     cnx1.commit()
  26.  
  27.     if cursor1.rowcount ==0:
  28.         cursor1.execute("INSERT INTO tabel_city (city) VALUES (%s)", (str(input_city)))
  29.         cnx1.commit()
  30.     else:
  31.         return false
  32.     cursor1.close()
  33.     cnx1.close()
  34. def checkandinsert_comp(input_comp):
  35.     cnx1 = pymysql.connect(user='root', password='Ettema128611', host='127.0.0.1', database='databasenormala/s')
  36.     cursor1 = cnx1.cursor()  
  37.     cursor1.execute("SELECT comp_key FROM tabel_comp WHERE comp = (%s)",(str(input_comp)))
  38.     cnx1.commit()
  39.    
  40.     if cursor1.rowcount ==0:
  41.         cursor1.execute("INSERT INTO tabel_comp (comp) VALUES (%s)", (str(input_comp)))
  42.         cnx1.commit()  
  43.     else:
  44.         return false
  45.     cursor1.close()
  46.     cnx1.close()    
  47. def checkandinsert_address(input_address, input_zipcode,input_city, input_comp):
  48.     cnx1 = pymysql.connect(user='root', password='Ettema128611', host='127.0.0.1', database='databasenormala/s')
  49.     cursor1 = cnx1.cursor()
  50.    
  51.     cursor1.execute("SELECT city_key FROM tabel_city WHERE city = (%s)",str(input_city))
  52.     cnx1.commit()
  53.     city_key_fk=str(cursor1.fetchone()[0])
  54.    
  55.     cursor1.execute("SELECT comp_key FROM tabel_comp WHERE comp = (%s)",str(input_comp))
  56.     cnx1.commit()
  57.     comp_key_fk=str(cursor1.fetchone()[0])
  58.  
  59.     cursor1.execute("SELECT zipcode_key FROM tabel_zipcode WHERE zipcode = (%s)",str(input_zipcode))
  60.     cnx1.commit()
  61.     zipcode_key_fk=str(cursor1.fetchone()[0])
  62.  
  63.     cursor1.execute("SELECT * FROM tabel_address WHERE address = \'"+input_address+"\' and zipcode_key = \'"+zipcode_key_fk+"\' and city_key= \'"+city_key_fk+"\'and comp_key=\'"+comp_key_fk+"\'")
  64.     cnx1.commit()
  65.     if cursor1.rowcount ==0:
  66.         cursor1.execute("INSERT INTO tabel_address (address, comp_key, zipcode_key, city_key) VALUES (%s, %s,%s,%s)", (str(input_address),comp_key_fk,zipcode_key_fk, city_key_fk))
  67.         cnx1.commit()
  68.         cursor1.close()
  69.         cnx1.close()
  70.     else:
  71.         return false
  72.         cursor1.close()
  73.         cnx1.close()
  74.  
  75.  
  76. ############## SQL#########################
  77. from Functions import *
  78. import pymysql
  79. import urllib.request, json
  80.  
  81. cnx = pymysql.connect(user='root', password='Ettema128611', host='127.0.0.1', database='rossmann')
  82. cursor = cnx.cursor()
  83.  
  84. longadress_list=[]
  85. cursor.execute("SELECT Adresse, kette FROM filialen")
  86. cnx.commit()
  87. for adress in cursor.fetchall():
  88.     longadress_list.append(str(adress))
  89. for i in (longadress_list):
  90.     words = i.replace("(", "")              
  91.     words1= words.replace("'","")
  92.     words2= words1.replace(")","").split(",")
  93.     words3=words2[1].split(" ")
  94.     checkandinsert_comp(words2[2].strip())
  95.     checkandinsert_zipcode((str(words3[1].strip())))
  96.     checkandinsert_city((str(words3[2].strip())))
  97.     checkandinsert_address(str(words2[0]), str(words3[1].strip()), words3[2].strip(), words2[2].strip())
  98. ###################JSON#####################3
  99. from Functions import *
  100. import urllib.request, json
  101. data = json.load(open('rossmann.json'))
  102. for i in range(len(data)):
  103.     split_original_zipcode=((data[i]['stadt'].split(" ")))
  104.     print()
  105.     checkandinsert_zipcode(split_original_zipcode[0].strip())
  106.     checkandinsert_city(split_original_zipcode[1].strip())
  107.     checkandinsert_comp(((data[i]['kette'])))
  108.     checkandinsert_address((data[i]['strasse']), split_original_zipcode[0].strip(),split_original_zipcode[1].strip(), (data[i]['kette']))
  109.  
  110.  
  111. ############################GUI################################
  112. from tkinter import *
  113. from Functions import *
  114.  
  115. class Application:
  116.     def __init__(self, master):   #"master" represents the parent window, where the entry widget should be placed.
  117.         self.master = master
  118.         master.title("Competitors for Normal A/S")
  119.        
  120.  
  121.  
  122.         #Caption
  123.         self.label = Label(master, text="Entries for competitors for normal A/S", font=("",10))
  124.         self.label.grid(row = 0, column = 0, columnspan = 2, sticky = W)
  125.        
  126.        
  127.         #Shut down button
  128.         self.close_button = Button(master, text="Close", command=master.quit)
  129.         self.close_button.grid(row = 8, column = 5, columnspan = 2)
  130.        
  131.         #Compeptitor entry and label
  132.         self.comp_lbl = Label(master, text = "Competitors name")
  133.         self.comp_lbl.grid(row = 1, column = 0, columnspan = 4, sticky = W)
  134.         self.entry_comp= Entry(master,width=50)
  135.         self.entry_comp.grid(row=1,column=4,sticky=W)
  136.  
  137.         #Adress entry and label
  138.         self.adress_lbl = Label(master, text = "Address")
  139.         self.adress_lbl.grid(row = 2, column = 0, columnspan = 4, sticky = W)
  140.         self.entry_adress= Entry(master,width=50)
  141.         self.entry_adress.grid(row=2,column=4,sticky=W)
  142.        
  143.         #Zipcode entry and label
  144.         self.zipcode_lbl = Label(master, text = "Zipcode")
  145.         self.zipcode_lbl.grid(row = 3, column = 0, columnspan = 4, sticky = W)
  146.         self.entry_zipcode= Entry(master,width=50)
  147.         self.entry_zipcode.grid(row=3,column=4,sticky=W)
  148.        
  149.         self.zipcode_lbl = Label(master, text = "City")
  150.         self.zipcode_lbl.grid(row = 4, column = 0, columnspan = 4, sticky = W)
  151.         self.entry_city= Entry(master,width=50)
  152.         self.entry_city.grid(row=4,column=4,sticky=W)
  153.        
  154.         #Entry and label for "Entry Succesfull"
  155.         self.label1 = Label(master, text = "Was the entry succesfull?")
  156.         self.label1.grid(row = 5, column = 0, columnspan = 4, sticky = W)
  157.         self.entry_succesfull= Entry(master,width=50)
  158.         self.entry_succesfull.grid(row=5,column=4,sticky=W)
  159.        
  160.         #Add to database button
  161.         self.search_button = Button(master, text="Add to database", command=self.Ad_to_database)
  162.         self.search_button.grid(row = 6, column = 3, columnspan =2 , sticky = E)
  163.        
  164.         #function that bind esc to exit
  165.         self.master.bind('<Escape>', self.esc)
  166.    
  167.     def esc(self,empty):
  168.         self.master.quit()
  169.    
  170.     def Ad_to_database(self):
  171.         self.entry_succesfull.configure(bg = "white")
  172.         self.entry_succesfull.delete(0,END)
  173.         message_succes= "The information has entered the database"
  174.         message_already="The information was all ready in the database"
  175.         message_error = "Insufficient information, please try again"
  176.  
  177.         self.entry_succesfull.delete(0,END)
  178.         if len(self.entry_adress.get()) <=1 and len(self.entry_zipcode.get())<=1  and len(self.entry_comp.get())<=0:
  179.             self.entry_succesfull.insert(0,message_error)
  180.             self.entry_succesfull.configure(bg = "red")
  181.            
  182.         else:
  183.             if checkandinsert_comp(str(self.entry_comp.get()))== false and checkandinsert_zipcode(str(self.entry_zipcode.get()))== false and checkandinsert_address(str(self.entry_adress.get()), str(self.entry_zipcode.get()), str(self.entry_comp.get())) ==false:
  184.                 self.entry_succesfull.delete(0,END)
  185.                 self.entry_succesfull.insert(0,message_already)
  186.                 self.entry_succesfull.configure(bg = "yellow")
  187.                 self.clear_all_entries()
  188.             else:
  189.                 self.entry_succesfull.insert(0,message_succes)
  190.                 self.entry_succesfull.configure(bg = "green")
  191.                 checkandinsert_comp(str(self.entry_comp.get()))
  192.                 checkandinsert_zipcode(str(self.entry_zipcode.get()))
  193.                 checkandinsert_city(str(self.entry_city.get()))
  194.                 checkandinsert_address(str(self.entry_adress.get()), str(self.entry_zipcode.get()), str(self.entry_comp.get()))
  195.                 self.clear_all_entries()
  196.     def clear_all_entries(self):
  197.         self.entry_adress.delete(0,END)
  198.         self.entry_zipcode.delete(0,END)
  199.         self.entry_comp.delete(0,END)
  200.  
  201.  
  202. root = Tk()
  203. root.title("Kosmetik and more stores in Germany")
  204. root.geometry("670x200")
  205. my_gui = Application(root)
  206. root.mainloop()
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!