iyappan24.github.io

Eziocon : A Python SDK for SQL

enter image description here

A wrapper that allows to perform basic operations of fetching insertion and updation in all kinds of databases which have SQL like schema.

Table of Contents :

  1. Installation Procedure
  2. Databases Covered
  3. Dependencies
  4. Functions
    4.1 count
    4.2 fetchOne
    4.3 fetchMany
    4.4 insert
    4.5 update
  5. Examples
  6. Github Links and Support
  7. Future Enhancements

Installation procedure

The python package can be installated using the following command using pip in your system. It requires a python version more than or equal to python 3.5.4. Information about other dependencies are given in the following sections.

Databases Covered :

The tested and stable version of the code has the support for the following databases :

Database Tested - Version
MySql >= 5.7
Oracle >=12c

Dependencies

These are the following dependencies which are to be installed in your system for the proper functioning of the package. These can be done using pip commands. Eziocon is a wrapper written above them for enabling the users to interact with python objects to achieve basic SQL operations instead of creating the queries every single time.

Dependency list:

cx_oracle dependency is used to connect to oracle based SQL databases. Apart from doing a simple pip install cx_oracle in the terminal , you need have oracle based libraries setup in your system and your environment variables must be configure for the proper working of cx_oracle library.

The following link will be useful to setup *cx_oracle and **oracle client libraries **in linux,windows and mac based operating systems respectively:

Functions

The following functions are the wrappers which will be common across all the databases covered by the package w.r.t. to return types and function arguments.
This enables the user to use these member functions for data migrations across different SQL platforms via python interface.

setConnect :

Method to take in the following inputs instantiate the connection object to the database

Mysql Parameters :

Oracle Parameters:

Returns: None : Raise Value error if in case of any error

Count :

Method to return the count of records given table name and SQL where clause

Parameters:
  • tablename : String :
    Name of the table in the database
  • condition : String :
    SQL query : Where clause filter

Returns : Count : Integer

fetchOne :

Method to return a single record given table name , column name and SQL where clause

Parameters:
  • tablename : String
    Name of the table in the database
  • condition : String
    SQL query : Where clause filter
  • columns : Iterator of String : (List or tuple )
    Any of the above mentioned iterators with column names of the table as values
  • return_type : Integer : Default Value 1
    Specifies the format in which the result has to be returned. For getting the results in Dataframe value must be 1 and for getting the results in Dictionaries the value must be 2.

Returns : Dataframe or Dictionaries

fetchMany :

Method to return a group of records given table name , column name ,SQL where clause and number of rows

Parameters:
  • tablename : String
    Name of the table in the database
  • condition : String
    SQL query : Where clause filter
  • columns : Iterator of String : (List or tuple )
    Any of the above mentioned iterators with column names of the table as values
  • rows : Integer: Default Value = -1
    Number of rows that have to fetched from the database. Must be greater than 1. The default value -1 specifies fethc all the rows for the given query.
  • return_type : Integer : Default Value 1
    Specifies the format in which the result has to be returned. For getting the results in Dataframe value must be 1 and for getting the results in Dictionaries the value must be 2.

Returns : Dataframe or Dictionaries

Insert :

Method to insert a bunch of records or a single record given the table name and data

Parameters:
  • tablename : String
    Name of the table in the database
  • objects : Dictionary or List of Dictionaries : {‘column name’ : Value}
    For inserting a single record pass a Dictionary with the above mentioned format and For inserting a bulk bunch of records into the Table pass a list of Dictionaries.

Returns : True : Boolean

update :

Method to update a group of records statisfying the where clause condition given table name, Values that must be updated and SQL where clause

Parameters:
  • tablename : String
    Name of the table in the database
  • updations : Dictionary : {‘column name’ : Value}
    A Dictionary object which has the list of columns to be updated as the key and changes of the corresponding the columns as the respective value.
  • condition : String
    SQL query : Where clause filter

Returns : True : Boolean

Examples

This Blog post will give you a detail walk through of how to use and manipulate data using the above mentioned functions.

Contents:

  1. Filtering and search operations in table
  2. Bulk insert and insert operations in table
  3. Best Scnearios and practices to use these functions
  4. Different Update variations using various where clauses

Future Enhancements

The following enhancements will be implemented:

This site is open source. Improve this page.