class ExtractCompanies(): def __init__(self): self._ConnectDB = ConnectDB() self._connection = self._ConnectDB.getConnection() self._cursor = None self._connectionMongo = ConnectMongo() self._dbMongo = self._connectionMongo.getConnetion() self._dbMongo['ExtractCompanies'].drop() self._collection = self._dbMongo['ExtractCompanies'] def exportData(self): try: self._cursor = self._connection.cursor() sql = readSql(os.path.dirname(os.path.abspath(__file__)), 'ExtractCompanies.sql') self._cursor.execute(sql) df = pd.read_sql_query(sql, self._connection) data = json.loads(df.to_json(orient='records', date_format='iso')) print('- Exportando empresas:') for companie in data: self._collection.update_one( { "codi_emp": companie['codi_emp'] }, { "$set": companie}, upsert=True ) print(f"\t- {companie['codi_emp']} | {companie['nome_emp']}") except Exception as e: print(f"Erro ao executar a consulta. O erro é: {e}") finally: if self._cursor is not None: self._cursor.close() self._ConnectDB.closeConnection() self._connectionMongo.closeConnection()
def addDashboardCourses(self): conn = ConnectDB() userID = CommonUtils.getUserIDfromUserName( Ui_ProfessorLoginWindow.LoggedInUserName) query = "Select CourseID, Semester from course_professor_table where ProfessorID=%s" params = (userID, ) courseIDWithSemesterList = conn.query(query, params) courseIDList = [courseID[0] for courseID in courseIDWithSemesterList] '''couresNameList = [] couresNumberList =[] for courseID in courseIDList: couresNameList.append(CommonUtils.getCourseNameFromCourseID(courseID)) couresNumberList.append(CommonUtils.getCourseNumberFromCourseID(courseID))''' courseNumberAndNameList = [] for couresID in courseIDList: courseNumberAndNameList.append( CommonUtils.getCourseNumberNameYearFromCourseID(couresID)) print(courseNumberAndNameList) courseTuple = () for courseIDAndName in courseNumberAndNameList: courseTuple = [x for x in courseIDAndName] courseNumber = courseTuple[0][0] courseName = courseTuple[0][1] courseYear = courseTuple[0][2] self.dashboardWidget.addItem(courseNumber + " " + courseName + " " + courseYear) self.dashboardWidget.setCurrentRow(0) '''
def __init__(self): self._ConnectDB = ConnectDB() self._connection = self._ConnectDB.getConnection() self._cursor = None self._connectionMongo = ConnectMongo() self._dbMongo = self._connectionMongo.getConnetion() self._dbMongo['ExtractCompanies'].drop() self._collection = self._dbMongo['ExtractCompanies']
class ScanFiles(object): def __init__(self, debug): self.DEBUG = debug if self.DEBUG == 1: print("scanfiles.py : constructor...") self.__rh = ReadHeader(self.DEBUG) def __del__(self): if self.DEBUG == 1: print("scanfiles.py : destructor...") def getDatabaseName(self): return self.__databasename def setDatabaseName(self, database): self.__databasename = database def processFile(self, path): countFiles = 0 numberOfDoubles = 0 self.__connectDB = ConnectDB(self.__databasename, 0) # traitement des fichiers se trouvant dans le dossier for root, dirs, files in os.walk(path): for name in files: if name.endswith((".dat")): # creation du chemin complet vers le fichier dat fullPath = join(root,name) # determination de la date de creation du fichier t = os.path.getmtime(fullPath) ctime = datetime.fromtimestamp(t) # calcul du nombre de jours depuis la date de creation du fichier time_to_today = abs(date.today() - ctime.date()) if self.DEBUG == 1 or self.DEBUG == 2: print("Scanfiles.py : Time from file creation date to today in days = %s" % time_to_today.days) self.__rh.setFilename(fullPath) self.__rh.openFile() filecreationdate = self.__rh.getFileCreationDate() filepath = self.__rh.getFilePath() filename = self.__rh.getFileName() filesize = self.__rh.getFileSize() # Teste si le fichier existe deja dans la base de donnee # Si le fichier n'existe pas, l'inserer dans la base de donnees if self.__connectDB.selectDB(filecreationdate, name, root, filesize) == False: self.__connectDB.insertDB(filecreationdate, name, root, filesize) else: numberOfDoubles = self.__connectDB.findDoubles(name) print("Scanfiles.py : fichier existe deja dans la base de donnee...") print("Scanfiles.py : number of files", name,"present in database = ", numberOfDoubles)
def __init__(self, driver='none', server='none', database='none', user='******', password='******'): self.connection = ConnectDB(driver, server, database, user, password) self.string_connection = { 'driver': 'none', 'server': 'none', 'database': 'none', 'user': '******', 'password': '******', 'truested_connection': 'no' } self.has_error = False self.str_error = '' self.str_createDB = '' self.path = '' self.file_table_json = None self.json = None self.script_ddl = '' if (self.connection != None): self.dictionary_database() if (self.json != None): self.create_table() self.connection.closeConnection() else: self.connection.setStringConnection(self.default_driver, self.default_server, self.default_database, self.default_user, self.default_pwd) if (self.connection.connect()): self.connected = True else: self.connected = False if (self.connected): self.createDatabase()
def main(): db = ConnectDB.ConnectDB() # test get all stock all_stock = db.get_stock_info('AAPL') for row in all_stock: print row all_sector_list = db.get_sector_company_list() for row in all_sector_list: print row
def __init__(self, db_no="0"): self.db_no = db_no db_config = Config.MYSQL[db_no] # 获取配置文件中连接信息 self.__con = MySQLdb.connect( host=db_config["host"], user=db_config["uname"], passwd=db_config["pwd"], db=db_config["db_name"], port=db_config["port"], charset=db_config["encoding"], ) self.__con.autocommit = True self.__encoding = ConnectDB.get_encoding(db_no)
def __init__(self): self.db = ConnectDB.ConnectDB()
class CreateDB(object): def __init__(self, driver='none', server='none', database='none', user='******', password='******'): self.connection = ConnectDB(driver, server, database, user, password) self.string_connection = { 'driver': 'none', 'server': 'none', 'database': 'none', 'user': '******', 'password': '******', 'truested_connection': 'no' } self.has_error = False self.str_error = '' self.str_createDB = '' self.path = '' self.file_table_json = None self.json = None self.script_ddl = '' if (self.connection != None): self.dictionary_database() if (self.json != None): self.create_table() self.connection.closeConnection() else: self.connection.setStringConnection(self.default_driver, self.default_server, self.default_database, self.default_user, self.default_pwd) if (self.connection.connect()): self.connected = True else: self.connected = False if (self.connected): self.createDatabase() def get_string_connection(self): if ("pyodbc" in str(type(self.connection)).lower()): self.string_connection['driver'] = self.connection.driver self.string_connection['server'] = self.connection.server self.string_connection['database'] = self.connection.database self.string_connection['user'] = self.connection.user self.string_connection['password'] = self.connection.password return self.string_connection # TODO: Definir a construção das tabelas de self.tables def create_table(self): self.str_createDB = '' self.clear_error() try: lst_tables = self.json.get('tables')[0].get('list_of_tables') for item in lst_tables: if (not self.has_table(item.get('name_table'))): self.script_ddl += "CREATE TABLE " + item.get( 'name_table') + '(\n' count_line = 0 for item_colunm in item.get('list_of_fields'): count_line += 1 self.script_ddl += '\t ' self.script_ddl += item_colunm.get( 'name_field') #nome do campo self.script_ddl += ' ' + item_colunm.get( 'type_field') #tipo de campo default_value = item_colunm.get('default_value') if (default_value != ''): self.script_ddl += ' ' + default_value # valor padrão identity = item_colunm.get('identity') if (identity != ''): self.script_ddl += ' identity' + identity type_key = item_colunm.get('type_key') if (type_key != ''): if ('primary' in type_key): self.script_ddl += ' primary key' elif ('foreign' in type_key): foreign_table = item_colunm.get( 'foreign_table') foreign_field = item_colunm.get( 'foreign_field') has_foreign_key = (foreign_table != '' and foreign_field != '') if (has_foreign_key): self.script_ddl += ' foreign key references ' + foreign_table + '(' + foreign_field + ')' is_null = item_colunm.get('is_null') if ('no' in is_null): self.script_ddl += ' not null' elif ('yes' in is_null): self.script_ddl += ' null' if (count_line < len(item.get('list_of_fields'))): self.script_ddl += ', \n' else: self.script_ddl += ')\n\n ' if (self.script_ddl != ''): try: self.connection.connection.cursor().execute( self.script_ddl) self.connection.connection.cursor().commit() except Exception as e: try: self.connection.connection.cursor().rollback() except Exception as e: self.set_error('Problemas para executar o rollback. ', str(e)) self.set_error('Não foi possível criar tabelas no banco. ', str(e)) except Exception as e: self.set_error('Não foi possível criar tabelas no banco. ', str(e)) @lru_cache(maxsize=32, typed=False) def has_table(self, str_table=''): bool_has_table = False self.clear_error() try: if (self.connection.connection.cursor().tables( str_table).fetchone() != None): bool_has_table = True except Exception as e: self.set_error('Problemas com a conexão com o banco de dados. ', e) bool_has_table = True return bool_has_table def dictionary_database(self): self.clear_error() try: self.path = os.getcwd() + '\\database\\jsonfiles\\' if (os.path.exists(self.path)): self.file_table_json = open(self.path + 'tables.json') self.json = json.load(self.file_table_json) except Exception as e: self.set_error('Problema com o arquivo tables.json', e) def set_error(self, msg_error, e=Exception): self.has_error = True self.str_error = msg_error + str(e) def clear_error(self): self.has_error = False self.str_error = ''
print("Scanfiles.py : Time from file creation date to today in days = %s" % time_to_today.days) self.__rh.setFilename(fullPath) self.__rh.openFile() filecreationdate = self.__rh.getFileCreationDate() filepath = self.__rh.getFilePath() filename = self.__rh.getFileName() filesize = self.__rh.getFileSize() # Teste si le fichier existe deja dans la base de donnee # Si le fichier n'existe pas, l'inserer dans la base de donnees if self.__connectDB.selectDB(filecreationdate, name, root, filesize) == False: self.__connectDB.insertDB(filecreationdate, name, root, filesize) else: numberOfDoubles = self.__connectDB.findDoubles(name) print("Scanfiles.py : fichier existe deja dans la base de donnee...") print("Scanfiles.py : number of files", name,"present in database = ", numberOfDoubles) if __name__ == '__main__': databasename = "scanfiles.db" path = './sourcedir2/' debug = 2; # instanciation des classes ScanFiles, ReadHeader et ConnectDB scanfiles = ScanFiles(debug) readHeader = ReadHeader(debug) connectDB = ConnectDB(databasename, debug) scanfiles.setDatabaseName(databasename) scanfiles.processFile(path) numberOfFiles = connectDB.countFiles()
def __init__(self, db='Attr'): self.__con = ConnectDB.get_con(db) self.__encoding = ConnectDB.get_encoding(db) self.__cursor = self.__con.cursor()
def vm_management(): db_path = op.join(op.dirname(__file__), 'DB', 'VMManagement.db') print("DB PATH : ", db_path) opt = receive_input() db_obj = ConnectDB(db_path) conn_obj = db_obj.connect() db = DBOps(conn_obj) vm_manage_obj = VMManagement(conn_obj, db) while 0 <= opt < 7: if opt == 1: print("Current Pool of VM inventory ...") c = 1 for i in vm_manage_obj.get_repository_details(): print("%d) %s" %(c, i)) c += 1 opt = receive_input() elif opt == 2: """ Add VM to the inventory """ ip = input("Enter Ip address of the vm to be added : ") name = input("Enter the name of the vm : ") os = input("Enter the OS type : ") dt = datetime.datetime.today().strftime("%Y-%m-%d %H:%M:%S") vm_user = input("Enter VMs username : "******"Enter Vms password : "******""" Check in VM to the inventory """ if db.get_check_out_vm_data(): ip = input("Enter Ip address of the vm to be checked in : ") user = input("Enter the user name : ") vm_manage_obj.vm_check_in(ip, user) else: print("No VM for check out yet ...") opt = receive_input() elif opt == 4: """ Check out vm of the inventory """ if list(db.get_check_out_vm_data().keys()) == db.get_vm_pool_data(): print("All VMs are currently checked out. No VMs available") print("Please Try after some time ...") else: ip = input("Enter Ip address of the vm to be checked out : ") user = input("Enter the user name : ") vm_manage_obj.vm_check_out(ip, user) opt = receive_input() elif opt == 5: c = 1 if db.get_check_out_vm_data(): print("List of Checked out VMs are as follows : ") for k, v in db.get_check_out_vm_data().items(): print("%d) %s --> %s" %(c, k, v)) c += 1 else: print("No VM is currently being checked out!") opt = receive_input() elif opt == 6: print("List of available VMs : ") c = 1 for i in db.get_list_of_available_vms(): print("%d) %s" %(c, i)) c += 1 opt = receive_input() elif opt == 0: print("Exiting VM Management System ...") sys.exit() else: print("Incorrect Option entered ...") print("Please Try again ...")