Show page

Differences

This shows you the differences between two versions of the page.

--- bd2:laboratoare:09 [2022/12/05 21:10]
alex.petrescu [Conectarea la BD Microsoft]
+++ — (current)
@@ Line 1: / Line 1: @@
-====== Laboratorul 09 - Conectare la o Baza de Date ======
-===== Conectare OracleDB din Python =====
-==== Configurare mediu de lucru ====
-=== Windows ===
-  - Descărcați și instalați managerul de pachete Miniconda pentru Python 3 de [[https://docs.conda.io/en/latest/miniconda.html | aici]].
-  - Deschideți programul Anaconda Prompt.
-  - Instalați pachetul cx_Oracle. <code>conda install -c anaconda cx_oracle</code>
-  - Descărcați Oracle Instant Client, urmând instrucțiunile de [[https://oracle.github.io/odpi/doc/installation.html#windows | aici]].
-=== Linux ===
-  - Instalați Python.
-  - Instalați pachetul cx_Oracle. <code>pip install cx_Oracle</code>
-  - Descărcați Oracle Instant Client, urmând instrucțiunile de [[https://oracle.github.io/odpi/doc/installation.html#linux | aici]].
-==== Pachet Oracle ====
-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.
-<code sql>
-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;
-/
-</code>
-Scriptul poate fi descărcat de {{:bd2:laboratoare:lab09_oracle_package_script.txt|aici}}.
-==== Conexiune din Python ====
-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:
-<code python>
-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
-</code>
-Această clasă va expune o metodă pentru inițializarea conexiunii către baza de date Oracle și o metoda pentru deconectare:
-<code python>
-    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)
-</code>
-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:
-<code python>
-    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)
-</code>
-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:
-<code python>
-if __name__ == "__main__":
-    oc = OracleConnection('localhost', 1521, 'ORCLCDB', 'system', 'parolaAiaPuternic4!')
-    oc.openConnection()
-    oc.exempluProcedura(10)
-    oc.exempluFunctie('SA_REP')
-    oc.closeConnection()
-</code>
-Dacă rulăm scriptul obținem:
-<code>
-Connection open!
-Jennifer Whalen 2003-09-17 Administration Assistant 4400 Administration
-SA_REP 10184.67 6000.0 12008.0
-Connection close!
-</code>
-Scriptul complet poate fi găsit {{:bd2:laboratoare:lab09_python_oracle_example.txt|aici}}.
-===== Conectare MongoDB din Python =====
-==== Configurare mediu de lucru ====
-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''
-==== Clasa de conectare ====
-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.
-<code python>
-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)
-</code>
-==== Utilizare ====
-Pentru următoarele exemple să se importe documentele din fișierul {{:bd2:laboratoare:bd2_mongo.txt|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:
-<code bash>
-mongoimport --db=BD --collection=documents --type=json --file=bd2_mongo.json
-</code>
-După ce creați baza de date, puneți următorii indecși pentur a funcționea exemplele
-<code javascript>
-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"})
-</code>
-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
-=== Interogarea bazei de date ===
-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''.
-<code python>
-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()
-</code>
-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''.
-<code python>
-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()
-</code>
-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''.
-<code python>
-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()
-</code>
-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''.
-<code python>
-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()
-</code>
-Codul se găsește {{:bd2:laboratoare:main.txt|main.py}}. Nu uitați să schimbați extensia.
-===== Exemplu Exercițiu Colocviu =====
-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:
-<code 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;
-/
-</code>
-Rezolvare Transact-SQL
-<code 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
-</code>
-===== PowerBI - Windows =====
-Pentru a descarca Microsoft PowerBI, pe Windows, accesati [[https://www.microsoft.com/en-us/download/details.aspx?id=58494 | 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".
-{{:bd2:laboratoare:getdata.jpg?400|}}
-==== Conectarea la BD Microsoft ====
-Pentru a selecta SQL Server ca baza de date vom selecta meniul **Database** > **SQL Server Database** > **Connect**.
-{{:bd2:laboratoare:sqlserverpbi.jpg?400|}}
-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.
-{{:bd2:laboratoare:sqlserverpbi2.jpg?400|}}
-Dupa ce apasam **OK** va trebui sa ne autentificam. Daca folosim exact datele din laborator vom folosi **SA**/**parolaAiaPuternic4!** la Database Credentials.
-{{:bd2:laboratoare:sqlserverpbi3.jpg?400|}}
-==== Conectarea la BD Oralce ====
-Inainte de a conecta PBI la o baza de date Oracle, trebuie sa instalam clientul. Detaliile complete se gasesc [[https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-connect-oracle-database#installing-the-oracle-client | aici]].
-=== Setup - TLDR ===
-  - Descarcam ODAC [[https://www.oracle.com/database/technologies/odac-downloads.html | x64]] sau [[https://www.oracle.com/database/technologies/dotnet-utilsoft-downloads.html | x32]]
-  - Dezarhivam folderul si deschidem un terminal cu drepturi elevate in el
-<code bash>
-.\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
-</code>

bd2/laboratoare/09.1670267435.txt.gz · Last modified: 2022/12/05 21:10 by alex.petrescu

Show page Old revisions

Media Manager Back to top