# database-factory
[](https://opensource.org/licenses/MIT)
[](https://pypi.org/project/database-factory)
[](https://circleci.com/gh/shrivastava-v-ankit/database-factory)
Database factory is used to manage/create database connection with execute queries using the connection.
The concept of having single source to connect various databases and perform database operations.
User need not to worry on the crafting the connection string and to identify the methods for the database operations.
Database factory supports DML / DDL executions and have support of Pandas DataFrame to create or replace existing tables.
Database factory is wrapper on sqlalchemy for crafting the connection and supports below databases:
```bash
* Sqlite3
* PostgreSQl
* BigQuery
* Snowflake
* MariaDB
* MySQL
```
Database factory can be enhanced for all the sqlalchemy supported database.
## Getting Started
```bash
pip install database-factory
```
Note: Default installation for database factory is to support Sqlite3. For other database/cloud support it can be installed with compinations of extra libraries
### Sqite3 with AWS cloud support
```bash
pip install database-factory["aws"]
```
### Snowflake with AWS cloud support
```bash
pip install database-factory["snowflake,aws"]
```
### Following options are supported
* Secret manager cloud support
* aws
* gcp
* Databases
* snowflake
* postgres
* mysql
* all: Will install with libraries of all supported cloud and supported databases.
### Using database-factory
-----
```python
from database_factory.manager import DatabaseManager
import tempfile
temp_dir = tempfile.gettempdir()
db = DatabaseManager(engine_type="sqlite", database="test_db", sqlite_db_path=temp_dir)
db.create_session()
db.execute_sql(sql="create table test (id int PRIMARY KEY)")
db.execute_sql(sql="insert into test values (1)")
db.execute_sql(sql="insert into test values (2)")
rows = db.execute_sql(sql="select * from test")
if rows:
print(rows)
df = db.get_df(sql="select * from test")
print(df)
db.execute_df(panda_df=df, table_name=copy_test, exist_action="replace")
# db.execute_df(panda_df=df, table_name=copy_test, exist_action="replace", chunk_size=100)
db.execute_sql(sql="insert into copy_test values (3)")
rows_copy = db.execute_sql(sql="select * from copy_test")
if rows_copy:
print(rows_copy)
```
## Appendix
### Supported database type:
----
```
* sqlite `default`
* postgres
* mysql
* mariadb
* snowflake
```
### Connection parameters for sqlite:
-----
```python
* engine_type: sqlite
* database: <name of database>
* sqlite_db_path: <path where database will be created>
```
### Connection parameters for postgres:
-----
```python
* engine_type: postgres
* database: <name of database>
* username: <postgres user>
* password: <user password>
* host: <host of postgres service>
* port: <port of postgres service>
```
### Connection parameters for mysql:
-----
```python
* engine_type: mysql
* database: <name of database>
* username: <mysql user>
* password: <user password>
* host: <host of mysql service>
* port: <port of mysql servic\>
```
### Connection parameters for mariadb:
-----
```python
* engine_type: mariadb
* database: <name of database>
* username: <mariadb user>
* password: <user password>
* host: <host of mariadb service>
* port: <port of mariadb service>
```
### Connection parameters for snowflake:
-----
```python
* engine_type: snowflake
* database: <name of database>
* username: <snowflake user>
* password: <user password>
* schema: <schema name>
* snowflake_role: <snowflake role>
* snowflake_warehouse: <snowflake warehouse>
* snowflake_account: <snowflake account>
```
### Connection parameters for bigquery:
-----
```python
* engine_type: bigquery
* database: <name of database>
```
### Getting connection properties from AWS / GCP Secret Manager Service:
-----
Note:
* GCP:
* On Cloud Server:
* Set server to execute the all cloud api services
* Attach following permissions
* Project Viewer
* Secret Manager Secret Accessor
* On Premises:
* Attach following permissions to user service account and download service account file for authentication:
* Project Viewer
* Secret Manager Secret Accessor
* Set environment variable "GOOGLE_APPLICATION_CREDENTIALS" pointing to service account file.
* AWS:
* On Cloud Server:
* Set execution profile with "secretsmanager:GetSecretValue" policy
* On Premises:
* AWS should be configured
* User should have permissions of "secretsmanager:GetSecretValue" policy.
```python
* engine_type: bigquery
* database: <name of database>
* secret_id: <Secret name of AWS / GCP Secret Manager Service>
* secrete_manager_cloud: <aws or gcp as per cloud>
* aws_region: <aws region: default=> us-east-1>
```
### Development Setup
#### Using virtualenv
```bash
python3 -m venv venv
source env/bin/activate
pip install .
```
### Contributing
1. Fork repo- https://github.com/shrivastava-v-ankit/database-factory.git
2. Create your feature branch - `git checkout -b feature/name`
3. Install Python packages
* sqlalchemy==1.4.47
* pandas==1.5.3
* GitPython
* coverage==7.2.3
* exceptiongroup==1.1.1
* iniconfig==2.0.0
* pluggy==1.0.0
* pytest==7.3.0
* pytest-cov==4.0.0
* tomli==2.0.1
4. Run Python test (pytest)
* pytest -v --cov --cov-report html --cov-report xml --junitxml=test-results/database_factory_test/results.xml
5. Add Python test (pytest) and covrage report for new/changed feature.
4. Commit your changes - `git commit -am "Added name"`
5. Push to the branch - `git push origin feature/name`
6. Create a new pull request
Raw data
{
"_id": null,
"home_page": "https://github.com/shrivastava-v-ankit/database-factory",
"name": "database-factory",
"maintainer": null,
"docs_url": null,
"requires_python": "<3.10,>=3.8,>=3.9",
"maintainer_email": null,
"keywords": "python, os independent, database, sqlalchemy, sqlite3, sqlite, postgres, mysql, maridb, snowflake, bigquery, secret manager",
"author": "Ankit Shrivastava",
"author_email": null,
"download_url": "https://files.pythonhosted.org/packages/33/84/ef56e8e49e4cbc631bc414eb497bd5f913c9eb89471ef8b229767f1d7cc3/database-factory-1.1.0.tar.gz",
"platform": "any",
"description": "# database-factory\n\n[](https://opensource.org/licenses/MIT)\n[](https://pypi.org/project/database-factory)\n[](https://circleci.com/gh/shrivastava-v-ankit/database-factory)\n\n\n\nDatabase factory is used to manage/create database connection with execute queries using the connection.\nThe concept of having single source to connect various databases and perform database operations.\n\nUser need not to worry on the crafting the connection string and to identify the methods for the database operations.\nDatabase factory supports DML / DDL executions and have support of Pandas DataFrame to create or replace existing tables.\n\nDatabase factory is wrapper on sqlalchemy for crafting the connection and supports below databases:\n\n```bash\n* Sqlite3\n* PostgreSQl\n* BigQuery\n* Snowflake\n* MariaDB\n* MySQL\n```\nDatabase factory can be enhanced for all the sqlalchemy supported database.\n\n## Getting Started\n\n```bash\npip install database-factory\n```\nNote: Default installation for database factory is to support Sqlite3. For other database/cloud support it can be installed with compinations of extra libraries\n\n### Sqite3 with AWS cloud support\n```bash\npip install database-factory[\"aws\"]\n```\n\n### Snowflake with AWS cloud support\n```bash\npip install database-factory[\"snowflake,aws\"]\n```\n\n### Following options are supported\n * Secret manager cloud support\n * aws\n * gcp\n * Databases\n * snowflake\n * postgres\n * mysql\n * all: Will install with libraries of all supported cloud and supported databases.\n\n### Using database-factory\n-----\n```python\nfrom database_factory.manager import DatabaseManager\nimport tempfile\ntemp_dir = tempfile.gettempdir()\ndb = DatabaseManager(engine_type=\"sqlite\", database=\"test_db\", sqlite_db_path=temp_dir)\ndb.create_session()\n\ndb.execute_sql(sql=\"create table test (id int PRIMARY KEY)\")\ndb.execute_sql(sql=\"insert into test values (1)\")\ndb.execute_sql(sql=\"insert into test values (2)\")\n\nrows = db.execute_sql(sql=\"select * from test\")\nif rows:\n print(rows)\n\n\ndf = db.get_df(sql=\"select * from test\")\nprint(df)\n\ndb.execute_df(panda_df=df, table_name=copy_test, exist_action=\"replace\")\n# db.execute_df(panda_df=df, table_name=copy_test, exist_action=\"replace\", chunk_size=100)\ndb.execute_sql(sql=\"insert into copy_test values (3)\")\nrows_copy = db.execute_sql(sql=\"select * from copy_test\")\nif rows_copy:\n print(rows_copy)\n```\n\n## Appendix\n### Supported database type:\n----\n```\n* sqlite `default`\n* postgres\n* mysql\n* mariadb\n* snowflake\n```\n\n### Connection parameters for sqlite:\n-----\n```python\n* engine_type: sqlite\n* database: <name of database>\n* sqlite_db_path: <path where database will be created>\n```\n\n### Connection parameters for postgres:\n-----\n```python\n* engine_type: postgres\n* database: <name of database>\n* username: <postgres user>\n* password: <user password>\n* host: <host of postgres service>\n* port: <port of postgres service>\n```\n\n### Connection parameters for mysql:\n-----\n```python\n* engine_type: mysql\n* database: <name of database>\n* username: <mysql user>\n* password: <user password>\n* host: <host of mysql service>\n* port: <port of mysql servic\\>\n```\n\n### Connection parameters for mariadb:\n-----\n```python\n* engine_type: mariadb\n* database: <name of database>\n* username: <mariadb user>\n* password: <user password>\n* host: <host of mariadb service>\n* port: <port of mariadb service>\n```\n\n### Connection parameters for snowflake:\n-----\n```python\n* engine_type: snowflake\n* database: <name of database>\n* username: <snowflake user>\n* password: <user password>\n* schema: <schema name>\n* snowflake_role: <snowflake role>\n* snowflake_warehouse: <snowflake warehouse>\n* snowflake_account: <snowflake account>\n```\n\n### Connection parameters for bigquery:\n-----\n```python\n* engine_type: bigquery\n* database: <name of database>\n```\n\n### Getting connection properties from AWS / GCP Secret Manager Service:\n-----\nNote:\n* GCP: \n * On Cloud Server:\n * Set server to execute the all cloud api services\n * Attach following permissions\n * Project Viewer\n * Secret Manager Secret Accessor\n * On Premises:\n * Attach following permissions to user service account and download service account file for authentication:\n * Project Viewer\n * Secret Manager Secret Accessor\n * Set environment variable \"GOOGLE_APPLICATION_CREDENTIALS\" pointing to service account file.\n* AWS:\n * On Cloud Server:\n * Set execution profile with \"secretsmanager:GetSecretValue\" policy\n * On Premises:\n * AWS should be configured\n * User should have permissions of \"secretsmanager:GetSecretValue\" policy.\n\n```python\n* engine_type: bigquery\n* database: <name of database>\n* secret_id: <Secret name of AWS / GCP Secret Manager Service>\n* secrete_manager_cloud: <aws or gcp as per cloud>\n* aws_region: <aws region: default=> us-east-1>\n```\n\n\n### Development Setup\n\n#### Using virtualenv\n\n```bash\npython3 -m venv venv\nsource env/bin/activate\npip install .\n```\n\n### Contributing\n\n1. Fork repo- https://github.com/shrivastava-v-ankit/database-factory.git\n2. Create your feature branch - `git checkout -b feature/name`\n3. Install Python packages\n * sqlalchemy==1.4.47\n * pandas==1.5.3\n * GitPython\n * coverage==7.2.3\n * exceptiongroup==1.1.1\n * iniconfig==2.0.0\n * pluggy==1.0.0\n * pytest==7.3.0\n * pytest-cov==4.0.0\n * tomli==2.0.1\n4. Run Python test (pytest)\n * pytest -v --cov --cov-report html --cov-report xml --junitxml=test-results/database_factory_test/results.xml\n5. Add Python test (pytest) and covrage report for new/changed feature.\n4. Commit your changes - `git commit -am \"Added name\"`\n5. Push to the branch - `git push origin feature/name`\n6. Create a new pull request\n\n\n\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "Database Factory;",
"version": "1.1.0",
"project_urls": {
"Homepage": "https://github.com/shrivastava-v-ankit/database-factory",
"Source": "https://github.com/shrivastava-v-ankit/database-factory/",
"Tracker": "https://github.com/shrivastava-v-ankit/database-factory/issues"
},
"split_keywords": [
"python",
" os independent",
" database",
" sqlalchemy",
" sqlite3",
" sqlite",
" postgres",
" mysql",
" maridb",
" snowflake",
" bigquery",
" secret manager"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "00b82978d27ef55a76ca397e3ec45907bc485626ad40340c6b23bbb6d44fbd37",
"md5": "408bded8884e8d93db035f522ccf28bd",
"sha256": "7b5ab899f1d6ec4afb0e6ed132716ddb394661451ff4328c8546f2035e5dfe06"
},
"downloads": -1,
"filename": "database_factory-1.1.0-py3-none-any.whl",
"has_sig": false,
"md5_digest": "408bded8884e8d93db035f522ccf28bd",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": "<3.10,>=3.8,>=3.9",
"size": 20032,
"upload_time": "2024-05-03T13:05:29",
"upload_time_iso_8601": "2024-05-03T13:05:29.459598Z",
"url": "https://files.pythonhosted.org/packages/00/b8/2978d27ef55a76ca397e3ec45907bc485626ad40340c6b23bbb6d44fbd37/database_factory-1.1.0-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "3384ef56e8e49e4cbc631bc414eb497bd5f913c9eb89471ef8b229767f1d7cc3",
"md5": "4bdabb47765ced3fbadb660a2a42f397",
"sha256": "821149b6ee2539ea74de41c4bf4f2e843fb9d0166fe131819f84456f4bd5de65"
},
"downloads": -1,
"filename": "database-factory-1.1.0.tar.gz",
"has_sig": false,
"md5_digest": "4bdabb47765ced3fbadb660a2a42f397",
"packagetype": "sdist",
"python_version": "source",
"requires_python": "<3.10,>=3.8,>=3.9",
"size": 17922,
"upload_time": "2024-05-03T13:05:31",
"upload_time_iso_8601": "2024-05-03T13:05:31.587453Z",
"url": "https://files.pythonhosted.org/packages/33/84/ef56e8e49e4cbc631bc414eb497bd5f913c9eb89471ef8b229767f1d7cc3/database-factory-1.1.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-05-03 13:05:31",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "shrivastava-v-ankit",
"github_project": "database-factory",
"travis_ci": false,
"coveralls": true,
"github_actions": false,
"circle": true,
"lcname": "database-factory"
}
JSON
