# DAOModel
An instant CRUD layer for your Python models (Powered by
[SQLModel](https://sqlmodel.tiangolo.com/)/
[Pydantic](https://docs.pydantic.dev/latest/)/
[SQLAlchemy](https://www.sqlalchemy.org/)).
Eliminate repetitive work by auto creating your DAOs.
There is no need to write SQL queries or recall how to work with
SQLAlchemy models when you are only looking to do basic functionality.
## Supported Functions
* `create`
* `insert`
* `update`
* `upsert`
* if `exists`
* `get`
* `find` (supports advanced searching, more details below)
* `remove`
* access to `query` (to do anything else not directly supported)
## Features
* DAOModel expands on SQLModel so no need to learn a new way to define your models.
* Existing SQLModel, Pydantic, and SQLAlchemy functionality is still accessible for anything not built into DAOModel.
* Provides many quality of life additions that I found to be repeated throughout my own projects.
## Usage
### Develop your SQLModel as usual:
```python
class Customer(SQLModel, table=True):
id: int = Field(primary_key=True)
name: str
```
More on this at [SQLModel's Documentation](https://sqlmodel.tiangolo.com/tutorial/create-db-and-table/#create-the-table-model-class)
### Inherit DAOModel in place of _SQLModel_
`DAOModel` acts as a middleman, adding methods to your class.
Yet `DAOModel` inherits from `SQLModel`
meaning your object still `isinstance(m, SQLModel)`.
```python
class Customer(DAOModel, table=True):
id: int = Field(primary_key=True)
name: str
```
### Configure and Initialize your database
This library doesn't really care how you setup your DB. Skip ahead if you already know how to do so.
Otherwise, you may find some of the library's built-in functionality useful.
#### Create your engine using DAOModel's helper function
```python
engine = create_engine("database.db")
```
This uses SQLite to store your data. If you don't need persistence,
an in-memory SQLite DB (perfect for testing) is achievable by excluding the path:
```python
engine = create_engine()
```
While good to start, when/if this doesn't meet your needs, please refer to
[SQLAlchemy's Docs on Engines](https://docs.sqlalchemy.org/core/engines_connections.html)
#### Initialize the Engine
Once you have your engine, you'll need to initialize each of the tables representing your models.
```python
init_db(engine)
```
This is simply a shortcut method of `SQLModel.metadata.create_all(engine)`.
> **NOTE:** Be sure your Models are all imported (if defined outside of this file)
> before executing this code or else those tables will not be included.
#### Create a DB session
```python
db = Session(engine)
```
Again, this isn't anything that is specific to DAOModel,
it is common across SQLModel, Flask, etc. so feel free to do this your own way.
There exist plenty of guides and tutorials or you can start with [SQLAlchemy's Docs on Sessions](https://docs.sqlalchemy.org/orm/session_basics.html)
<br>
Now you are ready to populate your database, but for that, we are going to use the DAO!
### Create a DAO for your DAOModel
Creating a DAO is simple enough, but you will need your db session your class that inherits DAOModel.
```python
DAO(Customer, db)
```
> **NOTE:** You pass the Class to DAO, not an instance of the Class
So there you have it, You now have a usable DAO layer for your model!
Let's look at the full code:
```python
class Customer(DAOModel, table=True):
id: int = Field(primary_key=True)
name: str
engine = create_engine("database.db")
init_db(engine)
db = Session(engine)
dao = DAO(Customer, db)
```
It may not be exactly what is wanted for your final product, but it gets you up and running quickly.
Just a few lines is all you need to get started!
### Using the DAO
The whole premise of an automatic DAO layer is to make your code more straightforward and readable.
Read the docs for more details, otherwise the following table should give a general understanding.
| Action | Method | Under the hood | Example |
|----------------------------|--------|---------------------------------------|-----------------------------|
| Create a new object | create | Adds values as new row to DB table | `model = dao.create(23)` |
| Insert an object | insert | Adds object as new row to DB table | `dao.insert(model)` |
| Update an object | update | Updates column values of a row | `dao.update(model)` |
| Update or insert an object | upsert | Updates or inserts row if not present | `dao.upsert(model)` |
| Check if an object exists | exists | Checks if any rows match object | `if dao.exists(model):` |
| Get an object | get | Selects row by primary key | `model = dao.get(23)` |
| Search for objects | find | Selects rows by column values | `results = dao.find("Bob")` |
| Delete an object | remove | Deletes row from DB table | `dao.remove(model)` |
Check out the Sample Code for a more thorough example.
You can even use it as a template for your own project!
## Searching
One of the best features about DAOModel is the robust search functionality.
Without assembling complicated SQL queries, you are able to search by specific columns.
```python
results = dao.find(name="Bob")
```
Said columns can even be foreign keys or columns from related tables
(provided those columns are defined as searchable).
Take a look at `test_dao_find.py` to see some examples.
### Is column value set?
Perhaps you don't want to search for _Bob_, but rather find all customers who do not have any 'name'.
Using `True` and `False` limits your results to rows having, or not having, a value.
```python
# find all nameless customers
results = dao.find(name=False)
```
### Duplicate (or unique) values
Sometimes your customers (or other data) gets added more than once.
Wouldn't it be great to easily find all of these duplicates? Say no more!
```python
# find all customers that share a name with another customer
results = dao.find(duplicate=Customer.name)
```
Now I can see that I have 3 customers named _Bob_ and 2 named _Joe_ without listing each other customer.
Or maybe it's the unique values you wish to see:
`dao.find(unique=Customer.name)` will provide all customers that don't share a name,
in this case, excluding all the _Bobs_ and _Joes_.
### Sorting
The order of your results can easily be specified.
By default, results are sorted by primary key.
But you can sort by any column, foreign key, or foreign property you desire.
```python
# sort by name and then id
results = dao.find(order=(Customer.name, Customer.id))
```
> NOTE: wrap a column with `desc()` to reverse the order
### All of the above
The previously stated options can be done together if needed:
```python
results = dao.find(name=True, region="US",
duplicate=Customer.name, unique=Customer.address,
order=desc(Customer.last_modified))
```
### Integrated Pagination
Pages of results come naturally for searches done with DAOModel DAOs.
Indicate the number of intended results using the `per_page` argument.
Optionally specify which page number to retrieve using the `page` argument.
```python
for result in dao.find(page=2, per_page=10):
```
As seen above, the returned SearchResults are directly iterable.
But they also include properties for the total number of results.
The page number and number of results per page are also included for convenience.
## Additional Functionality
### Commit on Demand
Each of the modifying actions in the table above will auto-commit.
However, if you wish to prevent this, include the argument `commit=False`
In that scenario, you will need to call `dao.commit()` explicitly.
This is most useful when conducting batch actions, or when you may wish to abort the changes.
### Copying values
Values (other than the primary key) can be copied from one model instance to another.
This is done through `model.copy_model(other_model)` or `model.copy_values(**dictionary)`.
Both the `create` and `get` functionality have copying built-in in the form of `create_with` and `get_with`.
```python
# create a new row but also populate the name column in the same line
model = dao.create_with(id=52, name="Bob")
# select the now existing row but reassign the name
model = dao.get_with(id=52, name="Joe")
# calling get_with does not modify the DB, so you need to explicitly update or commit
dao.update(model)
```
### DAOFactory
The DAOFactory allows you to easily open and close sessions as needed.
In order to use it, get yourself a [session factory](https://docs.sqlalchemy.org/orm/session_basics.html#using-a-sessionmaker) and then use a `with` statement.
```python
session_factory = sessionmaker(engine)
with DAOFactory(session_factory) as daos:
dao = daos[Customer]
```
Again, this may not fit your needs exactly,
but you can inherit from DAOFactory in order to create your own solution.
### Auto-increment ID
The [SQLModel Tutorial](https://sqlmodel.tiangolo.com/tutorial/automatic-id-none-refresh/)
discusses how to have an auto incrementing primary key.
This library contains a utility called `next_id()` that,
when passed as an argument, will not specify an ID so that it is auto generated.
```python
model = dao.create(next_id())
```
This code is equivalent to passing `None` as the argument.
However, the named method makes the line easier to understand.
## Caveats
Most testing has been completed using SQLite, though since SQLModel/SQLAlchemy
support other database solutions, DAOModel is expected to as well.
Speaking of SQLite, this library configures Foreign Key constraints to be enforced by default in SQLite.
Table names are configured to be snake_case which differs from SQLModel.
This can be adjusted by overridding `def __tablename__` in your own child class.
Not all functionality will work as intended through DAOModel.
If something isn't supported, submit a ticket or pull request.
And remember that you may always use what you can and then
override the code or use the query method in DAO to do the rest.
It should still save you a lot of lines of code.
Raw data
{
"_id": null,
"home_page": null,
"name": "DAOModel",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.7",
"maintainer_email": null,
"keywords": "dao, crud, model, database, db, search, query, sql, sqlmodel, sqlalchemy, pydantic",
"author": null,
"author_email": "Cody M Sommer <bassmastacod@gmail.com>",
"download_url": "https://files.pythonhosted.org/packages/48/78/2473cba44788012205d13a2064b89744864cab41a5fcbb9b9c2ce7225543/daomodel-0.3.1b0.tar.gz",
"platform": null,
"description": "# DAOModel\nAn instant CRUD layer for your Python models (Powered by\n[SQLModel](https://sqlmodel.tiangolo.com/)/\n[Pydantic](https://docs.pydantic.dev/latest/)/\n[SQLAlchemy](https://www.sqlalchemy.org/)).\n\nEliminate repetitive work by auto creating your DAOs.\nThere is no need to write SQL queries or recall how to work with\nSQLAlchemy models when you are only looking to do basic functionality.\n\n## Supported Functions\n* `create`\n* `insert`\n* `update`\n* `upsert`\n* if `exists`\n* `get`\n* `find` (supports advanced searching, more details below)\n* `remove`\n* access to `query` (to do anything else not directly supported)\n\n## Features\n* DAOModel expands on SQLModel so no need to learn a new way to define your models.\n* Existing SQLModel, Pydantic, and SQLAlchemy functionality is still accessible for anything not built into DAOModel.\n* Provides many quality of life additions that I found to be repeated throughout my own projects.\n\n## Usage\n### Develop your SQLModel as usual:\n```python\nclass Customer(SQLModel, table=True):\n id: int = Field(primary_key=True)\n name: str\n```\nMore on this at [SQLModel's Documentation](https://sqlmodel.tiangolo.com/tutorial/create-db-and-table/#create-the-table-model-class)\n\n### Inherit DAOModel in place of _SQLModel_\n`DAOModel` acts as a middleman, adding methods to your class.\nYet `DAOModel` inherits from `SQLModel`\nmeaning your object still `isinstance(m, SQLModel)`.\n```python\nclass Customer(DAOModel, table=True):\n id: int = Field(primary_key=True)\n name: str\n```\n\n### Configure and Initialize your database\nThis library doesn't really care how you setup your DB. Skip ahead if you already know how to do so.\nOtherwise, you may find some of the library's built-in functionality useful.\n\n#### Create your engine using DAOModel's helper function\n```python\nengine = create_engine(\"database.db\")\n```\nThis uses SQLite to store your data. If you don't need persistence,\nan in-memory SQLite DB (perfect for testing) is achievable by excluding the path:\n```python\nengine = create_engine()\n```\nWhile good to start, when/if this doesn't meet your needs, please refer to\n[SQLAlchemy's Docs on Engines](https://docs.sqlalchemy.org/core/engines_connections.html)\n\n#### Initialize the Engine\nOnce you have your engine, you'll need to initialize each of the tables representing your models.\n```python\ninit_db(engine)\n```\nThis is simply a shortcut method of `SQLModel.metadata.create_all(engine)`.\n> **NOTE:** Be sure your Models are all imported (if defined outside of this file)\n> before executing this code or else those tables will not be included.\n\n#### Create a DB session\n```python\ndb = Session(engine)\n```\nAgain, this isn't anything that is specific to DAOModel,\nit is common across SQLModel, Flask, etc. so feel free to do this your own way.\nThere exist plenty of guides and tutorials or you can start with [SQLAlchemy's Docs on Sessions](https://docs.sqlalchemy.org/orm/session_basics.html)\n\n<br>\n\nNow you are ready to populate your database, but for that, we are going to use the DAO!\n\n### Create a DAO for your DAOModel\nCreating a DAO is simple enough, but you will need your db session your class that inherits DAOModel.\n```python\nDAO(Customer, db)\n```\n> **NOTE:** You pass the Class to DAO, not an instance of the Class\n\nSo there you have it, You now have a usable DAO layer for your model!\nLet's look at the full code:\n\n```python\nclass Customer(DAOModel, table=True):\n id: int = Field(primary_key=True)\n name: str\n\nengine = create_engine(\"database.db\")\ninit_db(engine)\ndb = Session(engine)\ndao = DAO(Customer, db)\n```\nIt may not be exactly what is wanted for your final product, but it gets you up and running quickly.\nJust a few lines is all you need to get started!\n\n### Using the DAO\nThe whole premise of an automatic DAO layer is to make your code more straightforward and readable.\nRead the docs for more details, otherwise the following table should give a general understanding.\n\n| Action | Method | Under the hood | Example | \n|----------------------------|--------|---------------------------------------|-----------------------------|\n| Create a new object | create | Adds values as new row to DB table | `model = dao.create(23)` |\n| Insert an object | insert | Adds object as new row to DB table | `dao.insert(model)` |\n| Update an object | update | Updates column values of a row | `dao.update(model)` |\n| Update or insert an object | upsert | Updates or inserts row if not present | `dao.upsert(model)` |\n| Check if an object exists | exists | Checks if any rows match object | `if dao.exists(model):` |\n| Get an object | get | Selects row by primary key | `model = dao.get(23)` |\n| Search for objects | find | Selects rows by column values | `results = dao.find(\"Bob\")` |\n| Delete an object | remove | Deletes row from DB table | `dao.remove(model)` |\n\nCheck out the Sample Code for a more thorough example.\nYou can even use it as a template for your own project!\n\n## Searching\nOne of the best features about DAOModel is the robust search functionality.\nWithout assembling complicated SQL queries, you are able to search by specific columns.\n```python\nresults = dao.find(name=\"Bob\")\n```\nSaid columns can even be foreign keys or columns from related tables\n(provided those columns are defined as searchable).\nTake a look at `test_dao_find.py` to see some examples.\n\n### Is column value set?\nPerhaps you don't want to search for _Bob_, but rather find all customers who do not have any 'name'.\nUsing `True` and `False` limits your results to rows having, or not having, a value.\n```python\n# find all nameless customers\nresults = dao.find(name=False)\n```\n\n### Duplicate (or unique) values\nSometimes your customers (or other data) gets added more than once.\nWouldn't it be great to easily find all of these duplicates? Say no more!\n```python\n# find all customers that share a name with another customer\nresults = dao.find(duplicate=Customer.name)\n```\nNow I can see that I have 3 customers named _Bob_ and 2 named _Joe_ without listing each other customer.\n\nOr maybe it's the unique values you wish to see:\n`dao.find(unique=Customer.name)` will provide all customers that don't share a name,\nin this case, excluding all the _Bobs_ and _Joes_.\n\n### Sorting\nThe order of your results can easily be specified.\nBy default, results are sorted by primary key.\nBut you can sort by any column, foreign key, or foreign property you desire.\n```python\n# sort by name and then id\nresults = dao.find(order=(Customer.name, Customer.id))\n```\n> NOTE: wrap a column with `desc()` to reverse the order\n\n### All of the above\nThe previously stated options can be done together if needed:\n```python\nresults = dao.find(name=True, region=\"US\",\n duplicate=Customer.name, unique=Customer.address,\n order=desc(Customer.last_modified))\n```\n\n### Integrated Pagination\nPages of results come naturally for searches done with DAOModel DAOs.\nIndicate the number of intended results using the `per_page` argument.\nOptionally specify which page number to retrieve using the `page` argument.\n```python\nfor result in dao.find(page=2, per_page=10):\n```\nAs seen above, the returned SearchResults are directly iterable.\nBut they also include properties for the total number of results.\nThe page number and number of results per page are also included for convenience.\n\n## Additional Functionality\n### Commit on Demand\nEach of the modifying actions in the table above will auto-commit.\nHowever, if you wish to prevent this, include the argument `commit=False`\nIn that scenario, you will need to call `dao.commit()` explicitly.\nThis is most useful when conducting batch actions, or when you may wish to abort the changes.\n\n### Copying values\nValues (other than the primary key) can be copied from one model instance to another.\nThis is done through `model.copy_model(other_model)` or `model.copy_values(**dictionary)`.\nBoth the `create` and `get` functionality have copying built-in in the form of `create_with` and `get_with`.\n```python\n# create a new row but also populate the name column in the same line\nmodel = dao.create_with(id=52, name=\"Bob\")\n\n# select the now existing row but reassign the name\nmodel = dao.get_with(id=52, name=\"Joe\")\n\n# calling get_with does not modify the DB, so you need to explicitly update or commit\ndao.update(model)\n```\n\n### DAOFactory\nThe DAOFactory allows you to easily open and close sessions as needed.\n\n\nIn order to use it, get yourself a [session factory](https://docs.sqlalchemy.org/orm/session_basics.html#using-a-sessionmaker) and then use a `with` statement.\n```python\nsession_factory = sessionmaker(engine)\nwith DAOFactory(session_factory) as daos:\n dao = daos[Customer]\n```\n\nAgain, this may not fit your needs exactly,\nbut you can inherit from DAOFactory in order to create your own solution.\n\n### Auto-increment ID\nThe [SQLModel Tutorial](https://sqlmodel.tiangolo.com/tutorial/automatic-id-none-refresh/)\ndiscusses how to have an auto incrementing primary key.\nThis library contains a utility called `next_id()` that,\nwhen passed as an argument, will not specify an ID so that it is auto generated.\n```python\nmodel = dao.create(next_id())\n```\nThis code is equivalent to passing `None` as the argument.\nHowever, the named method makes the line easier to understand.\n\n## Caveats\nMost testing has been completed using SQLite, though since SQLModel/SQLAlchemy\nsupport other database solutions, DAOModel is expected to as well.\n\nSpeaking of SQLite, this library configures Foreign Key constraints to be enforced by default in SQLite.\n\nTable names are configured to be snake_case which differs from SQLModel.\nThis can be adjusted by overridding `def __tablename__` in your own child class.\n\nNot all functionality will work as intended through DAOModel.\nIf something isn't supported, submit a ticket or pull request.\nAnd remember that you may always use what you can and then\noverride the code or use the query method in DAO to do the rest.\nIt should still save you a lot of lines of code.",
"bugtrack_url": null,
"license": "MIT",
"summary": "Provides an automatic DAO layer for your models",
"version": "0.3.1b0",
"project_urls": {
"Issues": "https://github.com/BassMastaCod/DAOModel/issues",
"Repository": "https://github.com/BassMastaCod/DAOModel.git"
},
"split_keywords": [
"dao",
" crud",
" model",
" database",
" db",
" search",
" query",
" sql",
" sqlmodel",
" sqlalchemy",
" pydantic"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "10036f2eb34f4b31b5073ec76773b8aa304d17fdd1344539f692a6511c0805a5",
"md5": "c48b37f31107046eb90aa4480d3faf89",
"sha256": "129360ade24c3ecb3eb270505f1ac76b0b22a14b2cef91d24d7aafae56d29125"
},
"downloads": -1,
"filename": "daomodel-0.3.1b0-py3-none-any.whl",
"has_sig": false,
"md5_digest": "c48b37f31107046eb90aa4480d3faf89",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.7",
"size": 12676,
"upload_time": "2025-01-29T16:06:23",
"upload_time_iso_8601": "2025-01-29T16:06:23.845075Z",
"url": "https://files.pythonhosted.org/packages/10/03/6f2eb34f4b31b5073ec76773b8aa304d17fdd1344539f692a6511c0805a5/daomodel-0.3.1b0-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "48782473cba44788012205d13a2064b89744864cab41a5fcbb9b9c2ce7225543",
"md5": "c6180500917cfb56136f806f7aa8f68f",
"sha256": "8e4d63a5a2122a7137392682293e6ac7e059a2b115356a9e828392cb68330351"
},
"downloads": -1,
"filename": "daomodel-0.3.1b0.tar.gz",
"has_sig": false,
"md5_digest": "c6180500917cfb56136f806f7aa8f68f",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.7",
"size": 20732,
"upload_time": "2025-01-29T16:06:25",
"upload_time_iso_8601": "2025-01-29T16:06:25.719590Z",
"url": "https://files.pythonhosted.org/packages/48/78/2473cba44788012205d13a2064b89744864cab41a5fcbb9b9c2ce7225543/daomodel-0.3.1b0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-01-29 16:06:25",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "BassMastaCod",
"github_project": "DAOModel",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "daomodel"
}
JSON
