Python Programming Questions

1. Count the occurrence of the letter in the given string?

name = "ThulasiDhasan"
map = {}
for ch in name:
  map[ch] = map.get(ch, 0) + 1
print(map)

2. Flatten list of the list in Python

lst = [[2, 3, 5], [6, 7, 8], [9, 0, 1]]
t_lst = [ele for sublist in lst for ele in sublist]
print(t_lst)

Python project - Contactbook

Contact Book: Sample application to perform CRUD operations using Python, MySQL.

             MySQL
             Python 3.x

Libraries used :
           pymsql , beatutifultable

Installation of Libraries using pip
           pip install pymsql
           pip install beautifultable

This application performs basic CRUD orations to demonstrate Python end-to-end application.

Note: Update and delete methods were not called from main, you can give a try...

contact.py

from collections import namedtuple

Contact = namedtuple("Contact", ["cid","name", "email", "mobile"])

config.py

mysql = {
    'host': 'localhost',
    'user': 'root',
    'passwd': 'password',
    'db': 'counselor_db'}

util.py

import pymysql
import config

class DbUtil:

    @staticmethod
    def get_connection():
        try:
            con = pymysql.connect(config.mysql['host'], config.mysql['user'],config.mysql['passwd'],config.mysql['db'])
        except pymysql.MySQLError as error:
            print('Exception number: {}, value {s}'.format(error.args[0], error))
            raise
        else:
            return con

    @staticmethod
    def close_connection(conn):
        try:
            conn.close()
        except pymysql.MySQLError as error:
            print("While closing connection ...")
            print('Exception number: {}, value {!r}'.format(error.args[0], error))
            raise

    @staticmethod
    def close_cursor(cursor):
        try:
            cursor.close()
        except pymysql.MySQLError as error:
            print("While closing the cursor ...")
            print('Exception number: {}, value {!r}'.format(error.args[0], error))
            raise

dboperations.py

import config
from util import DbUtil
import pymysql
from contact import Contact


class ContactDbOperations:

    def add_contact(self, new_contact):
        try:
            connection = DbUtil.get_connection()
            with connection.cursor() as cursor:
                cursor.execute("insert into contact(name,email,mobile) values(%s,%s,%s)", (new_contact.name
                                                                        , new_contact.email
                                                                                            , new_contact.mobile))
            connection.commit()

        except pymysql.MySQLError as error:
            print("While inserting Data ...")
            print('Exception number: {}, value {!r}'.format(error.args[0], error))
        finally:
            DbUtil.close_connection(connection)
            DbUtil.close_cursor(cursor)

    def get_contact(self, cid):
        try:

            conn = DbUtil.get_connection()
            cursor = conn.cursor()
            cursor.execute("select  cid, name, email, mobile from contact where cid = %s", cid)
            contact = Contact(*cursor.fetchone())
            return  contact
        except pymysql.DatabaseError as error:
            print("While getting data from DB using id... ")
            print('Exception number: {}, value {!r}'.format(error.args[0], error))
        finally:
            DbUtil.close_connection(conn)
            DbUtil.close_cursor(cursor)

    def update_contact(self, contact):
        try:
            conn = DbUtil.get_connection()
            cursor = conn.cursor()
            cursor.execute("update contact set name = %s ,email = %s, mobile = %s where cid = %s", contact)
            conn.commit()

        except pymysql.DatabaseError as error:
            print("While updating ... ")
            print('Exception number: {}, value {!r}'.format(error.args[0], error))
        finally:
            DbUtil.close_connection(conn)
            DbUtil.close_cursor(cursor)

    def get_all_contacts(self):
        try:
            connection = DbUtil.get_connection()
            with connection.cursor() as cursor:
                cursor.execute("select cid, name, email, mobile from contact")
                rows = cursor.fetchall()
                contacts = self.get_list_data(rows)
                return contacts
        except Exception as error:
            print("While retrieving data ... ")
            print('Exception number: {}, value {!r}'.format(error.args[0], error))
        finally:
            if connection:
                DbUtil.close_connection(connection)
                DbUtil.close_cursor(cursor)

    def search_contact(self, search_str):
        try:
            connection = DbUtil.get_connection()
            with connection.cursor() as cursor:
                cursor.execute("select cid, name, email, mobile from contact where name like %s ", ('%' + search_str + '%'))
                rows = cursor.fetchall()
                contacts = self.get_list_data(rows)
                return contacts
        except Exception as error:
            print("While searching Data ...")
            print('Exception number: {}, value {!r}'.format(error.args[0], error))
        finally:
            DbUtil.close_connection(connection)
            DbUtil.close_cursor(cursor)

    @staticmethod
    def delete_contact(cid):
        try:
            connection = DbUtil.get_connection()
            cursor = connection.cursor()
            cursor.execute("delete from contact where cid = %s ", cid)
            connection.commit()

        except pymysql.DatabaseError as error:
            print("While deleting data ...")
            print('Exception number: {}, value {!r}'.format(error.args[0], error))
        finally:
            DbUtil.close_connection(connection)
            DbUtil.close_cursor(cursor)

    @staticmethod
    def get_list_data(rows):
        return [Contact(*row) for row in rows]

contactservice.py

from dboperations import ContactDbOperations
from contact import Contact
from beautifultable import BeautifulTable

class ContactService:

    def __init__(self):
        self.cs = ContactDbOperations()

    def add_contact(self):
        name = input("Name : ")
        email = input("Email : ")
        mobile = input("Mobile : ")
        contact = Contact(0, name, email, mobile)
        self.cs.add_contact(contact)
        self.show_all_contacts()

    def show_all_contacts(self):
        contacts = self.cs.get_all_contacts()
        ContactService.paint_data_list(contacts)

    def search_contact(self):
        name = input("Enter search name :")
        contacts = self.cs.search_contact(name)
        if contacts:
            ContactService.paint_data_list(contacts)
        else:
            print("No data found with search name :{}".format(name))

    @staticmethod
    def paint_data(data):
        table = BeautifulTable()
        table.column_headers = ["CID", "NAME", "EMAIL", "MOBILE"]
        table.append_row([data.cid, data.name, data.email, data.mobile])
        print(table)

    @staticmethod
    def paint_data_list(data):
        table = BeautifulTable()
        table.column_headers = ["CID", "NAME", "EMAIL", "MOBILE"]
        for li in data:
            table.append_row([li.cid, li.name, li.email, li.mobile])
        print(table)

main.py

if __name__ == '__main__':
    cs = ContactService()
    while True:
        print("*"*40)
        print("1. Add Contact 2. View All 3.Search 4. Exit")
        print("*"*40)
        choice = int(input("Enter your choice :"))
        if choice == 1:
            cs.add_contact()
        elif choice == 2:
            cs.show_all_contacts()
        elif choice == 3:
            cs.search_contact()
        elif choice == 4:
            exit()
        else:
            print("Enter only 1 to 4 ......")

MongoDB CRUD operations exercise

Installation of MongoDB

The executable file needs to be downloaded from:

https://www.mongodb.com/download-center?jmp=nav#community

Note: During installation of MongoDB, one of the wizards has a compass installation check box, please uncheck the same, and continue the installation process by clicking on “Next”.

·         Once installation is completed, create the directory “data/db” in the "C" drive.

·         Copy the MongoDB installation directory path (which looks like C:\Program Files\MongoDB\Server\3.6\bin) and add it to the environment variable of the system. 

•      To Run mongo client open command prompt and type mongo 

·         To verify the installation:

                 - Open the command prompt

                            Type the command mongod. If a message "Waiting for connections on port 27017" is                  displayed on the last line, it indicates the successful installation of MongoDB.

Installation of RoboMongo

·         Download the executable from the following link:

        https://robomongo.org/download    

·         Just follow the instructions of the installation wizard to complete installation of RoboMongo.

Working with MongoDB

The example given below covers all the basic (CRUD) operations in MongoDB.

1. Connect to MongoDB (by using mongo shell)
2. Create database with name (ems)   - use ems;
3.  Insert the following data into "faculty" collection (Use insertMany())

db.faculty.insrtMany([

            { "name":"Krish", "age":35,"gender":"M","exp":10,subjects:["DS","C","OS"],"type":"Full Time","qualification":"M.Tech" },

            { "name":"Manoj", "age":38,"gender":"M","exp":12,subjects:["JAVA","DBMS"],"type":"Full Time", "qualification":"Ph.D"},

            { "name":"Anush", "age":32,"gender":"F","exp":8,subjects:["C","CPP"],"type":"Part Time","qualification":"M.Tech" },

            { "name":"Suresh", "age":40,"gender":"M","exp":9,subjects:["JAVA","DBMS","NETWORKING"],"type":"Full Time", "qualification":"Ph.D"},

            { "name":"Rajesh", "age":35,"gender":"M","exp":7,subjects:["DS","C","OS"],"type":"Full Time","qualification":"M.Tech" },

            { "name":"Mani", "age":38,"gender":"F","exp":10,subjects:["JAVA","DBMS","OS"],"type":"Part Time", "qualification":"Ph.D"},

            { "name":"Sivani", "age":32,"gender":"F","exp":8,subjects:["C","CPP","MATHS"],"type":"Part Time","qualification":"M.Tech" },

            { "name":"Nagesh", "age":39,"gender":"M","exp":11,subjects:["JAVA","DBMS","NETWORKING"],"type":"Full Time", "qualification":"Ph.D"},

            { "name":"Nagesh", "age":35,"gender":"M","exp":9,subjects:["JAVA",".Net","NETWORKING"],"type":"Full Time", "qualification":"Ph.D"},

            { "name":"Latha", "age":40,"gender":"F","exp":13,subjects:["MATHS"],"type":"Full Time", "qualification":"Ph.D"}

]);

Write the following queries:

1. Get the details of all the faculty.

2. Get the count of all faculty members. 

3. Get all the faculty members whose qualification is “Ph.D”.

4. Get all the faculty members whose experience is between 8 to 12 years.

5. Get all the faculty members who teach “MATHS” or “NETWORKING”.

6. Get all the faculty members who teach “MATHS” and whose age is more than 30 years and qualification must be “Ph.D”.

7. Get all the faculty members who are working part-time or who teach “JAVA”.

8. Add the following new faculty members:

      { "name":"Suresh Babu", "age":55,"gender":"M","exp":25,subjects:["MATHS","DE"],"type":"Full Time", "qualification":"Ph.D"}

db.faculty.insert(

      { "name":"Suresh Babu", "age":55,"gender":"M","exp":25,subjects:["MATHS","DE"],"type":"Full Time", "qualification":"Ph.D"}

       );

9. Update the data of all faculty members by incrementing their age and exp by one year.

10. Update the faculty “Sivani” with the following data: update qualification to “Ph.D” and type to “Full Time”.

11. Update all faculty members who are teaching “MATHS” such that they should now also teach “PSK”.

12. Delete all faculty members whose age is more than 55 years.

13. Get only the name and qualification of all faculty members. 

14. Get the name, qualification and exp of all faculty members and display the same in ascending order of exp.

15. Sort the faculty details by their age (descending order) and get the details of the first five faculty members only. 

Solutions: 

1. db.faculty.find().pretty()

2. db.faculty.count()

3. db.faculty.find({qualification:'Ph.D'}).pretty()

4. db.faculty.find({$and:[{exp:{$gt:7}},{exp:{$lt:13}}]}).pretty()

5. db.faculty.find({subjects:{$in:["MATHS","NETWORKING"]}}).pretty()

6. db.faculty.find({$and:[{subjects:'MATHS'},{age:{$gt:29}},{qualification:'Ph.D'}]}).pretty()

7. db.faculty.find({$or:[{type:"Part Time"},{subjects:"JAVA"}]}).pretty()

8. db.faculty.insert( {"name" : "Suresh Babu", age":55, "gender":"M", "exp":25, subjects:["MATHS","DE"],"type":"Full Time", "qualification":"Ph.D"})

9. db.faculty.updateMany({},{$inc:{age:1,exp:1}})

10. db.faculty.updateMany({name:"Sivani"},{$set:{qualification:"Ph.D",type:"Full Time"}})

11. db.faculty.updateMany({subjects:"MATHS"},{$push:{subjects:"PSK"}})

12. db.faculty.deleteMany({age:{$gt:54}})

13. db.faculty.find({},{name:1,_id:0,qualification:1});

14. db.faculty.find({},{name:1,_id:0,qualification:1,exp:1}).sort({exp:-1}).pretty()

15. db.faculty.find({}).sort({age:-1}).limit(5).pretty()

Python copy content of file to another file

Python:

Copy the content of inputfile.txt to outputfile.txt:

with open("outputfile.txt","w") as fw,open("inputfile.txt") as f:
    for line in f:
        fw.write(line)

CRUD operations Python and MySQL

Contact Book APP:

This sample application is to demonstrate CRUD operations using Python and MySQL.

install pymysql from the following link:

        http://pymysql.readthedocs.io/en/latest/user/installation.html

--------------------------------------------------------------------------------------

from collections import  namedtuple
import pymysql
import db.config as config
from beautifultable import BeautifulTable

Contact = namedtuple("Contact", ["cid","name", "email", "mobile"])


class DbUtil:
    @staticmethod
    def get_connection():
        try:
            con = pymysql.connect(config.mysql['host'], config.mysql['user'],config.mysql['passwd'],config.mysql['db'])
        except pymysql.MySQLError as error:
            print('Exception number: {}, value {!r}'.format(error.args[0], error))
            raise
        else:
            return con

    @staticmethod
    def close_connection(conn):
        try:
            conn.close()
        except pymysql.MySQLError as error:
            print("While closing connection ...")
            print('Exception number: {}, value {!r}'.format(error.args[0], error))
            raise

    @staticmethod
    def close_cursor(cursor):
        try:
            cursor.close()
        except pymysql.MySQLError as error:
            print("While closing the cursor ...")
            print('Exception number: {}, value {!r}'.format(error.args[0], error))
            raise


class ContactService:

    def add_contact(self, new_contact):
        try:
            connection = DbUtil.get_connection()
            with connection.cursor() as cursor:
                cursor.execute("insert into contact(name,email,mobile) values(%s,%s,%s)", (new_contact.name
                                                                                           , new_contact.email
                                                                                           , new_contact.mobile))
            connection.commit()

        except pymysql.MySQLError as error:
            print("While inserting Data ...")
            print('Exception number: {}, value {!r}'.format(error.args[0], error))
        finally:
            DbUtil.close_connection(connection)
            DbUtil.close_cursor(cursor)

    def get_contact(self, cid):
        try:

            conn = DbUtil.get_connection()
            cursor = conn.cursor()
            cursor.execute("select  cid, name, email, mobile from contact where cid = %s", cid)
            contact = Contact(*cursor.fetchone())
            self.paint_data(contact)
        except pymysql.DatabaseError as error:
            print("While getting data from DB using id... ")
            print('Exception number: {}, value {!r}'.format(error.args[0], error))
        finally:
            DbUtil.close_connection(conn)
            DbUtil.close_cursor(cursor)

    def update_contact(self, contact):
        try:
            conn = DbUtil.get_connection()
            cursor = conn.cursor()
            cursor.execute("update contact set name = %s ,email = %s, mobile = %s where cid = %s", contact)
            conn.commit()

        except pymysql.DatabaseError as error:
            print("While updating ... ")
            print('Exception number: {}, value {!r}'.format(error.args[0], error))
        finally:
            DbUtil.close_connection(conn)
            DbUtil.close_cursor(cursor)

    def show_contacts(self):
        try:
            connection = DbUtil.get_connection()
            with connection.cursor() as cursor:
                cursor.execute("select cid, name, email, mobile from contact")
                rows = cursor.fetchall()

                contacts = self.get_list_data(rows)

                self.paint_data_list(contacts)
        except Exception as error:
            print("While retrieving data ... ")
            print('Exception number: {}, value {!r}'.format(error.args[0], error))
        finally:
            if connection:
               DbUtil.close_connection(connection)
               DbUtil.close_cursor(cursor)
    def search_contact(self, search_str):
        try:
            connection = DbUtil.get_connection()
            with connection.cursor() as cursor:
                cursor.execute("select cid, name, email, mobile from contact where name like %s ", ('%' + search_str + '%'))
                rows = cursor.fetchall()
                contacts = self.get_list_data(rows)
                print(contacts)
                self.paint_data_list(contacts)
        except Exception as error:
            print("While searching Data ...")
            print('Exception number: {}, value {!r}'.format(error.args[0], error))
        finally:
            DbUtil.close_connection(connection)
            DbUtil.close_cursor(cursor)

    def delete_contact(self, cid):
        try:
            connection = DbUtil.get_connection()
            cursor = connection.cursor()
            cursor.execute("delete from contact where cid = %s ", cid)
            connection.commit()

        except pymysql.DatabaseError as error:
            print("While deleting data ...")
            print('Exception number: {}, value {!r}'.format(error.args[0], error))
        finally:
            DbUtil.close_connection(connection)
            DbUtil.close_cursor(cursor)

    def get_list_data(self, rows):
        return [Contact(*row) for row in rows]

    def paint_data(self, data):
        table = BeautifulTable()
        table.column_headers = ["CID", "NAME", "EMAIL", "MOBILE"]
        table.append_row([data.cid, data.name, data.email, data.mobile])
        print(table)

    def paint_data_list(self, data):
        table = BeautifulTable()
        table.column_headers = ["CID", "NAME", "EMAIL", "MOBILE"]
        for li in data:
            table.append_row([li.cid, li.name, li.email, li.mobile])
        print(table)

MySQL sample data

Create the following 3 tables. Identify the primary key(s) and foreign key(s) as appropriate.

 

Table-1

 

CREATE TABLE DEPT

       (DEPTNO INT,

        DNAME VARCHAR(14),

        LOC VARCHAR(13) );

 

Insert the following data into the DEPT table (data is in the same sequence as the fields defined above):

 

10, 'ACCOUNTING', 'NEW YORK'

20, 'RESEARCH',   'DALLAS'

30, 'SALES',      'CHICAGO'

40, 'OPERATIONS', 'BOSTON'

Table-2

CREATE TABLE EMP(EMPNO NUMBER(4) NOT NULL,

        ENAME VARCHAR(10),

        JOB VARCHAR(9),

        MGR INT,

        HIREDATE DATE,

        SAL FLOAT,

        COMM FLOAT,

        DEPTNO INT REFERENCES DEPT(DEPTNO));

 

Insert the following data into the EMP table (data is in the same sequence as the fields defined above):

 

7369, 'SMITH',  'CLERK',     7902, '17-DEC-1980',        800,     NULL, 20

7499, 'ALLEN',  'SALESMAN',  7698, '20-FEB-1981',   1600, 300,       30

7521, 'WARD',   'SALESMAN',  7698, '22-FEB-1981', 1250, 500,       30

7566, 'JONES',  'MANAGER',   7839, '2-APR-1981',     2975, NULL, 20

7654, 'MARTIN', 'SALESMAN',  7698, '28-SEP-1981',  1250, 1400, 30

7698, 'BLAKE',  'MANAGER',   7839, '1-MAY-1981',    2850, NULL, 30

7782, 'CLARK',  'MANAGER',   7839, '9-JUN-1981',     2450, NULL, 10

7788, 'SCOTT',  'ANALYST',   7566, '09-DEC-1982',      3000, NULL, 20

7839, 'KING',   'PRESIDENT', NULL, '17-NOV-1981',    5000, NULL, 10

7844, 'TURNER', 'SALESMAN',  7698, '8-SEP-1981',    1500, NULL, 30

7876, 'ADAMS',  'CLERK',     7788, '12-JAN-1983',       1100, NULL, 20

7900, 'JAMES',  'CLERK',     7698, '3-DEC-1981',          950,     NULL, 30

7902, 'FORD',   'ANALYST',   7566, '3-DEC-1981',        3000, NULL, 20

7934, 'MILLER', 'CLERK',     7782, '23-JAN-1982',        1300, NULL, 10

Table-3

CREATE TABLE SALGRADE

        (GRADE INT,

         LOSAL INT,

         HISAL INT);

 

Insert the following data into the SALGRADE table (data is in the same sequence as the fields defined above):

 1, 700, 1200

2, 1201, 1400

3, 1401, 2000

4, 2001, 3000

5, 3001, 9999

  

Practice Questions:

 

1.     Write a query to display all the details of the employees who are working for department 10 or 20.

2.     Write a query to display the names of the employees whose salaries are 5000 or 3000 or 800. Write the query in 2 different ways.

3.     Write a query to display all the details of the employees whose names are:  ‘SCOTT’, ’BLAKE’, ’ADAMS’.

4.     Write a query to display the employees whose date of hire is one of the following: ’Dec 17, 1981’, ’3^rd Dec, 1981’, ’21^st Jan, 1981’.

5.     Write a query to display the details of the employees who are getting paid in the range 2000 – 5000. Write the query in two different ways.

6.     Write a query to display the details of all the employees who joined during the period ’Jan 01^st, 1981’ and ’31^st Aug, 1981’.

7.     Write a query to display all the details of the employees whose name begins with the letter ‘S’.

8.     Write a query to display all the details of the employees whose name is at least 4 characters long.

9.     Write a query to display names of the employees whose name contains the letter 'A'.

10.  Write a query to display names of the employees who have joined in the month where the name of the month starts with the letter 'A'.

11.  Write a query to display names of the employees whose date of joining is within the first 9 days of any calendar month.

12.  Write a query to display the names of the employees who are not receiving any commission.

13.  Write a query to display the names of the employees whose job title is ‘MANAGER’ and who are working for department 20.

14.  Write a query to display the details of employees, whose name starts with letter ‘J’ and whose salary is greater than 2500 and who work in the department 20.

15.  Write a query to display the details of employees, whose name starts with letter ‘J’ or whose salary is greater than 2500 or who work in the department 20.

16.  Write a query to display the details of all the employees whose job title is ‘MANAGER’ or ‘CLERK’.

17.  Write a query to display the employees whose salary is same as that of the employees ‘FORD’ or ‘JAMES’.

18.  Write a query to display the details of the employees who are working in the 'ACCOUNTING' department.

19.  Write a query to display the details of all the salesmen belonging to the sales department who are receiving commission.

20.  Write a query to display details of the all the managers having experience not less than 20 years and working in the location 'NEW YORK' or 'DALLAS' and working under some other manager.

21.  Write a query to display the salary grade of the employee 'FORD'.

22.  Display the details of Managers belonging to grades 4 or 5.

23.  Display the following data for all the employees: Employee number, employee name, salary, grade, work location.

24.  Display the following data for all the employees of the “Accounting” department: Employee number, employee name, salary, work location.

25.  Display all grade 4 and grade 5 MANAGERS of the following departments: ACCOUNTING, SALES. 

26.  Write a query to list the details of all the employees who belong to the department where 'ADAMS' is working.

27.  Display the details of all employees those who are senior (in terms of years of experience) to their own manager.

28.  Display the details of all the departments – if the department has any employee then display the employee's number and name as well.

29.  Display the details of employees whose department is located in 'NEW YORK' city.

30.  Display the details of employees whose salary is more than the salary of the employee ‘JAMES’.

31.  Display the details of all the employees whose job title is same as that of the employee ‘JAMES’.

32.  Display the details of all the employees who are senior to ‘JAMES’ (in terms of years of experience).

33.  Display the details of all the employees who are working in the same department as that of ‘SCOTT’; do not include the details of ‘SCOTT’.

34.  Display the details of the employees whose job titles are same as that of the employees of the ‘SALES’ department.

35.  Display the details of all grade 3 employees whose department is same as that of the employee ‘SCOTT’

36.  Display the details of all the employees working in the city of ‘NEW YORK’ or ‘DALLAS’ and whose job title is same as that of any grade 2 employee; however do not include the job title ‘SALESMAN’.

37.  Display the details of all the employees whose salary is more than that of employee ‘SMITH’ but less than that of employee ‘BLAKE’; however, it should not be equal to that of ‘SCOTT’ or that of ‘FORD’. 

38.  Display the details of all employees to whom other people are reporting.

39.  Display the details of all the employees who are working in the department where the manager of employee ‘BLAKE’ is working.

40.  Display the following details of all the employees: employee number, name, salary, department name, location. If the employee is not working in any department then display just the employee number, name, salary.

41.  Display the details of employees whose salaries are more than the salary of any employee belonging to department 10.

42.  Display the details of all the employees working in department 10 whose salary is more than the highest salary being given in department 20.

43.  Display the details of the nth highest paid employee. 

44.  Display the details of the highest paid employee from each department.

45.  Display the details of the senior-most employee of each department.