def convert(): list_voices = [] try: database_connection = DbConnection() with database_connection: query_voices = database_connection.query(Voice.UNCONVERTED_FILES) for voice in query_voices: list_voices.append(Voice(id_num=voice[0], voice_file=voice[1], email=voice[2], name=voice[3], tittle=voice[4])) if list_voices.__len__() != 0: database_connection.update(Voice.create_update_converting_sql(list_voices)) except: print('error en la base de datos') for voice in list_voices: file_mp3 = voice.voice_file[:-3] if voice.voice_file[-3:] != 'mp3': try: media_in = Settings.MEDIA_DIR + voice.voice_file path_out = 'converted/' + file_mp3 + 'mp3' media_out = Settings.MEDIA_DIR + path_out my_thread = Thread(target=ffmpeg, args=[media_in, media_out, voice.id_num, path_out, voice.email, voice.tittle, voice.name]) my_thread.start() my_thread.join(60) except OSError as e: print('error de os')
def get_companies_to_portfolio(portfolio, db=None): """ Get all company associate to a portfolio :param portfolio: id or name of portfolio :type portfolio: int|string :param db: if we have already connexion in other function who cal this function :type db: DbConnection :return: list company name and symbol :rtype: list[dict] """ if not db or not isinstance(db, DbConnection): db = DbConnection(HOST, USER, PASSWORD, DATABASE) # check if id or name of portfolio, if name => get id if isinstance(portfolio, str): if not portfolio.isdigit(): id_portfolio = get_id_portfolio(portfolio) portfolio = id_portfolio[0].get('id_portfolio')[0] # check if portfolio number is valid if not portfolio: raise ValueError( "Need to portfolio valid to add company to portfolio. portfolio = %s" % portfolio) query = """SELECT c.name, cp.symbol_company FROM company_portfolio cp JOIN company c ON c.symbol = cp.symbol_company WHERE cp.id_portfolio = %(portfolio)s AND cp.is_activate_in_portfolio = 1""" res = db.select_in_db(query, {'portfolio': portfolio}) return_value = [] for name, symbol in res: return_value.append({'name': name, 'symbol': symbol}) return return_value
def add_companies_to_portfolio(portfolio, list_company, db=None): """ Add list of company to portfolio :param portfolio: id or name of portfolio :type portfolio: int|string :param list_company: list contains symbol of company :type list_company: list[str] :param db: if we have already connexion in other function who cal this function :type db: DbConnection :return: nb added company to portfolio :rtype: int """ if not db or not isinstance(db, DbConnection): db = DbConnection(HOST, USER, PASSWORD, DATABASE) # check if id or name of portfolio, if name => get id if isinstance(portfolio, str): if not portfolio.isdigit(): id_portfolio = get_id_portfolio(portfolio) portfolio = id_portfolio[0].get('id_portfolio')[0] # check if portfolio number is valid if not portfolio: raise ValueError( "Need to portfolio valid to add company to portfolio. portfolio = %s" % portfolio) # add each company to portfolio query = """INSERT INTO company_portfolio (id_portfolio, symbol_company, is_activate_in_portfolio) VALUES (%(id_portfolio)s, %(symbol_company)s, 1) ON DUPLICATE KEY UPDATE is_activate_in_portfolio = 1""" nb_company_add = 0 for company in list_company: params = {'id_portfolio': portfolio, 'symbol_company': company} nb_company_add += db.modified_db(query, params) return nb_company_add
def get_info_simulation(id_simulation, db=None): """ Get information of simulation as id of simulation :param id_simulation: id of simulation :type id_simulation: int :param db: if we have already connexion in other function who cal this function :type db: DbConnection :return: information on simulation :rtype: list[dict] """ if not id_simulation: raise ValueError('ID simulation is None.') if not db or type(db) is not DbConnection: db = DbConnection(HOST, USER, PASSWORD, DATABASE) query = """SELECT id_portfolio, parameters, results FROM simulation WHERE id_simulation = %(id_simulation)s""" res = db.select_in_db(query, {'id_simulation': id_simulation}) return_value = [] for id_portfolio, parameters, results in res: return_value.append({ 'id_portfolio': id_portfolio, 'parameters': parameters, 'results': results }) return return_value
def get_transaction_simulation(id_simulation, db=None): """ Get transaction of simulation as id of simulation :param id_simulation: id of simulation :type id_simulation: int :param db: if we have already connexion in other function who cal this function :type db: DbConnection :return: transaction of simulation :rtype: list[dict] """ if not id_simulation: raise ValueError('ID simulation is None.') if not db or type(db) is not DbConnection: db = DbConnection(HOST, USER, PASSWORD, DATABASE) query = """SELECT id_transaction, id_portfolio, id_symbol, quantity, value_current, transaction_date FROM "transaction" WHERE id_simulation = %(id_simulation)s""" res = db.select_in_db(query, {'id_simulation': id_simulation}) return_value = [] for id_transaction, id_portfolio, id_symbol, quantity, value_current, transaction_date in res: return_value.append({ 'id_transaction': id_transaction, 'id_portfolio': id_portfolio, 'id_symbol': id_symbol, 'quantity': quantity, 'value_current': value_current, 'transaction_date': transaction_date }) return return_value
def __init__(self): self.__str__ = "Trader" self.email = "" self.company = "" self.db = DbConnection('market', 'stocks') # create database connection object self.menu_options = [ x for x in dir(self) if x.startswith("m_") ] # create options menu for user using class attributes while True: self._menu() # get input from user prompt and call the appropriate function name try: option_num = int( input( "Enter option number and press ENTER, or 0 to quit: ")) if option_num == 0: print("Good bye!") return try: func = getattr(self, self.menu[option_num]) print(func()) except AttributeError: print( "Unable to retrieve function name %s from menu option" % self.menu[option_num]) except: print("Invalid option")
class ConverterFactory: def __init__(self, fileName): self.fileName = fileName Dbconnection = DbConnection(self.fileName) self.connection = Dbconnection.connectDb() self.db = DbConnection(self.fileName) Dbinfoset = Dbinfo(self.connection) self.rules = Dbinfoset.ctcaeRuleTable() # store the ctcae rule information in this object self.para = Dbinfoset.ctcaeParaTable() # store the ctcae parameter information in this object self.units = Dbinfoset.ctcaeUnitTable() # store the ctcae units information in this object self.alias = Dbinfoset.ctcaeAliaTable() # store the ctcae alias information in this object self.unittrans = Dbinfoset.ctcaeUnitTransfer() # store the ctcae mu information in this object def ConverterLine(self): AeCompare = AeCompar(self.fileName) AeTestlist = AeCompare.aeReal() TestConvertlist = [] for item in AeTestlist: print(item) subj_3d_id = item[0] # aespid = item[1] aeName = item[2] labstartdate = item[6] labenddate = item[7] aeConverter = AeConverter(self.connection, subj_3d_id, labstartdate, labenddate, self.rules, self.para, self.alias, self.units, self.unittrans, aeName ) # dbConnection, subj_3d_id, startdate, enddate, rules, para, alias, units, unittrans, aeName) valuedic = aeConverter.lrInfo() if valuedic: # if the valuedic is not None transpre = aeConverter.transPrepare() if transpre: # if the list that contains rules to be evaluated is not None gradelist = aeConverter.gradeCheker() itemlist = [item[0], item[1], item[2],item[3], item[4], item[5], gradelist] TestConvertlist.append(itemlist) if TestConvertlist: sqlstring = "delete from cc_ae_grade " self.db.execute(sqlstring) return TestConvertlist
def home(): # connection = None # try: connection = DbConnection("mysql-app", "usuarios") res = jsonify( connection.query( "SELECT id_user, usuario, nombre, apellido FROM login;")) res.status_code = 200 return res
def run(self, username, password, server, port, powerTopic, energyTopic, deviceName): dbConnection = DbConnection() self.dbIdTestDevice = dbConnection.findDataloggerDbIdByName(deviceName) self.logger.info("Device dbId %s" % self.dbIdTestDevice) dbConnection.close() self.superRun(username, password, server, port, powerTopic) self.subscribe(energyTopic, 0) self.energyTopic = energyTopic
def insert_transaction_to_db(id_portfolio, symbol_company, quantity, value_current, transaction_date, id_simulation=None, db=None): """ Insert transaction link to portfolio :param id_portfolio: id of portfolio :type id_portfolio: int :param symbol_company: symbol of a company :type symbol_company: str :param quantity: :type quantity: int :param value_current: :type value_current: float :param transaction_date: :type transaction_date: datetime :param id_simulation: if transaction is during simulation :type id_simulation: int :param db: if we have already connexion in other function who cal this function :type db: DbConnection :return: number row affected :rtype: int """ if not id_portfolio: raise ValueError( "Need to id_portfolio valid to create transaction. id_portfolio = %s" % id_portfolio) if not symbol_company: raise ValueError( "Need to symbol_company valid to create transaction. symbol_company = %s" % symbol_company) if not db or type(db) is not DbConnection: db = DbConnection(HOST, USER, PASSWORD, DATABASE) query = """INSERT INTO transaction (id_portfolio, id_symbol, quantity, value_current, transaction_date, id_simulation) VALUES (%(id_portfolio)s, %(symbol_company)s, %(quantity)s, %(value_current)s, %(transaction_date)s, %(id_simulation)s) ON DUPLICATE KEY UPDATE id_symbol = %(symbol_company)s, quantity = %(quantity)s, value_current = %(value_current)s, transaction_date = %(transaction_date)s, id_simulation = %(id_simulation)s)""" params = { 'id_portfolio': id_portfolio, 'symbol_company': symbol_company, 'quantity': quantity, 'value_current': value_current, 'transaction_date': transaction_date, 'id_simulation': id_simulation } return db.modified_db(query, params)
def ffmpeg(media_in, media_out, id_num, path_out, email, tittle, name): output = subprocess.call(['ffmpeg', '-i', media_in, media_out, '-y']) if output < 0: print('error en conversion') else: try: database_connection = DbConnection() with database_connection: database_connection.update(Voice.create_update_converted_sql(id_num, path_out)) Email.send_email(email=email, tittle=tittle, name=name) except: print('Error actualizando')
class CapstoneTestCase(unittest.TestCase): def setUp(self): self.db = DbConnection("foo_db", "foo_coll") def test_chr2hex_encode(self): self.assertEqual(chr2hex.encode("snhucapstone"), "736E687563617073746F6E65") def test_chr2hex_decode(self): self.assertEqual(chr2hex.decode("736E687563617073746F6E65"), "snhucapstone") def test_request_prompt(self): request_prompt.input = mock.Mock() request_prompt.re = mock.Mock() request_prompt.return_value = "m" self.assertEqual("m", request_prompt.return_value) request_prompt.return_value = "t" self.assertEqual("t", request_prompt.return_value) def test_create_document(self): self.db.collection.insert_one = mock.Mock() result = self.db.create_document('{"FOO": 1000}') self.assertEqual(self.db.create_document('{"FOO": 1000}'), result) def test_update_volume(self): self.db.collection.update_one = mock.Mock() self.db.collection.find_one = mock.Mock() self.db.collection.find_one.return_value = {"FOO: 2000"} self.assertEqual(self.db.update_volume("FOO", 2000), self.db.collection.find_one.return_value) def test_delete_document_by_ticker(self): self.db.collection.find_one_and_delete = mock.Mock() self.db.collection.find_one_and_delete.return_value = dict( {"FOO": 1000}) self.assertEqual(self.db.collection.find_one_and_delete.return_value, self.db.delete_document("FOO")) def test_get_industry_tickers(self): self.db.collection.find = mock.Mock() self.db.collection.find.return_value = [{ "Industry": "Semiconductors", "Ticker": "AMD" }, { "Industry": "Semiconductors", "Ticker": "INTL" }] self.assertEqual(self.db.get_industry_tickers("Semiconductors"), ['AMD', 'INTL'])
def __init__(self, fileName): self.fileName = fileName Dbconnection = DbConnection(self.fileName) self.connection = Dbconnection.connectDb() self.db = DbConnection(self.fileName) Dbinfoset = Dbinfo(self.connection) self.rules = Dbinfoset.ctcaeRuleTable() # store the ctcae rule information in this object self.para = Dbinfoset.ctcaeParaTable() # store the ctcae parameter information in this object self.units = Dbinfoset.ctcaeUnitTable() # store the ctcae units information in this object self.alias = Dbinfoset.ctcaeAliaTable() # store the ctcae alias information in this object self.unittrans = Dbinfoset.ctcaeUnitTransfer() # store the ctcae mu information in this object
def process_api_login(): try: with DbConnection(decrypt_dict_vals(dbconfig, {'password'})) as cur: creds = request.get_json(force=True) if authenticate(creds['username'][0:150], creds['password'][0:150], cur): logger.info('authenticate succesful') session['user'] = create_new_session(creds['username'][0:150], cur) return json.dumps({ 'logged_in': True, 'organizations': session['user']['organizations'] }) else: logger.warning('authentication failed.') session['user'] = None return json.dumps({'logged_in': False, 'organizations': [{}]}) #- return '{"logged_in":false}' except Exception as err: logger.error('process_api_login exception: {}, {}, {}'.format( exc_info()[0], exc_info()[1], err)) session['user'] = None return json.dumps({'logged_in': False, 'organizations': [{}]})
def get_chart_list(system_uuid): #TODO - verify that the user has the privliges to see charts for the # grow system identified by <system_uuid> #TODO - I bet this could moved to a decorator or hell put it in enforce_login! logger.info('{}: api/get_chart_list/{}'.format( session['user']['nick_name'], system_uuid)) try: with DbConnection(decrypt_dict_vals(dbconfig, {'password'})) as cur: sql = """select chart_config from grow_system inner join grow_system_devices on grow_system.uuid = grow_system_devices.grow_system_uuid inner join device on device.guid = grow_system_devices.device_uuid where uuid = %s;""" cur.execute(sql, (system_uuid, )) assert ( cur.rowcount == 1 ), 'No or more than one device found. Only one device was expected.' return json.dumps({ 'r': True, 'chart_list': [{ 'rel_url': '/chart/{}/{}'.format(cl['vue_name'], system_uuid) } for cl in cur.fetchone()[0]['chart_list']] }) except: logger.error('error {}, {}'.format(exc_info()[0], exc_info()[1])) return json.dumps({'r': False, 'chart_list': [{}]})
def get_all_portfolio_info(db=None): """ Get all portfolio with name and id :param db: if we have already connexion in other function who cal this function :type db: DbConnection :return: list[dict] """ if not db or type(db) is not DbConnection: db = DbConnection(HOST, USER, PASSWORD, DATABASE) query = """SELECT id_portfolio, name FROM portfolio""" res = db.select_in_db(query) return_value = [] for id_portfolio, name in res: return_value.append({'id_portfolio': id_portfolio, 'name': name}) return return_value
def process_login(): try: with DbConnection(decrypt_dict_vals(dbconfig, {'password'})) as cur: if authenticate(request.form['username'][0:150], request.form['password'], cur): logger.info('authenticate succesful') session['user'] = create_new_session( request.form['username'][0:150], cur) return render_template( 'home.html', devices=session['user']['devices'], selected_device=session['user']['devices'][0], chart_list=session['user']['chart_config']['chart_list']) #- return render_template('home.html', chart_list=session['user']['chart_config']['chart_list']) else: logger.warning('authentication failed.') session['user'] = None flash('incorrect username or password') return render_template('login.html') except: logger.error('process_login exception: {}, {}'.format( exc_info()[0], exc_info()[1])) session['user'] = None flash('system error F_PL') return render_template('login.html')
def get_crops(): logger.info('{}: api/get_crops/'.format(session['user']['nick_name'])) try: with DbConnection(decrypt_dict_vals(dbconfig, {'password'})) as cur: sql = """select grow_batch_id, g.start_date, 'germination' as status, v.common_name, v.species from germination as g inner join grow_batch as gb on g.grow_batch_id = gb.id inner join seed_lot as sl on gb.seed_lot_id = sl.id inner join variety as v on sl.variety_id = v.id union select 100, '20190620' as start_date, 'stage 2' as status, 'basil' as common_name, 'Genovese' as variety;""" cur.execute(sql) if cur.rowcount > 0: crops = [{ 'batch_id': c[0], 'start_date': c[1].strftime('%x'), 'status': c[2], 'name': c[3], 'variety': c[4] } for c in cur.fetchall()] else: crops = [{}] return json.dumps({'r': True, 'crops': crops}) except: logger.error('get_crops exception: {}, {}'.format( exc_info()[0], exc_info()[1])) return json.dumps({'server error': True})
def __init__(self, user_name): self.nick_name = None self.guid = None self.django_username = None self.text_number = None with DbConnection(decrypt_dict_vals(dbconfig, {'password'})) as cur: sql = """select person.nick_name, person.guid, person.django_username, text_number from person where person.django_username = %s;""" cur.execute(sql, (user_name[0:150], )) if cur.rowcount == 1: record = cur.fetchone() self.nick_name = record[0] self.guid = record[1] self.django_username = record[2] self.text_number = record[3] else: logger.warn('no unique database record for {}'.format( user_name[0:150]))
def setUpClass(cls): ManagerDbTest.init_db_mysql_test(HOST, USER, PASSWORD, DATABASE) cls.__db = DbConnection(HOST, USER, PASSWORD, DATABASE) cls.__company = [{ 'symbol': 'GOOGL', 'name': 'Alphabet Inc Class A', 'last_update_historic': None }, { 'symbol': 'AMZN', 'name': 'Amazon.com Inc', 'last_update_historic': None }, { 'symbol': 'AAPL', 'name': 'Apple Inc.', 'last_update_historic': None }, { 'symbol': 'HRB', 'name': 'Block H&R', 'last_update_historic': None }, { 'symbol': 'gps', 'name': 'Gap (The)', 'last_update_historic': None }]
def get_data_json(system_uuid, start_date, end_date): #TODO - verify that the user has the privliges to see the contents of the zip file. logger.info('{}: api/get_data_json/{}/{}/{}'.format( session['user']['nick_name'], system_uuid, start_date, end_date)) try: # Get the fopd device UUID with DbConnection(decrypt_dict_vals(dbconfig, {'password'})) as cur: sql = """select device_uuid from grow_system as gs inner join grow_system_devices as gsd on gs.uuid = gsd.grow_system_uuid where gs.uuid = %s""" cur.execute(sql, (system_uuid, )) # Get the 1st device id returned from the grow system devices list device_uuid = cur.fetchone()[0] result = get_device_data_json(device_uuid, start_date, end_date, session['user']['ct_offset']) if result: return result else: #TODO: Need a different error message here than the s3_error return send_from_directory(path.join(app.root_path, 'static'), 's3_error.jpg', mimetype='image/png') except: logger.error('in /api/get_data_json route: {}, {}'.format( exc_info()[0], exc_info()[1])) return send_from_directory(path.join(app.root_path, 'static'), 's3_error.jpg', mimetype='image/png')
def __init__(self): self.bot = self.create_bot() self.connection = DbConnection() self.commands = { 'add': AddCommand(self.bot, self.connection), 'list': ListCommand(self.bot, self.connection), 'reset': ResetCommand(self.bot, self.connection), 'start': WelcomeCommand(self.bot, self.connection) } self.current_command = None self.register_commands(self.bot)
def set_new_password_reset_code(self, rc): #TODO: update the Person table to contain the password reset code and hte timestamp # save the 6 digit number in the db as the reset code with a timeout of say 1 hour with DbConnection(decrypt_dict_vals(dbconfig, {'password'})) as cur: sql = """update person set password_reset_code = %s, password_reset_code_create_time = now(), password_reset_failed_tries = 0 where guid = %s;""" cur.execute(sql, (rc, self.guid))
def get_device_data_json(device_uuid, start_date, end_date, utc_offset): try: q = """select ea.name as attribute, seo.units as units, seo.utc_timestamp + interval '%s' hour as sample_time, seo.measurement_value as value, d.local_name as device_local_name, es.name as subject_name, esl.location_guid as subject_location_id from environment_observation as eo inner join scalar_environment_observation as seo on eo.id = seo.environment_observation_id inner join environment_attribute as ea on eo.environment_attribute_id = ea.id inner join environment_subject_location as esl on esl.guid = eo.environment_subject_location_guid inner join environment_subject as es on esl.environment_subject_id = es.id inner join device as d on d.guid = eo.participant_guid where eo.participant_guid = %s and utc_timestamp >= timestamp %s and utc_timestamp < timestamp %s order by seo.utc_timestamp desc; """ with DbConnection(decrypt_dict_vals(dbconfig, {'password'})) as cur: # Note: We convert time filters from the user's local time to utc. cur.execute(q, (utc_offset, device_uuid, datetime.strptime(start_date, '%Y-%m-%d') - timedelta(hours=utc_offset), datetime.strptime(end_date, '%Y-%m-%d') + timedelta(days=1) - timedelta(hours=utc_offset))) rc = cur.rowcount if rc == 0: logger.warning('No device data available') return json.dumps('No device data available') obs_list = [] for record in cur: #- obs = {'value_name': None} obs = {'type': 'environment'} obs['device_name'] = record[4] obs['device_id'] = device_uuid obs['subject'] = record[5] obs['subject_location_id'] = record[6] obs['attribute'] = record[0] obs['value'] = record[3] obs['units'] = record[1] #- obs['ts'] = record[2].strftime('%c') obs['ts'] = record[2].isoformat() obs_list.append(obs) return json.dumps(obs_list, indent=3) except: logger.error('in get_device_data_json: {}, {}'.format( exc_info()[0], exc_info()[1])) return None
def check_that_unique_user_exists(user_name): # See if the user name given matches anything in the database. with DbConnection(decrypt_dict_vals(dbconfig, {'password'})) as cur: sql = """select person.guid from person where person.django_username = %s;""" cur.execute(sql, (user_name[0:150], )) if cur.rowcount == 1: return True else: return False
def get_id_portfolio(name, db=None): """ Get name of portfolio as name of portfolio :param name: name of portfolio :type name: str :param db: if we have already connexion in other function who cal this function :type db: DbConnection :return: result in list of dict :rtype: list[dict] """ if not name: raise ValueError('Name portfolio is None.') if not db or type(db) is not DbConnection: db = DbConnection(HOST, USER, PASSWORD, DATABASE) query = """SELECT id_portfolio FROM portfolio WHERE name = %(name)s""" res = db.select_in_db(query, {'name': name}) return_value = [] for id_portfolio in res: return_value.append({'id_portfolio': id_portfolio}) return return_value
def get_name_portfolio_name(id_portfolio, db=None): """ Get name of portfolio as id of portfolio :param id_portfolio: id of portfolio :type id_portfolio: int :param db: if we have already connexion in other function who cal this function :type db: DbConnection :return: result in list of dict :rtype: list[dict] """ if not id_portfolio: raise ValueError('ID portfolio is None.') if not db or type(db) is not DbConnection: db = DbConnection(HOST, USER, PASSWORD, DATABASE) query = """SELECT name FROM portfolio WHERE id_portfolio = %(id)s""" res = db.select_in_db(query, {'id': id_portfolio}) return_value = [] for name in res: return_value.append({'name': name}) return return_value
def writeFile(): os.mkdir(dirNand + "NoCleanedFile") for id in idUsers[0:70]: i = 0 file = open(dirNand + "NoCleanedFile/" + str(id) + ".txt", "w+") tweet = DbConnection.takeText(id) while (i < len(tweet)): text = tweet[i]['Tweet']['text'] newText = unicodedata.normalize('NFKD', text).encode('ascii', 'ignore') file.write(newText + "\n") i = i + 1 file.close()
def get_device_data(out_fp, device_uuid, start_date, end_date, utc_offset): try: q = """select ea.name as attribute, seo.units as units, seo.utc_timestamp + interval '%s' hour as sample_time, seo.measurement_value as value from environment_observation as eo inner join scalar_environment_observation as seo on eo.id = seo.environment_observation_id inner join environment_attribute as ea on eo.environment_attribute_id = ea.id inner join environment_subject_location as esl on esl.guid = eo.environment_subject_location_guid inner join environment_subject as es on esl.environment_subject_id = es.id where participant_guid = %s and utc_timestamp >= timestamp %s and utc_timestamp < timestamp %s order by seo.utc_timestamp desc; """ with DbConnection(decrypt_dict_vals(dbconfig, {'password'})) as cur: # Note: We convert time filters from the user's local time to utc. cur.execute(q, (utc_offset, device_uuid, datetime.strptime(start_date, '%Y-%m-%d') - timedelta(hours=utc_offset), datetime.strptime(end_date, '%Y-%m-%d') + timedelta(days=1) - timedelta(hours=utc_offset))) rc = cur.rowcount if rc == 0: logger.warning('No device data available') return False csv_writer = csv.writer(out_fp, delimiter=',', quotechar='|', quoting=csv.QUOTE_MINIMAL) rc = 0 csv_writer.writerow(['attribute', 'unit', 'sample time', 'value']) for record in cur: csv_writer.writerow(record) if rc == 0: logger.info('writing first row {}'.format( out_fp.getvalue())) rc = rc + 1 out_fp.seek(0) return True except: logger.error('in get_device_data: {}, {}'.format( exc_info()[0], exc_info()[1])) return False
def create_portfolio(name=None, db=None): """ Create new portfolio :param name: name of portfolio :type name: str :param db: if we have already connexion in other function who cal this function :type db: DbConnection :return: id of portfolio :rtype int """ if not db or type(db) is not DbConnection: db = DbConnection(HOST, USER, PASSWORD, DATABASE) if not name: query = """SELECT (CASE COUNT(id_portfolio) WHEN 0 THEN 1 ELSE COUNT(id_portfolio)+1 END) AS new_id FROM portfolio""" res = db.select_in_db(query) name = 'new_portfolio' + str(res[0][0]) query = """INSERT INTO portfolio (name) VALUES (%(name)s) ON DUPLICATE KEY UPDATE name = name""" db.modified_db(query, {'name': name}) # get id portfolio return get_id_portfolio(name, db)
def __init__(self): """Constructor""" self.dbcon = DbConnection.get_con()
def __init__(self): self.db = DbConnection()
class Database(): def __init__(self): self.db = DbConnection() """Get Queries""" def getById(self,id): return self.db.readQuery("""select * from inventory where product_id = '{}'""".format(id)) def getNameAndSellingPrice(self,productId): return self.db.readQuery("""select name, selling_price from inventory where product_id = '{}'""".format(productId)) def getByName(self,productName): productNameLike = str(productName+"%") return self.db.readQuery("""select * from inventory where name LIKE '{}'""".format(productNameLike)) def getBySupplier(self,supplierName): supplierNameLike = str(supplierName+"%") return self.db.readQuery("""select * from inventory where supplier LIKE '{}'""".format(supplierNameLike)) def getItemsByPrice(self, price): return self.db.readQuery("""select * from inventory where selling_price = '{}'""".format(price)) def getItems(self): return self.db.readQuery("""select * from inventory""") def getAllProductIds(self): return self.db.readQuery("""select product_id from inventory""") def getItemsWithLowInventory(self): return self.db.readQuery("""select * from inventory where amount_in_stock < 5""") def getFromInventoryLogByProductId(self, productId): return self.db.readQuery("""select * from inventory_log where product_id = '{}'""".format(productId)) def totalItemsSoldFromProductId(self,productId): return self.db.readQuery("""select products_sold from inventory_log where product_id = '{}'""".format(productId)) def totalItemsBoughtFromProductId(self,productId): return self.db.readQuery("""select products_recieved from inventory_log where product_id = '{}'""".format(productId)) def getTransactions(self): return self.db.readQuery("""select * from transactions""") def getUserLogs(self): return self.db.readQuery("""select * from user_logging""") """User Queries""" def getUser(self, username): usernameLike = str(username+"%") return self.db.readQuery("""select * from customer_emails where customer_name LIKE '{}'""".format(usernameLike)) def getEmailsInSystem(self): return self.db.readQuery("""select * from customer_emails""") def getPasswordForUser(self,username): return self.db.readQuery("""select password from employee_credentials where username = '******'""".format(username)) def getManagerStatus(self): username = session.get("current_user") return self.db.readQuery("""select ismanager from employee_credentials where username = '******'""".format(username)) """Insert Queries""" def writeEmailToDatabase(self, customerName, customerEmail): self.db.writeQuery("""insert into customer_emails values ('{}','{}')""".format(customerName,customerEmail)) def insertNewInventoryItem(self, name, productId, originalCost, sellPrice, supplier, productType, amountInStock): self.db.writeQuery("""insert into inventory values ('{}','{}','{}','{}','{}','{}','{}')""".format(name,productId, originalCost, sellPrice, supplier, productType, amountInStock)) def insertIntoInventoryLog(self,productId, productsRecieved, productsSold): self.db.writeQuery("""insert into inventory_log values ('{}','{}', '{}')""".format(productId, productsRecieved, productsSold)) def insertNewTransaction(self,transactionId,amountPaid,paymentType,itemsPurchased): self.db.writeQuery("""insert into transactions values ('{}','{}','{}','{}')""".format(transactionId, amountPaid, paymentType, itemsPurchased)) def insertUserLog(self,userName, endpoint, time): self.db.writeQuery("""insert into user_logging values ('{}', '{}', '{}')""".format(userName, endpoint, time)) """Update Queries""" def updateInventoryItem(self, name, productId, originalCost, sellPrice, supplier, productType, amountInStock): self.db.writeQuery("""update inventory set name ='{}', purchase_price = '{}', selling_price = '{}', supplier = '{}', product_type = '{}', amount_in_stock = '{}' where product_id='{}'""".format(name, originalCost, sellPrice, supplier, productType, amountInStock,productId)) def updateInventoryLog(self, productId,howManyPurchased): inventoryLogGet = self.getFromInventoryLogByProductId(productId) amountSold = inventoryLogGet[0][2] + int(howManyPurchased) self.db.writeQuery("""update inventory_log set products_sold = '{}' where product_id = '{}'""".format(amountSold, productId)) def updateInventoryLogItemsPurchased(self, productId,howManyRecieved): inventoryLogGet = self.getFromInventoryLogByProductId(productId) amountBought = inventoryLogGet[0][1] + int(howManyRecieved) self.db.writeQuery("""update inventory_log set products_recieved = '{}' where product_id = '{}'""".format(amountBought, productId)) def orderNewProducts(self,productId, howManyPurchased): inventoryGet = self.getById(productId) amountInStock = inventoryGet[0][6] + int(howManyPurchased) self.db.writeQuery("""update inventory set amount_in_stock = '{}' where product_id = '{}'""".format(amountInStock, productId)) def createSale(self,productId, newPrice): self.db.writeQuery("""update inventory set selling_price = '{}' where product_id = '{}'""".format(newPrice, productId)) def sellItem(self,productId,howManyPurchased): item = self.getById(productId) howMany = item[0][6] - int(howManyPurchased) self.db.writeQuery("""update inventory set amount_in_stock = '{}' where product_id = '{}'""".format(howMany, productId))
def __init__(self): """ Get a DB connection. """ self.dbcon = DbConnection.get_con()
def __init__(self): ''' Constructor ''' self.dbcon = DbConnection.get_con()
media = db_connection.graph.cypher.execute( "match (n:Envio) where n.mes= {a} " "return avg(n.peso) as mediaPeso, avg(n.volumen) as mediaVolumen, count(n) as cuenta " , a = mes) return media @staticmethod def getResumenOcupacion(tipo): db_connection = DbConnection() media = db_connection.graph.cypher.execute( "match (n:Locomotora)-[r]-(e:Estacion) where n.tipo = {a} " "return count(r) as numero", a = tipo) return media if __name__ == '__main__': DbConnection.deleteAllGraph() DbConnection.init() Estacion.crearEstaciones() Estacion.crearTramos() #Estacion.getVias("Madrid") madrid = Estacion.getEstacionFromNombre('Madrid') Locomotora.createLocomotoras() Locomotora.asociarLocomotoras(madrid) Envio.createEnvios() Envio.asociarEnvios() print "Camino economico Madrid - LaMancha:" print Estacion.getRutaOptima('Madrid', 'LaMancha', 'economico')