Metadata-Version: 2.1
Name: dbrequests
Version: 1.0.9
Summary: Python package for querying and connecting to databases.
Home-page: https://github.com/INWTlab/dbrequests
Author: Matthaeus Deutsch
Author-email: mdeutsch@outlook.com
License: MIT
Platform: UNKNOWN
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: Natural Language :: English
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.4
Classifier: Programming Language :: Python :: 3.5
Classifier: Programming Language :: Python :: 3.6
Classifier: Programming Language :: Python :: 3.7
Description-Content-Type: text/markdown
Requires-Dist: pandas
Requires-Dist: SQLAlchemy ; python_version >= "3.0"
Provides-Extra: mysql
Requires-Dist: pymysql ; extra == 'mysql'
Provides-Extra: pg
Requires-Dist: psycopg2 ; extra == 'pg'
Provides-Extra: redshift
Requires-Dist: sqlalchemy-redshift ; extra == 'redshift'
Requires-Dist: psycopg2 ; extra == 'redshift'

# dbrequests

[![Build Status](https://travis-ci.org/INWTlab/dbrequests.svg?branch=master)](https://travis-ci.org/INWTlab/dbrequests)

**dbrequests is a python package built for easy use of raw SQL within python and pandas projects.**

It uses ideas from [records](https://github.com/kennethreitz/records/) and is built using [sqlalchemy-engines](https://www.sqlalchemy.org/), but is more heavily integrated with pandas. It aims to reproduce the pilosophy behind the R-package [dbtools](https://github.com/INWT/dbtools/).


_Database support includes RedShift, Postgres, MySQL, SQLite, Oracle, and MS-SQL (drivers not included)._

## Usage

### Send queries and bulk queries

Easy sending of raw sql and output as pandas DataFrames, with creds or the url of the database:

```python
from dbrequests import Database

db = Database(creds=creds)
df = db.send_query("""
    SELECT * FROM test;
    """)
df # table test as pandas DataFrame
```

You can put the sql query in a file and direct `send_query` to the file. The sql-file may be parametrized:

```sql
SELECT {col1}, {col2} FROM test;

```

```python
from dbrequests import Database

db = Database(creds=creds, sql_dir = '/path/to/dir/')
db.send_query('select', col1='id', col2='name')
df # table test, including columns 'id', 'name' as pandas DataFrame
```

You can also pass arguments to pandas `read_sql`-Function:

```python
from dbrequests import Database

db = Database(creds=creds, sql_dir = '/path/to/dir/')
db.send_query('select', col1='id', col2='name', index_col='id')
df # table test, including column 'name' as pandas DataFrame with index 'id'
```

You may also send queries with no table as output to the database via `send_bulk_query`, which exhibits the same behavior as `send_query`:

```python
db.send_bulk_query('drop test from test;')
```

### Send data

Easy sending of pandas Dataframes in multiple modes:

```python
db.send_data(df, 'table', mode='insert')
```

Supported modes are:
  - 'insert': Appending new records. Duplicate primary keys will result in errors (sql insert into).
  - 'truncate': Delete the table and completely rewrite it (sql truncate and insert into).
  - 'replace': Replace records with duplicate primary keys (sql replace into).
  - 'update': Update records with duplicate primary keys (sql insert into duplicate key update).

### Uitilities

- Database.get_table_names will give existing tables in the database
- Parameters such es `chunksize` for `pandas.to_sql` may be given to the wrapper function `send_data` and are handed over to pandas. The same is true for `send_query`.
- For transactions the context manager `transaction` may be of use.

## Installation

The package can be installed via pip:

```
pip install dbrequests
```


## Version 1.0
  - implemented send_data and send_query wrappers for pandas sql-functionality

## Version 1.0.8
  - renamed to dbrequests


