%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 ``. 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 - :/notebooks ``` `.databases.conf` ```ini [redshift] host= user= password= port= database= [s3] aws_access_key_id= aws_secret_access_key= region= 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= aws_secret_access_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= aws_secret_access_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= aws_secret_access_key= default_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 ``. 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 - :/notebooks ``` `.databases.conf` ```ini [redshift] host= user= password= port= database= [s3] aws_access_key_id= aws_secret_access_key= region= 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= aws_secret_access_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= aws_secret_access_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= aws_secret_access_key= default_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 ``. 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 - :/notebooks ``` `.databases.conf` ```ini [redshift] host= user= password= port= database= [s3] aws_access_key_id= aws_secret_access_key= region= 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= aws_secret_access_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= aws_secret_access_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= aws_secret_access_key= default_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 * Tue May 30 2023 Python_Bot - 0.4.6-1 - Package Spec generated