Skip to main content

A generic database interface.

Project description

haip.config

License Build Status

haip.database is a minimalistic async database interface for Python 3

Features

  • minimalistic: query and do
  • db by name: reference your database (sessions) per config-name
  • sql templates: SQL seperated from code. placeholders are escaped automatically.
  • db pools: db connection pooling
  • dict short notation: row.fieldname == row['fieldname']
  • db supported: mysql, oracle, mssql, (ongoing)

Getting Started

Installing

pip install haip-database

or from source:

git clone https://github.com/haipdev/database.git

Usage

Config-files

databases:
    mysql_test_db:
        type: mysql
        host: host
        port: port
        database: database
        username: username
        password: password
        max_connections: 3
        max_idle_connections: 3

    oracle_test_db:
        type: oracle
        host: host
        port: port
        username: username
        password: password
        service_name: service_name

    mssql_test_db:
        type: mssql
        driver: driver
        host: host
        port: port
        database: TEST
        username: username
        password: password

The database is identified by the section-name (e.g. 'mysql_test_db').

Common options
  • type: mysql | oracle
  • host: hostname/IP of the database server - default='127.0.0.1'
  • port: port the database server is listening at - default depends on the type (mysql=3306, oracle=1521)
  • username: username for login
  • password: password for login
  • autocommit: true | false - default=true
  • max_connections: max number of open connections for this database (otherwise you will get DatabasePoolExhaustedExcpetions)
  • max_idle_connections: max number of open idle connections
Mysql options
  • database: name of the database on the database-server

Prerequisite: python module "mysql_connector"

Oracle options
  • service_name: service-name (higher priority then SID)
  • sid: sid

Prerequisite: python module "cx_Oracle"

MSSQL options
  • driver: driver definition string e.g. ""{SQL Server}"

Prerequisite: python module "pyodbc"

Example

/path-to-my-config-dir/databases.yml

databases:
    testdb:
        type: mysql
        username: testuser
        host: 127.0.0.2

(optionally you can place e.g. the passwords in seperate files):

/path-to-my-config-dir/dev/databases.yml

databases:
    testdb:
        password: testpassword

Functions

Query

async def query(db_name, query_template, *values, **args)

  • db_name: the name of the database as defined in the configuration files (main section "databases" - in the example above e.g. "testdb")
  • query_template: the filename of the template-file containing the SQL query. This file must have the suffix ".sql".
  • *values: values for query placeholders
  • **args: the template-vars for the query_template jinja template.

This function returns an array of arrays (array of rows).

Query example

/path-to-my-config-dir/queries/firstname.sql

SELECT firstname, lastname
    FROM users
    WHERE firstname = '{{ firstname }}
import haip.config as config
import haip.database as database

config.load('/path-to-my-config-dir', 'dev')
rows = await database.query('testdb', 'queries/firstname.sql', firstname='Reinhard')

for row in rows:
    firstname = row[0]
    lastname = row[1]

await database.shutdown()
shortcuts

async def query_assoc(db_name, query_template, **args)

Like "query" but returns an array of dicts. e.g. rows[0]['firstname'] or rows[0].firstname

async def query_first(db_name, query_template, **args)

Like "query" but returns only the first row as dict. e.g. row['firstname'] or row.firstname. If no rows found None will be returned.

Insert/Updates

async def do(db_name, query_template, **args)

Arguments like "query". This function returns the number of rows effected by this statement.

Do example

/path-to-my-config-dir/queries/update.sql

UPDATE users
    SET firstname='Test'
WHERE lastname = '{{ lastname }}
import haip.config as config
import haip.database as database

config.load('/path-to-my-config-dir', 'dev')
changes = await database.do('testdb', 'queries/update.sql', lastname='Hainz')

print(f'effected rows: {changes}')
await database.shutdown()

Procedures

async def call(db_name, procedure)

  • db_name: as above
  • procedure: the name of the procedure to be called

Pool shutdown

async def shutdown()

Close all open connections in the connection pool normally used before closing your application. If you do not close the open connections in the connection pool you will see some warnings on your db servers. So calling this function is not realy necessary but beautifies your code.

Running the tests

Tests are written using pytest and located in the "tests" directory.

pytest tests

Contributing

Feel free to use and enhance this project. Pull requests are welcome.

Authors

  • Reinhard Hainz - Initial work - haipdev

License

This project is licensed under the MIT License - see the LICENSE file for details

Dependencies

Project details


Download files

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

Source Distribution

haip_database-0.1.4.tar.gz (5.6 kB view details)

Uploaded Source

Built Distribution

haip_database-0.1.4-py3-none-any.whl (8.8 kB view details)

Uploaded Python 3

File details

Details for the file haip_database-0.1.4.tar.gz.

File metadata

  • Download URL: haip_database-0.1.4.tar.gz
  • Upload date:
  • Size: 5.6 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/1.12.1 pkginfo/1.5.0.1 requests/2.21.0 setuptools/40.7.1 requests-toolbelt/0.9.1 tqdm/4.30.0 CPython/3.7.2

File hashes

Hashes for haip_database-0.1.4.tar.gz
Algorithm Hash digest
SHA256 349427f31bd0f921242da867a26b41e3c90dbe9c538799611ba4467556d46170
MD5 c501fa6c28d8f50c83d59344461d6734
BLAKE2b-256 786852fb50ad5d6ac2e94aac58b6ed626cfe0170bd7e9c00aa1a5e151a2e7e24

See more details on using hashes here.

File details

Details for the file haip_database-0.1.4-py3-none-any.whl.

File metadata

  • Download URL: haip_database-0.1.4-py3-none-any.whl
  • Upload date:
  • Size: 8.8 kB
  • Tags: Python 3
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/1.12.1 pkginfo/1.5.0.1 requests/2.21.0 setuptools/40.7.1 requests-toolbelt/0.9.1 tqdm/4.30.0 CPython/3.7.2

File hashes

Hashes for haip_database-0.1.4-py3-none-any.whl
Algorithm Hash digest
SHA256 01fbe089f412ad480c2daaba7851696e9d3653153ca4d8351a0c094ca0d4254c
MD5 13bfa48335c5457132f84b3a3fb9dd90
BLAKE2b-256 fed1a53000efd8910286921691d7729feb8b39342e5915beb71021c15ecadf8a

See more details on using hashes here.

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page