def create_employee(self): try: conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE) except ConnectionError as e: print(e) return False cur = conn.cursor() query = ("INSERT INTO `employees`(name, street, city, state, zipcode, phone, email, join_date)" "VALUES ('{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}')" "".format(self.name, self.address['street'], self.address['city'], self.address['state'], self.address['zipcode'], self.phone, self.email, self.join_date)) if self.name == "" or self.phone == "" or self.email == "" or self.address == "": raise Exception try: cur.execute(query) except errors.ProgrammingError as e: print(e) return False conn.commit() conn.close() return True
def create_admin(self): if not self.check_duplicate(): try: conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE) except ConnectionError as e: print(e) return False cur = conn.cursor() bcrypt = Bcrypt() query = ("INSERT INTO " "`administrator`(name, user_name, password, street, city, state, zipcode, phone, email, join_date)" "VALUES ('{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}')" "".format(self.name, self.user_name, bcrypt.generate_password_hash(self.password), self.address['street'], self.address['city'], self.address['state'], self.address['zipcode'], self.phone, self.email, self.join_date)) try: cur.execute(query) except errors.ProgrammingError as e: print(e) return False conn.commit() conn.close() return True return False
def delete_employee_from_db(empno): try: #connect to db db_connection = Connect(user='******', password='******', database='training', port=3306) #get the db cursor db_cursor = db_connection.cursor() #identify the query and execute using cursor db_cursor.execute("delete from emp_data where empno = %s", (empno, )) db_connection.commit() db_connection.close() db_cursor.close() except: print("Excepion Occurred", sys.exc_info()) return
def add_employee_to_db(new_emp): try: #connect to db db_connection = Connect(user='******', password='******', database='training', port=3306) #get the db cursor db_cursor = db_connection.cursor() #identify the query and execute using cursor db_cursor.execute( "insert into emp_data values(%s,%s,%s)", (new_emp.empno, new_emp.name, new_emp.salary_per_day)) db_connection.commit() db_connection.close() db_cursor.close() except: new_emp = None print("Exception Occured", sys.exc_info()) return new_emp
def delete_invoice(self): try: conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE) except ConnectionError as e: print(e) return False cur = conn.cursor() query = "DELETE FROM `invoices` WHERE `invoice_id` = '{}'".format(self.invoice_id) try: cur.execute(query) except errors.ProgrammingError as e: print(e) return False conn.commit() conn.close() return True
def erase_employee(id): try: conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE) except ConnectionError as e: print(e) return False cur = conn.cursor() query = ("DELETE FROM employees WHERE '{}' = id".format(id)) try: cur.execute(query) except errors.ProgrammingError as e: print(e) return False conn.commit() conn.close() return True
def change_pending_status(invoce_num): try: conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE) except ConnectionError as e: print(e) return False cur = conn.cursor() query = ("SELECT `is_pending` FROM `invoices` " "WHERE `invoice_id` = '{}'".format(invoce_num)) try: cur.execute(query) except errors.ProgrammingError as e: print(e) return None response = cur.fetchall() is_pending = response[0][0] new_status = None if is_pending == 1: new_status = 0 elif is_pending == 0: new_status = 1 query = ("UPDATE `invoices` SET `is_pending` = {} " "WHERE invoice_id = '{}';".format(new_status, invoce_num)) try: cur.execute(query) except errors.ProgrammingError as e: print(e) return None conn.commit() conn.close() return response
def create_invoice(self): try: conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE) except ConnectionError as e: print(e) return False cur = conn.cursor() query = ("INSERT INTO `invoices`(invoice_id, costumer, date, seller, items, total, is_pending)" "VALUES ('{}', '{}', '{}', '{}', '{}', '{}', '{}')" "".format(self.invoice_id, self.info, self.date, self.seller, self.items, self.total, self.pending)) try: cur.execute(query) except errors.ProgrammingError as e: print(e) return False conn.commit() conn.close() return True
def add_item(self): try: conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE) except ConnectionError as e: print(e) return False cur = conn.cursor() query = ("INSERT INTO `items`(description, market_price, costumer_price, distributor_price, quantity)" "VALUES('{}', '{}', '{}', '{}', '{}')" "".format(self.description, self.market_price, self.costumer_price, self.distributor_price, self.quantity)) try: cur.execute(query) except errors.ProgrammingError as e: print(e) return False conn.commit() conn.close() return True
def create_business(self): try: conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE) except ConnectionError as e: print(e) return False cur = conn.cursor() query = ("INSERT INTO `business`(name, street, city, state, zipcode, phone, email, manager)" "VALUES ('{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}')" "".format(self.name, self.address['street'], self.address['city'], self.address['state'], self.address['zipcode'], self.phone, self.email, self.manager)) try: cur.execute(query) except errors.ProgrammingError as e: print(e) return False conn.commit() conn.close() return True
__author__ = 'Administrator' # 'Administrator' 为预定义的变量,为你当前登录的计算机用户名 from mysql.connector import Connect mydb = Connect( host="localhost", # 数据库主机地址 user="******", # 数据库用户名 passwd="123456", # 数据库密码 database="runnoob_db") mycursor = mydb.cursor() sql = "INSERT INTO sites (name, url) VALUES (%s, %s)" val = [('Google', 'https://www.google.com'), ('Github', 'https://www.github.com'), ('stackoverflow', 'https://www.stackoverflow.com/')] mycursor.executemany(sql, val) mydb.commit() print(mycursor.lastrowid)