PySimpleSQL


NamePySimpleSQL JSON
Version 0.0.2 PyPI version JSON
download
home_pagehttps://github.com/PySimpleSQL/PySimpleSQL
Summarysqlite3 database binding for PySimpleGUI
upload_time2021-05-04 18:48:33
maintainer
docs_urlNone
authorJonathan Decker
requires_python
license
keywords sql sqlite database application front-end access libre office gui pysimplegui
VCS
bugtrack_url
requirements No requirements were recorded.
Travis-CI No Travis.
coveralls test coverage No coveralls.
            # PySimpleSQL User's Manual

## DISCLAIMER:
While PySimpleSQL works with and was inspired by the excellent PySimpleGUI™ project, it has no affiliation.

## Rapidly build and deploy database applications in Python
PySimpleSQL binds PySimpleGUI™ to sqlite3 databases for rapid, effortless database application development. Makes a great
replacement for MS Access or Libre Office Base! Have the full power and language features of Python while having the 
power and control of managing your own codebase. PySimpleSQL not only allows for super simple automatic control (not one single
line of SQL needs written to use PySimpleSQL), but also allows for very low level control for situations that warrant it.

## History
PySimpleSQL was conceived after having used PySimpleGUI™ to prototype a GUI in Python.  After some time it became apparent that
my approach of prototyping in one language, just to implement it in another wasn't very efficient and didn't make much sense.
I had taken this approach many times in the past due to the lack of a good RAD (Rapid Application Development) tool when it comes
to making database front ends in Python.  Rather than spending my time porting my prototype over, one time I decided to try my hand
at creating such a tool - and this is what I ended up with.
Now make no mistake - I'm not a good project maintainer, and my goal was never to launch an open source project in the first place!
The more I used this combination of PySimpleSQL and PySimpleGUI™ for my own database projects, the more I realized how many others 
would benefit from it. With that being said, I will do my best to maintain and improve this tool over time.  Being new to open source
as well as hosting projects like this, I have a lot to learn moving forward.  Your patience is appreciated.

# Lets do this!

## Install
NOTE: PySimpleSQL is not yet on PyPi, but will be soon!
```
pip install PySimpleGUI
pip install pysimplesql
or
pip3 install PySimpleGUI
pip3 install pysimplesql
```

### This Code

```python
#!/usr/bin/python3
import PySimpleGUI as sg
import PySimpleSQL as ss  # <=== PySimpleSQL lines will be marked like this.  There's only a few!

# Define our layout. We will use the PySimpleSQL.record() convenience function to create the controls
layout = [
    ss.record('Restaurant', 'name'),
    ss.record('Restaurant', 'location'),
    ss.record('Restaurant', 'fkType', sg.Combo)]
sub_layout = [
    [sg.Listbox(values=(), size=(35, 10), key="SELECTOR.Item", select_mode=sg.LISTBOX_SELECT_MODE_SINGLE,
                enable_events=True),
     sg.Col(
         [ss.record('Item', 'name'),
          ss.record('Item', 'fkMenu', sg.Combo),
          ss.record('Item', 'price'),
          ss.record('Item', 'description', sg.MLine, (30, 7))
          ])],
    ss.record_actions('Item', False)
]
layout += [[sg.Frame('Items', sub_layout)]]
layout += [ss.actions('Restaurant')]

# Initialize our window and database, then bind them together
win = sg.Window('places to eat', layout, finalize=True)
db = ss.Database(':memory:', 'example.sql', win)  # <=== load the database and bind it to the window

while True:
    event, values = win.read()
    if db.process_events(event, values):  # <=== let PySimpleSQL process its own events! Simple!
        print('PySimpleDB event handler handled the event!')
    elif event == sg.WIN_CLOSED or event == 'Exit':
        break
    else:
        print(f'This event ({event}) is not yet handled.')
```
along with this sqlite table
```sql
DROP TABLE IF EXISTS "Restaurant";
DROP TABLE IF EXISTS "Item";
DROP TABLE IF EXISTS "Type";
DROP TABLE IF EXISTS "Menu";

CREATE TABLE "Restaurant"(
	"pkRestaurant" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	"name" TEXT DEFAULT "New Restaurant",
	"location" TEXT,
	"fkType" INTEGER,
	FOREIGN KEY(fkType) REFERENCES Type(pkType)
);

CREATE TABLE "Item"(
	"pkItem" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	"name" TEXT DEFAULT "New Item",
	"fkRestaurant" INTEGER,
	"fkMenu" INTEGER,
	"price" TEXT,
	"description" TEXT,
	FOREIGN KEY(fkRestaurant) REFERENCES Restaurant(pkRestaurant) ON UPDATE CASCADE,
	FOREIGN KEY(fkMenu) REFERENCES Menu(pkMenu)
);

CREATE TABLE "Type"(
	"pkType" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	"name" TEXT DEFAULT "New Type"
);

CREATE TABLE "Menu"(
	"pkMenu" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	"name" TEXT DEFAULT "New Menu"
);

INSERT INTO "Menu" VALUES (1,"Breakfast");
INSERT INTO "Menu" VALUES (2,"Lunch");
INSERT INTO "Menu" VALUES (3,"Dinner");

INSERT INTO "Type" VALUES (1,"Fast Food");
INSERT INTO "Type" VALUES (2,"Fine Dining");
INSERT INTO "Type" VALUES (3,"Hole in the wall");
INSERT INTO "Type" VALUES (4,"Chinese Food");

INSERT INTO "Restaurant" VALUES (1,"McDonalds","Seatle WA",1);
INSERT INTO "Item" VALUES (1,"Hamburger",1,2,"$4.99","Not flame broiled");
INSERT INTO "Item" VALUES (2,"Cheeseburger",1,2,"$5.99","With or without pickles");
INSERT INTO "Item" VALUES (3,"Big Breakfast",1,1,"$6.99","Your choice of bacon or sausage");

INSERT INTO "Restaurant" VALUES (2,"Chopstix","Cleveland OH",4);
INSERT INTO "Item" VALUES (4,"General Tso",2,3,"$7.99","Our best seller!");
INSERT INTO "Item" VALUES (5,"Teriaki Chicken",2,3,"$5.99","Comes on a stick");
INSERT INTO "Item" VALUES (6,"Sticky Rice",2,2,"$6.99","Our only lunch option, sorry!");

INSERT INTO "Restaurant" VALUES (3,"Jimbos","Lexington KY",3);
INSERT INTO "Item" VALUES (7,"Breakfast Pizza",3,1,"$11.99","Pizza in the morning");
INSERT INTO "Item" VALUES (8,"Lunch Pizza",3,3,"$12.99","Pizza at noon");
INSERT INTO "Item" VALUES (9,"Dinner Pizza",3,3,"$16.99","Whatever we did not sell earlier in the day");

```
### Makes This fully operational database front-end

![image](https://user-images.githubusercontent.com/70232210/91227678-e8c73700-e6f4-11ea-83ee-4712e687bfb4.png)

Like PySimpleGUI™, pySimpleSQL supports subscript notation, so your code can access the data easily in the format of db['Table']['field'].
In the example above, you could get the current item selection with the following code:
```python
selected_restaurant=db['Restaurant']['name']
selected_item=db['Item']['name']
```
or via the PySimpleGUI™ control elements with the following:
```python
selected_restaurant=win['Restaurant.name']
selected_item=win['Item.name']
```
### Any Questions?  It's that simple.

To get the best possible experience with PySimpleSQL, the magic is in the schema of the database.
The automatic functionality of PySimpleSQL relies on just a couple of things:
- foreign key constraints on the database tables (lets PySimpleSQL know what the relationships are)
- a CASCADE ON UPDATE constraint on any tables that should automatically refresh child tables when parent tables are 
refreshed
- PySimpleGUI™ control keys need to be named {table}.{field} for automatic mapping.  Of course, manual mapping is 
supported as well. @Database.record() is a convenience function/"custom control" to make adding records quick and easy!
- The field 'name', (or the 2nd column of the database in the absence of a 'name' column) is what will display in 
comboxes for foreign key relationships.  Of course, this can be changed manually if needed, but truly the simplictiy of 
PySimpleSQL is in having everything happen automatically!

Here is another example sqlite table that shows the above rules at work.  Don't let this scare you, there are plenty of
tools to create your database without resorting to raw SQL commands. These commands here are just shown for completeness
(Creating the sqlite database is only done once anyways) 
```sql
CREATE TABLE "Book"(
    "pkBook" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "title" TEXT,
    "author" TEXT
);
CREATE TABLE "Chapter"(
    "pkChapter" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "title" TEXT,
    "fkBook" INTEGER,
    "startPage" INTEGER,
    -- SECRET SAUCE BELOW! If you have foreign key constraints set on the database,
    -- then PySimpleSQL will pick them up!
    -- note: ON UPDATE CASCADE only needed if you want automatic GUI refreshing
    -- (i.e. not every constraint needs them, like fields that will populate comboboxes for example)
    FOREIGN KEY(fkBook) REFERENCES Book(pkBook) ON UPDATE CASCADE
);
```

### But wait, there's more!
The above is literally all you have to know for working with simple and even moderate databases.  However, there is a 
lot of power in learning what is going on under the hood!  Starting with the fully automatic example above, we will work
backwards to explain what is available to you for more control at a lower level.

#### PySimpleSQL elements:
Referencing the example above, look at the following:
```python
# convience function for rapid front-end development
ss.record('Restaurant', 'name') # Table name, field name parameters

# could have been written like this:
[sg.Text('Name:',size=(15,1)),sg.Input('',key='Restaurant.name',size=(30,1))]
```
As you can see, the @Database.record() convenience function simplifies making record controls that adhere to the
PySimpleSQL naming convention of Table.field. In fact, there is even more you can do with this. The @Database.record() 
function can take a PySimpleGUI™ control element as a parameter as well, overriding the defaul Input() element.
See this code which creates a combobox instead:
```python
ss.record('Restaurant', 'fkType', sg.Combo)]
```
Furthering that, the functions @Database.set_text_size() and @Database.set_control_size() can be used before calls to 
@Database.record() to have custom sizing of the control elements.  Even with these defaults set, the size parameter of 
@Database.record() will override the default control size, for plenty of flexibility.

Place those two functions just above the layout definition shown in the example above and then run the code again

```python
ss.set_text_size(10, 1)  # Set the text/label size for all subsequent calls
ss.set_control_size(50, 1)  # set the control size for all subsequent calls
layout = [
    ss.record('Restaurant', 'name'),
    ss.record('Restaurant', 'location'),
    ss.record('Restaurant', 'fkType', sg.Combo)]
sub_layout = [
    [sg.Listbox(values=(), size=(35, 10), key="SELECTOR.Item", select_mode=sg.LISTBOX_SELECT_MODE_SINGLE,
                enable_events=True),
     sg.Col(
         [ss.record('Item', 'name'),
          ss.record('Item', 'fkMenu', sg.Combo),
          ss.record('Item', 'price'),
          ss.record('Item', 'description', sg.MLine, (30, 7))  # Override the default size for this element!
          ])],
    ss.record_actions('Item', False)
]
layout += [[sg.Frame('Items', sub_layout)]]
layout += [ss.actions('Restaurant', protect=True, search=True, save=True)]
```
![image](https://user-images.githubusercontent.com/70232210/91287363-a71ea680-e75d-11ea-8b2f-d240c1ec2acf.png)
You will see that now, the controls were resized using the new sizing rules.  Notice however that the 'Description'
field isn't as wide as the others.  That is because we overridden the control size for just that single control.

Lets see one more example.  This time we will fix the oddly sized 'Description' field, as well as make the 'Restaurant' 
and 'Items' sections with their own sizing

```python
# set the sizing for the Restaurant section
ss.set_text_size(10, 1)
ss.set_control_size(90, 1)
layout = [
    ss.record('Restaurant', 'name'),
    ss.record('Restaurant', 'location'),
    ss.record('Restaurant', 'fkType', sg.Combo)]
# set the sizing for the Items section
ss.set_text_size(10, 1)
ss.set_control_size(50, 1)
sub_layout = [
    [sg.Listbox(values=(), size=(35, 10), key="SELECTOR.Item", select_mode=sg.LISTBOX_SELECT_MODE_SINGLE,
                enable_events=True),
     sg.Col(
         [ss.record('Item', 'name'),
          ss.record('Item', 'fkMenu', sg.Combo),
          ss.record('Item', 'price'),
          ss.record('Item', 'description', sg.MLine, (50, 10))  # Override the default size for this element
          ])],
    ss.record_actions('Item', False)
]
layout += [[sg.Frame('Items', sub_layout)]]
layout += [ss.actions('Restaurant', protect=True, search=True, save=True)]
```
![image](https://user-images.githubusercontent.com/70232210/91288080-8e62c080-e75e-11ea-8438-86035d4d6609.png)




### Binding the window to the element
Referencing the same example above, the window and database were bound with this one single line:
```python
db = ss.Database(':memory:', 'example.sql', win) # Load in the database and bind it to win
```
The above is a one-shot approach and all most users will ever need!
The above could have been written as:
```python
db=ss.Database(':memory:', 'example.sql') # Load in the database
db.auto_bind(win) # automatically bind the window to the database
```

db.auto_bind() likewise can be peeled back to it's own components and could have been written like this:
```python
db.auto_add_tables()
db.auto_add_relationships()
db.auto_map_controls(win)
db.auto_map_events(win)
db.requery_all()
db.update_controls()
```

And finally, that brings us to the lowest-level functions for binding the database.
This is how you can MANUALLY map tables, relationships, controls and events to the database.
The above auto_map_* functions could have been manually achieved as follows:
```python
# Add the tables you want PySimpleSQL to handle.  The function db.auto_add_tables() will add all tables found in the database 
# by default.  However, you may only need to work with a couple of tables in the database, and this is how you would do that
db.add_table('Restaurant','pkRestaurant','name') # add the table Restaurant, with it's primary key field, and descriptive field (for comboboxes)
db.add_table('Item','pkItem','name') # Note: While I personally prefer to use the pk{Table} and fk{Table} naming
db.add_table('Type','pkType','name') #       conventions, it's not necessary for pySimpleSQL
db.add_table('Menu','pkMenu','name') #       These could have just as well been restaurantID and itemID for example

# Set up relationships
# Notice below that the first relationship has the last parameter to True.  This is what the ON UPDATE CASCADE constraint accomplishes.
# Basically what it means is that then the Restaurant table is requeried, the associated Item table will automatically requery right after.
# This is what allows the GUI to seamlessly update all of the control elements when records are changed!
# The other relationships have that parameter set to False - they still have a relationship, but they don't need requeried automatically
db.add_relationship('LEFT JOIN', 'Item', 'fkRestaurant', 'Restaurant', 'pkRestaurant', True) 
db.add_relationship('LEFT JOIN', 'Restaurant', 'fkType', 'Type', 'pkType', False)
db.add_relationship('LEFT JOIN', 'Item', 'fkMenu', 'Menu', 'pkMenu', False)

# Map our controls
# Note that you can map any control to any Table/field combination that you would like.
# The {Table}.{field} naming convention is only necessary if you want to use the auto-mapping functionality of PySimpleSQL!
db.map_control(win['Restaurant.name'],'Restaurant','name')
db.map_control(win['Restaurant.location'],'Restaurant','location')
db.map_control(win['Restaurant.fkType'],'Type','pkType')
db.map_control(win['Item.name'],'Item','name')
db.map_control(win['Item.fkRestaurant'],'Item','fkRestaurant')
db.map_control(win['Item.fkMenu'],'Item','fkMenu')
db.map_control(win['Item.price'],'Item','price')
db.map_control(win['Item.description'],'Item','description')

# Map out our events
# In the above example, this was all done in the background, as we used convenience functions to add record navigation buttons.
# However, we could have made our own buttons and mapped them to events.  Below is such an example
db.map_event('Edit.Restaurant.First',db['Restaurant'].First) # button control with the key of 'Edit.Restaurant.First'
                                                             # mapped to the Table.First method
db.map_event('Edit.Restaurant.Previous',db['Restaurant'].Previous)
db.map_event('Edit.Restaurant.Next',db['Restaurant'].Next)
db.map_event('Edit.Restaurant.Last',db['Restaurant'].Last)
# and so on...
# In fact, you can use the event mapper however you want to, mapping control names to any function you would like!
# Event mapping will be covered in more detail later...

# This is the magic function which populates all of the controls we mapped!
# For your convience, you can optionally use the function Database.set_callback('update_controls',function) to set a callback function
# that will be called every time the controls are updated.  This allows you to do custom things like update
# a preview image, change control parameters or just about anythong you want!
db.update_controls()
```

As you can see, there is a lot of power in the auto functionality of PySimpleSQL, and you should take advantage of it any time you can.  Only very specific cases need to reach this lower level of manual configuration and mapping!

# BREAKDOWN OF ADVANCED FUNCTIONALITY
PySimpleSQL does much more than just bridge the gap between PySimpleGUI™ and Sqlite databases! In full, PySimpleSQL contains:
* Convenience functions for simplifying PySimpleGUI™ layout code
* Control binding between PySimpleGUI™ controls and Sqlite database fields
* Automatic requerying of related tables
* Record navigation - Such as First, Previous, Next, Last, Searching and selector controls
* Callbacks allow your own functions to expand control over your own database front ends
* Event Mapping

We will break each of these down below to give you a better understanding of how each of these features works.
## Convenience Functions
There are currently only a few convenience functions to aid in quikly creating PySimpleGUI™ layout code
Database.set_text_size(width,height) - Sets the PySimpleGUI™ text size for subsequent calls to Database.record(). Defaults to (15,1) otherwise.

Database.set_control_size(width, height) - Sets the PySImpleGUI™ control size for subsequent calls to Database.record(). Defaults to (30,1) otherwise.

Database.record(table, field,control_type=None,size=None,text_label=None)- This is a convenience function for creating a PySimpleGUI™ text control and a PySimpleGUI™ Input control inline for purposes of displaying a record from the database.  This function also creates the naming convention (table.field) in the control's key parameter that PySimpleSQL uses for advanced automatic functionality. The optional control_type parameter allows you to bind control types other than Input to a database field.  Checkboxes, listboxes and other controls entered here will override the default Input control. The size parameter will override the default control size that was set with Database.set_control_size().  Lastly, the text_label parameter will previx a text field before the control.

Database.actions()-

## Control Binding

## Automatic Requerying

## Record Navigation
PySimpleSQL includes a convenience function for adding record navigation buttons to your project.  For lower level control or a custom look, you may want to learn how to do this on your own.  Lets start with the convenience function and work backwards from there to see how you can implement your own record navigation controls.

The convenience function PySimpleSQL.actions() is a swiss army knife when it comes to generating PySimpleGUI™ layout code for your record navigation controls.  With it, you can add First, Previous, Next and Last record navigation buttons, a search box, edit protection modes, and record actions such as Insert, Save and Delete (Or any combination of these items).  Under the hood, the actions() convenience function uses the Event Mapping features of PySimpleSQL, and your own code can do this too!
See the code below on example usage of the PySimpleSQL.actions() convenience function

```python
#!/usr/bin/python3
import PySimpleGUI as sg
import PySimpleSQL as ss

# Create a small table just for demo purposes
sql = '''
CREATE TABLE "Fruit"(
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	"name" TEXT DEFAULT "New Fruit"
);
INSERT INTO "Fruit" ("name") VALUES ("Apple");
INSERT INTO "Fruit" ("name") VALUES ("Orange");
INSERT INTO "Fruit" ("name") VALUES ("Banana");
INSERT INTO "Fruit" ("name") VALUES ("Kiwi");
'''

# PySimpleGUI™ layout code to create your own navigation buttons
table = 'Fruit'  # This is the table in the database that you want to navigate
layout = [
    ss.record(table, 'name', label='Fruit Name'),  # PySimpleSQL.record() convenience function for easy record creation!
    ss.actions(table)  # PySimpleSQL.actions() convenience function for easy navigation controls!
]

win = sg.Window('Navigation demo', layout, finalize=True)
# note: Since win was passed as a parameter, binding is automatic (including event mapping!)
# Also note, in-memory databases can be created with ":memory:"!
db = ss.Database(':memory:', win, sql_commands=sql)

while True:
    event, values = win.read()
    if db.process_events(event, values):  # <=== let PySimpleSQL process its own events! Simple!
        print(f'PySimpleDB event handler handled the event {event}!')
    elif event == sg.WIN_CLOSED or event == 'Exit':
        db = None  # <= ensures proper closing of the sqlite database and runs a database optimization
        break
    else:
        print(f'This event ({event}) is not yet handled.')
```
Simple!
But as stated earlier, PySimpleSQL.actions is a swiss army knife!  Experiment with the code ablove, trying all of these variations to see all of goodness this convenience functions provides!

```python
ss.actions(table, search=False)
ss.actions(table, save=False)
ss.actions(table, edit_protect=False)
ss.actions(table, insert=False)
ss.actions(table, delete=False, save=False) 
```


See example below of how your can make your own record navigation controls instead of using the PySimpleSQL.actions() convenience function:

```python
#!/usr/bin/python3
import PySimpleGUI as sg
import PySimpleSQL as ss

# Create a small table just for demo purposes
sql = '''
CREATE TABLE "Fruit"(
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	"name" TEXT DEFAULT "New Fruit"
);
INSERT INTO "Fruit" ("name") VALUES ("Apple");
INSERT INTO "Fruit" ("name") VALUES ("Orange");
INSERT INTO "Fruit" ("name") VALUES ("Banana");
INSERT INTO "Fruit" ("name") VALUES ("Kiwi");
'''

# PySimpleGUI™ layout code to create your own navigation buttons
table = 'Fruit'  # This is the table in the database that you want to navigate
layout = [
    ss.record(table, 'name', label='Fruit Name'),  # PySimpleSQL.record() convenience function for easy record creation!
    # Below we will create navigation buttons manually, naming the key so that the automatic event mapper will map the events
    [sg.Button('<<', key=f'Event.{table}.First', size=(1, 1)),
     sg.Button('<', key=f'Event.{table}.Previous', size=(1, 1)),
     sg.Button('>', key=f'Event.{table}.Next', size=(1, 1)),
     sg.Button('>>', key=f'Event.{table}.Last', size=(1, 1))
     ]
]

win = sg.Window('Navigation demo', layout, finalize=True)
# note: Since win was passed as a parameter, binding is automatic (including event mapping!)
# Also note, in-memory databases can be created with ":memory:"!
db = ss.Database(':memory:', win, sql_commands=sql)

while True:
    event, values = win.read()
    if db.process_events(event, values):  # <=== let PySimpleSQL process its own events! Simple!
        print(f'PySimpleDB event handler handled the event {event}!')
    elif event == sg.WIN_CLOSED or event == 'Exit':
        db = None  # <= ensures proper closing of the sqlite database and runs a database optimization
        break
    else:
        print(f'This event ({event}) is not yet handled.')
```
Notice the naming convention of the PySimpleGUI™ contrl keys in the example above.  They are in the format of "Event.table_name.event_type".  This is so that the Automatic event mapping of PySimpleSql will handle these.  Valid event_types include: Insert, Save, Delete, First, Previous, Next, Last and Search.

Peeling this back further, you can rewrite the same without the special naming convention used by the automatic event mapper, and instead name the keys however you would like, then manually map them in the event mapper...

```python
#!/usr/bin/python3
import PySimpleGUI as sg
import PySimpleSQL as ss

# Create a small table just for demo purposes
sql = '''
CREATE TABLE "Fruit"(
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	"name" TEXT DEFAULT "New Fruit"
);
INSERT INTO "Fruit" ("name") VALUES ("Apple");
INSERT INTO "Fruit" ("name") VALUES ("Orange");
INSERT INTO "Fruit" ("name") VALUES ("Banana");
INSERT INTO "Fruit" ("name") VALUES ("Kiwi");
'''

# PySimpleGUI™ layout code to create your own navigation buttons
table = 'Fruit'  # This is the table in the database that you want to navigate
layout = [
    ss.record(table, 'name', label='Fruit Name'),  # PySimpleSQL.record() convenience function for easy record creation!
    # Below we will create navigation buttons manually, naming the key so that the automatic event mapper will map the events
    [
        sg.Button('<<', key=f'btnFirst', size=(1, 1)),
        sg.Button('<', key=f'btnPrevious', size=(1, 1)),
        sg.Button('>', key=f'btnNext', size=(1, 1)),
        sg.Button('>>', key=f'btnLast', size=(1, 1))
    ]
]

win = sg.Window('Navigation demo', layout, finalize=True)
# note: Since win was passed as a parameter, binding is automatic (including event mapping!)
# Also note, in-memory databases can be created with ":memory:"!
db = ss.Database(':memory:', win, sql_commands=sql)

# Manually map the events, since we did not adhere to the naming convention that the automatic mapper expects
db.map_event('btnFirst', db[table].first)
db.map_event('btnPrevious', db[table].previous)
db.map_event('btnNext', db[table].next)
db.map_event('btnLast', db[table].last)

while True:
    event, values = win.read()
    if db.process_events(event, values):  # <=== let PySimpleSQL process its own events! Simple!
        print(f'PySimpleDB event handler handled the event {event}!')
    elif event == sg.WIN_CLOSED or event == 'Exit':
        db = None  # <= ensures proper closing of the sqlite database and runs a database optimization
        break
    else:
        print(f'This event ({event}) is not yet handled.')
```

Lastly, you can rewrite the same and handle the events yourself instead of relying on PySimpleSQL's event mapper

```python
#!/usr/bin/python3
import PySimpleGUI as sg
import PySimpleSQL as ss

# Create a small table just for demo purposes
sql = '''
CREATE TABLE "Fruit"(
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	"name" TEXT DEFAULT "New Fruit"
);
INSERT INTO "Fruit" ("name") VALUES ("Apple");
INSERT INTO "Fruit" ("name") VALUES ("Orange");
INSERT INTO "Fruit" ("name") VALUES ("Banana");
INSERT INTO "Fruit" ("name") VALUES ("Kiwi");
'''

# PySimpleGUI™ layout code to create your own navigation buttons
table = 'Fruit'  # This is the table in the database that you want to navigate
layout = [
    ss.record(table, 'name', label='Fruit Name'),  # PySimpleSQL.record() convenience function for easy record creation!
    # Below we will create navigation buttons manually, naming the key so that the automatic event mapper will map the events
    [
        sg.Button('<<', key=f'btnFirst', size=(1, 1)),
        sg.Button('<', key=f'btnPrevious', size=(1, 1)),
        sg.Button('>', key=f'btnNext', size=(1, 1)),
        sg.Button('>>', key=f'btnLast', size=(1, 1))
    ]
]

win = sg.Window('Navigation demo', layout, finalize=True)
# note: Since win was passed as a parameter, binding is automatic (including event mapping!)
# Also note, in-memory databases can be created with ":memory:"!
db = ss.Database(':memory:', win, sql_commands=sql)

while True:
    event, values = win.read()
    # Manually handle our record selector events, bypassing the event mapper completely
    if db.process_events(event, values):  # <=== let PySimpleSQL process its own events! Simple!
        print(f'PySimpleDB event handler handled the event {event}!')
    elif event == 'btnFirst':
        db[table].first()
    elif event == 'btnPrevious':
        db[table].previous()
    elif event == 'btnNext':
        db[table].next()
    elif event == 'btnLast':
        db[table].last()
    elif event == sg.WIN_CLOSED or event == 'Exit':
        db = None  # <= ensures proper closing of the sqlite database and runs a database optimization
        break
    else:
        print(f'This event ({event}) is not yet handled.')

```

Whether you want to use the PySimpleSQL.actions() convenience function, write your own navigation button layout code, use the auto event mapper, manually map the events, or handle the events yourself, you have plenty of options for flexibility writing your navigation button code!  Of course, the convenience function is very flexible and has attractive icons in the buttons, and really should be used in most cases.
## Callbacks

## Event Mapping

*
            

Raw data

            {
    "_id": null,
    "home_page": "https://github.com/PySimpleSQL/PySimpleSQL",
    "name": "PySimpleSQL",
    "maintainer": "",
    "docs_url": null,
    "requires_python": "",
    "maintainer_email": "",
    "keywords": "SQL sqlite database application front-end access libre office GUI PySimpleGUI",
    "author": "Jonathan Decker",
    "author_email": "PySimpleSQL@gmail.com",
    "download_url": "https://files.pythonhosted.org/packages/d3/f7/959d5091dcb5a287992426fea7f1b9862b2b39c93684814c8ad04c859cb8/PySimpleSQL-0.0.2.tar.gz",
    "platform": "",
    "description": "# PySimpleSQL User's Manual\n\n## DISCLAIMER:\nWhile PySimpleSQL works with and was inspired by the excellent PySimpleGUI\u2122 project, it has no affiliation.\n\n## Rapidly build and deploy database applications in Python\nPySimpleSQL binds PySimpleGUI\u2122 to sqlite3 databases for rapid, effortless database application development. Makes a great\nreplacement for MS Access or Libre Office Base! Have the full power and language features of Python while having the \npower and control of managing your own codebase. PySimpleSQL not only allows for super simple automatic control (not one single\nline of SQL needs written to use PySimpleSQL), but also allows for very low level control for situations that warrant it.\n\n## History\nPySimpleSQL was conceived after having used PySimpleGUI\u2122 to prototype a GUI in Python.  After some time it became apparent that\nmy approach of prototyping in one language, just to implement it in another wasn't very efficient and didn't make much sense.\nI had taken this approach many times in the past due to the lack of a good RAD (Rapid Application Development) tool when it comes\nto making database front ends in Python.  Rather than spending my time porting my prototype over, one time I decided to try my hand\nat creating such a tool - and this is what I ended up with.\nNow make no mistake - I'm not a good project maintainer, and my goal was never to launch an open source project in the first place!\nThe more I used this combination of PySimpleSQL and PySimpleGUI\u2122 for my own database projects, the more I realized how many others \nwould benefit from it. With that being said, I will do my best to maintain and improve this tool over time.  Being new to open source\nas well as hosting projects like this, I have a lot to learn moving forward.  Your patience is appreciated.\n\n# Lets do this!\n\n## Install\nNOTE: PySimpleSQL is not yet on PyPi, but will be soon!\n```\npip install PySimpleGUI\npip install pysimplesql\nor\npip3 install PySimpleGUI\npip3 install pysimplesql\n```\n\n### This Code\n\n```python\n#!/usr/bin/python3\nimport PySimpleGUI as sg\nimport PySimpleSQL as ss  # <=== PySimpleSQL lines will be marked like this.  There's only a few!\n\n# Define our layout. We will use the PySimpleSQL.record() convenience function to create the controls\nlayout = [\n    ss.record('Restaurant', 'name'),\n    ss.record('Restaurant', 'location'),\n    ss.record('Restaurant', 'fkType', sg.Combo)]\nsub_layout = [\n    [sg.Listbox(values=(), size=(35, 10), key=\"SELECTOR.Item\", select_mode=sg.LISTBOX_SELECT_MODE_SINGLE,\n                enable_events=True),\n     sg.Col(\n         [ss.record('Item', 'name'),\n          ss.record('Item', 'fkMenu', sg.Combo),\n          ss.record('Item', 'price'),\n          ss.record('Item', 'description', sg.MLine, (30, 7))\n          ])],\n    ss.record_actions('Item', False)\n]\nlayout += [[sg.Frame('Items', sub_layout)]]\nlayout += [ss.actions('Restaurant')]\n\n# Initialize our window and database, then bind them together\nwin = sg.Window('places to eat', layout, finalize=True)\ndb = ss.Database(':memory:', 'example.sql', win)  # <=== load the database and bind it to the window\n\nwhile True:\n    event, values = win.read()\n    if db.process_events(event, values):  # <=== let PySimpleSQL process its own events! Simple!\n        print('PySimpleDB event handler handled the event!')\n    elif event == sg.WIN_CLOSED or event == 'Exit':\n        break\n    else:\n        print(f'This event ({event}) is not yet handled.')\n```\nalong with this sqlite table\n```sql\nDROP TABLE IF EXISTS \"Restaurant\";\nDROP TABLE IF EXISTS \"Item\";\nDROP TABLE IF EXISTS \"Type\";\nDROP TABLE IF EXISTS \"Menu\";\n\nCREATE TABLE \"Restaurant\"(\n\t\"pkRestaurant\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n\t\"name\" TEXT DEFAULT \"New Restaurant\",\n\t\"location\" TEXT,\n\t\"fkType\" INTEGER,\n\tFOREIGN KEY(fkType) REFERENCES Type(pkType)\n);\n\nCREATE TABLE \"Item\"(\n\t\"pkItem\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n\t\"name\" TEXT DEFAULT \"New Item\",\n\t\"fkRestaurant\" INTEGER,\n\t\"fkMenu\" INTEGER,\n\t\"price\" TEXT,\n\t\"description\" TEXT,\n\tFOREIGN KEY(fkRestaurant) REFERENCES Restaurant(pkRestaurant) ON UPDATE CASCADE,\n\tFOREIGN KEY(fkMenu) REFERENCES Menu(pkMenu)\n);\n\nCREATE TABLE \"Type\"(\n\t\"pkType\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n\t\"name\" TEXT DEFAULT \"New Type\"\n);\n\nCREATE TABLE \"Menu\"(\n\t\"pkMenu\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n\t\"name\" TEXT DEFAULT \"New Menu\"\n);\n\nINSERT INTO \"Menu\" VALUES (1,\"Breakfast\");\nINSERT INTO \"Menu\" VALUES (2,\"Lunch\");\nINSERT INTO \"Menu\" VALUES (3,\"Dinner\");\n\nINSERT INTO \"Type\" VALUES (1,\"Fast Food\");\nINSERT INTO \"Type\" VALUES (2,\"Fine Dining\");\nINSERT INTO \"Type\" VALUES (3,\"Hole in the wall\");\nINSERT INTO \"Type\" VALUES (4,\"Chinese Food\");\n\nINSERT INTO \"Restaurant\" VALUES (1,\"McDonalds\",\"Seatle WA\",1);\nINSERT INTO \"Item\" VALUES (1,\"Hamburger\",1,2,\"$4.99\",\"Not flame broiled\");\nINSERT INTO \"Item\" VALUES (2,\"Cheeseburger\",1,2,\"$5.99\",\"With or without pickles\");\nINSERT INTO \"Item\" VALUES (3,\"Big Breakfast\",1,1,\"$6.99\",\"Your choice of bacon or sausage\");\n\nINSERT INTO \"Restaurant\" VALUES (2,\"Chopstix\",\"Cleveland OH\",4);\nINSERT INTO \"Item\" VALUES (4,\"General Tso\",2,3,\"$7.99\",\"Our best seller!\");\nINSERT INTO \"Item\" VALUES (5,\"Teriaki Chicken\",2,3,\"$5.99\",\"Comes on a stick\");\nINSERT INTO \"Item\" VALUES (6,\"Sticky Rice\",2,2,\"$6.99\",\"Our only lunch option, sorry!\");\n\nINSERT INTO \"Restaurant\" VALUES (3,\"Jimbos\",\"Lexington KY\",3);\nINSERT INTO \"Item\" VALUES (7,\"Breakfast Pizza\",3,1,\"$11.99\",\"Pizza in the morning\");\nINSERT INTO \"Item\" VALUES (8,\"Lunch Pizza\",3,3,\"$12.99\",\"Pizza at noon\");\nINSERT INTO \"Item\" VALUES (9,\"Dinner Pizza\",3,3,\"$16.99\",\"Whatever we did not sell earlier in the day\");\n\n```\n### Makes This fully operational database front-end\n\n![image](https://user-images.githubusercontent.com/70232210/91227678-e8c73700-e6f4-11ea-83ee-4712e687bfb4.png)\n\nLike PySimpleGUI\u2122, pySimpleSQL supports subscript notation, so your code can access the data easily in the format of db['Table']['field'].\nIn the example above, you could get the current item selection with the following code:\n```python\nselected_restaurant=db['Restaurant']['name']\nselected_item=db['Item']['name']\n```\nor via the PySimpleGUI\u2122 control elements with the following:\n```python\nselected_restaurant=win['Restaurant.name']\nselected_item=win['Item.name']\n```\n### Any Questions?  It's that simple.\n\nTo get the best possible experience with PySimpleSQL, the magic is in the schema of the database.\nThe automatic functionality of PySimpleSQL relies on just a couple of things:\n- foreign key constraints on the database tables (lets PySimpleSQL know what the relationships are)\n- a CASCADE ON UPDATE constraint on any tables that should automatically refresh child tables when parent tables are \nrefreshed\n- PySimpleGUI\u2122 control keys need to be named {table}.{field} for automatic mapping.  Of course, manual mapping is \nsupported as well. @Database.record() is a convenience function/\"custom control\" to make adding records quick and easy!\n- The field 'name', (or the 2nd column of the database in the absence of a 'name' column) is what will display in \ncomboxes for foreign key relationships.  Of course, this can be changed manually if needed, but truly the simplictiy of \nPySimpleSQL is in having everything happen automatically!\n\nHere is another example sqlite table that shows the above rules at work.  Don't let this scare you, there are plenty of\ntools to create your database without resorting to raw SQL commands. These commands here are just shown for completeness\n(Creating the sqlite database is only done once anyways) \n```sql\nCREATE TABLE \"Book\"(\n    \"pkBook\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n    \"title\" TEXT,\n    \"author\" TEXT\n);\nCREATE TABLE \"Chapter\"(\n    \"pkChapter\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n    \"title\" TEXT,\n    \"fkBook\" INTEGER,\n    \"startPage\" INTEGER,\n    -- SECRET SAUCE BELOW! If you have foreign key constraints set on the database,\n    -- then PySimpleSQL will pick them up!\n    -- note: ON UPDATE CASCADE only needed if you want automatic GUI refreshing\n    -- (i.e. not every constraint needs them, like fields that will populate comboboxes for example)\n    FOREIGN KEY(fkBook) REFERENCES Book(pkBook) ON UPDATE CASCADE\n);\n```\n\n### But wait, there's more!\nThe above is literally all you have to know for working with simple and even moderate databases.  However, there is a \nlot of power in learning what is going on under the hood!  Starting with the fully automatic example above, we will work\nbackwards to explain what is available to you for more control at a lower level.\n\n#### PySimpleSQL elements:\nReferencing the example above, look at the following:\n```python\n# convience function for rapid front-end development\nss.record('Restaurant', 'name') # Table name, field name parameters\n\n# could have been written like this:\n[sg.Text('Name:',size=(15,1)),sg.Input('',key='Restaurant.name',size=(30,1))]\n```\nAs you can see, the @Database.record() convenience function simplifies making record controls that adhere to the\nPySimpleSQL naming convention of Table.field. In fact, there is even more you can do with this. The @Database.record() \nfunction can take a PySimpleGUI\u2122 control element as a parameter as well, overriding the defaul Input() element.\nSee this code which creates a combobox instead:\n```python\nss.record('Restaurant', 'fkType', sg.Combo)]\n```\nFurthering that, the functions @Database.set_text_size() and @Database.set_control_size() can be used before calls to \n@Database.record() to have custom sizing of the control elements.  Even with these defaults set, the size parameter of \n@Database.record() will override the default control size, for plenty of flexibility.\n\nPlace those two functions just above the layout definition shown in the example above and then run the code again\n\n```python\nss.set_text_size(10, 1)  # Set the text/label size for all subsequent calls\nss.set_control_size(50, 1)  # set the control size for all subsequent calls\nlayout = [\n    ss.record('Restaurant', 'name'),\n    ss.record('Restaurant', 'location'),\n    ss.record('Restaurant', 'fkType', sg.Combo)]\nsub_layout = [\n    [sg.Listbox(values=(), size=(35, 10), key=\"SELECTOR.Item\", select_mode=sg.LISTBOX_SELECT_MODE_SINGLE,\n                enable_events=True),\n     sg.Col(\n         [ss.record('Item', 'name'),\n          ss.record('Item', 'fkMenu', sg.Combo),\n          ss.record('Item', 'price'),\n          ss.record('Item', 'description', sg.MLine, (30, 7))  # Override the default size for this element!\n          ])],\n    ss.record_actions('Item', False)\n]\nlayout += [[sg.Frame('Items', sub_layout)]]\nlayout += [ss.actions('Restaurant', protect=True, search=True, save=True)]\n```\n![image](https://user-images.githubusercontent.com/70232210/91287363-a71ea680-e75d-11ea-8b2f-d240c1ec2acf.png)\nYou will see that now, the controls were resized using the new sizing rules.  Notice however that the 'Description'\nfield isn't as wide as the others.  That is because we overridden the control size for just that single control.\n\nLets see one more example.  This time we will fix the oddly sized 'Description' field, as well as make the 'Restaurant' \nand 'Items' sections with their own sizing\n\n```python\n# set the sizing for the Restaurant section\nss.set_text_size(10, 1)\nss.set_control_size(90, 1)\nlayout = [\n    ss.record('Restaurant', 'name'),\n    ss.record('Restaurant', 'location'),\n    ss.record('Restaurant', 'fkType', sg.Combo)]\n# set the sizing for the Items section\nss.set_text_size(10, 1)\nss.set_control_size(50, 1)\nsub_layout = [\n    [sg.Listbox(values=(), size=(35, 10), key=\"SELECTOR.Item\", select_mode=sg.LISTBOX_SELECT_MODE_SINGLE,\n                enable_events=True),\n     sg.Col(\n         [ss.record('Item', 'name'),\n          ss.record('Item', 'fkMenu', sg.Combo),\n          ss.record('Item', 'price'),\n          ss.record('Item', 'description', sg.MLine, (50, 10))  # Override the default size for this element\n          ])],\n    ss.record_actions('Item', False)\n]\nlayout += [[sg.Frame('Items', sub_layout)]]\nlayout += [ss.actions('Restaurant', protect=True, search=True, save=True)]\n```\n![image](https://user-images.githubusercontent.com/70232210/91288080-8e62c080-e75e-11ea-8438-86035d4d6609.png)\n\n\n\n\n### Binding the window to the element\nReferencing the same example above, the window and database were bound with this one single line:\n```python\ndb = ss.Database(':memory:', 'example.sql', win) # Load in the database and bind it to win\n```\nThe above is a one-shot approach and all most users will ever need!\nThe above could have been written as:\n```python\ndb=ss.Database(':memory:', 'example.sql') # Load in the database\ndb.auto_bind(win) # automatically bind the window to the database\n```\n\ndb.auto_bind() likewise can be peeled back to it's own components and could have been written like this:\n```python\ndb.auto_add_tables()\ndb.auto_add_relationships()\ndb.auto_map_controls(win)\ndb.auto_map_events(win)\ndb.requery_all()\ndb.update_controls()\n```\n\nAnd finally, that brings us to the lowest-level functions for binding the database.\nThis is how you can MANUALLY map tables, relationships, controls and events to the database.\nThe above auto_map_* functions could have been manually achieved as follows:\n```python\n# Add the tables you want PySimpleSQL to handle.  The function db.auto_add_tables() will add all tables found in the database \n# by default.  However, you may only need to work with a couple of tables in the database, and this is how you would do that\ndb.add_table('Restaurant','pkRestaurant','name') # add the table Restaurant, with it's primary key field, and descriptive field (for comboboxes)\ndb.add_table('Item','pkItem','name') # Note: While I personally prefer to use the pk{Table} and fk{Table} naming\ndb.add_table('Type','pkType','name') #       conventions, it's not necessary for pySimpleSQL\ndb.add_table('Menu','pkMenu','name') #       These could have just as well been restaurantID and itemID for example\n\n# Set up relationships\n# Notice below that the first relationship has the last parameter to True.  This is what the ON UPDATE CASCADE constraint accomplishes.\n# Basically what it means is that then the Restaurant table is requeried, the associated Item table will automatically requery right after.\n# This is what allows the GUI to seamlessly update all of the control elements when records are changed!\n# The other relationships have that parameter set to False - they still have a relationship, but they don't need requeried automatically\ndb.add_relationship('LEFT JOIN', 'Item', 'fkRestaurant', 'Restaurant', 'pkRestaurant', True) \ndb.add_relationship('LEFT JOIN', 'Restaurant', 'fkType', 'Type', 'pkType', False)\ndb.add_relationship('LEFT JOIN', 'Item', 'fkMenu', 'Menu', 'pkMenu', False)\n\n# Map our controls\n# Note that you can map any control to any Table/field combination that you would like.\n# The {Table}.{field} naming convention is only necessary if you want to use the auto-mapping functionality of PySimpleSQL!\ndb.map_control(win['Restaurant.name'],'Restaurant','name')\ndb.map_control(win['Restaurant.location'],'Restaurant','location')\ndb.map_control(win['Restaurant.fkType'],'Type','pkType')\ndb.map_control(win['Item.name'],'Item','name')\ndb.map_control(win['Item.fkRestaurant'],'Item','fkRestaurant')\ndb.map_control(win['Item.fkMenu'],'Item','fkMenu')\ndb.map_control(win['Item.price'],'Item','price')\ndb.map_control(win['Item.description'],'Item','description')\n\n# Map out our events\n# In the above example, this was all done in the background, as we used convenience functions to add record navigation buttons.\n# However, we could have made our own buttons and mapped them to events.  Below is such an example\ndb.map_event('Edit.Restaurant.First',db['Restaurant'].First) # button control with the key of 'Edit.Restaurant.First'\n                                                             # mapped to the Table.First method\ndb.map_event('Edit.Restaurant.Previous',db['Restaurant'].Previous)\ndb.map_event('Edit.Restaurant.Next',db['Restaurant'].Next)\ndb.map_event('Edit.Restaurant.Last',db['Restaurant'].Last)\n# and so on...\n# In fact, you can use the event mapper however you want to, mapping control names to any function you would like!\n# Event mapping will be covered in more detail later...\n\n# This is the magic function which populates all of the controls we mapped!\n# For your convience, you can optionally use the function Database.set_callback('update_controls',function) to set a callback function\n# that will be called every time the controls are updated.  This allows you to do custom things like update\n# a preview image, change control parameters or just about anythong you want!\ndb.update_controls()\n```\n\nAs you can see, there is a lot of power in the auto functionality of PySimpleSQL, and you should take advantage of it any time you can.  Only very specific cases need to reach this lower level of manual configuration and mapping!\n\n# BREAKDOWN OF ADVANCED FUNCTIONALITY\nPySimpleSQL does much more than just bridge the gap between PySimpleGUI\u2122 and Sqlite databases! In full, PySimpleSQL contains:\n* Convenience functions for simplifying PySimpleGUI\u2122 layout code\n* Control binding between PySimpleGUI\u2122 controls and Sqlite database fields\n* Automatic requerying of related tables\n* Record navigation - Such as First, Previous, Next, Last, Searching and selector controls\n* Callbacks allow your own functions to expand control over your own database front ends\n* Event Mapping\n\nWe will break each of these down below to give you a better understanding of how each of these features works.\n## Convenience Functions\nThere are currently only a few convenience functions to aid in quikly creating PySimpleGUI\u2122 layout code\nDatabase.set_text_size(width,height) - Sets the PySimpleGUI\u2122 text size for subsequent calls to Database.record(). Defaults to (15,1) otherwise.\n\nDatabase.set_control_size(width, height) - Sets the PySImpleGUI\u2122 control size for subsequent calls to Database.record(). Defaults to (30,1) otherwise.\n\nDatabase.record(table, field,control_type=None,size=None,text_label=None)- This is a convenience function for creating a PySimpleGUI\u2122 text control and a PySimpleGUI\u2122 Input control inline for purposes of displaying a record from the database.  This function also creates the naming convention (table.field) in the control's key parameter that PySimpleSQL uses for advanced automatic functionality. The optional control_type parameter allows you to bind control types other than Input to a database field.  Checkboxes, listboxes and other controls entered here will override the default Input control. The size parameter will override the default control size that was set with Database.set_control_size().  Lastly, the text_label parameter will previx a text field before the control.\n\nDatabase.actions()-\n\n## Control Binding\n\n## Automatic Requerying\n\n## Record Navigation\nPySimpleSQL includes a convenience function for adding record navigation buttons to your project.  For lower level control or a custom look, you may want to learn how to do this on your own.  Lets start with the convenience function and work backwards from there to see how you can implement your own record navigation controls.\n\nThe convenience function PySimpleSQL.actions() is a swiss army knife when it comes to generating PySimpleGUI\u2122 layout code for your record navigation controls.  With it, you can add First, Previous, Next and Last record navigation buttons, a search box, edit protection modes, and record actions such as Insert, Save and Delete (Or any combination of these items).  Under the hood, the actions() convenience function uses the Event Mapping features of PySimpleSQL, and your own code can do this too!\nSee the code below on example usage of the PySimpleSQL.actions() convenience function\n\n```python\n#!/usr/bin/python3\nimport PySimpleGUI as sg\nimport PySimpleSQL as ss\n\n# Create a small table just for demo purposes\nsql = '''\nCREATE TABLE \"Fruit\"(\n    \"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n\t\"name\" TEXT DEFAULT \"New Fruit\"\n);\nINSERT INTO \"Fruit\" (\"name\") VALUES (\"Apple\");\nINSERT INTO \"Fruit\" (\"name\") VALUES (\"Orange\");\nINSERT INTO \"Fruit\" (\"name\") VALUES (\"Banana\");\nINSERT INTO \"Fruit\" (\"name\") VALUES (\"Kiwi\");\n'''\n\n# PySimpleGUI\u2122 layout code to create your own navigation buttons\ntable = 'Fruit'  # This is the table in the database that you want to navigate\nlayout = [\n    ss.record(table, 'name', label='Fruit Name'),  # PySimpleSQL.record() convenience function for easy record creation!\n    ss.actions(table)  # PySimpleSQL.actions() convenience function for easy navigation controls!\n]\n\nwin = sg.Window('Navigation demo', layout, finalize=True)\n# note: Since win was passed as a parameter, binding is automatic (including event mapping!)\n# Also note, in-memory databases can be created with \":memory:\"!\ndb = ss.Database(':memory:', win, sql_commands=sql)\n\nwhile True:\n    event, values = win.read()\n    if db.process_events(event, values):  # <=== let PySimpleSQL process its own events! Simple!\n        print(f'PySimpleDB event handler handled the event {event}!')\n    elif event == sg.WIN_CLOSED or event == 'Exit':\n        db = None  # <= ensures proper closing of the sqlite database and runs a database optimization\n        break\n    else:\n        print(f'This event ({event}) is not yet handled.')\n```\nSimple!\nBut as stated earlier, PySimpleSQL.actions is a swiss army knife!  Experiment with the code ablove, trying all of these variations to see all of goodness this convenience functions provides!\n\n```python\nss.actions(table, search=False)\nss.actions(table, save=False)\nss.actions(table, edit_protect=False)\nss.actions(table, insert=False)\nss.actions(table, delete=False, save=False) \n```\n\n\nSee example below of how your can make your own record navigation controls instead of using the PySimpleSQL.actions() convenience function:\n\n```python\n#!/usr/bin/python3\nimport PySimpleGUI as sg\nimport PySimpleSQL as ss\n\n# Create a small table just for demo purposes\nsql = '''\nCREATE TABLE \"Fruit\"(\n    \"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n\t\"name\" TEXT DEFAULT \"New Fruit\"\n);\nINSERT INTO \"Fruit\" (\"name\") VALUES (\"Apple\");\nINSERT INTO \"Fruit\" (\"name\") VALUES (\"Orange\");\nINSERT INTO \"Fruit\" (\"name\") VALUES (\"Banana\");\nINSERT INTO \"Fruit\" (\"name\") VALUES (\"Kiwi\");\n'''\n\n# PySimpleGUI\u2122 layout code to create your own navigation buttons\ntable = 'Fruit'  # This is the table in the database that you want to navigate\nlayout = [\n    ss.record(table, 'name', label='Fruit Name'),  # PySimpleSQL.record() convenience function for easy record creation!\n    # Below we will create navigation buttons manually, naming the key so that the automatic event mapper will map the events\n    [sg.Button('<<', key=f'Event.{table}.First', size=(1, 1)),\n     sg.Button('<', key=f'Event.{table}.Previous', size=(1, 1)),\n     sg.Button('>', key=f'Event.{table}.Next', size=(1, 1)),\n     sg.Button('>>', key=f'Event.{table}.Last', size=(1, 1))\n     ]\n]\n\nwin = sg.Window('Navigation demo', layout, finalize=True)\n# note: Since win was passed as a parameter, binding is automatic (including event mapping!)\n# Also note, in-memory databases can be created with \":memory:\"!\ndb = ss.Database(':memory:', win, sql_commands=sql)\n\nwhile True:\n    event, values = win.read()\n    if db.process_events(event, values):  # <=== let PySimpleSQL process its own events! Simple!\n        print(f'PySimpleDB event handler handled the event {event}!')\n    elif event == sg.WIN_CLOSED or event == 'Exit':\n        db = None  # <= ensures proper closing of the sqlite database and runs a database optimization\n        break\n    else:\n        print(f'This event ({event}) is not yet handled.')\n```\nNotice the naming convention of the PySimpleGUI\u2122 contrl keys in the example above.  They are in the format of \"Event.table_name.event_type\".  This is so that the Automatic event mapping of PySimpleSql will handle these.  Valid event_types include: Insert, Save, Delete, First, Previous, Next, Last and Search.\n\nPeeling this back further, you can rewrite the same without the special naming convention used by the automatic event mapper, and instead name the keys however you would like, then manually map them in the event mapper...\n\n```python\n#!/usr/bin/python3\nimport PySimpleGUI as sg\nimport PySimpleSQL as ss\n\n# Create a small table just for demo purposes\nsql = '''\nCREATE TABLE \"Fruit\"(\n    \"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n\t\"name\" TEXT DEFAULT \"New Fruit\"\n);\nINSERT INTO \"Fruit\" (\"name\") VALUES (\"Apple\");\nINSERT INTO \"Fruit\" (\"name\") VALUES (\"Orange\");\nINSERT INTO \"Fruit\" (\"name\") VALUES (\"Banana\");\nINSERT INTO \"Fruit\" (\"name\") VALUES (\"Kiwi\");\n'''\n\n# PySimpleGUI\u2122 layout code to create your own navigation buttons\ntable = 'Fruit'  # This is the table in the database that you want to navigate\nlayout = [\n    ss.record(table, 'name', label='Fruit Name'),  # PySimpleSQL.record() convenience function for easy record creation!\n    # Below we will create navigation buttons manually, naming the key so that the automatic event mapper will map the events\n    [\n        sg.Button('<<', key=f'btnFirst', size=(1, 1)),\n        sg.Button('<', key=f'btnPrevious', size=(1, 1)),\n        sg.Button('>', key=f'btnNext', size=(1, 1)),\n        sg.Button('>>', key=f'btnLast', size=(1, 1))\n    ]\n]\n\nwin = sg.Window('Navigation demo', layout, finalize=True)\n# note: Since win was passed as a parameter, binding is automatic (including event mapping!)\n# Also note, in-memory databases can be created with \":memory:\"!\ndb = ss.Database(':memory:', win, sql_commands=sql)\n\n# Manually map the events, since we did not adhere to the naming convention that the automatic mapper expects\ndb.map_event('btnFirst', db[table].first)\ndb.map_event('btnPrevious', db[table].previous)\ndb.map_event('btnNext', db[table].next)\ndb.map_event('btnLast', db[table].last)\n\nwhile True:\n    event, values = win.read()\n    if db.process_events(event, values):  # <=== let PySimpleSQL process its own events! Simple!\n        print(f'PySimpleDB event handler handled the event {event}!')\n    elif event == sg.WIN_CLOSED or event == 'Exit':\n        db = None  # <= ensures proper closing of the sqlite database and runs a database optimization\n        break\n    else:\n        print(f'This event ({event}) is not yet handled.')\n```\n\nLastly, you can rewrite the same and handle the events yourself instead of relying on PySimpleSQL's event mapper\n\n```python\n#!/usr/bin/python3\nimport PySimpleGUI as sg\nimport PySimpleSQL as ss\n\n# Create a small table just for demo purposes\nsql = '''\nCREATE TABLE \"Fruit\"(\n    \"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n\t\"name\" TEXT DEFAULT \"New Fruit\"\n);\nINSERT INTO \"Fruit\" (\"name\") VALUES (\"Apple\");\nINSERT INTO \"Fruit\" (\"name\") VALUES (\"Orange\");\nINSERT INTO \"Fruit\" (\"name\") VALUES (\"Banana\");\nINSERT INTO \"Fruit\" (\"name\") VALUES (\"Kiwi\");\n'''\n\n# PySimpleGUI\u2122 layout code to create your own navigation buttons\ntable = 'Fruit'  # This is the table in the database that you want to navigate\nlayout = [\n    ss.record(table, 'name', label='Fruit Name'),  # PySimpleSQL.record() convenience function for easy record creation!\n    # Below we will create navigation buttons manually, naming the key so that the automatic event mapper will map the events\n    [\n        sg.Button('<<', key=f'btnFirst', size=(1, 1)),\n        sg.Button('<', key=f'btnPrevious', size=(1, 1)),\n        sg.Button('>', key=f'btnNext', size=(1, 1)),\n        sg.Button('>>', key=f'btnLast', size=(1, 1))\n    ]\n]\n\nwin = sg.Window('Navigation demo', layout, finalize=True)\n# note: Since win was passed as a parameter, binding is automatic (including event mapping!)\n# Also note, in-memory databases can be created with \":memory:\"!\ndb = ss.Database(':memory:', win, sql_commands=sql)\n\nwhile True:\n    event, values = win.read()\n    # Manually handle our record selector events, bypassing the event mapper completely\n    if db.process_events(event, values):  # <=== let PySimpleSQL process its own events! Simple!\n        print(f'PySimpleDB event handler handled the event {event}!')\n    elif event == 'btnFirst':\n        db[table].first()\n    elif event == 'btnPrevious':\n        db[table].previous()\n    elif event == 'btnNext':\n        db[table].next()\n    elif event == 'btnLast':\n        db[table].last()\n    elif event == sg.WIN_CLOSED or event == 'Exit':\n        db = None  # <= ensures proper closing of the sqlite database and runs a database optimization\n        break\n    else:\n        print(f'This event ({event}) is not yet handled.')\n\n```\n\nWhether you want to use the PySimpleSQL.actions() convenience function, write your own navigation button layout code, use the auto event mapper, manually map the events, or handle the events yourself, you have plenty of options for flexibility writing your navigation button code!  Of course, the convenience function is very flexible and has attractive icons in the buttons, and really should be used in most cases.\n## Callbacks\n\n## Event Mapping\n\n*",
    "bugtrack_url": null,
    "license": "",
    "summary": "sqlite3 database binding for PySimpleGUI",
    "version": "0.0.2",
    "split_keywords": [
        "sql",
        "sqlite",
        "database",
        "application",
        "front-end",
        "access",
        "libre",
        "office",
        "gui",
        "pysimplegui"
    ],
    "urls": [
        {
            "comment_text": "",
            "digests": {
                "md5": "254af05ae3a63dc823d896b2733f8038",
                "sha256": "a3596a7aa152f973ac26629e3a1585497350498a78544e81e40a87b12edf61eb"
            },
            "downloads": -1,
            "filename": "PySimpleSQL-0.0.2.tar.gz",
            "has_sig": false,
            "md5_digest": "254af05ae3a63dc823d896b2733f8038",
            "packagetype": "sdist",
            "python_version": "source",
            "requires_python": null,
            "size": 23967,
            "upload_time": "2021-05-04T18:48:33",
            "upload_time_iso_8601": "2021-05-04T18:48:33.022787Z",
            "url": "https://files.pythonhosted.org/packages/d3/f7/959d5091dcb5a287992426fea7f1b9862b2b39c93684814c8ad04c859cb8/PySimpleSQL-0.0.2.tar.gz",
            "yanked": false,
            "yanked_reason": null
        }
    ],
    "upload_time": "2021-05-04 18:48:33",
    "github": true,
    "gitlab": false,
    "bitbucket": false,
    "github_user": null,
    "github_project": "PySimpleSQL",
    "error": "Could not fetch GitHub repository",
    "lcname": "pysimplesql"
}
        
Elapsed time: 0.23716s