Exemplo n.º 1
0
    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
Exemplo n.º 2
0
    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
Exemplo n.º 4
0
    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
Exemplo n.º 5
0
    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
Exemplo n.º 6
0
    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()
Exemplo n.º 7
0
    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()
Exemplo n.º 8
0
    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()
Exemplo n.º 9
0
    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()
Exemplo n.º 10
0
    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
Exemplo n.º 11
0
    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()
Exemplo n.º 12
0
    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
Exemplo n.º 13
0
    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]
Exemplo n.º 14
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
Exemplo n.º 15
0
    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
Exemplo n.º 16
0
 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))
Exemplo n.º 17
0
    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
Exemplo n.º 20
0
    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
Exemplo n.º 21
0
    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
Exemplo n.º 22
0
    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
Exemplo n.º 23
0
    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
Exemplo n.º 24
0
    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
Exemplo n.º 25
0
    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
Exemplo n.º 26
0
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}')
        }
Exemplo n.º 27
0
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()
Exemplo n.º 28
0
 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")
Exemplo n.º 29
0
 def close(self, conn: Connect):
     """
         Closes the connection to the database
     """
     conn.close()
Exemplo n.º 30
0
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)
Exemplo n.º 31
0
__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)