def update(self):
# Let's get the names in the operaciones table.
with DatabaseConnection(self.host) as connection:
cursor = connection.cursor()
cursor.execute("SELECT name FROM operaciones")
names = {e[0]
for e in cursor.fetchall()
} # {'NON', 'REPEATED', 'NAMES'}
cursor.execute("DELETE FROM saldos")
# Search for the total for each person and add it to the database.
for name in names:
try:
cursor.execute(
"SELECT amount FROM operaciones WHERE name=?",
(name, ))
amounts = [e[0] for e in cursor.fetchall()]
total = sum(amounts)
cursor.execute("INSERT INTO saldos VALUES(?, ?)",
(name, total))
except sqlite3.OperationalError:
logger.critical(
"For some reason, a 'sqlite3.OperationalError' was raised."
)
# Update and erase totals equal to zero
with DatabaseConnection(self.host) as connection:
cursor = connection.cursor()
cursor.execute("DELETE FROM saldos WHERE amount=0")
def get_all_blog_posts():
with DatabaseConnection('data.db') as connection:
cursor = connection.cursor()
cursor.execute('SELECT * FROM blog_posts')
all_blog_posts = cursor.fetchall()
return all_blog_posts
def delete_book(name: str, author: str) -> None:
with DatabaseConnection(database) as (connection, cursor):
rows_affected = cursor.execute(
f'DELETE FROM books WHERE name=? and author=?', (name, author))
if rows_affected.rowcount == 0:
raise BookNotFoundException(name, author)
def get_book(name: str, author: str) -> Book:
with DatabaseConnection(database) as (connection, cursor):
cursor.execute(
f'SELECT name, author, read FROM books WHERE name=? and author=?',
(name, author))
row = cursor.fetchone()
return {'name': row[0], 'author': row[1], 'is_read': row[2]}
def insert_data(data):
with DatabaseConnection() as connection:
data.to_sql(
name='AdjustmentDetails', con=connection, if_exists='replace'
) # Value can also be append, but have used 'replace' since it is the same file
rows = len(data.index)
print("Insert successful for {} rows".format(rows))
def verify_table():
with DatabaseConnection() as connection:
cursor = connection.cursor()
cursor.execute("SELECT * FROM 'AdjustmentDetails'")
rows = cursor.fetchall()
for row in rows:
print(row)
def insert_blog_post(title: str, content: str) -> None:
with DatabaseConnection('data.db') as connection:
cursor = connection.cursor()
cursor.execute('INSERT INTO blog_posts (title, content) VALUES (?, ?)', (title, content))
post_id = cursor.lastrowid
return post_id
def history(self, name: str) -> Tuple:
"""
Returns a tuple where each element consits of a list of tuples and a balance. This list of tuples has, in this order,
the following string objects:
-> 'debt' or 'payment' corresponding to the operation
-> the amount (formatted as a string to only show two decimal places)
-> the operation's date
-> at what time of this date the operation was loaded into the system.
If the list is empty, then, there are no operations for such individual.
The 'balance' (the second element of the returned tuple) is either a float or a False statement. If it were a string, contains
the 'total balance'. It is a False statement when the total is zero.
"""
with DatabaseConnection(self.host) as connection:
cursor = connection.cursor()
cursor.execute(
"SELECT amount, date FROM operaciones WHERE name=? ORDER BY date DESC",
(name, ))
results = cursor.fetchall()
balance = self.get_individual_balance(name)
operations = []
if results:
for (amount, date) in results:
if amount != 0:
operation = 'debt' if amount < 0 else 'payment'
date = float(date)
timestamp = datetime.fromtimestamp(date)
date_to_show = timestamp.strftime('%d/%m/%Y')
time_to_show = timestamp.strftime('%H:%M')
amount_string = "%.2f" % abs(amount)
operations.append((operation, amount_string,
date_to_show, time_to_show))
return (operations, balance)
def update_master(self, password: str):
enc = Encrypter(password)
with DatabaseConnection(self.host) as connection:
cursor = connection.cursor()
cursor.execute('UPDATE passwords SET pass=? WHERE name=?',
(enc.encrypted, self.account))
def __init__(self, host: str):
self.host = host
logger.debug(
f"New log file created and 'Database' object referred to {self.host}."
)
try:
logger.debug(f"Checking if {self.host} exists")
with open(self.host, 'r'):
logger.debug(f"{self.host} exists")
except FileNotFoundError:
with open(self.host, 'w'):
logger.error(f"{self.host} created, since it did't exist.")
finally:
with DatabaseConnection(self.host) as connection:
cursor = connection.cursor()
cursor.execute(
"CREATE TABLE IF NOT EXISTS operaciones(name TEXT, amount FLOAT, date FLOAT)"
)
cursor.execute(
"CREATE TABLE IF NOT EXISTS saldos(name TEXT UNIQUE primary key, amount FLOAT)"
)
cursor.execute(
"CREATE TABLE IF NOT EXISTS usuarios(name TEXT UNIQUE primary key, password TEXT)"
)
cursor.execute("INSERT OR IGNORE INTO usuarios VALUES(?, ?)",
(ADMIN[0], ADMIN[1]))
def get_all_furniture() -> List[Furniture]:
with DatabaseConnection('furniture_data.db') as connection:
cursor = connection.cursor()
cursor.execute('SELECT * FROM furniture')
furniture = cursor.fetchall()
return furniture
def get_all_books() -> List[Book]:
with DatabaseConnection('data.db') as connection:
cursor = connection.cursor()
cursor.execute('SELECT * FROM books')
books = cursor.fetchall()
return books
def list_movies(self, store_type="f"):
"""
Function created to delete registers of movies from a json file or from database
:param store_type: store_type: db (database) or f (file). By default it considers db (database).
:return: True of False
"""
if store_type.lower() == "f":
file = file_operations.read_file("Movie_Registration.json")
result_list = self._create_dataframe_list(file)
else:
with DatabaseConnection(self.path_db) as cursor:
cursor.execute("select * from tb_movies")
movies = cursor.fetchall()
# creates the same structure that the json file has and create the dataframe
list_movies = []
for row in movies:
row_dict = {}
row_dict["movie_name"] = row[0]
row_dict["director_name"] = row[1]
row_dict["movie_year"] = row[2]
row_dict["name_main_actor_actress"] = row[3]
row_dict["read"] = row[4]
list_movies.append(row_dict)
result_list = self._create_dataframe_list(list_movies)
return result_list
def _add_movie_db(self, content) -> bool:
"""
Private function created to insert new movies into a Movie's table inside a database
:param content: dictionary with all movies' information.
:return: True of False
"""
movie_name = content["movie_name"].upper()
director_name = content["director_name"].upper()
year = content["movie_year"]
main_actor = content["name_main_actor_actress"].upper()
read = content["read"]
with DatabaseConnection(self.path_db) as cursor:
try:
cursor.execute("Create table if not exists tb_movies("
" movie_name varchar(100)"
", director_name varchar(100)"
", year int"
", main_actor varchar(100)"
", read boolean)")
cursor.execute("insert into tb_movies (movie_name, director_name, year, main_actor, read)"
"values (?, ?, ?, ?, ?)",
(movie_name, director_name, year, main_actor, read))
return True
except ValueError as e_value:
print(f"\nDefinition of an object is not set properly. {str(e_value)}")
return False
except Exception as e:
print(f"\nOccurred an error during the adding operation. Error description: {str(e)}")
return False
def create_table() -> None:
"""Creates books table in the database."""
with DatabaseConnection('data.sqlite') as connection:
cursor = connection.cursor()
cursor.execute(
'CREATE TABLE IF NOT EXISTS books ( name TEXT PRIMARY KEY, author TEXT, read INTEGER )'
)
def insert_book(name: str, author: str) -> None:
with DatabaseConnection('data.db') as conn:
cursor = conn.cursor()
try:
cursor.execute('INSERT INTO books VALUES(?,?,0)', (name, author))
except sqlite3.IntegrityError as e:
print("book already exists")
def total_maintenance(self) -> True:
"""
The total maintenance constists of erasing all the operations for all individuals, leaving only one, where
we synthesize the debt/payment for that person at the moment.
"""
logger.debug(
"Performing total maintenance. First, we do a parcial one.")
self.parcial_maintenance()
with DatabaseConnection(self.host) as connection:
cursor = connection.cursor()
logger.debug(
"Getting the actual account state for each individual from 'saldos'."
)
cursor.execute("SELECT * FROM saldos")
results = cursor.fetchall()
for (name, amount) in results:
logger.debug(f"Erasing all of {name.title()}'s operations.")
cursor.execute("DELETE FROM operaciones WHERE name=?",
(name, ))
logger.debug(
f"Adding a operation to synthesize the state of the account."
)
cursor.execute("INSERT INTO operaciones VALUES(?, ?, ?)",
(name, amount, time.time()))
logger.debug("Total maintenance performed correctly.")
return True
def create_book_table():
with DatabaseConnection("data.db") as connection:
# connection=sqlite3.connect("data.db")
cursor = connection.cursor()
cursor.execute(
"CREATE TABLE IF NOT EXISTS books(name text,author text,read integer)"
)
def start_database() -> None:
with DatabaseConnection() as connection:
cursor = connection.cursor()
cursor.execute(
'CREATE TABLE IF NOT EXISTS book(name text primary key, author text, read integer)'
)
def create_table():
with DatabaseConnection("Passwords.db") as connection:
cursor = connection.cursor()
cursor.execute(
'CREATE TABLE IF NOT EXISTS passwords(name text primary key, pass text)'
)
def get_clients(self) -> List:
"""
Returns a list where each element is a client's name. These names are gathered
from the 'operaciones' table. This means that any client who payed or contracted
a debt before is going to figure here.
I pictured this would be useful when the prompt for 'adding a debt' (or payment)
was raised, since I want the user to be able to 'select' a client's name or 'insert
a new one' if it's needed.
"""
with DatabaseConnection(self.host) as connection:
cursor = connection.cursor()
logger.debug("Geting the names in the 'operaciones' table.")
cursor.execute("SELECT name FROM operaciones ORDER BY name")
results = cursor.fetchall()
if results:
logger.debug("Clients found. Saving them into a set")
clients = []
for e in results:
if e[0] not in clients:
clients.append(e[0])
logger.debug("Returning these clients as a list")
return clients
logger.debug("There are no clients yet!")
return []
def add_payment(self, name: str, amount: float) -> None:
"""
This method deals with adding a debt to the database.
:param name: client's name
:param amount: amount the client payed.
:return:
"""
logger.debug("Try to get today's date and add the payment.")
try:
date = time.time()
amount = abs(amount)
with DatabaseConnection(self.host) as connection:
cursor = connection.cursor()
cursor.execute("INSERT INTO operaciones VALUES(?, ?, ?)",
(name.lower(), amount, date))
logger.debug("Updating database.")
self.update()
except sqlite3.OperationalError:
logger.critical(
"For some reason, a 'sqlite3.OperationalError' was raised.")
raise
else:
print(
f"El pago de {name.title()} por $%.2f fue agregado correctamente."
% amount)
def create_furniture_table(self) -> None:
with DatabaseConnection('furniture_data.db') as connection:
cursor = connection.cursor()
# SQLite automatically makes `integer primary key` row auto-incrementing (see link in further reading)
cursor.execute(
'CREATE TABLE IF NOT EXISTS furniture(id integer primary key, name text, size text, price integer, image text, link text, code text, read integer default 0)'
)
def mark_as_read(name: str, author: str) -> None:
with DatabaseConnection(database) as (connection, cursor):
rows_affected = cursor.execute(
f'UPDATE books SET read = 1 WHERE name=? and author=?',
(name, author))
if rows_affected.rowcount == 0:
raise BookNotFoundException(name, author)
def create_book_table() -> None:
with DatabaseConnection('data.db') as connection:
cursor = connection.cursor()
# SQLite automatically makes `integer primary key` row auto-incrementing (see link in further reading)
cursor.execute(
'CREATE TABLE books (id integer primary key, name text, author text, read integer default 0)'
)
def insert_furniture(self, dataframe) -> None:
with DatabaseConnection('furniture_data.db') as connection:
cursor = connection.cursor()
cursor.execute(
'INSERT INTO furniture (name, size, price, image, link, code) VALUES (?, ?, ?, ?, ?, ?)',
(dataframe["Product_Name"], dataframe["Product_Size"],
dataframe["Product_Prize"], dataframe["Product_Image"],
dataframe["Product_Link"], dataframe["Product_ID"]))
def add_book(book):
name = book["name"]
author = book["author"]
read = book["read"]
with DatabaseConnection("data.db") as connection:
connection.cursor().execute("INSERT INTO books VALUES(?, ?, ?)",
(name, author, read))
def check_login(self, username: str, password: str):
with DatabaseConnection(self.host) as connection:
cursor = connection.cursor()
cursor.execute("SELECT * FROM usuarios")
result = cursor.fetchone()
if str(result[0]).lower() == username and result[1] == password:
return True
else:
return False
def get_all_books():
with DatabaseConnection() as connection:
records = connection.cursor().execute(
"SELECT * FROM `books`").fetchall()
return [{
'name': name,
'author': author,
'read': bool(int(read))
} for name, author, read in records]
def remove_book(name: str) -> None:
"""Remove book from the database.
:parameter name: name of the book
"""
# TODO: send a message to the user if the book to delete is not exists.
with DatabaseConnection('data.sqlite') as connection:
cursor = connection.cursor()
cursor.execute('DELETE FROM books WHERE name = ?', (name, ))