| Name | SQLiteHint JSON |
| Version |
1.0.19
JSON |
| download |
| home_page | https://github.com/awolverp/sqlitehint/ |
| Summary | Have a easier SQLite with helpful methods and readable syntax with sqlitehint. |
| upload_time | 2024-02-09 15:42:06 |
| maintainer | |
| docs_url | None |
| author | awolverp |
| requires_python | |
| license | |
| keywords |
|
| VCS |
 |
| bugtrack_url |
|
| requirements |
No requirements were recorded.
|
| Travis-CI |
No Travis.
|
| coveralls test coverage |
No coveralls.
|
## SQLiteHint - SQLite helper
Have a easier SQLite with helpful methods and readable syntax with **sqlitehint**. This is designed to make working with sqlite3 better with more options.
sqlitehint is not an **ORM**. It can help you in type hinting (and with some other features, e.g. manage transactions).
> SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It’s also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle.
**Features:**
- Full type hint
- Manage cursors easily
- Configure your database easily
- Start transaction easily
- Use locks for asynchronize applications.
**Installation**
```bash
pip3 install -U sqlitehint
```
**Content:**
- [tutorial](#tutorial)
- [reference](#reference)
- [how-to guide](#how-to-guide)
## Tutorial
In this tutorial, we're rewrite the [sqlite3 tutorial](https://docs.python.org/3/library/sqlite3.html#tutorial):
Use `sqlitehint.connect()` to create a connection to database *tutorial.db*:
```python
import sqlitehint
conn = sqlitehint.connect('tutorial.db')
```
We can use `tuning` method to configure the connection and database, for example we use it here to change [journal_mode](https://www.sqlite.org/pragma.html#pragma_journal_mode):
```python
conn = conn.tuning(journal_mode="WAL")
```
Like [sqlite3](https://docs.python.org/3/library/sqlite3.html#module-sqlite3) to execute SQL queries, we need a database cursor. In **sqlitehint**, we use `conn.context()` to create that:
```python
with conn.context() as ctx:
# ...
```
For example, here we use it to create *movie* table and insert a row into it:
```python
with conn.context(autocommit=True) as ctx:
ctx.execute("CREATE TABLE movie(title, year, score)")
ctx.execute("INSERT INTO movie VALUES(?,?,?)", ('Python', 1975, 8.2))
```
We set **autocommit** to True, so the context will commit at the end and we don't need to call `conn.commit()`.
> [!IMPORTANT]\
> [sqlite3](https://docs.python.org/3/library/sqlite3.html#module-sqlite3): Notice that `?` placeholders are used to bind data to the query. Always use placeholders instead of string formatting to bind Python values to SQL statements, to avoid SQL injection attacks
Now we can use `SELECT` query to verify that data was inserted.
So we can call `conn.context()` again or **use old context**.
> [!NOTE]\
> Notice that **sqlitehint** uses **sqlitehint.RowModel** instead of tuple.
Before creating context, we create an instance of *sqlitehint.RowModel* for *movie* table (*this is just for type hinting*).
```python
class Movie(sqlitehint.RowModel):
title: str
year: int
score: float
with conn.context(Movie) as ctx:
ctx.selectall("SELECT * FROM movie")
# Returns [Movie(title='Python', year=1975, score=8.2)]
# We can go normal way instead of using selectall:
ctx.execute("SELECT * FROM movie")
ctx.fetchall()
# Returns [Movie(title='Python', year=1975, score=8.2)]
```
**Full Code**
```python
import sqlitehint
class Movie(sqlitehint.RowModel):
title: str
year: int
score: float
conn = sqlitehint.connect("tutorial.db").tuning(journal_mode="WAL")
with conn.context(autocommit=True) as ctx:
ctx.execute("CREATE TABLE movie(title, year, score)")
ctx.execute("INSERT INTO movie VALUES(?,?,?)", ('Python', 1975, 8.2))
with conn.context(Movie) as ctx:
ctx.selectall("SELECT * FROM movie")
# Returns [Movie(title='Python', year=1975, score=8.2)]
```
## Reference
### sqlitehint.connect
Open a connection to an SQLite database.
**Parameters:**
- database (`StrOrBytesPath`): The path to the database file to be opened. You can pass ":memory:" to
create an SQLite database existing only in memory, and open a connection to it.
- timeout (`float`): How many seconds the connection should wait before raising an OperationalError when a table
is locked. If another connection opens a transaction to modify a table, that table will be
locked until the transaction is committed. Default five seconds.
- detect_types (`int`): Control whether and how data types not natively supported by SQLite are looked up to be
converted to Python types, using the converters registered with `register_converter()`.
Set it to any combination (using |, bitwise or) of PARSE_DECLTYPES and PARSE_COLNAMES
to enable this. Column names takes precedence over declared types if both flags are set.
- isolation_level (`str | None`): Control legacy transaction handling behaviour. See `Connection.isolation_level`
and Transaction control via the isolation_level attribute for more information.
Can be "DEFERRED" (default), "EXCLUSIVE" or "IMMEDIATE"; or None to disable
opening transactions implicitly. Has no effect unless `Connection.autocommit`
is set to LEGACY_TRANSACTION_CONTROL (the default).
- check_same_thread (`bool`): If True (default), ProgrammingError will be raised if the database connection is
used by a thread other than the one that created it. If False, the connection may
be accessed in multiple threads; write operations may need to be serialized by the
user to avoid data corruption.
- factory (`type[sqlite3.Connection]`): A custom subclass of Connection to create the connection with,
if not the default Connection class.
- cached_statements (`int`): The number of statements that sqlite3 should internally cache for this connection,
to avoid parsing overhead. By default, 128 statements.
- uri (`bool`): If set to True, database is interpreted as a URI with a file path and an optional query string.
The scheme part must be "file:", and the path can be relative or absolute.
- autocommit (`bool` - New in version 3.12): Control PEP 249 transaction handling behaviour. autocommit currently
defaults to LEGACY_TRANSACTION_CONTROL. The default will change to
False in a future Python release.
- context_block (`bool`): Specifies the context blocking situation. If True, the `context()` block is True on default,
And vice versa.
------
### sqlitehint.Connection
Each open SQLite database is represented by a `Connection` object, which is created using `sqlitehint.connect()`.
------
### sqlitehint.Connection.context
Creates a context and manage transactions.
**Parameters:**
- factory (`type | (Cursor, tuple) -> object | None`): Specifies the cursor row_factory, and this helps type hinting.
- begin (`bool`): If False, does not start a transaction, just returns the context.
If True, starts a transaction depends on isolation_level.
- autocommit (`bool`): If True, at the end of using context, it will automatically commits (or rollback)
the connection. (ignore this parameter if 'begin' is True)
- isolation_level (`str`): If begin=True, the isolation level will use for creating transaction.
- block (`bool`): (overrides the Connection.context_block) If True, and there is an another active transaction,
context will block the code until that be close. This is very helpful on multi-threads.
If begin is True and you are using `with` statement, the object will commit/rollback the transaction
at the exit
------
### sqlitehint.Connection.tuning
You can use it for change some connection settings and clean database to speed-up the database.
**Parameters:**
- vacuum (`bool`): If True, vacuums the database.
- journal_mode (`str`): If specified, changes database journal_mode.
- journal_size_limit (`str`): If specified, changes database journal_size_limit.
- synchronous (`str`): If specified, changes database synchronous.
**Example**
```python
>>> conn = sqlitehint.connect("test.db").tuning(vacuum=True, journal_mode="WAL", synchronous="NORMAL")
>>> conn.pragma("journal_mode")
('wal',)
>>> conn.pragma("synchronous")
(1,)
```
------
### sqlitehint.Connection.pragma
Executes the PRAGMA statements.
**Example**
```python
>>> conn.pragma("journal_mode", "wal")
>>> conn.pragma("journal_mode")
('wal',)
```
------
### sqlitehint.TxContext
Transaction context for managing transactions that returns by `Connection.context()`.
------
### sqlitehint.RowModel
An enchanced [sqlite3.Row](https://docs.python.org/3/library/sqlite3.html#sqlite3.Row) object.
You can use it as a subclass for typehinting like example.
## How-to guide
### How to use sqlitehint in threading?
**sqlitehint** provides a blocking mode protocol for threading and asynchronize applications.
If you want to use it, when calling `Connection.context()`, just set `block` parameter to True.
> [!TIP]\
> In threading, set journal_mode to 'WAL' and synchronous to 'NORMAL', that will improve database speed in threading.
For example:
```python
import sqlitehint
import threading
conn = sqlitehint.connect("threading.db", check_same_thread=False).tuning(journal_mode="WAL", synchronous="NORMAL")
with conn.context(autocommit=True) as ctx:
ctx.execute("CREATE TABLE IF NOT EXISTS movie(title)")
def execute(n):
with conn.context(block=True) as ctx:
ctx.execute("INSERT INTO movie VALUES(?)", (str(n),))
threads = [threading.Thread(target=execute, args=(n,)) for n in range(10)]
for t in threads:
t.start()
for t in threads:
t.join()
# verify data
with conn.context() as ctx:
assert (ctx.selectone("SELECT COUNT(*) FROM movie")[0]) == 10
```
Raw data
{
"_id": null,
"home_page": "https://github.com/awolverp/sqlitehint/",
"name": "SQLiteHint",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "",
"author": "awolverp",
"author_email": "awolverp@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/be/8e/df0654935ab1a6fcf26d62610f832c495c742a52238d1a3d96895bd4569d/SQLiteHint-1.0.19.tar.gz",
"platform": null,
"description": "## SQLiteHint - SQLite helper\n\nHave a easier SQLite with helpful methods and readable syntax with **sqlitehint**. This is designed to make working with sqlite3 better with more options.\n\nsqlitehint is not an **ORM**. It can help you in type hinting (and with some other features, e.g. manage transactions).\n\n> SQLite is a C library that provides a lightweight disk-based database that doesn\u2019t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It\u2019s also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle.\n\n**Features:**\n- Full type hint\n- Manage cursors easily\n- Configure your database easily\n- Start transaction easily\n- Use locks for asynchronize applications.\n\n**Installation**\n```bash\npip3 install -U sqlitehint\n```\n\n**Content:**\n- [tutorial](#tutorial)\n- [reference](#reference)\n- [how-to guide](#how-to-guide)\n\n## Tutorial\nIn this tutorial, we're rewrite the [sqlite3 tutorial](https://docs.python.org/3/library/sqlite3.html#tutorial):\n\nUse `sqlitehint.connect()` to create a connection to database *tutorial.db*:\n```python\nimport sqlitehint\nconn = sqlitehint.connect('tutorial.db')\n```\n\nWe can use `tuning` method to configure the connection and database, for example we use it here to change [journal_mode](https://www.sqlite.org/pragma.html#pragma_journal_mode):\n```python\nconn = conn.tuning(journal_mode=\"WAL\")\n```\n\nLike [sqlite3](https://docs.python.org/3/library/sqlite3.html#module-sqlite3) to execute SQL queries, we need a database cursor. In **sqlitehint**, we use `conn.context()` to create that:\n```python\nwith conn.context() as ctx:\n # ...\n```\n\nFor example, here we use it to create *movie* table and insert a row into it:\n```python\nwith conn.context(autocommit=True) as ctx:\n ctx.execute(\"CREATE TABLE movie(title, year, score)\")\n ctx.execute(\"INSERT INTO movie VALUES(?,?,?)\", ('Python', 1975, 8.2))\n```\nWe set **autocommit** to True, so the context will commit at the end and we don't need to call `conn.commit()`.\n\n> [!IMPORTANT]\\\n> [sqlite3](https://docs.python.org/3/library/sqlite3.html#module-sqlite3): Notice that `?` placeholders are used to bind data to the query. Always use placeholders instead of string formatting to bind Python values to SQL statements, to avoid SQL injection attacks\n\nNow we can use `SELECT` query to verify that data was inserted.\nSo we can call `conn.context()` again or **use old context**.\n\n> [!NOTE]\\\n> Notice that **sqlitehint** uses **sqlitehint.RowModel** instead of tuple.\n\nBefore creating context, we create an instance of *sqlitehint.RowModel* for *movie* table (*this is just for type hinting*).\n```python\nclass Movie(sqlitehint.RowModel):\n title: str\n year: int\n score: float\n\nwith conn.context(Movie) as ctx:\n ctx.selectall(\"SELECT * FROM movie\")\n # Returns [Movie(title='Python', year=1975, score=8.2)]\n\n # We can go normal way instead of using selectall:\n ctx.execute(\"SELECT * FROM movie\")\n ctx.fetchall()\n # Returns [Movie(title='Python', year=1975, score=8.2)]\n```\n\n**Full Code**\n```python\nimport sqlitehint\n\nclass Movie(sqlitehint.RowModel):\n title: str\n year: int\n score: float\n\nconn = sqlitehint.connect(\"tutorial.db\").tuning(journal_mode=\"WAL\")\n\nwith conn.context(autocommit=True) as ctx:\n ctx.execute(\"CREATE TABLE movie(title, year, score)\")\n ctx.execute(\"INSERT INTO movie VALUES(?,?,?)\", ('Python', 1975, 8.2))\n\nwith conn.context(Movie) as ctx:\n ctx.selectall(\"SELECT * FROM movie\")\n # Returns [Movie(title='Python', year=1975, score=8.2)]\n```\n\n## Reference\n### sqlitehint.connect\nOpen a connection to an SQLite database.\n\n**Parameters:**\n- database (`StrOrBytesPath`): The path to the database file to be opened. You can pass \":memory:\" to\n create an SQLite database existing only in memory, and open a connection to it.\n\n- timeout (`float`): How many seconds the connection should wait before raising an OperationalError when a table\n is locked. If another connection opens a transaction to modify a table, that table will be\n locked until the transaction is committed. Default five seconds.\n\n- detect_types (`int`): Control whether and how data types not natively supported by SQLite are looked up to be\n converted to Python types, using the converters registered with `register_converter()`.\n Set it to any combination (using |, bitwise or) of PARSE_DECLTYPES and PARSE_COLNAMES\n to enable this. Column names takes precedence over declared types if both flags are set.\n\n- isolation_level (`str | None`): Control legacy transaction handling behaviour. See `Connection.isolation_level`\n and Transaction control via the isolation_level attribute for more information.\n Can be \"DEFERRED\" (default), \"EXCLUSIVE\" or \"IMMEDIATE\"; or None to disable\n opening transactions implicitly. Has no effect unless `Connection.autocommit`\n is set to LEGACY_TRANSACTION_CONTROL (the default).\n- check_same_thread (`bool`): If True (default), ProgrammingError will be raised if the database connection is\n used by a thread other than the one that created it. If False, the connection may\n be accessed in multiple threads; write operations may need to be serialized by the\n user to avoid data corruption.\n\n- factory (`type[sqlite3.Connection]`): A custom subclass of Connection to create the connection with,\n if not the default Connection class.\n\n- cached_statements (`int`): The number of statements that sqlite3 should internally cache for this connection,\n to avoid parsing overhead. By default, 128 statements.\n\n- uri (`bool`): If set to True, database is interpreted as a URI with a file path and an optional query string.\n The scheme part must be \"file:\", and the path can be relative or absolute.\n\n- autocommit (`bool` - New in version 3.12): Control PEP 249 transaction handling behaviour. autocommit currently\n defaults to LEGACY_TRANSACTION_CONTROL. The default will change to\n False in a future Python release.\n\n- context_block (`bool`): Specifies the context blocking situation. If True, the `context()` block is True on default,\n And vice versa.\n\n------\n### sqlitehint.Connection\nEach open SQLite database is represented by a `Connection` object, which is created using `sqlitehint.connect()`.\n\n------\n### sqlitehint.Connection.context\nCreates a context and manage transactions.\n\n**Parameters:**\n- factory (`type | (Cursor, tuple) -> object | None`): Specifies the cursor row_factory, and this helps type hinting.\n\n- begin (`bool`): If False, does not start a transaction, just returns the context.\n If True, starts a transaction depends on isolation_level.\n\n- autocommit (`bool`): If True, at the end of using context, it will automatically commits (or rollback)\n the connection. (ignore this parameter if 'begin' is True)\n\n- isolation_level (`str`): If begin=True, the isolation level will use for creating transaction.\n\n- block (`bool`): (overrides the Connection.context_block) If True, and there is an another active transaction,\n context will block the code until that be close. This is very helpful on multi-threads.\n\nIf begin is True and you are using `with` statement, the object will commit/rollback the transaction\nat the exit\n\n------\n### sqlitehint.Connection.tuning\nYou can use it for change some connection settings and clean database to speed-up the database.\n\n**Parameters:**\n- vacuum (`bool`): If True, vacuums the database.\n\n- journal_mode (`str`): If specified, changes database journal_mode.\n\n- journal_size_limit (`str`): If specified, changes database journal_size_limit.\n\n- synchronous (`str`): If specified, changes database synchronous.\n\n**Example**\n```python\n>>> conn = sqlitehint.connect(\"test.db\").tuning(vacuum=True, journal_mode=\"WAL\", synchronous=\"NORMAL\")\n>>> conn.pragma(\"journal_mode\")\n('wal',)\n>>> conn.pragma(\"synchronous\")\n(1,)\n```\n\n------\n### sqlitehint.Connection.pragma\nExecutes the PRAGMA statements.\n\n**Example**\n```python\n>>> conn.pragma(\"journal_mode\", \"wal\")\n>>> conn.pragma(\"journal_mode\")\n('wal',)\n```\n\n------\n### sqlitehint.TxContext\nTransaction context for managing transactions that returns by `Connection.context()`.\n\n------\n### sqlitehint.RowModel\nAn enchanced [sqlite3.Row](https://docs.python.org/3/library/sqlite3.html#sqlite3.Row) object.\n\nYou can use it as a subclass for typehinting like example.\n\n## How-to guide\n### How to use sqlitehint in threading?\n**sqlitehint** provides a blocking mode protocol for threading and asynchronize applications.\n\nIf you want to use it, when calling `Connection.context()`, just set `block` parameter to True.\n\n> [!TIP]\\\n> In threading, set journal_mode to 'WAL' and synchronous to 'NORMAL', that will improve database speed in threading.\n\nFor example:\n```python\nimport sqlitehint\nimport threading\n\nconn = sqlitehint.connect(\"threading.db\", check_same_thread=False).tuning(journal_mode=\"WAL\", synchronous=\"NORMAL\")\n\nwith conn.context(autocommit=True) as ctx:\n ctx.execute(\"CREATE TABLE IF NOT EXISTS movie(title)\")\n\ndef execute(n):\n with conn.context(block=True) as ctx:\n ctx.execute(\"INSERT INTO movie VALUES(?)\", (str(n),))\n\nthreads = [threading.Thread(target=execute, args=(n,)) for n in range(10)]\n\nfor t in threads:\n t.start()\nfor t in threads:\n t.join()\n\n# verify data\nwith conn.context() as ctx:\n assert (ctx.selectone(\"SELECT COUNT(*) FROM movie\")[0]) == 10\n```\n",
"bugtrack_url": null,
"license": "",
"summary": "Have a easier SQLite with helpful methods and readable syntax with sqlitehint.",
"version": "1.0.19",
"project_urls": {
"Bug Tracker": "https://github.com/awolverp/sqlitehint/issues/new",
"Github": "https://github.com/awolverp/sqlitehint/",
"Homepage": "https://github.com/awolverp/sqlitehint/"
},
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "1821bb4a91ca75f9df6ad0a4b95f82e0f4d00228c062c8e861acbada76a81df7",
"md5": "945d1e33718cadfe2577b8b0c4ade144",
"sha256": "9ba88a6e45b356028de0bfd95881f77d979b0d948c5b58183f325a4a0b761943"
},
"downloads": -1,
"filename": "SQLiteHint-1.0.19-py3-none-any.whl",
"has_sig": false,
"md5_digest": "945d1e33718cadfe2577b8b0c4ade144",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": null,
"size": 11976,
"upload_time": "2024-02-09T15:42:04",
"upload_time_iso_8601": "2024-02-09T15:42:04.935999Z",
"url": "https://files.pythonhosted.org/packages/18/21/bb4a91ca75f9df6ad0a4b95f82e0f4d00228c062c8e861acbada76a81df7/SQLiteHint-1.0.19-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "be8edf0654935ab1a6fcf26d62610f832c495c742a52238d1a3d96895bd4569d",
"md5": "668712362f782786dcfb0390db23c501",
"sha256": "99447c9860589c3e6bc435e3bf0e8e0c48d183122d595cad092abc911f4accd1"
},
"downloads": -1,
"filename": "SQLiteHint-1.0.19.tar.gz",
"has_sig": false,
"md5_digest": "668712362f782786dcfb0390db23c501",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 10807,
"upload_time": "2024-02-09T15:42:06",
"upload_time_iso_8601": "2024-02-09T15:42:06.962025Z",
"url": "https://files.pythonhosted.org/packages/be/8e/df0654935ab1a6fcf26d62610f832c495c742a52238d1a3d96895bd4569d/SQLiteHint-1.0.19.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-02-09 15:42:06",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "awolverp",
"github_project": "sqlitehint",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"requirements": [],
"lcname": "sqlitehint"
}
