2023-11-24 01:25:20 +00:00
|
|
|
import os
|
2023-12-03 23:19:21 +00:00
|
|
|
import getpass
|
|
|
|
import mysql.connector
|
2023-11-24 01:25:20 +00:00
|
|
|
|
|
|
|
from table import Table
|
|
|
|
from sql import SQL
|
2023-11-30 23:15:30 +00:00
|
|
|
from mysql.connector import Error
|
2023-11-24 01:25:20 +00:00
|
|
|
|
|
|
|
def main():
|
2023-11-29 23:24:17 +00:00
|
|
|
print("Bem-vindo!")
|
2023-12-03 23:19:21 +00:00
|
|
|
print("Inicialmente, os dados precisam ser importados do MySQL")
|
|
|
|
print("Caso isso já tenha sido feito, os arquivos .csv serao sobreescritos")
|
|
|
|
print("Se algum update,insert ou delete tenha sido feito, ele sera perdido")
|
|
|
|
print("Eh recomendado importar as tabelas do bando de dados pelo menos no primeiro acesso")
|
|
|
|
print("Deseja importar os dados (digite BD) ou utilizar os arquivos.csv (digite CSV)?")
|
2023-11-29 23:24:17 +00:00
|
|
|
escolha = input()
|
2023-11-24 01:25:20 +00:00
|
|
|
|
2023-12-03 23:19:21 +00:00
|
|
|
tables = {}
|
|
|
|
|
2023-11-29 23:24:17 +00:00
|
|
|
if escolha == "CSV":
|
2023-12-03 01:44:33 +00:00
|
|
|
print("Espere os arquivos.csv carregarem...")
|
|
|
|
#print("Digite a pasta onde estão os arquivos CSV:")
|
2023-11-29 23:24:17 +00:00
|
|
|
#csv_folder = input()
|
|
|
|
csv_folder = "./source"
|
2023-11-24 01:25:20 +00:00
|
|
|
|
2023-11-29 23:24:17 +00:00
|
|
|
# Seleciona todos o csv presentes na pasta
|
|
|
|
for file in os.listdir(csv_folder):
|
|
|
|
if file.endswith(".csv"):
|
|
|
|
table_name = file.split(".")[0]
|
|
|
|
table = Table(table_name)
|
|
|
|
table.load_from_csv(f"{csv_folder}/{file}")
|
|
|
|
tables[table_name] = table
|
2023-11-24 01:25:20 +00:00
|
|
|
|
2023-12-03 23:19:21 +00:00
|
|
|
print("\nTabelas carregadas:")
|
|
|
|
print(', '.join(tables.keys())+"\n")
|
2023-11-24 01:25:20 +00:00
|
|
|
|
2023-11-29 23:24:17 +00:00
|
|
|
while True:
|
|
|
|
print("Digite a query [0 para sair]:")
|
|
|
|
query = input()
|
2023-11-24 01:25:20 +00:00
|
|
|
|
2023-11-29 23:24:17 +00:00
|
|
|
if query == "0":
|
|
|
|
break
|
2023-11-24 01:25:20 +00:00
|
|
|
|
2023-11-29 23:24:17 +00:00
|
|
|
sql = SQL(tables, query)
|
|
|
|
sql.execute()
|
|
|
|
|
|
|
|
print()
|
|
|
|
|
|
|
|
elif escolha == "BD":
|
2023-12-03 23:19:21 +00:00
|
|
|
|
|
|
|
print("Host (localhost):")
|
|
|
|
host_name = input()
|
|
|
|
|
|
|
|
print("Database (employees):")
|
|
|
|
database_name = input()
|
|
|
|
|
|
|
|
print("User:")
|
|
|
|
user_name = input()
|
|
|
|
|
|
|
|
password_name = getpass.getpass()
|
|
|
|
|
2023-11-30 23:15:30 +00:00
|
|
|
try:
|
2023-12-01 01:05:39 +00:00
|
|
|
|
2023-12-03 23:19:21 +00:00
|
|
|
connection = mysql.connector.connect(host=host_name,
|
|
|
|
database=database_name,
|
|
|
|
user=user_name,
|
|
|
|
password=password_name)
|
2023-11-30 23:15:30 +00:00
|
|
|
|
|
|
|
if connection.is_connected():
|
|
|
|
print("Conectado ao employees-db")
|
|
|
|
cursor = connection.cursor()
|
|
|
|
|
2023-12-01 01:05:39 +00:00
|
|
|
cursor.execute("SHOW TABLES")
|
|
|
|
tables_names = cursor.fetchall()
|
|
|
|
|
|
|
|
for table_name in tables_names:
|
|
|
|
# table_name = ('departments', )
|
|
|
|
table_name = table_name[0]
|
|
|
|
# table_name = 'departments'
|
|
|
|
|
|
|
|
print('Carregando', table_name)
|
|
|
|
tables[table_name] = Table(table_name)
|
|
|
|
tables[table_name].load_from_mysql(table_name,cursor)
|
2023-12-03 01:44:33 +00:00
|
|
|
tables[table_name].save_to_csv()
|
2023-12-01 01:05:39 +00:00
|
|
|
print('Carregado', table_name)
|
|
|
|
|
2023-11-30 23:15:30 +00:00
|
|
|
except Error as e:
|
|
|
|
print("Error while connecting to MySQL", e)
|
|
|
|
finally:
|
|
|
|
if connection.is_connected():
|
|
|
|
cursor.close()
|
|
|
|
connection.close()
|
|
|
|
print("MySQL connection is closed")
|
|
|
|
|
2023-12-03 23:19:21 +00:00
|
|
|
print("Espere os arquivos.csv carregarem...")
|
|
|
|
#print("Digite a pasta onde estão os arquivos CSV:")
|
|
|
|
#csv_folder = input()
|
|
|
|
csv_folder = "./source"
|
|
|
|
|
|
|
|
tables = {}
|
|
|
|
|
|
|
|
# Seleciona todos o csv presentes na pasta
|
|
|
|
for file in os.listdir(csv_folder):
|
|
|
|
if file.endswith(".csv"):
|
|
|
|
table_name = file.split(".")[0]
|
|
|
|
table = Table(table_name)
|
|
|
|
table.load_from_csv(f"{csv_folder}/{file}")
|
|
|
|
tables[table_name] = table
|
|
|
|
|
|
|
|
print("\nTabelas carregadas:")
|
|
|
|
print(', '.join(tables.keys())+"\n")
|
|
|
|
|
|
|
|
while True:
|
|
|
|
print("Digite a query [0 para sair]:")
|
|
|
|
query = input()
|
|
|
|
|
|
|
|
if query == "0":
|
|
|
|
break
|
|
|
|
|
|
|
|
sql = SQL(tables, query)
|
|
|
|
sql.execute()
|
|
|
|
|
|
|
|
print()
|
|
|
|
|
2023-11-29 23:24:17 +00:00
|
|
|
else:
|
|
|
|
print("Fora do intervalo!")
|
2023-11-24 01:38:46 +00:00
|
|
|
|
2023-11-24 01:25:20 +00:00
|
|
|
if __name__ == "__main__":
|
|
|
|
main()
|