Skip to main content

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, port, user, password, db_name, sslmode='require')

Alternatively, you can use dictionary with parameters.

params = {
    'host': 'localhost',
    'port': '5432',
    'user': 'postgres',
    'password': 'password',
    'db_name': 'test_db'
}

commuter = PgCommuter(**params) 

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 insert_fast() method efficiently copies data from pandas dataframe to database employing PostgreSQL copy command.

commuter.insert_fast('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


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Files for db-commuter, version 0.1.7
Filename, size File type Python version Upload date Hashes
Filename, size db_commuter-0.1.7-py3-none-any.whl (7.0 kB) File type Wheel Python version py3 Upload date Hashes View hashes
Filename, size db-commuter-0.1.7.tar.gz (5.4 kB) File type Source Python version None Upload date Hashes View hashes

Supported by

Elastic Elastic Search Pingdom Pingdom Monitoring Google Google BigQuery Sentry Sentry Error logging AWS AWS Cloud computing DataDog DataDog Monitoring Fastly Fastly CDN SignalFx SignalFx Supporter DigiCert DigiCert EV certificate StatusPage StatusPage Status page