def number_of_boxes_sold():
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except ConnectionError as e:
print(e)
return False
cur = conn.cursor()
query = "SELECT `items` FROM `invoices`"
cur.execute(query)
response = cur.fetchall()
num_of_boxes = 0
t = []
for invoice in response:
for items in invoice:
if 'Box' in items:
t.append(items.split(')('))
for x in t:
for i in x:
num_of_boxes += int(i.replace('(', '')[0])
return num_of_boxes
def auth_admin(self):
"""Authenticate administrator trying to login."""
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except ConnectionError as e:
print(e)
return False
cur = conn.cursor()
query = "SELECT `password` FROM `administrator` WHERE `user_name` = '{}'".format(self.user_name)
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
return False
response = cur.fetchall()
conn.close()
bcrypt = Bcrypt()
try:
return bcrypt.check_password_hash(response[0][0], self.password)
except IndexError:
return False
def get_all_employees_from_db():
emps = []
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("select * from emp_data")
#process data received from cursor
for (empno, name, salary) in db_cursor:
emps.append(
Employee({
"empno": empno,
"name": name,
"daily_salary": salary
}))
#close the cursor and connection
db_connection.close()
db_cursor.close()
except:
print("exception occurred", sys.exc_info())
return emps
def is_login(sk):
"""This method is used to verify if at least one administrator has been setup."""
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except ConnectionError as e:
print(e)
return False
cur = conn.cursor()
query = "SELECT * FROM `administrator`"
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
return False
conn.close()
response = cur.fetchall()
if response:
b = Bcrypt()
try:
return b.check_password_hash(request.cookies['admin_session'], sk)
except KeyError as e:
return False
else:
return True
def get_connection(self):
"""
Function name : get_connection()
Return DB object of specified DB reading parameters from conf_vars dictionary
Args : self (reference to the object of class MyDBConnection)
Returns : DB object
Exception : SQL_ERR (write to log)
Exception (write to log)
"""
try:
self.db = Connect(host=self.conf_vars['host'],
port=self.conf_vars['port'],
user=self.conf_vars['user'],
password=self.conf_vars['password'],
database=self.conf_vars['database'])
except SQL_ERR as e:
logger.exception('Exception: %s', str(e))
print e
except Exception as exp:
logger.exception('Exception: %s', str(exp))
print exp
return self.db
def create_database():
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD)
except:
raise ConnectionError
cur = conn.cursor()
query = 'CREATE DATABASE IF NOT EXISTS `pytry`'
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
conn.close()
def create_invoice_table():
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except:
raise ConnectionError
cur = conn.cursor()
query = ("CREATE TABLE IF NOT EXISTS `invoices`(`id` INT(11) NOT NULL AUTO_INCREMENT,"
"`invoice_id` VARCHAR(255),`costumer` VARCHAR(255) NOT NULL, `date` DATE NOT NULL,"
"seller VARCHAR(255) NOT NULL, `items` TEXT NOT NULL, `total` DECIMAL(6,2),"
"is_pending BOOLEAN NOT NULL, PRIMARY KEY(`id`));")
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
conn.close()
def create_costumer_table():
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except:
raise ConnectionError
cur = conn.cursor()
query = ("CREATE TABLE IF NOT EXISTS `costumers` (`id` INT(11) NOT NULL AUTO_INCREMENT, "
"`name` VARCHAR(255) NOT NULL, `street` VARCHAR(255) NOT NULL, `city` VARCHAR(255) NOT NULL,"
"`state` VARCHAR(255) NOT NULL, `zipcode` VARCHAR(255) NOT NULL, `phone` VARCHAR(255) NOT NULL,"
"`email` VARCHAR(255) NOT NULL, PRIMARY KEY(`id`));")
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
conn.close()
def create_item_table():
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except:
raise ConnectionError
cur = conn.cursor()
query = ("CREATE TABLE IF NOT EXISTS `items` (`id` INT(11) NOT NULL AUTO_INCREMENT, "
"`description` VARCHAR(255), `market_price` DECIMAL(6,2) NOT NULL, "
"`costumer_price` DECIMAL(6,2) NOT NULL, `distributor_price` DECIMAL(6,2) NOT NULL, "
"`quantity` INT(11) NOT NULL, PRIMARY KEY(`id`));")
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
conn.close()
def get_invoice_by_id(self):
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except ConnectionError as e:
print(e)
return False
cur = conn.cursor()
query = ("SELECT * FROM invoices WHERE `invoice_id` = {}".format(self.invoice_id))
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
return None
response = cur.fetchall()
conn.close()
return response
def check_if_admin_exist():
"""This method is used to verify if at least one administrator has been setup."""
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except ConnectionError as e:
print(e)
return False
cur = conn.cursor()
query = "SELECT * FROM `administrator`"
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
return False
conn.close()
return cur.fetchall()
def get_pending_invoices():
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except ConnectionError as e:
print(e)
return False
cur = conn.cursor()
query = "SELECT * FROM invoices WHERE `is_pending` = 1"
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
return None
response = cur.fetchall()
conn.close()
return response
def get_item_description_by_id(id):
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except ConnectionError as e:
print(e)
return False
cur = conn.cursor()
query = ("SELECT `description` FROM items WHERE id = '{}'".format(id))
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
return None
response = cur.fetchall()
conn.close()
return response[0][0]
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 _connect_db(self): # 如果是数据库名链接不上，则创建该名称的数据库，返回的是一个连接实例
try:
con = Connect(host=self.host, port=self.port, user=self.user, password=self.password, db=self.dbname)
return con
except Exception as e:
if str(e)[:4] == '1049':
con = Connect(host=self.host, port=self.port, user=self.user, password=self.password)
cur = con.cursor()
cur.execute('CREATE DATABASE %s' % self.dbname)
con = Connect(host=self.host, port=self.port, user=self.user, password=self.password, db=self.dbname)
return con
else:
print('MySQL数据库初始化错误，错误信息是：%s' % str(e))
def check_duplicate(self):
try:
conn = Connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE)
except ConnectionError as e:
print(e)
return False
cur = conn.cursor()
# Check if username exist
query = "SELECT * FROM `administrator` WHERE `user_name` = '{}'".format(self.user_name)
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
return False
response = cur.fetchall()
print(response)
# If so, return True
if response:
conn.close()
return True
# If not, check if email exists
query = "SELECT * FROM `administrator` WHERE `email` = '{}'".format(self.email)
try:
cur.execute(query)
except errors.ProgrammingError as e:
print(e)
return False
response = cur.fetchall()
print(response)
if response:
conn.close()
return True
conn.close()
# If not return False
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 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
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
import re
import mysql
import pytest
from mysql.connector import Connect
HOST = 'localhost'
USER = '******'
PSWD = 'root'
DBNAME = 'office'
MYSQL_CONNECTION = Connect(host=HOST, user=USER, passwd=PSWD, db=DBNAME)
class Employee():
TBNAME = 'employees'
CNAMES = {
'name': {
'column': 'name',
'type': 'varchar(30)',
'rules': re.compile('^[a-zA-Z]{2,30}$')
},
'dob': {
'column': 'dob',
'type': 'date',
'rules': re.compile('[0-9]{4}-[0-9]{2}-[0-9]{2}')
},
'salary': {
'column': 'salary',
'type': 'double',
'rules': re.compile('[0-9]{3,10}')
}
class MyDBConnection:
"""
Database connection class
"""
def __init__(self, conf=None, db_type=None):
"""
Function name : __init__()
If conf file path is passed , read the file and write its content to conf_vars dictionary.
Update conf_vars dictionary for specified db_type
Args : self (reference to the object of class MyDBConnection), conf (path to config file of DB),db_type (DB type name defined in config file)
Returns : None
Exception : None
"""
self.conf_vars = {}
self.db = None
if conf:
execfile(conf, self.conf_vars)
self.conf_vars = self.conf_vars[db_type]
#del self.conf_vars['__builtins__']
def get_connection(self):
"""
Function name : get_connection()
Return DB object of specified DB reading parameters from conf_vars dictionary
Args : self (reference to the object of class MyDBConnection)
Returns : DB object
Exception : SQL_ERR (write to log)
Exception (write to log)
"""
try:
self.db = Connect(host=self.conf_vars['host'],
port=self.conf_vars['port'],
user=self.conf_vars['user'],
password=self.conf_vars['password'],
database=self.conf_vars['database'])
except SQL_ERR as e:
logger.exception('Exception: %s', str(e))
print e
except Exception as exp:
logger.exception('Exception: %s', str(exp))
print exp
return self.db
def close_connection(self):
"""
Function name : close_connection()
Closes the MySQL DB connection
Args : self (reference to the object of class MyDBConnection)
Returns : None
Exception : None
"""
if isinstance(self.db, mysql.connector.connection.MySQLConnection):
self.db.close()
def thread_cmd(server):
cnx = Connect(**server.client_config.copy())
cnx.cmd_query("SELECT USER()")
rows = cnx.get_rows()
print(rows)
cnx.cmd_query("shutdown")
def close(self, conn: Connect):
"""
Closes the connection to the database
"""
conn.close()
def Main():
parser, opt = ParseCommandLine()
opt.src = opt.src[0]
opt.dst = opt.dst[0]
if opt.src == opt.dst:
print("new database name can not be the same with old.")
os.sys.exit(1)
conn = Connect(
host=opt.mysql_host,
port=opt.mysql_port,
user=opt.mysql_user,
password=opt.mysql_passwd,
charset=opt.mysql_charset,
use_unicode=True,
)
databases = []
old_tables = []
new_tables = []
cursor = conn.cursor()
cursor.execute("show databases")
rows = cursor.fetchall()
for row in rows:
databases.append( row[0] )
if not opt.src in databases:
print("database %s not exists."%(opt.src))
os.sys.exit(2)
if not opt.dst in databases:
sql = """
SELECT
SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM
information_schema.SCHEMATA
WHERE
SCHEMA_NAME = %s
"""
cursor.execute(sql, (opt.src,))
rows = cursor.fetchall()
row = rows[0]
if row[0] == opt.src:
DEFAULT_CHARACTER_SET_NAME = row[1]
DEFAULT_COLLATION_NAME = row[2]
else:
print("load database charset setting failed!")
os.sys.exit(3)
sql = "CREATE DATABASE `%s` DEFAULT CHARACTER SET '%s' DEFAULT COLLATE '%s';"%(
opt.dst, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME)
print(sql)
if opt.doit:
cursor.execute(sql)
cursor.execute("use %s"%(opt.src))
cursor.execute("show tables")
rows = cursor.fetchall()
for row in rows:
old_tables.append( "`%s`.`%s`"%(opt.src, row[0]) )
new_tables.append( "`%s`.`%s`"%(opt.dst, row[0]) )
for old_table, new_table in zip(old_tables, new_tables):
sql = "ALTER TABLE %s RENAME TO %s;"%(old_table, new_table)
print(sql)
if opt.doit:
cursor.execute(sql)
if opt.dropold:
sql = "DROP DATABASE `%s`;"%(opt.src)
print(sql)
if opt.doit:
cursor.execute(sql)
__author__ = 'Administrator'
# 'Administrator' 为预定义的变量，为你当前登录的计算机用户名
from mysql.connector import Connect
mydb = Connect(
host="localhost", # 数据库主机地址
user="******", # 数据库用户名
passwd="123456", # 数据库密码
database="runnoob_db")
mycursor = mydb.cursor()
mycursor.execute("select id, url from sites")
myresult = mycursor.fetchall(3)
for x in myresult:
print(x)
