Communicating with Oracle using cx_Oracle – python

On the series of communicating with various databases, I am going to add on oracle. Sometimes it becomes difficult to debug and this helps. Remember, a semicolon (;) at the end is not needed. If you use it, it shall give you a 00911 Error. Be careful.


import cx_Oracle

def oracle_connect_and_execute():

    connstr = 'user_name/pass_word@(DESCRIPTION=\
                 (ADDRESS=(PROTOCOL=TCP)\
                 (HOST=host_name)\
                 (PORT=1521))\
                 (CONNECT_DATA=(SERVICE_NAME=db_instance_name)))'
    conn = cx_Oracle.connect(connstr)
    curs = conn.cursor()
    curs.arraysize=50
    try:
        curs.execute('SELECT * FROM TABLE_NAME')
        for row in curs:
            print row 
    except cx_Oracle.DatabaseError as e:
        error, = e.args
        print('Database connection error: %s' % format(e))
        print "Exception %r" % e 
        print error.code
        print error.message
        print error.context
        print error.offset
    finally:
        curs.close()
        conn.close()
    return True

oracle_connect_and_execute()

Communicating with MySQL using Python using MySQLdb

Recently I was supposed to port my Oracle stuff to mysql for obvious reasons and I wanted to use a few of the Oracle features in mysql like the ref cursors in oracle.  So in mysql we have dynamic results sets which I wanted to test.

I did not have a lot of difficulty connecting it but it was primarily getting dynamic result sets and processing them and using it in my application.

Using a trusted library (without major flaws) and then using it and later finding a flaw was the last thing I wanted and so I ended up using MySQLdb.

I did not try any other since it was easy to download and install it and start using it.

Everything started working out when I was having hard time with processing result sets dynamically.

I referred to the book MySQL Stored Procedure which helped me in getting exactly what I wanted.  Thanks to Guy Harrison and Steven Feuerstein.

Once I found how to retrieve the result sets, I was happy since I could see all the data I needed to see.

After a little while I realized that the data I was getting was not the exact data that mysql was sending.  MySQLdb was processing it and infact converting it into python datatypes.

So there were a lot of Nonetypes which I was not happy with.

Now I had to find out why and how these datatypes are converted and provide a solution to keep it as is.

And finally I found a solution which basically bypasses the conversion.

So here it goes..! I hope it helps the people looking for this solution.


import MySQLdb
from collections import OrderedDict
try:
    import MySQLdb.converters
except ImportError:
    _connarg('conv')

def connect(host='ronak.local', user='my_dev_1', passwd='my_dev_1', db='my_dev1', port=3306):

    try:
        orig_conv = MySQLdb.converters.conversions
        conv_iter = iter(orig_conv)
        convert = dict(zip(conv_iter, [str,] * len(orig_conv.keys())))
        print "Connecting host=%s user=%s db=%s port=%d" % (host, user, db, port)
        conn = MySQLdb.connect(host, user, passwd, db, port, conv=convert)
    except MySQLdb.Error, e:
        print "Error connecting %d: %s" % (e.args[0], e.args[1])
    return conn

def parse_data_and_description(cursor, data, rs_id):

    res = []
    cols = [d[0] for d in cursor.description]

    for i in data:
        res.append(OrderedDict(zip(cols, i)))
    return res
    rs_id=0;

def get_multiple_result_sets():
    conn = connect()
    cursor = conn.cursor( )
    final_list = []
    try:
        conn.autocommit(True)
        cursor.execute ("CALL %s%s" % (sp, args))
        while True:
            rs_id+=1

            data = cursor.fetchall( )
            listout = parse_data_and_description(cursor, data, rs_id)
            print listout
            if cursor.nextset( )==None:
                # This means no more recordsets available
                break
            print "\n"
            # Consolidate all the cursors in a single list
            final_list.append(listout)
        print final_list
    except MySQLdb.Error, e:
        # Lets rollback the transaction in case of an exception
        conn.rollback()
        print "Transaction aborted: %d: %s" % (e.args[0], e.args[1])
        cursor.close( )
        conn.close()
    else:
        # Commit the transaction in case of no failures/exceptions
        conn.commit()
        print "Transaction succeeded"
        cursor.close( )
        conn.close()

1

Simple hash function for characters

Simple Hash Function Implementation

The requirements for this hash function are

1. Values for each alpabet should remain the same for all conversions
2. abc should not be equal to cba or bac
3. Consider only lower case characters (not to complicate things)

def hashString(input):
    prim = 2049982463
    ret = 0
    for i in input:
        i = ord(i)
        print ("ret(%d) = (%d)*8 + (%d)" % (ret, ret, i))
        ret = ret*8 + i
    if ret > 0:
        return ret % prim
    else:
        return -ret % prim


def main():
    print hashString('and')
    print hashString('dan')
    print hashString('abc')
    print hashString('cba')


if __name__ == '__main__':
    main()

Output for the above source code would be as below

ret(0) = (0)*8 + (97)
ret(97) = (97)*8 + (110)
ret(886) = (886)*8 + (100)
7188
ret(0) = (0)*8 + (100)
ret(100) = (100)*8 + (97)
ret(897) = (897)*8 + (110)
7286
ret(0) = (0)*8 + (97)
ret(97) = (97)*8 + (98)
ret(874) = (874)*8 + (99)
7091
ret(0) = (0)*8 + (99)
ret(99) = (99)*8 + (98)
ret(890) = (890)*8 + (97)
7217

Another implementation for a simple hash function. I like better than the one above.


def simple_hash_function(hash):
    ret = 0
    for i in hash:
        print ("ret=(%d) = 31 * ret=(%d) + ord(i)=(%d)" % (ret, ret, ord(i)))
        ret = 31*ret + ord(i)
    return ret


def main():

    print simple_hash_function("abc")
    print simple_hash_function("cba")
    print simple_hash_function("bac")
    print simple_hash_function("dan")
    print simple_hash_function("and")


if __name__ == '__main__':
    main()

Output of the above code


ret=(0) = 31 * ret=(0) + ord(i)=(97)
ret=(97) = 31 * ret=(97) + ord(i)=(98)
ret=(3105) = 31 * ret=(3105) + ord(i)=(99)
96354
ret=(0) = 31 * ret=(0) + ord(i)=(99)
ret=(99) = 31 * ret=(99) + ord(i)=(98)
ret=(3167) = 31 * ret=(3167) + ord(i)=(97)
98274
ret=(0) = 31 * ret=(0) + ord(i)=(98)
ret=(98) = 31 * ret=(98) + ord(i)=(97)
ret=(3135) = 31 * ret=(3135) + ord(i)=(99)
97284
ret=(0) = 31 * ret=(0) + ord(i)=(100)
ret=(100) = 31 * ret=(100) + ord(i)=(97)
ret=(3197) = 31 * ret=(3197) + ord(i)=(110)
99217
ret=(0) = 31 * ret=(0) + ord(i)=(97)
ret=(97) = 31 * ret=(97) + ord(i)=(110)
ret=(3117) = 31 * ret=(3117) + ord(i)=(100)
96727

Extract values from a dictionary in python


def extract(dictin):

        dictout={}
        for key, value in dictin.iteritems():
            print('key:%s \t Value:%s' % (key,value))
            if isinstance(value, dict):
                extract(value, dictout)
            elif isinstance(value, list):
                for i in value:
                    extract(i,dictout)
            else:
                dictout[key] = value
        print('Dictout:%s' % dictout)
        return dictout

The above function looks for dictionaries inside of a value as well.
The resulting dictionary will be key values pairs only.
The input dictionary may have some values as dictionaries.
The function consolidates all the key value pairs in a single dictionary as output.

Convert hex color values to RGB and vice versa in python

I was working on something where I needed to convert the hex color values into a tuple of RGB.  Below is the python code that accomplishes the task.

>>> def hex_to_rgb(value):
...   value = value.lstrip('#')
...   lv = len(value)
...   return tuple(int(value[i:i+lv/3], 16) for i in range(0, lv, lv/3))
... 
>>> 
>>> hex_to_rgb("FFFFFF")
(255, 255, 255)
>>> def rgb_to_hex(rgb):
...   return '%02x%02x%02x' % rgb
... 
>>> rgb_to_hex((255, 255, 255))
'ffffff'
>>>

Installing git from source

First, lets resolve all the dependencies.  If these are installed, you probably would not be able to succeed.

$ yum install curl-devel expat-devel gettext-devel \ openssl-devel zlib-devel 
OR 
$ apt-get install libcurl4-gnutls-dev libexpat1-dev gettext \ libz-dev libssl-dev 

When you have all the necessary dependencies, you can go ahead and grab the latest snapshot from the Git web site:

http://git-scm.com/download 

Then, compile and install:

$ tar -zxf git-1.7.2.2.tar.gz $ cd git-1.7.2.2 $ make prefix=/usr/local all $ sudo make prefix=/usr/local install 

After this is done, you can also get Git via Git itself for updates:

$ git clone git://git.kernel.org/pub/scm/git/git.git 

Python Interview questions

Hi Everybody,

As always interview questions but this time I was lucky to be interviewed for Python, which I had recent experience on. And the interviewer was great. He told me that he had pretty much fundamental and short questions to ask and he joked about MS interviewees will be joyed with this kind of interview process.

This was basically a python position but this made me feel why people cannot do with other programming language experts for python because a python programmer is really way too different than a C++ or Java programmer. Since I am a C++ programmer with recent experience in Python, I was considered for the position. It was fun going through the interview since it was web conference coding interview.

I had tried searching for python questions over the internet but was not too lucky since I got only a few of them which made sense. Some of them were asked but restricting to just a couple of them. So I think my questions and answers will really help people to get a good idea on what can be asked for python interviews and also for people who are getting interviewed on python. I really appreciate comments and improvements on the questions and answers.

Hope the information helps…..

1. Name five modules that are included in python by default

2. Name a module that is not included in python by default

3. What is __init__.py used for?

4. When is pass used for?

5. What is a docstring?

6. What is list comprehension?

7. What is map?

8. What is the difference between a tuple and a list?

Ans. This is the most frequently asked question on python.

A tuple is a list that is immutable. A list is mutable i.e. The members can be changed and altered but a tuple is immutable i.e. the members cannot be changed.

Other significant difference is of the syntax. A list is defined as

>>> list1 = [1,2,5,8,5,3,]
>>> list2 = ["Sachin", "Ramesh", "Tendulkar"]

A tuple is defined in the following way

>>> tup1 = (1,4,2,4,6,7,8)
>>> tup2 = ("Sachin","Ramesh", "Tendulkar")

So the difference is in the type of brackets.

Coding questions

9. Using various python modules convert the list a to generate the output ‘one, two, three’

a = ['one', 'two', 'three']

<strong>ANSWER:</strong>
>>> a = ['one','two','three']
>>> ','.join(a)
'one,two,three'

10. What would the following code yield?


word = 'abcdefghij'
print word[:3] + word[3:]

Ans. This will print the word ‘abcdefghij’

11. Optimize these statements as a python programmer


word = 'word'
print word.__len__()

Ans.

>>> print 'word'.__len__()
4

12. Write a program to print all the contents of a file

Ans.

try:
  f1=open("filename.txt","r")
except Exception, e:
  print "%s" %e  

print f1.readlines() 

Ans2:

with open("tmp1.txt", "r") as f:
     f.readlines()</pre>

13. What will be the output of the following code

a = 1
a,b=a+1,a+1
print a
print b

Ans.
2
2
Here in the second line a,b=a+1,a+1 means that a=a+1 and b=a+1 which is 2. But this is the python way of initialization which a python programmer should understand.

14. Given the list below remove the repetition of an element. All the elements should be uniquewords = ['one', 'one', 'two', 'three', 'three', 'two']
Ans.

>>> set(uniquewords)
set(['three', 'two', 'one'])

15. Iterate over a list of words and use a dictionary to keep track of the frequency(count) of each word. for example

{'one':2,'two':2,'three':2}

Ans.

>>> l = ['one', 'one', 'two', 'three', 'three', 'two', 'one', 'one', 'two']
>>> d = {}
>>> for w in l:
...   d[w] = 1 + d.get(w,0)
...
>>> d
{'three': 2, 'two': 3, 'one': 4}

16.Write the following logic in Python:
If a list of words is empty, then let the user know it’s empty, otherwise let the user know it’s not empty.

Ans.

a=[]
if len(a):
print"The list is not empty"
else:
print"The list is empty"

17. Demonstrate the use of exception handling in python.

Ans.

a=[1,2,3,4]
try:
print a[5]
except Exception, e # This was important. Just do not say except: and print out something. It is
print e # Important to know what is the error
a=[1,2,3,4]
try:
...   print a[5]
... except Exception, e:
...   print "Error %s" % e
...
Error list index out of range

18. Print the length of each line in the file ‘file.txt’ not including any whitespaces at the end of the lines.
Ans.


>>> f1 = open("abc.py", 'r')
>>> i=0
>>> for line in iter(f1):
...   print "Length of line %d is %d" % (i, len(line.rstrip()))
...   i+=1
...
Length of line 0 is 11
Length of line 1 is 21

19. Print the sum of digits of numbers starting from 1 to 100
Ans.

print sum(range(1,101))

This is way too easy but just who know python. Since I am a C++ Programmer, I started writing a for loop to add up which was way too dumb. Hope you don’t make this mistake.

Python is known for it short syntax and easy to use functions.

20. Create a new list that converts the following list of number strings to a list of numbers.

num_strings = ['1','21','53','84','50','66','7','38','9']

Ans.

>>>num_strings = ['1','21','53','84','50','66','7','38','9']
>>>[int(j) for j in num_strings]
[1, 21, 53, 84, 50, 66, 7, 38, 9]

21. Create two new lists one with odd numbers and other with even numbers
num_strings = [1,21,53,84,50,66,7,38,9]
Ans.

>>>num_strings = [1,21,53,84,50,66,7,38,9]
>>>o, e = [], []
>>>[o.append(n) if n % 2 else e.append(n) for n in num_strings]
[None, None, None, None, None, None, None, None, None]
>>>o,e
([1, 21, 53, 7, 9], [84, 50, 66, 38])

>>> num_strings = [1,21,53,84,50,66,7,38,9]
>>> odd, even = filter(lambda x:x%2, num_strings), filter(lambda x: not x%2, num_strings)
>>> print odd,even
[1, 21, 53, 7, 9] [84, 50, 66, 38]

22. Write a program to sort the following intergers in list

>>> nums = [1,5,2,10,3,45,23,1,4,7,9]
>>> nums.sort() # This is the quickest sorting algorithm. This is the best possible way to sort.
>>> print nums

23. Write a for loop that prints all elements of a list and their position in the list.

abc = [4,7,3,2,5,9] 

Ans.

>>> abc = [4,7,3,2,5,9]
>>> for i, v in enumerate(abc):
...   print i,v
... 
0 4
1 7
2 3
3 2
4 5
5 9

24. The following code is supposed to remove numbers less than 5 from list n, but there is a bug. Fix the bug.

n = [1,2,5,10,3,100,9,24]

for e in n:
if e > 5: 
    n.remove(e)
print n

Ans. The output here will be
[2,3,5,10,100,9,24] which means the 1st and the 5th elements are removed.
It should be implemented as below.

>>> n = [1,2,5,10,3,100,9,24]
>>> nlist = filter(lambda x: x &lt;= 5, n)
>>> print nlist
[5, 10, 100, 9, 24]

list.remove(element) will remove the element, and shrink the list. If you are iterating over the same list at that time, and you wanted to go to next element, the next element may very well be the one after. Here is what is happening in the problem: The 0th element in the list is less than 5 and is removed, thus, making the list shorter by one element. The next iteration in the for loop goes to n[1], but n[0] now is 2, so the loop skips element 2 and doesn’t remove it. Same thing happens at 100, but it is ok to skip 100 as it is > 5

25. What will be the output of the following

def func(x,*y,**z):
print z

func(1,2,3)

Ans.
Here the output is :
{}
If I print all the variables, namely x, y and z it yields me this

1 (2,3) {}

* and ** have special usage in the function argument list. * implies that the argument is a list and ** implies that the argument is a dictionary. This allows functions to take arbitrary number of arguments (like your sum function that took range of numbers from 0 .. 100. Pretty cool, eh?

26. Write a program to swap two numbers.

a = 5
b = 9

def swap(c,d):
return d,c

swap(a,b)

This will print the swapped values of a and b

(9,5)

OR if this does not seem convincing,

a, b = 5, 10
t = a
a=b
b=t

print a,b
>>> a = 5
>>> b = 10
>>> a,b=b,a
>>> a
10
>>> b
5

27. What will be the output of the following code


class C(object):
def__init__(self):
self.x =1

c=C()
print c.x
print c.x
print c.x
print c.x

Ans.
All the outputs will be 1
1
1
1
1

28. What is wrong with the code

func([1,2,3]) # explicitly passing in a list
func()             # using a default empty list

def func(n = []):
#do something with n
print n

Ans. I tried running the code with my addition of printing the value of n in the function and found out the following result

func([1,2,3]) resulted in [1,2,3] while func() resulted in []

29. What all options will work?
a.

n = 1
print n++

b.

n = 1
print ++n

c.


n = 1
print n+=1

d.


int n = 1
print n = n+1

e.


n =1
n = n+1

From the above options I believe the following will work

b. and e.

There are some problems with a, c and d.

if you try running the code in a , it does not accept n++ but it accepts ++n

n+=1 is not accepted while in d the variable is preceded by an int which is not pythonically correct.

30. In Python function parameters are passed by value or by reference?

Ans. Please refer to this

31.Remove the whitespaces from the string.


s = 'aaa bbb ccc ddd eee'

Ans.


a = string.split(s)
print a
['aaa', 'bbb', 'ccc', 'ddd', 'eee'] # This is the output of print a
print string.join(a)
aaa bbb ccc ddd eee # This is the output of print string.join(a)

32. What does the below mean?


<pre>s = a + '[' + b + ':' + c + ']'</pre>

33. Optimize the below code


def append_s(words):
new_words=[]
for word in words:
new_words.append(word + 's')
return new_words
for word in append_s(['a','b','c']):
print word

The above code adds a trailing s after each element of the list. Is there a better way one can write the above script?

34. If given the first and last names of bunch of employees how would you store it and what datatype?

Ans. Either a dictionary or just a list with first and last names included in an element.