-
Notifications
You must be signed in to change notification settings - Fork 0
/
connection.py
125 lines (112 loc) · 3.89 KB
/
connection.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
import sqlite3
import os
from dotenv import load_dotenv
from utils.encrypter import encriptedPassword, load_key
# Aqui adjunta tus credenciales de administrador
load_dotenv()
DEFAULT_ADMIN_USERNAME = "default_username"
DEFAULT_ADMIN_EMAIL = "default@email.com"
DEFAULT_ADMIN_PASS = "default_password"
ADMIN_USERNAME = os.getenv("ADMIN_USERNAME", DEFAULT_ADMIN_USERNAME)
ADMIN_EMAIL = os.getenv("ADMIN_EMAIL", DEFAULT_ADMIN_EMAIL)
ADMIN_PASS = os.getenv("ADMIN_PASS", DEFAULT_ADMIN_PASS)
key = load_key()
encriptAdminPass = encriptedPassword(ADMIN_PASS, key)
class ConnectionDB:
def __init__(self):
try:
self.con = sqlite3.connect("users.db")
except Exception as error:
print(error)
def init_db(self): # Nuevo método para inicializar la base de datos
self.createTable()
self.createClientsTable()
self.createBudgetsTable()
# Crea la tabla de la DB si no existe
def createTable(self):
sql_create_table = """
CREATE TABLE IF NOT EXISTS users(
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT,
email TEXT,
password TEXT
)
"""
cur = self.con.cursor()
cur.execute(sql_create_table)
cur.close()
self.createAdmin() # Crea el usuario adminnistrador
# Crea la tabla de clientes
def createClientsTable(self):
sql_create_clients_table = """
CREATE TABLE IF NOT EXISTS clients(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
address TEXT,
email TEXT,
city TEXT,
zip_code TEXT,
phone TEXT,
user_id INTEGER,
FOREIGN KEY(user_id) REFERENCES users(id)
)
"""
try:
cur = self.con.cursor()
cur.execute(sql_create_clients_table)
self.con.commit()
except sqlite3.Error as e:
print(f"An error occurred: {e.args[0]}")
finally:
cur.close()
# Crea la tabla de budgets ne la DB si no existe
def createBudgetsTable(self):
sql_create_budgets_table = """
CREATE TABLE IF NOT EXISTS budgets(
id INTEGER PRIMARY KEY AUTOINCREMENT,
budgetId TEXT,
budgetName TEXT,
budgetAmountSubtotal INTEGER,
budgetAmountTaxes INTEGER,
budgetAmountTotal INTEGER,
budgetDate TEXT, -- ISO8601 formatted date as TEXT,
budgetDescription TEXT,
budgetStatus TEXT,
budgetType TEXT,
budgetCategory TEXT,
budgetNotes TEXT,
client_id INTEGER,
FOREIGN KEY(client_id) REFERENCES clients(id)
)
"""
try:
cur = self.con.cursor()
cur.execute(sql_create_budgets_table)
self.con.commit()
except sqlite3.Error as e:
print(f"An error occurred: {e.args[0]}")
finally:
cur.close()
# Crea el usuario administrador si ya existe lanza excepcion
def createAdmin(self):
cur = self.con.cursor()
# Verifica si el usuario administrador ya existe
cur.execute("SELECT * FROM users WHERE username = ?", (ADMIN_USERNAME,))
if cur.fetchone():
print(f"El usuario administrador {ADMIN_USERNAME} ya existe")
cur.close()
return
try:
sql_insert = """
INSERT INTO users(username, email, password) values(?, ?, ?)
"""
cur.execute(
sql_insert, (ADMIN_USERNAME, ADMIN_EMAIL, encriptAdminPass)
) # Pasa los valores aquí
self.con.commit()
print("Usuario administrador creado")
cur.close()
except Exception as error:
print(f"El usuario {error} ya existe")
def connect(self):
return self.con