API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jul 15th, 2016
82
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 48.96 KB | None | 0 0
raw download clone embed print report
  1. import re
  2. from tkinter import *
  3. from tkinter.ttk import *
  4. import pymysql.cursors
  5.  
  6. class DBTest:
  7. #Initiates things we'll use
  8. def __init__(self, root):
  9. self._create_tabs()
  10. self._layout()
  11. #Create tabs
  12. def _create_tabs(self):
  13. self.frame = Frame(root) # Main Frame
  14. self.update_button = Button(self.frame, text="\"Update\"", command=lambda: self.rebuild_window()) # Rebuild window thing
  15. self.nb = Notebook(self.frame) # The 'tab-thing'
  16.  
  17. # Create main tabs
  18. customer = Frame(self.nb)
  19. order = Frame(self.nb)
  20. product = Frame(self.nb)
  21. # Create customer NB
  22. self.customer = Notebook(customer)
  23. # Create Customer-tabs
  24. customer.insert = Frame(self.customer)
  25. customer.select = Frame(self.customer)
  26. customer.selAll = Frame(self.customer)
  27. customer.delete = Frame(self.customer)
  28. customer.modify = Frame(self.customer)
  29. self._customer_insert_frame(customer.insert)
  30. self._customer_select_frame(customer.select)
  31. self._customer_selAll_frame(customer.selAll)
  32. self._customer_delete_frame(customer.delete)
  33. self._customer_modify_frame(customer.modify)
  34.  
  35. # Create product NB
  36. self.product = Notebook(product)
  37. # Create product-tabs
  38. product.insert = Frame(self.product)
  39. product.select = Frame(self.product)
  40. product.selAll = Frame(self.product)
  41. product.delete = Frame(self.product)
  42. product.modify = Frame(self.product)
  43. self._product_insert_frame(product.insert)
  44. self._product_select_frame(product.select)
  45. self._product_selAll_frame(product.selAll)
  46. self._product_delete_frame(product.delete)
  47. self._product_modify_frame(product.modify)
  48.  
  49. # Create order NB
  50. self.order = Notebook(order)
  51. # Create order-tabs
  52. order.insert = Frame(self.order)
  53. order.select = Frame(self.order)
  54. order.selAll = Frame(self.order)
  55. order.delete = Frame(self.order)
  56. self._order_insert_frame(order.insert)
  57. self._order_select_frame(order.select)
  58. self._order_selAll_frame(order.selAll)
  59. self._order_delete_frame(order.delete)
  60.  
  61. # Adds the tabs to the notebook
  62. self.nb.add(customer, text="Customer")
  63. self.nb.add(order, text="Order")
  64. self.nb.add(product, text="Product")
  65. self.customer.add(customer.insert, text="Insert")
  66. self.customer.add(customer.select, text="Select")
  67. self.customer.add(customer.selAll, text="Select ALL")
  68. self.customer.add(customer.delete, text="Delete Cust.")
  69. self.customer.add(customer.modify, text="Modify Cust.")
  70. self.product.add(product.insert, text="Insert")
  71. self.product.add(product.select, text="Select")
  72. self.product.add(product.selAll, text="Select ALL")
  73. self.product.add(product.delete, text="Delete Prod.")
  74. self.product.add(product.modify, text="Modify Prod.")
  75. self.order.add(order.insert, text="Insert")
  76. self.order.add(order.select, text="Select")
  77. self.order.add(order.selAll, text="Select ALL")
  78. self.order.add(order.delete, text="Delete Order")
  79.  
  80. def _layout(self):
  81. # Layout creation
  82. self.frame.grid(row=0, column=0, sticky=(N,S,W,E))
  83. self.update_button.grid(row=1, column=0)
  84. self.nb.grid(row=0, column=0, sticky=(N,S,W,E))
  85. self.customer.grid(row=0, column=0, columnspan=2, sticky=(N,S,W,E), pady=5)
  86. self.product.grid(row=0, column=0, columnspan=2, sticky=(N, S, W, E), pady=5)
  87. self.order.grid(row=0, column=0, columnspan=2, sticky=(N,S,W,E), pady=5)
  88. # Resize rules
  89. root.columnconfigure(0, weight=1)
  90. root.rowconfigure(0, weight=1)
  91. self.frame.columnconfigure(0, weight=2)
  92. self.frame.rowconfigure(0, weight=2)
  93.  
  94. #CUSTOMER section
  95. def _customer_insert_frame(self, insert):
  96. # Creates the Entry boxes to get data
  97. insert.name = Entry(insert)
  98. insert.address = Entry(insert)
  99. insert.phone = Entry(insert)
  100. # Makes a label and puts things out on the screen.
  101. Label(insert, text="Name: ").grid(row=0, column=0, sticky=E)
  102. insert.name.grid(row=0, column=1, sticky=W)
  103. Label(insert, text="Address: ").grid(row=1, column=0, sticky=E)
  104. insert.address.grid(row=1, column=1, sticky=W)
  105. Label(insert, text="Phone: ").grid(row=2, column=0, sticky=E)
  106. insert.phone.grid(row=2, column=1, sticky=W)
  107. # Using 'lambda: ' to prevent prepare_insert from running before we want it to.
  108. insert.submit_button = Button(insert, text="Submit", command=lambda: self.customer_prepare_insert(insert))
  109. insert.submit_button.grid(row=4, column=0, columnspan=2)
  110. insert.text = Text(insert, width=34, height=4, wrap=WORD)
  111. insert.text.grid(row=5,column=0,columnspan=2)
  112.  
  113. def customer_prepare_insert(self, insert):
  114. """ Reg ex for the phone number.
  115. Explanation:
  116. [+]? = First char can be a +, not needed tho
  117. [\d -]* = allow unlimited digits (0-9), spaces and -
  118. {3,} = Specifies at least 3 numbers."""
  119. pattern = re.compile(r"^[+]?[\d -]{3,}")
  120. # Gets values from the Entry boxes above.
  121. name = insert.name.get()
  122. address = insert.address.get()
  123. phone = insert.phone.get()
  124.  
  125. # Checks if every field has something in them. (Does not check if only a space)
  126. if not all((name, address, phone)):
  127. errorText = "Need to enter something in all boxes."
  128. error = True
  129. # Checks if the phone-pattern matches our regEx
  130. elif not pattern.match(phone):
  131. error = True
  132. errorText = "Phone number can only contain 0-9, spaces and dashes (-).\nFirst symbol can be + for country codes."
  133. else:
  134. error = False
  135. # If we have an error, throw the error text
  136. if error:
  137. text = errorText
  138. # Else does the insert into DB.
  139. else:
  140. text = self.add_to_DB('CUSTOMER',name, address, phone)
  141.  
  142. insert.text.config(state=NORMAL)
  143. insert.text.delete(0.0, END)
  144. insert.text.insert(0.0, text)
  145. insert.text.config(state=DISABLED)
  146.  
  147. def _customer_select_frame(self, select):
  148. # Create the Entry boxes
  149. select.sID = Entry(select)
  150. select.name = Entry(select)
  151. select.address = Entry(select)
  152. select.phone = Entry(select)
  153.  
  154. # Puts them out on the frame
  155. Label(select, text="Search for Databasedata\nenter at least 1 searchfield").grid(row=0, column=0, columnspan=2)
  156. Label(select, text="Search ID:").grid(row=1, column=0, sticky=E)
  157. select.sID.grid(row=1, column=1, sticky=W)
  158. Label(select, text="Search Name:").grid(row=2, column=0, sticky=E)
  159. select.name.grid(row=2, column=1, sticky=W)
  160. Label(select, text="Search Address").grid(row=3, column=0, sticky=E)
  161. select.address.grid(row=3, column=1, sticky=W)
  162. #Label(select, text="Search phone").grid(row=4, column=0, sticky=E)
  163. #select.phone.grid(row=4, column=1, sticky=W)
  164. # Using lambda again on the button to not make it do the
  165. # prepare before we actually click the button
  166. select.submit_button = Button(select, text="Submit", command=lambda: self.customer_prepare_select(select))
  167. select.submit_button.grid(row=5, column=0, columnspan=2)
  168. select.text = Text(select, width=30, height=10, wrap=WORD)
  169. select.text.grid(row=6,column=0,columnspan=2)
  170.  
  171. def customer_prepare_select(self, select):
  172. # Get value from the search fields
  173. sID = select.sID.get()
  174. name = select.name.get()
  175. address = select.address.get()
  176. phone = select.phone.get()
  177. args = {}
  178. stmt = ""
  179. # Checks at least one have data. (Does not check if only a space)
  180. if sID != "" or name != "" or address != "" or phone != "":
  181. # If at least one have data we check which
  182. # one and adds the string for the query
  183. if sID != "":
  184. args['ID'] = "id='"+sID+"' AND "
  185. if name != "":
  186. args['name'] = "name='"+name+"' AND "
  187. if address != "":
  188. args['address'] = "address='"+address+"' AND "
  189. #This is for checking by the phone number but search by that felt a bit stupid so leaving it out:
  190. # if phone != "":
  191. # args['phone'] = "phone='"+phone+"' AND"
  192. for key, value in args.items():
  193. stmt += value
  194. # Removes the last ' AND '
  195. stmt = stmt[:-5]
  196. sql = "SELECT * FROM CUSTOMER WHERE {val}".format(val=stmt)
  197. db_result = self.get_from_DB(sql)
  198. textBox = ""
  199. # If we get rows back
  200. if db_result:
  201. # Iterates for all rows gotten
  202. for key in db_result:
  203. textBox += "ID: {id} Name: {name}\nAddress: {address}\nPhone: {phone}\n\n".format(id=key['id'], name=key['name'], address=key['address'], phone=int(key['phone']))
  204. # If we searched for an entry that didnt exist
  205. else:
  206. textBox = "Could not find what you searched for."
  207. # If we enter no value in any box.
  208. else:
  209. textBox = "Must insert at least one value."
  210.  
  211. # Updates the textfield
  212. select.text.config(state=NORMAL)
  213. select.text.delete(0.0, END)
  214. select.text.insert(0.0, textBox)
  215. select.text.config(state=DISABLED)
  216.  
  217. def _customer_selAll_frame(self, selAll):
  218. #Create the button to show everything
  219. Label(selAll, text="Fetch all entries?").grid(row=0, column=0, sticky=E)
  220. selAll.submit_button = Button(selAll, text="Fetch!", command=lambda: self.customer_select_all_DB(selAll))
  221. selAll.submit_button.grid(row=0, column=1, sticky=W)
  222. selAll.text = Text(selAll, width=30, height=15, wrap=WORD)
  223. selAll.text.grid(row=1, column=0, columnspan=2)
  224.  
  225. def customer_select_all_DB(self, selAll):
  226. sql = "SELECT * from CUSTOMER"
  227. # Gets all entries from the DB
  228. db_result = self.get_from_DB(sql)
  229. textBox = ""
  230. # If we get result returned
  231. if db_result:
  232. # Iterates for every Row
  233. for key in db_result:
  234. textBox += "ID: {id} Name: {name}\nAddress: {address}\nPhone: {phone}\n\n".format(id=key['id'], name=key['name'], address=key['address'], phone=key['phone'])
  235. # If no entries was found in the DB
  236. else:
  237. textBox = "There's nothing in the Database or some error n shit."
  238. # Updates the textfield
  239. selAll.text.config(state=NORMAL)
  240. selAll.text.delete(0.0, END)
  241. selAll.text.insert(0.0, textBox)
  242. selAll.text.config(state=DISABLED)
  243.  
  244. def _customer_delete_frame(self, delete):
  245. Label(delete, text="Delete a user with the dropdown menu").grid(row=0, column=0, columnspan=2, sticky=E)
  246. # Creates the menu
  247. # The variable we need to "get"-value from
  248. delete.var = StringVar()
  249. delete.var.set("None")
  250. # Gets the list with usernames:
  251. delete.users = self.get_customer_list()
  252. delete.menu = OptionMenu(delete, delete.var, *delete.users[0])
  253. delete.menu.grid(row=1, column=0)
  254. delete.submit_button = Button(delete, text="DELETE!", command=lambda: self.customer_delete_from_DB(delete))
  255. delete.submit_button.grid(row=2)
  256. delete.text = Text(delete, width=34, height=15, wrap=WORD)
  257. delete.text.grid(row=3, column=0, columnspan=2)
  258.  
  259. def customer_delete_from_DB(self, delete):
  260. # Gets the name we want to delete
  261. # and sets from which tab(table) and column
  262. custName = delete.var.get()
  263. custID = delete.users[1].get(custName)
  264. table = "customer"
  265. x = self.delete_from_DB(table, custID, custName)
  266. delete.text.config(state=NORMAL)
  267. delete.text.delete(0.0, END)
  268. delete.text.insert(0.0, x)
  269. delete.text.config(state=DISABLED)
  270.  
  271. def _customer_modify_frame(self, modify):
  272. # Makes the LabelFrames
  273. self.modify_cust = LabelFrame(modify, text="Who do you want to change?", labelanchor=N)
  274. self.modify_cust.grid(row=0, column=0, sticky=(N, S, W, E))
  275. self.modify_values = LabelFrame(modify, text="Update with...", labelanchor=N)
  276. self.modify_values.grid(row=1, column=0, sticky=(N, S, W, E))
  277. self.modify_submit = LabelFrame(modify, text="Submit changes", labelanchor=N)
  278. self.modify_submit.grid(row=2, column=0, sticky=(N, S, W, E))
  279. # Creates text fields
  280. modify.newAddress = Entry(self.modify_values)
  281. modify.newPhone = Entry(self.modify_values)
  282. ## Customer Frame
  283. # default Vars
  284. modify.customerVar = StringVar()
  285. modify.customerVar.set("None")
  286. # Get the list of customers
  287. modify.customers = self.get_customer_list()
  288. modify.customerMenu = OptionMenu(self.modify_cust, modify.customerVar, *modify.customers[0])
  289. modify.customerMenu.grid(row=0, column=0, sticky=(W, E))
  290. ## New values field
  291. Label(self.modify_values, text="New Phone: ").grid(row=0, column=0, sticky=W)
  292. modify.newPhone.grid(row=0, column=1, sticky=W)
  293. Label(self.modify_values, text="New Address: ").grid(row=1, column=0, sticky=W)
  294. modify.newAddress.grid(row=1, column=1, sticky=W)
  295. ## Submit button field
  296. modify.submit_button = Button(self.modify_submit, text="Submit",
  297. command=lambda: self.customer_prepare_modify(modify))
  298. modify.submit_button.grid(row=0, column=1, sticky=W)
  299. modify.text = Text(modify, width=34, heigh=10, wrap=WORD)
  300. modify.text.grid(row=3, column=0, columnspan=2, sticky=(N, S, W, E))
  301.  
  302. def customer_prepare_modify(self, modify):
  303. """ Reg ex for the phone number.
  304. Explanation:
  305. [+]? = First char can be a +, not needed tho
  306. [\d -]* = allow unlimited digits (0-9), spaces and -
  307. {3,} = Specifies at least 3 numbers."""
  308. pattern = re.compile(r"^[+]?[\d -]{3,}")
  309. # Sets up the vars
  310. error = False
  311. customer = modify.customerVar.get()
  312. phone = modify.newPhone.get()
  313. address = modify.newAddress.get()
  314. fields = []
  315. text = ""
  316. # Checks if anything is filled
  317. if customer == "None":
  318. text += "Got to choose a customer.\n\n"
  319. error = True
  320. if phone == "" and address == "":
  321. text += "At least one field must be changed (Contain a value).\n\n"
  322. error = True
  323. #Checking the phone number
  324. if not pattern.match(phone) and not phone == "" and not error:
  325. text = "Phone number can only contain 0-9, spaces and dashes (-).\nFirst symbol can be + for country codes."
  326. error = True
  327. #Checks the done changes
  328. if not error:
  329. if phone:
  330. fields.append('phone')
  331. fields.append(phone)
  332. if address:
  333. fields.append('address')
  334. fields.append(address)
  335. text = self.update_DB('modCustomer', customer, fields)
  336. modify.text.config(state=NORMAL)
  337. modify.text.delete(0.0, END)
  338. modify.text.insert(0.0, text)
  339. modify.text.config(state=DISABLED)
  340.  
  341. #PRODUCT section
  342. def _product_insert_frame(self, insert):
  343. # Creates the Entry boxes to get data
  344. insert.name = Entry(insert)
  345. insert.price = Entry(insert)
  346. insert.unit = Entry(insert)
  347. # Makes a label and puts things out on the screen.
  348. Label(insert, text="Name: ").grid(row=0, column=0, sticky=E)
  349. insert.name.grid(row=0, column=1, sticky=W)
  350. Label(insert, text="Price: ").grid(row=1, column=0, sticky=E)
  351. insert.price.grid(row=1, column=1, sticky=W)
  352. Label(insert, text="Unit: ").grid(row=2, column=0, sticky=E)
  353. insert.unit.grid(row=2, column=1, sticky=W)
  354. # Using 'lambda: ' to prevent prepare_insert from running before we want it to.
  355. insert.submit_button = Button(insert, text="Submit", command=lambda: self.product_prepare_insert(insert))
  356. insert.submit_button.grid(row=4, column=0, columnspan=2)
  357. insert.text = Text(insert, width=34, height=4, wrap=WORD)
  358. insert.text.grid(row=5, column=0, columnspan=2)
  359.  
  360. def product_prepare_insert(self, insert):
  361. # Gets values from the Entry boxes above.
  362. name = insert.name.get()
  363. price = insert.price.get()
  364. unit = insert.unit.get()
  365. pattern = re.compile(r"^[0-9]*$")
  366.  
  367. # Checks if every field has something in them. (Does not check if only a space)
  368. # If we have an error, throw the error text
  369. if not all((name, price, unit)):
  370. errorText = "Need to enter something in all boxes."
  371. error = True
  372. #Checks that the pattern matches the regular expression, which only accepts numbers. 0 alone is not accepted.
  373. elif not pattern.match(price):
  374. error = True
  375. errorText = "Price needs to be a number (0-9) and above 0."
  376. elif not pattern.match(unit):
  377. error = True
  378. errorText = "Price needs to be a number (0-9) and above 0."
  379. # Else does the insert into DB since there's no error.
  380. else:
  381. error = False
  382.  
  383. if error:
  384. text = errorText
  385. else:
  386. text = self.add_to_DB('PRODUCT', name, price, unit)
  387.  
  388. insert.text.config(state=NORMAL)
  389. insert.text.delete(0.0, END)
  390. insert.text.insert(0.0, text)
  391. insert.text.config(state=DISABLED)
  392.  
  393. def _product_select_frame(self, select):
  394. # Create the Entry boxes
  395. select.sID = Entry(select)
  396. select.name = Entry(select)
  397. select.price = Entry(select)
  398. select.unit = Entry(select)
  399.  
  400. # Puts them out on the frame
  401. Label(select, text="Search for Databasedata\nenter at least 1 searchfield").grid(row=0, column=0, columnspan=2)
  402. Label(select, text="Search ID:").grid(row=1, column=0, sticky=E)
  403. select.sID.grid(row=1, column=1, sticky=W)
  404. Label(select, text="Search Name:").grid(row=2, column=0, sticky=E)
  405. select.name.grid(row=2, column=1, sticky=W)
  406. Label(select, text="Search price").grid(row=3, column=0, sticky=E)
  407. select.price.grid(row=3, column=1, sticky=W)
  408. Label(select, text="Search unit").grid(row=4, column=0, sticky=E)
  409. select.unit.grid(row=4, column=1, sticky=W)
  410. # Using lambda again on the button to not make it do the
  411. # prepare before we actually click the button
  412. select.submit_button = Button(select, text="Submit", command=lambda: self.product_prepare_select(select))
  413. select.submit_button.grid(row=5, column=0, columnspan=2)
  414. select.text = Text(select, width=34, height=10, wrap=WORD)
  415. select.text.grid(row=6, column=0, columnspan=2)
  416.  
  417. def product_prepare_select(self, select):
  418. # Get value from the search fields.
  419. sID = select.sID.get()
  420. name = select.name.get()
  421. price = select.price.get()
  422. unit = select.unit.get()
  423. args = {}
  424. stmt = ""
  425. # Checks at least one have data. (Does not check if only a space)
  426. if sID != "" or name != "" or price != "" or unit != "":
  427. # If at least one have data we check which
  428. # one and adds the string for the query
  429. if sID != "":
  430. args['ID'] = "id='" + sID + "' AND "
  431. if name != "":
  432. args['name'] = "name='" + name + "' AND "
  433. if price != "":
  434. args['price'] = "price='" + price + "' AND "
  435. if unit != "":
  436. args['unit'] = "unit='" + unit + "' AND "
  437. for key, value in args.items():
  438. stmt += value
  439. # Removes the last ' AND '
  440. stmt = stmt[:-5]
  441. sql = "SELECT * FROM PRODUCT WHERE {val}".format(val=stmt)
  442. db_result = self.get_from_DB(sql)
  443. textBox = ""
  444. # If we get rows back
  445. if db_result:
  446. # Iterates for all rows gotten
  447. for key in db_result:
  448. textBox += "ID: {id} Name: {name}\nPrice: {price}\nUnit: {unit}\n\n".format(id=key['id'],
  449. name=key['name'],
  450. price=key[
  451. 'price'],
  452. unit=key[
  453. 'unit'])
  454. # If we searched for an entry that didnt exist
  455. else:
  456. textBox = "Could not find what you searched for."
  457. # If we enter no value in any box.
  458. else:
  459. textBox = "Must insert at least one value."
  460.  
  461. # Updates the textfield
  462. select.text.config(state=NORMAL)
  463. select.text.delete(0.0, END)
  464. select.text.insert(0.0, textBox)
  465. select.text.config(state=DISABLED)
  466.  
  467. def _product_selAll_frame(self, selAll):
  468. # Create the button to show everything
  469. Label(selAll, text="Fetch all entries?").grid(row=0, column=0, sticky=E)
  470. selAll.submit_button = Button(selAll, text="Fetch!", command=lambda: self.product_select_all_DB(selAll))
  471. selAll.submit_button.grid(row=0, column=1, sticky=W)
  472. selAll.text = Text(selAll, width=34, height=15, wrap=WORD)
  473. selAll.text.grid(row=1, column=0, columnspan=2)
  474.  
  475. def product_select_all_DB(self, selAll):
  476. sql = "SELECT * from PRODUCT"
  477. # Gets all entries from the DB
  478. db_result = self.get_from_DB(sql)
  479. textBox = ""
  480. # If we get result returned
  481. if db_result:
  482. # Iterates for every Row
  483. for key in db_result:
  484. textBox += "ID: {id} Name: {name}\nPrice: {price}\nUnit: {unit}\n\n".format(id=key['id'],
  485. name=key['name'],
  486. price=key[
  487. 'price'],
  488. unit=key['unit'])
  489. # If no entries was found in the DB
  490. else:
  491. textBox = "There's nothing in the Database or some error n shit."
  492. # Updates the textfield
  493. selAll.text.config(state=NORMAL)
  494. selAll.text.delete(0.0, END)
  495. selAll.text.insert(0.0, textBox)
  496. selAll.text.config(state=DISABLED)
  497.  
  498. def _product_delete_frame(self, delete):
  499. Label(delete, text="Delete a product with the dropdown menu").grid(row=0, column=0, columnspan=2, sticky=E)
  500. # Creates the menu
  501. # The variable we need to "get"-value from
  502. delete.var = StringVar()
  503. delete.var.set("None")
  504. # Gets the list with products:
  505. delete.products = self.get_product_list()
  506. delete.menu = OptionMenu(delete, delete.var, *delete.products[0])
  507. delete.menu.grid(row=1, column=0)
  508. delete.submit_button = Button(delete, text="DELETE!", command=lambda: self.product_delete_from_DB(delete))
  509. delete.submit_button.grid(row=2)
  510. delete.text = Text(delete, width=34, height=15, wrap=WORD)
  511. delete.text.grid(row=3, column=0, columnspan=2)
  512.  
  513. def product_delete_from_DB(self, delete):
  514. # Gets the product we want to delete
  515. # and sets from which tab(table) and column
  516. prodName = delete.var.get()
  517. prodID = delete.products[1].get(prodName)
  518. table = "product"
  519. x = self.delete_from_DB(table, prodID, prodName)
  520. delete.text.config(state=NORMAL)
  521. delete.text.delete(0.0, END)
  522. delete.text.insert(0.0, x)
  523. delete.text.config(state=DISABLED)
  524.  
  525. def _product_modify_frame(self, modify):
  526. # Makes the LabelFrames
  527. self.modify_prod = LabelFrame(modify, text="What do you want to change?", labelanchor=N)
  528. self.modify_prod.grid(row=0, column=0, sticky=(N, S, W, E))
  529. self.modify_values = LabelFrame(modify, text="Update with...", labelanchor=N)
  530. self.modify_values.grid(row=1, column=0, sticky=(N, S, W, E))
  531. self.modify_submit = LabelFrame(modify, text="Submit changes", labelanchor=N)
  532. self.modify_submit.grid(row=2, column=0, sticky=(N, S, W, E))
  533. # Creates text fields
  534. modify.newUnit = Entry(self.modify_values)
  535. modify.newPrice = Entry(self.modify_values)
  536. ## Product Frame
  537. # default Vars
  538. modify.productVar = StringVar()
  539. modify.productVar.set("None")
  540. # Get the list of products
  541. modify.products = self.get_product_list()
  542. modify.productMenu = OptionMenu(self.modify_prod, modify.productVar, *modify.products[0])
  543. modify.productMenu.grid(row=0, column=0, sticky=(W, E))
  544. ## New values field
  545. Label(self.modify_values, text="New amount: ").grid(row=0, column=0, sticky=W)
  546. modify.newUnit.grid(row=0, column=1, sticky=W)
  547. Label(self.modify_values, text="New price: ").grid(row=1, column=0, sticky=W)
  548. modify.newPrice.grid(row=1, column=1, sticky=W)
  549. ## Submit button field
  550. modify.submit_button = Button(self.modify_submit, text="Submit",
  551. command=lambda: self.product_prepare_modify(modify))
  552. modify.submit_button.grid(row=0, column=1, sticky=W)
  553. modify.text = Text(modify, width=34, heigh=10, wrap=WORD)
  554. modify.text.grid(row=3, column=0, columnspan=2, sticky=(N, S, W, E))
  555.  
  556. def product_prepare_modify(self, modify):
  557. pattern = re.compile(r"^[0-9]*$")
  558. # Sets up the vars
  559. error = False
  560. product = modify.productVar.get()
  561. unit = modify.newUnit.get()
  562. price = modify.newPrice.get()
  563. fields = []
  564. text = ""
  565. # Checks if anything is filled
  566. if product == "None":
  567. text += "Got to choose a product.\n\n"
  568. error = True
  569. if unit == "" and price == "":
  570. text += "At least one field must be changed (Contain a value).\n\n"
  571. error = True
  572. if not pattern.match(unit) and not unit == "" and not error:
  573. text = "The amount needs to be a number (0-9) and above 0."
  574. error = True
  575. if not pattern.match(price) and not price == "" and not error:
  576. text = "The price needs to be a number (0-9) and above 0."
  577. error = True
  578. #Checks the unit has no errors
  579. if not error:
  580. if unit:
  581. fields.append('unit')
  582. fields.append(unit)
  583. #Checks the price has no errors
  584. if price:
  585. fields.append('price')
  586. fields.append(price)
  587. text = self.update_DB('modProduct', product, fields)
  588. modify.text.config(state=NORMAL)
  589. modify.text.delete(0.0, END)
  590. modify.text.insert(0.0, text)
  591. modify.text.config(state=DISABLED)
  592.  
  593. #ORDERS section
  594. def _order_insert_frame(self, insert):
  595. # Makes the Customer and Product Frames
  596. self.order_customer = LabelFrame(insert, text="Buyer: ", labelanchor=N)
  597. self.order_customer.grid(row=0, column=0, sticky=(N,S,W,E))
  598. self.order_product = LabelFrame(insert, text="Product: ",labelanchor=N)
  599. self.order_product.grid(row=1, column=0, sticky=(N,S,W,E))
  600. self.order_amount = LabelFrame(insert, text="Amount: ", labelanchor=N)
  601. self.order_amount.grid(row=2, column=0, sticky=(N,S,W,E))
  602. self.order_confirm = LabelFrame(insert, text="Confirm: ",labelanchor=N)
  603. self.order_confirm.grid(row=3, column=0, sticky=(N,S,W,E))
  604. # Some default Vars
  605. insert.productVar = StringVar()
  606. insert.productVar.set("None")
  607. insert.totalUnits = IntVar()
  608. insert.customerVar = StringVar()
  609. insert.customerVar.set("None")
  610. # Gets the list of customers
  611. insert.customers = self.get_customer_list()
  612. insert.customerMenu = OptionMenu(self.order_customer, insert.customerVar, *insert.customers[0])
  613. insert.customerMenu.grid(row=0, column=1, sticky=EW)
  614. # Gets the list of products and current stocks
  615. insert.products = self.get_product_list()
  616. insert.unitLabel = Label(self.order_product, text="In stock: ~")
  617. insert.unitLabel.grid(row=0, column=1, sticky=W)
  618. insert.productMenu = OptionMenu(self.order_product, insert.productVar, *insert.products[0], command=lambda _: self.order_update_price(insert))
  619. insert.productMenu.config(width=15)
  620. insert.productMenu.grid(row=0, column=0, sticky="EW")
  621. # Enter how many you want to buy
  622. insert.amount = Entry(self.order_amount)
  623. insert.amount.grid()
  624. insert.insert_button = Button(self.order_confirm, text="Submit", command=lambda: self.order_prepare_insert(insert))
  625. insert.insert_button.grid(row=0, column=0)
  626. # Shows result.
  627. insert.text = Text(self.order_confirm, width=34, height=10, wrap=WORD)
  628. insert.text.grid(row=1, column=0,columnspan=2, sticky=(N,S,W,E))
  629. insert.text.config(state=DISABLED)
  630.  
  631. def order_prepare_insert(self, x):
  632. # Only allow numbers
  633. pattern = re.compile(r"^[^0][\d]{0,}")
  634. # Sets up the things we need to use
  635. error = False
  636. errorT = ""
  637. totUnit = x.totalUnits.get()
  638. amount = x.amount.get()
  639. cust = x.customerVar.get()
  640. custID = x.customers[1].get(cust, "CustError")
  641. prod = x.productVar.get()
  642. prodID = x.products[1].get(prod, "prodError")
  643.  
  644. # Try and see if amount is a number
  645. try:
  646. amount = int(amount)
  647. except Exception:
  648. error = True
  649. # Checks so everything is entered:
  650. if cust == "None" or prod == "None":
  651. error = True
  652. errorT = "Must choose a customer and/or product.\n"
  653. # If amount doesn't match pattern
  654. if not pattern.match(str(amount)):
  655. error = True
  656. errorT += "Amount must contain numbers (0-9), can't be 0\n"
  657.  
  658. # Checks if we don't have an error
  659. if not error:
  660. if int(totUnit) - amount < 0:
  661. answer = "Can't order more units than what exist in stock."
  662. else:
  663. stock = int(totUnit) - int(amount)
  664. #answer = "Customer: {cust} with ID: {custid}\nOrdered: {amount} units\nProduct: {prod} with ID: {prodID}\nUnits left: {stock}".format(cust=cust, custid=custID, prod=prod, prodID=prodID, amount=amount, stock=stock)
  665. answer = self.add_to_DB('ORDERS', custID, prodID, amount)
  666. answer += "\n\n" + self.update_DB('productUnit', stock, prodID)
  667. else:
  668. answer = errorT
  669.  
  670. x.text.config(state=NORMAL)
  671. x.text.delete(0.0, END)
  672. x.text.insert(0.0, answer)
  673. x.text.config(state=DISABLED)
  674.  
  675. def order_update_price(self, insert):
  676. productName = insert.productVar
  677. unitsList = insert.products[2]
  678. errorT = False
  679. if productName.get() != "None":
  680. units = unitsList[productName.get()]
  681. else:
  682. units = 0
  683. try:
  684. insert.totalUnits.set(int(units))
  685. except Exception as e:
  686. errorT = True
  687. error = self.errorCodeHandler(e)
  688. if errorT:
  689. text = error
  690. else:
  691. text = "In stock: "+str(units)
  692. insert.unitLabel.config(text=text)
  693.  
  694. def _order_select_frame(self, select):
  695. # Create entry boxes
  696. select.orderID = Entry(select)
  697. select.custID = Entry(select)
  698. select.productID = Entry(select)
  699. # Puts them out on the frame
  700. Label(select, text="Search for Order-data\nenter at least 1 searchfield").grid(row=0, column=0, columnspan=2)
  701. Label(select, text="Order ID").grid(row=1, column=0, sticky=E)
  702. select.orderID.grid(row=1, column=1, sticky=W)
  703. Label(select, text="Customer").grid(row=2, column=0, sticky=E)
  704. select.custID.grid(row=2, column=1, sticky=W)
  705. Label(select, text="Product ID").grid(row=3, column=0, sticky=E)
  706. select.productID.grid(row=3, column=1, sticky=W)
  707.  
  708. # Using lambda to not do function until button is pressed
  709. select.submit_button = Button(select, text="Submit", command=lambda: self.order_prepare_select(select))
  710. select.submit_button.grid(row=5, column=0, columnspan=2)
  711. select.text = Text(select, width=34, height=10, wrap=WORD)
  712. select.text.grid(row=6, column=0, columnspan=2)
  713.  
  714. def order_prepare_select(self, select):
  715. # Get value from the search fields.
  716. custID = select.custID.get()
  717. orderID = select.orderID.get()
  718. productID = select.productID.get()
  719. args = {}
  720. stmt = ""
  721. # Checks at least one have data. (Does not check if only a space)
  722. if custID != "" or orderID != "" or productID != "":
  723. # If at least one have data we check which
  724. # one and adds the string for the query
  725. if custID != "":
  726. args['custID'] = "customer_id='" + custID + "' AND "
  727. if orderID != "":
  728. args['orderID'] = "id='" + orderID + "' AND "
  729. if productID != "":
  730. args['productID'] = "product_id='" + productID + "' AND "
  731. for key, value in args.items():
  732. stmt += value
  733. # Removes the last ' AND '
  734. stmt = stmt[:-5]
  735. sql = "SELECT * FROM ORDERS WHERE {val}".format(val=stmt)
  736. db_result = self.get_from_DB(sql)
  737. textBox = ""
  738. # If we get rows back
  739. if db_result:
  740. # Iterates for all rows gotten
  741. for key in db_result:
  742. textBox += "OrderID: {order} CustomerID: {cust}\nProductID: {prod}\nAmount: {x}\nOrderdate: {y}\n\n".format(
  743. order=str(key['id']), cust=str(key['customer_id']), prod=str(key['product_id']),
  744. x=str(key['amount']), y=str(key['date']))
  745. # If we searched for an entry that didnt exist
  746. else:
  747. textBox = "Could not find what you searched for."
  748. # If we enter no value in any box.
  749. else:
  750. textBox = "Must insert at least one value."
  751.  
  752. # Updates the textfield
  753. select.text.config(state=NORMAL)
  754. select.text.delete(0.0, END)
  755. select.text.insert(0.0, textBox)
  756. select.text.config(state=DISABLED)
  757.  
  758. def _order_selAll_frame(self, selAll):
  759. Label(selAll, text="Fetch all orders?").grid(row=0, column=0, sticky=E)
  760. selAll.submit_button = Button(selAll, text="Fetch!", command=lambda: self.order_select_all_DB(selAll))
  761. selAll.submit_button.grid(row=0, column=1, sticky=W)
  762. selAll.text = Text(selAll, width=34, height=15, wrap=WORD)
  763. selAll.text.grid(row=1, column=0, columnspan=2)
  764.  
  765. def order_select_all_DB(self, selAll):
  766. sql = "SELECT * from ORDERS"
  767. # Gets all entries from the DB
  768. db_result = self.get_from_DB(sql)
  769. textBox = ""
  770. # If we get result returned
  771. if db_result:
  772. # Iterates for every Row
  773. for key in db_result:
  774. textBox += "OrderID: {order} CustomerID: {cust}\nProductID: {prod}\nAmount: {x}\nOrderdate: {y}\n\n".format(
  775. order=str(key['id']), cust=str(key['customer_id']), prod=str(key['product_id']),
  776. x=str(key['amount']), y=str(key['date']))
  777. # If no entries was found in the DB
  778. else:
  779. textBox = "There's nothing in the Database or some error n shit."
  780. # Updates the textfield
  781. selAll.text.config(state=NORMAL)
  782. selAll.text.delete(0.0, END)
  783. selAll.text.insert(0.0, textBox)
  784. selAll.text.config(state=DISABLED)
  785.  
  786. def _order_delete_frame(self, delete):
  787. Label(delete, text="Delete an order with the dropdown menu").grid(row=0, column=0, columnspan=2, sticky=E)
  788. # Creates the menu
  789. # The variable we need to "get"-value from
  790. delete.var = StringVar()
  791. delete.var.set("None")
  792. # Gets the list with usernames:
  793. delete.orderID = self.get_order_list()
  794. delete.menu = OptionMenu(delete, delete.var, *delete.orderID)
  795. delete.menu.grid(row=1, column=0)
  796. delete.submit_button = Button(delete, text="DELETE!", command=lambda: self.order_delete_from_DB(delete))
  797. delete.submit_button.grid(row=2)
  798. delete.text = Text(delete, width=34, height=15, wrap=WORD)
  799. delete.text.grid(row=3, column=0, columnspan=2)
  800.  
  801. def order_delete_from_DB(self, delete):
  802. # Gets the name we want to delete
  803. # and sets from which tab(table) and column
  804. orderID = delete.var.get()
  805. if orderID != "None":
  806. table = "orders"
  807. x = self.delete_from_DB(table, orderID)
  808. else:
  809. x = "Choose an order to remove."
  810. delete.text.config(state=NORMAL)
  811. delete.text.delete(0.0, END)
  812. delete.text.insert(0.0, x)
  813. delete.text.config(state=DISABLED)
  814.  
  815. def delete_from_DB(self, table, value, *args):
  816. # Making the query
  817. # Takes the tab (order, customer, products), plus the ID of the thing to delete
  818. # This is to re-use this delete code for other tabs.
  819. sql = "DELETE FROM {tab} WHERE id='{value}'".format(tab=table.upper(), value=value)
  820. # Connection config
  821. connection = self.db_settings()
  822. # Try to do the thing
  823. try:
  824. with connection.cursor() as cursor:
  825. # Executes the delete-query
  826. cursor.execute(sql)
  827. #Commit the changes
  828. connection.commit()
  829. # Checks how many rows that was affected
  830. result = cursor.rowcount
  831. # If 1 (row deleted) then show message.
  832. if result == 1:
  833. if args:
  834. result = "{name} from {tab}-tab has been deleted from the Database.\n".format(name=args[0], tab=table)
  835. else:
  836. result = "The order with the ID: {id} was succesfully deleted.".format(id=value)
  837. # If 0, no rows affected, thus nothing deleted.
  838. elif result == 0:
  839. result = "Nothing got deleted."
  840. else:
  841. result = "Something weird happened. More than 1 row changed."
  842.  
  843. except Exception as e:
  844. result = self.errorCodeHandler(e)
  845. finally:
  846. connection.close()
  847.  
  848. # Return value
  849. return result
  850.  
  851. def add_to_DB(self, *args):
  852. """
  853. Accepts multiple args depending on what for.
  854. If args[0] is equal to, it wants these values:
  855. CUSTOMER: name, address, phone
  856. ORDER: customer_id, product_id, amount
  857. PRODUCT: name, unit (in stock), price (per unit)
  858. """
  859. # Connection config
  860. connection = self.db_settings()
  861. #Inserting the data
  862. try:
  863. # Adds it to DB
  864. with connection.cursor() as cursor:
  865. if args[0] == 'CUSTOMER':
  866. # Makes query for CUSTOMER
  867. # Args needed are args[1-3], name, address, phone.
  868. sql = "INSERT INTO CUSTOMER (name, address, phone) VALUES (%s, %s, %s)"
  869. cursor.execute(sql, (args[1], args[2], args[3]))
  870. result = "Data added\nName: {name}\nAddress: {address}\nPhone: {phone}".format(name=args[1],address=args[2],phone=args[3])
  871. elif args[0] == 'ORDERS':
  872. # Makes query for orders
  873. # Args needed are args[1-3], customer_id, product_id, amount
  874. sql = "INSERT INTO ORDERS (customer_id, product_id, amount) VALUES (%s, %s, %s)"
  875. cursor.execute(sql, [args[1], args[2], args[3]])
  876. result = "Data added\nCustomer ID: {custID}\nProduct ID: {prodID}\nAmount: {amount} ex.".format(custID=args[1], prodID=args[2], amount=args[3])
  877. elif args[0] == 'PRODUCT':
  878. # Makes query for PRODUCT
  879. # Args needed are args[1-3], name, unit, price
  880. sql = "INSERT INTO PRODUCT (name, unit, price) VALUES (%s, %s, %s)"
  881. cursor.execute(sql, (args[1], args[2], args[3]))
  882. result = "Data added\nProduct: {name}\nUnits: {unit} ex.\nPrice: {price}€ each".format(name=args[1], unit=args[2], price=args[3])
  883. # Comit the query
  884. connection.commit()
  885.  
  886. except Exception as e:
  887. result = self.errorCodeHandler(e)
  888. finally:
  889. connection.close()
  890. # Returns whatever result we got
  891. return result
  892.  
  893. def update_DB(self, *args):
  894. result = ""
  895. # Connection config
  896. connection = self.db_settings()
  897. # Modify data
  898. try:
  899. with connection.cursor() as cursor:
  900. # Updates the total units of a product
  901. # Used by self.order_prepare_insert
  902. if args[0] == 'productUnit':
  903. sql = "UPDATE PRODUCT SET PRODUCT.unit = '%s' WHERE PRODUCT.id = %s"
  904. cursor.execute(sql, (args[1], args[2]))
  905. result = "Updated product stock."
  906. # Updates a customer's data
  907. # Used by self.customer_prepare_modify
  908. elif args[0] == 'modCustomer':
  909. ## If the customerList is 2 (as in only one field to change)
  910. if len(args[2]) == 2:
  911. ## If that value is phone, change the phone field
  912. if args[2][0] == "phone":
  913. sql = "UPDATE CUSTOMER SET phone = %s WHERE CUSTOMER.name = %s"
  914. cursor.execute(sql, (args[2][1], args[1]))
  915. result = "Updated customer: " + args[1]
  916. ## If that value is address, change the address field
  917. elif args[2][0] == "address":
  918. sql = "UPDATE CUSTOMER SET address = %s WHERE CUSTOMER.name = %s"
  919. cursor.execute(sql, (args[2][1], args[1]))
  920. result = "Updated customer: " + args[1]
  921. ## If anything else is put, do wonky msg (shouldn't happen tho)
  922. else:
  923. result = "Error, not updating neither phone nor address"
  924. elif len(args[2]) == 4:
  925. sql = "UPDATE CUSTOMER SET phone = %s, address = %s WHERE CUSTOMER.name = %s"
  926. cursor.execute(sql, (args[2][1], args[2][3], args[1]))
  927. result = "Updated customer: " + args[1]
  928. elif args[0] == 'modProduct':
  929. if len(args[2]) == 2:
  930. if args[2][0] == "unit":
  931. sql = "UPDATE PRODUCT SET unit = %s WHERE PRODUCT.name = %s"
  932. cursor.execute(sql, (args[2][1], args[1]))
  933. result = "Updated product: " + args[1]
  934. elif args[2][0] == 'price':
  935. sql = "UPDATE PRODUCT SET price = %s WHERE PRODUCT.name = %s"
  936. cursor.execute(sql, (args[2][1], args[1]))
  937. result = "Updated product: " + args[1]
  938. else:
  939. result = "Error, not updating neither unit nor price"
  940. if len(args[2]) == 4:
  941. sql = "UPDATE PRODUCT SET unit = %s, price = %s WHERE PRODUCT.name = %s"
  942. cursor.execute(sql, (args[2][1], args[2][3], args[1]))
  943. result = "Updated customer: " + args[1]
  944. # Comit query
  945. connection.commit()
  946. except Exception as e:
  947. result = self.errorCodeHandler(e)
  948. finally:
  949. connection.close()
  950. return result
  951.  
  952.  
  953. def get_from_DB(self, sql):
  954. # Connection config
  955. connection = self.db_settings()
  956. # Try to get data
  957. try:
  958. with connection.cursor() as cursor:
  959. # The query from prepare_select()
  960. cursor.execute(sql)
  961. result = cursor.fetchall()
  962. except Exception as e:
  963. result = self.errorCodeHandler(e)
  964. finally:
  965. connection.close()
  966. return result
  967.  
  968. """Returns:
  969. # [0] = list with only names
  970. # [1] = dictionary name : id
  971. """
  972. def get_customer_list(self, *args):
  973. sql = "SELECT name, id FROM CUSTOMER"
  974. # Gets all customers and put them in a list
  975. users = self.get_from_DB(sql)
  976. user = []
  977. userID = {}
  978. # Iterates the Dict we got, and turns them into list with just names.
  979. if users:
  980. for key in users:
  981. user.append(key['name'])
  982. userID[key['name']] = key['id']
  983. # Alphabetical sorting, cuz why not
  984. user.sort()
  985. # Adds "None" to start of list, cuz else OptionMenu gets wonky
  986. user.insert(0, "None")
  987. return user, userID
  988.  
  989. """Returns:
  990. # List with order ID's only
  991. """
  992.  
  993. def get_order_list(self, *args):
  994. sql = "SELECT * FROM ORDERS"
  995. # Gets all orderID's and puts them in a list
  996. orders = self.get_from_DB(sql)
  997. result = []
  998. # Iterates the dict we get from DB and turns into a list with names.
  999. if orders:
  1000. for key in orders:
  1001. x = str(key['id'])
  1002. result.append(x)
  1003. # Adds "None" to the start of list, cuz else OptionMenu gets wonky
  1004. result.insert(0, "None")
  1005. return result
  1006.  
  1007. """Returns
  1008. # [0] = list with only names
  1009. # [1] = dictionary, name : id
  1010. # [2] = dictionary, name : units
  1011. """
  1012.  
  1013. def get_product_list(self, *args):
  1014. sql = "SELECT name, id, unit, price FROM PRODUCT"
  1015. # Gets all products and their ID and puts them in a list
  1016. db_products = self.get_from_DB(sql)
  1017. db_product = []
  1018. db_productID = {}
  1019. db_productUnit = {}
  1020. db_productPrice = {}
  1021. # Iterates through the dictionary we get back from DB
  1022. if db_products:
  1023. for key in db_products:
  1024. db_product.append(key['name'])
  1025. db_productID[key['name']] = key['id']
  1026. db_productUnit[key['name']] = key['unit']
  1027. db_productPrice[key['name']] = key['price']
  1028. # Alphabetical sorting, cuz why not
  1029. db_product.sort()
  1030. # Adds "None" to start of list, avoiding wonkyness
  1031. db_product.insert(0, "None")
  1032. return db_product, db_productID, db_productUnit
  1033.  
  1034. # Destroys all windows, and rebuilds.
  1035. # Got tired of trying other ways.
  1036. def rebuild_window(self):
  1037. self.frame.destroy()
  1038. self._create_tabs()
  1039. self._layout()
  1040.  
  1041. def errorCodeHandler(self, error):
  1042. # Simple "One place for all error codes"-thingy.
  1043. return {
  1044. 1062 : "Dublicate entry was found!",
  1045. 1451 : "Entry is tied to a foreign key (orders)\nCan't be deleted until the order tied to this entry is deleted."
  1046. # Panic message!
  1047. }.get(error.args[0], "Something weird happened, give this errorcode to someone more handy: "+str(error.args[0]) +"\n\nFor debug use:\n"+str(error))
  1048.  
  1049. ##########
  1050. #The settings for the database, change this:
  1051. ##########
  1052. def db_settings(self):
  1053. connection = pymysql.connect(host='localhost',
  1054. user='root',
  1055. password='8drD3F74',
  1056. db='python',
  1057. charset='utf8',
  1058. cursorclass=pymysql.cursors.DictCursor)
  1059. return connection
  1060.  
  1061.  
  1062. root = Tk()
  1063. # Makes window not resizeable.
  1064. root.resizable(0,0)
  1065. # Window size
  1066. root.geometry("290x390")
  1067. # Title on the window
  1068. root.title("Python Project")
  1069. # "activates"(words pls) the mainClass
  1070. app = DBTest(root)
  1071. # Main loop for the tkinder
  1072. 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!