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)
