from os import getcwd as pwd import tkinter as tk import sqlite3 def fin_05(): """A small art gallery is selling works from different artists and wants to keep track of the paintings using an SQL database. You need to create a user-friendly system to keep track of the art. This should include using a GUI. Below is the current data that needs to be stored in a database. Artists Contact Details: ArtistID Name Address Town Country Postcode ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 1 Martin Leighton 5 Park Place Peterborough Cambridgeshire PE32 5LP 2 Eva Czariecka 77 Warner Close Chelmsford Essex CM22 5FT 3 Roxy Parkin 90 Hindhead Road London SE12 6WM 4 Nigel Farnworth 41 Whitby Road Huntly Aberdeenshire AB54 5PN 5 Teresa Tanner 70 Guild Street London NW7 1SP PieceID ArtistID Title Medium Price ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 1 5 Woman with black Labrador Oil 220 2 5 Bees & thistles Watercolour 85 3 2 A stroll to Westminster Ink 190 4 1 African giant Oil 800 5 3 Water daemon Acrylic 1700 6 4 A seagull Watercolour 35 7 1 Three friends Oil 1800 8 2 Summer breeze 1 Acrylic 1350 9 4 Mr Hamster Watercolour 35 10 1 Pulpit Rock, Dorset Oil 600 11 5 Trawler Dungeness beach Oil 195 12 2 Dance in the snow Oil 250 13 4 St Tropez port Ink 45 14 3 Pirate assassin Acrylic 420 15 1 Morning walk Oil 800 16 4 A baby barn swallow Watercolour 35 17 4 The old working mills Ink 395 The art gallery must be able to add new artists and pieces of art. Once a piece of art has been sold, the data about that art should be removed from the main SQL database and stored in a separate text file. Users should be able to search by artist, medium or price.""" db_path = f"{pwd()}/final/files/artgallery.db" file_path = f"{pwd()}/final/files/art_sales.txt" def exec_query(query, params=None): with sqlite3.connect(db_path) as db: cursor = db.cursor() if params is not None: result = cursor.execute(query, params) else: result = cursor.execute(query) db.commit() return result def exec_queries(query, params): with sqlite3.connect(db_path) as db: cursor = db.cursor() result = cursor.executemany(query, params) db.commit() return result def create_tables(): query="""CREATE TABLE IF NOT EXISTS artists( id INTEGER PRIMARY KEY, name TEXT NOT NULL, address TEXT NOT NULL, town TEXT, country TEXT NOT NULL, postcode TEXT NOT NULL)""" exec_query(query) query="""CREATE TABLE IF NOT EXISTS pieces( id INTEGER PRIMARY KEY, artistid INTEGER, title TEXT NOT NULL, medium TEXT NOT NULL, price FLOAT, FOREIGN KEY (artistid) REFERENCES artists(id))""" exec_query(query) def populate_tables(): query = """INSERT INTO artists(name, address, town, country, postcode) VALUES(?,?,?,?,?)""" artists = [ ('Martin Leighton', '5 Park Place', 'Peterborough', 'Cambridgeshire', 'PE32 5LP'), ('Eva Czariecka', '77 Warner Close', 'Chelmsford', 'Essex', 'CM22 5FT'), ('Roxy Parkin', '90 Hindhead Road', None, 'London', 'SE12 6WM'), ('Nigel Farnworth', '41 Whitby Road', 'Huntly', 'Aberdeenshire', 'AB54 5PN'), ('Teresa Tanner', '70 Guild Street', None ,'London', 'NW7 1SP') ] exec_queries(query, artists) query = "INSERT INTO pieces(artistid, title, medium, price) VALUES(?,?,?,?)" pieces = [ ('5', 'Woman with black Labrador', 'Oil', 220), ('5', 'Bees & thistles', 'Watercolour', 85), ('2', 'A stroll to Westminster', 'Ink' , 190), ('1', 'African giant', 'Oil', 800), ('3', 'Water daemon', 'Acrylic', 1700), ('4', 'A seagull', 'Watercolour', 35), ('1', 'Three friends', 'Oil', 1800), ('2', 'Summer breeze 1', 'Acrylic', 1350), ('4', 'Mr Hamster', 'Watercolour', 35), ('1', 'Pulpit Rock, Dorset', 'Oil', 600), ('5', 'Trawler Dungeness beach', 'Oil', 195), ('2', 'Dance in the snow', 'Oil', 250), ('4', 'St Tropez port', 'Ink', 45), ('3', 'Pirate assassin', 'Acrylic', 420), ('1', 'Morning walk', 'Oil', 800), ('4', 'A baby barn swallow', 'Watercolour', 35), ('4', 'The old working mills', 'Ink', 395) ] exec_queries(query, pieces) def drop_tables(): query = "DROP TABLE IF EXISTS pieces" exec_query(query) query = "DROP TABLE IF EXISTS artists" exec_query(query) def add_artist(artist): query = """INSERT INTO artists (name, address, town, country, postcode) VALUES(?,?,?,?,?)""" exec_query(query, artist) def add_piece(piece): query = """INSERT INTO pieces (artistid, title, medium, price) VALUES(?,?,?,?)""" exec_query(query, piece) def del_piece(piece_id): query = "DELETE FROM pieces WHERE id=?" exec_query(query, [piece_id]) def sell_piece(): piece = lst_out.get(tk.ANCHOR) try: piece_id = piece.split()[0] query = "SELECT * FROM pieces WHERE id=?" result = exec_query(query, [piece_id]) data = result.fetchone() with open(file_path, 'a+') as file: file.write(f"{data[0]}, {data[1]}, {data[2]}, {data[3]}, {data[4]}\n") del_piece(piece_id) clean_tk() except IndexError: return def search_by_id(artistid): query = f"SELECT * FROM pieces WHERE artistid = ?" result = exec_query(query, str(artistid)) data = result.fetchall() return data def search_by_medium(medium): query = f"SELECT * FROM pieces WHERE medium LIKE ?" result = exec_query(query, ['%'+medium+'%']) data = result.fetchall() return data def search_by_price(price): query = f"SELECT * FROM pieces WHERE price <= ?" result = exec_query(query, [price]) data = result.fetchall() return data def restart_data(): drop_tables() create_tables() populate_tables() with open(file_path, 'w') as file: file.write('') #add_artist(("BOB", "Mi casa", None, "Taiwan", "06660")) #add_piece((6, "Su Casa", "Bic sobre cuaderno", 6666.666)) #sell_piece(18) restart_data() com_bg = "dodger blue" alt_bg = "deep sky blue" window = tk.Tk() window.title("Galeria de Arte") window.geometry("800x600") window["bg"] = com_bg lbl_artist_id = tk.Label(text="Id Artista", font="Verdana 12") lbl_artist_id.place(x=40, y=230, width=80, height=25) lbl_artist_id["bg"] = com_bg txt_artist_id = tk.Entry(font="Verdana 12") txt_artist_id["justify"] = "center" txt_artist_id.place(x=120, y=230, width=40, height=25) lbl_piece_title = tk.Label(text="Titulo", font="Verdana 12") lbl_piece_title.place(x=40, y=270, width=60, height=25) lbl_piece_title['bg'] = com_bg txt_piece_title = tk.Entry(font="Verdana 12") txt_piece_title.place(x=100, y=270, width=400, height=25) def add_art_piece(): artist_id = txt_artist_id.get() if artist_id == '': txt_artist_id.focus() return title = txt_piece_title.get() if title == '': txt_piece_title.focus() return medium = txt_medium.get() if medium == '': txt_medium.focus() return price = txt_price.get() if price == '' or not price.isdigit(): txt_price.delete(0, 'end') txt_price.focus() return clean_tk() add_piece((artist_id,title,medium,price)) btn_add_piece = tk.Button(text="Agregar Pieza", font="Verdana 12", command=add_art_piece) btn_add_piece['bg'] = alt_bg btn_add_piece.place(x=510, y=270, width=250, height=25) def display_artists(): query = "SELECT * FROM artists" data = exec_query(query) lst_out.delete(0, 'end') for art in data: artist_id = (str(art[0]).rjust(3)).ljust(5) name = (art[1].rjust(15)).ljust(20) address = art[2].rjust(20) town = art[3].rjust(20) if art[3] is not None else " " country = art[4].rjust(20) postcode = art[5].rjust(20) line = artist_id+name+address+town+country+postcode lst_out.insert('end', line) def display_pieces(data): lst_out.delete(0, 'end') for art in data: piece_id = (str(art[0]).rjust(5)).ljust(8) artist_id = (str(art[1]).rjust(5)).ljust(8) title = art[2].rjust(40) medium = art[3].rjust(20) price = str(art[4]).rjust(20) line = piece_id+artist_id+title+medium+price lst_out.insert('end', line) def search(): search_by = sel_by.get() lst_out.delete(0, 'end') match search_by: case 'ID Artista': artistid = txt_artist_id.get() if artistid.isdigit(): data = search_by_id(artistid) display_pieces(data) case 'Material': medium = txt_medium.get() data = search_by_medium(medium) display_pieces(data) case 'Precio': price = txt_price.get() data = search_by_price(price) display_pieces(data) case _: lst_by.focus() btn_find = tk.Button(text="Buscar por", font="Verdana 12", command=search) btn_find.place(x=510, y=230, width=135, height=25) btn_find['bg'] = alt_bg sel_by = tk.StringVar(window) sel_by.set("------------") by_ops = [ 'ID Artista', 'Material', 'Precio' ] lst_by = tk.OptionMenu(window, sel_by, *by_ops) lst_by.place(x=645, y=230, width=115, height=25) lst_by['bg'] = alt_bg lbl_medium = tk.Label(text="Material", font="Verdana 12") lbl_medium['bg'] = com_bg lbl_medium.place(x=160, y=230, width=80, height=25) txt_medium = tk.Entry(font="Verdana 12") txt_medium["justify"] = "center" txt_medium.place(x=240, y=230, width=120, height=25) lbl_price = tk.Label(text="Precio <", font="Verdana 12") lbl_price['bg'] = com_bg lbl_price.place(x=370, y=230, width=70, height=25) txt_price = tk.Entry(font="Verdana 12") txt_price["justify"] = "center" txt_price.place(x=440, y=230, width=60, height=25) btn_showall = tk.Button(text="Mostrar todos los artistas", font="Verdana 12", command=display_artists) btn_showall['bg'] = alt_bg btn_showall.place(x=510, y=190, width=250, height=25) lst_out = tk.Listbox(font="Verdana 12") lst_out["justify"] = "left" lst_out["bg"] = "tomato" lst_out.place(x=40, y=310, width=720, height=280) def new_artist(): name = txt_in_name.get() if name == '': txt_in_name.focus() return addrs = txt_in_adrs.get() if addrs == '': txt_in_adrs.focus() return town = txt_in_town.get() #opt country = txt_in_country.get() if country == '': txt_in_country.focus() return postal = txt_in_code.get() if country == '': txt_in_code.focus() return new_artist = (name, addrs, town, country, postal) add_artist(new_artist) clean_tk() lbl_title = tk.Label(text="Gestion de Artistas y Obras") lbl_title['bg'] = com_bg lbl_title['font'] = "Verdana 30" lbl_title.place(x=120, y=10, width=550, height=80) y1 = 100 y2 = y1+(y1/2) x1 = 40 lbl_art_name = tk.Label(text="Nombre", font="Verdana 12") lbl_art_name.place(x=x1, y=y1, width=70, height=25) lbl_art_name['bg'] = com_bg txt_in_name = tk.Entry(font="Verdana 12") txt_in_name.place(x=x1+70, y=y1, width=220, height=25) lbl_art_adrs = tk.Label(text="Dirección", font="Verdana 12") lbl_art_adrs['bg'] = com_bg lbl_art_adrs.place(x=340, y=y1, width=70, height=25) txt_in_adrs = tk.Entry(font="Verdana 12") txt_in_adrs.place(x=420, y=y1, width=340, height=25) lbl_art_town = tk.Label(text="Ciudad", font="Verdana 12") lbl_art_town['bg'] = com_bg lbl_art_town.place(x=x1, y=y2, width=80, height=25) txt_in_town = tk.Entry(font="Verdana 12") txt_in_town.place(x=x1+70, y=y2, width=180, height=25) lbl_art_country = tk.Label(text="Región", font="Verdana 12") lbl_art_country['bg'] = com_bg lbl_art_country.place(x=x1+260, y=y2, width=70, height=25) txt_in_country = tk.Entry(font="Verdana 12") txt_in_country.place(x=x1+330, y=y2, width=160, height=25) lbl_art_code = tk.Label(text="Cod. Postal", font="Verdana 12") lbl_art_code['bg'] = com_bg lbl_art_code.place(x=540, y=y2, width=90, height=25) txt_in_code = tk.Entry(font="Verdana 12") txt_in_code.place(x=630, y=y2, width=130, height=25) btn_add_art = tk.Button(text="Agregar artista", font="Verdana 12", command=new_artist) btn_add_art["bg"] = alt_bg btn_add_art.place(x=x1, y=190, width=250, height=25) btn_del_art = tk.Button(text="Vender pieza", font="Verdana 12", command=sell_piece) btn_del_art["bg"] = alt_bg btn_del_art.place(x=280, y=190, width=250, height=25) def clean_tk(): txt_in_name.delete(0,'end') txt_in_adrs.delete(0,'end') txt_in_town.delete(0,'end') txt_in_country.delete(0,'end') txt_in_code.delete(0,'end') lst_out.delete(0, 'end') txt_artist_id.delete(0,'end') txt_piece_title.delete(0,'end') txt_medium.delete(0,'end') txt_price.delete(0,'end') window.mainloop()