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(host=self.host, port=self.port, service_name=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, 'XEPDB1', '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.

Configurati mediul de lucru urmarind pasii de aici in functie de platforma pe care va aflati.

Pentru a testa conexiunea din Python la MSSQL vom folosi doua subprograme, o procedura si o functie.

Procedura va intoarce rezultatul unei interogari SELECT care preia numele complet, data angajarii, titlul jobului si venitul angajatilor din departamentul cu id-ul primit ca parametru.

Functia calculeaza numarul de subordonati ai unui manager cu id-ul primit ca parametru, care au salariul mai mic decat o valoare data ca parametru, care are valoarea default salariul managerului.

CREATE OR ALTER FUNCTION dbo.GET_SUBORDINATES_WITH_LOWER_SALARY
(@ManagerId INT, @Salary REAL = NULL)
RETURNS INT AS
BEGIN
    IF @Salary IS NULL
        SELECT @Salary = salary
        FROM employees
        WHERE employee_id = @ManagerId;
 
    DECLARE @result INT;
 
    SELECT @result = COUNT(*)
    FROM employees EMP
    LEFT OUTER JOIN employees MGR ON EMP.manager_id = MGR.employee_id
    WHERE EMP.manager_id = @ManagerId AND EMP.salary <= @Salary;
 
    RETURN @result;
END
GO
 
CREATE OR ALTER PROCEDURE dbo.exemplu_procedura
(@idDept INT) 
AS
    SELECT e.first_name + ' ' + e.last_name AS full_name, e.hire_date, j.job_title, e.salary * (1 + isnull(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;
RETURN 0 
GO

Pentru conexiunea din python vom crea o clasa MSSQLConnection cu urmatorul constructor:

import pyodbc 
 
class MSSQLConnection:
    def __init__(self, host, port, database, username, password):
        self.host = host
        self.port = port
        self.database = database
        self.username = username
        self.password = password

Vom defini apoi doua functii, open_connection si close_connection care vor deschide, respectiv inchide, conexiunea la baza de date.

    def openConnection(self):
        try:
            self.db = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};SERVER='+self.host+';DATABASE='+self.database+';ENCRYPT=yes;TrustServerCertificate=yes;UID='+self.username+';PWD='+ self.password)
            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)

Acum mai ramane de adaugat metode care sa apeleze subprogramele definite in baza de date mai sus:

    def exempluProcedura(self, idDept):
        try:
            cmd = "EXEC dbo.exemplu_procedura ?;"
            values = (idDept)
            self.cursor.execute(cmd, values) 
            for elem in self.cursor.fetchall():
                print(elem)
        except Exception as e:
            print(e)
 
    def exempluFunctie(self, manager_id, salary=None):
        try:
            cmd = """\
            SET NOCOUNT ON;
            DECLARE @RC int;
            EXEC @RC = dbo.GET_SUBORDINATES_WITH_LOWER_SALARY ?, ?;
            SELECT @RC AS rc;
            """
            values = (str(manager_id), (str(salary or "NULL"))
            self.cursor.execute(cmd, values)
            emp_count = self.cursor.fetchval()
            print('Managerul cu id-ul ' + str(manager_id) + ' are ' + str(emp_count) + ' angajati cu salariul mai mic decat ' + (str(salary) or 'el'))
        except Exception as e:
            print(e)

Aici sunt cateva lucruri de observat. Accesul la date in afara contextului de T-SQL necesita o instructiune de tip SELECT la final. Astfel, in cazul procedurii, care facea un singur SELECT, este de ajuns sa apelam procedura folosind exec numele_procedurii lista_parametri. Pentru functie, in schimb, va trebui sa rulam un bloc de instructiuni T-SQL care se termina in SELECT rezultat_apel_functie.

In ambele cazuri, cursorul va contine acum valorile dorite. Pentru a accesa valorile multiple intoarse de procedura putem folosi cursor.fetchall(), iar pentru functie putem folosi cursor.fetchval().

Vom si testa clasa nou creata cu o functie main:

if __name__ == "__main__":
    oc = MSSQLConnection('localhost', 1433, 'master', 'sa', 'parolaAiaPuternic4!')
    oc.openConnection()
    oc.exempluProcedura(10)
    oc.exempluFunctie(100, 20000)
    oc.closeConnection()

Dupa rulare putem vedea urmatorul output:

Connection open!
('Jennifer Whalen', datetime.date(2003, 9, 17), 'Administration Assistant', 4400.0)
Managerul cu id-ul 100 are 14 angajati cu salariul mai mic decat 20000
Connection close!

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.

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 SA@parolaAiaPuternic4! 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 (nu cel cu copy_, cel mare)
2. Rulam Setup-ul (avem grija sa fie bifat Configure ODP.NET nad/or Oracle Providers for ASP.NET at machine-wide level in sectiunea de ODP.NET)

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

Dupa care vom introduce datele de conectare. Daca folosim exact datele din laborator vom folosi localhost:1521.

Vom selecta modul Import.

Dupa ce apasam OK va trebui sa ne autentificam. Daca folosim exact datele din laborator vom folosi system@parolaAiaPuternic4 la Database Credentials.

• Get started with Power BI Desktop
• Sample App With Microsoft Tech