Instalando PostgreSQL en local en windows o linux usando conda

Para el que no lo sepa, podéis instalar PostgreSQL usando conda \o/

Vamos a hacer un tutorial paso a paso para poder instalarlo todo y dejarlo listo para trabajar desde Python de forma sencilla.

[A lo largo de todo el tutorial se indica si el código a usar es para windows o para linux. Si no se indica nada el código debería ser válido en ambos sistemas operativos.]

1. Creamos un entorno virtual usando conda e instalamos PostgreSQL.

Este paso es sencillo. Solo necesitáis tener instalado conda en vuestro equipo y una conexión a internet. Si no tenéis conda instalado podéis ir a la sección de enlaces, más abajo, para visitar la documentación de conda donde os indica como instalarlo. Con conda instalado, podemos añadir el canal de conda-forge (básico para poder extender la cantidad de paquetes disponibles además de los mantenidos oficialmente). Lo podéis añadir a vuestros canales de referencia mediante el siguiente código a ejecutar en la línea de comandos:

conda config --add channels conda-forge

Una vez hecho lo anterior, en vuestra línea de comandos, podéis escribir:

conda create --name pgenv postgresql python=3.6

Lo anterior nos crea un entorno virtual conda llamado pgenv con Python 3.6. Activamos el nuevo entorno que hemos creado escribiendo en la línea de comandos:

source activate pgenv # linux

activate pgenv # windows

Antes de poder usar PostgreSQL debemos hacer alguna cosita más. Veamos la siguiente sección.

2. Creando un cluster de BBDD

Una vez instalado PostgreSQL deberéis crear la carpeta de datos donde se guardarán las BBDD (a esto se le llama cluster en los docs de PostgreSQL).

Lo vamos a instalar en una carpeta que se llame data. La carpeta data la creará el comando si no existe pero fallará si ya existe y no está vacía. Por ello, para evitar problemas podemos crear la carpeta a mano donde deseemos para asegurarnos que la misma esté vacía y para asegurarnos que tenemos permisos de escritura en esa ubicación.

mkdir /ruta/hasta/data # linux
mkdir "C:\\ruta\\hasta\\data" # windows.

En linux, ubicaciones populares de esta carpeta data son:

/usr/local/pgsql/data
/var/lib/pgsql/data

Pero la podéis colocar donde queráis.

Y ahora vamos al comando en cuestión. Si estáis en linux podéis hacer:

initdb -D /usr/local/pgsql/data # linux

En windows es similar pero con una ruta aceptable para windows:

initdb -D "C:\\ruta\\hasta\\data" # windows

De forma alternativa podéis hacer:

pg_ctl -D /usr/local/pgsql/data initdb # linux

"pg_ctl" -D "C:\\ruta\\hasta\\data" initdb # windows

Es mejor usar, en general, pg_ctl ya que es el comando que usaremos para arrancar, parar,..., el servidor de BBDD por lo que será útil familiarizarnos con el mismo.

3. Arrancando el servidor de BBDD.

Podemos arrancar el servidor de BBDD usando:

pg_ctl -D /usr/local/pgsql/data start # linux

"pg_ctl" -D "C:\\ruta\\hasta\\data" start # windows

Si, además, queremos tener un fichero log con la información de lo que se vaya
haciendo podemos usar la opción -l:

pg_ctl -D /usr/local/pgsql/data -l fichero_log start # linux

"pg_ctl" -D "C:\\ruta\\hasta\\data" -l fichero_log start # windows

Y se creará un fichero de texto con la información llamado fichero_log en la
ubicación desde donde lanzamos el comando (o en la ruta que defináis si así queréis). Es recomendable usar esta opción si no queréis que toda la información se vaya mostrando en la línea de comandos y para tener un registro de lo que vamos haciendo.

El directorio de datos se crea con seguridad mínima (modo trust). Como vamos a trabajar en local y, generalmente, en un sistema monousuario o con usuarios en los que confiamos no vamos a prestar mucha atención a esto pero puedes leer más sobre ello aquí.

El usuario por defecto del sistema que hace la instalación de PostgreSQL (usando conda en este caso) es el que se puede usar para la base de datos.

4. Interactuando con la base de datos.

Podemos instalar también psycopg2, driver para comunicar Python con PostgreSQL, y pgcli, una línea de comandos con esteroides, lo que viene a ser IPython para la consola Python. Con nuestro entorno pgenv activado escribimos en la línea de comandos:

conda install pgcli psycopg2

Genial, ¡qué fácil todo!

Vamos a crear nuestra primera base de datos. Para ello deberemos tener el servidor de BBDD funcionando. En este caso, con el comando que hemos usado anteriormente, pg_ctl ... start, debería haber arrancado y lo siguiente debería funcionar sin dar problemas:

createdb dbtest

Lo anterior debería haber creado una base de datos llamada dbtest. Si no ha
habido ningún problema podríamos acceder con pgcli (o psql, el comando de serie que viene con PostgreSQL) haciendo:

pgcli dbtest # 'psql dbtest' en caso que no hayáis instalado pgcli

(si hemos entrado en pgcli o en psql podemos salir usando \q).

Ahora podríamos empezar a crear tablas e insertar datos pero, si os acordáis, hemos instalado psycopg2. Usémoslo para hacerlo desde Python.

El siguiente código va a crear una tabla llamada tabla y vamos a insertar una serie de filas. Lo podéis ejecutar desde la consola Python mismo:

import psycopg2

# tu_usuario en la siguiente línea debería ser tu usuario del sistema
conn = psycopg2.connect("dbname=dbtest user=tu_usuario")

cur = conn.cursor()

cur.execute(
    "CREATE TABLE tabla (id serial PRIMARY KEY, num integer, num_txt varchar);"
)
cur.execute(
    "INSERT INTO tabla (num, num_txt) VALUES (%s, %s)", 
    (1, "uno")
)
cur.execute(
    "INSERT INTO tabla (num, num_txt) VALUES (%s, %s)",
    (10, "diez")
)

conn.commit()

cur.close()
conn.close()

Lo que hace el código anterior es, básicamente:

  • se conecta a la base de datos que acabamos de crear, dbtest,
  • crea una tabla, llamada 'tabla',
  • mete varias filas de datos en esa nueva tabla y,
  • finalmente, cierra la conexión con la base de datos.

Desde la línea de comandos podemos usar pgcli para hacer una consulta, también desde python pero vamos a hacerlo con pgcli en este caso:

(salid de la consola Python usando exit() si todavía estáis dentro de la misma)

En la línea de comandos:

pgcli dbtest # 'psql dbtest' si no habéis instalado pgcli

Ya dentro de pgcli (o psql) podemos hacer una consulta SQL:

SELECT * FROM tabla

Y nos debería dar el siguiente resultado:

+------+-------+-----------+
|  id  |  num  |  num_txt  |
|------+-------+-----------|
|   1  |   1   |    uno    |
|   2  |   10  |    diez   |
+------+-------+-----------+
SELECT 2
Time: 0.008s

Salimos nuevamente de pgcli (o psql) usando:

\q

5. Administración y limpieza.

Si no queréis la base de datos y la deseáis eliminar podéis usar, desde la línea de comandos:

dropdb dbtest

Y la base de datos se borrará.

Vamos a apagar el servidor PostgreSQL para ver como se hace:

pg_ctl -D /usr/local/pgsql/data stop # linux

"pg_ctl" -D "C:\\ruta\\hasta\\data" stop # windows

Si lo quisiéramos volver a arrancar podemos hacer:

pg_ctl -D /usr/local/pgsql/data start # linux

"pg_ctl" -D "C:\\ruta\\hasta\\data" start # windows

Podéis ver las diferentes opciones del comando pg_ctl mediante:

pg_ctl --help

Y eso es todo. Una forma sencilla de usar PostgreSQL en local mediante conda.

6. Eliminar el entorno virtual y PostgreSQL de forma eficaz.

Eliminarlo todo sería tan sencillo como eliminar el entorno conda creado una vez que lo tengamos desactivado y que el servidor de BBDD este parado.

Para parar el servidor de BBDD hacemos en la línea de comandos:

pg_ctl -D /usr/local/pgsql/data stop # linux

"pg_ctl" -D "C:\\ruta\\hasta\\data" stop # windows

Para desactivar el entorno virtual hacemos desde la línea de comandos

source deactivate # linux

deactivate # windows

Y para borrar el entorno virtual pgenv hacemos, desde la línea de comandos:

conda-env remove --name pgenv

Si, además, queremos hacer limpieza general de conda, limpiar paquetes 'cacheados' y liberar espacio en disco podemos hacer desde la linea de comandos:

conda clean -pt

Finalmente, borramos la carpeta data que creamos al principio de esta entrada.

rm -fr /usr/local/pgsql/data # linux

rmdir "C:\\ruta\\hasta\\data" /s /q # windows

Enlaces.

Página oficial de Postgresql: https://www.postgresql.org/
Documentación oficial de Postgresql: https://www.postgresql.org/docs/
Documentación oficial de conda: https://conda.io/docs/
Por si queréis cambiar el usuario y/o password que usa conda en la instalación: https://stackoverflow.com/questions/15008204/how-to-check-postgres-user-and-password#15008311
Documentación oficial de psycopg2: http://initd.org/psycopg/docs/index.html
Página oficial de pgcli: https://www.pgcli.com/

Notas finales.

No se entra en profundidad en ninguna de las herramientas (PostgreSQL, pgcli, psycopg2) para mantener el tutorial lo más sencillo posible.

En los enlaces tenéis mucha más información para ampliar.

En ningún caso se presta atención al tema de seguridad, configuración en profundidad,..., de PostgreSQL ya que eso daría para unas cuantas entradas.

¡¡Disfruten lo instalado!!

Aprende (funcionalidad básica de) PyTables paso a paso (III)

En los anteriores capítulos vimos como crear una estructura básica para nuestro fichero h5 y como rellenar una tabla de datos. Ahora vamos a ver como volver a la tabla que ya rellenamos para añadir nuevos registros sin eliminar los anteriores.

Modificar tablas existentes

Podemos añadir datos a tablas existentes de la siguiente forma. Para hacerlo primero abrimos el fichero que ya creamos anteriormente en modo "append", obtenemos el nodo que nos lleva a la tabla en cuestión que queremos rellenar (tab será una instancia de la clase Table) y establecemos un 'puntero' donde guardar los nuevos datos (con ayuda de tab.row donde mis_datos serán una instancia de la clase Row):

# Si no habéis importado las librerías necesarias
# descomentad las siguientes tres líneas:
# import numpy as np
# import datetime as dt
# import tables as tb
h5file = tb.openFile("tabla_test.h5", mode = "a")
tab = h5file.getNode("/carpeta1/datos_carpeta1_tabla1")
mis_datos = tab.row

Ahora vamos a recuperar la última fecha que habíamos introducido en la columna fechas para seguir metiendo registros con fechas a continuación.

## En la siguiente línea recuperamos el último elemento
## de la columna fecha
## No os preocupéis por esto ahora, lo veremos más adelante
ult_fecha = str(tab.cols.fecha[-1])
yy = int(ult_fecha[:4])
mm = int(ult_fecha[4:6])
dd = int(ult_fecha[6:])
fecha0 = dt.date(yy, mm, dd) + dt.timedelta(days = 1)
## Creamos 2000 nuevas fechas a continuación de la última que
## habíamos introducido con sus valores x, y, z correspondientes
fechas = np.array([int((fecha0 +
                  dt.timedelta(days = 1) * i).strftime("%Y%m%d"))
                  for i in range(2000)],
                  dtype = np.int32)
x = np.random.randn(2000)
y = np.random.randn(2000)
z = np.random.randn(2000)

Y, finalmente, añadimos los nuevos valores tal como vimos en el anterior capítulo:

for i in range(2000):
    mis_datos['fecha'] = fechas[i]
    mis_datos['x'] = x[i]
    mis_datos['y'] = y[i]
    mis_datos['z'] = z[i]
    mis_datos.append()
tab.flush()

En la siguiente imagen se muestra un ejemplo de lo que acabamos de hacer:

hdfview_nuevos valores añadidos

¿Qué pasa si ahora queremos modificar un valor de una columna? Eso lo podemos hacer usando la clase `Cols`. Imaginad que los primeros diez elementos de la columna `x` son erróneos y los queremos representar con 'NaNs'. Lo podemos hacer así:

tab.cols.x[0:10] = np.repeat(np.NAN, 10)
tab.flush()

Si ahora inspeccionamos la tabla veremos que los diez primeros elementos de la columna 'x' han cambiado al valor NaN:

hdfview_reemplazar valores

¿Y si queremos cambiar una columna entera de datos? Pues siguiendo el mismo procedimiento podemos hacer lo siguiente para modificar la columna 'z':

tab.cols.z[:] = np.repeat(-999.99, tab.cols.z.shape[0])
tab.flush()

Con lo que la tabla quedaría con la última columna (columna 'z') con todos sus elementos con valor '-999.99'

hdfview_reemplazar columna

Finalmente, para terminar este capítulo, cerramos la tabla creada como vimos en anteiores capítulos.

h5file.close()

Veremos un poco más en los próximos días, estad atentos.

Saludos.

Aprende (funcionalidad básica de) PyTables paso a paso (II)

En el anterior capítulo vimos como crear una estructura básica para nuestro fichero h5. Ahora vamos a ver como rellenar tablas de ese mismo fichero.

Rellenar tablas

Ahora es tiempo de rellenar la tabla con datos sin sentido (datos aleatorios) y fechas. numpy y datetime nos facilitarán la tarea por lo que vamos a importarlos antes).

import numpy as np
import datetime as dt

Abrimos de nuevo el fichero h5 que creamos en el anterios capítulo, pero en lugar de abrirlo en modo "write" lo abriremos en modo "append".

h5file = tb.openFile("tabla_test.h5", mode = "a")

Recuperamos la tabla que queremos llenar (indicando la ruta en la estructura del fichero hdf5) y usando la función getNode:

tab = h5file.getNode("/carpeta1/datos_carpeta1_tabla1")

Para rellenar la tabla creamos un puntero (o constructor de líneas propio a la tabla) row.

mis_datos = tab.row

Creamos los datos que usaremos para rellenar la tabla:

fechas = np.array([int((dt.date(2000, 1, 1) +
                        dt.timedelta(days = 1) * i).strftime("%Y%m%d"))
                        for i in range(10000)],
                        dtype = np.int32)
x = np.random.randn(10000)
y = np.random.randn(10000)
z = np.random.randn(10000)

Llenamos la tabla de manera recursiva con el constructor de líneas mis_datos y el método append:

for i in range(10000):
    mis_datos['fecha'] = fechas[i]
    mis_datos['x'] = x[i]
    mis_datos['y'] = y[i]
    mis_datos['z'] = z[i]
    mis_datos.append()

Se llama al método flush sobre la tabla para volcar y registrar efectivamente los datos en la tabla.

tab.flush()

Si queremos, podemos añadir meta-información a la tabla:

tab.attrs.nombre_sensor="medidas de un escaterómetro"
tab.attrs.numero_columnas = 3

Además de la meta-información que hemos añadido, el objeto tabla contenía ya un cierto número de atributos que podemos ver escribiendo lo siguiente en IPython

tab.attrs

que nos mostrará lo siguiente:

/carpeta1/datos_carpeta1_tabla1._v_attrs (AttributeSet), 14 attributes:
   [CLASS := 'TABLE',
    FIELD_0_FILL := 0,
    FIELD_0_NAME := 'fecha',
    FIELD_1_FILL := 0.0,
    FIELD_1_NAME := 'x',
    FIELD_2_FILL := 0.0,
    FIELD_2_NAME := 'y',
    FIELD_3_FILL := 0.0,
    FIELD_3_NAME := 'z',
    NROWS := 0,
    TITLE := 'ejemplo de tabla',
    VERSION := '2.7',
    nombre_sensor := 'medidas de un escaterómetro',
    numero_columnas := 3]

Finalmente, cerramos la tabla creada como vimos en el primer capítulo.

h5file.close()

Si queréis usar una aplicación gráfica para ver el alrchivo que hemos creado podéis usar hdfview (sudo apt-get install hdfview) o vitables. En este caso os muestro un ejemplo con hdfview ya que vitables posíblemente lo veamos en uno de los próximos capítulos.

hdfview
[Pulsa sobre la imagen para agrandarla]
Bueno, ya tenemos algunos datos guardados en nuestro fichero HDF5. El próximo día veremos como anexar datos a una tabla ya existente.

Saludos.

Aprende (funcionalidad básica de) PyTables paso a paso (I)

HDF5

El HDF5 (hierarchical dataset format, http://www.hdfgroup.org/HDF5/) es un formato que permite almacenar eficientemente grandes volúmenes de datos. Los datos se pueden almacenar de forma jerarquizada conjuntamente con metadatos. Es un formato portable que prácticamente no tiene límite en el tamaño de los datos.

PyTables

Pytables está programado sobre el formato hdf5 con ayuda de Python y Numpy. La creación, modificación y búsquedas sobre grandes conjuntos de datos es relativamente sencilla. La utilización de memoria RAM y compresión de datos están optimizados en segundo plano por la biblioteca. El espacio de disco utilizado es mucho más pequeño y el acceso a los datos suele ser más rápido comparado con bases de datos relacionales.

La versión actual es la 3.0 y es compatible con python 2.7 y 3.1 o superiores.

Para instalarlo en un linux basado en Debian podéis hacer lo siguiente (asumo que tenéis pip y numpy instalado en vuestro virtualenv científico):

sudo apt-get install libhdf5-serial-dev
pip install numexpr cython
pip install tables

Para instalarlo en Windows podéis hacerlo usando un ejecutable ya preparado por el gran Christoph Gohlke.

Si todo está correcto podemos empezar con el tutorial. Primero importamos la biblioteca

Creación de ficheros h5

import tables as tb

Ahora vamos a definir la estructura de una tabla creando una clase que deriva de la clase IsDescription (igual os suena a algo parecido a los modelos de Django). Nuestra tabla contará con una columna de fechas y tres columnas de datos 'float':

class EstructuraTabla(tb.IsDescription):
    fecha = tb.Int64Col(pos = 0)
    x = tb.Float32Col(pos = 1)
    y = tb.Float32Col(pos = 2)
    z = tb.Float32Col(pos = 3)

Esta clase es un constructor de tabla. Se dan nombre predefinidos a las columnas, el tipo de datos que contendrán y su posición.

Ahora creamos un nuevo fichero h5 en modo de escritura.

# Donde pone "tabla_test.h5" y "Ejemplo pybonaccico"
# puedes poner la ruta y nombre de fichero que quieras
h5file = tb.openFile("tabla_test.h5",
                     mode = "w",
                     title = "Ejemplo pybonaccico")

En este caso, en el título hemos puesto algo muy estúpido pero en ese campo se puede poner algo más serio como 'Datos aportados por Google, fecha: 2012/01/01 14.23h, proyecto PRISM ;-P'

Creamos un "grupo" en la raiz del h5 (parecido a una carpeta en un sistema de ficheros):

grupo1 = h5file.createGroup("/",
                            'carpeta1',
                            'carpeta para un primer grupo de datos')

carpeta1 será el nombre del grupo dentro del fichero h5 y carpeta para un primer grupo de datos serán los metadatos descriptivos que podemos asociar al grupo carpeta1.

Y ahora, dentro del grupo que acabamos de crear, creamos una tabla (que sería como un fichero en un sistema de ficheros) con la estructura de tabla que hemos definido anteriormente (clase EstructuraTabla):

tab = h5file.createTable(grupo1,
                         "datos_carpeta1_tabla1",
                         EstructuraTabla,
                         "ejemplo de tabla")

Aquí, datos_carpeta1_tabla1 sería como el nombre del fichero (tabla) dentro de un sistema de archivos (dentro del fichero h5), EstructuraTabla es la estructura de los datos de la tabla que acabamos de definir y ejemplo de tabla es la información que asociamos a esta tabla.

En cualquier momento podemos inspeccionar la estructura que va tomando nuestro fichero h5 haciendo un print del fichero h5.

print(h5file)

Lo anterior nos debería de mostrar algo parecido a:

tabla_test.h5 (File) 'Ejemplo pybonaccico'
Last modif.: 'Thu Jul  4 21:51:41 2013'
Object Tree:
/ (RootGroup) 'Ejemplo pybonaccico'
/carpeta1 (Group) 'carpeta para un primer grupo de datos'
/carpeta1/datos_carpeta1_tabla1 (Table(0,)) 'ejemplo de tabla'

Si queremos obtener más información podemos escribir simplemente en IPython:

h5file

que nos mostrará lo siguiente:

File(filename=tabla_test.h5, title='Ejemplo pybonaccico', mode='w', root_uep='/', filters=Filters(complevel=0, shuffle=False, fletcher32=False))
/ (RootGroup) 'Ejemplo pybonaccico'
/carpeta1 (Group) 'carpeta para un primer grupo de datos'
/carpeta1/datos_carpeta1_tabla1 (Table(0,)) 'ejemplo de tabla'
  description := {
  "fecha": Int64Col(shape=(), dflt=0, pos=0),
  "x": Float32Col(shape=(), dflt=0.0, pos=1),
  "y": Float32Col(shape=(), dflt=0.0, pos=2),
  "z": Float32Col(shape=(), dflt=0.0, pos=3)}
  byteorder := 'little'
  chunkshape := (3276,)

Finalmente, cerramos la tabla creada de la siguiente forma.

h5file.close()

Y ya es suficiente por hoy, el próximo día veremos como rellenar tablas con datos ya que, de momento, solo hemos creado una estructura básica.

Saludos.