| Name | s3ew JSON |
| Version |
0.0.4
JSON |
| download |
| home_page | |
| Summary | Sqlite Extensions and Wrappers |
| upload_time | 2024-03-08 14:33:23 |
| maintainer | |
| docs_url | None |
| author | icyveins7 |
| requires_python | |
| license | MIT License Copyright (c) 2022 icyveins7 Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. |
| keywords |
sqlite
wrapper
extensions
|
| VCS |
 |
| bugtrack_url |
|
| requirements |
No requirements were recorded.
|
| Travis-CI |
No Travis.
|
| coveralls test coverage |
No coveralls.
|
# sew  
SQLite Extensions & Wrappers for Python.
Tired of writing create/insert/select statements yourself? Or maybe you'd just like to access your tables with keys like a Pythonic dictionary? Then just subclass this.
# Motivation
Many processes for data manipulation with sqlite databases can be achieved with big-data libraries like Pandas. Sometimes, however, you might want some fine-grained control over the database creation and modification.
For example, Pandas currently does not support replacement inserts on a per-row basis. The .to_sql() method will only replace the entire table if specified.
A general ethos for this framework is to reduce the developer's code typed; if the sqlite statement you require is complicated then it may well be more efficient to use a custom statement yourself. Fundamentally, ```sew``` is still meant to only function as a wrapper around the default-included sqlite for Python.
However, for simple CRUD operations it should be easier to use methods this framework provides!
# Installation
## From PyPI
We are now on PyPI!
```
pip install s3ew
```
```sew``` was taken...
## From GitHub Releases
Head over to the Releases page and download the latest wheel there.
## From Source
Simply clone and then install in editable mode.
```
git clone https://github.com/icyveins7/sew
cd sew
pip install -e .
```
You should then be able to use it from anywhere. Updates just require a ```git pull``` command on your repository folder.
There are no additional requirements to install to use the base functionality, as this is just a wrapper around the default-included sqlite for Python. However, if you want to use the extra plugins, you can do
```
pip install -r plugin_requirements.txt
```
# Usage
The most common use-case is to initialise a ```Database``` object, just like you would with sqlite3.
```python
import sew
d = sew.Database(":memory:")
```
This contains the sqlite3.Connection as well as the sqlite3.Cursor objects, referenced with ```.con``` and ```.cur``` respectively.
Some common commands have shortcuts, if that's all you want to do:
```python
d.execute() # Same as d.cur.execute
d.executemany() # Same as d.cur.executemany
d.commit() # Same as d.con.commit
```
You also get a context manager for free:
```python
with sew.Database(":memory:") as db:
db.execute("create table tbl(col INTEGER))")
# db connect is now closed i.e. .close() called on db.con
# further statements to the cursor will result in sqlite3.ProgrammingError
db.execute("select * from tbl") # ERROR
# This is unlike the default sqlite3 connection which allows this
with sq.connect(":memory:") as sqcon:
sqcur = sqcon.cursor()
sqcur.execute("create table tbl(col INTEGER))")
sqcur.execute("select * from tbl") # This is allowed, sqcon is still valid!
```
But the main benefit is in shortened requirements for database changes. Docstrings are available for most common methods.
As an example, instead of writing the whole statements, you can do the following:
## Create Tables
```python
d.createTable(fmt, "mytablename")
# You can now access this table via dict-like methods like d['mytablename']
# See below for uses
```
## Format Specifiers
This framework operates using a pre-defined dictionary structure specifying each table's format. Fundamentally, this is just a dictionary that looks like this:
```python
fmt = {
'cols': [
['col1', 'integer'], # list of lists, with inner list begin column name and sqlite type in that order
['col2', 'REAL'] # the type upper/lower-case doesn't matter
],
'conds': [
"UNIQUE(col1, col2)" # this is just a list of strings, with each one specifying an extra condition
],
'foreign_keys': [
["col1", "parent_table(parent_col)"] # This specifies a foreign key relationship
]
}
```
You can specify this directly yourself, if you're familiar with sqlite3, or you can use the ```FormatSpecifier``` object to generate one like so:
```python
fmtspec = sew.FormatSpecifier()
fmtspec.addColumn('col1', int) # Yes, it accepts the pythonic type directly
fmtspec.addColumn('col2', float)
fmtspec.addUniques(['col1','col2'])
fmtspec.addForeignKey(["col1", "parent_table(parent_col)"])
fmt = fmtspec.generate() # Then use this wherever you need, like the createTable() call
```
Example of how the framework uses this in the create table example:
```python
stmt = d.createTable(fmt, 'tbl2')
>>> 'create table "tbl2"(col1 INTEGER, col2 REAL, UNIQUE(col1,col2), FOREIGN KEY(col1) REFERENCES parent_table(parent_col))'
```
You can also reference a format specifier from the table; this should be identical to what was used during creation.
But for cases where you need a python-parsable object that reflects the table schema:
```python
fmt = d['tbl2'].formatSpecifier
>>> {'cols': [['col1', 'INTEGER'], ['col2', 'REAL']],
'conds': ['UNIQUE(col1,col2)'],
'foreign_keys': [['col1', 'parent_table(parent_col)']]}
```
## Selects
```python
stmt = d["mytablename"].select(["thisColumn", "thatColumn"], ["otherColumn < 10"])
results = d.fetchall()
print(stmt) # Most commands return the statement generated, so you can check if needed
```
## Inserts
Insertions of complete rows (which is probably the most common scenario):
```python
d["mytablename"].insertOne(
1, 2.0, # For insertOne, just place the arguments one after another; no need to pack into a list/tuple/dict
orReplace=True # use a replace instead of insert
)
data = ((i, float(i+1)) for i in range(10)) # Generator expression
d["mytablename"].insertMany(
data # Anything that works with executemany should work here
)
```
You can also insert specific columns:
```python
# Columns are col1, col2, col3
d["mytablename"].insertOne(
{'col1': 1, 'col2': 2}, # Only insert 2 column values, leaving col3 NULL
orReplace=True # use a replace instead of insert
)
```
## Hard Refresh of Internal Tables Structure
```python
d.reloadTables()
```
This is generally only required to be performed when commits of table changes are made from another source - outside the current interpreter. This will force ```sew``` to re-execute a select statement from ```sqlite_master``` and re-populate the internal tables.
It is automatically called when first instantiating the ```sew.Database``` object.
## Other Examples
Many of the examples are encoded into actual tests! Take a look at the ```tests``` folder to see examples of everything from standard selects and inserts to the use of the plugins.
# Plugins
## Pandas
You can return Pandas dataframes using the ```PandasDatabase```, which simply forwards selects to ```read_sql```, but provides the same automatic statement generation behaviour and dictionary-like table access as before. The difference is that results are now extracted via the property ```.pdresults```.
```python
import sew.plugins
d = sew.plugins.PandasDatabase(...)
d['mytable'].select(...)
dataframe = d['mytable'].pdresults
```
## NumPy
If you'd like direct NumPy array support (rather than converting to and from Pandas dataframes), you can use the ```NumpyDatabase```, which performs inserts by assigning each array to a column. Select-statement behaviour returns arrays as a named dictionary, with keys as the column names.
```python
import sew.plugins
d = sew.plugins.NumpyDatabase(...)
data_f64 = np.array([...], dtype=np.float64)
data_f32 = np.array([...], dtype=np.float32)
# Table is created with "col1_f64 REAL, col2_f32 REAL"
d['mytable'].insertMany(data_f64, data_f32)
```
All datatypes should be preserved where possible; that means if you inserted an np.uint8 array, it should automatically return as an np.uint8 array. This is achieved via explicit encoding of the datatypes into the column names as suffixes. You can view these as the class global variable ```NummpyTableProxy.numpyColumnSuffixes```.
# Running Unit Tests and Benchmarks
Unit tests in the ```tests``` folder are a good way to look at some examples. To run them all, simply run the following command from the main repository ```sew``` folder (where the ```tests``` folder is visible):
```bash
python -m tests
```
Individual tests can be run by simply doing:
```bash
python -m tests.correctness
```
Benchmarks are similarly run:
```bash
python -m benchmarks
```
Raw data
{
"_id": null,
"home_page": "",
"name": "s3ew",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "sqlite,wrapper,extensions",
"author": "icyveins7",
"author_email": "",
"download_url": "https://files.pythonhosted.org/packages/2e/27/d86573eed6a999c6d0bcb278406ff49f428dee5ccb16d585be8f827581c1/s3ew-0.0.4.tar.gz",
"platform": null,
"description": "# sew  \n\nSQLite Extensions & Wrappers for Python.\n\nTired of writing create/insert/select statements yourself? Or maybe you'd just like to access your tables with keys like a Pythonic dictionary? Then just subclass this.\n\n# Motivation\n\nMany processes for data manipulation with sqlite databases can be achieved with big-data libraries like Pandas. Sometimes, however, you might want some fine-grained control over the database creation and modification.\n\nFor example, Pandas currently does not support replacement inserts on a per-row basis. The .to_sql() method will only replace the entire table if specified.\n\nA general ethos for this framework is to reduce the developer's code typed; if the sqlite statement you require is complicated then it may well be more efficient to use a custom statement yourself. Fundamentally, ```sew``` is still meant to only function as a wrapper around the default-included sqlite for Python.\n\nHowever, for simple CRUD operations it should be easier to use methods this framework provides!\n\n# Installation\n\n## From PyPI\nWe are now on PyPI!\n\n```\npip install s3ew\n```\n\n```sew``` was taken...\n\n## From GitHub Releases\n\nHead over to the Releases page and download the latest wheel there.\n\n## From Source\nSimply clone and then install in editable mode.\n\n```\ngit clone https://github.com/icyveins7/sew\ncd sew\npip install -e .\n```\n\nYou should then be able to use it from anywhere. Updates just require a ```git pull``` command on your repository folder.\n\nThere are no additional requirements to install to use the base functionality, as this is just a wrapper around the default-included sqlite for Python. However, if you want to use the extra plugins, you can do\n\n```\npip install -r plugin_requirements.txt\n```\n\n# Usage\nThe most common use-case is to initialise a ```Database``` object, just like you would with sqlite3.\n\n```python\nimport sew\nd = sew.Database(\":memory:\")\n```\n\nThis contains the sqlite3.Connection as well as the sqlite3.Cursor objects, referenced with ```.con``` and ```.cur``` respectively.\n\nSome common commands have shortcuts, if that's all you want to do:\n\n```python\nd.execute() # Same as d.cur.execute\nd.executemany() # Same as d.cur.executemany\nd.commit() # Same as d.con.commit\n```\n\nYou also get a context manager for free:\n\n```python\nwith sew.Database(\":memory:\") as db:\n db.execute(\"create table tbl(col INTEGER))\")\n\n# db connect is now closed i.e. .close() called on db.con\n# further statements to the cursor will result in sqlite3.ProgrammingError\ndb.execute(\"select * from tbl\") # ERROR\n\n# This is unlike the default sqlite3 connection which allows this\nwith sq.connect(\":memory:\") as sqcon:\n sqcur = sqcon.cursor()\n sqcur.execute(\"create table tbl(col INTEGER))\")\n\nsqcur.execute(\"select * from tbl\") # This is allowed, sqcon is still valid!\n```\n\nBut the main benefit is in shortened requirements for database changes. Docstrings are available for most common methods.\n\nAs an example, instead of writing the whole statements, you can do the following:\n\n## Create Tables\n\n```python\nd.createTable(fmt, \"mytablename\")\n# You can now access this table via dict-like methods like d['mytablename']\n# See below for uses\n```\n\n## Format Specifiers\nThis framework operates using a pre-defined dictionary structure specifying each table's format. Fundamentally, this is just a dictionary that looks like this:\n\n```python\nfmt = {\n 'cols': [\n ['col1', 'integer'], # list of lists, with inner list begin column name and sqlite type in that order\n ['col2', 'REAL'] # the type upper/lower-case doesn't matter\n ],\n 'conds': [\n \"UNIQUE(col1, col2)\" # this is just a list of strings, with each one specifying an extra condition\n ],\n 'foreign_keys': [\n [\"col1\", \"parent_table(parent_col)\"] # This specifies a foreign key relationship\n ]\n}\n```\n\nYou can specify this directly yourself, if you're familiar with sqlite3, or you can use the ```FormatSpecifier``` object to generate one like so:\n\n```python\nfmtspec = sew.FormatSpecifier()\nfmtspec.addColumn('col1', int) # Yes, it accepts the pythonic type directly\nfmtspec.addColumn('col2', float)\nfmtspec.addUniques(['col1','col2'])\nfmtspec.addForeignKey([\"col1\", \"parent_table(parent_col)\"])\nfmt = fmtspec.generate() # Then use this wherever you need, like the createTable() call\n```\n\nExample of how the framework uses this in the create table example:\n\n```python\nstmt = d.createTable(fmt, 'tbl2')\n>>> 'create table \"tbl2\"(col1 INTEGER, col2 REAL, UNIQUE(col1,col2), FOREIGN KEY(col1) REFERENCES parent_table(parent_col))'\n```\n\nYou can also reference a format specifier from the table; this should be identical to what was used during creation.\nBut for cases where you need a python-parsable object that reflects the table schema:\n\n```python\nfmt = d['tbl2'].formatSpecifier\n\n>>> {'cols': [['col1', 'INTEGER'], ['col2', 'REAL']],\n 'conds': ['UNIQUE(col1,col2)'],\n 'foreign_keys': [['col1', 'parent_table(parent_col)']]}\n```\n\n## Selects\n\n```python\nstmt = d[\"mytablename\"].select([\"thisColumn\", \"thatColumn\"], [\"otherColumn < 10\"])\nresults = d.fetchall()\nprint(stmt) # Most commands return the statement generated, so you can check if needed\n```\n\n## Inserts\n\nInsertions of complete rows (which is probably the most common scenario):\n\n```python\nd[\"mytablename\"].insertOne(\n 1, 2.0, # For insertOne, just place the arguments one after another; no need to pack into a list/tuple/dict\n orReplace=True # use a replace instead of insert\n)\n\ndata = ((i, float(i+1)) for i in range(10)) # Generator expression\nd[\"mytablename\"].insertMany(\n data # Anything that works with executemany should work here\n) \n```\n\nYou can also insert specific columns:\n\n```python\n# Columns are col1, col2, col3\nd[\"mytablename\"].insertOne(\n {'col1': 1, 'col2': 2}, # Only insert 2 column values, leaving col3 NULL\n orReplace=True # use a replace instead of insert\n)\n```\n\n## Hard Refresh of Internal Tables Structure\n\n```python\nd.reloadTables()\n```\n\nThis is generally only required to be performed when commits of table changes are made from another source - outside the current interpreter. This will force ```sew``` to re-execute a select statement from ```sqlite_master``` and re-populate the internal tables.\nIt is automatically called when first instantiating the ```sew.Database``` object.\n\n## Other Examples\n\nMany of the examples are encoded into actual tests! Take a look at the ```tests``` folder to see examples of everything from standard selects and inserts to the use of the plugins.\n\n\n# Plugins\n\n## Pandas\nYou can return Pandas dataframes using the ```PandasDatabase```, which simply forwards selects to ```read_sql```, but provides the same automatic statement generation behaviour and dictionary-like table access as before. The difference is that results are now extracted via the property ```.pdresults```.\n\n```python\nimport sew.plugins\n\nd = sew.plugins.PandasDatabase(...)\nd['mytable'].select(...)\ndataframe = d['mytable'].pdresults\n```\n\n## NumPy\nIf you'd like direct NumPy array support (rather than converting to and from Pandas dataframes), you can use the ```NumpyDatabase```, which performs inserts by assigning each array to a column. Select-statement behaviour returns arrays as a named dictionary, with keys as the column names.\n\n```python\nimport sew.plugins\n\nd = sew.plugins.NumpyDatabase(...)\n\ndata_f64 = np.array([...], dtype=np.float64)\ndata_f32 = np.array([...], dtype=np.float32)\n\n# Table is created with \"col1_f64 REAL, col2_f32 REAL\"\nd['mytable'].insertMany(data_f64, data_f32)\n```\n\nAll datatypes should be preserved where possible; that means if you inserted an np.uint8 array, it should automatically return as an np.uint8 array. This is achieved via explicit encoding of the datatypes into the column names as suffixes. You can view these as the class global variable ```NummpyTableProxy.numpyColumnSuffixes```.\n\n\n# Running Unit Tests and Benchmarks\nUnit tests in the ```tests``` folder are a good way to look at some examples. To run them all, simply run the following command from the main repository ```sew``` folder (where the ```tests``` folder is visible):\n\n```bash\npython -m tests\n```\n\nIndividual tests can be run by simply doing:\n\n```bash\npython -m tests.correctness\n```\n\nBenchmarks are similarly run:\n\n```bash\npython -m benchmarks\n```\n",
"bugtrack_url": null,
"license": "MIT License Copyright (c) 2022 icyveins7 Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the \"Software\"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED \"AS IS\", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. ",
"summary": "Sqlite Extensions and Wrappers",
"version": "0.0.4",
"project_urls": {
"Repository": "https://github.com/icyveins7/sew"
},
"split_keywords": [
"sqlite",
"wrapper",
"extensions"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "47db19a3acf660ef631d9ed20fcb666b47cb3be427011420c6fdd82275ed8b3b",
"md5": "095636654181107066f6267b22dc7bb6",
"sha256": "647711d2ec0881dd00312a1d1332e88cf4a40ffbfa9b4f6a4eff222e89f8e35b"
},
"downloads": -1,
"filename": "s3ew-0.0.4-py3-none-any.whl",
"has_sig": false,
"md5_digest": "095636654181107066f6267b22dc7bb6",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": null,
"size": 35272,
"upload_time": "2024-03-08T14:33:21",
"upload_time_iso_8601": "2024-03-08T14:33:21.060806Z",
"url": "https://files.pythonhosted.org/packages/47/db/19a3acf660ef631d9ed20fcb666b47cb3be427011420c6fdd82275ed8b3b/s3ew-0.0.4-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "2e27d86573eed6a999c6d0bcb278406ff49f428dee5ccb16d585be8f827581c1",
"md5": "43e8a2fc1bdd2195fc639587baf77e6a",
"sha256": "51effbcd0d8aa295897cb48c16f990b591c1d8472567b084132725808f403341"
},
"downloads": -1,
"filename": "s3ew-0.0.4.tar.gz",
"has_sig": false,
"md5_digest": "43e8a2fc1bdd2195fc639587baf77e6a",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 31495,
"upload_time": "2024-03-08T14:33:23",
"upload_time_iso_8601": "2024-03-08T14:33:23.135461Z",
"url": "https://files.pythonhosted.org/packages/2e/27/d86573eed6a999c6d0bcb278406ff49f428dee5ccb16d585be8f827581c1/s3ew-0.0.4.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-03-08 14:33:23",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "icyveins7",
"github_project": "sew",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "s3ew"
}
