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.
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
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.3.9-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 56e9ddea4d8e4a6c41802b678b9a758ee7a11318dcdff4f1032f85660fb4c2bf |
|
MD5 | b96705d3333826e506f5c4fccef1eb83 |
|
BLAKE2b-256 | 041d11dddaeeb922b2eb0899b0ff2ed89ce2bd57f39caffa73db6f0226e4e5d2 |