dbrequests

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

It uses ideas from records and is built using sqlalchemy-engines, but is more heavily integrated with pandas. It aims to reproduce the pilosophy behind the R-package 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 credentials given as dictionary (for an example see creds_example.json) or the url of the database:

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:

SELECT {col1}, {col2} FROM test;

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:

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:

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

Send data

Easy sending of pandas Dataframes in multiple modes:

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).

Utilities

• Comments can be automatically removed from SQL code by adding remove_comments=True either to the Database defintion or send_query. This is especially useful if outcommenting code blocks including parametized variables and thus {}. The default of this behavior is False.
• Percentage signs can be transfered to a Python readable way by adding escape_percentage=True either to the Database definition or send_query. This means percentage signs dont have to be escaped manually when sending them via Python. The default is False.
• 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

Extensibility

dbrequests is designed to easily accommodate different needs in the form of drivers / dialects. For examples of how to extend the capabilities of the Connection class, see connection_subclass.py under examples.

Existing extensions

• MySQL / MariaDB: use

from dbrequests.mysql import Database

for using the MySQL specific extension as Database connector. The extension provides MySQL specific functionalities, like using load data infile for writing data to tables.

Version 1.0

• implemented send_data and send_query wrappers for pandas sql-functionality

Version 1.0.8

• renamed to dbrequests

Version 1.1

• added handling of percentage signs
• added possibility of automated comment removal
• ensured the compatibility of raw SQL code with code sent via dbrequests

Version 1.2

• changed static loading of the connection class to a flexible model, allowing to override connections for different SQL drivers

Version 1.3

• added mysql backend for specific mysql and mariadb support
• added possibility for send_data to use infile for mysql databases

Version 1.3.8

• dbrequests.mysql module:
  • see #11 for motivation on moving dialect specific implementation into extras
  • see #15 for mysql specific send_data via load data local infile

Version 1.3.9

• dbrequests.mysql:
  • see #20 for memory efficient send_data using datatable

Version 1.3.10

• dbrequests.mysql:
  • see #24 for bugfix while writing to csv

Version 1.3.11

• dbrequests.mysql:
  • see #28 for bugfix when sending escape sequences

Version 1.3.12

• dbrequests.mysql:
  • see #22 for performance improvements for send_query when reading large datasets
  • support for pymysql and mysqldb

Version 1.3.13

• dbrequests.mysql
  • see #30: fixes handling of None/NULL values in columns

Version 1.3.14

• dbrequests.mysql:
  • see #32 for bugfix in send_query for empty result sets

Version 1.3.15

• dbrequests:
  • see #27 for bugfix in Database class when specifiying a port in a credentials object.
  • the argument 'creds' in the init method of a database class is now deprecated
  • the argument 'db_url' can now handle str and dict type; str is a sqlalchemy url; a dict a credentials object
  • credential objects can now have additional fields which will be used as elements in connect_args for sqlalchemies create_engine: see #12
• dbrequests.mysql
  • see #36 for bugfix while sending an empty frame

Version 1.3.16

• dbrequests.mysql
  • see #35: New send_data modes: update_diffs, insert_diffs, replace_diffs

Version 1.3.17

• dbrequests.mysql
  • new function in database: send_delete for deleting rows in a database.
  • send_data in mode 'update' now allows to send only partial subset of columns.

Version 1.4.0

• dbrequests.mysql
  • bugfix in send_data with mode [update|insert|replace]_diffs: same as #36

Version 1.4.1

• dbrequests.mysql
  • bugfix for upstream bug in mariadb: sending diffs needs persisten tables instead of temporary.

Version 1.4.2

• dbrequests.mysql
  • creating temorary removing partitions and system versioned from temporary tables.
  • new mode for send data: sync_diffs: update differences and delete deprecated rows.
  • new mode for delete data: in_delete_col: mark rows to delete, then delete.
  • bugfix for temporary tables: now properly removes tables.

Version 1.4.3-5

• dbrequests.mysql
  • More stable and reliable Version of sync_diffs mode for sending data. Respects scarce resources on the mysql server.

Version 1.4.6

• dbrequests.mysql
  • HOTFIX #51: Bug with latest datatable version

Version 1.4.7

• dbrequests.mysql
  • bugfix of #52

Version 1.4.8

• dbrequests.mysql
  • fix for creating temporary files on Windows.