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)