Database communication manager
Project description
Database Communication Manager
Collection of wrappers for communication with database. Supports following databases:
- SQLite
- PostgreSQL
Installation
To install the package, simply use pip.
$ pip install db_commuter
SQLite
To create a new commuter instance, you need to set path to SQLite database file via constructor.
from db_commuter.commuters import SQLiteCommuter
commuter = SQLiteCommuter(path2db)
Select data from table and return pandas dataframe.
age = 55
salary = 1000
data = commuter.select('select * from people where age > %s and salary > %s' % (age, salary))
Insert from pandas dataframe to database table.
commuter.insert('people', data)
Execute an SQL statement.
who = 'Yeltsin'
age = 72
commuter.execute('insert into people values (?, ?)', vars=(who, age))
To execute multiple SQL statements with one call, use executescript()
.
commuter.execute_script(path2script)
PostgreSQL
Setting the Commuter
To initialize a new commuter with PostgreSQL database, you need to set the basic connection parameters, which are
host
, port
, user
, password
, db_name
. Any other connection parameter can be passed as a keyword.
The list of the supported parameters can be seen here.
from db_commuter.commuters import PgCommuter
commuter = PgCommuter(
host='localhost',
port='5432',
user='postgres',
password='password',
db_name='test_db',
sslmode='require')
Basic Usage
Basic operations are provided with select()
, insert()
and execute()
methods.
data = commuter.select('select * from people where age > %s and salary > %s' % (55, 1000))
commuter.insert('people', data)
commuter.execute('insert into people values (%s, %s)', vars=('Yeltsin', 72))
To execute multiple SQL statements with one call, use executescript().
commuter.execute_script(path2script)
Fast Insert
In contrast to insert
method which, in turn, uses pandas to_sql
machinery, the copy_from
method
efficiently copies data from pandas dataframe to database employing PostgreSQL copy_from
command.
commuter.copy_from('people', data)
As compared to conventional insert
, this method works much more effective on the large dataframes.
Setting Schema in Constructor
If you operate only on tables within the specific schema, it could make sense to specify the name of database schema when you create the commuter instance.
from db_commuter.commuters import PgCommuter
commuter = PgCommuter(host, port, user, password, db_name, schema='model')
License
Package is released under MIT License.
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 db_commuter-0.1.9-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | ba96e9d3e0ee4386f8ae6deb8c10500c53e926ea2f9893139d06904355f12c61 |
|
MD5 | b50700f25b922a9dadaf0a8b248d48b4 |
|
BLAKE2b-256 | 5ce555a9b81b35051328e72e6c6ddbc8a7eb3e4bdb1bea762732d4d4e5d73033 |