*******
Pandemy
*******
|PyPI| |conda-forge| |Python| |conda-forge-platform| |Docs| |LICENSE|
Pandemy is a wrapper around `pandas`_ and `SQLAlchemy`_ to provide an easy class based interface for working with DataFrames and databases.
This package is for those who enjoy working with pandas and SQL but do not want to learn all "bells and whistles" of SQLAlchemy.
Use your existing SQL knowledge and provide text based SQL statements to load DataFrames from and write DataFrames to databases.
.. _pandas: https://pandas.pydata.org/
.. _SQLAlchemy: https://www.sqlalchemy.org/
Installation
============
Pandemy is available for installation through `PyPI`_ using `pip`_ and conda-forge_ using conda_.
The source code is hosted on GitHub at: https://github.com/antonlydell/Pandemy
.. _conda: https://docs.conda.io/en/latest/
.. _conda-forge: https://anaconda.org/conda-forge/pandemy
.. _pip: https://pip.pypa.io/en/stable/getting-started/
.. _PyPI: https://pypi.org/project/pandemy/
Install with pip:
.. code-block:: bash
$ pip install Pandemy
Install with conda:
.. code-block:: bash
$ conda install -c conda-forge pandemy
Dependencies
------------
The core dependencies of Pandemy are:
- **pandas** : powerful Python data analysis toolkit
- **SQLAlchemy** : The Python SQL Toolkit and Object Relational Mapper
Databases except for SQLite_ require a third-party database driver package to be installed.
The table below lists database driver packages for supported databases and their corresponding `optional dependency identifier`_.
.. csv-table:: Optional dependencies of Pandemy.
:delim: ;
:header-rows: 1
:align: left
Database;Driver package;Optional dependency identifier;Version added
Oracle_;cx_Oracle_;oracle; 1.1.0
To install `cx_Oracle`_ together with Pandemy run:
.. code-block:: bash
$ pip install Pandemy[oracle]
When using conda supply the driver package as a separate argument to the install command:
.. code-block:: bash
$ conda install -c conda-forge pandemy cx_oracle
.. _cx_Oracle: https://oracle.github.io/python-cx_Oracle/
.. _optional dependency identifier: https://setuptools.pypa.io/en/latest/userguide/dependency_management.html#optional-dependencies
.. _Oracle: https://www.oracle.com/database/
.. _SQLite: https://sqlite.org/index.html
A DataFrame to and from table round trip
========================================
This section shows a simple example of writing a DataFrame_ to a SQLite database and reading it back again.
.. _DataFrame: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.html
Save a DataFrame to a table
---------------------------
Let's create a new SQLite database and save a DataFrame to it.
.. code-block:: python
import io
import pandas as pd
import pandemy
# Data to save to the database
data = io.StringIO("""
ItemId,ItemName,MemberOnly,Description
1,Pot,0,This pot is empty.
2,Jug,0,This jug is empty.
3,Shears,0,For shearing sheep.
4,Bucket,0,It's a wooden bucket.
5,Bowl,0,Useful for mixing things.
6,Amulet of glory,1,A very powerful dragonstone amulet.
""")
df = pd.read_csv(filepath_or_buffer=data, index_col='ItemId') # Create a DataFrame
# SQL statement to create the table Item in which to save the DataFrame df
create_table_item = """
-- The available items in General Stores
CREATE TABLE IF NOT EXISTS Item (
ItemId INTEGER,
ItemName TEXT NOT NULL,
MemberOnly INTEGER NOT NULL,
Description TEXT,
CONSTRAINT ItemPk PRIMARY KEY (ItemId)
);
"""
db = pandemy.SQLiteDb(file='Runescape.db') # Create the SQLite DatabaseManager instance
with db.engine.begin() as conn:
db.execute(sql=create_table_item, conn=conn)
db.save_df(df=df, table='Item', conn=conn)
The database is managed through the DatabaseManager_ class (in this case the SQLiteDb_ instance).
Each SQL dialect is a subclass of ``DatabaseManager``. The initialization of the ``DatabaseManager``
creates the database engine_, which is used to create a connection to the database. The begin_ method of
the engine returns a context manager with an open database transaction, which commits the statements if
no errors occur or performs a rollback on error. The connection is automatically returned to the engine's
connection pool when the context manager exits. If the database file does not exist it will be created.
The execute_ method allows for execution of arbitrary SQL statements such as creating a table. The save_df_
method saves the DataFrame ``df`` to the table *Item* in the database ``db``.
.. _begin: https://docs.sqlalchemy.org/en/14/core/connections.html#sqlalchemy.engine.Engine.begin
.. _DatabaseManager: https://pandemy.readthedocs.io/en/latest/api_reference/databasemanager.html#databasemanager
.. _engine: https://docs.sqlalchemy.org/en/14/core/connections.html#sqlalchemy.engine.Engine
.. _execute: https://pandemy.readthedocs.io/en/latest/api_reference/databasemanager.html#pandemy.DatabaseManager.execute
.. _save_df: https://pandemy.readthedocs.io/en/latest/api_reference/databasemanager.html#pandemy.DatabaseManager.save_df
.. _SQliteDb: https://pandemy.readthedocs.io/en/latest/api_reference/databasemanager.html#sqlitedb
Load a table into a DataFrame
-----------------------------
The DataFrame saved to the table *Item* of the database *Runescape.db* can easily be read back into a DataFrame.
.. code-block:: python
import pandas as pd
from pandas.testing import assert_frame_equal
import pandemy
db = pandemy.SQLiteDb(file='Runescape.db', must_exist=True)
sql = """SELECT * FROM Item ORDER BY ItemId;""" # Query to read back table Item into a DataFrame
with db.engine.connect() as conn:
df_loaded = db.load_table(sql=sql, conn=conn, index_col='ItemId')
assert_frame_equal(df, df_loaded, check_dtype=False)
print(df)
.. code-block::
ItemName MemberOnly Description
ItemId
1 Pot 0 This pot is empty.
2 Jug 0 This jug is empty.
3 Shears 0 For shearing sheep.
4 Bucket 0 It's a wooden bucket.
5 Bowl 0 Useful for mixing things.
6 Amulet of glory 1 A very powerful dragonstone amulet.
If the ``must_exist`` parameter is set to ``True`` `pandemy.DatabaseFileNotFoundError`_ will be raised if the database file is not found.
This is useful if you expect the database to exist and you want to avoid creating a new database by mistake if it
does not exist. The connect_ method of the engine is similar to begin_, but without opening a transaction.
The load_table_ method supports either a table name or a sql statement for the ``sql`` parameter.
.. _connect: https://docs.sqlalchemy.org/en/14/core/connections.html#sqlalchemy.engine.Engine.connect
.. _load_table: https://pandemy.readthedocs.io/en/latest/api_reference/databasemanager.html#pandemy.DatabaseManager.load_table
.. _pandemy.DatabaseFileNotFoundError: https://pandemy.readthedocs.io/en/latest/api_reference/exceptions.html#pandemy.DatabaseFileNotFoundError
Documentation
=============
The full documentation is hosted at: https://pandemy.readthedocs.io
Tests
=====
Pandemy has a test suite that is using the `pytest`_ framework.
The test suite is located in the directory `tests`_.
.. _pytest: https://docs.pytest.org/en/latest/
.. _tests: https://github.com/antonlydell/Pandemy/tree/main/tests
Run the test suite with the ``pytest`` command from the root directory of the repository:
.. code-block:: bash
$ pytest
License
=======
Pandemy is distributed under the `MIT-license`_.
.. _MIT-license: https://opensource.org/licenses/mit-license.php
Contributing
============
Suggestions, feature requests and feedback are welcome in *text form* on the tab `GitHub Discussions`_, but *not* as written code.
This project is meant as a source of practice for me to become a better Python developer and I prefer to write the code myself.
Please use the category `Ideas`_ for suggestions and feature request and the `General`_ category for feedback on the project and general questions.
Bug reports should be submitted at the `Github Issues`_ tab.
.. _Github Discussions: https://github.com/antonlydell/Pandemy/discussions
.. _Ideas: https://github.com/antonlydell/Pandemy/discussions/categories/ideas
.. _General: https://github.com/antonlydell/Pandemy/discussions/categories/general
.. _Github Issues: https://github.com/antonlydell/Pandemy/issues
.. |conda-forge| image:: https://img.shields.io/conda/vn/conda-forge/pandemy?style=plastic
:alt: conda-forge - Version
:scale: 100%
:target: https://anaconda.org/conda-forge/pandemy
.. |conda-forge-platform| image:: https://img.shields.io/conda/pn/conda-forge/pandemy?color=yellowgreen&style=plastic
:alt: conda-forge - Platform
:scale: 100%
:target: https://anaconda.org/conda-forge/pandemy
.. |Docs| image:: https://readthedocs.org/projects/pip/badge/?version=latest&style=plastic
:alt: Documentation status
:scale: 100%
:target: https://pandemy.readthedocs.io/en/latest/?badge=latest
.. |LICENSE| image:: https://img.shields.io/pypi/l/Pandemy?style=plastic
:alt: PyPI - License
:scale: 100%
:target: https://github.com/antonlydell/Pandemy/blob/main/LICENSE
.. |PyPI| image:: https://img.shields.io/pypi/v/Pandemy?style=plastic
:alt: PyPI
:scale: 100%
:target: https://pypi.org/project/Pandemy/
.. |Python| image:: https://img.shields.io/pypi/pyversions/Pandemy?style=plastic
:alt: PyPI - Python Version
:scale: 100%
:target: https://pypi.org/project/Pandemy/
Raw data
{
"_id": null,
"home_page": "",
"name": "Pandemy",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3.8",
"maintainer_email": "",
"keywords": "database,DataFrame,data analytics,data science,merge,Oracle,pandas,sql,SQLAlchemy,SQLite,update,upsert",
"author": "",
"author_email": "Anton Lydell <antoningenjoren@gmail.com>",
"download_url": "https://files.pythonhosted.org/packages/72/9a/a984e3b299f23e0d54295f848375f2d37c4278850cdec5399261b99a9464/Pandemy-1.2.0.tar.gz",
"platform": null,
"description": "*******\r\nPandemy\r\n*******\r\n\r\n|PyPI| |conda-forge| |Python| |conda-forge-platform| |Docs| |LICENSE|\r\n\r\nPandemy is a wrapper around `pandas`_ and `SQLAlchemy`_ to provide an easy class based interface for working with DataFrames and databases.\r\nThis package is for those who enjoy working with pandas and SQL but do not want to learn all \"bells and whistles\" of SQLAlchemy.\r\nUse your existing SQL knowledge and provide text based SQL statements to load DataFrames from and write DataFrames to databases.\r\n\r\n.. _pandas: https://pandas.pydata.org/\r\n.. _SQLAlchemy: https://www.sqlalchemy.org/\r\n\r\n\r\nInstallation\r\n============\r\n\r\nPandemy is available for installation through `PyPI`_ using `pip`_ and conda-forge_ using conda_.\r\nThe source code is hosted on GitHub at: https://github.com/antonlydell/Pandemy\r\n\r\n.. _conda: https://docs.conda.io/en/latest/\r\n.. _conda-forge: https://anaconda.org/conda-forge/pandemy\r\n.. _pip: https://pip.pypa.io/en/stable/getting-started/\r\n.. _PyPI: https://pypi.org/project/pandemy/\r\n\r\nInstall with pip:\r\n\r\n.. code-block:: bash\r\n\r\n $ pip install Pandemy\r\n\r\n\r\nInstall with conda:\r\n\r\n.. code-block:: bash\r\n\r\n $ conda install -c conda-forge pandemy\r\n\r\n\r\nDependencies\r\n------------\r\n\r\nThe core dependencies of Pandemy are:\r\n\r\n- **pandas** : powerful Python data analysis toolkit\r\n- **SQLAlchemy** : The Python SQL Toolkit and Object Relational Mapper\r\n\r\nDatabases except for SQLite_ require a third-party database driver package to be installed.\r\nThe table below lists database driver packages for supported databases and their corresponding `optional dependency identifier`_.\r\n\r\n.. csv-table:: Optional dependencies of Pandemy.\r\n :delim: ;\r\n :header-rows: 1\r\n :align: left\r\n\r\n Database;Driver package;Optional dependency identifier;Version added\r\n Oracle_;cx_Oracle_;oracle; 1.1.0\r\n\r\n\r\nTo install `cx_Oracle`_ together with Pandemy run:\r\n\r\n.. code-block:: bash\r\n\r\n $ pip install Pandemy[oracle]\r\n\r\n\r\nWhen using conda supply the driver package as a separate argument to the install command:\r\n\r\n.. code-block:: bash\r\n\r\n $ conda install -c conda-forge pandemy cx_oracle\r\n\r\n\r\n.. _cx_Oracle: https://oracle.github.io/python-cx_Oracle/\r\n.. _optional dependency identifier: https://setuptools.pypa.io/en/latest/userguide/dependency_management.html#optional-dependencies\r\n.. _Oracle: https://www.oracle.com/database/\r\n.. _SQLite: https://sqlite.org/index.html\r\n\r\n\r\nA DataFrame to and from table round trip\r\n========================================\r\n\r\nThis section shows a simple example of writing a DataFrame_ to a SQLite database and reading it back again.\r\n\r\n.. _DataFrame: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.html\r\n\r\n\r\nSave a DataFrame to a table\r\n---------------------------\r\n\r\nLet's create a new SQLite database and save a DataFrame to it.\r\n\r\n.. code-block:: python\r\n\r\n import io\r\n import pandas as pd \r\n import pandemy\r\n\r\n # Data to save to the database\r\n data = io.StringIO(\"\"\"\r\n ItemId,ItemName,MemberOnly,Description\r\n 1,Pot,0,This pot is empty.\r\n 2,Jug,0,This jug is empty.\r\n 3,Shears,0,For shearing sheep.\r\n 4,Bucket,0,It's a wooden bucket.\r\n 5,Bowl,0,Useful for mixing things.\r\n 6,Amulet of glory,1,A very powerful dragonstone amulet.\r\n \"\"\")\r\n\r\n df = pd.read_csv(filepath_or_buffer=data, index_col='ItemId') # Create a DataFrame\r\n\r\n # SQL statement to create the table Item in which to save the DataFrame df\r\n create_table_item = \"\"\"\r\n -- The available items in General Stores\r\n CREATE TABLE IF NOT EXISTS Item (\r\n ItemId INTEGER,\r\n ItemName TEXT NOT NULL,\r\n MemberOnly INTEGER NOT NULL,\r\n Description TEXT,\r\n\r\n CONSTRAINT ItemPk PRIMARY KEY (ItemId)\r\n );\r\n \"\"\"\r\n\r\n db = pandemy.SQLiteDb(file='Runescape.db') # Create the SQLite DatabaseManager instance\r\n\r\n with db.engine.begin() as conn:\r\n db.execute(sql=create_table_item, conn=conn)\r\n db.save_df(df=df, table='Item', conn=conn)\r\n\r\n\r\nThe database is managed through the DatabaseManager_ class (in this case the SQLiteDb_ instance).\r\nEach SQL dialect is a subclass of ``DatabaseManager``. The initialization of the ``DatabaseManager``\r\ncreates the database engine_, which is used to create a connection to the database. The begin_ method of\r\nthe engine returns a context manager with an open database transaction, which commits the statements if\r\nno errors occur or performs a rollback on error. The connection is automatically returned to the engine's\r\nconnection pool when the context manager exits. If the database file does not exist it will be created.\r\nThe execute_ method allows for execution of arbitrary SQL statements such as creating a table. The save_df_\r\nmethod saves the DataFrame ``df`` to the table *Item* in the database ``db``.\r\n\r\n.. _begin: https://docs.sqlalchemy.org/en/14/core/connections.html#sqlalchemy.engine.Engine.begin\r\n.. _DatabaseManager: https://pandemy.readthedocs.io/en/latest/api_reference/databasemanager.html#databasemanager\r\n.. _engine: https://docs.sqlalchemy.org/en/14/core/connections.html#sqlalchemy.engine.Engine\r\n.. _execute: https://pandemy.readthedocs.io/en/latest/api_reference/databasemanager.html#pandemy.DatabaseManager.execute\r\n.. _save_df: https://pandemy.readthedocs.io/en/latest/api_reference/databasemanager.html#pandemy.DatabaseManager.save_df\r\n.. _SQliteDb: https://pandemy.readthedocs.io/en/latest/api_reference/databasemanager.html#sqlitedb\r\n\r\n\r\nLoad a table into a DataFrame\r\n-----------------------------\r\n\r\nThe DataFrame saved to the table *Item* of the database *Runescape.db* can easily be read back into a DataFrame.\r\n\r\n.. code-block:: python\r\n\r\n import pandas as pd\r\n from pandas.testing import assert_frame_equal\r\n import pandemy\r\n\r\n db = pandemy.SQLiteDb(file='Runescape.db', must_exist=True)\r\n\r\n sql = \"\"\"SELECT * FROM Item ORDER BY ItemId;\"\"\" # Query to read back table Item into a DataFrame\r\n\r\n with db.engine.connect() as conn:\r\n df_loaded = db.load_table(sql=sql, conn=conn, index_col='ItemId')\r\n \r\n assert_frame_equal(df, df_loaded, check_dtype=False)\r\n print(df)\r\n\r\n\r\n.. code-block::\r\n\r\n ItemName MemberOnly Description\r\n ItemId\r\n 1 Pot 0 This pot is empty.\r\n 2 Jug 0 This jug is empty.\r\n 3 Shears 0 For shearing sheep.\r\n 4 Bucket 0 It's a wooden bucket.\r\n 5 Bowl 0 Useful for mixing things.\r\n 6 Amulet of glory 1 A very powerful dragonstone amulet.\r\n\r\n\r\nIf the ``must_exist`` parameter is set to ``True`` `pandemy.DatabaseFileNotFoundError`_ will be raised if the database file is not found.\r\nThis is useful if you expect the database to exist and you want to avoid creating a new database by mistake if it\r\ndoes not exist. The connect_ method of the engine is similar to begin_, but without opening a transaction.\r\nThe load_table_ method supports either a table name or a sql statement for the ``sql`` parameter.\r\n\r\n.. _connect: https://docs.sqlalchemy.org/en/14/core/connections.html#sqlalchemy.engine.Engine.connect\r\n.. _load_table: https://pandemy.readthedocs.io/en/latest/api_reference/databasemanager.html#pandemy.DatabaseManager.load_table\r\n.. _pandemy.DatabaseFileNotFoundError: https://pandemy.readthedocs.io/en/latest/api_reference/exceptions.html#pandemy.DatabaseFileNotFoundError\r\n\r\n\r\nDocumentation\r\n=============\r\n\r\nThe full documentation is hosted at: https://pandemy.readthedocs.io\r\n\r\n\r\nTests\r\n=====\r\n\r\nPandemy has a test suite that is using the `pytest`_ framework.\r\nThe test suite is located in the directory `tests`_.\r\n\r\n.. _pytest: https://docs.pytest.org/en/latest/\r\n.. _tests: https://github.com/antonlydell/Pandemy/tree/main/tests\r\n\r\nRun the test suite with the ``pytest`` command from the root directory of the repository:\r\n\r\n.. code-block:: bash\r\n\r\n $ pytest\r\n\r\n\r\nLicense\r\n=======\r\n\r\nPandemy is distributed under the `MIT-license`_.\r\n\r\n.. _MIT-license: https://opensource.org/licenses/mit-license.php\r\n\r\n\r\nContributing\r\n============\r\n\r\nSuggestions, feature requests and feedback are welcome in *text form* on the tab `GitHub Discussions`_, but *not* as written code.\r\nThis project is meant as a source of practice for me to become a better Python developer and I prefer to write the code myself.\r\nPlease use the category `Ideas`_ for suggestions and feature request and the `General`_ category for feedback on the project and general questions.\r\nBug reports should be submitted at the `Github Issues`_ tab.\r\n\r\n\r\n.. _Github Discussions: https://github.com/antonlydell/Pandemy/discussions\r\n\r\n.. _Ideas: https://github.com/antonlydell/Pandemy/discussions/categories/ideas\r\n\r\n.. _General: https://github.com/antonlydell/Pandemy/discussions/categories/general\r\n\r\n.. _Github Issues: https://github.com/antonlydell/Pandemy/issues\r\n\r\n\r\n.. |conda-forge| image:: https://img.shields.io/conda/vn/conda-forge/pandemy?style=plastic\r\n :alt: conda-forge - Version\r\n :scale: 100%\r\n :target: https://anaconda.org/conda-forge/pandemy\r\n\r\n.. |conda-forge-platform| image:: https://img.shields.io/conda/pn/conda-forge/pandemy?color=yellowgreen&style=plastic\r\n :alt: conda-forge - Platform\r\n :scale: 100%\r\n :target: https://anaconda.org/conda-forge/pandemy\r\n\r\n.. |Docs| image:: https://readthedocs.org/projects/pip/badge/?version=latest&style=plastic \r\n :alt: Documentation status\r\n :scale: 100%\r\n :target: https://pandemy.readthedocs.io/en/latest/?badge=latest\r\n\r\n.. |LICENSE| image:: https://img.shields.io/pypi/l/Pandemy?style=plastic\r\n :alt: PyPI - License\r\n :scale: 100%\r\n :target: https://github.com/antonlydell/Pandemy/blob/main/LICENSE\r\n\r\n.. |PyPI| image:: https://img.shields.io/pypi/v/Pandemy?style=plastic\r\n :alt: PyPI\r\n :scale: 100%\r\n :target: https://pypi.org/project/Pandemy/\r\n\r\n.. |Python| image:: https://img.shields.io/pypi/pyversions/Pandemy?style=plastic\r\n :alt: PyPI - Python Version\r\n :scale: 100%\r\n :target: https://pypi.org/project/Pandemy/\r\n",
"bugtrack_url": null,
"license": "MIT License",
"summary": "A wrapper around pandas and SQLAlchemy to provide an easy interface for DataFrames and databases.",
"version": "1.2.0",
"split_keywords": [
"database",
"dataframe",
"data analytics",
"data science",
"merge",
"oracle",
"pandas",
"sql",
"sqlalchemy",
"sqlite",
"update",
"upsert"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "58b4ec1e6f0347681b979bd5174bdc8284ad87e22ea10ced156aa4238505fe6c",
"md5": "d2b7172331f2cded14ca2580403a3582",
"sha256": "cd7f9dd7571e5bb8f2bcca4a4beb4868f3faa61d71d4540c2b966c1e83503646"
},
"downloads": -1,
"filename": "Pandemy-1.2.0-py3-none-any.whl",
"has_sig": false,
"md5_digest": "d2b7172331f2cded14ca2580403a3582",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.8",
"size": 35680,
"upload_time": "2023-02-06T19:57:04",
"upload_time_iso_8601": "2023-02-06T19:57:04.761484Z",
"url": "https://files.pythonhosted.org/packages/58/b4/ec1e6f0347681b979bd5174bdc8284ad87e22ea10ced156aa4238505fe6c/Pandemy-1.2.0-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "729aa984e3b299f23e0d54295f848375f2d37c4278850cdec5399261b99a9464",
"md5": "7b06cd6dc22ca2eeedf38599ecf9d04d",
"sha256": "364e768ddaa87b47eb490b584f7948a70b2d2b897f5d3a52e839f6b0c03e43a3"
},
"downloads": -1,
"filename": "Pandemy-1.2.0.tar.gz",
"has_sig": false,
"md5_digest": "7b06cd6dc22ca2eeedf38599ecf9d04d",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.8",
"size": 33726,
"upload_time": "2023-02-06T19:57:06",
"upload_time_iso_8601": "2023-02-06T19:57:06.631981Z",
"url": "https://files.pythonhosted.org/packages/72/9a/a984e3b299f23e0d54295f848375f2d37c4278850cdec5399261b99a9464/Pandemy-1.2.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-02-06 19:57:06",
"github": false,
"gitlab": false,
"bitbucket": false,
"lcname": "pandemy"
}
JSON