Creates an object representation of a given database, and a set of data aware PySide6 widgets on top of it. Master detail relationships are derived from the foreign keys already defined in the database.
The widgets follow Delphi's Data Controls palette: the relationship lives on the dataset, and every control attached to a data source follows the current record without knowing about the other controls.
SQLite, PostgreSQL, MySQL and MariaDB are implemented. Column information, primary keys and foreign keys are extracted automatically.
pip install PySide6
pip install psycopg2-binary # only for postgresql
pip install PyMySQL # only for mysql or mariadb
Run the demo:
python demo_dbcontrols.py
| Module | Delphi counterpart | What it holds |
|---|---|---|
dbwidgets |
TDatabase |
DB, DBSQLite, DBPostgres, DBMySQL / DBMariaDB -- the extracted schema |
dbwidgets.dataset |
TDataSet, TDataSource, TQuery |
DataSet, QueryDataSet, DataSource, Field -- cursor, edit state, master detail |
dbwidgets.dbcontrols |
Data Controls palette | the data aware widgets |
dbwidgets.binding |
-- | bind, ui_loader -- wiring a form from Designer |
dbwidgets.project |
-- | Project -- reading dbwidgets.json |
dbwidgets.connection |
-- | ConnectionDialog -- setting the connection up |
dbwidgets.designer_plugin |
-- | the Qt Designer plugin |
dbwidgets.widgets |
-- | the original API, kept for compatibility |
| Delphi | dbwidgets |
|---|---|
TDBText |
DBText |
TDBEdit |
DBEdit |
TDBMemo |
DBMemo |
TDBRichEdit |
DBRichEdit |
TDBImage |
DBImage |
TDBCheckBox |
DBCheckBox |
TDBRadioGroup |
DBRadioGroup |
TDBComboBox |
DBComboBox |
TDBListBox |
DBListBox |
TDBLookupComboBox |
DBLookupComboBox |
TDBLookupListBox |
DBLookupListBox |
TDBNavigator |
DBNavigator |
TDBGrid |
DBGrid |
TColumn |
GridColumn |
TDBCtrlGrid |
DBCtrlGrid |
TField (fkCalculated) |
DataSet.add_calculated_field |
TField (fkLookup) |
DataSet.add_lookup_field |
The whole Data Controls palette is covered except TDBChart, which would
pull in a plotting library.
CREATE TABLE city (
id INTEGER NOT NULL,
name VARCHAR,
photo BLOB,
PRIMARY KEY (id)
);
CREATE TABLE district (
id INTEGER NOT NULL,
name VARCHAR,
city_id INTEGER,
PRIMARY KEY (id),
FOREIGN KEY(city_id) REFERENCES city (id)
);
First, create the database object by giving connection parameters. For sqlite, file name is sufficient.
db = DBSQLite("test.db")
db.extract()
From that point, db has a dictionary of tables, table names being the key
value. A table has a dictionary of columns, column names being the key value.
If a foreign key is defined for any column, the referring table name is
foreign_key_table and the referring column name is foreign_key_column as
attributes of the column object.
customer = db.record("customers", "id", 123)
A DataSet is a cursor over one table. It has a current record, an edit state
and, optionally, a master.
from dbwidgets.dataset import DataSet, DataSource
city = DataSet(db, "city", order_by='"name"').open()
city_source = DataSource(city)
district = DataSet(db, "district")
district.set_master_source(city_source)
district.open()
set_master_source was called without column names, so the link was read from
the foreign key district.city_id -> city.id. Pass master_fields and
detail_fields when you want to name them yourself.
Moving the master moves the detail:
city.locate("name", "Ankara")
print(district.record_count) # districts of Ankara
Navigation follows the Delphi idiom:
city.first()
while not city.eof:
print(city["name"])
city.next()
So does editing. The dataset has the four states INACTIVE, BROWSE, EDIT
and INSERT, and every statement it runs is parameter bound:
city.edit()
city["name"] = "Ankara"
city.post() # or city.cancel()
district.insert()
district["name"] = "Yeni ilce" # city_id is prefilled from the master
district.post()
district.delete()
Every control takes a data source and a field name, and there is nothing else to wire up:
from dbwidgets.dbcontrols import DBGrid, DBNavigator, DBEdit, DBText
DBGrid(parent, city_source) # master grid
DBNavigator(parent, city_source) # first/prior/next/last/insert/...
DBText(parent, city_source, "name") # shows the selected city
DBGrid(parent, district_source) # shows its districts
DBEdit(parent, district_source, "name") # edits the current district
Selecting a row in the grid moves the dataset cursor, and moving the cursor from anywhere else moves the selection, so the grid, the navigator and the edit controls always sit on the same record. The navigator enables and disables its buttons from the dataset state, the way the VCL one does.
A grid shows every column by default. Name them to control the header, the
width, the alignment and the formatting, the way TDBGrid's Columns editor
does:
from dbwidgets.dbcontrols import DBGrid, GridColumn
DBGrid(parent, city_source, columns=[
GridColumn("id", title="No", width=60, alignment=Qt.AlignRight),
GridColumn("name", title="İl adı"),
GridColumn("salary", format="{:.2f}", read_only=True),
])
Clicking a header reorders the dataset; click again to reverse it. Pass
sortable=False to turn that off.
city.photo in test.db holds a small photograph of every city, so
DBImage has something real to show:
DBImage(parent, city_source, "photo", stretch=True)
DBImage reads a blob and draws it, and unless it is read only it writes one
back. The VCL shortcuts work: Ctrl+C, Ctrl+V, Ctrl+X and Delete. So does
dropping an image file on it. The context menu carries the same commands plus
load and save, and the same things are available from code:
image.load_from_file("view.jpg")
image.save_to_file("view.jpg")
image.paste() # from the clipboard
image.copy()
image.cut()
image.clear_image() # sets the field to NULL
stretch scales the picture to the control, center places it when it is
not stretched, and image_format chooses what a written picture is encoded
as. With auto_display off the control does not decode the blob until
load_picture() is called or the control is double clicked, which is what
you want for a table of large pictures.
The photographs are fetched by tools/make_city_photos.py. They are the lead
images of the Turkish Wikipedia articles for each province, taken only when
the file is hosted on Wikimedia Commons, which accepts freely licensed work
only. Each is cropped to 4:3 and reduced to the same 160x120, and every
author and licence is listed in CITY_PHOTO_CREDITS.md, as those licences
require. To rebuild the column, at a different size if you like:
python tools/make_city_photos.py --size 200x150 --quality 80
A blob shows as its size, not its bytes, anywhere a grid or a label would otherwise print it, and a blob column cannot be edited in place.
DBImage reads a blob field and, unless it is read only, writes one back.
It has a context menu with copy, paste, load and clear, and the same
operations are available from code:
image = DBImage(parent, pics_source, "photo", stretch=True)
image.load_from_file("photo.png")
image.paste() # from the clipboard
image.clear_image() # sets the field to NULL
DataSet covers one table. For anything else, including joins, use
QueryDataSet. Parameters are written :name and are filled either from
params or from the current record of the master source:
from dbwidgets.dataset import QueryDataSet
report = QueryDataSet(db, '''
select c.name as il, d.name as ilce
from district d join city c on c.id = d.city_id
where c.id = :id
order by d.name
''')
report.set_master_source(city_source)
report.open()
The rows of a join cannot be written back to a single table, so a query dataset is read only: editing is refused and every control bound to it goes read only on its own.
DBCtrlGrid repeats a panel down the page, one per record. Write a builder
that fills a single panel, and bind its controls to the panel's own source:
def build_card(panel, source):
layout = QFormLayout(panel)
layout.addRow("No", DBText(panel, source, "id"))
layout.addRow("Ad", DBEdit(panel, source, "name"))
DBCtrlGrid(parent, district_source, build_card, rows=5)
Delphi paints its panel once per record, so only the current one is live.
Here each panel has its own RecordSource, so the controls on all of them
are real and editable. Clicking a panel makes its record current.
A dataset can carry fields that are not columns of the table. They show up in grids and controls like any other field, and they are read only:
# value pulled from another dataset, Delphi's fkLookup
district.add_lookup_field("il_adi", "city_id", city_source, "id", "name")
# value worked out per record, Delphi's fkCalculated
district.add_calculated_field(
"etiket", lambda record: f"{record['il_adi']} / {record['name']}")
DBText(parent, district_source, "etiket")
insert() leaves the database defaults to the database, because some
drivers report them as SQL expressions rather than values. To prefill a new
record yourself, the counterpart of Delphi's DefaultExpression:
district.defaults["name"] = "isimsiz"
While a dataset is inserting, a grid on it grows a blank row at the bottom, the way Delphi's does, and that row is where the new record is typed.
DBLookupComboBox works two ways. With a datasource and a datafield it
edits a foreign key column of the current record:
DBLookupComboBox(parent, list_source=city_source, key_field="id",
list_field="name", datasource=district_source,
datafield="city_id", navigates=False)
Without them it is a selector over the list source, moving that dataset's cursor, so every detail follows the selection:
DBLookupComboBox(parent, list_source=city_source,
key_field="id", list_field="name")
Every control can be laid out in Designer, and the form can show real data while you are designing it.
Point Designer at the designer_plugins folder and the controls appear in a
dbwidgets group, with dataSourceName, dataField and the rest in the
property editor:
PYSIDE_DESIGNER_PLUGINS=/path/to/dbwidgets/designer_plugins pyside6-designer
It has to be that folder, not the repository. Designer executes every
Python file in the directory it is given, so pointing it at the repository
would run the demo applications and hang. That is also why
designer_plugins/ holds one file and nothing else.
Two more things about how Designer loads a Python plugin, both of which bite
silently: it runs the file without setting __file__, and it runs it with
separate globals and locals, so a function defined in that file cannot see
the imports above it. register_dbwidgets.py is written around both.
On macOS, pyside6-designer may stop with a dyld error about a missing
Python3.framework under /Applications/Xcode.app. That happens when only
the command line tools are installed. Run Designer directly and point it at
the framework that is actually there:
FRAMEWORKS=/Library/Developer/CommandLineTools/Library/Frameworks
export DYLD_INSERT_LIBRARIES=$FRAMEWORKS/Python3.framework/Versions/3.9/Python3
export PYSIDE_DESIGNER_PLUGINS=$PWD/designer_plugins
.venv/lib/python3.9/site-packages/PySide6/Designer.app/Contents/MacOS/Designer designer_demo.ui
Without the plugin the controls still work through promotion: drop the base
widget, right click, Promote to..., class DBEdit, header
dbwidgets.dbcontrols.
A form names its sources instead of holding them. A project file next to the
.ui says what those names mean:
// dbwidgets.json
{
"database": {"driver": "sqlite", "filename": "test.db"},
"sources": {
"city": {"table": "city", "order_by": "\"name\""},
"district": {"table": "district", "master": "city"},
"report": {"sql": "select ... where c.id = :id", "master": "city"}
}
}
The plugin opens it read only and hands the sources to the controls, so
typing district into dataSourceName fills the grid with real rows there
and then, master detail included. The last part of dbwidgets_demo.mp4
shows exactly that, inside a real Designer. Read only means laying out a form can
never change data. Set DBWIDGETS_PROJECT to point at a specific file,
otherwise the plugin walks up from the working directory looking for
dbwidgets.json.
The plugin puts a dbwidgets menu on Designer's menu bar. Data sources... opens a dialog with two tabs:
- Connection picks the driver (SQLite, PostgreSQL, MySQL, MariaDB), the database file or the server details, and Test connection says straight away whether it works and how many tables it found. A password is only written to the project file if you tick the box; otherwise it is used for the design time connection and forgotten.
- Sources lists the tables. Tick the ones the form needs, name them, and set an order. The master of each table is filled in from its foreign key and can be changed.
Saving writes dbwidgets.json and reopens the sources, so the widgets on
the open form refill immediately with data from the new database. Nothing
has to be restarted. Reload project does the same for a file that was
edited by hand.
The dialog is not tied to Designer, so a project can be set up from a script too:
from dbwidgets.connection import ConnectionDialog
ConnectionDialog.edit_project("dbwidgets.json")
pyside6-uic drops properties it does not recognise, so the
dataSourceName set in Designer never reaches the generated Python. Load
the .ui at run time instead, which keeps them:
from dbwidgets.binding import bind, ui_loader
from dbwidgets.project import Project
form = ui_loader().load("designer_demo.ui")
bind(form, Project("dbwidgets.json").sources)
That is the whole application: the form says which source each widget wants,
the project file says what the sources are, and bind puts them together.
designer_demo.ui and designer_demo.py in the repository are a working
example.
If you would rather compile with pyside6-uic, promotion still works, you
just wire the sources yourself after setupUi:
self.setupUi(self)
self.nameEdit.set_data_source(self.district_source, "name")
dbwidgets.widgets still provides DBComboBox, DBTableWidget and
DBNavigatorWidget with their original constructors, signals and methods.
They are now built on top of the dataset layer. testapp.py is the example:
self.db = DBSQLite("test.db")
self.db.extract()
self.city = DBComboBox(self.widget1, self.db, "city", "name", "id", 34)
self.district = DBComboBox(self.widget2, self.db, "district", "name", "id")
self.district.setMaster(self.city, "city_id")
self.districtlist = DBTableWidget(self.widget3, self.db, "district")
self.citylist = DBTableWidget(self.widget4, self.db, "city")
self.districtlist.setMaster(self.citylist, "city_id")
DBComboBox(placeholder, db, table, textcolumn, idcolumn, default_id) fills a
combo from a table, setMaster(masterwidget, mycolumn) connects a detail
widget to a master one, and DBNavigatorWidget(placeholder, db, table) now
builds an editor per column plus a working button row.
docs/ holds a walkthrough that goes from an empty directory to a form drawn
in Qt Designer showing live data, and a reference for every class.
dbwidgets.pdf is the same thing as one file.
Every code block in the walkthrough is a file in examples/, included in the
document as it is on disk, and each one is run by the test suite, so the code
in the documentation is the code that works:
| Example | Shows |
|---|---|
01_dataset_basics.py |
the schema, a cursor, edit and post -- no widgets |
02_first_form.py |
a grid, a navigator and two editors on one source |
03_master_detail.py |
a detail linked through the foreign key |
04_lookup_and_picture.py |
the two jobs of a lookup combo, and a blob |
05_query_and_fields.py |
a join, a lookup field, a calculated field |
06_project_and_bind.py |
the same thing described in dbwidgets.json |
07_designer_form.py |
loading and binding a form built in Designer |
08_other_databases.py |
PostgreSQL, MySQL and MariaDB |
Run one with:
python examples/01_dataset_basics.py
To build the documentation. The PDF is drawn by rst2pdf, so no LaTeX is needed:
pip install sphinx sphinx_rtd_theme rst2pdf
cd docs
python -m sphinx -b html source _build/html
python -m sphinx -b pdf source _build/pdf
The pictures of Qt Designer in the walkthrough are not screenshots taken by hand. Designer is a Qt application, so it is run under the offscreen platform and the plugin, which lives inside its process, photographs it:
python tools/capture_designer_docs.py
pip install pytest
pytest
The suite runs headless, on a private copy of test.db, so it never touches
the database file in the repository.
The postgresql tests need a server. pgserver brings its own binaries and
runs them over a unix socket, so nothing has to be installed system wide:
pip install pgserver psycopg2-binary
pytest
Without those two packages tests/test_postgres.py skips itself.
Delphi and VCL are trademarks of Embarcadero Technologies, Inc. Borland is a trademark of its respective owner. This project is not affiliated with, endorsed by, or sponsored by any of them. Those names are used here only to describe which well known components the classes in this library are modelled on. dbwidgets is an independent implementation written from scratch; no Delphi, VCL or Lazarus source code was used.
dbwidgets_demo.mp4 walks through the whole thing, from git clone to a
form built in Qt Designer: every control with real rows in it, master detail
and editing, and the Designer integration with live data.
It is generated, not recorded, so it can be rebuilt after any change:
pip install PySide6 # and ffmpeg on the path
python tools/make_demo_video.py
The terminal panels hold the real output of the commands that were run, and
the widget panels are the real controls rendered offscreen against
test.db. Nothing in it is mocked up.
The earlier recording of the original API is still here: