Python package for querying and connecting to databases.
Project description
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 isFalse
. - 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 isFalse
. - Database.get_table_names will give existing tables in the database
- Parameters such es
chunksize
forpandas.to_sql
may be given to the wrapper functionsend_data
and are handed over to pandas. The same is true forsend_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.
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
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Hashes for dbrequests-1.2.0-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 1c7a2bd0a465d1903b62c408fc4b0f730ac75854b3b53aeb8554b76d4748beb0 |
|
MD5 | a7032319e46c2475033cf471dda5ef2a |
|
BLAKE2b-256 | f7259ab045decc3ff3053763ca15a7aeb203915055eaa66f80a549a922c94ab2 |