def remove_ballast_from_virtual_group(): ballast_id = request.args.get('ballast_id') virtual_group_id = request.args.get('virtual_group_id') db = Database() sql_string = 'DELETE FROM virtual_group_items WHERE ballast_id=' + ballast_id + ' AND virtual_group_id=' + virtual_group_id db.execute(sql_string) return 'http200'
def __init__(self, args): self.args = args self.database_bought = Database(config.BOUGHT_FILE, config.BOUGHT_FIELDS) self.database_sold = Database(config.SOLD_FILE, config.SOLD_FIELDS) self.database_products = Database(config.PRODUCTS_FILE, config.PRODUCTS_FIELDS) # parse --now, --today and --yesterday today = Today().get_date() today = datetime.strptime(today, config.DATE_FORMAT) if self.args['yesterday'] == True: today = Today().get_date() today = datetime.strptime(today, config.DATE_FORMAT) today = today + timedelta(days=-1) self.today = today # --export-format self.export = self.args['export_format']
def add_ballast_to_virtual_group(): ballast_id = request.args.get('ballast_id') virtual_group_id = request.args.get('virtual_group_id') db = Database() sql_string = 'INSERT INTO virtual_group_items (ballast_id, virtual_group_id) VALUES (' + ballast_id + ', ' + virtual_group_id + ')' db.execute(sql_string) return 'http200'
def update_ballast(): ballast_name = request.form.get('ballast_name') ballast_id = request.form.get('ID') ballast_id = ballast_id[3:] db = Database() db.update_ballast_name(ballast_id, ballast_name) return ballast_name
def rename_virtual_group(): virtual_group_ID = request.args.get('virtual_group_id') group_name = request.args.get('new_name') db = Database() sql_string = 'UPDATE virtual_group SET name="' + group_name + '" WHERE ID = ' + virtual_group_ID db.execute(sql_string) return 'http200'
def do_set_arc_level(self, arc_level, visualID): db = Database() visual_item = db.get_visual_item(visualID) var = 100 if visual_item.get('visual_type') == 0: var = 1 elif visual_item.get('visual_type') == 1: var = 1 elif visual_item.get('visual_type') == 2: group = db.get_group(visual_item.get('visual_ID_of_type')) channel = self.get_chan(group.get('dali_group_channel')) channel.set_ballast_or_group_address( group.get('dali_group_number')) channel.set_address_mode(AddressModes.group) channel.set_direct_arc_enabled(1) channel.set_arc_level(arc_level) elif visual_item.get('visual_type') == 3: ballast = db.get_ballast(visual_item.get('visual_ID_of_type')) channel = self.get_chan(ballast.get('ballast_channel')) channel.set_ballast_or_group_address( ballast.get('ballast_short_address')) channel.set_address_mode(AddressModes.ballast) channel.set_direct_arc_enabled(1) channel.set_arc_level(arc_level) return 'http200'
class Buy(): """Handles the SuperPy buy action Uses the bought and products database to record the buy action New products are added to the product database, complete with a new EAN13 barcode Bought products are then stored in the bought database """ def __init__(self, args): self.args = args check_required_arguments( args, ('product_name', 'price', 'expiration_date')) self.database_bought = Database( config.BOUGHT_FILE, config.BOUGHT_FIELDS) self.database_products = Database( config.PRODUCTS_FILE, config.PRODUCTS_FIELDS) self.product_name = args['product_name'] def run(self): # check id product exists in product database product = filter_list( self.database_products.data, 'product_name', [self.product_name]) barcode = '' if len(product) == 0: # generate a new barcode barcode = Barcode(config.STORE_BARCODE_PREFIX) # add product to products database self.database_products.add({ 'product_name': self.product_name, 'full_name': self.product_name.title(), 'ean13': barcode, }) else: # use preexisting barcode barcode = product[0]['ean13'] # add buy to bought database self.database_bought.add( { 'id': self.database_bought.rowcount + 1, 'product_name': self.product_name, 'buy_date': Today().get_date(), 'buy_price': self.args['price'], 'expiration_date': self.args['expiration_date'], 'ean13': barcode }) return 'OK'
def download_groups_from_ballast(): ballast_id = request.args.get('ballast_id') dcs = DaliChannels() db = Database() ballast = db.get_ballast(ballast_id) dc = dcs.get_chan(ballast.get('ballast_channel')) answer = dc.download_groups_from_ballast(ballast_id) return answer
def get_scene_values_for_group(): group_id = int(request.args.get('group_id')) scene_number = int(request.args.get('scene_number')) channel = dcs.get_chan_via_group_id(group_id) channel.dali_goto_scene(scene_number) db = Database() data = db.get_scene_values_for_group(group_id, scene_number) return json.dumps(data)
def get_ballasts_in_group(): virtual_group_id = request.args.get('virtual_group_id') channel_number = request.args.get('channel_number') db = Database() sql_string = 'SELECT ballasts.ID as BallastID, ballast_name, virtual_group_items.ballast_id FROM ballasts LEFT JOIN virtual_group_items ON ballasts.ID = virtual_group_items.ballast_id ' \ 'WHERE ballast_channel = ' \ + channel_number + ' AND virtual_group_ID=' + virtual_group_id + ' ORDER BY ballast_short_address ASC' data = db.get_sql_data(sql_string) return json.dumps(data)
def __init__(self, args={}): self.args = args self.today = Database(config.TODAY_FILE, config.TODAY_FIELDS) # set today if database is empty if len(self.today.data) == 0: self.args['advance_time'] = 0 self.args['init'] = True self.run()
def update_scene_value(): ballast_id = int(request.args.get('ballast_id')) scene_number = int(request.args.get('scene_number')) scene_value = int(request.args.get('scene_value')) db = Database() data = db.update_scene_value(ballast_id, scene_number, scene_value) channel = dcs.get_chan_via_ballast_id(ballast_id) channel.dali_set_scene_value(scene_number, scene_value) channel.dali_goto_scene(scene_number) return data
def insert_visual(): visual_name = request.args.get('visual_name') visual_page_id = int(request.args.get('visual_page_ID')) visual_type = int(request.args.get('visual_type')) visual_columns = int(request.args.get('visual_columns')) visual_id_of_type = int(request.args.get('visual_ID_of_type')) db = Database() db.insert_visual(visual_name, visual_page_id, visual_type, visual_columns, visual_id_of_type) return 'http200'
def do_control_ballast(self, action, ballastID): db = Database() ballast = db.get_ballast(ballastID) channel = self.get_chan(ballast.get('ballast_channel')) channel.set_ballast_or_group_address( ballast.get('ballast_short_address')) channel.set_address_mode(AddressModes.ballast) channel.set_direct_arc_enabled(0) channel.do_command(action) return 'http200'
def __init__(self, args): self.args = args check_required_arguments(args, ('product_name', 'price')) self.database_bought = Database(config.BOUGHT_FILE, config.BOUGHT_FIELDS) self.database_sold = Database(config.SOLD_FILE, config.SOLD_FIELDS)
def do_set_arc_level_ballast(self, arc_level, ballast_id): db = Database() ballast = db.get_ballast(ballast_id) channel = self.get_chan(ballast.get('ballast_channel')) channel.set_ballast_or_group_address( ballast.get('ballast_short_address')) channel.set_address_mode(AddressModes.ballast) channel.set_direct_arc_enabled(1) channel.set_arc_level(arc_level) return 'http200'
class Sell(): """Handles the SuperPy sell action Uses the bought and products database to record the sell action Sold products that are in inventory are stored in the sold database The product with the earliest expiration date is sold first """ def __init__(self, args): self.args = args check_required_arguments(args, ('product_name', 'price')) self.database_bought = Database(config.BOUGHT_FILE, config.BOUGHT_FIELDS) self.database_sold = Database(config.SOLD_FILE, config.SOLD_FIELDS) def run(self): bought_id = self.get_bought_id() if bought_id == None: return 'ERROR: Product not in stock' self.database_sold.add({ 'id': self.database_sold.rowcount + 1, 'bought_id': bought_id, 'sell_date': Today().get_date(), 'sell_price': self.args['price'], }) return 'OK' def get_bought_id(self): inventory = filter_list(self.database_bought.data, 'product_name', [self.args['product_name']]) if len(inventory) == 0: return None # sell the product with the earliest expiration date inventory = sort_list(inventory, 'expiration_date') if len(inventory) == 1: return inventory[0]['id'] for item in inventory: is_sold = filter_list(self.database_sold.data, 'bought_id', [item['id']]) if len(is_sold) == 0: return item['id'] return None
def get_aluno(matricula): try: if matricula is None: abort(404) with Database.getConn().cursor() as cursor: sql = "SELECT nome, email, endereco, cpf, matricula, telefone FROM aluno WHERE matricula = %s" cursor.execute(sql, (matricula)) result = cursor.fetchone() from classes.Aluno import Aluno return jsonify(Aluno(result).__dict__) finally: Database.closeConn()
def download_scene_from_ballast(self, ballast_short_address, ballast_id, scene_number): self.__address_mode = AddressModes.ballast self.__ballast_or_group_address = ballast_short_address scene_value = self.dali_query_scene_level(scene_number) db = Database() db.update_scene_value(ballast_id, scene_number, scene_value) return 'Ballast ' + str(self.__ballast_or_group_address) \ + ' - scene ' \ + str(scene_number) \ + ' level: '\ + str(scene_value)
def index(): try: with Database.getConn().cursor() as cursor: sql = "SELECT nome, email, endereco, matricula FROM aluno" cursor.execute(sql) result = cursor.fetchall() return_data = [] for aluno in result: from classes.Aluno import Aluno return_data.append(Aluno(aluno).__dict__) return jsonify(return_data) finally: Database.closeConn()
def do_value(): db = Database() ballast_id = request.form.get('ID') nv = db.get_ballast_id_and_channel(ballast_id) ballast_short_address = nv['ballast_short_address'] channel_number = nv['ballast_channel'] channel = dcs.get_chan(channel_number) channel.set_direct_arc_enabled(1) channel.set_address_mode(AddressModes.ballast) channel.set_ballast_or_group_address(ballast_short_address) value = int(request.form.get('value')) channel.set_arc_level(value) return 'ok'
def delete_aluno(): try: if request.form['matricula'] is None: abort(404) return_data = {"success": False} with Database.getConn().cursor() as cursor: sql = "DELETE FROM aluno WHERE matricula = %s" cursor.execute(sql, (request.form['matricula'])) Database.getConn().commit() return_data['success'] = True return jsonify(return_data) finally: Database.closeConn()
def save(self): """ Method save or update data do bdd """ if not self.__is_error: fields = list(self.fields) table = str(self.table) if hasattr(self, 'PK_id') and int(self.PK_id) > 0: # Update data DB.update(table, fields, self,) else: # Save data # if isset primary key id unset self.PK_id = None DB.save(table, fields, self)
def __init__(self, args): self.args = args check_required_arguments( args, ('product_name', 'price', 'expiration_date')) self.database_bought = Database( config.BOUGHT_FILE, config.BOUGHT_FIELDS) self.database_products = Database( config.PRODUCTS_FILE, config.PRODUCTS_FIELDS) self.product_name = args['product_name']
def UploadGroupsToDevice(self, ballast_sa): db = Database() ballast_data = db.get_ballast_by_short_address(ballast_sa) self.__address_mode = AddressModes.ballast sa = ballast_data['ballast_short_address'] self.__ballast_or_group_address = sa for i in range(16): if ballast_data['ballast_group_' + str(i)]: self.DaliAddToGroup(sa, i) else: self.DaliRemoveFromGroup(sa, i) return 'Ballast ' + str(sa) + ': groups uploaded'
def insert_page(): db = Database() c = db.conn.cursor() sql_string = 'INSERT INTO pages (page_name, page_sort_order) VALUES ("new",1)' c.execute(sql_string) db.conn.commit() return 'http200'
def delete_equipment(): user = flask_login.current_user # only admin is allowed to delete a new equipment if not user.is_admin(): return 'http403' db = Database() ID = request.args.get('ID') sql1 = 'DELETE FROM equipment WHERE ID = "' + ID + '"' sql2 = 'DELETE FROM pictures WHERE equipment_id = "' + ID + '"' sql3 = 'DELETE FROM documents WHERE equipment_id = "' + ID + '"' db.conn.row_factory = db.dict_factory c = db.conn.cursor() c.execute(sql1) c.execute(sql2) c.execute(sql3) db.conn.commit() updir = os.path.join('database/files') for filename in os.listdir(updir): if filename.startswith("equipment_picture_id_" + ID) or filename.startswith( "equipment_document_id_" + ID): os.remove(os.path.join(updir, filename)) c.close() return 'http200'
def upload_equipment_file(): equipment_id = request.args.get('equipment_id') if request.method == 'POST': file = request.files['file'] filename = secure_filename(file.filename) file_type = filename.rsplit('.', 1)[-1] if files and allowed_file(file.filename): db = Database() c = db.conn.cursor() if file_type == 'png' or file_type == 'jpg' or file_type == 'jpeg' or file_type == 'gif': # prepare upload picture to files filename = 'equipment_picture_id_' + str( equipment_id) + '_filename_' + filename # prepare upload picture to pictures table in db sql_string = 'INSERT INTO pictures (picture_name, equipment_id) VALUES ("' + filename + '","' + equipment_id + '")' else: # prepare upload document to files filename = 'equipment_document_id_' + str( equipment_id) + '_filename_' + filename # prepare upload document to documents table in db sql_string = 'INSERT INTO documents (document_name, equipment_id) VALUES ("' + filename + '","' + equipment_id + '")' c.execute(sql_string) db.conn.commit() updir = os.path.join('database/files') file.save(os.path.join(updir, filename)) file_size = os.path.getsize(os.path.join(updir, filename)) json_string = '{"name": "' + filename + '", "size": "' + str( file_size) + '"}' return json.dumps(json_string)
def deny_access(): user = flask_login.current_user # only admin is allowed to do this if not user.is_admin(): return 'http403' db = Database() user_id = request.args.get('user_id') sql1 = 'DELETE FROM users WHERE ID = "' + user_id + '"' sql2 = 'DELETE FROM documents WHERE user_id = "' + user_id + '"' db.conn.row_factory = db.dict_factory c = db.conn.cursor() c.execute(sql1) c.execute(sql2) db.conn.commit() my_dir = os.path.join('database/files/') for filename in os.listdir(my_dir): if filename.startswith("user_register_upload_id_" + user_id): os.remove(os.path.join(my_dir, filename)) # my_dir = os.path.join('static/images/upload/') # for filename in os.listdir(my_dir): # if filename.startswith("user_register_upload_id_" + user_id): # os.remove(os.path.join(my_dir, filename)) c.close() # sent email to user that he got denied access to the db return 'http200'
def change_password_form(): email_hash = request.form.get('email_hash') new_pw_hash = request.form.get('new_pw_hash') confirm_pw_hash = request.form.get('confirm_pw_hash') if new_pw_hash == "" or confirm_pw_hash == "": flash("Make sure that both passwords are the filled in.") return render_template("change_password.html", email_hash=email_hash) if new_pw_hash != confirm_pw_hash: flash("Make sure that both new passwords are the same.") return render_template("change_password.html", email_hash=email_hash) db = Database() db.conn.row_factory = db.dict_factory c = db.conn.cursor() sql_string = 'SELECT ID, user_email FROM users' c.execute(sql_string) data = c.fetchall() email = '' for user in data: user_email_hash = hashlib.sha256( user['user_email'].encode()).hexdigest() if user_email_hash == email_hash: email = user['user_email'] sql1 = 'UPDATE users SET user_set_pw="1", user_pw_hash = "' + new_pw_hash + '" WHERE user_email = "' + email + '"' c.execute(sql1) db.conn.commit() c.close() flash("Password successfully changed.") return redirect(url_for('auth.login'))
def setup(self): config = configparser.ConfigParser() config.read(testpath + '/' + 'config.ini') self.db = Database(config['db']['host'], config['db']['user'], config['db']['pass'], config['db']['database']) self.db.q("DROP TABLE IF EXISTS `test`") self.db.q( "CREATE TABLE `test` (\ `id` int(11) NOT NULL AUTO_INCREMENT,\ `name` varchar(200) NOT NULL,\ `tested` tinyint(1) NOT NULL DEFAULT '0',\ PRIMARY KEY (`id`)\ ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;" )
class Test_Database(object): """Unit tests for Database class""" model = None db = None def setup(self): config = configparser.ConfigParser() config.read(testpath + '/' + 'config.ini') self.db = Database(config['db']['host'], config['db']['user'], config['db']['pass'], config['db']['database']) self.db.q("DROP TABLE IF EXISTS `test`") self.db.q( "CREATE TABLE `test` (\ `id` int(11) NOT NULL AUTO_INCREMENT,\ `name` varchar(200) NOT NULL,\ `tested` tinyint(1) NOT NULL DEFAULT '0',\ PRIMARY KEY (`id`)\ ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;" ) def teardown(self): self.db.q("DROP TABLE IF EXISTS `test`") self.db.close() def test_fetch_one(self): assert self.db.fetch_one("SELECT 1") == 1 def test_fetch_all(self): data = self.db.fetch_all("SELECT 1 as a, 2 as b UNION SELECT 3 as a, 4 as b") compare = [ {'a': 1, 'b': 2}, {'a': 3, 'b': 4}, ] assert compare == data def test_fetch_col(self): data = self.db.fetch_col("SELECT 1 as a, 2 as b UNION SELECT 3 as a, 4 as b") compare = [1, 3] assert compare == data def test_fetch_row(self): data = self.db.fetch_row("SELECT 1 as a, 2 as b UNION SELECT 3 as a, 4 as b") compare = {'a': 1, 'b': 2} assert compare == data def atest_fetch_pairs(self): data = self.db.fetch_pairs("SELECT 1 as a, 2 as b UNION SELECT 3 as a, 4 as b") compare = {1: 2, 3: 4} assert compare == data def test_escape(self): assert self.db.escape(r"a\b'c") == r"a\\b\'c" def test_quote(self): assert self.db.quote(r"a\b'c") == r"'a\\b\'c'" def test_q(self): curs = self.db.q("SELECT 1", True) assert curs.__class__.__name__ == 'MySQLCursor' curs.fetchall() curs.close() def test_insert(self): assert self.db.fetch_one("SELECT COUNT(id) FROM `test`") == 0 self.db.insert('test', {'name': 'aaa'}) assert self.db.fetch_one("SELECT COUNT(id) FROM `test`") == 1 with pytest.raises(mysql.connector.IntegrityError): self.db.insert('test', {'id': 1, 'name': 'aaa'}) self.db.insert('test', {'id': 1, 'name': 'aaa'}, ignore=True) # Without ex def test_insert_mass(self): assert self.db.fetch_one("SELECT COUNT(id) FROM `test`") == 0 data = [] for i in range(1, 75): data.append({'id': i, 'name': 'aaa' + str(i)}) self.db.insert_mass('test', data) assert self.db.fetch_one("SELECT COUNT(id) FROM `test`") == 74 self.db.q("TRUNCATE TABLE `test`") data = [] for i in range(1, 30): data.append({'id': i, 'name': 'aaa' + str(i)}) self.db.insert_mass('test', data) assert self.db.fetch_one("SELECT COUNT(id) FROM `test`") == 29 data = [] for i in range(1, 30): data.append({'id': i, 'name': 'aaa' + str(i)}) with pytest.raises(mysql.connector.IntegrityError): self.db.insert_mass('test', data) data = [] for i in range(1, 30): data.append({'id': i, 'name': 'aaa' + str(i)}) self.db.insert_mass('test', data, ignore=True) def test_update(self): self.db.insert('test', {'name': 'aaa'}) self.db.insert('test', {'name': 'bbb'}) self.db.insert('test', {'name': 'ccc'}) assert self.db.fetch_one("SELECT COUNT(id) FROM `test` WHERE `tested`=0") == 3 self.db.update('test', {'tested': 1}, "`name`='aaa'") assert self.db.fetch_one("SELECT COUNT(id) FROM `test` WHERE `tested`=0") == 2 def test_update_mass(self): data = [] for i in range(1, 30): data.append({'id': i, 'name': 'aaa' + str(i)}) self.db.insert_mass('test', data) assert self.db.fetch_one("SELECT COUNT(id) FROM `test` WHERE `tested`=0") == 29 self.db.update_mass('test', 'tested', {"`name`='aaa1'": 1, "`name`='aaa2'": 1, "`name`='aaa3'": 1, }) assert self.db.fetch_one("SELECT COUNT(id) FROM `test` WHERE `tested`=0") == 26 def test_replace(self): assert self.db.fetch_one("SELECT COUNT(id) FROM `test`") == 0 self.db.insert('test', {'id': 1, 'name': 'aaa'}) assert self.db.fetch_one("SELECT COUNT(id) FROM `test` WHERE `name`='aaa' AND `id`=1") == 1 self.db.replace('test', {'id': 1, 'name': 'aaa1'}) assert self.db.fetch_one("SELECT COUNT(id) FROM `test` WHERE `name`='aaa1' AND `id`=1") == 1