[0] Database management

Python provides a interface with the databases GadFly, mSQL, MySQL, PostgreSQL, Microsoft SQL Server 2000, Informix, Interbase, Oracle, and Sybase.

Example (MySQL):

pip install pymysql



In [ ]:

    
# Source: Manuel Torres. Universidad de Almería.

import pymysql

# Establecemos la conexion con la base de datos
bd = pymysql.connect("localhost", "root", "gebd", "RRHH")
 
# Preparamos el cursor que nos va a ayudar a realizar las operaciones con la base de datos
cursor = bd.cursor()
 
# Ejecutamos un query SQL usando el metodo execute() que nos proporciona el cursor
cursor.execute("SELECT * FROM Empleado")
 
resultado = cursor.fetchall()
 
# Extraemos una sola fila usando el metodo fetchone()
for fila in resultado:
    print("%s %s %s" % (fila[0], fila[1], fila[2]))
 
# Nos desconectamos de la base de datos
bd.close()



In [ ]:

    
resultado[0][2]

Exercise 1:

Create a list with the jobs of the third column of resultado. Tip: a list comprehension can be useful!



In [ ]:

    
all_jobs = [x[2] for x in resultado]

all_jobs = []
for i in resultado:
    all_jobs.append(i[2])
all_jobs

Exercise 2:

Compute a set of different jobs in resultado (this set will store some may items a different jobs are found in resultado). Clue: sets cannot store dupplicate elements!



In [ ]:

    
diff_jobs = set(all_jobs)
diff_jobs

Exercise 3:

Compute the number of occurrences of each job in resultado. This can be accomplished by generating a dictionary with a structure: {job:number_of_occurrences}. Tip: use the result of the previous exercise (a set of different jobs) to create the required dictionary.



In [ ]:

    
jobs_count = {}
for i in diff_jobs:
    jobs_count[i] = 0
jobs_count



In [ ]:

    
for i in all_jobs:
    jobs_count[i] += 1
jobs_count



In [ ]:

    
jobs_count = {}
for i in diff_jobs:
    jobs_count[i] = all_jobs.count(i)
jobs_count



In [ ]:

    
jobs_count['Analista']

Exercise 4:

Python dictionaries cannot be sorted (by definition). For this reason, create a list of tuples with a structure (number_of_ocurrences, job), using as input the result of the previous exercise.



In [ ]:

    
count_jobs = []
for i in jobs_count:
    count_jobs.append((jobs_count[i], i))
count_jobs

Exercise 5:

Sort (in reverse order) the previous list. Create a sorted list (in descending order) of tuples with a structure (number_of_ocurrences, job), using as input the result of the previous exercise. Tip: visit https://docs.python.org/3/howto/sorting.html#sortinghowto and find out how you can use the reverse=True parameter.



In [ ]:

    
sorted_count_jobs = sorted(count_jobs, reverse=True)
sorted_count_jobs



In [ ]:

    
sorted_count_jobs[1][1]

Exercise 6:

Compute the most common job(s) in resultado. Create a list of tuples with a structure (job, number_of_ocurrences) with the most common job(s), using as input the result of the previous exercise.



In [ ]:

    
i = 1
most_common_jobs = [(sorted_count_jobs[0][1], sorted_count_jobs[0][0])]
most_common_jobs



In [ ]:

    
while(sorted_count_jobs[i][0] == sorted_count_jobs[0][0]):
    x = (sorted_count_jobs[i][1], sorted_count_jobs[i][0])
    most_common_jobs.append(x)
    i += 1
most_common_jobs



In [ ]: