Laboratorul 09 - Conectare la o Baza de Date

1. Descărcați și instalați managerul de pachete Miniconda pentru Python 3 de aici.
2. Deschideți programul Anaconda Prompt.
3. Instalați pachetul cx_Oracle.

   conda install -c anaconda cx_oracle

4. Descărcați Oracle Instant Client, urmând instrucțiunile de aici.

1. Instalați Python.
2. Instalați pachetul cx_Oracle.

   pip install cx_Oracle

3. Descărcați Oracle Instant Client, urmând instrucțiunile de aici.

Vom crea un pachet care expune o funcție și o procedură. Funcția va primi ca parametru id-ul unui job și va returna salariul mediu pentru acel job, expunând ca parametri de tip OUT salariul minim și salariul maxim pentru jobul respectiv. Procedura va primi ca parametru un id de departament și va expune ca parametri de tip OUT numele departamentului și un cursor ce va întoarce angajații acelui departament.

CREATE OR REPLACE package pck_example
AS
    PROCEDURE exemplu_procedura(idDept IN departments.department_id%TYPE, 
        numeDept OUT departments.department_name%TYPE, angajati OUT sys_refcursor);
    FUNCTION exemplu_functie(jobId IN jobs.job_id%TYPE, 
        salLow OUT jobs.min_salary%TYPE, salHigh OUT jobs.max_salary%TYPE) RETURN NUMBER;
END pck_example;
/
 
CREATE OR REPLACE package body pck_example
AS
    FUNCTION exemplu_functie(jobId IN jobs.job_id%TYPE, 
        salLow OUT jobs.min_salary%TYPE, salHigh OUT jobs.max_salary%TYPE) RETURN NUMBER
    IS
        avgVenit NUMBER;
    BEGIN
        SELECT DISTINCT min_salary, max_salary INTO salLow, salHigh FROM jobs WHERE job_id = jobId;
        SELECT round(avg(salary * (1 + nvl(commission_pct, 0))), 2) INTO avgVenit 
            FROM employees WHERE job_id = jobId;
        RETURN avgVenit;
    END exemplu_functie;
 
    PROCEDURE exemplu_procedura(idDept IN departments.department_id%TYPE, 
        numeDept OUT departments.department_name%TYPE, angajati OUT sys_refcursor)
    IS
    BEGIN
        SELECT department_name INTO numeDept FROM departments WHERE department_id = idDept;
        OPEN angajati FOR 
            SELECT e.first_name || ' ' || e.last_name, e.hire_date, j.job_title, e.salary * (1 + nvl(e.commission_pct, 0)) AS venit 
            FROM employees e INNER JOIN jobs j ON e.job_id = j.job_id WHERE e.department_id = idDept;
    END exemplu_procedura;
END pck_example;
/

Scriptul poate fi descărcat de aici.

Vom crea o clasă OracleConnection pentru a ne conecta la baza de date Oracle, salvând în constructor pe câmpuri ale clasei adresa bazei de date, numele utilizatorului și parola lui:

import cx_Oracle
 
class OracleConnection:
 
    def __init__(self, host, port, schema, username, password):
        self.host = host
        self.port = port
        self.schema = schema
        self.username = username
        self.password = password

Această clasă va expune o metodă pentru inițializarea conexiunii către baza de date Oracle și o metoda pentru deconectare:

    def openConnection(self):
        try:
            dsn_tns = cx_Oracle.makedsn(self.host, self.port, self.schema)
            self.db = cx_Oracle.connect(self.username, self.password, dsn_tns)
            self.cursor = self.db.cursor()
            print("Connection open!")
        except Exception as e:
            print("Connection not open!")
            print(e)
 
    def closeConnection(self):
        try:
            self.cursor.close()
            self.db.close()
            print("Connection close!")
        except Exception as e:
            print("Connection not closed!")
            print(e)

Urmează implementarea interacțiunii cu procedura și functia din pachetul definit mai sus. Pentru aceasta vom expune o metodă care primește ca parametru un id de departament pentru a apela procedura și o metodă care primește ca parametru id-ul unui job pentru a apela funcția:

    def exempluProcedura(self, idDept):
        try:
            numeDept = self.cursor.var(cx_Oracle.STRING)
            angajati = self.cursor.var(cx_Oracle.CURSOR)
            self.cursor.callproc("pck_example.exemplu_procedura", [idDept, numeDept, angajati]) 
            for elem in angajati.getvalue():
                print(elem[0], elem[1].strftime("%Y-%m-%d"), elem[2], elem[3], numeDept.getvalue())
        except Exception as e:
            print(e)
 
    def exempluFunctie(self, jobId):
        try:
            salLow = self.cursor.var(cx_Oracle.NUMBER)
            salHigh = self.cursor.var(cx_Oracle.NUMBER)
            avgVenit = self.cursor.callfunc("pck_example.exemplu_functie", cx_Oracle.NUMBER, [jobId, salLow, salHigh])
            print(jobId, avgVenit, salLow.getvalue(), salHigh.getvalue())
        except Exception as e:
            print(e)

Putem observa că valorile parametrilor întorși de procedură și de funcție pot fi accesate apelând metoda .getvalue(), iar pentru a itera peste un cursor este de ajuns sa iterăm peste valoarea parametrului de tip cursor, folosind o buclă for.

Vom adăuga și comportamentul de funcție main pentru script, unde vom deschide o conexiune către baza de date, apelând procedura cu id-ul de departament 10 și funcția cu id-ul de job 'SA_REP', la final închizând conexiunea:

if __name__ == "__main__":
    oc = OracleConnection('localhost', 1521, 'ORCLCDB', 'system', 'parolaAiaPuternic4!')
    oc.openConnection()
    oc.exempluProcedura(10)
    oc.exempluFunctie('SA_REP')
    oc.closeConnection()

Dacă rulăm scriptul obținem:

Connection open!
Jennifer Whalen 2003-09-17 Administration Assistant 4400 Administration
SA_REP 10184.67 6000.0 12008.0
Connection close!

Scriptul complet poate fi găsit aici.

Pentru a va conecta la MongoDB din python aveți nevoie de pachetul pymongo. Acesta se poate instala fie cu pip fie folosinf conda:

• pip install pymongo sau
• conda install pymongo

Pentru a vă conecta la baza de date, aveți nevoie de urmatorele informații:

• hostname numele sau adreasa de IP pe care se afla serverul de MongoDB
• port portul pe care asculta MongoDB
• dbname numele bazei de date la care doriti sa va conectatii

Toate aceste informații se găsesc în constructorul classei MongoDBConnector.

Pentru a vă conecta la instanța de mongo, folosiți client = pymongo.MongoClient(host, port). Pentru a vă baza de date, folosit client[dbname]. Aceste sunt implementate în metoda openConnection(…).

Pentru a interoga baza de date, se pot folosi funcțiile find_one() sau find(). Pentru a ușura munca, vom defini două metode: getRecords(…) și getRecord(…). Parametrul obligatoriu primit de aceste metode este collection care specifică colecția pe care să se facă căutare. Aceste metode primesc parametrii opționali:

• filter - pentru a pune condiții și a filtra înregistrările întoarse
• projection - pentru a spune ce câmpuri să se întoarcă.

Pentru a folosi Aggregation Pipeline, este definită metoda execAggreation(…) care primește doi parametrii:

• collection - coleția pe care să se facă agregarea
• pipeline - operațiile de agregare sub forma unei liste de dicționare.

Metoda execMapReduce(…) este folosită pentur a trimite către server intrucțiuni de tip MapReduce. primește următorii parametrii:

• collection - colecția pe care să se execute MapReduce
• mapper - funcția pentru Map
• reducer - funcția pentru Reduce
• out - colecția unde să se salveze rezultatul
• query (opțional) - cerere de filtrare a întregistrărilor înainte de să facă MapReduce.

import pymongo
 
class MongoDBConnector:
    def __init__(self, dbname, host='localhost', port=27017):
        self.host = host
        self.port = port
        self.dbname = dbname
 
    def openConnection(self):
        self.client = pymongo.MongoClient(host=self.host, port=self.port)
        self.db = self.client[self.dbname]
 
    def closeConection(self):
    	self.client.close()
 
    def getRecords(self, collection, filter=None, projection=None):
        if projection is None and not filter is None:
            return self.db[collection].find(filter=filter)
        elif not projection is None and filter is None:
            return self.db[collection].find(projection=projection)
        elif projection is None and filter is None:
            return self.db[collection].find()
        return self.db[collection].find(filter=filter, projection=projection)
 
    def getRecord(self, collection, filter=None, projection=None):
        if projection is None and not filter is None:
            return self.db[collection].find_one(filter=filter)
        elif not projection is None and filter is None:
            return self.db[collection].find_one(projection=projection)
        elif projection is None and filter is None:
            return self.db[collection].find_one()
        return self.db[collection].find_one(filter=filter, projection=projection)
 
    def execAggreation(self, collection, pipeline):
        return self.db[collection].aggregate(pipeline=pipeline)
 
    def execMapReduce(self, collection, mapper, reducer, out, query=None):
        self.db[out].drop()
        if query is None:
            return self.db[collection].map_reduce(mapper, reducer, out)
        return self.db[collection].map_reduce(mapper, reducer, out, query=query)

Pentru următoarele exemple să se importe documentele din fișierul bd2_mongo care conține baza de date în format JSON. Baza de date se va numi BD și colecția se va numi documents. Folosiți mongoimport pentru a popula baza de date:

mongoimport --db=BD --collection=documents --type=json --file=bd2_mongo.json

După ce creați baza de date, puneți următorii indecși pentur a funcționea exemplele

db.documents.createIndex({author: 1})
db.documents.createIndex({gender: 1, age: 1})
db.documents.createIndex({"words.word": 1})
db.documents.createIndex({"geoLocation": "2d"})
db.documents.createIndex({"lemmaText": "text"}) 
db.documents.createIndex({"date": "hashed"})

Se folosesc host-ul și portul default al bazei de date MongoDB. Nu uitați să imporați clasa MongoDBConnector sau să o puneți în același fișier

Următorul exeplu folosește folosește indexul de tip text pentru a găsi un document care are cuvântul “coffee” în interiorul șirului de caractere din câmpul lemmaText.

from bson.code import Code
 
if __name__ == '__main__':
    dbname = "BD"
    db = MongoDBConnector(dbname=dbname)
    collection = "documents"
 
    filter = { "$text": 
        { 
            "$search": "coffee", 
            "$language": "english", 
            "$caseSensitive": False, 
            "$diacriticSensitive": False 
        } 
    }
    projection = { "lemmaText": 1 }
    db.openConnection()
    print("Test 1")
    doc = db.getRecord(collection, filter, projection)
    print(doc)
    print("Test 2")
    doc = db.getRecord(collection, filter=filter)
    print(doc)
    print("Test 3")
    doc = db.getRecord(collection, projection=projection)
    print(doc)
    print("Test 4")
    doc = db.getRecord(collection)
    print(doc)
    db.closeConection()

Următorul exeplu găsește toate documentele care se află în aproierea coordonatelor [25,25]. Se folosește indexul de tip geoLocation pentru a folosi operatorul $near.

from bson.code import Code
 
if __name__ == '__main__':
    dbname = "BD"
    db = MongoDBConnector(dbname=dbname)
    collection = "documents"
 
    filter = { "geoLocation": { "$near": [25, 25] } }
    projection = {"geoLocation": 1, "lemmaText": 1 }
    db.openConnection()
    docs = db.getRecords(collection, filter, projection)
    for doc in docs:
        print(doc)
    db.closeConection()
 
 

Următorul exemplu foloseșete Aggregation Pipeline pentru a număra de câte ori apar un cuvânt în documentele care au ca autor un bărbat. Pipeline-ul este format din următoarele câmpuri:

• $match - este folosit pentru filtrare
• $project - folosit pentru proiecție. La cest pas se extrac cuvintele din câmpul $lemmaText. Cuvintele sunt salvate în vectorul words
• $unwind - este folosit pentru a despacheta vectorul words și a lua fiecare element din el care este folosit mai departe în $group
• $group - este folosti pentru a asigna ca “_id” un cuvânt din $words și a calcula numărul de apariții ale unui cuvânt “counts”: { “$sum”: 1 }

Filtrarea se face folosind q.

from bson.code import Code
 
if __name__ == '__main__':
    dbname = "BD"
    db = MongoDBConnector(dbname=dbname)
    collection = "documents"
 
    q = {"gender": "male"}
    pipeline = [
                    { "$match": q },
                    { "$project": { "words": { "$split": ["$lemmaText", " "]}}},
                    { "$unwind": "$words" },
                    { "$group": { "_id": "$words", "counts": { "$sum": 1 } } }
        ]
 
    db.openConnection()
    docs = db.execAggreation(collection, pipeline)
    for doc in docs:
        print(doc)
    db.closeConection()

Următorul exemplu foloseșete MapReduce pentru a număra de câte ori apar un cuvânt în documentele care au ca autor o femeie. Trebuie să scriem funcțiile map și reduce în limbnajul JavaScript. Funcția mapper ia textul care se găsește în câmpul lemmaText și extrage cuvintele. Apoi, pentru fiecare cuvânt se “emite' o pereche cheie-valoare care cuprinde cuvântul (cheia) și numărul de apariții initializat la 1 (valoare). Funcția reducer ia fiecare set cuvânt (key) și lista cu numărul de apariții (values) și însumează valorile. Rezultatele sunt salvate în colecția out_collection = “wordCounts”. Filtrarea se face folosind q.

from bson.code import Code
 
if __name__ == '__main__':
    dbname = "BD"
    db = MongoDBConnector(dbname=dbname)
    collection = "documents"
 
    mapper = Code("""
        function() {
            var tokens = this.lemmaText.split(" ");
            for (var idx=0; idx<tokens.length; idx++){
                emit(tokens[idx], 1);
            }
        }
        """)
 
    reducer = Code("""
        function(key, values) {
            return Array.sum(values);
        }
        """)
 
    q = {"gender": "female"}
    out_collection = "wordCounts"
    db.openConnection()
    docs = db.execMapReduce(collection, mapper, reducer, out_collection, q)
    for doc in docs.find():
        print(doc)
    db.closeConection()

Codul se găsește main.py. Nu uitați să schimbați extensia.

Să se scrie o procedura care selectează într-o variabilă de tip colecție/tabel următoarele informații folosind un cursor:

• nume șef format prin concatenarea prenumelui cu numele (un șef este o persoană al cărui employee_id se găsește pe coloana manager_id)
• venit șef (venit = salariu * (1 + puncte commision))
• numele țării unde se află departamentul în care lucrează șeful
• numărul de subalterni
• bonus = venit * procent de bonus

Să se folosească o funcție care calculeză un procent de bonus pentru fiecare șef astfel:

• dacă subalterni >= 6 și țara este United States atunci procentul de bonus este 15%
• dacă subalterni >= 6 și țara este United Kingdom atunci procentul de bonus este 20% din venit
• dacă subalterni < 6 și țara este United States atunci procentul de bonus este 10% din venit
• dacă subalterni < 6 și țara este United Kingdom atunci procentul de bonus este 15% din venit
• altfel procentul de bonus este 5%

Afișați informațiile selectate și bonusul pentru șefi.

Pentru rezolvare, puteți să folosiți fie PL/SQL fie Transact-SQL. Dacă folosiți PL/SQL încapsulați totul într-un pachet.

Rezolvare PL/SQL:

CREATE OR REPLACE package pck_bonus IS
  PROCEDURE afisare;
END pck_bonus;
/
 
CREATE OR REPLACE package body pck_bonus IS
  FUNCTION procent(eid employees.employee_id%TYPE, tara countries.country_name%TYPE, subalterni OUT NUMBER)
  RETURN NUMBER
  IS
  BEGIN
    SELECT COUNT(employee_id) INTO subalterni FROM employees WHERE manager_id = eid;
    CASE
      WHEN subalterni >= 6 AND tara = 'United States'  THEN RETURN 0.15;
      WHEN subalterni >= 6 AND tara = 'United Kingdom' THEN RETURN 0.20;
      WHEN subalterni <  6 AND tara = 'United States'  THEN RETURN 0.10;
      WHEN subalterni <  6 AND tara = 'United Kingdom' THEN RETURN 0.15;
      ELSE RETURN 0.05;
    END CASE;
  END procent;
 
  PROCEDURE afisare
  IS
    TYPE r IS record (
      nume varchar2(50),
      venit NUMBER(8,2),
      tara countries.country_name%TYPE,
      subalterni NUMBER(4),
      bonus NUMBER(8,2)
    );
    TYPE tbl IS TABLE OF r INDEX BY pls_integer;
    v tbl;
    idx NUMBER := 1;
  BEGIN
    FOR linie IN 
      (SELECT
          employee_id idemp,
        first_name||' '||last_name nume, 
        salary * (1 + nvl(commission_pct, 0)) venit, 
        department_id iddept 
      FROM employees 
      WHERE employee_id IN 
        (SELECT DISTINCT manager_id 
          FROM employees 
          WHERE manager_id IS NOT NULL))
    loop
      v(idx).nume := linie.nume;
      v(idx).venit := linie.venit;
      SELECT c.country_name INTO v(idx).tara 
      FROM departments d
      INNER JOIN locations l ON l.location_id = d.location_id
      INNER JOIN countries c ON c.country_id = l.country_id
      WHERE d.department_id = linie.iddept;
 
      v(idx).bonus := v(idx).venit * procent(linie.idemp, v(idx).tara, v(idx).subalterni);
 
      idx := idx + 1;
    END loop;
 
    FOR idx IN v.FIRST .. v.LAST
    loop
        dbms_output.put_line(v(idx).nume
            || ' ' ||v(idx).venit
            || ' ' ||v(idx).tara
            || ' ' ||v(idx).subalterni
            || ' ' ||v(idx).bonus
        );
    END loop;
  END afisare;
END pck_bonus;
/
 
 
BEGIN
  pck_bonus.afisare;
END;
/

Rezolvare Transact-SQL

CREATE OR ALTER FUNCTION procent(@tara VARCHAR(50), @subalterni INTEGER)
RETURNS FLOAT
AS
BEGIN
  DECLARE @bonus FLOAT;
  SET @bonus = CASE
      WHEN @subalterni >= 6 AND @tara = 'United States'  THEN 0.15
      WHEN @subalterni >= 6 AND @tara = 'United Kingdom' THEN 0.20
      WHEN @subalterni <  6 AND @tara = 'United States'  THEN 0.10
      WHEN @subalterni <  6 AND @tara = 'United Kingdom' THEN 0.15
      ELSE 0.05
    END;
  RETURN @bonus;
END;
GO
 
CREATE OR ALTER PROCEDURE AFISARE
AS
BEGIN
  DECLARE @tbl TABLE (
      nume VARCHAR(50)
      , venit FLOAT
      , tara VARCHAR(50)
      , subalterni INTEGER
      , bonus FLOAT
  );
  DECLARE 
      @idemp INTEGER
      , @iddept INTEGER
      , @nume VARCHAR(50)
      , @venit FLOAT
      , @tara VARCHAR(50)
      , @subalterni INTEGER
      , @bonus FLOAT;
  DECLARE DATA cursor FOR 
      SELECT 
          employee_id idemp
          , first_name + ' ' + last_name nume
          , salary * (1 + ISNULL(commission_pct, 0)) venit
          , department_id iddept
      FROM employees
      WHERE employee_id IN (SELECT DISTINCT manager_id FROM employees WHERE manager_id IS NOT NULL);
  OPEN DATA;
  FETCH NEXT FROM DATA INTO @idemp, @nume, @venit, @iddept;
  WHILE (@@FETCH_STATUS <> -1)
  BEGIN 
    SELECT @subalterni = COUNT(employee_id) FROM employees WHERE manager_id = @idemp;
 
    SELECT @tara = c.country_name 
    FROM departments d 
    INNER JOIN locations l ON l.location_id = d.location_id
    INNER JOIN countries c ON c.country_id = l.country_id
    WHERE d.department_id = @iddept;
 
    SET @bonus = @venit * dbo.procent(@tara, @subalterni);
 
    print @bonus;
 
    INSERT INTO @tbl VALUES(@nume, @venit, @tara, @subalterni, @bonus);
 
    FETCH NEXT FROM DATA INTO @idemp, @nume, @venit, @iddept;
  END;
  CLOSE DATA;
  DEALLOCATE DATA;
  SELECT * FROM @tbl;
END;
GO
 
EXEC dbo.afisare
GO

Pentru a descarca Microsoft PowerBI, pe Windows, accesati acest link. Dupa ce ati selectat versiunea pentru arhitectura voastra, vom deschide aplicatia si o vom conecta la bazele noastre de date (Oracle si Microsoft din Docker, dar principiul este acelasi si pentru unele hostate complet).

In toate scenariile, dupa deschiderea aplicatiei vom apasa pe butonul “Get Data”.

Pentru a selecta SQL Server ca baza de date vom selecta meniul Database > SQL Server Database > Connect.

Dupa care vom introduce datele de conectare. Daca folosim exact datele din laborator vom folosi localhost,1433 (port-ul in PBI este precizat cu virgula, nu cu:).

Vom selecta modul Import.

Dupa ce apasam OK va trebui sa ne autentificam. Daca folosim exact datele din laborator vom folosi SAparolaAiaPuternic4!** la Database Credentials.

Inainte de a conecta PBI la o baza de date Oracle, trebuie sa instalam clientul. Detaliile complete se gasesc aici.

1. Descarcam ODAC x64 sau x32
2. Dezarhivam folderul si deschidem un terminal cu drepturi elevate in el
3. Rulam urmatoarele comenzi:

.\install.bat all c:\oracle odac
 
C:\oracle\odp.net\bin\4\OraProvCfg.exe /action:gac /providerpath:C:\oracle\odp.net\bin\4\Oracle.DataAccess.dll
 
C:\oracle\odp.net\bin\4\OraProvCfg.exe /action:config /force /product:odp /frameworkversion:v4.0.30319 /providerpath:C:\oracle\odp.net\bin\4\Oracle.DataAccess.dll