# sql_pylib
<!-- #region -->
## Install docquity_pylib package
`pip install sql_pylib`
### Create a text file containing credentials for RedShift
* Create a `creds.txt` file
* Copy paste your credentials in this format:
```
NAME_DB=sample-redshift
HOST_DB=sample-redshift-prod.....ap-southeast-1.redshift.amazonaws.com
PORT_DB=5XXX
USER_DB=XXXX
PASSWORD_DB=XXXX
```
* Note that you must copy the credentials as-is. Do not add quotation marks
## Compatibility
Right now, this works on Redshift Postgresql, and may work on MySQL (not tested)
## Sample usage
More examples how to use can be found in [Sample Usage](https://github.com/PrinceJavier/sql_pylib/blob/main/docs/01_sql_builder_functions.ipynb)
### Import and define SQLBuilder
```python
from sql_pylib.sql_builder import SQLBuilder
DOTENV_FILE = "./creds.txt"
sql = SQLBuilder(credentials=DOTENV_FILE, sql_type='postgresql') # or sql_type='mysql'
```
### Show table in database
```python
q = sql.get_table_names(schema="schema_name")
df = sql.download(q)
```
### Show columns in a table
```python
q = sql.get_column_names(table_name="table_name", schema="schema_name")
df = sql.download(q)
```
### Run any Query/Instruction on Redshift Cluster (without needing to download)
```python
q = "SELECT 123"
sql.run_query(q)
```
#### Download Query as Pandas DataFrame
```python
q = """SELECT 123 AS test_col """
df = sql.download_query(q)
```
### Quickly download from a table (automatically gets all columns)
#### Get all columns if columns not specified
```python
q = sql.make_query(schema='schema_name',
table_name='table_name',
filters="id BETWEEN 0 AND 5000",
limit=5)
df = sql.download(q.query)
```
#### Select only certain columns
```python
q = sql.make_query(schema='schema_name',
table_name='table_name',
cols=['id AS user_id', 'user_name'])
q = q.limit(5) # limit can also be defined here
df = sql.download(q.query)
```
### Building queries using SQL Builder
##### Query with conditions
```python
cols = "col1, col2, col1 + col2 AS col3"
cols = ['col1', 'col2', 'col1 + col2 AS col3']
table1 = "user_table"
table2 = "item_table"
conditions = "col1 > col2 AND col1 > 0"
q = (SQLBuilder()
.select(cols)
.from_table(table1)
.where(conditions))
# print corresponding SQL query
print(q.query)
```
<!-- #endregion -->
Raw data
{
"_id": null,
"home_page": "https://github.com/PrinceJavier/sql_pylib",
"name": "sql-pylib",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3.6",
"maintainer_email": "",
"keywords": "sql python",
"author": "Prince Javier",
"author_email": "othepjavier@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/58/16/8161673d97a2c1af103361eba8c8e4a6c01990c7c79a6b0b18c5d66329cb/sql-pylib-0.14.tar.gz",
"platform": null,
"description": "# sql_pylib\n\n<!-- #region -->\n\n## Install docquity_pylib package\n\n`pip install sql_pylib`\n\n### Create a text file containing credentials for RedShift\n* Create a `creds.txt` file\n* Copy paste your credentials in this format:\n\n```\nNAME_DB=sample-redshift\nHOST_DB=sample-redshift-prod.....ap-southeast-1.redshift.amazonaws.com\nPORT_DB=5XXX\nUSER_DB=XXXX\nPASSWORD_DB=XXXX\n```\n\n* Note that you must copy the credentials as-is. Do not add quotation marks\n\n## Compatibility\nRight now, this works on Redshift Postgresql, and may work on MySQL (not tested)\n\n## Sample usage\nMore examples how to use can be found in [Sample Usage](https://github.com/PrinceJavier/sql_pylib/blob/main/docs/01_sql_builder_functions.ipynb)\n\n### Import and define SQLBuilder\n```python\nfrom sql_pylib.sql_builder import SQLBuilder \nDOTENV_FILE = \"./creds.txt\"\nsql = SQLBuilder(credentials=DOTENV_FILE, sql_type='postgresql') # or sql_type='mysql'\n```\n\n### Show table in database\n```python\nq = sql.get_table_names(schema=\"schema_name\")\ndf = sql.download(q)\n```\n\n### Show columns in a table\n```python\nq = sql.get_column_names(table_name=\"table_name\", schema=\"schema_name\")\ndf = sql.download(q)\n```\n\n### Run any Query/Instruction on Redshift Cluster (without needing to download)\n```python\nq = \"SELECT 123\"\nsql.run_query(q)\n```\n\n#### Download Query as Pandas DataFrame\n```python\nq = \"\"\"SELECT 123 AS test_col \"\"\"\ndf = sql.download_query(q)\n```\n\n### Quickly download from a table (automatically gets all columns)\n#### Get all columns if columns not specified\n```python\nq = sql.make_query(schema='schema_name',\n table_name='table_name', \n filters=\"id BETWEEN 0 AND 5000\",\n limit=5)\ndf = sql.download(q.query)\n```\n\n#### Select only certain columns\n```python\nq = sql.make_query(schema='schema_name',\n table_name='table_name',\n cols=['id AS user_id', 'user_name'])\nq = q.limit(5) # limit can also be defined here\ndf = sql.download(q.query)\n```\n\n### Building queries using SQL Builder\n##### Query with conditions\n```python\ncols = \"col1, col2, col1 + col2 AS col3\"\ncols = ['col1', 'col2', 'col1 + col2 AS col3']\n\ntable1 = \"user_table\"\ntable2 = \"item_table\"\nconditions = \"col1 > col2 AND col1 > 0\"\n\nq = (SQLBuilder()\n .select(cols)\n .from_table(table1)\n .where(conditions))\n\n# print corresponding SQL query\nprint(q.query)\n```\n<!-- #endregion -->\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "Custom Python library for standard SQL tasks",
"version": "0.14",
"project_urls": {
"Homepage": "https://github.com/PrinceJavier/sql_pylib"
},
"split_keywords": [
"sql",
"python"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "58168161673d97a2c1af103361eba8c8e4a6c01990c7c79a6b0b18c5d66329cb",
"md5": "efd108ddb8da2d0e4c0d4e8e79d7b2dd",
"sha256": "ec524c9c4c5db112298e9587ae8c3927add28541d984fc2e03ac20eeb8e782c4"
},
"downloads": -1,
"filename": "sql-pylib-0.14.tar.gz",
"has_sig": false,
"md5_digest": "efd108ddb8da2d0e4c0d4e8e79d7b2dd",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.6",
"size": 10714,
"upload_time": "2023-05-18T09:17:22",
"upload_time_iso_8601": "2023-05-18T09:17:22.152430Z",
"url": "https://files.pythonhosted.org/packages/58/16/8161673d97a2c1af103361eba8c8e4a6c01990c7c79a6b0b18c5d66329cb/sql-pylib-0.14.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-05-18 09:17:22",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "PrinceJavier",
"github_project": "sql_pylib",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"lcname": "sql-pylib"
}
JSON
