[简体中文](https://github.com/amisadmin/sqlalchemy_database/blob/master/README.zh.md)
| [English](https://github.com/amisadmin/sqlalchemy_database)
<h2 align="center">
SQLAlchemy-Database
</h2>
<p align="center">
<em>SQLAlchemy-Database provides shortcut functions to common database operations for SQLAlchemy ORM.</em><br/>
</p>
<p align="center">
<a href="https://github.com/amisadmin/sqlalchemy_database/actions/workflows/pytest.yml" target="_blank">
<img src="https://github.com/amisadmin/sqlalchemy_database/actions/workflows/pytest.yml/badge.svg" alt="Pytest">
</a>
<a href="https://codecov.io/gh/amisadmin/sqlalchemy_database" >
<img src="https://codecov.io/gh/amisadmin/sqlalchemy_database/branch/master/graph/badge.svg?token=SKOGAKIX4M" alt="codecov"/>
</a>
<a href="https://pypi.org/project/sqlalchemy_database" target="_blank">
<img src="https://badgen.net/pypi/v/sqlalchemy_database?color=blue" alt="Package version">
</a>
<a href="https://gitter.im/amisadmin/fastapi-amis-admin">
<img src="https://badges.gitter.im/amisadmin/fastapi-amis-admin.svg" alt="Chat on Gitter"/>
</a>
<a href="https://jq.qq.com/?_wv=1027&k=U4Dv6x8W" target="_blank">
<img src="https://badgen.net/badge/qq%E7%BE%A4/229036692/orange" alt="229036692">
</a>
</p>
## Introduction
- Support `SQLAlchemy` and `SQLModel`,recommend using `SQLModel`.
## Install
```bash
pip install sqlalchemy-database
```
## ORM Model
### SQLAlchemy Model Sample
```python
import datetime
import sqlalchemy as sa
from sqlalchemy.orm import declarative_base
Base = declarative_base()
class User(Base):
__tablename__ = "User"
id = sa.Column(sa.Integer, primary_key=True)
username = sa.Column(sa.String(30), unique=True, index=True, nullable=False)
password = sa.Column(sa.String(30), default='')
create_time = sa.Column(sa.DateTime, default=datetime.datetime.utcnow)
```
### SQLModel Model Sample
```python
import datetime
from sqlmodel import SQLModel, Field
class User(SQLModel, table=True):
id: int = Field(default=None, primary_key=True, nullable=False)
username: str = Field(title='username', max_length=30, unique=True, index=True, nullable=False)
password: str = Field(default='', title='Password')
create_time: datetime = Field(default_factory=datetime.now, title='Create Time')
```
## AsyncDatabase
### Creation Connection
```python
from sqlalchemy_database import AsyncDatabase
# 1.Create an asynchronous database connection
db = AsyncDatabase.create('sqlite+aiosqlite:///amisadmin.db?check_same_thread=False') # sqlite
# db = AsyncDatabase.create('mysql+aiomysql://root:123456@127.0.0.1:3306/amisadmin?charset=utf8mb4')# mysql
# db = AsyncDatabase.create('postgresql+asyncpg://postgres:root@127.0.0.1:5432/amisadmin')# postgresql
```
## Database
### Creation Connection
```python
from sqlalchemy_database import Database
# 1.Create a database connection
db = Database.create('sqlite:///amisadmin.db?check_same_thread=False') # sqlite
# db = Database.create('mysql+pymysql://root:123456@127.0.0.1:3306/amisadmin?charset=utf8mb4') # mysql
# db = Database.create('postgresql://postgres:root@127.0.0.1:5432/amisadmin') # postgresql
# db = Database.create('oracle+cx_oracle://scott:tiger@tnsname') # oracle
# db = Database.create('mssql+pyodbc://scott:tiger@mydsn') # SQL Server
```
## AbcAsyncDatabase
When you are developing a library of tools, your Python program may require a database connection.
But you can't be sure whether the other person personally prefers synchronous or asynchronous connections.
You can use asynchronous shortcut functions with the `async_` prefix.
`AsyncDatabase` and `Database` both inherit from `AbcAsyncDatabase` and both implement the usual `async_` prefixed asynchronous
shortcut functions.
For example: `async_execute`,`async_scalar`,`async_scalars`,`async_get`,`async_delete`,`async_run_sync`.
Remark: The `async_` prefix in `Database` is implemented by executing the corresponding synchronous shortcut in the thread pool.
### Asynchronous compatible shortcut functions
```python
from sqlalchemy import insert, select, update, delete
from sqlalchemy_database import AsyncDatabase, Database
async def fast_execute(db: Union[AsyncDatabase, Database]):
# update
stmt = update(User).where(User.id == 1).values({'username': 'new_user'})
result = await db.async_execute(stmt)
# select
stmt = select(User).where(User.id == 1)
user = await db.async_execute(stmt, on_close_pre=lambda r: r.scalar())
# insert
stmt = insert(User).values({'username': 'User-6', 'password': 'password-6'})
result = await db.async_execute(stmt)
# delete
stmt = delete(User).where(User.id == 6)
result = await db.async_execute(stmt)
# scalar
user = await db.async_scalar(select(User).where(User.id == 1))
# scalars
stmt = select(User)
result = await db.async_scalars(stmt)
# get
user = await db.async_get(User, 1)
# delete
user = User(id=1, name='test')
await db.async_delete(user)
# run_sync
await db.async_run_sync(Base.metadata.create_all, is_session=False)
```
## Use dependencies in FastAPI
```python
app = FastAPI()
# AsyncDatabase
@app.get("/user/{id}")
async def get_user(id: int, session: AsyncSession = Depends(db.session_generator)):
return await session.get(User, id)
# Database
@app.get("/user/{id}")
def get_user(id: int, session: Session = Depends(db.session_generator)):
return session.get(User, id)
```
## Use middleware in FastAPI
```python
app = FastAPI()
# Database
sync_db = Database.create("sqlite:///amisadmin.db?check_same_thread=False")
app.add_middleware(sync_db.asgi_middleware)
@app.get("/user/{id}")
def get_user(id: int):
return sync_db.session.get(User, id)
# AsyncDatabase
async_db = AsyncDatabase.create("sqlite+aiosqlite:///amisadmin.db?check_same_thread=False")
app.add_middleware(async_db.asgi_middleware)
@app.get("/user/{id}")
async def get_user(id: int):
return await async_db.session.get(User, id)
```
## Get session object
You can get the session object anywhere, but you need to manage the lifecycle of the session yourself. For example:
- 1.In FastAPI, you can use middleware or dependencies to get the session object. In the routing function, the method called will
automatically get the session object in the context.
- 2.In the local work unit, you can use the `with` statement to get the session object. In the `with` statement, the method called
will automatically get a new session object.
```mermaid
graph LR
session[Get session] --> scopefunc{Read context var}
scopefunc -->|None| gSession[Return the global default session]
scopefunc -->|Not a Session object| sSession[Return the scoped session corresponding to the current context variable]
scopefunc -->|Is a Session object| cSession[Return session in the current context variable]
```
## More tutorial documentation
### [sqlalchemy](https://github.com/sqlalchemy/sqlalchemy)
`SQLAlchemy-Database` adds extension functionality to `SQLAlchemy`.
More features and complicated to use, please refer to the ` SQLAlchemy ` [documentation](https://www.sqlalchemy.org/).
`SQLAlchemy` is very powerful and can fulfill almost any complex need you have.
### [sqlmodel](https://github.com/tiangolo/sqlmodel)
Recommend you to use ` SQLModel ` definition `ORM` model, please refer to
the ` SQLModel ` [documentation](https://sqlmodel.tiangolo.com/).
`SQLModel` written by `FastAPI` author, Perfectly combine [SQLAlchemy](https://www.sqlalchemy.org/)
with [Pydantic](https://pydantic-docs.helpmanual.io/), and have all their features .
## Relevant project
- [FastAPI-Amis-Admin](https://docs.amis.work/)
## License
According to the `Apache2.0` protocol.
Raw data
{
"_id": null,
"home_page": null,
"name": "sqlalchemy_database",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.7",
"maintainer_email": "Atomi <1456417373@qq.com>",
"keywords": "SQLAlchemy-Database, SQLAlchemy, Database, AsyncDatabase, FastAPI-Amis-Admin",
"author": null,
"author_email": "Atomi <1456417373@qq.com>",
"download_url": "https://files.pythonhosted.org/packages/1e/62/aefbe690d926f32563d87c7a88fbd0e11ac2840d5d6c152f1e86827ccf7f/sqlalchemy_database-0.1.2.tar.gz",
"platform": null,
"description": "[\u7b80\u4f53\u4e2d\u6587](https://github.com/amisadmin/sqlalchemy_database/blob/master/README.zh.md)\n| [English](https://github.com/amisadmin/sqlalchemy_database)\n\n<h2 align=\"center\">\n SQLAlchemy-Database\n</h2>\n<p align=\"center\">\n <em>SQLAlchemy-Database provides shortcut functions to common database operations for SQLAlchemy ORM.</em><br/>\n</p>\n<p align=\"center\">\n <a href=\"https://github.com/amisadmin/sqlalchemy_database/actions/workflows/pytest.yml\" target=\"_blank\">\n <img src=\"https://github.com/amisadmin/sqlalchemy_database/actions/workflows/pytest.yml/badge.svg\" alt=\"Pytest\">\n </a>\n <a href=\"https://codecov.io/gh/amisadmin/sqlalchemy_database\" > \n <img src=\"https://codecov.io/gh/amisadmin/sqlalchemy_database/branch/master/graph/badge.svg?token=SKOGAKIX4M\" alt=\"codecov\"/> \n </a>\n <a href=\"https://pypi.org/project/sqlalchemy_database\" target=\"_blank\">\n <img src=\"https://badgen.net/pypi/v/sqlalchemy_database?color=blue\" alt=\"Package version\">\n </a>\n <a href=\"https://gitter.im/amisadmin/fastapi-amis-admin\">\n <img src=\"https://badges.gitter.im/amisadmin/fastapi-amis-admin.svg\" alt=\"Chat on Gitter\"/>\n </a>\n <a href=\"https://jq.qq.com/?_wv=1027&k=U4Dv6x8W\" target=\"_blank\">\n <img src=\"https://badgen.net/badge/qq%E7%BE%A4/229036692/orange\" alt=\"229036692\">\n </a>\n</p>\n\n## Introduction\n\n- Support `SQLAlchemy` and `SQLModel`,recommend using `SQLModel`.\n\n## Install\n\n```bash\npip install sqlalchemy-database\n```\n\n## ORM Model\n\n### SQLAlchemy Model Sample\n\n```python\nimport datetime\n\nimport sqlalchemy as sa\nfrom sqlalchemy.orm import declarative_base\n\nBase = declarative_base()\n\n\nclass User(Base):\n __tablename__ = \"User\"\n id = sa.Column(sa.Integer, primary_key=True)\n username = sa.Column(sa.String(30), unique=True, index=True, nullable=False)\n password = sa.Column(sa.String(30), default='')\n create_time = sa.Column(sa.DateTime, default=datetime.datetime.utcnow)\n```\n\n### SQLModel Model Sample\n\n```python\nimport datetime\n\nfrom sqlmodel import SQLModel, Field\n\n\nclass User(SQLModel, table=True):\n id: int = Field(default=None, primary_key=True, nullable=False)\n username: str = Field(title='username', max_length=30, unique=True, index=True, nullable=False)\n password: str = Field(default='', title='Password')\n create_time: datetime = Field(default_factory=datetime.now, title='Create Time')\n```\n\n## AsyncDatabase\n\n### Creation Connection\n\n```python\nfrom sqlalchemy_database import AsyncDatabase\n\n# 1.Create an asynchronous database connection\ndb = AsyncDatabase.create('sqlite+aiosqlite:///amisadmin.db?check_same_thread=False') # sqlite\n# db = AsyncDatabase.create('mysql+aiomysql://root:123456@127.0.0.1:3306/amisadmin?charset=utf8mb4')# mysql\n# db = AsyncDatabase.create('postgresql+asyncpg://postgres:root@127.0.0.1:5432/amisadmin')# postgresql\n\n```\n\n## Database\n\n### Creation Connection\n\n```python\nfrom sqlalchemy_database import Database\n\n# 1.Create a database connection\ndb = Database.create('sqlite:///amisadmin.db?check_same_thread=False') # sqlite\n# db = Database.create('mysql+pymysql://root:123456@127.0.0.1:3306/amisadmin?charset=utf8mb4') # mysql\n# db = Database.create('postgresql://postgres:root@127.0.0.1:5432/amisadmin') # postgresql\n# db = Database.create('oracle+cx_oracle://scott:tiger@tnsname') # oracle\n# db = Database.create('mssql+pyodbc://scott:tiger@mydsn') # SQL Server\n```\n\n## AbcAsyncDatabase\n\nWhen you are developing a library of tools, your Python program may require a database connection.\n\nBut you can't be sure whether the other person personally prefers synchronous or asynchronous connections.\n\nYou can use asynchronous shortcut functions with the `async_` prefix.\n\n`AsyncDatabase` and `Database` both inherit from `AbcAsyncDatabase` and both implement the usual `async_` prefixed asynchronous\nshortcut functions.\n\nFor example: `async_execute`,`async_scalar`,`async_scalars`,`async_get`,`async_delete`,`async_run_sync`.\n\nRemark: The `async_` prefix in `Database` is implemented by executing the corresponding synchronous shortcut in the thread pool.\n\n### Asynchronous compatible shortcut functions\n\n```python\nfrom sqlalchemy import insert, select, update, delete\nfrom sqlalchemy_database import AsyncDatabase, Database\n\n\nasync def fast_execute(db: Union[AsyncDatabase, Database]):\n # update\n stmt = update(User).where(User.id == 1).values({'username': 'new_user'})\n result = await db.async_execute(stmt)\n\n # select\n stmt = select(User).where(User.id == 1)\n user = await db.async_execute(stmt, on_close_pre=lambda r: r.scalar())\n\n # insert\n stmt = insert(User).values({'username': 'User-6', 'password': 'password-6'})\n result = await db.async_execute(stmt)\n\n # delete\n stmt = delete(User).where(User.id == 6)\n result = await db.async_execute(stmt)\n\n # scalar\n user = await db.async_scalar(select(User).where(User.id == 1))\n\n # scalars\n stmt = select(User)\n result = await db.async_scalars(stmt)\n\n # get\n user = await db.async_get(User, 1)\n\n # delete\n user = User(id=1, name='test')\n await db.async_delete(user)\n\n # run_sync\n await db.async_run_sync(Base.metadata.create_all, is_session=False)\n\n```\n\n## Use dependencies in FastAPI\n\n```python\napp = FastAPI()\n\n\n# AsyncDatabase\n@app.get(\"/user/{id}\")\nasync def get_user(id: int, session: AsyncSession = Depends(db.session_generator)):\n return await session.get(User, id)\n\n\n# Database\n@app.get(\"/user/{id}\")\ndef get_user(id: int, session: Session = Depends(db.session_generator)):\n return session.get(User, id)\n```\n\n## Use middleware in FastAPI\n\n```python\napp = FastAPI()\n\n# Database\nsync_db = Database.create(\"sqlite:///amisadmin.db?check_same_thread=False\")\n\napp.add_middleware(sync_db.asgi_middleware)\n\n\n@app.get(\"/user/{id}\")\ndef get_user(id: int):\n return sync_db.session.get(User, id)\n\n\n# AsyncDatabase\nasync_db = AsyncDatabase.create(\"sqlite+aiosqlite:///amisadmin.db?check_same_thread=False\")\n\napp.add_middleware(async_db.asgi_middleware)\n\n\n@app.get(\"/user/{id}\")\nasync def get_user(id: int):\n return await async_db.session.get(User, id)\n\n```\n\n## Get session object\n\nYou can get the session object anywhere, but you need to manage the lifecycle of the session yourself. For example:\n\n- 1.In FastAPI, you can use middleware or dependencies to get the session object. In the routing function, the method called will\n automatically get the session object in the context.\n\n- 2.In the local work unit, you can use the `with` statement to get the session object. In the `with` statement, the method called\n will automatically get a new session object.\n\n```mermaid\ngraph LR\nsession[Get session] --> scopefunc{Read context var}\nscopefunc -->|None| gSession[Return the global default session]\nscopefunc -->|Not a Session object| sSession[Return the scoped session corresponding to the current context variable]\nscopefunc -->|Is a Session object| cSession[Return session in the current context variable]\n```\n\n## More tutorial documentation\n\n### [sqlalchemy](https://github.com/sqlalchemy/sqlalchemy)\n\n`SQLAlchemy-Database` adds extension functionality to `SQLAlchemy`.\n\nMore features and complicated to use, please refer to the ` SQLAlchemy ` [documentation](https://www.sqlalchemy.org/).\n\n`SQLAlchemy` is very powerful and can fulfill almost any complex need you have.\n\n### [sqlmodel](https://github.com/tiangolo/sqlmodel)\n\nRecommend you to use ` SQLModel ` definition `ORM` model, please refer to\nthe ` SQLModel ` [documentation](https://sqlmodel.tiangolo.com/).\n\n`SQLModel` written by `FastAPI` author, Perfectly combine [SQLAlchemy](https://www.sqlalchemy.org/)\nwith [Pydantic](https://pydantic-docs.helpmanual.io/), and have all their features .\n\n## Relevant project\n\n- [FastAPI-Amis-Admin](https://docs.amis.work/)\n\n## License\n\nAccording to the `Apache2.0` protocol.\n",
"bugtrack_url": null,
"license": null,
"summary": "SQLAlchemy-Database provides shortcut functions to common database operations for SQLAlchemy ORM.",
"version": "0.1.2",
"project_urls": {
"FastAPI-Amis-Admin": "https://github.com/amisadmin/fastapi_amis_admin",
"Source": "https://github.com/amisadmin/sqlalchemy_database"
},
"split_keywords": [
"sqlalchemy-database",
" sqlalchemy",
" database",
" asyncdatabase",
" fastapi-amis-admin"
],
"urls": [
{
"comment_text": null,
"digests": {
"blake2b_256": "36d2a100111c6e0b36a2fb121dbd920a3312ae0559434eec8c9a796f10f89fa6",
"md5": "a3bfac8017e4b5ca10b823ceba3a1984",
"sha256": "f9e5ff8cc607883673aedd9179c743e6550240bcca2084ff63771586cab8b3b4"
},
"downloads": -1,
"filename": "sqlalchemy_database-0.1.2-py3-none-any.whl",
"has_sig": false,
"md5_digest": "a3bfac8017e4b5ca10b823ceba3a1984",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.7",
"size": 11156,
"upload_time": "2024-03-31T07:19:43",
"upload_time_iso_8601": "2024-03-31T07:19:43.092670Z",
"url": "https://files.pythonhosted.org/packages/36/d2/a100111c6e0b36a2fb121dbd920a3312ae0559434eec8c9a796f10f89fa6/sqlalchemy_database-0.1.2-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "1e62aefbe690d926f32563d87c7a88fbd0e11ac2840d5d6c152f1e86827ccf7f",
"md5": "d060495ec92973be055ac6e29cd88889",
"sha256": "85720ed45b10e2c02fd02b82b770c735b2644cda38ddab9c0cb94348b6e8e839"
},
"downloads": -1,
"filename": "sqlalchemy_database-0.1.2.tar.gz",
"has_sig": false,
"md5_digest": "d060495ec92973be055ac6e29cd88889",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.7",
"size": 63512,
"upload_time": "2024-03-31T07:19:44",
"upload_time_iso_8601": "2024-03-31T07:19:44.994150Z",
"url": "https://files.pythonhosted.org/packages/1e/62/aefbe690d926f32563d87c7a88fbd0e11ac2840d5d6c152f1e86827ccf7f/sqlalchemy_database-0.1.2.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-03-31 07:19:44",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "amisadmin",
"github_project": "fastapi_amis_admin",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "sqlalchemy_database"
}
JSON
