# dbpd
**The main theme of this module is to establish a working relationship between
relational databases and Pandas DataFrames; because these objects are tabular in nature,
it reveals itself to be an efficient way to inspect, manage and manipulate a given database.**
The dbpd.BaseDBPD class is the parent class for six child classes that are specific to different database types:<br>
<br>dbpd.Access
<br>dbpd.MySQL
<br>dbpd.Oracle
<br>dbpd.Postgres
<br>dbpd.SQLite
<br>dbpd.SQLiteInMemory
Abstractions have been created such that working with these various database types
is consistent throughout the user's code base.
The ```query() ``` method is responsible for returning SELECT sql statements as
their respective DataFrames. However the ```query()``` method can also be used to make changes to
the database (such as INSERT, UPDATE, DELETE, etc.)
Users are expected to write their own sql statements to query the database, and parameterized
queries are accepted and encouraged as well.
This is not meant to be an Object-Relational-Mapper (ORM) and has no such functionality, although
it may be possible for users to create their own ORM using the classes herein.
DOCUMENTATION
https://zacharybeebe.github.io/dbpd/
## Installation
```bash
pip install dbpd
```
## Connecting to a Database #1 - Direct
```python
from dbpd import Oracle
oracle = Oracle(
username='example_username',
password='example_password',
host='127.0.0.1',
sid='prod',
port=5000,
threaded=True,
description='My Example Oracle database',
show_description=True
)
dataframe = oracle.query('SELECT * FROM example_table')
oracle.close()
```
## Connecting to a Database #2 - Inheritance
```python
from dbpd import Postgres
class ExamplePostgres(Postgres):
def __init__(self):
super(ExamplePostgres, self).__init__(
username='example_username',
password='example_password',
host='127.0.0.1',
database_name='example',
port=5000,
postgres_schema='public',
description='My Example Postgres database',
show_description=True
)
def awesome_custom_method(self):
print('I love pandas and databases')
pg = ExamplePostgres()
dataframe = pg.query('SELECT * FROM public.example_table')
pg.close()
```
## Other Examples
```python
from dbpd import Access, MySQL, SQLite, SQLiteInMemory
# Connect to existing Access Database
existing_access = Access(
filepath='path/to/existing/access.accdb',
fernet_encryption_key=b'<theFernetEncryptionKeyForYourDatabase>',
description='My Existing Access database',
show_description=True
)
existing_access.close()
#####################################################################
# Create new, blank Access Database
new_access = Access(
filepath='path/to/non-existent/access.accdb',
fernet_encryption_key=b'<theFernetEncryptionKeyForYourDatabase>',
description='My New Access database',
show_description=True
)
new_access.query(
sql="""
CREATE TABLE my_table (
[a_number] INTEGER,
[a_date] DATETIME,
[a_double] DOUBLE,
[a_string] VARCHAR
);
"""
)
new_access.commit()
new_access.insert_values(
table_name='my_table',
a_number=1,
a_date=new_access.dt_now(),
a_double=22.22,
a_string='HelloWorld'
)
new_access.commit()
new_access.close()
#####################################################################
# Connect to MySQL Database
mysql = MySQL(
username='example_username',
password='example_password',
host='127.0.0.1',
database_name='example',
port=5000
)
# Export query to SQLite database
dataframe = mysql.export_query_to_sqlite(
out_filepath='path/to/export/sqlite.db',
out_table_name='exported_table',
in_sql="""
SELECT
A.*,
B.*
FROM
example_table A
LEFT JOIN (
SELECT
*
FROM
other_table
) B ON A.id = B.id
WHERE
A.column = :value
""",
in_parameters={'value': 'This value'}
)
mysql.close()
#####################################################################
# Connect to an Existing SQLite database
existing_sqlite = SQLite(
filepath='path/to/existing/sqlite.db',
)
existing_sqlite.close()
#####################################################################
# Create a new, blank SQLite database
new_sqlite = SQLite(
filepath='path/to/non-existent/sqlite.db',
)
new_sqlite.close()
#####################################################################
# Create a new in-memory SQLite database and save to disk
in_mem_sqlite = SQLiteInMemory()
in_mem_sqlite.query(
sql="""
CREATE TABLE my_table (
[a_number] INTEGER,
[a_date] DATETIME,
[a_double] DOUBLE,
[a_string] VARCHAR
);
"""
)
in_mem_sqlite.commit()
in_mem_sqlite.insert_values(
table_name='my_table',
a_number=1,
a_date=new_access.dt_now(),
a_double=22.22,
a_string='HelloWorld'
)
in_mem_sqlite.commit()
saved_sqlite = in_mem_sqlite.save_as(
filepath='path/to/sqlite.db',
return_new_database_manager=True
)
in_mem_sqlite.close()
dataframe = saved_sqlite.query('SELECT * FROM my_table')
saved_sqlite.close()
```
Raw data
{
"_id": null,
"home_page": "https://github.com/zacharybeebe/dbpd",
"name": "dbpd",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3.8",
"maintainer_email": "",
"keywords": "database,pandas,DataFrameAccess,MySQL,Oracle,Postgres,SQLite",
"author": "Zach Beebe",
"author_email": "z.beebe@yahoo.com",
"download_url": "https://files.pythonhosted.org/packages/28/88/bf49d948c09e99eb84836dcc723c91f5c064d5db1e3fdb68f6ec98dbc339/dbpd-1.1.tar.gz",
"platform": null,
"description": "# dbpd\r\n\r\n**The main theme of this module is to establish a working relationship between\r\nrelational databases and Pandas DataFrames; because these objects are tabular in nature,\r\nit reveals itself to be an efficient way to inspect, manage and manipulate a given database.**\r\n\r\nThe dbpd.BaseDBPD class is the parent class for six child classes that are specific to different database types:<br>\r\n<br>dbpd.Access\r\n<br>dbpd.MySQL\r\n<br>dbpd.Oracle\r\n<br>dbpd.Postgres\r\n<br>dbpd.SQLite\r\n<br>dbpd.SQLiteInMemory\r\n\r\nAbstractions have been created such that working with these various database types\r\nis consistent throughout the user's code base.\r\n\r\nThe ```query() ``` method is responsible for returning SELECT sql statements as\r\ntheir respective DataFrames. However the ```query()``` method can also be used to make changes to\r\nthe database (such as INSERT, UPDATE, DELETE, etc.)\r\n\r\nUsers are expected to write their own sql statements to query the database, and parameterized\r\nqueries are accepted and encouraged as well.\r\n\r\nThis is not meant to be an Object-Relational-Mapper (ORM) and has no such functionality, although\r\nit may be possible for users to create their own ORM using the classes herein.\r\n\r\n\r\nDOCUMENTATION\r\nhttps://zacharybeebe.github.io/dbpd/\r\n\r\n## Installation\r\n```bash\r\npip install dbpd\r\n```\r\n\r\n\r\n## Connecting to a Database #1 - Direct\r\n```python\r\nfrom dbpd import Oracle\r\n\r\noracle = Oracle(\r\n username='example_username',\r\n password='example_password',\r\n host='127.0.0.1',\r\n sid='prod',\r\n port=5000,\r\n threaded=True,\r\n description='My Example Oracle database',\r\n show_description=True\r\n)\r\ndataframe = oracle.query('SELECT * FROM example_table')\r\noracle.close()\r\n```\r\n\r\n\r\n## Connecting to a Database #2 - Inheritance\r\n```python\r\nfrom dbpd import Postgres\r\n\r\nclass ExamplePostgres(Postgres):\r\n def __init__(self):\r\n super(ExamplePostgres, self).__init__(\r\n username='example_username',\r\n password='example_password',\r\n host='127.0.0.1',\r\n database_name='example',\r\n port=5000,\r\n postgres_schema='public',\r\n description='My Example Postgres database',\r\n show_description=True\r\n )\r\n \r\n def awesome_custom_method(self):\r\n print('I love pandas and databases')\r\n\r\npg = ExamplePostgres()\r\ndataframe = pg.query('SELECT * FROM public.example_table')\r\npg.close()\r\n```\r\n\r\n\r\n\r\n## Other Examples\r\n```python\r\nfrom dbpd import Access, MySQL, SQLite, SQLiteInMemory\r\n\r\n# Connect to existing Access Database\r\nexisting_access = Access(\r\n filepath='path/to/existing/access.accdb',\r\n fernet_encryption_key=b'<theFernetEncryptionKeyForYourDatabase>',\r\n description='My Existing Access database',\r\n show_description=True\r\n)\r\nexisting_access.close()\r\n\r\n#####################################################################\r\n# Create new, blank Access Database\r\nnew_access = Access(\r\n filepath='path/to/non-existent/access.accdb',\r\n fernet_encryption_key=b'<theFernetEncryptionKeyForYourDatabase>',\r\n description='My New Access database',\r\n show_description=True\r\n)\r\nnew_access.query(\r\n sql=\"\"\"\r\n CREATE TABLE my_table (\r\n [a_number] INTEGER,\r\n [a_date] DATETIME,\r\n [a_double] DOUBLE,\r\n [a_string] VARCHAR\r\n );\r\n \"\"\"\r\n)\r\nnew_access.commit()\r\nnew_access.insert_values(\r\n table_name='my_table',\r\n a_number=1,\r\n a_date=new_access.dt_now(),\r\n a_double=22.22,\r\n a_string='HelloWorld'\r\n)\r\nnew_access.commit()\r\nnew_access.close()\r\n\r\n#####################################################################\r\n# Connect to MySQL Database\r\nmysql = MySQL(\r\n username='example_username',\r\n password='example_password',\r\n host='127.0.0.1',\r\n database_name='example',\r\n port=5000\r\n)\r\n# Export query to SQLite database\r\ndataframe = mysql.export_query_to_sqlite(\r\n out_filepath='path/to/export/sqlite.db',\r\n out_table_name='exported_table',\r\n in_sql=\"\"\"\r\n SELECT\r\n A.*,\r\n B.*\r\n FROM\r\n example_table A\r\n LEFT JOIN (\r\n SELECT\r\n *\r\n FROM\r\n other_table\r\n ) B ON A.id = B.id\r\n WHERE\r\n A.column = :value\r\n \"\"\",\r\n in_parameters={'value': 'This value'} \r\n)\r\nmysql.close()\r\n\r\n#####################################################################\r\n# Connect to an Existing SQLite database\r\nexisting_sqlite = SQLite(\r\n filepath='path/to/existing/sqlite.db',\r\n)\r\nexisting_sqlite.close()\r\n\r\n#####################################################################\r\n# Create a new, blank SQLite database\r\nnew_sqlite = SQLite(\r\n filepath='path/to/non-existent/sqlite.db',\r\n)\r\nnew_sqlite.close()\r\n\r\n#####################################################################\r\n# Create a new in-memory SQLite database and save to disk\r\nin_mem_sqlite = SQLiteInMemory()\r\nin_mem_sqlite.query(\r\n sql=\"\"\"\r\n CREATE TABLE my_table (\r\n [a_number] INTEGER,\r\n [a_date] DATETIME,\r\n [a_double] DOUBLE,\r\n [a_string] VARCHAR\r\n );\r\n \"\"\"\r\n)\r\nin_mem_sqlite.commit()\r\nin_mem_sqlite.insert_values(\r\n table_name='my_table',\r\n a_number=1,\r\n a_date=new_access.dt_now(),\r\n a_double=22.22,\r\n a_string='HelloWorld'\r\n)\r\nin_mem_sqlite.commit()\r\nsaved_sqlite = in_mem_sqlite.save_as(\r\n filepath='path/to/sqlite.db',\r\n return_new_database_manager=True\r\n)\r\nin_mem_sqlite.close()\r\ndataframe = saved_sqlite.query('SELECT * FROM my_table')\r\nsaved_sqlite.close()\r\n```\r\n\r\n\r\n\t\t\r\n\r\n\r\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "Python module for establishing a working relationship between relational databases and Pandas DataFrames",
"version": "1.1",
"project_urls": {
"Homepage": "https://github.com/zacharybeebe/dbpd"
},
"split_keywords": [
"database",
"pandas",
"dataframeaccess",
"mysql",
"oracle",
"postgres",
"sqlite"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "172616b96667273dd79229326dab5f7a2ec03a07ab12479a69c312970d74c2b1",
"md5": "6bb811ce090d157a2bcc9d7ce30fa3eb",
"sha256": "981c99b2bff6f1bb3c364166a216b436899d38fc623da3cf96a4c35f548148ea"
},
"downloads": -1,
"filename": "dbpd-1.1-py3-none-any.whl",
"has_sig": false,
"md5_digest": "6bb811ce090d157a2bcc9d7ce30fa3eb",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.8",
"size": 17301,
"upload_time": "2023-09-19T20:42:38",
"upload_time_iso_8601": "2023-09-19T20:42:38.939524Z",
"url": "https://files.pythonhosted.org/packages/17/26/16b96667273dd79229326dab5f7a2ec03a07ab12479a69c312970d74c2b1/dbpd-1.1-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "2888bf49d948c09e99eb84836dcc723c91f5c064d5db1e3fdb68f6ec98dbc339",
"md5": "230d363319de68a79fe269e7ebb71896",
"sha256": "a35caee6618ce455f9bb583c7c05e82ecfb2cfc238b04147af5b53365c12611f"
},
"downloads": -1,
"filename": "dbpd-1.1.tar.gz",
"has_sig": false,
"md5_digest": "230d363319de68a79fe269e7ebb71896",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.8",
"size": 10938799,
"upload_time": "2023-09-19T20:42:57",
"upload_time_iso_8601": "2023-09-19T20:42:57.454199Z",
"url": "https://files.pythonhosted.org/packages/28/88/bf49d948c09e99eb84836dcc723c91f5c064d5db1e3fdb68f6ec98dbc339/dbpd-1.1.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-09-19 20:42:57",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "zacharybeebe",
"github_project": "dbpd",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"requirements": [],
"lcname": "dbpd"
}
JSON
