ASIX-M10-UF2. Administració d'un SGBD. PostgreSQL
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.
- Descàrrega del script: Fitxer:Project postgres.sql.zip
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:
python3-pygresql - PostgreSQL module for Python $ sudo apt-get install python3-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 (%i,%i,%i,%s,%s,%s,%s,%i,%i)", (5,3,7,'projecte de prova', 'desc projecte de prova', '2004-07-01', '2004-07-01', 1200, 1300))
con.commit()
Per executar:
$ python3 insert_projects.py
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 ('%i: %s - %s' % (row[0], row[3], row[5]))
