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 ...")
