dbConnect: Database for Humans¶
Release v2.1. (Installation)
dbConnect is an MPLv2 Licensed Module for little projects using mysql or postgresql databases. It generates mysql and postgresql queries automatically, you just send data in pythonic style and it does the rest.
>>> from dbConnect import DBConnect
>>> database = DBConnect('credentials.json')
>>> users = database.fetch('users', limit=5, filters={'status': 'active'})
>>> new_user = {'name': 'Emin', 'status': 'active', 'company': 'pyninjas'}
>>> database.insert(new_user, 'users')
Feature Support¶
- fetch all fields in table as dictionary (column name: value)
- fetch only selected fields
- fetch using filters
- limit fetch result
- filter case [AND, OR]
- insert to table
- update row
- delete row
- increment column in table
- sum of a numeric column(s)
- custom sql query
User Guide¶
Introduction¶
dbConnect vs ORM¶
If you have big project then building models (entities) and using ORM will help you a lot and will be a lot safer.
dbConnect was made as little module to be used in small projects that need to do some interactions with MySQL or PostgreSQL databases.
It’s just a big time saver for developers and helps to keep your code clean and readable.
dbConnect License¶
dbConnect is released under terms of MPLv2 License.
Installation¶
This part of the documentation covers the installation of dbConnect.
Requirements¶
dbConnect uses mysql.connector and mysqlclient for mysql, install one of them using:
$ apt-get install python3-mysql.connector
$ apt-get install python-mysql.connector
$ pip install mysqlclient
For PostgreSQL install psycopg2 module:
$ pip install psycopg2
Distribute & Pip¶
Installing dbConnect is simple with pip, just run this in your terminal:
$ pip install dbConnect
or, with easy_install:
$ easy_install dbConnect
But, you really shouldn’t do that.
Get the Code¶
dbConnect is actively developed on Gitlab, where the code is always available.
You can either clone the public repository:
$ git clone git@gitlab.com:mastizada/dbConnect.git
Once you have a copy of the source, you can embed it in your Python package, or install it into your site-packages using:
$ python setup.py install
Quickstart¶
This page gives a good introduction in how to get started with dbConnect.
First, make sure that:
- dbConnect is installed
Lets go over every function.
Connection¶
dbConnect uses credentials.json
file from your project directory by default.
You can give custom path for that file:
>>> database = DBConnect('/home/user/project/credentials.json')
Or provide database details:
>>> database = DBConnect(host='127.0.0.1', user='root', password='', database='test')
You can provide any other parameters that are available in mysql.connector or PostgreSQL Documentation
- After successfull connection there will be database.connection and
database.cursor variables that can be used as in official MySQL or PostgreSQL documentation.
Engines¶
dbConnect supports mysql and postgres as engine options.
Fetch Data¶
Get data from table.
Basic usage:
>>> database.fetch('table_name')
- Fields:
- table:
str
: name of table, must be provided - limit:
int
: result limit, default1000
- fields:
array
: list of column names, defaultNone
- filters:
dict
: dictionary with keys as column name, defaultNone
- case:
str
: search case for filter [AND, OR], default'AND'
- table:
Example:
>>> database.fetch('user', limit=5, fields=['id', 'name', 'email'], filters={'company': 'pyninjas'})
>>> database.fetch('user', limit=5, filters={'id': (10, '>=')}) # Get 5 user whose id is higher than 10
>>> database.fetch('user', filters={'email': (None, 'is')}) # Get users whose email is NULL
>>> database.fetch('user', filters={'email': None}) # Same as (None, 'is')
>>> database.fetch('user', filters={'email': (None, 'is not')}) # Get users whole email is not NULL
>>> database.fetch('user', filters={'id': (0, 100, '<=>')}) # Get users whose id is between 0 and 100
>>> database.fetch('user', filters={'id': (0, 100, '<>')}) # Get users whose id is between 1 and 99
Insert Data¶
Add new row (data) to table.
- Fields:
- data:
dict
: dictionary with keys as column name, must be provided - table:
str
: name of table, must be provided - commit:
bool
: commit after insert command, default: True - update:
dict
: Update selected columns if key is duplicate, default: None
- data:
Example:
>>> new_user = {'name': 'Emin', 'company': 'pyninjas', 'website': 'mastizada.com'}
>>> database.insert(new_user, 'user') # Adds new_user to user table
Example 2:
>>> new_user = {'id': 1, 'name': 'Ramin', 'company': 'pyninjas', 'website': 'mastizada.com'}
>>> # if there is user with id=1, then update its name:
>>> updated_columns = {'name': 'Ramin'}
>>> database.insert(new_user, 'user', update=updated_columns)
Update Data¶
Update existing row.
- Fields:
- data:
dict
: dictionary with keys as column name that will be changed, must be provided - filters:
dict
: filters to find row(s) that will be changed, must be provided - table:
str
: name of table, must be provided - case:
str
: search case for filter [AND, OR], default'AND'
- commit:
bool
: commit after insert command, default: True
- data:
Example:
>>> database.update({'name': 'Emin Mastizada'}, {'id': 1, 'name': 'Emin'}, 'user', case='OR')
Delete Data¶
Delete row from database.
- Fields:
- table:
str
: name of table, must be provided - filters:
dict
: filters to find row(s) that will be deleted, must be provided - case:
str
: search case for filter [AND, OR], default'AND'
- commit:
bool
: commit after insert command, default: True
- table:
Example:
>>> database.delete('user', {'id': 1, 'name': 'Emin Mastizada'}, case='OR')
Increment Columns¶
Increment provided columns.
- Fields:
- table:
str
: name of table, must be provided - fields:
array
: list of column names to increment, required - steps:
int
: Steps to increment, must be provided - filters:
dict
: filters to find row(s) - case:
str
: search case for filter [AND, OR], default'AND'
- commit:
bool
: commit after insert command, default: True
- table:
Example:
>>> database.increment('user', ['views'], steps=2, filters={'id': 1})Total sum of a numeric column(s).
- Fields:
- table:
str
: name of table, must be provided- fields:
array
: list of numeric column names, required- filters:
dict
: filters to find row(s)- case:
str
: search case for filter [AND, OR], default'AND'
Example:
>>> database.value_sum('user', fields=['views'])
Custom SQL Query¶
Execute custom sql queries when you need something complex.
Example:
>>> database.cursor.execute("SELECT * FROM table WHERE id = 5")
>>> results = database.cursor.fetchall()
>>> database.cursor.execute("SELECT u.name FROM users as u INNER JOIN tasks as t ON t.user = u.id WHERE t.progress = 'assigned'")
>>> users = database.cursor.fetchall()
Commit Data¶
Commit changes to database.
No fields.
Example:
>>> database.commit()
And now enjoy and give me your feedbacks ;)