API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jul 14th, 2016
114
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 22.17 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 thingies we'll use
  8. def __init__(self, root):
  9. self._create_tabs()
  10. self._layout()
  11.  
  12. def _create_tabs(self):
  13. self.frame = Frame(root) # Main Frame
  14. self.nb = Notebook(self.frame) # The 'tab-thingy'
  15.  
  16. # Create main tabs
  17. customer = Frame(self.nb)
  18. order = Frame(self.nb)
  19. product = Frame(self.nb)
  20. # Create customer NB
  21. self.customer = Notebook(customer)
  22. # Create Customer-tabs
  23. customer.insert = Frame(self.customer)
  24. customer.select = Frame(self.customer)
  25. customer.selAll = Frame(self.customer)
  26. customer.delete = Frame(self.customer)
  27. self._customer_insert_frame(customer.insert)
  28. self._customer_select_frame(customer.select)
  29. self._customer_selAll_frame(customer.selAll)
  30. self._customer_delete_frame(customer.delete)
  31.  
  32. # Create order NB
  33. self.order = Notebook(order)
  34. # Create order-tabs
  35. order.insert = Frame(self.order)
  36. order.select = Frame(self.order)
  37. order.selAll = Frame(self.order)
  38. self._order_insert_frame(order.insert)
  39. #self._order_select_frame(order.select)
  40. #self._order_selAll_frame(order.selAll)
  41.  
  42. # Adds the tabs to the notebook
  43. self.nb.add(customer, text="Customer")
  44. self.nb.add(order, text="Order")
  45. self.nb.add(product, text="Product")
  46. self.customer.add(customer.insert, text="Insert")
  47. self.customer.add(customer.select, text="Select")
  48. self.customer.add(customer.selAll, text="Select ALL")
  49. self.customer.add(customer.delete, text="Delete Cust.")
  50. self.order.add(order.insert, text="Insert")
  51. self.order.add(order.select, text="Select")
  52. self.order.add(order.selAll, text="Select ALL")
  53.  
  54. def _layout(self):
  55. # Layout
  56. self.frame.grid(row=0, column=0, sticky=(N,S,W,E))
  57. self.nb.grid(row=0, column=0, sticky=(N,S,W,E))
  58. self.customer.grid(row=0, column=0, columnspan=2, sticky=(N,S,W,E), pady=5)
  59. self.order.grid(row=0, column=0, columnspan=2, sticky=(N,S,W,E), pady=5)
  60. # Resize rules
  61. root.columnconfigure(0, weight=1)
  62. root.rowconfigure(0, weight=1)
  63. self.frame.columnconfigure(0, weight=2)
  64. self.frame.rowconfigure(0, weight=2)
  65.  
  66. def _customer_insert_frame(self, insert):
  67. # Creates the Entry boxes to get data
  68. insert.name = Entry(insert)
  69. insert.address = Entry(insert)
  70. insert.phone = Entry(insert)
  71. # Makes a label and puts things out on the screen.
  72. Label(insert, text="Name: ").grid(row=0, column=0, sticky=E)
  73. insert.name.grid(row=0, column=1, sticky=W)
  74. Label(insert, text="Address: ").grid(row=1, column=0, sticky=E)
  75. insert.address.grid(row=1, column=1, sticky=W)
  76. Label(insert, text="Phone: ").grid(row=2, column=0, sticky=E)
  77. insert.phone.grid(row=2, column=1, sticky=W)
  78. # Using 'lambda: ' to prevent prepare_insert from running before we want it to.
  79. insert.submit_button = Button(insert, text="Submit", command=lambda: self.customer_prepare_insert(insert))
  80. insert.submit_button.grid(row=4, column=0, columnspan=2)
  81. insert.text = Text(insert, width=30, height=4, wrap=WORD)
  82. insert.text.grid(row=5,column=0,columnspan=2)
  83.  
  84. def customer_prepare_insert(self, insert):
  85. """ Reg ex for the phone number.
  86. Explanation:
  87. [+]? = First char can be a +, not needed tho
  88. [\d -]* = allow unlimited digits (0-9), spaces and -
  89. {3,} = Specifies at least 3 numbers."""
  90. pattern = re.compile(r"^[+]?[\d -]{3,}")
  91. # Gets values from the Entry boxes above.
  92. name = insert.name.get()
  93. address = insert.address.get()
  94. phone = insert.phone.get()
  95.  
  96. # Checks if every field has something in them. (Does not check if only a space)
  97. if not all((name, address, phone)):
  98. errorText = "Need to enter something in all boxes."
  99. error = True
  100. # Checks if the phone-pattern matches our regEx
  101. elif not pattern.match(phone):
  102. error = True
  103. errorText = "Phone number can only contain 0-9, spaces and dashes (-).\nFirst symbol can be + for country codes."
  104. else:
  105. error = False
  106. # If we have an error, throw the error text
  107. if error:
  108. text = errorText
  109. # Else does the insert into DB.
  110. else:
  111. text = self.add_to_DB('CUSTOMER',name, address, phone)
  112.  
  113. insert.text.config(state=NORMAL)
  114. insert.text.delete(0.0, END)
  115. insert.text.insert(0.0, text)
  116. insert.text.config(state=DISABLED)
  117.  
  118. def _customer_select_frame(self, select):
  119. # Create the Entry boxes
  120. select.sID = Entry(select)
  121. select.name = Entry(select)
  122. select.address = Entry(select)
  123. select.phone = Entry(select)
  124.  
  125. # Puts them out on the frame
  126. Label(select, text="Search for Databasedata\nenter at least 1 searchfield").grid(row=0, column=0, columnspan=2)
  127. Label(select, text="Search ID:").grid(row=1, column=0, sticky=E)
  128. select.sID.grid(row=1, column=1, sticky=W)
  129. Label(select, text="Search Name:").grid(row=2, column=0, sticky=E)
  130. select.name.grid(row=2, column=1, sticky=W)
  131. Label(select, text="Search address").grid(row=3, column=0, sticky=E)
  132. select.address.grid(row=3, column=1, sticky=W)
  133. Label(select, text="Search phone").grid(row=4, column=0, sticky=E)
  134. select.phone.grid(row=4, column=1, sticky=W)
  135. # Using lambda again on the button to not make it do the
  136. # prepare before we actually click the button
  137. select.submit_button = Button(select, text="Submit", command=lambda: self.customer_prepare_select(select))
  138. select.submit_button.grid(row=5, column=0, columnspan=2)
  139. select.text = Text(select, width=30, height=10, wrap=WORD)
  140. select.text.grid(row=6,column=0,columnspan=2)
  141.  
  142. def customer_prepare_select(self, select):
  143. # Get value from the search fields.
  144. sID = select.sID.get()
  145. name = select.name.get()
  146. address = select.address.get()
  147. phone = select.phone.get()
  148. args = {}
  149. stmt = ""
  150. # Checks at least one have data. (Does not check if only a space)
  151. if sID != "" or name != "" or address != "" or phone != "":
  152. # If at least one have data we check which
  153. # one and adds the string for the query
  154. if sID != "":
  155. args['ID'] = "id='"+sID+"' AND "
  156. if name != "":
  157. args['name'] = "name='"+name+"' AND "
  158. if address != "":
  159. args['address'] = "address='"+address+"' AND "
  160. if phone != "":
  161. args['phone'] = "phone='"+phone+"'"
  162. for key, value in args.items():
  163. stmt += value
  164. # Removes the last ' AND '
  165. stmt = stmt[:-5]
  166. sql = "SELECT * FROM CUSTOMER WHERE {val}".format(val=stmt)
  167. db_result = self.get_from_DB(sql)
  168. textBox = ""
  169. # If we get rows back
  170. if db_result:
  171. # Iterates for all rows gotten
  172. for key in db_result:
  173. textBox += "ID: {id} Name: {name}\nAddress: {address}\nPhone: {phone}\n\n".format(id=key['id'], name=key['name'], address=key['address'], phone=key['phone'])
  174. # If we searched for an entry that didnt exist
  175. else:
  176. textBox = "Could not find what you searched for."
  177. # If we enter no value in any box.
  178. else:
  179. textBox = "Must insert at least one value."
  180.  
  181. # Updates the textfield
  182. select.text.config(state=NORMAL)
  183. select.text.delete(0.0, END)
  184. select.text.insert(0.0, textBox)
  185. select.text.config(state=DISABLED)
  186.  
  187. def _customer_selAll_frame(self, selAll):
  188. #Create the button to show everything
  189. Label(selAll, text="Fetch all entries?").grid(row=0, column=0, sticky=E)
  190. selAll.submit_button = Button(selAll, text="Fetch!", command=lambda: self.customer_select_all_DB(selAll))
  191. selAll.submit_button.grid(row=0, column=1, sticky=W)
  192. selAll.text = Text(selAll, width=30, height=15, wrap=WORD)
  193. selAll.text.grid(row=1, column=0, columnspan=2)
  194.  
  195. def customer_select_all_DB(self, selAll):
  196. sql = "SELECT * from CUSTOMER"
  197. # Gets all entries from the DB
  198. db_result = self.get_from_DB(sql)
  199. textBox = ""
  200. # If we get result returned
  201. if db_result:
  202. # Iterates for every Row
  203. for key in db_result:
  204. textBox += "ID: {id} Name: {name}\nAddress: {address}\nPhone: {phone}\n\n".format(id=key['id'], name=key['name'], address=key['address'], phone=key['phone'])
  205. # If no entries was found in the DB
  206. else:
  207. textBox = "There's nothing in the Database or some error n shit."
  208. # Updates the textfield
  209. selAll.text.config(state=NORMAL)
  210. selAll.text.delete(0.0, END)
  211. selAll.text.insert(0.0, textBox)
  212. selAll.text.config(state=DISABLED)
  213.  
  214. def _customer_delete_frame(self, delete):
  215. Label(delete, text="Delete a user with the dropdown menu").grid(row=0, column=0, columnspan=2, sticky=E)
  216. # Creates the menu
  217. # The variable we need to "get"-value from
  218. delete.var = StringVar()
  219. delete.var.set("None")
  220. # Gets the list with usernames:
  221. delete.users = self.get_customer_list()
  222. delete.menu = OptionMenu(delete, delete.var, *delete.users[0])
  223. delete.menu.grid(row=1, column=0)
  224. delete.submit_button = Button(delete, text="DELETE!", command=lambda: self.customer_delete_from_DB(delete))
  225. delete.submit_button.grid(row=2)
  226. delete.update_button = Button(delete, text="\"Update\" List", command=lambda: self.rebuild_window())
  227. delete.update_button.grid(row=4)
  228. delete.text = Text(delete, width=30, height=15, wrap=WORD)
  229. delete.text.grid(row=3, column=0, columnspan=2)
  230.  
  231. def customer_delete_from_DB(self, delete):
  232. # Gets the name we want to delete
  233. # and sets from which tab(table) and column
  234. custName = delete.var.get()
  235. custID = delete.users[1].get(custName)
  236. table = "customer"
  237. x = self.delete_from_DB(table, custID, custName)
  238. delete.text.config(state=NORMAL)
  239. delete.text.delete(0.0, END)
  240. delete.text.insert(0.0, x)
  241. delete.text.config(state=DISABLED)
  242.  
  243. def _order_insert_frame(self, insert):
  244. # Makes the Customer and Product Frames
  245. self.order_customer = LabelFrame(insert, text="Buyer: ", labelanchor=N)
  246. self.order_customer.grid(row=0, column=0, sticky=(N,S,W,E))
  247. self.order_product = LabelFrame(insert, text="Product: ",labelanchor=N)
  248. self.order_product.grid(row=1, column=0, sticky=(N,S,W,E))
  249. self.order_amount = LabelFrame(insert, text="Amount: ", labelanchor=N)
  250. self.order_amount.grid(row=2, column=0, sticky=(N,S,W,E))
  251. self.order_confirm = LabelFrame(insert, text="Confirm: ",labelanchor=N)
  252. self.order_confirm.grid(row=3, column=0, sticky=(N,S,W,E))
  253. # Some default Vars
  254. insert.productVar = StringVar()
  255. insert.productVar.set("None")
  256. insert.totalUnits = IntVar()
  257. insert.customerVar = StringVar()
  258. insert.customerVar.set("None")
  259. # Gets the list of customers
  260. insert.customers = self.get_customer_list()
  261. insert.customerMenu = OptionMenu(self.order_customer, insert.customerVar, *insert.customers[0])
  262. insert.customerMenu.grid(row=0, column=1, sticky=EW)
  263. # Gets the list of products and current stocks
  264. insert.products = self.get_product_list()
  265. insert.unitLabel = Label(self.order_product, text="In stock: ~")
  266. insert.unitLabel.grid(row=0, column=1, sticky=W)
  267. insert.productMenu = OptionMenu(self.order_product, insert.productVar, *insert.products[0], command=lambda _: self.order_update_price(insert))
  268. insert.productMenu.config(width=15)
  269. insert.productMenu.grid(row=0, column=0, sticky="EW")
  270. # Enter how many you want to buy
  271. insert.amount = Entry(self.order_amount)
  272. insert.amount.grid()
  273. insert.insert_button = Button(self.order_confirm, text="Submit", command=lambda: self.order_prepare_insert(insert))
  274. insert.insert_button.grid(row=0, column=0)
  275. # Shows result.
  276. insert.text = Text(self.order_confirm, width=30, height=10, wrap=WORD)
  277. insert.text.grid(row=1, column=0,columnspan=2, sticky=(N,S,W,E))
  278. insert.text.config(state=DISABLED)
  279.  
  280. def order_prepare_insert(self, x):
  281. # Only allow numbers
  282. pattern = re.compile(r"^[^0][\d]{1,}")
  283. # Sets up the things we need to use
  284. error = False
  285. errorT = ""
  286. totUnit = x.totalUnits.get()
  287. amount = x.amount.get()
  288. cust = x.customerVar.get()
  289. custID = x.customers[1].get(cust, "CustError")
  290. prod = x.productVar.get()
  291. prodID = x.products[1].get(prod, "prodError")
  292.  
  293. # Try and see if amount is a number
  294. try:
  295. amount = int(amount)
  296. except Exception:
  297. error = True
  298. # Checks so everything is entered:
  299. if cust == "None" or prod == "None":
  300. error = True
  301. errorT = "Must choose a customer and/or product.\n"
  302. # If amount doesn't match pattern
  303. if not pattern.match(str(amount)):
  304. error = True
  305. errorT += "Amount must contain numbers (0-9), can't be 0\n"
  306.  
  307. # Checks if we don't have an error
  308. if not error:
  309. if int(totUnit) - amount < 0:
  310. answer = "Can't order more units than what exist in stock."
  311. else:
  312. stock = int(totUnit) - int(amount)
  313. 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)
  314. else:
  315. answer = errorT
  316.  
  317. x.text.config(state=NORMAL)
  318. x.text.delete(0.0, END)
  319. x.text.insert(0.0, answer)
  320. x.text.config(state=DISABLED)
  321.  
  322. def order_update_price(self, insert):
  323. productName = insert.productVar
  324. unitsList = insert.products[2]
  325. errorT = False
  326. if productName.get() != "None":
  327. units = unitsList[productName.get()]
  328. else:
  329. units = 0
  330. try:
  331. insert.totalUnits.set(int(units))
  332. except Exception as e:
  333. errorT = True
  334. error = self.errorCodeHandler(e)
  335. if errorT:
  336. text = error
  337. else:
  338. text = "In stock: "+str(units)
  339. insert.unitLabel.config(text=text)
  340.  
  341. def delete_from_DB(self, table, value, *args):
  342. # Making the query
  343. # Takes the tab (order, customer, products), plus the ID of the thing to delete
  344. # This is to re-use this delete code for other tabs.
  345. sql = "DELETE FROM {tab} WHERE id='{value}'".format(tab=table.upper(), value=value)
  346. # Connection config
  347. connection = self.db_settings()
  348. # Try to do the thing
  349. try:
  350. with connection.cursor() as cursor:
  351. # Executes the delete-query
  352. cursor.execute(sql)
  353. #Commit the changes
  354. connection.commit()
  355. # Checks how many rows that was affected
  356. result = cursor.rowcount
  357. # If 1 (row deleted) then show message.
  358. if result == 1:
  359. if args[0]:
  360. result = "{name} from {tab}-tab has been deleted from the Database.\n".format(name=args[0], tab=table)
  361. else:
  362. result = "The order with the ID: {id} was succesfully deleted.".format(id=value)
  363. # If 0, no rows affected, thus nothing deleted.
  364. elif result == 0:
  365. result = "Nothing got deleted."
  366. else:
  367. result = "Something weird happened. More than 1 row changed."
  368.  
  369. except Exception as e:
  370. result = self.errorCodeHandler(e)
  371. finally:
  372. connection.close()
  373.  
  374. # Return value
  375. return result
  376.  
  377. def add_to_DB(self, *args):
  378. """
  379. Accepts multiple args depending on what for.
  380. If args[0] is equal to, it wants these values:
  381. CUSTOMER: name, address, phone
  382. ORDER: customer_id, product_id, amount
  383. PRODUCT: name, unit (in stock), price (per unit)
  384. """
  385. # Connection config
  386. connection = self.db_settings()
  387. #Inserting the data
  388. try:
  389. # Adds it to DB
  390. with connection.cursor() as cursor:
  391. if args[0] == 'CUSTOMER':
  392. # Makes query for CUSTOMER
  393. # Args needed are args[1-3], name, address, phone.
  394. sql = "INSERT INTO CUSTOMER (name, address, phone) VALUES (%s, %s, %s)"
  395. cursor.execute(sql, (args[1], args[2], args[3]))
  396. result = "Data added\nName: {name}\nAddress: {address}\nPhone: {phone}".format(name=args[1],address=args[2],phone=args[3])
  397. elif args[0] == 'ORDERS':
  398. # Makes query for ORDERS
  399. # Args needed are args[1-3], customer_id, product_id, amount
  400. cursor.execute("INSERT INTO ORDERS (customer_id, product_id, amount) VALUES (%d, %d, %d)", [args[1], args[2], args[3]])
  401. result = "Data added\nCustomer ID: {custID}\nProduct ID: {prodID}\nAmount: {amount} ex.".format(custID=args[1], prodID=args[2], amount=args[3])
  402. elif args[0] == 'PRODUCT':
  403. # Makes query for PRODUCT
  404. # Args needed are args[1-3], name, unit, price
  405. cursor.execute("INSERT INTO PRODUCT (name, unit, price) VALUES (%s, %d, %d", [args[1], args[2], args[3]])
  406. result = "Data added\nProduct: {name}\nUnits: {unit} ex.\nPrice: {price}€ each".format(name=args[1], unit=args[2], price=args[3])
  407. # Comit the query
  408. connection.commit()
  409.  
  410. except Exception as e:
  411. result = self.errorCodeHandler(e)
  412. finally:
  413. connection.close()
  414. # Returns whatever result we got
  415. return result
  416.  
  417. def get_from_DB(self, sql):
  418. # Connection config
  419. connection = self.db_settings()
  420. # Try to get data
  421. try:
  422. with connection.cursor() as cursor:
  423. # The query from prepare_select()
  424. cursor.execute(sql)
  425. result = cursor.fetchall()
  426. except Exception as e:
  427. result = self.errorCodeHandler(e)
  428. finally:
  429. connection.close()
  430. return result
  431.  
  432. """Returns:
  433. # [0] = list with only names
  434. # [1] = dictionary name : id
  435. """
  436. def get_customer_list(self, *args):
  437. sql = "SELECT name, id FROM CUSTOMER"
  438. # Gets all customers and put them in a list
  439. users = self.get_from_DB(sql)
  440. user = []
  441. userID = {}
  442. # Iterates the Dict we got, and turns them into list with just names.
  443. if users:
  444. for key in users:
  445. user.append(key['name'])
  446. userID[key['name']] = key['id']
  447. # Alphabetical sorting, cuz why not
  448. user.sort()
  449. # Adds "None" to start of list, cuz else OptionMenu gets wonky
  450. user.insert(0, "None")
  451. return user, userID
  452.  
  453. """Returns
  454. # [0] = list with only names
  455. # [1] = dictionary, name : id
  456. # [2] = dictionary, name : units
  457. """
  458. def get_product_list(self, *args):
  459. sql = "SELECT name, id, unit FROM PRODUCT"
  460. # Gets all products and their ID and puts them in a list
  461. db_products = self.get_from_DB(sql)
  462. db_product = []
  463. db_productID = {}
  464. db_productAmount = {}
  465. # Iterates through the dictionary we get back from DB
  466. if db_products:
  467. for key in db_products:
  468. db_product.append(key['name'])
  469. db_productID[key['name']] = key['id']
  470. db_productAmount[key['name']] = key['unit']
  471. # Alphabetical sorting, cuz why not
  472. db_product.sort()
  473. # Adds "None" to start of list, avoiding wonkyness
  474. db_product.insert(0, "None")
  475. return db_product, db_productID, db_productAmount
  476.  
  477. # Destroys all windows, and rebuilds.
  478. # Got tired of trying other ways.
  479. def rebuild_window(self):
  480. self.frame.destroy()
  481. self._create_tabs()
  482. self._layout()
  483.  
  484. def errorCodeHandler(self, error):
  485. # Simple "One place for all error codes"-thingy.
  486. return {
  487. 1062 : "Dublicate entry was found!",
  488. 1451 : "Entry is tied to a foreign key (orders)\nCan't be deleted until the order tied to this entry is deleted."
  489. }.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))
  490.  
  491. ##########
  492. #The settings for the database, change this.
  493. ##########
  494. def db_settings(self):
  495. connection = pymysql.connect(host='',
  496. user='',
  497. password='',
  498. db='',
  499. charset='utf8',
  500. cursorclass=pymysql.cursors.DictCursor)
  501. return connection
  502.  
  503.  
  504. root = Tk()
  505. # Makes window not resizeable.
  506. root.resizable(0,0)
  507. # Window size
  508. root.geometry("260x390")
  509. # Title on the window
  510. root.title("GUI Test :>")
  511. # "activates"(words pls) the mainClass
  512. app = DBTest(root)
  513. # Main loop for the tkinder
  514. 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!