Guía para principiantes de PostgreSQL

Saber consultar sistemas de bases de datos relacionales es una habilidad imprescindible para cualquier científico de datos. SQL, o Structured Query Language, te permite hacerlo de forma muy eficiente. Con SQL no solo puedes hacer preguntas relevantes a los datos, sino también manipularlos de muchas maneras. Sin bases de datos, prácticamente no existirían aplicaciones reales. Por eso, conocerlas y saber manejarlas es clave en la caja de herramientas de cualquier profesional de datos.

Dato rápido: a SQL también se le llama SE-QU-EL. Tiene su historia: el nombre inicial fue Simple English Query Language.

En general, las bases de datos relacionales se ven así:

A las relaciones también se las llama tablas. Hay varias formas de representar bases de datos; esta es solo una de ellas y la más popular.

Este tutorial presenta las cuatro operaciones más comunes en SQL: crear, leer, actualizar y borrar. En conjunto se conocen como CRUD. En cualquier aplicación con interacción de usuario, estas cuatro operaciones siempre están presentes.

Usarás PostgreSQL como sistema de gestión de bases de datos relacionales. PostgreSQL es muy ligero y además es gratuito. En este tutorial, vas a:

• Instalar y poner en marcha PostgreSQL
• Conectarte a una base de datos de PostgreSQL
• Crear, leer, actualizar y borrar tablas en esa base de datos
• Ejecutar SQL en Jupyter Notebook
• Ejecutar SQL en Python

Vamos allá.

Puesta en marcha con PostgreSQL

PostgreSQL es un RDBMS ligero y de código abierto. Está muy extendido en la industria. Puedes saber más en su sitio oficial.

Para empezar a escribir y ejecutar consultas en PostgreSQL, necesitas instalarlo en tu equipo. La instalación es muy sencilla. Estos dos vídeos cortos muestran cómo descargar e instalar PostgreSQL en un Windows 7 de 32 bits:

• Descargar PostgreSQL
• Instalar PostgreSQL

Nota: durante la instalación de PostgreSQL, anota la contraseña y el puerto que introduzcas.

Cuando hayas instalado PostgreSQL correctamente, abre pgAdmin. pgAdmin es una utilidad que viene con la instalación de PostgreSQL y te permite hacer tareas habituales de base de datos con una interfaz gráfica muy cómoda. La interfaz de pgAdmin es así:

Al abrir pgAdmin verás un servidor llamado "PostgreSQL 9.4 (localhost:5432)" en la interfaz:

Nota: tu versión puede ser distinta, y también el número de puerto (5432).

Conéctate al servidor introduciendo la contraseña que diste durante la instalación. Para referencia: https://www.loom.com/share/71708f6a99ee483a977991e9e8a25808.

Una vez conectado al servidor local, verás una interfaz similar a esta:

Tu primera tarea será crear una base de datos. Haz clic derecho en la pestaña Databases y selecciona New Database en el menú. Vamos a crear una base de datos llamada DataCamp_Courses. Cuando esté creada, puedes continuar con las siguientes secciones del tutorial.

Operaciones CRUD en PostgreSQL

Crear una tabla a partir de una especificación dada:

Para poder operar en una base de datos necesitas una tabla. Vamos a crear una tabla sencilla (también llamada relación) llamada datacamp_courses con la siguiente especificación (esquema):

La especificación nos da bastante información sobre las columnas de la tabla:

• La clave primaria de la tabla debe ser course_id (observa que solo esta aparece en negrita) y su tipo de dato debe ser entero. Una clave primaria es una restricción que obliga a que los valores de la columna sean no nulos y únicos. Te permite identificar de forma unívoca cada registro de la tabla.
• El resto de la información de la especificación ahora debería ser fácil de interpretar.

Para crear una tabla, haz clic derecho en la base de datos recién creada DataCamp_Courses y selecciona CREATE Script en las opciones. Verás algo parecido a esto:

Ejecuta ahora la siguiente consulta:

CREATE TABLE datacamp_courses(
 course_id SERIAL PRIMARY KEY,
 course_name VARCHAR (50) UNIQUE NOT NULL,
 course_instructor VARCHAR (100) NOT NULL,
 topic VARCHAR (20) NOT NULL
);
Con la tecnología de

Para ejecutar la consulta, selecciónala y haz clic en el botón de ejecutar de la barra de menú:

La salida debería ser:

La estructura general de una consulta de creación de tabla en PostgreSQL es:

CREATE TABLE table_name (
 column_name TYPE column_constraint,
 table_constraint table_constraint
)
Con la tecnología de

No especificamos table_constraints al crear la tabla; por ahora podemos omitirlos. Todo es bastante legible salvo la palabra clave SERIAL. Serial en PostgreSQL crea una columna autoincremental. Por defecto genera valores de tipo entero. Serial nos ahorra tener que recordar el último valor insertado/actualizado de la clave primaria, y es buena práctica usar autoincrementos para claves primarias. Puedes saber más sobre serial aquí.

Insertar algunos registros en la tabla recién creada

En este paso vas a insertar algunos registros en la tabla. Cada registro debe contener:

• Nombre del curso
• Nombre de la persona instructora
• Tema del curso

Los valores de la columna course_id los gestionará PostgreSQL automáticamente. La estructura general de una consulta de inserción en PostgreSQL es:

INSERT INTO table(column1, column2, …)
VALUES
 (value1, value2, …);
Con la tecnología de

Vamos a insertar algunos registros:

INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Deep Learning in Python','Dan Becker','Python');

INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Joining Data in PostgreSQL','Chester Ismay','SQL');
Con la tecnología de

Fíjate en que no has especificado las claves primarias explícitamente. Verás el efecto enseguida.

Al ejecutar las dos consultas anteriores, deberías ver este resultado si todo fue bien:

Query returned successfully: one row affected, 11 ms execution time.

Leer/visualizar los datos de la tabla

Esto es probablemente algo que harás mucho en tu día a día con datos. Por ahora, veamos cómo va la tabla datacamp_courses.

Esto se llama una consulta de selección (select). Su estructura genérica es:

SELECT
 column_1,
 column_2,
 ...
FROM
 table_name;
Con la tecnología de

Seleccionemos todas las columnas de la tabla datacamp_courses

SELECT * FROM datacamp_courses;
Con la tecnología de

Y obtienes:

Observa ahora las claves primarias. Si solo quieres ver los nombres de los cursos, puedes hacerlo así:

SELECT course_name from datacamp_courses;
Con la tecnología de

Y obtienes:

Puedes indicar tantos nombres de columna como quieras ver, siempre que existan en la tabla. Si ejecutas select course_name, number_particpants from datacamp_courses; obtendrás un error porque la columna number_particpants no existe en la tabla. Ahora verás cómo actualizar un registro concreto de la tabla.

Actualizar un registro de la tabla

La estructura general de una consulta de actualización en SQL es la siguiente:

UPDATE table
SET column1 = value1,
    column2 = value2 ,...
WHERE
 condition;
Con la tecnología de

Vas a actualizar el registro donde course_instructor = "Chester Ismay" y establecer course_name a "Joining Data in SQL". Luego verificarás que el registro se ha actualizado. La consulta sería:

UPDATE datacamp_courses SET course_name = 'Joining Data in SQL'
WHERE course_instructor = 'Chester Ismay';
Con la tecnología de

Comprobemos si tu consulta de actualización tuvo el efecto deseado ejecutando un select:

Como ves, la actualización se realizó exactamente como querías. Ahora verás cómo borrar un registro de la tabla.

Borrar un registro de la tabla

La estructura general de una consulta de borrado en SQL es:

DELETE FROM table
WHERE condition;
Con la tecnología de

Vas a borrar el registro donde course_name = "Deep Learning in Python" y luego verificar que se ha eliminado. Siguiendo la estructura, esta consulta debería hacerlo:

DELETE from datacamp_courses
WHERE course_name = 'Deep Learning in Python';
Con la tecnología de

Ten en cuenta que las palabras clave no distinguen mayúsculas y minúsculas en SQL, pero los datos sí. Por eso ves una mezcla de mayúsculas y minúsculas en las consultas.

Veamos si el registro previsto se borró de la tabla:

Sí, se eliminó el registro correcto.

Las estructuras genéricas de las consultas mencionadas en el tutorial están referenciadas de PostgreSQL Tutorial.

Ya sabes hacer consultas básicas CRUD en SQL. Si usas mucho Jupyter Notebooks y te estás preguntando si puedes ejecutar estas consultas directamente desde el notebook, en la siguiente sección verás cómo hacerlo.

SQL + Jupyter Notebooks

Para ejecutar consultas SQL desde Jupyter Notebooks, el primer paso es instalar el paquete ipython-sql.

Si no lo tienes instalado, hazlo con:

pip install ipython-sql
Con la tecnología de

Una vez hecho, carga la extensión sql en tu Jupyter Notebook ejecutando:

%load_ext sql
Con la tecnología de

El siguiente paso es conectarte a una base de datos de PostgreSQL. Te conectarás a la base de datos que creaste: DataCamp_Courses.

Para conectarte a una base de datos ya creada en tu sistema, tienes que indicar a Python su dialecto. En otras palabras, decirle que es una base de datos de PostgreSQL. Para ello, necesitarás psycopg2, que puedes instalar con:

pip install psycopg2
Con la tecnología de

Cuando hayas instalado psycopg, conéctate a la base de datos con:

%sql postgresql://postgres:postgres@localhost:5432/DataCamp_Courses
Con la tecnología de

'Connected: postgres@DataCamp_Courses'Con la tecnología de

Fíjate en el uso de %sql. Es un comando mágico. Te permite ejecutar sentencias SQL desde Jupyter Notebook. Lo que sigue a %sql es la URL de conexión a la base de datos, donde indicas:

• Dialect (postgres)
• Usuario (postgres)
• Contraseña (postgres)
• Dirección del servidor (localhost)
• Puerto (5432)
• Nombre de la base de datos (DaaCamp_Courses)

Ahora puedes hacer desde tu Jupyter Notebook todo lo que hiciste en la interfaz de pgAdmin. Empecemos creando la tabla _datacampcourses con el mismo esquema.

Pero antes tendrás que eliminar la tabla, ya que SQL no permite tener dos tablas con el mismo nombre. Puedes borrar una tabla con:

%sql DROP table datacamp_courses;
Con la tecnología de

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
Done.





[] Con la tecnología de

La tabla _datacampcourses se ha eliminado de PostgreSQL y ahora puedes crearla de nuevo con ese nombre.

%%sql
CREATE TABLE datacamp_courses(
 course_id SERIAL PRIMARY KEY,
 course_name VARCHAR (50) UNIQUE NOT NULL,
 course_instructor VARCHAR (100) NOT NULL,
 topic VARCHAR (20) NOT NULL
);
Con la tecnología de

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
Done.





[] Con la tecnología de

Observa el uso de %sql y %%sql. Para ejecutar una sola línea de consulta usa %sql; si quieres ejecutar varias consultas de una vez, usa %%sql.

Insertemos algunos registros:

%%sql
INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Deep Learning in Python','Dan Becker','Python');
INSERT INTO datacamp_courses(course_name, course_instructor, topic)
VALUES('Joining Data in PostgreSQL','Chester Ismay','SQL');
Con la tecnología de

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.
1 rows affected.





[] Con la tecnología de

Visualiza la tabla para asegurarte de que las inserciones se han hecho como esperabas:

%%sql
select * from datacamp_courses;
Con la tecnología de

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
2 rows affected. Con la tecnología de

Sigamos el flujo. Como siguiente paso, actualiza un registro de la tabla:

%sql update datacamp_courses set course_name = 'Joining Data in SQL' where course_instructor = 'Chester Ismay';
Con la tecnología de

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.





[] Con la tecnología de

Presta mucha atención cuando trabajes con cadenas en SQL. A diferencia de lenguajes de programación tradicionales, los valores de tipo cadena deben ir entre comillas simples.

Verifiquemos ahora si la actualización tuvo el efecto deseado:

%%sql
select * from datacamp_courses;
Con la tecnología de

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
2 rows affected. Con la tecnología de

Ahora borra un registro y compruébalo:

%%sql
delete from datacamp_courses where course_name = 'Deep Learning in Python';
Con la tecnología de

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected.





[] Con la tecnología de

%%sql
select * from datacamp_courses;
Con la tecnología de

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected. Con la tecnología de

A estas alturas ya tienes claro cómo ejecutar operaciones CRUD en PostgreSQL y cómo hacerlas desde Jupyter Notebook. Si dominas Python y te interesa acceder a tu base de datos desde tu código, también puedes hacerlo. La siguiente sección va de eso.

Primeros pasos con SQLAlchemy y su combinación con comandos mágicos de SQL

Para esta sección necesitarás el paquete SQLAlchemy. Suele venir con la distribución de Anaconda. También puedes instalarlo con pip. Una vez instalado, impórtalo así:

import sqlalchemy
Con la tecnología de

Para interactuar con tus bases de datos usando SQLAlchemy, necesitas crear un engine para el RDBMS donde estén almacenadas. En tu caso, PostgreSQL. SQLAlchemy te permite crear el engine en una sola llamada a create_engine(), que recibe una URL de conexión a la base de datos, como ya has visto.

from sqlalchemy import create_engine
Con la tecnología de

engine = create_engine('postgresql://postgres:postgres@localhost:5432/DataCamp_Courses')
print(engine.table_names()) # Te permite ver los nombres de las tablas presentes en la base de datos
Con la tecnología de

['datacamp_courses']Con la tecnología de

Puedes ver la tabla _datacampcourses, lo que confirma que el engine se creó correctamente. Ejecutemos una consulta select sencilla para ver los registros de la tabla _datacampcourses y guardarlos en un DataFrame de pandas.

Usarás el método read_sql() (de pandas), que recibe una cadena SQL y un engine.

import pandas as pd

df = pd.read_sql('select * from datacamp_courses', engine)
df.head()
Con la tecnología de

También puedes combinar el comando mágico %sql dentro de tu código Python.

df_new = %sql select * from datacamp_courses
df_new.DataFrame().head()
Con la tecnología de

 * postgresql://postgres:***@localhost:5432/DataCamp_Courses
1 rows affected. Con la tecnología de

¡Has llegado muy lejos!

¡Enhorabuena, date una palmada en la espalda! En este tutorial no solo has empezado con PostgreSQL, sino que también has visto cómo ejecutar consultas SQL de varias formas. Como siguiente paso, te recomendamos ver este webinar de David Robinson (Chief Data Scientist en DataCamp), donde muestra cómo usa SQL en su trabajo diario. Échale un vistazo a nuestro tutorial Cómo instalar PostgreSQL en Windows y Mac OS X. Si quieres mejorar tus habilidades en SQL, estos son muy buenos recursos para empezar:

• Intro to SQL for Data Science
• SQL by Kaggle Learn
• Joining Data in SQL