ASIX-M10-UF2. Administració d'un SGBD. PostgreSQL

De Wikijoan
Dreceres ràpides: navegació, cerca

Contingut

Referències

Exemples

Els següents exercicis utiltizen la base de dades project que vam veure en el seu dia. Ho pots repassar a:

Exemple 1

Executar un script de postgres des de un bash script parametritzant

Has de recuperar el script project_postgres.sql que regenera la base de dades project.

Recordem com executem un script SQL des de la línia de comandes:

$ psql -h localhost -p 5432 -U alumne -d project -f ./project_postgres.sql

script load_sql_script.sh:

#!/bin/bash
set -e
set -u

if [ $# != 3 ]; then
    echo "please enter a db host, a database name, and a script to execute (full path)"
    exit 1
fi

export DBHOST=$1
export DBNAME=$2
export SCRIPT_PATH=$3

export PGPASSWORD="postgres"

psql \
    -X \
    -U postgres \
    -h $DBHOST \
    -f $SCRIPT_PATH \
    --echo-all \
    --set AUTOCOMMIT=off \
    --set ON_ERROR_STOP=on \
    $DBNAME

psql_exit_status=$?

if [ $psql_exit_status != 0 ]; then
    echo "psql failed while trying to run this sql script" 1>&2
    exit $psql_exit_status
fi

echo "sql script successful"
exit 0

NOTA: Si no volem que demani el password, tenim dues opcions com a mínim. Una possibilitat és, com vam veure al tema de seguretat a PostgreSQL, tocar el fitxer pg_hba.conf i fer confiança amb l'opció trust. La segona opció és incorporar al teu script la comanda:

$ export PGPASSWORD=postgres

El script demana tres paràmetres: host, nom de la base de dades, i ruta completa del script SQL que volem executar.

Per exemple:

$ ./load_sql_script.sh localhost project /home/joan/M10_1516/scripts_admin_postgres/project_postgres.sql

Amb la comanda:

psql_exit_status=$?

podem saber si el script ha acabat correctament o no. Per veure com funciona, introdueix un error en el script SQL. En el cas de què $? sigui diferent de zero, es notifica un error.

Després d'executar un script sempre pots fer $? per veure el resultat del script.

Exemple 2

Obtenir el valor d'una consulta SQL, i ficar-lo en una variable bash.

script num_projectes.sh:

#!/bin/bash
export PGPASSWORD="postgres"

NUM_PROJECTES=$(psql -U postgres -h localhost -P t -P format=unaligned -d project -c "select count(*) from project")

echo "Número de projectes: $NUM_PROJECTES"

if [ "$NUM_PROJECTES" -gt "10" ]; then
echo més de 10
else
echo menys o igual que 10
fi

Exemples 3 i 4

Seguint el link de referència, anem a provar aquests dos exemples. Hem d'obtenim el camps d'un dels projectes de la taula project. Ho fem de dues maneres diferents:

one_row_select_v1.sh:

#!/bin/bash
export PGPASSWORD="postgres"

read id start_date end_date budget spent<<< $(psql \
    -X \
    -U postgres \
    -h localhost \
    -d project \
    --single-transaction \
    --set ON_ERROR_STOP=on \
    --no-align \
    -t \
    --field-separator ' ' \
    --quiet \
    -c "select id, start_date, end_date, budget, spent from project where id=1")

echo "id: $id, start_date: $start_date, end_date: $end_date, budget: $budget, spent: $spent"

one_row_select_v2.sh:

#!/bin/bash
export PGPASSWORD="postgres"

set -e
set -u

declare -a ROW=($(psql \
    -X \
    -h localhost \
    -U postgres \
    -c "select id, start_date, end_date, budget, spent from project where id = 1" \
    --single-transaction \
    --set AUTOCOMMIT=off \
    --set ON_ERROR_STOP=on \
    --no-align \
    -t \
    --field-separator ' ' \
    --quiet \
    project))

id=${ROW[0]}
start_date=${ROW[1]}
end_date=${ROW[2]}
budget=${ROW[3]}
spent=${ROW[4]}

echo "id: $id, start_date: $start_date, end_date: $end_date, budget: $budget, spent: $spent"

Exemples 5 i 6

Seguint el link de referència, anem a provar aquests dos exemples. Hem d'obtenir els camps dels projectes de la taula project, recorrent totes les files. Ho fem de dues maneres diferents:

multiple_row_select_v1.sh:

#!/bin/bash
export PGPASSWORD="postgres"

set -e
set -u

PSQL=/usr/bin/psql

DB_USER=postgres
DB_HOST=localhost
DB_NAME=project

$PSQL \
    -X \
    -h $DB_HOST \
    -U $DB_USER \
    -c "select id, start_date, end_date, budget, spent from project" \
    --single-transaction \
    --set AUTOCOMMIT=off \
    --set ON_ERROR_STOP=on \
    --no-align \
    -t \
    --field-separator ' ' \
    --quiet \
    -d $DB_NAME \
| while read id start_date end_date budget spent ; do
    echo "id: $id, start_date: $start_date, end_date: $end_date, budget: $budget, spent: $spent"
done

multiple_row_select_v2.sh:

#!/bin/bash
export PGPASSWORD="postgres"

set -e
set -u

PSQL=/usr/bin/psql

DB_USER=postgres
DB_HOST=localhost
DB_NAME=project

$PSQL \
    -X \
    -h $DB_HOST \
    -U $DB_USER \
    -c "select id, start_date, end_date, budget, spent from project" \
    --single-transaction \
    --set AUTOCOMMIT=off \
    --set ON_ERROR_STOP=on \
    --no-align \
    -t \
    --field-separator ' ' \
    --quiet \
    $DB_NAME | while read -a Record ; do

    id=${Record[0]}
    start_date=${Record[1]}
    end_date=${Record[2]}
    budget=${Record[3]}
    spent=${Record[4]}

    echo "id: $id, start_date: $start_date, end_date: $end_date, budget: $budget, spent: $spent"
done

NOTA: en aquesta segona versió, si hi ha un camp buit dóna error.

Exemple 7

En els exemples 3, 4, 5 i 6 hem fet una petita trampa. Només hem agafat camps que no tenien espais en blanc. Per exemple, no hem agafat el camp nom o descripció. Per poder-ho fer, hem de saber com des de bash podem separar una cadena que conté delimitadors diferents de l'espai en blanc, en les variables corresponents. Per exemple, mira't aquest enllaç:

Exemple Python i PostgreSQL

Primer de tot:

python-pygresql - PostgreSQL module for Python
$ sudo apt-get install python-pygresql

Fer un insert: insert_projects.py:

#!/usr/bin/python
from pgdb import connect
con = connect(database='project', host='localhost:5432', user='postgres', password='postgres')
cursor = con.cursor()
#cursor.execute("insert into project (name) values (%s)", ('banana',))
#insert into project (id, project_status_type_id, manager_person_id, name, description, start_date, end_date, budget, spent) values (5,3,7,'projecte de prova', 'desc projecte de prova', '2004-07-01', '2004-07-01', 1200, 1300);
cursor.execute("insert into project (id, project_status_type_id, manager_person_id, name, description, start_date, end_date, budget, spent) values (%s,%s,%s,%s,%s,%s,%s,%s,%s)", (5,3,7,'projecte de prova', 'desc projecte de prova', '2004-07-01', '2004-07-01', 1200, 1300))
con.commit()

select de múltiples files: select_projects.py:

#!/usr/bin/python
from pgdb import connect
con = connect(database='project', host='localhost:5432', user='postgres', password='postgres')
cursor = con.cursor()
cursor.execute("select * from project")
rows=cursor.fetchall()
for row in rows:
	print '%s: %s - %s' % (row[0], row[3], row[5])
Eines de l'usuari
Espais de noms
Variants
Accions
Navegació
Institut Jaume Balmes
Màquines recreatives
CNC
Informàtica musical
joanillo.org Planet
Eines