A generic database interface.
Project description
haip.config
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
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 haip_database-0.1.4-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 01fbe089f412ad480c2daaba7851696e9d3653153ca4d8351a0c094ca0d4254c |
|
MD5 | 13bfa48335c5457132f84b3a3fb9dd90 |
|
BLAKE2b-256 | fed1a53000efd8910286921691d7729feb8b39342e5915beb71021c15ecadf8a |