# streamlit_sql
## Introduction
This package shows a CRUD frontend to a database using sqlalchemy in a streamlit app. With just one line of code, show the data as table and allow the user to **read, filter, update, create and delete rows** with many useful features.
## Demo
See the package in action [here](https://example-crud.streamlit.app/).
## Features
### READ
- Display as a regular st.dataframe
- Add pagination, displaying only a set of rows each time
- Set the dataframe to be displayed using standard sqlalchemy select statement, where you can JOIN, ORDER BY, WHERE, etc.
- Add a column to show the rolling sum of a numeric column
- Conditional styling if the DataFrame based on each row value. For instance, changing its background color
### FILTER
- Filter the data by some columns before presenting the table.
- Let users filter the columns by selecting conditions in the filter expander
- Give possible candidates when filtering using existing values for the columns
- Let users select ForeignKey's values using the string representation of the foreign table, instead of its id number
### UPDATE
- Users update rows with a dialog opened by selecting the row and clicking the icon
- Text columns offers candidates from existing values
- ForeignKey columns are added by the string representation instead of its id number
- In Update form, list all ONE-TO-MANY related rows with pagination, where you can directly create and delete related table rows.
### CREATE
- Users create new rows with a dialog opened by clicking the create button
- Text columns offers candidates from existing values
- Hide columns to fill by offering default values
- ForeignKey columns are added by the string representation instead of its id number
### DELETE
- Delete one or multiple rows by selecting in DataFrame and clicking the corresponding button. I dialog will list selected rows and confirm deletion.
## Requirements
All the requirements you should probably have anyway.
1. streamlit and sqlalchemy
2. Sqlalchemy models needs a __str__ method
2. Id column should be called "id"
3. Relationships should be added for all ForeignKey columns
## Basic Usage
Install the package using pip:
```bash
pip install streamlit_sql
```
Run `show_sql_ui` as the example below:
```python
from streamlit_sql import show_sql_ui
from sqlalchemy import select
conn = st.connection("sql", url="<db_url>")
stmt = (
select(
db.Invoice.id,
db.Invoice.Date,
db.Invoice.amount,
db.Client.name,
)
.join(db.Client)
.where(db.Invoice.amount > 1000)
.order_by(db.Invoice.date)
)
show_sql_ui(conn=conn,
read_instance=stmt,
edit_create_model=db.Invoice,
available_filter=["name"],
rolling_total_column="amount",
)
show_sql_ui(conn, model_opts)
```
!!! warning
In the statement, **always** include the primary_key column, that should be named *id*
### Interface
- Filter: Open the "Filter" expander and fill the inputs
- Add row: Click on "plus" button (no dataframe row can be selected)
- Edit row: Click on "pencil" button (one and only one dataframe row should be selected)
- Delete row: Click on "trash" button (one or more dataframe rows should be selected)
## Customize
You can adjust the CRUD interface by the select statement you provide to *read_instance* arg and giving optional arguments to the *show_sql_ui* function. See the docstring for more information or at [documentation webpage](https://edkedk99.github.io/streamlit_sql/api/#streamlit_sql.show_sql_ui):
## Only create or update form
You can display just a create or update/delete form without the read interface using functions *show_updade*, and *show_create*.
Raw data
{
"_id": null,
"home_page": null,
"name": "streamlit-sql",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.8",
"maintainer_email": null,
"keywords": null,
"author": null,
"author_email": null,
"download_url": "https://files.pythonhosted.org/packages/4d/9c/e5865c3c47a5e7050971f42ac4934711d4d3519bee2ffaaba75da8020c71/streamlit_sql-0.2.4.tar.gz",
"platform": null,
"description": "# streamlit_sql\n\n## Introduction\n\nThis package shows a CRUD frontend to a database using sqlalchemy in a streamlit app. With just one line of code, show the data as table and allow the user to **read, filter, update, create and delete rows** with many useful features.\n\n## Demo\n\nSee the package in action [here](https://example-crud.streamlit.app/).\n\n## Features\n\n### READ\n\n- Display as a regular st.dataframe\n- Add pagination, displaying only a set of rows each time\n- Set the dataframe to be displayed using standard sqlalchemy select statement, where you can JOIN, ORDER BY, WHERE, etc.\n- Add a column to show the rolling sum of a numeric column\n- Conditional styling if the DataFrame based on each row value. For instance, changing its background color\n\n### FILTER\n\n- Filter the data by some columns before presenting the table.\n- Let users filter the columns by selecting conditions in the filter expander\n- Give possible candidates when filtering using existing values for the columns\n- Let users select ForeignKey's values using the string representation of the foreign table, instead of its id number\n\n### UPDATE\n\n- Users update rows with a dialog opened by selecting the row and clicking the icon\n- Text columns offers candidates from existing values\n- ForeignKey columns are added by the string representation instead of its id number\n- In Update form, list all ONE-TO-MANY related rows with pagination, where you can directly create and delete related table rows. \n\n\n### CREATE\n\n- Users create new rows with a dialog opened by clicking the create button\n- Text columns offers candidates from existing values\n- Hide columns to fill by offering default values\n- ForeignKey columns are added by the string representation instead of its id number\n\n### DELETE\n\n- Delete one or multiple rows by selecting in DataFrame and clicking the corresponding button. I dialog will list selected rows and confirm deletion.\n\n\n\n## Requirements\n\nAll the requirements you should probably have anyway.\n\n1. streamlit and sqlalchemy\n2. Sqlalchemy models needs a __str__ method\n2. Id column should be called \"id\"\n3. Relationships should be added for all ForeignKey columns \n\n\n## Basic Usage\n\nInstall the package using pip:\n\n```bash\npip install streamlit_sql\n```\n\nRun `show_sql_ui` as the example below:\n\n```python\nfrom streamlit_sql import show_sql_ui\nfrom sqlalchemy import select\n\nconn = st.connection(\"sql\", url=\"<db_url>\")\n\nstmt = (\n select(\n db.Invoice.id,\n db.Invoice.Date,\n db.Invoice.amount,\n db.Client.name,\n )\n .join(db.Client)\n .where(db.Invoice.amount > 1000)\n .order_by(db.Invoice.date)\n)\n\nshow_sql_ui(conn=conn,\n read_instance=stmt,\n edit_create_model=db.Invoice,\n available_filter=[\"name\"],\n rolling_total_column=\"amount\",\n)\n\nshow_sql_ui(conn, model_opts)\n```\n\n!!! warning\n In the statement, **always** include the primary_key column, that should be named *id*\n\n### Interface\n\n- Filter: Open the \"Filter\" expander and fill the inputs\n- Add row: Click on \"plus\" button (no dataframe row can be selected)\n- Edit row: Click on \"pencil\" button (one and only one dataframe row should be selected)\n- Delete row: Click on \"trash\" button (one or more dataframe rows should be selected)\n\n\n## Customize\n\nYou can adjust the CRUD interface by the select statement you provide to *read_instance* arg and giving optional arguments to the *show_sql_ui* function. See the docstring for more information or at [documentation webpage](https://edkedk99.github.io/streamlit_sql/api/#streamlit_sql.show_sql_ui):\n\n\n## Only create or update form\n\nYou can display just a create or update/delete form without the read interface using functions *show_updade*, and *show_create*.\n",
"bugtrack_url": null,
"license": "MIT License",
"summary": "CRUD interface for sqlalchemy using streamlit",
"version": "0.2.4",
"project_urls": {
"documentation": "https://edkedk99.github.io/streamlit_sql/",
"homepage": "https://github.com/edkedk99/streamlit_sql",
"repository": "https://github.com/edkedk99/streamlit_sql"
},
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "192999dccfd3604848f6f2f1f76ed08e9140421144b878c86803d4fe5c2dc8e9",
"md5": "369ccceeac4f4fad6b3c3e5be15242d0",
"sha256": "b13451ca78081b8d62e0bf55a41f3bbb30e59eec0e6a5e119b6554883d685420"
},
"downloads": -1,
"filename": "streamlit_sql-0.2.4-py3-none-any.whl",
"has_sig": false,
"md5_digest": "369ccceeac4f4fad6b3c3e5be15242d0",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.8",
"size": 17509,
"upload_time": "2024-12-21T16:26:15",
"upload_time_iso_8601": "2024-12-21T16:26:15.682292Z",
"url": "https://files.pythonhosted.org/packages/19/29/99dccfd3604848f6f2f1f76ed08e9140421144b878c86803d4fe5c2dc8e9/streamlit_sql-0.2.4-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "4d9ce5865c3c47a5e7050971f42ac4934711d4d3519bee2ffaaba75da8020c71",
"md5": "f581b2447da98c763fa513096c0d36de",
"sha256": "4093c0444e5c6d47d0e910933196b2040d08a3ed5da5df0c100346f392f40937"
},
"downloads": -1,
"filename": "streamlit_sql-0.2.4.tar.gz",
"has_sig": false,
"md5_digest": "f581b2447da98c763fa513096c0d36de",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.8",
"size": 15583,
"upload_time": "2024-12-21T16:26:19",
"upload_time_iso_8601": "2024-12-21T16:26:19.573766Z",
"url": "https://files.pythonhosted.org/packages/4d/9c/e5865c3c47a5e7050971f42ac4934711d4d3519bee2ffaaba75da8020c71/streamlit_sql-0.2.4.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-12-21 16:26:19",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "edkedk99",
"github_project": "streamlit_sql",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"requirements": [
{
"name": "python-dateutil",
"specs": []
},
{
"name": "pandas",
"specs": []
},
{
"name": "sqlalchemy",
"specs": []
},
{
"name": "streamlit",
"specs": []
},
{
"name": "streamlit_datalist",
"specs": []
},
{
"name": "streamlit_antd_components",
"specs": []
}
],
"lcname": "streamlit-sql"
}
JSON
