Laboratorium 2 z Baz Danych

Na drugim laboratorium nauczymy się trzech rzeczy:

Importowania bazy danych do mysql-a
Połączenia z bazą danych za pomocą programu OpenOffice
Połączenia z bazą danych za pomocą Pythona

Importowanie bazy w trybie wsadowym (Batch mode)

Ściągamy z githuba bazę employees.
Po rozpakowaniu ZIP-a, uruchamiamy konsolę (tak, od konsoli nie da się ucieć, nawet pod Windowsem…).
Poleceniem cd przechodzimy do katalogu, w którym rozpakowaliśmy ściagniętą bazę.
Importujemy ją do mysql
```
mysql -t < employees.sql
```
Prawdopodobnie dostaniemy błąd informujący nas o tym, że mysql nie jest rozpoznawanym poleceniem. Oznacza, to że komputery nie zostały skonfigurowane. Mamy dwie mozliwości

Dodać ścieżkę do folderu z wykonywalnym plikiem mysql.exe to zmiennej środowiskowej PATHS
Podać bezpośrednio ścieżkę do pliku "scieżka_do_pliku\mysql.exe" -t < employees.sql

Nadal nie działa. Co mówi błąd? Mamy złą nazwę użytkownika i brak hasło. Aby to naprawić, dodajemy “-u root”, czyli, że użytkownik(user) nazywa się root i “-p”, czyli, że będziemy uwierzytelniali się za pomoca hasła
```
"scieżka_do_pliku\mysql.exe" -t -u root -p < employees.sql
```

Możemy sprawdzić, czy poprawnie wczytaliśmy naszą bazę danych. Korzystamy z hashów

mysql -t < test_employees_sha.sql -u root -p
mysql -t < test_employees_md5.sql -u root -p

Ekslopracja bazy danych w DataGrip

Powtarzamy procedurę z zeszłego tygodnia. Wybieramy new source, typ bazy to mysql

Dane do połączenia

host:localhost
database: employees
user: nazwa_uzytkownika
password: hasło

Jak wygląda nasza baza danych? Możemy kliknąc prawym przyciskiem i wybrać Diagram, możemy też użyć skrótu klawiszowego Ctrl-Alt-Shift-U.

Mamy nazwy tabel, nazwy kolumn, typy kolumn, czy kolumna jest kluczem, jakie kolumny są ze sobą powiązane w różnych tabelach (czyli na czym możemy robić join).

Łączenie z bazą z OpenOffice/LibreOffice

Aby połaczyć się z bazą potrzebujemy wtyczki JDBC. Opis instalacji znajduje się w dokumentacji. Na komputerach w laboratorium ta wtyczka jest już skonfigurowana
Uruchamiamy OpenOffice Base i wybieramy ,,Połącz z istniejącą bazą danch" i MySQL.
Korzystamy z wtyczki JDBC
Użytkownik taki jak zawsze (do bazy lokalnej!). Ponieważ jest ustawione hasło, to zaznaczamy, że hasło jest potrzebne.
Po przetestowaniu idziemy dalej. Bazę chcemy zarejestrować w pakiecie, ponieważ dzięki temu bęziemy mieli do niej dostęp z poziomu arkusza kalkulacyjnego (OpenOffice Calc)

Z programu OpenOffice Base możemy wykonywać kwerendy, zapytania w SQL. Np.

SELECT first_name, last_name, hire_date FROM employees
WHERE hire_date BETWEEN '1985-11-18'  AND '1985-12-05'
ORDER BY hire_date DESC;

Możemy też wykorzystać nawiązane połaczenie z bazą danych z poziomu arkusza kalkulacyjnego
W Arkuszu (sic!) wybieramy Dane → Tabela przestawna → Utwórz, a następnie „Źródło danych zarejestrowane w pakiecie”.
Możemy teraz ściągnąc albo całą tabelę (Arkusze), albo wynik wykonania kwerendy, którą wcześniej zapisaliśmy

Łącznie z bazą danych z Pythona

Przede wszystkim dokumentacja https://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html

Wystarczy wpisać w google mysql python connection i wyjdzie mnóstwo rezultatów

Tworzymy plik pythonowy

import mysql.connector

cnx = mysql.connector.connect(user='root', password='',
                              host='127.0.0.1',
                              database='employees')
cnx.close()

Jeśli spróbujemy go uruchomic mamy brak biblioteki. Trzeba ją doinstalować. Na ubuntu/debianie korzystamy z menadżera pakietów

sudo apt-get install python3-mysql.connector

Teraz możemy wykonać kod z dokumentacji

import datetime
import mysql.connector

cnx = mysql.connector.connect(user='root', database='employees')
cursor = cnx.cursor()

query = ("SELECT first_name, last_name, hire_date FROM employees "
         "WHERE hire_date BETWEEN %s AND %s")

hire_start = datetime.date(1999, 1, 1)
hire_end = datetime.date(1999, 12, 31)

cursor.execute(query, (hire_start, hire_end))

for (first_name, last_name, hire_date) in cursor:
  print("{}, {} was hired on {:%d %b %Y}".format(
    last_name, first_name, hire_date))

cursor.close()
cnx.close()

Inna możliwość to połączenie za pomocą biblioteki SQLAlchemy i od razu wrzucenie danych do biblioteki dostarczającej standardową ramkę danych - Pandas. Szczegóły np. na stronie dr. Giniewicza.

Bonus

Jak wygląda skrypt, który uruchomiliśmy employess.sql?

Kilka ważnych elementów:

Najpierw czyścimy sobie przedpole. Usuwamy bazę jeśli taka istnieje, usuwamy wszystkie tabele.

DROP DATABASE IF EXISTS employees;
CREATE DATABASE IF NOT EXISTS employees;
USE employees;

DROP TABLE IF EXISTS dept_emp,
                     dept_manager,
                     titles,
                     salaries,
                     employees,
                     departments;

Tworzymy nową tabelę - szczegóły będziemy powoli przyswajać

CREATE TABLE departments (
    dept_no     CHAR(4)         NOT NULL,
    dept_name   VARCHAR(40)     NOT NULL,
    PRIMARY KEY (dept_no),
    UNIQUE  KEY (dept_name)
);

Poniżej po prostu trik, żeby mieć ładny komunikat dla użytkownika

SELECT 'LOADING departments' as 'INFO';

Ładowanie danych z innego pliku

source load_departments.dump ;

Ładujemy dane z pliku .dump

Co się w takim pliku znajduje? Zobaczmy jako przykład load_departments.dump

INSERT INTO `departments` VALUES
('d001','Marketing'),
('d002','Finance'),
('d003','Human Resources'),
('d004','Production'),
('d005','Development'),
('d006','Quality Management'),
('d007','Sales'),
('d008','Research'),
('d009','Customer Service');