diff options
Diffstat (limited to 'python-db-utils.spec')
| -rw-r--r-- | python-db-utils.spec | 808 |
1 files changed, 808 insertions, 0 deletions
diff --git a/python-db-utils.spec b/python-db-utils.spec new file mode 100644 index 0000000..50e0a25 --- /dev/null +++ b/python-db-utils.spec @@ -0,0 +1,808 @@ +%global _empty_manifest_terminate_build 0 +Name: python-db-utils +Version: 0.4.6 +Release: 1 +Summary: Helper class to connect to Redshift, Snowflake, DynamoDB and S3 +License: MIT License +URL: https://github.com/ktechboston/db_utils +Source0: https://mirrors.nju.edu.cn/pypi/web/packages/08/54/59af67000f5e00c9e8511afac1ac309e5353b6d68d2413a52f6657ed9f0b/db_utils-0.4.6.tar.gz +BuildArch: noarch + +Requires: python3-boto3 +Requires: python3-psycopg2 +Requires: python3-psycopg2-binary +Requires: python3-pandas +Requires: python3-sqlparse +Requires: python3-awscli +Requires: python3-snowflake-connector-python +Requires: python3-mysql-connector-python +Requires: python3-pyodbc +Requires: python3-jinja2 + +%description +# db_utils library + +## Introduction +db-utils is a Python package that standardizes interactions with various types of databases. db-utils is a collection of modules that lowers the bar to viewing, extracting, and analyzing data from various sources including: + + * Redshift + * Snowflake + * Postgres + * Mysql + * Sqlserver + * sqlite + * s3 + * dynamoDB + + +## Docker +One line command to start a Docker container with db-utils installed. This will run on any system that is running Docker. A jupyter notebook will open up on port 8888, just copy and paste the url from the terminal into your preferred browser. + +```bash +docker run -p 8888:8888 hannaj06/db-utils:latest +``` + +## docker-compose + +Below is an example `docker-compose.yaml` file. With this configuration jupyter notebook changes persist in the location defined by `<local_notebooks>`. Below is a sample of the `.databases.conf` file which will be bindmounted to the docker image. + +`docker-compose.yaml` +```bash +version: "2.1" +services: + dbutils_juypter: + image: hannaj06/db-utils + ports: + - 8888:8888 + volumes: + - ${HOME}/.databases.conf:/root/.databases.conf + - <local_notebooks>:/notebooks + +``` + +`.databases.conf` +```ini +[redshift] +host=<redshift_host> +user=<user> +password=<redshift_password> +port=<port> +database=<db> + +[s3] +aws_access_key_id=<access_id> +aws_secret_access_key=<secret_access> +region=<aws_region> +default_bucket=<default_bucket> +``` + +## Installation on local envoirnment + * sudo apt-get update + +Required system packages: + * sudo apt-get install python3-dev (Ubuntu) + * sudo apt-get apt-get install g++ (Ubuntu) + * sudo apt-get install libpq-dev (Ubuntu) + * sudo apt-get install unixodbc-dev (Ubuntu) + * brew install postgresql (MacOS) + +```bash +pip install db_utils +``` + +### pg_connect class (previously DBUtil) +A database connection class to interact with Postgres or Redshift + +Basic Usage: + * create database configuration file + * example below is called .databases.conf + +``` + [redshift_example] + host=redshift.example.com + user=test_user + password=password + port=5439 + database=test_db + + >>> from db_utils.pg_connect import pg_connect + >>> + >>> db = pg_connect('redshift_example', '.databases.conf') + >>> db.get_arr_from_query('select * from test', pprint=True) +``` + +### snowflake_connect class +A database connection class to interact with snowflake + +Basic Usage: + * create database configuration file + * example below is called .databases.conf + +``` + [snowflake] + account=abc123.us-east-1 + host=abc123.us-east-1.snowflakecomputing.com + user=test_user + password=password + port=443 + database=test_db + aws_access_key_id=<key_id> + aws_secret_access_key=<secret_key> +``` + +### snowflake_s3 class +A child class of snowflake_connect class used to retrieve large datasets in small chunks + +Basic Usage: + * create database configuration file + * example below is called .databases.conf + * note the additional fields required + +``` + [snowflake] + account=abc123.us-east-1 + host=abc123.us-east-1.snowflakecomputing.com + user=test_user + password=password + port=443 + database=test_db + aws_access_key_id=<key_id> + aws_secret_access_key=<secret_key> + default_bucket= +``` + +example) Loading large data set into memory in chunks +``` + >>> from db_utils.snowflake_connect import snowflake_s3 + >>> import os + >>> + >>> file_format = ''' + TYPE = CSV + COMPRESSION = NONE + ''' + >>> + >>> + >>> with snowflake_s3('snowflake', '.databases.conf') as db: + >>> db.cursor('SELECT * FROM example_large_table', file_format=file_format, pprint=True) + >>> + >>> while True: + >>> file = db.fetch(contents=True) + >>> + >>> if file: + >>> for row in file: + >>> print(row) + >>> + >>> else: + >>> break + + +``` + +### sqlite_connect class +A database connection class to interact with SQLite + +``` + + >>> from db_utils.sqlite_connect import sqlite_connect + >>> + >>> db = sqlite_connect('test.db') + >>> db.get_df_from_query('select * from test_table', pprint=True) +``` + +### s3_connect class +Connection library for interacting with S3 + +Basic Usage: + * add s3 section to .databases.conf file (created in previous example) + +``` + [s3] + aws_access_key_id=<key_id> + aws_secret_access_key=<secret_key> + default_bucket=<bucket> + + + >>> from db_utils.s3_connect import s3_connect + >>> + >>> s3 = s3_connect('.databases.conf', 's3') + >>> s3.list_keys(prefix='examples') + +``` + +example) grab file from s3 into memory as stringIO object +``` + >>> from db_utils.s3_connect import s3_connect + >>> + >>> s3 = s3_connect('.databases.conf', 's3') + >>> s3.get_contents('example_file', stringIO=True) + >>> s3.read() + +``` + + +### sql_server connect class + +Requirements: + * sql server drivers - https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017 + +Basic Usage: + * add sql server section to .databases.conf file (created in previous example) + + +``` + [sql_server] + driver=ODBC Driver 17 for SQL Server + server=127.0.0.1 + user=bill + password=gates + database=master + + >>> from db_utils.sql_server_connect import sql_server_connect + >>> db = sql_server_connect('sql_server', 'databases.conf') + >>> + >>> db.get_arr_from_query('''SELECT * FROM SYSOBJECTS''', pprint=True)) +``` + + +### dynamodb_connect class +Connection library for interacting with Dynamodb + + +### timer class +Helper class to time long running processes + +Basic Usage: + +``` +>>> from db_utils.timer import timer +>>> +>>> t = timer() +>>> t.lap('s') +5.469961 +``` + + + + +%package -n python3-db-utils +Summary: Helper class to connect to Redshift, Snowflake, DynamoDB and S3 +Provides: python-db-utils +BuildRequires: python3-devel +BuildRequires: python3-setuptools +BuildRequires: python3-pip +%description -n python3-db-utils +# db_utils library + +## Introduction +db-utils is a Python package that standardizes interactions with various types of databases. db-utils is a collection of modules that lowers the bar to viewing, extracting, and analyzing data from various sources including: + + * Redshift + * Snowflake + * Postgres + * Mysql + * Sqlserver + * sqlite + * s3 + * dynamoDB + + +## Docker +One line command to start a Docker container with db-utils installed. This will run on any system that is running Docker. A jupyter notebook will open up on port 8888, just copy and paste the url from the terminal into your preferred browser. + +```bash +docker run -p 8888:8888 hannaj06/db-utils:latest +``` + +## docker-compose + +Below is an example `docker-compose.yaml` file. With this configuration jupyter notebook changes persist in the location defined by `<local_notebooks>`. Below is a sample of the `.databases.conf` file which will be bindmounted to the docker image. + +`docker-compose.yaml` +```bash +version: "2.1" +services: + dbutils_juypter: + image: hannaj06/db-utils + ports: + - 8888:8888 + volumes: + - ${HOME}/.databases.conf:/root/.databases.conf + - <local_notebooks>:/notebooks + +``` + +`.databases.conf` +```ini +[redshift] +host=<redshift_host> +user=<user> +password=<redshift_password> +port=<port> +database=<db> + +[s3] +aws_access_key_id=<access_id> +aws_secret_access_key=<secret_access> +region=<aws_region> +default_bucket=<default_bucket> +``` + +## Installation on local envoirnment + * sudo apt-get update + +Required system packages: + * sudo apt-get install python3-dev (Ubuntu) + * sudo apt-get apt-get install g++ (Ubuntu) + * sudo apt-get install libpq-dev (Ubuntu) + * sudo apt-get install unixodbc-dev (Ubuntu) + * brew install postgresql (MacOS) + +```bash +pip install db_utils +``` + +### pg_connect class (previously DBUtil) +A database connection class to interact with Postgres or Redshift + +Basic Usage: + * create database configuration file + * example below is called .databases.conf + +``` + [redshift_example] + host=redshift.example.com + user=test_user + password=password + port=5439 + database=test_db + + >>> from db_utils.pg_connect import pg_connect + >>> + >>> db = pg_connect('redshift_example', '.databases.conf') + >>> db.get_arr_from_query('select * from test', pprint=True) +``` + +### snowflake_connect class +A database connection class to interact with snowflake + +Basic Usage: + * create database configuration file + * example below is called .databases.conf + +``` + [snowflake] + account=abc123.us-east-1 + host=abc123.us-east-1.snowflakecomputing.com + user=test_user + password=password + port=443 + database=test_db + aws_access_key_id=<key_id> + aws_secret_access_key=<secret_key> +``` + +### snowflake_s3 class +A child class of snowflake_connect class used to retrieve large datasets in small chunks + +Basic Usage: + * create database configuration file + * example below is called .databases.conf + * note the additional fields required + +``` + [snowflake] + account=abc123.us-east-1 + host=abc123.us-east-1.snowflakecomputing.com + user=test_user + password=password + port=443 + database=test_db + aws_access_key_id=<key_id> + aws_secret_access_key=<secret_key> + default_bucket= +``` + +example) Loading large data set into memory in chunks +``` + >>> from db_utils.snowflake_connect import snowflake_s3 + >>> import os + >>> + >>> file_format = ''' + TYPE = CSV + COMPRESSION = NONE + ''' + >>> + >>> + >>> with snowflake_s3('snowflake', '.databases.conf') as db: + >>> db.cursor('SELECT * FROM example_large_table', file_format=file_format, pprint=True) + >>> + >>> while True: + >>> file = db.fetch(contents=True) + >>> + >>> if file: + >>> for row in file: + >>> print(row) + >>> + >>> else: + >>> break + + +``` + +### sqlite_connect class +A database connection class to interact with SQLite + +``` + + >>> from db_utils.sqlite_connect import sqlite_connect + >>> + >>> db = sqlite_connect('test.db') + >>> db.get_df_from_query('select * from test_table', pprint=True) +``` + +### s3_connect class +Connection library for interacting with S3 + +Basic Usage: + * add s3 section to .databases.conf file (created in previous example) + +``` + [s3] + aws_access_key_id=<key_id> + aws_secret_access_key=<secret_key> + default_bucket=<bucket> + + + >>> from db_utils.s3_connect import s3_connect + >>> + >>> s3 = s3_connect('.databases.conf', 's3') + >>> s3.list_keys(prefix='examples') + +``` + +example) grab file from s3 into memory as stringIO object +``` + >>> from db_utils.s3_connect import s3_connect + >>> + >>> s3 = s3_connect('.databases.conf', 's3') + >>> s3.get_contents('example_file', stringIO=True) + >>> s3.read() + +``` + + +### sql_server connect class + +Requirements: + * sql server drivers - https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017 + +Basic Usage: + * add sql server section to .databases.conf file (created in previous example) + + +``` + [sql_server] + driver=ODBC Driver 17 for SQL Server + server=127.0.0.1 + user=bill + password=gates + database=master + + >>> from db_utils.sql_server_connect import sql_server_connect + >>> db = sql_server_connect('sql_server', 'databases.conf') + >>> + >>> db.get_arr_from_query('''SELECT * FROM SYSOBJECTS''', pprint=True)) +``` + + +### dynamodb_connect class +Connection library for interacting with Dynamodb + + +### timer class +Helper class to time long running processes + +Basic Usage: + +``` +>>> from db_utils.timer import timer +>>> +>>> t = timer() +>>> t.lap('s') +5.469961 +``` + + + + +%package help +Summary: Development documents and examples for db-utils +Provides: python3-db-utils-doc +%description help +# db_utils library + +## Introduction +db-utils is a Python package that standardizes interactions with various types of databases. db-utils is a collection of modules that lowers the bar to viewing, extracting, and analyzing data from various sources including: + + * Redshift + * Snowflake + * Postgres + * Mysql + * Sqlserver + * sqlite + * s3 + * dynamoDB + + +## Docker +One line command to start a Docker container with db-utils installed. This will run on any system that is running Docker. A jupyter notebook will open up on port 8888, just copy and paste the url from the terminal into your preferred browser. + +```bash +docker run -p 8888:8888 hannaj06/db-utils:latest +``` + +## docker-compose + +Below is an example `docker-compose.yaml` file. With this configuration jupyter notebook changes persist in the location defined by `<local_notebooks>`. Below is a sample of the `.databases.conf` file which will be bindmounted to the docker image. + +`docker-compose.yaml` +```bash +version: "2.1" +services: + dbutils_juypter: + image: hannaj06/db-utils + ports: + - 8888:8888 + volumes: + - ${HOME}/.databases.conf:/root/.databases.conf + - <local_notebooks>:/notebooks + +``` + +`.databases.conf` +```ini +[redshift] +host=<redshift_host> +user=<user> +password=<redshift_password> +port=<port> +database=<db> + +[s3] +aws_access_key_id=<access_id> +aws_secret_access_key=<secret_access> +region=<aws_region> +default_bucket=<default_bucket> +``` + +## Installation on local envoirnment + * sudo apt-get update + +Required system packages: + * sudo apt-get install python3-dev (Ubuntu) + * sudo apt-get apt-get install g++ (Ubuntu) + * sudo apt-get install libpq-dev (Ubuntu) + * sudo apt-get install unixodbc-dev (Ubuntu) + * brew install postgresql (MacOS) + +```bash +pip install db_utils +``` + +### pg_connect class (previously DBUtil) +A database connection class to interact with Postgres or Redshift + +Basic Usage: + * create database configuration file + * example below is called .databases.conf + +``` + [redshift_example] + host=redshift.example.com + user=test_user + password=password + port=5439 + database=test_db + + >>> from db_utils.pg_connect import pg_connect + >>> + >>> db = pg_connect('redshift_example', '.databases.conf') + >>> db.get_arr_from_query('select * from test', pprint=True) +``` + +### snowflake_connect class +A database connection class to interact with snowflake + +Basic Usage: + * create database configuration file + * example below is called .databases.conf + +``` + [snowflake] + account=abc123.us-east-1 + host=abc123.us-east-1.snowflakecomputing.com + user=test_user + password=password + port=443 + database=test_db + aws_access_key_id=<key_id> + aws_secret_access_key=<secret_key> +``` + +### snowflake_s3 class +A child class of snowflake_connect class used to retrieve large datasets in small chunks + +Basic Usage: + * create database configuration file + * example below is called .databases.conf + * note the additional fields required + +``` + [snowflake] + account=abc123.us-east-1 + host=abc123.us-east-1.snowflakecomputing.com + user=test_user + password=password + port=443 + database=test_db + aws_access_key_id=<key_id> + aws_secret_access_key=<secret_key> + default_bucket= +``` + +example) Loading large data set into memory in chunks +``` + >>> from db_utils.snowflake_connect import snowflake_s3 + >>> import os + >>> + >>> file_format = ''' + TYPE = CSV + COMPRESSION = NONE + ''' + >>> + >>> + >>> with snowflake_s3('snowflake', '.databases.conf') as db: + >>> db.cursor('SELECT * FROM example_large_table', file_format=file_format, pprint=True) + >>> + >>> while True: + >>> file = db.fetch(contents=True) + >>> + >>> if file: + >>> for row in file: + >>> print(row) + >>> + >>> else: + >>> break + + +``` + +### sqlite_connect class +A database connection class to interact with SQLite + +``` + + >>> from db_utils.sqlite_connect import sqlite_connect + >>> + >>> db = sqlite_connect('test.db') + >>> db.get_df_from_query('select * from test_table', pprint=True) +``` + +### s3_connect class +Connection library for interacting with S3 + +Basic Usage: + * add s3 section to .databases.conf file (created in previous example) + +``` + [s3] + aws_access_key_id=<key_id> + aws_secret_access_key=<secret_key> + default_bucket=<bucket> + + + >>> from db_utils.s3_connect import s3_connect + >>> + >>> s3 = s3_connect('.databases.conf', 's3') + >>> s3.list_keys(prefix='examples') + +``` + +example) grab file from s3 into memory as stringIO object +``` + >>> from db_utils.s3_connect import s3_connect + >>> + >>> s3 = s3_connect('.databases.conf', 's3') + >>> s3.get_contents('example_file', stringIO=True) + >>> s3.read() + +``` + + +### sql_server connect class + +Requirements: + * sql server drivers - https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017 + +Basic Usage: + * add sql server section to .databases.conf file (created in previous example) + + +``` + [sql_server] + driver=ODBC Driver 17 for SQL Server + server=127.0.0.1 + user=bill + password=gates + database=master + + >>> from db_utils.sql_server_connect import sql_server_connect + >>> db = sql_server_connect('sql_server', 'databases.conf') + >>> + >>> db.get_arr_from_query('''SELECT * FROM SYSOBJECTS''', pprint=True)) +``` + + +### dynamodb_connect class +Connection library for interacting with Dynamodb + + +### timer class +Helper class to time long running processes + +Basic Usage: + +``` +>>> from db_utils.timer import timer +>>> +>>> t = timer() +>>> t.lap('s') +5.469961 +``` + + + + +%prep +%autosetup -n db-utils-0.4.6 + +%build +%py3_build + +%install +%py3_install +install -d -m755 %{buildroot}/%{_pkgdocdir} +if [ -d doc ]; then cp -arf doc %{buildroot}/%{_pkgdocdir}; fi +if [ -d docs ]; then cp -arf docs %{buildroot}/%{_pkgdocdir}; fi +if [ -d example ]; then cp -arf example %{buildroot}/%{_pkgdocdir}; fi +if [ -d examples ]; then cp -arf examples %{buildroot}/%{_pkgdocdir}; fi +pushd %{buildroot} +if [ -d usr/lib ]; then + find usr/lib -type f -printf "/%h/%f\n" >> filelist.lst +fi +if [ -d usr/lib64 ]; then + find usr/lib64 -type f -printf "/%h/%f\n" >> filelist.lst +fi +if [ -d usr/bin ]; then + find usr/bin -type f -printf "/%h/%f\n" >> filelist.lst +fi +if [ -d usr/sbin ]; then + find usr/sbin -type f -printf "/%h/%f\n" >> filelist.lst +fi +touch doclist.lst +if [ -d usr/share/man ]; then + find usr/share/man -type f -printf "/%h/%f.gz\n" >> doclist.lst +fi +popd +mv %{buildroot}/filelist.lst . +mv %{buildroot}/doclist.lst . + +%files -n python3-db-utils -f filelist.lst +%dir %{python3_sitelib}/* + +%files help -f doclist.lst +%{_docdir}/* + +%changelog +* Thu May 18 2023 Python_Bot <Python_Bot@openeuler.org> - 0.4.6-1 +- Package Spec generated |