databased 4.3.5

Wrapper for the standard library Sqlite3 module to make setting up and using a database quicker and easier.

Databased

Databased is a module that wraps the standard library SQLite3 module to streamline integrating a database into a project.

Install with: pip install databased

Usage

Creation and Connections

from databased import Databased

# for reference, "chi.db" is database of Chicago food inspections and business licenses
db = Databased("chi.db") # The file will be created if it doesn't exist

You can call db.connect() manually, but generally you shouldn't need to.
All database functions are built on the db.query() function, which will open a connection if one isn't already established.
e.g. Accessing the db.tables property uses the db.query() function and opens a connection for you

print(*db.tables, sep="\n")

Output:

business_addresses
businesses
license_codes
license_statuses
licenses
facility_types
risk_levels
facility_addresses
inspected_businesses
inspection_types
result_types
inspections
violation_types
violations

db.query() executes SQL strings and returns the results as a list of dictionaries

print(
    *db.query("SELECT * FROM businesses WHERE legal_name LIKE 'z%' LIMIT 5;"), sep="\n"
)

Output:

{'account_number': 106, 'legal_name': 'Zaven, Inc.', 'dba': 'Zaven / Lepetit Paris', 'address_id': 6880}
{'account_number': 113, 'legal_name': 'Zanies Comedy Clubs, Inc.', 'dba': 'Zanies Comedy Club', 'address_id': 5702}
{'account_number': 122, 'legal_name': 'Ziemek Corporation, Inc.', 'dba': 'The Thirsty Tavern', 'address_id': 146144}
{'account_number': 1918, 'legal_name': 'Zimmies Inc #8', 'dba': 'Original Pancake House', 'address_id': 143541}
{'account_number': 3007, 'legal_name': 'Zikainan Nursing Home Inc', 'dba': 'All American Nursing Home', 'address_id': 155957}

When a connection is no longer needed, it will need to be manually closed.

db.close()

By default the database will be committed when db.close() is called.
This can be prevented by setting commit_on_close to False in either the Databased constructor or through the property db.commit_on_close.

db = Databased("chi.db", commit_on_close=False)
# or
db.commit_on_close = False

The database can always be committed manually with db.commit().

Using Databased with a context manager will call the close() method for you (and commit the database if commit_on_close is True)

with Databased("chi.db") as db:
    print(f"{db.connected=}")
    print(*db.get_columns("businesses"), sep="\n")
print(f"{db.connected=}")

Output:

db.connected=True
account_number
legal_name
dba
address_id
db.connected=False

Tables and Columns

with Databased("chi.db") as db:
    db.create_table(
        "inspectors",
        "id INTEGER PRIMARY KEY AUTOINCREMENT",
        "first_name TEXT",
        "last_name TEXT",
        "ward INTEGER",
    )
    db.insert(
        "inspectors",
        ("first_name", "last_name", "ward"),
        [("Billy", "Bob", 1), ("Jenna", "Jones", 33), ("Tiny", "Tim", 25)],
    )
    print(db.to_grid(db.select("inspectors")))
    print()
    # ---------------------------------------------------------
    db.rename_table("inspectors", "employees")
    db.add_column("employees", "title TEXT DEFAULT inspector")
    print(db.to_grid(db.select("employees")))
    print()
    # ---------------------------------------------------------
    db.drop_column("employees", "title")
    db.rename_table("employees", "inspectors")
    print(db.to_grid(db.select("inspectors")))
    print()
    db.drop_table("inspectors")

Output:

+------+--------------+-------------+--------+
| id   | first_name   | last_name   | ward   |
+======+==============+=============+========+
| 1    | Billy        | Bob         | 1      |
+------+--------------+-------------+--------+
| 2    | Jenna        | Jones       | 33     |
+------+--------------+-------------+--------+
| 3    | Tiny         | Tim         | 25     |
+------+--------------+-------------+--------+

+------+--------------+-------------+--------+-----------+
| id   | first_name   | last_name   | ward   | title     |
+======+==============+=============+========+===========+
| 1    | Billy        | Bob         | 1      | inspector |
+------+--------------+-------------+--------+-----------+
| 2    | Jenna        | Jones       | 33     | inspector |
+------+--------------+-------------+--------+-----------+
| 3    | Tiny         | Tim         | 25     | inspector |
+------+--------------+-------------+--------+-----------+

+------+--------------+-------------+--------+
| id   | first_name   | last_name   | ward   |
+======+==============+=============+========+
| 1    | Billy        | Bob         | 1      |
+------+--------------+-------------+--------+
| 2    | Jenna        | Jones       | 33     |
+------+--------------+-------------+--------+
| 3    | Tiny         | Tim         | 25     |
+------+--------------+-------------+--------+

Select

Moderately complex queries can be executed with db.select().
More advanced queries will need to be written out and executed directly with db.query().
Example using all available db.select() parameters:

with Databased("chi.db") as db:
    print(
        db.to_grid(
            db.select(
                table="inspections",
                columns=[
                    "inspections.license_number",
                    "businesses.legal_name",
                    "result_types.id",
                    "result_types.description",
                    "business_addresses.ward",
                    "COUNT(*) AS num_inspections",
                ],
                joins=[
                    "INNER JOIN result_types ON inspections.result_type_id = result_types.id",
                    "INNER JOIN licenses ON inspections.license_number = licenses.license_number",
                    "INNER JOIN businesses ON licenses.account_number = businesses.account_number",
                    "INNER JOIN business_addresses ON businesses.address_id = business_addresses.id",
                ],
                where="business_addresses.ward IN (1, 10, 20, 40) OR result_types.id < 5",
                group_by="inspections.license_number",
                having="num_inspections > 10",
                order_by="num_inspections DESC",
                limit=5,
            )
        )
    )
    print("<==equivalent==>")
    print(
        db.to_grid(
            db.query(
                """
            SELECT 
            inspections.license_number, businesses.legal_name, 
            result_types.id, result_types.description, 
            business_addresses.ward, COUNT(*) as num_inspections
            FROM inspections
            INNER JOIN result_types ON inspections.result_type_id = result_types.id
            INNER JOIN licenses ON inspections.license_number = licenses.license_number
            INNER JOIN businesses ON licenses.account_number = businesses.account_number
            INNER JOIN business_addresses ON businesses.address_id = business_addresses.id
            WHERE business_addresses.ward IN (1, 10, 20, 40) OR result_types.id < 5
            GROUP BY inspections.license_number
            HAVING num_inspections > 10
            ORDER BY num_inspections DESC
            LIMIT 5;
            """
            )
        )

Output:

+------------------+-------------------------+------+--------------------+--------+-------------------+
| license_number   | legal_name              | id   | description        | ward   | num_inspections   |
+==================+=========================+======+====================+========+===================+
| 2583423          | Meadowflour Llc         | 7    | Pass W/ Conditions | 40     | 18                |
+------------------+-------------------------+------+--------------------+--------+-------------------+
| 2594606          | Hz Ops Holdings Inc     | 4    | Not Ready          | 6      | 17                |
+------------------+-------------------------+------+--------------------+--------+-------------------+
| 2405951          | Wendy's Properties, Llc | 7    | Pass W/ Conditions | 10     | 17                |
+------------------+-------------------------+------+--------------------+--------+-------------------+
| 833              | Steve Ziemek            | 7    | Pass W/ Conditions | 10     | 17                |
+------------------+-------------------------+------+--------------------+--------+-------------------+
| 55418            | 2053 W. Division Inc.   | 5    | Out Of Business    | 1      | 16                |
+------------------+-------------------------+------+--------------------+--------+-------------------+
<==equivalent==>
+------------------+-------------------------+------+--------------------+--------+-------------------+
| license_number   | legal_name              | id   | description        | ward   | num_inspections   |
+==================+=========================+======+====================+========+===================+
| 2583423          | Meadowflour Llc         | 7    | Pass W/ Conditions | 40     | 18                |
+------------------+-------------------------+------+--------------------+--------+-------------------+
| 2594606          | Hz Ops Holdings Inc     | 4    | Not Ready          | 6      | 17                |
+------------------+-------------------------+------+--------------------+--------+-------------------+
| 2405951          | Wendy's Properties, Llc | 7    | Pass W/ Conditions | 10     | 17                |
+------------------+-------------------------+------+--------------------+--------+-------------------+
| 833              | Steve Ziemek            | 7    | Pass W/ Conditions | 10     | 17                |
+------------------+-------------------------+------+--------------------+--------+-------------------+
| 55418            | 2053 W. Division Inc.   | 5    | Out Of Business    | 1      | 16                |
+------------------+-------------------------+------+--------------------+--------+-------------------+

Update

with Databased("chi.db", commit_on_close=False) as db:
    print(db.to_grid(db.select("businesses", where="dba LIKE 'deli %'")))
    num_rows = db.update(
        "businesses", "dba", "deli BreadMeat City", "dba LIKE 'deli %'"
    )
    print(f"num rows updated: {num_rows}")
    print(db.to_grid(db.select("businesses", where="dba LIKE 'deli %'")))

Output:

+------------------+-------------------------+---------------------------------+--------------+
| account_number   | legal_name              | dba                             | address_id   |
+==================+=========================+=================================+==============+
| 17214            | Emil Korpacki, Inc.     | Deli On Rice                    | 110271       |
+------------------+-------------------------+---------------------------------+--------------+
| 348935           | Deli King Inc.          | Deli King Inc.                  | 17090        |
+------------------+-------------------------+---------------------------------+--------------+
| 389169           | Pheidias, Inc.          | Deli Boutique, Wine And Spirits | 138721       |
+------------------+-------------------------+---------------------------------+--------------+
| 391766           | Ted's Deli & More, Inc. | Deli & More                     | 142080       |
+------------------+-------------------------+---------------------------------+--------------+
| 421955           | Deli Flavor, Inc.       | Deli Flavor                     | 5057         |
+------------------+-------------------------+---------------------------------+--------------+
num rows updated: 5
+------------------+-------------------------+---------------------+--------------+
| account_number   | legal_name              | dba                 | address_id   |
+==================+=========================+=====================+==============+
| 17214            | Emil Korpacki, Inc.     | deli BreadMeat City | 110271       |
+------------------+-------------------------+---------------------+--------------+
| 348935           | Deli King Inc.          | deli BreadMeat City | 17090        |
+------------------+-------------------------+---------------------+--------------+
| 389169           | Pheidias, Inc.          | deli BreadMeat City | 138721       |
+------------------+-------------------------+---------------------+--------------+
| 391766           | Ted's Deli & More, Inc. | deli BreadMeat City | 142080       |
+------------------+-------------------------+---------------------+--------------+
| 421955           | Deli Flavor, Inc.       | deli BreadMeat City | 5057         |
+------------------+-------------------------+---------------------+--------------+

Delete

with Databased("chi.db", commit_on_close=False) as db:
    num_rows = db.delete("businesses", "dba LIKE 'deli %' AND address_id > 6000")
    print(f"num rows deleted: {num_rows}")
    print(db.to_grid(db.select("businesses", where="dba LIKE 'deli %'")))

Output:

num rows deleted: 4
+------------------+-------------------+-------------+--------------+
| account_number   | legal_name        | dba         | address_id   |
+==================+===================+=============+==============+
| 421955           | Deli Flavor, Inc. | Deli Flavor | 5057         |
+------------------+-------------------+-------------+--------------+

databased also comes with an interactive shell called dbshell, which is built from the argshell package.
It can be launched from the terminal by entering dbshell

>dbshell
Searching for database...
Could not find a .db file in e:/1vsCode/python/databased.
Enter path to .db file to use or press enter to search again recursively:
Searching recursively...
DB options:
(1) shelltesting/chi.db (2) shelltesting/chi_backup.db (3) shelltesting/chi_backup_09-21-2023-12_06_37_PM.db
Enter the number of the option to use: 1
Starting dbshell v3.0.0 (enter help or ? for arg info)...

chi.db>help

Documented commands (type help <topic>):
========================================
add_column  describe     properties  schema                    size
backup      drop_column  query       select                    sys
customize   drop_table   quit        set_connection_timeout    update
dbpath      flush_log    restore     set_detect_types          use
delete      help         scan        set_enforce_foreign_keys  vacuum

Unrecognized commands will be executed as queries.
Use the `query` command explicitly if you don't want to capitalize your key words.
All transactions initiated by commands are committed immediately.

chi.db>help schema
Print out the names of the database tables, their columns, and, optionally, the number of rows.
Parser help for schema:
usage: dbshell [-h] [-t [TABLES ...]] [-c]

options:
  -h, --help            show this help message and exit
  -t [TABLES ...], --tables [TABLES ...]
                        Only display info for this table(s).
  -c, --rowcount        Count and display the number of rows for each table.

chi.db>schema -c
Getting database schema...
+----------------------+----------------------------------------------------------------------------------------------------------------------+------------------+
| Table Name           | Columns                                                                                                              | Number of Rows   |
+======================+======================================================================================================================+==================+
| business_addresses   | id, street, zip, ward, latitude, longitude                                                                           | 13276            |
+----------------------+----------------------------------------------------------------------------------------------------------------------+------------------+
| businesses           | account_number, legal_name, dba, address_id                                                                          | 14696            |
+----------------------+----------------------------------------------------------------------------------------------------------------------+------------------+
| license_codes        | code, description                                                                                                    | 146              |
+----------------------+----------------------------------------------------------------------------------------------------------------------+------------------+
| license_statuses     | id, status, description                                                                                              | 5                |
+----------------------+----------------------------------------------------------------------------------------------------------------------+------------------+
| licenses             | license_number, account_number, start_date, expiration_date, issue_date, status_id, status_change_date, license_code | 20120            |
+----------------------+----------------------------------------------------------------------------------------------------------------------+------------------+
| facility_types       | id, name                                                                                                             | 243              |
+----------------------+----------------------------------------------------------------------------------------------------------------------+------------------+
| risk_levels          | id, name                                                                                                             | 5                |
+----------------------+----------------------------------------------------------------------------------------------------------------------+------------------+
| facility_addresses   | id, street, zip, latitude, longitude, facility_type_id, risk_id                                                      | 13036            |
+----------------------+----------------------------------------------------------------------------------------------------------------------+------------------+
| inspected_businesses | id, license_number, dba, aka                                                                                         | 20120            |
+----------------------+----------------------------------------------------------------------------------------------------------------------+------------------+
| inspection_types     | id, name                                                                                                             | 16               |
+----------------------+----------------------------------------------------------------------------------------------------------------------+------------------+
| result_types         | id, description                                                                                                      | 7                |
+----------------------+----------------------------------------------------------------------------------------------------------------------+------------------+
| inspections          | id, license_number, facility_address_id, inspection_type_id, result_type_id, date                                    | 80016            |
+----------------------+----------------------------------------------------------------------------------------------------------------------+------------------+
| violation_types      | id, name                                                                                                             | 64               |
+----------------------+----------------------------------------------------------------------------------------------------------------------+------------------+
| violations           | id, inspection_id, violation_type_id, comment                                                                        | 312471           |
+----------------------+----------------------------------------------------------------------------------------------------------------------+------------------+
chi.db>select violation_types -o "id DESC" -l 5
Querying violation_types...
Found 5 rows:
+------+----------------------------------------------------+
| id   | name                                               |
+======+====================================================+
| 64   | Miscellaneous / Public Health Orders               |
+------+----------------------------------------------------+
| 63   | Removal Of Suspension Sign                         |
+------+----------------------------------------------------+
| 62   | Compliance With Clean Indoor Air Ordinance         |
+------+----------------------------------------------------+
| 61   | Summary Report Displayed And Visible To The Public |
+------+----------------------------------------------------+
| 60   | Previous Core Violation Corrected                  |
+------+----------------------------------------------------+
5 rows from violation_types
chi.db>SELECT * FROM violation_types ORDER BY id DESC LIMIT 5;
+------+----------------------------------------------------+
| id   | name                                               |
+======+====================================================+
| 64   | Miscellaneous / Public Health Orders               |
+------+----------------------------------------------------+
| 63   | Removal Of Suspension Sign                         |
+------+----------------------------------------------------+
| 62   | Compliance With Clean Indoor Air Ordinance         |
+------+----------------------------------------------------+
| 61   | Summary Report Displayed And Visible To The Public |
+------+----------------------------------------------------+
| 60   | Previous Core Violation Corrected                  |
+------+----------------------------------------------------+

The customize command or the custom_shell script can be used to generate a template file in the current directory that subclasses DBManager.
This allows for project specific additional commands as well as modifications of available commands.