{ "info": { "author": "jojoduquartier", "author_email": "", "bugtrack_url": null, "classifiers": [ "Development Status :: 3 - Alpha", "Intended Audience :: Developers", "License :: OSI Approved :: MIT License", "Programming Language :: Python :: 3.6", "Programming Language :: Python :: 3.7", "Topic :: Database :: Database Engines/Servers" ], "description": "# dsdbmanager\nData Science DataBase Manager\n\n[![Travis](https://travis-ci.com/jojoduquartier/dsdbmanager.svg?branch=master)](\n https://travis-ci.com/jojoduquartier/dsdbmanager)\n[![Documentation Status](https://readthedocs.org/projects/dsdbmanager/badge/?version=latest)](\nhttps://dsdbmanager.readthedocs.io/en/latest/?badge=latest)\n[![codecov](https://codecov.io/gh/jojoduquartier/dsdbmanager/branch/master/graph/badge.svg)](\n https://codecov.io/gh/jojoduquartier/dsdbmanager)\n\nDo you love SqlAlchemy? Do you usually have to connect to the same databases all the time for some quick data processing and/or exploration?\n\n \n\nWhen dealing with simple data processing (especially with different databases with relatively small tables), it does not always make sense\nto have the something like\n```python\nimport sqlalchemy\nimport cx_Oracle\n\n```\n\nin the header of each script. There are many ways of dealing with these issues but this project tries to provide an example\nfor the new data scientist. This project is not meant to be used in production; the goal is to speed up exploration by eliminating \nsome frequent database connection activities.\n\n* The examples below are based on a locally hosted [MySql](https://www.mysql.com/products/workbench/) database\n\nConsider the following database **dstest** with tables **category** and **user**. The database host, schema and/or port can be\nstored in a json file that the package uses to quickly create sqlalchemy engines. \n\n![mysql workbench](https://github.com/jojoduquartier/dsdbmanager/blob/master/source/imgs/workbench.png)\n\n## Adding databases\nAfter (or before) installation of the package, set an envionment variable `DSDBMANAGER_CONFIG` pointing to a folder where the configuration files can be stored.\nIf the environment variable is not available, `pathlib.Path.home() / \".dsdbmanager\"` is used by default.\n\nUse entry point command `dsdbmanager add-database` directly in a command shell to add a database.\n\nOr any python interactive interactive shell:\n\n![add database](https://github.com/jojoduquartier/dsdbmanager/blob/master/source/imgs/add_db.gif) \n\nThis will add the database directly to the `.host.json` file automatically created at first `import`.\n\n![host json](https://github.com/jojoduquartier/dsdbmanager/blob/master/source/imgs/host.png)\n\n## Connecting\n\n#### Manual Connection \nOnce a database is added, it is easy to connect to it using one of the following modules (each corresponding to a sql flavor/dialect) \nto establish a connection. This approach means that the user only needs to provide the username and password to create the engines.\n\n\n\n![mysql connect](https://github.com/jojoduquartier/dsdbmanager/blob/master/source/imgs/manualconnection.png)\n\n#### Connecting With Shortcut\nIn order to save encrypted credentials for reuse, the project comes with a shortcut for each flavor/dialect\n\n![shortcut](https://github.com/jojoduquartier/dsdbmanager/blob/master/source/imgs/using_shortcut.png)\n\nThis approach creates an object that has the name of each database as a method. The image above shows the only mysql database `dstest` as a property.\nThis property is actually a function which must be called to establish connection.\n\n![connect](https://github.com/jojoduquartier/dsdbmanager/blob/master/source/imgs/first_time.png) \n\nBecause the connection is only made when the function is called, all the databases available are just properties until the user needs them.\nWhen a connection is attempted for the first time, the user is prompted for the username and password. The credentials are used to create the\nsqlalchemy engine which is used to test for successful connection. If the connection fails, the credentials are discarded and an error is raised. If\nthe connection succeeds, the credentials are encrypted and stored in a file like this:\n\n```json\n{\n \"mysql\": {\n \"dstest\": {\n \"username\": \"gAAAAABdYe_Le1rX3W5y23GlLf0dtrVVOWJhaPGVk2_CbIfpcqb_0dzu5_MFJpgTRuXF7EKk3UcLvCI5HyjP6b5daZQoMJRM2g==\",\n \"password\": \"gAAAAABdYe_LtuwnY95B0nhsSKQbe8DEuvhbjO2Y9zo-PwC_UqsmQ1whRsGyTlZGc3RRyWc3yde6cGozxPJjcjZv77itSuyKVg==\"\n }\n }\n}\n```\n\nThe credentials are retrieved every other time so that the user never has to pass the host or password info when needed.\n\n#### Connect_Only = False\nWhat happens when `connect_only = False`? The object created has not only an engine but:\n\n
    \n
  1. Attribute '_metadata': provides the metadata for each table/view in the schema\n\n![metadata](https://github.com/jojoduquartier/dsdbmanager/blob/master/source/imgs/metadata.png)\n
    2. \n\n
  2. Every table or view in the schema becomes an attribute that can be used to pull data into a dataframe\n\n![read data](https://github.com/jojoduquartier/dsdbmanager/blob/master/source/imgs/read_table.png)\n
    3. \n\n
  3. Attribute '_insert': provides a function that uses a pandas dataframe to insert records into a table\n\n![read data](https://github.com/jojoduquartier/dsdbmanager/blob/master/source/imgs/insert.png)\n\nThe picture above shows that `2` records were inserted in the `category` table of `dstest` and we can see it here\n\n![read data](https://github.com/jojoduquartier/dsdbmanager/blob/master/source/imgs/inserted.png)\n
    4. \n
  4. Attribute '_update': provides a function that uses a pandas dataframe to update records in a table
    5. \n
\n\n#### Context Manager\nIt is possible to connect to the databases with a context manager approach and this is highly suggested. Sqlachemy engines\nare disposed and properties pointing to tables etc. are all cleared\n\n![context manager](https://github.com/jojoduquartier/dsdbmanager/blob/master/source/imgs/as_context_manager.png)\n\nThe context manager approach is way cleaner anyways.\n```python\nfrom dsdbmanager import mysql\n\nwith mysql().dstest(connect_only=True, schema=None) as dbobject:\n # anything with the engine goes here\n pass\n```\n\n", "description_content_type": "text/markdown", "docs_url": null, "download_url": "", "downloads": { "last_day": -1, "last_month": -1, "last_week": -1 }, "home_page": "https://github.com/jojoduquartier/dsdbmanager", "keywords": "sqlalchemy data-science database-connections pandas", "license": "", "maintainer": "", "maintainer_email": "", "name": "dsdbmanager", "package_url": "https://pypi.org/project/dsdbmanager/", "platform": "", "project_url": "https://pypi.org/project/dsdbmanager/", "project_urls": { "Bug Reports": "https://github.com/jojoduquartier/dsdbmanager/issues", "Homepage": "https://github.com/jojoduquartier/dsdbmanager", "Source": "https://github.com/jojoduquartier/dsdbmanager/" }, "release_url": "https://pypi.org/project/dsdbmanager/1.0.0/", "requires_dist": [ "sqlalchemy", "numpy", "pandas", "toolz", "click", "cryptography" ], "requires_python": ">=3.6, <4", "summary": "Manage your data science databases", "version": "1.0.0" }, "last_serial": 6000458, "releases": { "1.0.0": [ { "comment_text": "", "digests": { "md5": "46c2f0b944838edafaf3225bb19fcd53", "sha256": "e6a6e788771bbfc5d4a7b173ba031a9cbff2ca7886e6de8a07b6f40574dc8a11" }, "downloads": -1, "filename": "dsdbmanager-1.0.0-py3-none-any.whl", "has_sig": false, "md5_digest": "46c2f0b944838edafaf3225bb19fcd53", "packagetype": "bdist_wheel", "python_version": "py3", "requires_python": ">=3.6, <4", "size": 25774, "upload_time": "2019-10-19T17:24:19", "url": "https://files.pythonhosted.org/packages/44/73/e9a1d237c618b407cf0c4240327057780f5f08b9126d48cfdd388554231e/dsdbmanager-1.0.0-py3-none-any.whl" }, { "comment_text": "", "digests": { "md5": "552494017a59fc0247d12fbc731ed9c5", "sha256": "741c8a4560c28dbc13d7fe40b0baabebfbf293a9b8f034b85376d14ed3910df6" }, "downloads": -1, "filename": "dsdbmanager-1.0.0.tar.gz", "has_sig": false, "md5_digest": "552494017a59fc0247d12fbc731ed9c5", "packagetype": "sdist", "python_version": "source", "requires_python": ">=3.6, <4", "size": 21702, "upload_time": "2019-10-19T17:24:21", "url": "https://files.pythonhosted.org/packages/a3/f5/ada519232e6d9c3d4bfc10d02ee2c6801d0a1a9ffdd4291a3bf1a09735aa/dsdbmanager-1.0.0.tar.gz" } ] }, "urls": [ { "comment_text": "", "digests": { "md5": "46c2f0b944838edafaf3225bb19fcd53", "sha256": "e6a6e788771bbfc5d4a7b173ba031a9cbff2ca7886e6de8a07b6f40574dc8a11" }, "downloads": -1, "filename": "dsdbmanager-1.0.0-py3-none-any.whl", "has_sig": false, "md5_digest": "46c2f0b944838edafaf3225bb19fcd53", "packagetype": "bdist_wheel", "python_version": "py3", "requires_python": ">=3.6, <4", "size": 25774, "upload_time": "2019-10-19T17:24:19", "url": "https://files.pythonhosted.org/packages/44/73/e9a1d237c618b407cf0c4240327057780f5f08b9126d48cfdd388554231e/dsdbmanager-1.0.0-py3-none-any.whl" }, { "comment_text": "", "digests": { "md5": "552494017a59fc0247d12fbc731ed9c5", "sha256": "741c8a4560c28dbc13d7fe40b0baabebfbf293a9b8f034b85376d14ed3910df6" }, "downloads": -1, "filename": "dsdbmanager-1.0.0.tar.gz", "has_sig": false, "md5_digest": "552494017a59fc0247d12fbc731ed9c5", "packagetype": "sdist", "python_version": "source", "requires_python": ">=3.6, <4", "size": 21702, "upload_time": "2019-10-19T17:24:21", "url": "https://files.pythonhosted.org/packages/a3/f5/ada519232e6d9c3d4bfc10d02ee2c6801d0a1a9ffdd4291a3bf1a09735aa/dsdbmanager-1.0.0.tar.gz" } ] }