Closed ssweber closed 1 year ago
Wow! What's even funnier is that I have been thinking about the same exact thing (creating something similar to a datasheet view in access) and supporting text fields, combo boxes and checkboxes right in the table. This is definitely something we need to add and make simple for the end user to accomplish. Great implementation on your end, that's an awesome demo
On Thu, Apr 13, 2023, 3:12 PM ssweber @.***> wrote:
Playing around with PySimpleGui editable-table example https://github.com/PySimpleGUI/PySimpleGUI/issues/4972. Basically it creates an input box, and sets it in front of the table cell.
Got it somewhat working with force-save, and disabling the threaded info close. It would require some thinking to decide the exact behavior desired and figure out how to accomplish, and we'd need to change the info-popup to use asyncio.
sql = """DROP TABLE IF EXISTS Customers;CREATE TABLE Customers( CustomerID INTEGER PRIMARY KEY AUTOINCREMENT, CustomerName TEXT, ContactName TEXT, Address TEXT, City TEXT, PostalCode TEXT, Country TEXT);INSERT INTO Customers VALUES(1,'Alfreds Futterkiste','Maria Anders','Obere Str. 57','Berlin','12209','Germany');INSERT INTO Customers VALUES(2,'Ana Trujillo Emparedados y helados','Ana Trujillo','Avda. de la Constitución 2222','México D.F.','5021','Mexico');INSERT INTO Customers VALUES(3,'Antonio Moreno Taquería','Antonio Moreno','Mataderos 2312','México D.F.','5023','Mexico');INSERT INTO Customers VALUES(4,'Around the Horn','Thomas Hardy','120 Hanover Sq.','London','WA1 1DP','UK');INSERT INTO Customers VALUES(5,'Berglunds snabbköp','Christina Berglund','Berguvsvägen 8','Luleå','S-958 22','Sweden');INSERT INTO Customers VALUES(6,'Blauer See Delikatessen','Hanna Moos','Forsterstr. 57','Mannheim','68306','Germany');INSERT INTO Customers VALUES(7,'Blondel père et fils','Frédérique Citeaux','24, place Kléber','Strasbourg','67000','France');INSERT INTO Customers VALUES(8,'Bólido Comidas preparadas','Martín Sommer','C/ Araquil, 67','Madrid','28023','Spain');INSERT INTO Customers VALUES(9,'Bon app''''','Laurence Lebihans','12, rue des Bouchers','Marseille','13008','France');INSERT INTO Customers VALUES(10,'Bottom-Dollar Marketse','Elizabeth Lincoln','23 Tsawassen Blvd.','Tsawassen','T2F 8M4','Canada');INSERT INTO Customers VALUES(11,'B''''s Beverages','Victoria Ashworth','Fauntleroy Circus','London','EC2 5NT','UK');INSERT INTO Customers VALUES(12,'Cactus Comidas para llevar','Patricio Simpson','Cerrito 333','Buenos Aires','1010','Argentina');INSERT INTO Customers VALUES(13,'Centro comercial Moctezuma','Francisco Chang','Sierras de Granada 9993','México D.F.','5022','Mexico');""" import loggingimport sysfrom pathlib import Path import PySimpleGUI as sg ## pysimplegui 4.60.4sg.set_options(dpi_awareness=True)sg.change_look_and_feel("SystemDefaultForReal")sg.set_options(font=("Arial", 11)) # Set the font and font size for the table
p = Path.cwd().parent# sys.path.append(f"{str(p)}/pysimplesql/")import pysimplesql as ss
logger = logging.getLogger(name)logging.basicConfig( level=logging.DEBUG ) # <=== You can set the logging level here (NOTSET,DEBUG,INFO,WARNING,ERROR,CRITICAL) def edit_cell(window, element, key, row, col, justify='left'): global textvariable, edit
def callback(event, row, col, text, key): global edit widget = event.widget if key == 'Return': text = widget.get() values = list(table.item(row, 'values')) values[col] = text table.item(row, values=values) dataset_row[column_names[col-1]] = text frm[data_key].save_record(display_message=False) # threaded info close has error here widget.destroy() widget.master.destroy() edit = False if edit or row <= 0: return edit = True column_names = element.metadata["TableHeading"].columns() data_key = key dataset_row = frm[key].rows[frm[key].current_index] table = window[key].Widget root = table.master text = table.item(row, "values")[col] x, y, width, height = table.bbox(row, col) frame = sg.tk.Frame(root) frame.place(x=x, y=y, anchor="nw", width=width, height=height) textvariable = sg.tk.StringVar() textvariable.set(text) entry = sg.tk.Entry(frame, textvariable=textvariable, justify=justify) entry.pack(expand=True, fill="both") entry.select_range(0, sg.tk.END) entry.icursor(sg.tk.END) entry.focus_force() entry.bind("<Return>", lambda e, r=row, c=col, t=text, k='Return':callback(e, r, c, t, k)) entry.bind("<Escape>", lambda e, r=row, c=col, t=text, k='Escape':callback(e, r, c, t, k))
-------------------------# CREATE PYSIMPLEGUI LAYOUT# -------------------------edit = False
Film# -------------------------# Define the columns for the table selector using the TableHeading convenience class. This will also allow sorting!headings = ss.TableHeadings(sort_enable=True)headings.add_column("CustomerName", "Company", width=30)headings.add_column("ContactName", "Contact", width=30)headings.add_column("Country", "Country", width=30)selector = [
ss.selector( "Customers", sg.Table, key = 'Customers', num_rows=20, headings=headings, auto_size_columns=True, alternating_row_color="#f2f2f2", row_height=25, enable_click_events=True, ),
]
Define the columns for the table selectorlayout = [selector,]
--------------------------------------------------------------------------------------# Main Layout# --------------------------------------------------------------------------------------window = sg.Window(
"Northwind Example", layout, finalize=True, grab_anywhere=True, alpha_channel=0, ttk_theme=ss.themepack.ttk_theme, icon=ss.themepack.icon
) driver = ss.Driver.sqlite(":memory:", sql_commands=sql) # Create a new database connection
Here is the magic!frm = ss.Form(
driver, bind_window=window,
) window.SetAlpha(1)frm.force_save = True window["Customers"].bind('
' , "+-double click-") --------------------------------------------------------------------------------------# MAIN LOOP# --------------------------------------------------------------------------------------while True:
event, values = window.read() if event in (sg.WIN_CLOSED, "Exit", "-ESCAPE-"): frm.close() # <= ensures proper closing of the sqlite database and runs a database optimization window.close() break elif ss.process_events( event, values ): # <=== let PySimpleSQL process its own events! Simple! logger.info(f"PySimpleDB event handler handled the event {event}!") elif event == 'Customers+-double click-': row, col = window['Customers'].get_last_clicked_position() edit_cell(window, window['Customers'], 'Customers', row+1, col, justify='left') else: logger.info(f"This event ({event}) is not yet handled.")
— Reply to this email directly, view it on GitHub https://github.com/PySimpleSQL/pysimplesql/issues/282, or unsubscribe https://github.com/notifications/unsubscribe-auth/AQX2RESH5SXECK4LPVEWM5DXBBFYVANCNFSM6AAAAAAW5PYF7M . You are receiving this because you are subscribed to this thread.Message ID: @.***>
Mainly just duck-taped it to use our dataset/TableHeader information!
I think it would be possible, using same technique, to float comboboxes (if it had an fk relationship) and checkboxes if user somehow designated column as bool. You’d probably un-bind/re-bind the heading clicks to disable sorting if an element was open.
Worked on this a bit more.
1) We will need a 'lock_navigation' function.
2) Need to re-write info window to use asyncio
3) Need to write the Combobox drown-down part
4) Add comparing row data (vs just field elements) in records_changed
so we don't need to use force_save
Working on this at https://github.com/ssweber/pysimplesql-examples/blob/main/parent%20child%20grandchild.py - it currently is all tkinter code (doesn’t need a while True loop like above), so once it’s working good it shouldn’t be hard to integrate in.
Somewhat working comboboxs (I cheated a bit to get the fk to save to correct int value)... you usually take care of that in save_record:
This might influence what we want to call TableHeadings. I’m thinking of adding edit_enable and possibly designating checkbox.
We could infer placing a checkbox if current value is in [“True”,”False”] or if col-type is bool (MySQL has that right? Not SQLite)
headings=ss.TableHeadings(sort_enable=True, edit_enable=True)
headings.add_column('name', 'Name', width=12)
headings.add_column('active', 'Active ?', width=10, widget=ss.CHECKBOX)
layout = [
[ss.selector('people', sg.Table, num_rows=10, headings=headings)],
And now for checkboxes (if column domain is 'BOOLEAN'. I found out this type can be used in sqlite... just stores as 1/0):
or with ttk checkbutton:
Where the code for all of this stands:
window.TKroot.bind("<Double-Button-1>", callback)
(so no sg Event Loop needed)On save, it updates table element values, and cheats to save row to dataset.
Its a fun hack, but needs to be properly integrated (this is without modifying pysimplesql.py)
I updated example, so it just destroys window if user clicks anywhere else. I think this is very smooth:
Wow, this is extremely impressive! Very smooth for sure
Are you cool with me trying to integrate? See plan at top of page. I’ll work on in a branch.
At first I thought it would be a lot harder and finicky… but seems pretty straight-forward now.
Do you like this behavior? This now syncs both ways Cell edit -> field elements, field elements -> Cell. Excel/libreoffice/msaccess 'accept' by default, so I changed to keep that behavior (and that matches field elements as well)
also, buttons are optional:
Very cool. Yeah, bringing this in line with Access and Libre is a good idea and makes a lot of sense
On Sat, Apr 29, 2023, 12:20 AM ssweber @.***> wrote:
Do you like this behavior? This now syncs both ways Cell edit -> field elements, field elements -> Cell. Excel/libreoffice/msaccess 'accept' by default, so I changed to keep that behavior (and that matches field elements as well)
also, buttons are optional:
— Reply to this email directly, view it on GitHub https://github.com/PySimpleSQL/pysimplesql/issues/282#issuecomment-1528646614, or unsubscribe https://github.com/notifications/unsubscribe-auth/AQX2REWFODBTSKMEWQBFHALXDSJKLANCNFSM6AAAAAAW5PYF7M . You are receiving this because you commented.Message ID: @.***>
What’s left todo:
[x] Generate a diff from save_record after looking at changed_row, and field elements. Right now it updates entire row into db.
[x] Cleanup a few functions, putting them into Dataset instead of _CellEdit
Logic is like this:
For syncing from fields to selectors:
Bound single-click, unfocus, spacebar to _SelectorSync. This:
I wonder if records_changed() should be updated to return a list of changed rows, or an empty list if no changes instead of a bool. That would make it a little more general purpose and 90% of the logic is already there
On Sat, Apr 29, 2023, 7:55 AM ssweber @.***> wrote:
What’s left todo:
- Generate a diff from save_record after looking at changed_row, and field elements. Right now it updates entire row into db.
- Cleanup a few functions, putting them into Dataset instead of _CellEdit
Logic is like this:
- CellEdit binds double clicks. If treeview is clicked. it finds row/col. If matched element has TableHeader edit_enable, it sets up the tk frame/element. It doesn’t allow marker or pk col. Fk-col are setup as combo boxes. Boolean col types are setup as checkbuttons.
- On click of Accept button, or click away (it binds single-click), it casts/checks if different and saves single row series change to Dataset.rows.attrs[“changed_row”]. After 1 edit, it looks directly at this changed_row, so we don’t keep overwriting any changes on other cells of same row.
- It then calls Form.update_fields. A couple places needed to be modified to prefer to get value from changed_row series if it exists (refactored update_fields out of update_elements, and modified table_values).
- Records_changed has been modified to return true if Dataset.has_changed_row() returns true (just looks if isinstance(Dataset.rows.attrs[“changed_row”], pd.Series)
- save_record first copies over the data from changed_row, and then goes through same routine. So any unsynced gui elements take preference.
For syncing from fields to selectors:
Bound single-click, unfocus, spacebar to _SelectorSync. This:
- Pushes change (if diff) to changed_row, and calls update_selectors (which has been modified to prefer changed_row over rows.
— Reply to this email directly, view it on GitHub https://github.com/PySimpleSQL/pysimplesql/issues/282#issuecomment-1528771136, or unsubscribe https://github.com/notifications/unsubscribe-auth/AQX2REUDPLMMXYDCIPKCBPLXDT6UZANCNFSM6AAAAAAW5PYF7M . You are receiving this because you commented.Message ID: @.***>
Update:
I’ve done more refining and getting very close. After I worked for awhile today, I realized that flipping my strategy around would result in a cleaner implementation:
Currently I store all “accepted” changes and field syncs in self.rows.attrs[“changed_row”]. But to have the gui be persistent, I’m constantly either grabbing this row (if it exists, otherwise getting the iloc[self.current_index]), or a copy of the entire self.rows with change_row inserted.
Instead, I’m going to accept/sync directly into self.rows, storing a backup in self.rows.attrs[“current_row_backup”]. Then on records_changed/save_records, I’ll compare to the backup. I’ll discard the backup on a save, or restore if it exists if the index changes. (first/prev/next/last/set_by_index/pk,search)
Additionally, will try to bind directly to the field Entry/Combobox/Checkboxes, rather than blanketly binding to the entire window for selector syncs.
That said, I’ll try to get these changes done, and then It’ll be ready for you to review!
I used your idea above, and split out the value-comparing from records_changed to value_changed. It either returns the cast new value (ready to insert into the row[column], or False. (May look into comparing to an Enum… make sure empty str or 0 don’t get confused with return.
@PySimpleSQL do you have preference on how you’d like to review this?
I can do as 1 pull request, or multiple that build on each-other. Obviously 1 big diff is easier for me, but I also don’t want it to be too big to review.
As multiple it would look like: 1) a few panda-related fixes 2) refactoring:
Or you (if you prefer), look at it celledit-wip. I’ve been squash-merging, so there isn’t much commit-history.
totally open to changing names, behavior, reworking things, etc.
We will be home from our trip later today. I'll check out your branch and get back to you ASAP!
On Mon, May 1, 2023, 6:17 PM ssweber @.***> wrote:
Or you (if you prefer), look at it celledit-wip. I’ve been squash-merging, so there isn’t much commit-history.
totally open to changing names, behavior, reworking things, etc.
— Reply to this email directly, view it on GitHub https://github.com/PySimpleSQL/pysimplesql/issues/282#issuecomment-1530426748, or unsubscribe https://github.com/notifications/unsubscribe-auth/AQX2REVBC2KYPWRTT7TBSNTXEAZAHANCNFSM6AAAAAAW5PYF7M . You are receiving this because you were mentioned.Message ID: @.***>
Awesome. I updated journal_internal.py, so you can open that up and see both the table-editing, and the live-updating (field to selector sync).
I'll leave live_update = False by default for now. I want to look at optimizing by using a tk.after() call to update selectors after a brief pause of inactivity, rather on each keystroke. I don't notice an lag, but I don't have a 1000 record table to test it on!
I split out into separate commits (I didn't test each commit individually, but tried to split them out logically) - you can see them: https://github.com/PySimpleSQL/pysimplesql/commits/celledit_wip
I finally had a moment to sit down and look over your branch - these are all excellent changes! I'm happy with the naming conventions and the great decision to separate action updates from field updates as well.
As soon as you do a pull request, I'll merge it right in :)
Implement checklist:
[ ] use tk.after() for delay live update on text entry
[ ] Expand live update to slider, radio buttons, and listbox
[ ] Don’t update other dataset combo boxes when a description_column, if row is virtual
[ ] Create new Relationship function that returns data_key : column of all dependent columns (that are foreign keyed to the updated column, but arnt cascade dependent.
[ ] And another uses above to see if the description column used in dependent so.Table TableHeadings col list
[x] initial port into pysimplesql: Move logic into _CellEdit
[x] IN PROGRESS - clean up initial port into single class
[x] Refactor update_elements. New: update_actions, update_fields.
[x] Bind window.TKroot.bind("", self.discard()) to destroy the edit window if user clicks anywhere else other than edit-field/buttons.
[x] Put window.TKroot.bind("", callback) in Form.bind routine
[x] Refactor get_combobox_values
[x] Add edit_enable=True to TableHeaders (does this change what we call TableHeaders?)
[x] push Table cell change to field elements, if there are any
[x] If we push changes to field elements, then we can check field elements against changed_row (if it exists) instead of getting copy directly from rows.
[x] In records_changed, add logic to see when Table element has changed, and use that for database update instead of elements.
[x] Final _CellEdit cleanup: Pass more variables around instead of using class self variables.
[x] Possibly bind field elements to push changes to sg.Tables on unfocus edit.
[x] Make sure Dataset.changed_row resets properly in all save conditions.
[x] I think I’m saving changes into changed_row using col position. Change to name, so that sg.Table can have different column positions and still work
[x] Make sure I’m getting current row correctly, and sorting the table doesn’t break it.
[x] Add save/discards to themepack, to either enable or disable.
[x] Look at ttk.Combobox width. In example I posted it originally stretched too wide.
[x] rename Dataset.changed_row to? MOVED INTO rows.attrs["changed_row"]
[x] Editing virtual rows? Check logic.
[x] Make behavior configurable. Current has save/discard buttons, click outside of widget discards. But buttons sorta take up a lot space. I think behavior should be: autosave or auto-discard. Autosave would have an X button to discard. Auto-discard would have a ✓ to save.
[x] Rename save to save_cell - Made ACCEPT/CANCEL, like excel/libreoffice
[x] sort() currently reverts table element, since it updates directly from rows. Either need to prompt_save() or something? Maybe after sorting, before updating selector, replace current row with changed_row
[x] Properly handle keyed values
[x] Final cleanup, put functions in correct spot, minimize code duplication.
Playing around with PySimpleGui editable-table example. Basically it creates an input box, and sets it in front of the table cell.
Got it somewhat working with force-save, and disabling the threaded info close. It would require some thinking to decide the exact behavior desired and figure out how to accomplish, and we'd need to change the info-popup to use asyncio.
https://user-images.githubusercontent.com/57631333/231857227-c6916daf-71b7-4155-9fb0-b71098b0926e.mp4