~Nožka

Troška PosgreSQL

Obsah:

1. Úvodem
2. Reference
3. Instalace a správa
4. SQL konzola
5. Testovací databáze
6. Příklad několika SQL příkazů/dotazů
7. Postgre a Python
8. Pony ORM

Úvodem

SQL je standardizovaný dotazovací jazyk používaný pro práci s daty v relačních databázích. V relační databázi jsou data uložena v tabulkách, které jsou navzájem v nějakém vztahu -- relaci.

Přístup k databázi je realizován softwarem označovaným jako Systém řízení báze dat. Jednotlivé systémy řízení báze dat se liší. My budeme používat PostgreSQL, ale za zmínku stojí i MySQL, Firebird nebo SQLite.

Reference

• Hezký český tutoriál: http://www.sallyx.org/sally/psql/
• PostgeSQL na Linuxsoftu
• PostgreSQL
• PostgreSQL
• PostgreSQL datové typy
• PostgreSQL na Wikipédii
• http://phppgadmin.sourceforge.net/
• Adminer
• http://www.adminer.org/

Instalace a správa

Návod:

http://www.sallyx.org/sally/psql/install.php

http://www.sallyx.org/sally/psql/psql1.php

http://www.sallyx.org/sally/psql/psql2.php

# aptitude install postgresql

Správa se provádí pomocí uživatelského účtu postgres. Je potřeba se přihlásit jako root potom z roota se přihlásit jako postgres.

$ su - root
Heslo: ********
# su - postgres 
postgres@$ createuser --createdb marek
postgres@$ createuser --createdb --no-createrole --no-superuser vasek
postgres@$ createuser --no-createdb --no-createrole --no-superuser usr12345

nebo

$ su - root
Heslo: ********
# su - postgres
$ psql

=> CREATE USER marek WITH CREATEDB;
CREATE ROLE

-- nebo jen
=> CREATE USER marek;
CREATE ROLE

Postgres umí propojit systémové a databázové účty, takže se hodí, aby se uživatel v Postgres jmenoval stejně jako v sytému. Při spuštění SQL konzoly potom nemusíte zadávat heslo.

SQL konzola

SQL konzola se spouští příkazem psql. Pokud neřeknete jinak budete použit uživatel a databáze stejného jména jako právě přihlášený uživatel.

$ psql
Null je zobrazován jako '"NULL"'.
psql (9.4.0)
Pro získání nápovědy napište "help".

[marek@[local]:marek=> 

Tyto údaje je ale možné také zadat na příkazovém řádku:

$ psql -h lab.spseol.cz -d lididb -U user12345
Heslo pro uživatele user12345: *********
Null je zobrazován jako '"NULL"'.
psql (9.4.0)
Pro získání nápovědy napište "help".

[user12345@lab.spseol.cz]:lididb=> 

Meta-příkazy SQL konzoly:

http://www.sallyx.org/sally/psql/psql2.php#meta

Občas se najde někdo líný, koho nebaví zadávat hesla. Pro tyto jedince se hodí soubor ~/.pgpass

Může vypadat třeba takto:

# ~/.pgpass
# hostname:port:database:username:password 
#
*:*:lididb:marek:superTAJNEheslo
localhost:*:*:*:toJEjedno

Souboru je nutné nastavit přístupová práva:

$ chmod 600 ~/.pgpass

Testovací databáze

Vytvořil jsem malou testovací databázi. Uchovávají se v ní informace o lidech, jejich zaměstnáních a jejich koníčcích. Předpokládám, že máte vytvořenou nějakou databázi. Já jsem ji nazval lididb. Pro rychlý a pohodlný import mnou vytvořených tabulek můžete vložit do příkazové řádky:

$ curl http://hroch.spseol.cz/~nozka/site/postgre/lididb.sql | psql

případně:

$ curl http://hroch.spseol.cz/~nozka/site/postgre/lididb.sql | psql lididb

Zálohu databáze lze vytvořit pomocí příkazu pg_dump

$ pg_dump -O lididb > lididb.sql

| navigace |

Příklad několika SQL příkazů/dotazů

Vytvoření tabulky

• První tabulka
• Sloupcová omezení

CREATE TABLE "osoba" (
  "id" SERIAL PRIMARY KEY,
  "jmeno" VARCHAR(50) NOT NULL,
  "rok_narozeni" INTEGER NOT NULL CHECK (rok_narozeni > 1900),
  "poznamka" VARCHAR(250),
  "zamestnani" INTEGER
);

CREATE TABLE "konicek" (
  "id" SERIAL PRIMARY KEY,
  "nazev" VARCHAR(30) UNIQUE NOT NULL
);

CREATE TABLE "zamestnani" (
  "id" SERIAL PRIMARY KEY,
  "nazev" VARCHAR(30) UNIQUE NOT NULL
);

CREATE TABLE "konicek_osoby" (
  "id" SERIAL PRIMARY KEY,
  "osoba" INTEGER NOT NULL,
  "konicek" INTEGER NOT NULL
);

-------------------------------------------------------------

ALTER TABLE tabulka ADD PRIMARY KEY (id);
ALTER TABLE tabulka ADD UNIQUE (jmeno);

ALTER TABLE tabulka ALTER jmeno SET NOT NULL ;

ALTER TABLE tabulka DROP COLUMN sloupec;
ALTER TABLE tabulka ADD navrat_dluhu DATE DEFAULT current_date+10;

`--> stáhnout

Vkládání

• Úprava tabulky

INSERT INTO zamestnani (id, nazev) VALUES (19,'stolář');
INSERT INTO zamestnani VALUES (20,'lyžař');
INSERT INTO zamestnani (nazev) VALUES ('psycholog');

INSERT INTO konicek (nazev) VALUES ('rybaření');


INSERT INTO osoba (jmeno, rok_narozeni) VALUES ('Bedřich', 1994);
INSERT INTO osoba (jmeno, rok_narozeni, zamestnani) 
    VALUES ('Viktorie', 2006, 3);
INSERT INTO osoba VALUES (37, 'Zdislav', 2001, 'šikulka', NULL);

`--> stáhnout

Změna

• Úprava tabulky

-- můžu upravit jen jeden řádek
UPDATE zamestnani SET nazev = 'bojovník s ohněm'
    WHERE nazev = 'hasič';

-- nebo víc řádků najednou
UPDATE osoba SET data = 'nic o něm nevím' 
    WHERE data IS NULL;

UPDATE blog SET 
  jmeno = 'O ničem', 
  text_zapisu = 'bla bla bla'
WHERE id = 7 ;

`--> stáhnout

Smazání

• Úprava tabulky

DELETE FROM osoba WHERE poznamka IS NULL;
DELETE FROM zamestnani WHERE nazev='učitel'
DELETE FROM zamestnani WHERE rok_narozeni > 1999;

`--> stáhnout

Výběr

• SELECT a VIEW
• SELECT
• Postgre Functions and Operators

-- tohle používat jen vyjímečně
SELECT * FROM osoba;

SELECT jmeno, poznamka FROM osoba;

SELECT jmeno, rok_narozeni
  FROM osoba 
  WHERE rok_narozeni < 1980 
  ORDER BY rok_narozeni DESC  /* DESC říká, že se bude řadit sestupně*/
                              /* ASC by bylo vzestupně */
;

-- V dotazu lze rok narození přepočítat na věk
SELECT jmeno, DATE_PART('year', CURRENT_DATE)-rok_narozeni AS vek
  FROM osoba  
  WHERE rok_narozeni >1980  ORDER BY vek ASC
;

SELECT jmeno, DATE_PART('year', CURRENT_DATE)-rok_narozeni AS vek
  FROM osoba  
  WHERE DATE_PART('year', CURRENT_DATE)-rok_narozeni < 20 
;

`--> stáhnout

Spojování více tabulek

• SELECT a VIEW
• SELECT

-- Vypíše všechny kuchaře
SELECT osoba.jmeno, zamestnani.nazev FROM osoba, zamestnani 
  WHERE osoba.zamestnani = zamestnani.id
      AND 
      zamestnani.nazev = 'uhlobaron'
;

-- Seřadí osoby podle jejich zaměstnání
/* pomocí AS lze přejmenovat sloupec */
SELECT osoba.jmeno, zamestnani.nazev AS zamestnani
FROM osoba, zamestnani 
  WHERE osoba.zamestnani = zamestnani.id 
ORDER BY zamestnani;      


-- Všichni kuchaři narozní po a v roce 1990
SELECT osoba.jmeno, osoba.rok_narozeni AS 'rok narození'
FROM osoba JOIN zamestnani ON osoba.id_zamestnani = zamestnani.id_zamestnani
WHERE nazev_zamestnani = 'kuchař' 
         AND 
      osoba.rok_narozeni >=1990;


-- můžeme použít zkratky
SELECT O.jmeno, O.rok_narozeni AS "rok narození"
FROM osoba O, zamestnani Z  
WHERE O.zamestnani = Z.id
        AND 
      Z.nazev = 'uhlobaron'     
        AND 
      O.rok_narozeni <= 1980
;

/* ************************ */

-- vypíše zaměstnání a osoby, kteřé je vykonávají
SELECT zamestnani.nazev, jmeno 
FROM osoba JOIN zamestnani ON osoba.zamestnani = zamestnani.id 
  ORDER BY nazev
;

-- ... vypíše i osoby bez zaměstnání
SELECT zamestnani.nazev, osoba.jmeno 
FROM osoba LEFT JOIN zamestnani ON  osoba.zamestnani = zamestnani.id 
  ORDER BY zamestnani;


-- ... vypíše i zaměstnání, která nemají přiřazeno žádného člověka
SELECT zamestnani.nazev, osoba.jmeno 
FROM osoba RIGHT JOIN zamestnani ON  osoba.zamestnani = zamestnani.id 
  ORDER BY zamestnani;


/* ************************ */

-- osoby a jejich koníčky
SELECT osoba.jmeno, konicek.nazev 
FROM konicek_osoby
    JOIN konicek ON konicek_osoby.konicek = konicek.id
    JOIN osoba   ON konicek_osoby.osoba = osoba.id
ORDER BY konicek.nazev;

SELECT osoba.jmeno, konicek.nazev 
FROM konicek_osoby
    JOIN konicek ON konicek_osoby.konicek = konicek.id
    JOIN osoba   ON konicek_osoby.osoba = osoba.id
ORDER BY osoba.jmeno;

-- všichni, co milují omalovánky
SELECT osoba.jmeno, konicek.nazev 
FROM konicek_osoby
    JOIN konicek ON konicek_osoby.konicek = konicek.id
    JOIN osoba   ON konicek_osoby.osoba = osoba.id
WHERE konicek.nazev = 'omalovánky'
;

`--> stáhnout

| navigace |

Postgre a Python

• python psycopg2
• Psycopg – PostgreSQL database adapter for Python

Pro Python existuje více modulů, které komunikují s PostgreSQL. My se budeme zabývat psycopg.

# aptitude install python-psycopg2

import psycopg2

`--> stáhnout

K databázi se nejprve připojíme:

conn = psycopg2.connect(host='localhost',
                        user='franta',
                        password='tojetajny',
                        database='lididb')

`--> stáhnout

nebo

DB= "host='localhost' user='franta' password='tojetajny' dbname='lididb'"
conn = psycopg2.connect(DB)

`--> stáhnout

Vytvoříme objekt kurzor:

cursor = conn.cursor()

`--> stáhnout

Spuštění SQL příkazů se děje pomocí metody cursor.execute(). Knihovna podporuje transakce. Transakce se ukončí metodou conn.commit(). Pokud se během transakce něco pokazí vše se dá vrátit zpět pomocí conn.rollback().

cursor.execute("INSERT INTO konicek (nazev) VALUES('střelba z luku');")
conn.commit()  # nebo nastavím conn.autocommit na True

`--> stáhnout

Celá transakce se dá jednoduše zapouzdřit pomocí správce kontextu výrazem with.

with psycopg2.connect(DB) as conn:
    with conn.cursor() as curs:
        curs.execute("INSERT INTO zamestnani (nazev) VALUES ('blabla');")
        curs.execute("INSERT INTO konicek (nazev) VALUES ('horolezeni');")

`--> stáhnout

nebo o něco přehledněji

conn = psycopg2.connect(host='localhost',
                        user='franta',
                        password='tojetajny',
                        database='lididb')
with conn, conn.cursor() as curs:
    curs.execute("INSERT INTO zamestnani (nazev) VALUES ('blabla');")
    curs.execute("INSERT INTO konicek (nazev) VALUES ('horolezeni');")

`--> stáhnout

Automatické ukončení transakce (tedy vlastně vypnutí transakcí) lze zařídit pomocí conn.autocommit=True.

Select

cursor.execute('SELECT * FROM osoba;')

`--> stáhnout

Data si můžeme vybrat postupně pomocí metody .fetchone():

radek = cursor.fetchone()

`--> stáhnout

Každé další zavolání metody .fetchone() vydá další řádek výsledku SQL dotazu. Po vydání posledního řádku vrací metoda .fetchone() už jen None. Seznam vše řádků vrátí metoda .fetchall() a zadaný počet řádků metoda .fetchmany(pocet).

Objekt .fetchall je iteratovatelný, proto lze použít něco jako:

for radek in curs.fetchall():
    print radek

`--> stáhnout

Nakonec se od databáze odpojíme:

cursor.close()
db.close()

`--> stáhnout

Parametry v SQL dotazech

Nikdy, nikdy, nikdy bychom neměli dělat toto:

>>> SQL = "INSERT INTO osoba (jmeno) VALUES ('%s');" # nikdy, nikdy nikdy
>>> data = ("O'Reilly", )
>>> cur.execute(SQL % data) # tady bude chyba
ProgrammingError: syntax error at or near "Reilly"
LINE 1: INSERT INTO authors (name) VALUES ('O'Reilly')

`--> stáhnout

...,protože jak jste si všimly jsou v dotazu 3 apostrofy a to je chyba. Znak apostrof je třeba escapovat. Navíc pokud přijímáme data od uživatele můžete se vystavit útoku zvanému SQL injection.

Pro tyto případy je metoda exicute vybavena druhým parametrem. Zde umístíme tuple (nebo list) s parametry.

>>> SQL = "INSERT INTO osoba (jmeno) VALUES (%s);" # Pozor žádné apostrofy
>>> data = ("O'Reilly", )
>>> cur.execute(SQL, data) #  žádné proceto

>>> SQL = "INSERT INTO osoba (jmeno, rok_narozeni) VALUES (%s, %s);"
>>> cur.execute(SQL, ("Bedřich", 1994)) 

`--> stáhnout

Příklad

#!/usr/bin/python
# -*- coding: utf8 -*-
from __future__ import division, print_function, unicode_literals

import psycopg2

conn = psycopg2.connect(host='localhost',
                        user='marek',
                        password='tojetajny',
                        database='lididb')

try:
    with conn, conn.cursor() as curs:
        curs.execute("INSERT INTO zamestnani (nazev) "
                     "VALUES ('horolezec');")
        curs.execute("INSERT INTO zamestnani (nazev) "
                     "VALUES ('novinář');")
except:
    print("Chyba integrity dat.")

"Sem vložím další kód......"
"Sem vložím další kód......"

try:
    with conn, conn.cursor() as curs:
        curs.execute("INSERT INTO zamestnani (nazev) VALUES "
                     "('tohle se nepřidá')")
        curs.execute("INSERT INTO zamestnani (nazev) VALUES ('novinář')")
except:
    print("Chyba ...")

conn.autocommit = True
curs = conn.cursor()
curs.execute('SELECT jmeno, poznamka FROM osoba WHERE rok_narozeni < 1980;')

print(*curs.fetchone())
print(*curs.fetchone())
print(*curs.fetchone())

for jmeno, poznamka in curs.fetchall():
    print("%10s  %15s" % (jmeno, poznamka))


conn.close()

`--> stáhnout

| navigace |

Pony ORM

Pony je Python knihovna, která provádí tzv. objektově relační mapování. Celý přístup k databázi je potom mnohem jednodušší, protože se není nutné psát SQL, ale vystačíme si většinou s Pythonem.

Další výhodou Pony je, že nabízí jednoduchý ER diagram editor, pomocí kterého lze provést celý návrh a nechat si Python nebo SQL kód automaticky vygenerovat.

• lididb

Vše je hezky popsáno v dokumentaci uvedu zde jen malý příklad.

Malý modul s definicí databáze

# -*- coding: utf8 -*-

from pony.orm import (Database, PrimaryKey, Required, Optional, Set,
                      sql_debug)

db = Database("postgres",
              host="localhost",
              user="marek",
              password="tojetajny",
              database="lididb")


class Osoba(db.Entity):
    _table_ = "osoba"
    id = PrimaryKey(int, auto=True)
    jmeno = Required(unicode, 50)
    rok_narozeni = Required(int)
    poznamka = Optional(unicode, 250, nullable=True)
    zamestnani = Optional("Zamestnani")
    konicky__osoby = Set("Konicek_Osoby")


class Zamestnani(db.Entity):
    _table_ = "zamestnani"
    id = PrimaryKey(int, auto=True)
    nazev = Required(unicode, 30, unique=True)
    osoby = Set(Osoba)


class Konicek(db.Entity):
    _table_ = "konicek"
    id = PrimaryKey(int, auto=True)
    nazev = Required(unicode, 30, unique=True)
    konicky__osoby = Set("Konicek_Osoby")


class Konicek_Osoby(db.Entity):
    _table_ = "konicek_osoby"
    id = PrimaryKey(int, auto=True)
    osoba = Required(Osoba)
    konicek = Required(Konicek)


sql_debug(True)
db.generate_mapping(create_tables=True)

`--> stáhnout

>>> import lididb
>>> from pony.orm import select, get, commit, delete, update

# nový záznam
>>> slon = lididb.Osoba(jmeno="Slon", rok_narozeni=1963)
>>> zdislav = lididb.Osoba(jmeno="Zdislav", rok_narozeni=1979)
>>> commit()

# SQL dotaz
>>> dotaz = select(o for o in lididb.Osoba if o.rok_narozeni < 1970)
>>> dotaz[:].show()

id|jmeno|rok_narozeni|poznamka|zamestnani
--+-----+------------+--------+----------
39|Slon |1963        |None    |None      
41|Kal  |1961        |None    |None      
43|Nos  |1969        |To je on|None      

>>> for o in dotaz:
...:     print o.jmeno, o.rok_narozeni
...: 
Slon 1963
Kal 1961
Nos 1969


>>> zamesnani_osob = select((o.jmeno, o.rok_narozeni, o.zamestnani.nazev)
                            for o in lididb.Osoba 
                            if o.zamestnani.nazev == 'programátor')

>>> zamesnani_osob[:].show()
o.jmeno|o.rok_narozeni|o.zamestnani.nazev
-------+--------------+------------------
Helena |1976          |programátor       
Petr   |1988          |programátor       
Martin |1988          |programátor       
Karel  |1983          |programátor       
Libor  |1992          |programátor       


>>> lididb.Osoba[43].delete()

>>> slon = get(o for o in lididb.Osoba if o.jmeno == 'Slon')
>>> print slon.id, slon.jmeno, slon.rok_narozeni
39 Slon 1963

>>> slon.jmeno = 'Mamut'

>>> commit()

`--> stáhnout

| navigace |