Simple ORACLE Database Connection with Python

How to work with Oracle through Python?

Installation of Oracle latest version 19c.

Go to oracle.com website at the following link:

http://www.oracle.com/technetwork.datbases.database-technologies/express-edition/downlaods/index.html

So after this you able to download and install on your system but you need another thing Oracle Database Driver.

Installing Oracle Database Driver

We have to install a driver or connector software that connects the Oracle database with our Python programs.

The name of the driver needed is 'ex_Oracle' and it can be installed using pip command that comes with python by default. Just go to system prompt and type the command like below:

C:\>pip install cx_Oracle

Now let me give you a simple program to understand.

Program # 1 : 

import cx_Oracle

conn = cx_Oracle.connect('SYSTEM/ABHINAW@localhost')

cursor = conn.cursor()

cursor.execute("select * from table")

row = cursor.fetchone()

while row is not None:

    print(row)

    row = cursor.fetchone()

cursor.close()

conn.close()

Output: 

C:>python ora.python

(1001,'ABHINAW',100000)

(1002,'JAYANTA',100000)

(1003,'PRITAM',100000)

(1004,'BIPRADIP',100000)

Now since we are discussing ORACLE so let me also tell you about "Stored Procedures"

Stored Procedures

So a stored procedure is a set of statements written using PL/SQL(Procedural  Language / Structured Query Language). To perform some calculations on tables of a database, we can use stored procedures. Stored procedures are written and stored at the database server. So when a client contacts the server, the stored procedures are executed and results are sent to the client.

Stored Procedures are compiled once and stored in the executable form at the server-side. The memory for stored procedures is not allocated every time they are called. Stored Procedures are stored in cache memory at the server-side and they are immediately available to clients. This is one of the main advantages of Stored Procedures. 

let me give you an example of how to write the stored procedures program:

-- myproc.sql

-- to increase number in my table

create or replace provider myproc(no in int,isal out float) as

salary float;

begin 

         select sal into salary from table where someid=no

         isal := Salary+1000;

end;

/

Note: atlast line you see '/' indicates to execute the procedure at server side.

Now Points to Remember:

• A database management system(DBMS) represents software that stores and manage data.
• After installing the ORACLE driver we can see a new module by name 'cx_Oracle' in the Python library.
• The SQL commands are almost the same for all databases like MYSQL or ORACLE.

Python Program to retrieve a row from a MYSQL database table using GUI

 

So another example this time with GUI retrieves a row using GUI -- Python a GUI application

#Program: 

import MySQLdb

from tkinter import *

root = TK() # create root window

def retrieve_rows(eno): # takes employee number and display

    conn = MYSQLdb.connect(host = 'localhost' , database = 'Anything' , user = 'root' , password ='Pass123')

cursor = conn.cursor()

     str = "select * from table where eno = '%d'"

args = (eno)

cursor.execute(str % args)

row = cursor.fetchone()

 

if row is not None:

     lbl = Label(text=row,font=('Arial' , 14)).place(x=50, y=200)

 

cursor.close()

     conn.close()

#takes input from Entry widget

def display(self): 

    str =e1.get()

    lbl =Label(text='You entered: '+str,font=('Arial' , 14)).place(x=50, y=150) 

    retrieve_rows(int(str)

# create a frame as child to root window

f = Frame(root, hegight=350,width=600)

f.propagete(0)

f.pack()

l1=Label(text='Enter employee number : ',font=('Arial' , 14))

e1 = Entry(f,width=15 , fg='blue' , bg='yellow',font=('Arial',14))

e1.bind(("<Return>" , display)

l1.place(x=50,y=100)

e1.place(x=300,y=100)

root.mainloop()

Output: 

Python Database Connectivity with MySQL

So again another most demanded topic this week is Python's Database Connection

I am going to cover everything in a peeling way. I will tell you exactly how to do it without any crap.

DBMS

To store data we need a database. A database represents a collection of data. We can also perform some operations on data. for example modifying the existing data, deleting the unwanted data, or retrieving the data from the database. To perform such operations a database comes with the software. This is called a database management system(DBMS).

DBMS = database + software to manage the data

Examples for DBMS are MYSQL, ORACLE, SYBASE, SQL SERVER, etc.

Types of Databases Used with Python

So here I will certainly talk about Python and Databases for a better understanding of how it works with Python.

To work with any database we should have the database installed in our computer system. And to connect to that database we need a driver or connector program that connects the database with Python program.

So now let's use MYSQL very commonly used database. In order to install it open the mysql.com website and download the installer.

https://dev.mysql.com/downloads/windows/installer/

Just click next - next and install it after this how to verify that MYSQL is installed properly.

Verifying MySQL in the Windows Operating System

So for this just go to the Start -> your computer and you will find the MYSQL option. but you have to do one more thing after this is like

Installing MYSQLdb Module

Why this? because To communicate with MYSQL database from Python we need a program that becomes an interface to the MySQL database server. So MYSQLdb is an interface between the MySQL server and Python programs. To install it you have to do this way.

https://pypi.python.org/pypi/mysqlclient

So after this, you are ready to use the MySQL database with Python certainly. Whatever  I have mentioned is for the core-python developer.

So let me give you some examples of simple programs that you can directly execute on your systems.

#Program : 

Retrieve and Display all rows from a any table.

import MySQLdb

# connect to Mysql database

conn = MYSQLdb.connect(host = 'localhost' , database = 'Anything' , user = 'root' , password ='Pass123')

# prepare a cursor object using cursor() method

cursor = conn.cursor()

# execute the query

cursor.execute("select * from table")

# fetch only one row

row = cursor.fetchone()

#if the rows exists

while row is not None:

        print(row)

         row = cursor.fetchone()

# close the connection

cursor.close()

conn.close()

Output: In My case, it is like below

Connected to MySQL database

(1001 ,  'Abhinav', '10000')

(1002 ,  'Pritam' , '10000')

(1003 ,  'Jayanta' , '10000')

(1004 ,  'Arindam' , '10000')

So we can also use the fetchall() method that retrieved all the rows from my table like 

rows = cursor.fetchall()

This is a simple database connection program for Python Developer.

Best Practice for Handling Back Hard/Soft Navigation for Fragments - Kotlin/Java Android

So I was getting this question from my viewers for handling the back/soft press on Android App. See there are many ways to handle this but every app has nowadays followed a Design-Patterns like MVC, MVP, MVVM, REDUX, etc. So how to implement the back navigations which work smoothly. I will try to give you a complex use-case So that you can relate to it. But let's assume you have created an Android Project with 2 Activities and Say 20 fragments in it.

So suppose it is like below

Activity1 hosting 10 fragments and then Another Activity2 hosting another 10 fragments So now how to design you back navigation for this use case.

For Fragments and Back Navigation Google App Developer site have a well-defined article but you have to understand it in order to implement below is the URL of it in case you wish to see.

https://developer.android.com/guide/components/activities/tasks-and-back-stack

So suppose you have Designed your app code structure like you know you have BaseActivity and BaseFragment and you are defining all the functions withing it and obviously implementing those in your Activity1 and Activity2 right So far with me?

The same goes for Fragments to Since you have Base Fragments So you must be doing like below I am giving example.

This is pretty basic Java/Kotlin but actually quite essential for any fragment-heavy application. I find this leads to less code complexity but more extensibility for me. I have a standard container layout in XML, and call the following to the swap fragments in it:

Fragment myNiceFragment = MyNiceFragment.instantiate(mContext, MyNiceFragment.class.getName(), args);

getSupportFragmentManager().beginTransaction()

        .replace(R.id.content_container, myNiceFragment, myNiceFragment.getClass().getSimpleName())

        .setTransition(FragmentTransaction.TRANSIT_FRAGMENT_OPEN)

        .addToBackStack(null)

        .commit();

For committing fragments you must be doing like above this is just a code snippet to make you understand how to do it? Do not confuse between Add/Replace/Remove of fragments right?

Of course, by adding it to the back stack it pops when we press back by default, which is just lovely.  user hits back, and not just have the hosting activity know that we pressed back. He should share that info with anyone that would like to know!.

Solution

To tell fragments when the back button has been pressed, first of all, you need a base fragment which all of your other fragments inherit from. This base fragment implements the following:

BaseFragment.Java/BaseFragment.kt: whichever way you prefer.

public interface OnBackPressed {

    void onBackPressed();

}

public class BaseFragment extends Fragment implements OnBackPressed {

    @Override

    public void onCreate(Bundle savedInstanceState) {

        super.onCreate(savedInstanceState);

    }

    @Override

    public View onCreateView(LayoutInflater inflater, ViewGroup container, Bundle savedInstanceState) {

        return super.onCreateView(inflater, container, savedInstanceState);

    }

    public void onBackPressed() {//Leave it empty}

}

Now in your hosting Activity, call any implementations of the interface before any fragments are popped:

Just do this in your Hosting Activit1 and Activity2 and You are good to go.No problem at all.

@Override

public void onBackPressed() {

    tellFragments();

    super.onBackPressed();

}

private void tellFragments(){

    List<Fragment> fragments = getSupportFragmentManager().getFragments();

    for(Fragment f : fragments){

        if(f != null && f instanceof BaseFragment)

            ((BaseFragment)f).onBackPressed();

    }

}

That’s it! Any fragments which implement our onBackPressed() can do so and will be told as soon as the Activity is.

In Your Respective Fragments:

@Override

public void onBackPressed(){

    getActivity().getSupportFragmentManager().popBackStack();

}

So what are the Pros and Cons implementing this way if you see Suppose you have a requirement like when press back from Fragment 10 and I should Navigate to Fragment 2 Right? So it is just easy like in you on the BackPress method write a while loop and put a condition and just make a fragment Pop call and you are good. This type of requirement you will find in almost all your project So I find this way you are more controlled and maintained and once you leave the company your fellow Developer can understand it too :). I saw many people end up doing it in Activiting making switch cases and handling there believe me at some point in time the code becomes impossible to understand for a big project. So this one is the best way to do it. let me know your thoughts on it.

Note Just little tweak to make it compatible with Kotlin is just do it like below:

interface OnBackPressed {

    fun onBackPressed();

}

I hope you understand it and if you are not able to d to it just copy and paste this code and Android Studio will do it for you.

Thanks.

Kotlin - Extension Functions

Extensions - Extensions let you add new functions and properties to an existing type without you having to create a whole new subtype.

Before getting deep into it lest understand some points about Design Patterns.

Object declarations provide a way of implementing the Singleton patterns as each declaration creates a single instance of that object.

Extensions may be used in place of the Decorator patterns as they allow you to extend the behaviour of classes and objects And if you are interested in using the Delegation patterns as an alternative to the inheritance.

For example: 

// Defines a function named toDollar() which extends Double.
fun Double.toDollar() : String
{
   return "$$this" // returns the current value prefixed with $.
}

Kotlin - Modifiers , Enum classes ,Sealed classes

Visibility Modifiers 

It lets you set the visibility of any code that you create such as classes and functions. For example, a member function can only be used inside its class.

So Kotlin has four visibility modifiers : 

1) public
2)private
3)protected
4)internal

Modifiers:  What it does:

public        It makes the declaration visible everywhere.This is applied by default so it can be omitted.

private       It makes the declaration visible to code inside its source file but invisible elsewhere.

internal      It makes the declaration visible inside the same module but invisible elsewhere.

Lets take an example :

open class Parent
{
  var a = 1

   private var b =2

   protected open var c = 3

   internal var d =4
 
}         

class Child : Parent()
{
  override var c = 6
}

Note that if you override a protected member as in the above code, the subclass version of that member will also be protected by default.you can however
change its visibility as in this example,

class Child : Parent()
{
 public override var c = 6
}

Enum classes :

An enum class lets you create a set of values that represent the only valid values for a variable.

for example:

enum class BandMember { AVI,KUNAL,PRASHANT} // the enum class has 3 values.

fun main(args : Arrays<String>)
{
   var selectedBandMember : BandMember // The variable's type is BandMember
   selectedBandMember = BandMember.AVI  // So we can assign one of BandMembers value to it.
}

Note : 

• Each value in an enum class is constant.
• Each enum constant exists as a single instance of that enum class.

enum properties and functions example:

enum class BandMember(val instrument : String) { AVI("Lead Guitar"),KUNAL("Rythm guitar"),PRASHANT("bass");

fun sings() = "occasionally"  // Each enum value has a function named sings which return the String
}

enum class BandMember(val instrument : String)
{
  AVI("Lead Guitar")
  {
    override fun sings() = "plaintively"
  },
  KUNAL("Rythm Gutar")
  {
    override fun signs() = "hoarsely"
  },
  PRASHANT("Bass")
  {
  };

  open fun signs() = "occasionally"

}

fun main(args : Array<String>)
{
   var selectedBandMember : BandMember

   selectedBandMember = BandMember.AVI
 
   println(selectedBandMember.instrument)
 
   println(selectedBandMember.sings())
}

Sealed classes 

Suppose that you want to be able to use two different message types in your application: one for success and another for failure.

How to do this?

enum class Message Type(var msg: String)
{
  SUCCESS("YAH!!"),
  FAILURE("Failed!!")
}

But there is a problem with this approach:

1) Each value is a constant which only exists as a single instance.
2)Each value must have the same properties and functions.

So what's the solution?

Sealed classes to the rescue!:

So a sealed class is like a souped-up version of an enum class.it lets restrict your class hierarchy to a specific set of subtypes each one of which can be defined as its own properties and functions.

You can create a sealed class by prefixing the class name with sealed.

for example;

 sealed class Message Type
 class MessageSuccess(var msg: String) : MessageType()
 class MessageFailure(var msg: String) : MessageType()

fun main(args : Array<String>)
{
   var messageSucc : MessageSuccess ("Successful")
   var messageSucc2 : MessageSuccess ("It worked Successful")
   var messageFail : MessageSuccess ("Failed")
}

Kotlin - JUnit and Kotlin Test

JUnit and Kotlin Test

JUnit and Kotlin Test are libraries that you can use to unit test your code so that you can always have a safety net.

Add the JUnit library 

you can add these lines to your build.gradle file:

dependencies{

testimplementation 'org.junit.jupiter:jupiter:junit-jupiter-api:5.3.1'
testRuntimeOnly 'org.junit.jupiter:junit-jupiter-engine:5.3.31'
test{useJUnitPlatform}

}

So let me show you how to write the JUnit with Kotlin. Let's create a class :

class Totaller(var total : Int =0)
{
  fun add (num: Int) : Int{
       total+ = num
   return total
  }
}

Create a JUnit test class like below :

class Totaller(var total : Int =0)

{

  fun add (num: Int) : Int{

       total+ = num

   return total

  }

}

TotallerTest:

import org.junit.jupiter.api.Assertions.*

import org.junit.jupiter.api.Test

class TotallerTest

{

 @Test

 fun shouldBeAbleToAdd3And4()

 {

   val totaller = Totaller()

   

   assertEquals(3 , totaller.add(3))

   assertEquals(3 , totaller.add(4))

   assertEquals(3 , totaller.total)

 }

}

Points to take a note on:

1) We are using code from the JUnit packages so we need to import them.

2)The TotallerTest class is used to test Totaller

3)@Test is an annotation that marks the following function as a test.

4)Tests are made up of actions and assertions.

5)Actions are a piece of code that does stuff while assertions are pieces of code that check stuff.

Now We will use the KotlinTest :

The KotlinTest library has been designed to use the full bread of the Kotlin language to write tests in a more expressive way.

KotlinTest is pretty vast and it allows you to write tests in many different styles but here is one way of writing a KotlinTest version of the JUnit code.

import io.kotlintest.shouldBe

imnport io.kotlintest.specs.StringSpec

class AnotherTotallerTest :StringSpec({

"Should be able to add 3 and 4  - and it must not go wrong"{

   

    val totaller = TotallerTest()

totaller.add(3) shouldBe 3

totaller.add(4) shouldBe 7

totaller.total shouldBe 7

  

}

Points to take a note on:

This is String Specification or StringSpec style.

Now we will use rows to test against sets of data

import io.kotlintest.data.forall

import io.kotlintest.shoudBe

import io.kotlintest.specs.StringSpec

import io.kotlintest.tables.rows

class AnotherTotallerTest :StringSpec({

"Should be able to add 3 and 4  - and it must not go wrong"{

   

    val totaller = TotallerTest()

totaller.add(3) shouldBe 3

totaller.add(4) shouldBe 7

totaller.total shouldBe 7

  

}

"should be able to add lots of different numbers"{

forall(

         row(1,2,3),

row(19,47,66),

row(11,21,32)

){ x,y,expectedTotal - >

val totaller = TotallerTest(x)

totaller.add(y) shouldBe expectedTotal

}

}

})  

Points to take a note on:

• Run test in parallel
• Create tests with generated properties
• Enable/disable tests dynamically. you may, for example, want some test to run only on Linux and others to run on Mac.
• Put tests in groups.

Kotlin - Coroutines

Some tasks are best performed in the background. So Coroutines let you write code that's run asynchronously. Coroutines allow you to create multiple pieces of code that run asynchronously. Instead of running pieces of code in sequence one after another, Coroutines let you run them side-by-side.

To add a Coroutine dependency in your project you have to add this:

dependencies
{
  compile 'org.jetbrains.kotlin:kotlin-stdlib-jdk8'
  implementation 'org.jetbrains.kotlinx:kotlinx-coroutines-core:1.0.1'
}

A Coroutine is like a lightweight thread 

Behind the scenes, launching a Coroutine is like starting a separate thread of execution or thread. The key difference, however, is that it is more efficient to use Coroutines in your code than it is to use threads.
Starting a thread and keeping it running is quite expensive in terms of performance. Coroutines run on a shared pool of threads by default and the same thread can run many coroutines. As fewer threads are used this makes it more efficient to use Coroutines when you want to run tasks asynchronously.

So let's create a sample code that runs in parallel. So I will play sound files run in separate coroutines in the same thread.

Code Example:  

import java.io.File

import javax.sound.sampled.AudioSystem

import kotlinx.coroutines.*



//Why Suspend because it van call the delay function

suspend fun playBeats(beats:String,file:string)

{

   val parts = beats.split("x")

   var count = 0

   for(parts in parts)

    {
        count + = parts.lenght + 1

        if(part == "")

        {

           playSound(file)

        }
else
{
  delay(100 * (part.length + 1L))
  if(count < beats.length)
  {
    playSound(file)
  }
}

   }
 
   fun playSound(file : String)
   {
     val clip = AudioSystem.getClip()
     val audioInputStream = AudioSystem.getAudioInputStream(
            File(
file
)
)
     clip.open()
clip.start()
   }

suspend fun main()
{
           runBlocking
   {
     launch
{
   playBeats("some file name","abc.aiff")
}

   }
}

}

When you run the code, it plays the some music in parallel, and the sound file run in separate coroutines in the same thread.

Some Important Points:

• coroutines let you run code asynchronously . They are useful for running background tasks.
• A coroutine is like a lightweight thread. Coroutine run on the shared pool of threads by default and the same thread can run many coroutines.
• Use the launch function to launch a new coroutine.
• The runBlocking function blocks the current thread until the code it contains has finished running.
• The delay function suspends the code for a specified length of time. It can be used inside a coroutine or inside a function that's marked using suspend.