This module is a wrapper aroung SQL query language, i.e an interface between the database and python.
Can be used to develop a database based application quickly in python. It is a method based package, i.e you don't require the knowledge of sql language. Basically one can create a CRUD application quickly.
Currently provides an interface for Mysql, sqlite, postgresql databases. Tested for python 2.7.x
Requirements
psycopg2==2.6.1 (and above)(for postgresql)
mysqlclient==1.3.7 (and above)(for mysql Basically import MYSQLdb)
Advantages
Installation
pip install sqlwrapper
There are Basically three submodules inside the main module.
Although there are some examples but let's just see how to import
>> from sql import sqlitewrapper
>> dblite = sqlitewrapper()
>> from sqlwrapper import mysqlwrapper
>> dbmysql = mysqlwrapper()
>> from sqlwrapper import psqlwrapper
>> dbpg = psqlwrapper()
Let's Look at the functions that are provided
set the database path using this function
function definition:
connect(databasepath)
example: db.connect('/path/to/your/database.sqlite')
set a session password to execute sensitive commands
function definition:
set_session_password(pwd)
example: db.set_session_password("a_password")
login to a session to execute sensitive commands
function definition:
login(pwd)
example: db.login("password_that_was_set")
logs out the session now no more sesitive commands can be used
fetches all the data from a given table
function definition:
fetch_all(tablename)
example: db.fetch_all('users')
return_type: returns list of dictionaries (i.e the whole table)
fetches the first data from the table
function definition:
fetch_first(tablename)
example: db.fetch_first('users')
return_type: single dictionary (i.e row)
fetches the first data from the table
function definition:
fetch_last(tablename)
example: db.fetch_last('users')
return_type: single dictionary (i.e row)
fetches data from a given table with where condition
function definition:
fetch_where(tablename,where)
type of where clause should be string
example: db.fetch_where('users','id >= 4')
returns: list of dictionaries that satisfies the where clause
inserts data in the given tale
function definition:
insert(tablename,columns,values)
columns should be a list containing column names(string)
values should be a list containing column values
db.insert('users',['id','name'],[1,'saif'])
db.insert('users',[],[1,'saif']) if there are only two columns in the table
deletes data from a given table provided a where condition that identifies
the row to delete.
function definition:
delete(tablename,where)
type of where clause should be string
example:db.delete('users',"name = 'ankitesh' or id = 4")
deletes all data from a given table
function definition:
delete_all_from(tablename)
example: db.delete('users')
drops the given table from the database
requires to be authenticated to execute this command
function definition:
drop_table(tablename)
before executing this set the password using set_session_password() method and then login
drop_table('users')
creates a table in the database
function definition:
create_table(tablename,columns,data_types,primary_key)
arguments:
tablename: appropriate tablename (string)
coulmns = [] array which contains column names (string)
data_types = [] valid data_types = ['integer','text','real','numeric','blob']
primary_key: a key that uniquely identifies the row (string)
returns a list of table which contains all the table name in the database
updates data to a given table with where condition
function definition:
update(tablename,columns,where,values)
type of where clause should be string
example: db.update('users',['name'],'id >= 4',['saif'])
returns number of entries in a table
function definition:
count_entries(tablename)
example: db.count_entries('users')
describes the columns of the given table
describe_table(tablename)
set the database path using this function
function definition:
connect(host,username,password,dbname)
example: db.connect('host','username','password','dbname')
fetches all the data from a given table
function definition:
fetch_all(tablename)
example: db.fetch_all('users')
return_type: returns list of dictionaries (i.e the whole table)
fetches the first data from the table
function definition:
fetch_first(tablename)
example: db.fetch_first('users')
return_type: single dictionary (i.e row)
fetches the first data from the table
function definition:
fetch_last(tablename)
example: db.fetch_last('users')
return_type: single dictionary (i.e row)
fetches data from a given table with where condition
function definition:
fetch_by(tablename,where)
type of where clause should be string
example: db.fetch_by('users','id >= 4')
returns: list of dictionaries that satisfies the where clause
inserts data in the given tale
function definition:
insert(tablename,columns,values)
columns should be a list containing column names(string)
values should be a list containing column values
db.insert('users',['id','name'],[1,'saif'])
db.insert('users',[],[1,'saif']) if there are only two columns in the table
deletes data from a given table provided a where condition that identifies
the row to delete.
function definition:
delete_by(tablename,where)
type of where clause should be string
example:db.delete_by('users',"name = 'ankitesh' or id = 4")
deletes all data from a given table
function definition:
delete_all_from(tablename)
example: db.delete('users')
drops the given table from the database
requires to be authenticated to execute this command
function definition:
drop_table(tablename)
before executing this set the password using set_session_password() method and then login
drop_table('users')
creates a table in the database
function definition:
create_table(tablename,columns,data_types,primary_key)
arguments:
tablename: appropriate tablename (string)
coulmns = [] array which contains column names (string)
data_types = [] valid data_types = ['integer','text','real','numeric','blob']
primary_key: a key that uniquely identifies the row (string)
returns a list of table which contains all the table name in the database
updates data to a given table with where condition
function definition:
update_by(tablename,columns,where,values)
type of where clause should be string
example: db.update_by('users',['name'],'id >= 4',['saif'])
returns number of entries in a table
function definition:
count_entries(tablename)
example: db.count_entries('users')
describes the columns of the given table
describe_table(tablename)
set the database path using this function
function definition:
connect(host,username,password,dbname)
example: db.connect('host','username','password','dbname')
fetches all the data from a given table
function definition:
fetch_all(tablename)
example: db.fetch_all('users')
return_type: returns list of dictionaries (i.e the whole table)
fetches the first data from the table
function definition:
fetch_first(tablename)
example: db.fetch_first('users')
return_type: single dictionary (i.e row)
fetches the first data from the table
function definition:
fetch_last(tablename)
example: db.fetch_last('users')
return_type: single dictionary (i.e row)
fetches data from a given table with where condition
function definition:
fetch_by(tablename,**kwargs)
kwargs should be a column name = its value(it is where clause which will identify the row
note: for where clause if multiple keyword arguments are supplied it will be joined using and
example: db.fetch_by('users',id = 4)
returns: list of dictionaries that satisfies the where clause
inserts data in the given tale
function definition:
insert(tablename,columns,values)
columns should be a list containing column names(string)
values should be a list containing column values
db.insert('users',['id','name'],[1,'saif'])
db.insert('users',[],[1,'saif']) if there are only two columns in the table
deletes the entry from the table using the where clause to identify the row
function definition:
delete_by(tablename. **kwargs)
kwargs should be a column name = its value(it is where clause which will identify the row
note: for where clause if multiple keyword arguments are supplied it will be joined using and
example: delete_by('users',id=4)
deletes all data from a given table
function definition:
delete_all_from(tablename)
example: db.delete('users')
drops the given table from the database
requires to be authenticated to execute this command
function definition:
drop_table(tablename)
before executing this set the password using set_session_password() method and then login
drop_table('users')
creates a table in the database
function definition:
create_table(tablename,columns,data_types,primary_key)
arguments:
tablename: appropriate tablename (string)
coulmns = [] array which contains column names (string)
data_types = [] valid data_types = ['integer','text','real','numeric','blob']
primary_key: a key that uniquely identifies the row (string)
returns a list of table which contains all the table name in the database
updates data to a given table with where condition
function definition:
update(tablename,columns,values,**kwargs)
tablename is the name of the table
columns = [] should be a list of columns that is to be updated
values = [] should be a list of vlaues corresponding to the columns
kwargs should be a column name = its value(it is where clause which will identify the row
note: for where clause if multiple keyword arguments are supplied it will be joined using and
also you can't update the whole row at once without giving the column names
example:
db.update('users',['name'],['saif'], id=4)
db.update('Books', ['title', 'cover'], ['new_title','new_cover'], pages>100, pages<200)
in this case it will be equivalent to pages between 100 and 200
returns number of entries in a table
function definition:
count_entries(tablename)
example: db.count_entries('users')
describes the columns of the given table
describe_table(tablename)
Let's create a sample CRUD application to understand the usage of this module. The application is a management system which manages a pet store
ALL the py files of the examples is in the github repo, also you can click the below link to view text file
SQLitePlease Email or post on github if any issues/changes are required also if you want to make any contributions then you can generate a pull request on github.