Tablas cruzadas en PostgreSQL: PivotMyTable

  • Google Plus
  • Facebook
  • Twitter
  • Email
  • RSS
  • Add to favorites

Tablas cruzadas en  PostgreSQL: PivotMyTable

PivotMyTable es una función escrita en PL/Python para su uso en servidores PostgreSQL. Su objetivo es obtener tablas cruzadas en PostgreSQL de una manera más amigable que lo que posibilita el módulo tablefunc de PostgreSQL con su serie de funciones crosstab, siendo en el fondo una suerte de intermediario entre el usuario y éstas.

PivotMyTable hace posible crear tablas cruzadas en PostgreSQL del mismo modo que otras soluciones disponibles, automatizando la creación de las consultas que las funciones crosstab de tablefunc necesitan para funcionar. Más concretamente, Continue reading

¡¡Compártelo y disfruta!!

  • Google Plus
  • Facebook
  • Twitter
  • Email
  • RSS
  • Add to favorites

PostgreSQL a excel con Python

Exportar datos desde PostgreSQL a excel
  • Google Plus
  • Facebook
  • Twitter
  • Email
  • RSS
  • Add to favorites

Exportar datos desde PostgreSQL a excel no es sencillo… Si como base de datos nos referimos a MS Access, no es mayor problema. Pero si nos referimos a una base de datos más… de verdad, como puede ser PostgresSQL o MySQL o cualquier otro sistema gestor de bases de datos ya no es lo mismo. Nosotros nos vamos a ocupar en este artículo de esportar datos desde PostgreSQl a excel con Python. Nótese que donde digo excel digo LibreOffice/OpenOffice Calc también, por supuesto.

Exportar datos desde PostgreSQL a excel

Exportar datos desde PostgreSQL a excel puede ser muy sencillo

La manera sencilla pero poco productiva consiste en exportar los datos desde PostgreSQL a excel  creando un archivo de texto (valores separados por comas o como nos convenga) mediante el comando de psql \copy:

Luego este archivo lo importamos mediante excel y listos.

Pero aquí la cosa se trata de sacar directamente la información desde PostgreSQL a excel, es decir,  obtener un bonito archivo xls o xlsx mediante unas pocas líneas de código.

Herramientas necesarias

  • Servidor PostgreSQL funcionando (mi versión es la 9.0). La manera más sencilla de obtenerlo es mediante tu gestor de paquetes si utilizas Linux.
  • Python 2.7 instalado en tu computadora. Viene instalado por defecto en cualquier distribución Linux al uso (OpenSUSE, Ubuntu…)
  • PygreSQL, módulo Python openSource que permite la interacción con servidores PostgreSQL. Te recomiendo instalar la versión 4.11. En mi caso, tuve que instalarla mediante pip y no mediante el gestor de paquetes, que me ofrecía la versión 4.0. Es preferible la versión 4.11 que ofrece pip por disponer de ciertos métodos convenientes para manejar los resultados de las consultas.
  • Pandas, módulo Python que provee de herramientas sencillas y de alto rendimiento para análisis de datos y estructuras de datos de manejo sencillo, instalable tanto desde paquetes como a través de easy_install o pip.
  • Módulos Python xlwt y openpyxl, necesarios para que Pandas funcione correctamente. Se pueden instalar sin porblemas mediante easy_install, pip o tu gestor de paquetes. Estos son los módulos que permiten que python interaccione con archivos xls y xlsx.

Con todas estas herramientas instaladas, ya podemos ponernos a exportar datos desde PostgreSQL a excel.

PostgreSQL a excel con Python: Pasos a seguir

Los pasos que  tenemos que seguir son bastante sencillos. Exportar una tabla de una base de datos (o una consulta cualquiera, ojo) a excel mediante python es materia de sólo unas pocas líneas de código, como podréis ver

1. Importamos los módulos python necesarios y dotamos a nuestro archivo de script del sharpbang necesario para que se ejecute en nuestro sistema:

2. Creamos una conexión a la base de datos con pygresql:

3. Hacemos una consulta a la base de datos para obtener los datos que exportaremos desde PostrgreSQL a excel, también mediante las funciones de pygresql:

4. Comprobaremos si la consulta ha recuperado datos (¡Importante: si no lo hacemos, en caso de que no haya recuperado nada, tendríamos que gestionar la excepción que se generará!). Si es así, convertiremos esos datos a un objeto del tipo DataFrame de pandas, donde podremos, si la exportación desde PostgreSQL a excel lo requiriese, hacer manipulaciones adicionales sobre los datos (cálculos, etc):

Lo que hacemos aquí es indicar que el objeto DataFrame estará constituido por los datos de la consulta, que es lo que se consigue mediante datos.getresult()  y darle un nombre a las columnas del DataFrame al pasarle una tupla python de los nombres de columna mediante  datos.listfields()

Podríamos dar cualquier otro nombre de columnas si quisiéramos. Por ejemplo, al pasar datos de PostgreSQL a excel dispusiéramos de cuatro columnas y quisiéramos darles los nombres nombre, apellidos, fecha, regalo, sólo tendremos que crear una tupla python  con los nombres de las columnas y pasárselos al crear el DataFrame:

6. Exportaremos el DataFrame con los datos de nuestra base de datos PostgreSQL a excel… esto es sencillísimo ya que el objeto DataFrame de pandas dispone del método to_excel() que facilita enormemente las cosas y… ¡listos!

El parámetro index indica si se deben guardar los datos con el índice de las filas o no. Si lo hubiéramos puesto a True, la primera columna de nuestra hoja de cálculo sería ese índice, o sea, la numeración de las líneas. Esto no suele ser de interés al pasar los datos desde una base de datos cualquiera o PostgreSQL a excel.

Si empleas la extensión xls en tu archivo, se generará un archivo compatible con excel 2007, para lo que pandas necesita del módulo xlwt . Si utilizas xlsx se generará un archivo compatible con las últimas versiones de excel y pandas precisa del módulo openpyxl, de ahí la necesidad de tenerlos instalados para exportar datos de PostgreSQL a excel.

¡OJO!

Existe un problema frecuente y que no he conseguido resolver relativo a la imposibilidad de exportar datos si estos contienen caracteres acentuados y especiales. Cuando este problema te suceda al exportar a xls, exporta tus datos al formato xlsx. Parece ser la única solución por el momento.

 

Todo esto lo podemos juntar en un sencillo script:

Guardamos nuestro script en un archivo y otorgamos permisos de ejecución al script mediante  chmod +x miscript.py

Y ya está… este trozo de código se podría utilizar como parte de aplicaciones más grandes, para sacar informes desde PostgreSQL a excel. Por cierto: con otras bases de datos se procedería de igual manera. Apenas sería necesario cambiar el módulo pygresql por otro que te permita conectarte con tu base de datos preferida (MySQL, SQLite…)

 

¡¡Compártelo y disfruta!!

  • Google Plus
  • Facebook
  • Twitter
  • Email
  • RSS
  • Add to favorites

Funciones de fecha en Postgresql

  • Google Plus
  • Facebook
  • Twitter
  • Email
  • RSS
  • Add to favorites

Los que hayáis trabajado con PostgreSQl quizá os hayáis enfrentado al tedio que supone tener que utilizar las funciones de fecha que proporciona PostgreSQL cuando queréis emplear parte de una fecha en vuestras consultas.  Por ejemplo, cuando queremoshacer consultas sobre meses, no nos queda más remedio que usar la funcion extract, que funciona más o menos así:

De manera que para emplear en una consulta el mes y el año de una fecha, tendríamos que escribir algo como:

Por no contar si lo que queremos es seleccionar los datos por trimestre, la cosa se complica:

Lo cual es , obviamente, muy engorroso.  De manera que un día me puse con el manual de PostgreSQL y me saqué unas funciones mucho más sencillas de utilizar. Una función que me devuelve directamente el mes a partir de una fecha y otra que me devuelve el trimestre. Con el mismo esquema se podría hacer un juego completo para el año y el día.

Mis funciones de fecha

En primer lugar, os paso la función trimestre:

El funcionamiento es, como habreis podido ver, muy sencillo. Practicamente es la misma consulta a la que me he referido antes, pero convertida en funcion. De esta manera, nuestra pesada consulta anterior su transforma en:

De manera muy similar ocurre con la funcion mes:

En fin, espero que ambas funciones de fecha os resulten útiles. ¡¡Espero vuestros comentarios!!

 

 

¡¡Compártelo y disfruta!!

  • Google Plus
  • Facebook
  • Twitter
  • Email
  • RSS
  • Add to favorites