Gestionar bases de datos PostgreSQL en Python con psycopg2

Crear una tabla en PostgreSQL

Es hora de crear tu primera tabla en la base de datos datacamp_courses. Queremos crear una tabla con información sobre algunos de los cursos del catálogo de cursos de DataCamp. La tabla tiene el siguiente esquema: 

La especificación nos da bastante información sobre las columnas de la tabla. La clave principal de la tabla debe ser course_id, y su tipo de datos debe ser un número entero. Una clave primaria es una restricción que obliga a que los valores de las columnas no sean nulos y sean únicos. Te permite identificar de forma única una instancia concreta o un conjunto de instancias presentes en la tabla. 

Las columnas restantes proporcionan información sobre el nombre del curso, el nombre de la instrucción del curso y el tema del curso.

Antes de crear la tabla, es importante explicar cómo funciona la instancia de conexión que acabas de crear. En esencia, la conexión encapsula una sesión de base de datos, y te permite ejecutar comandos y consultas SQL, como SELECT, INSERT, CREATE, UPDATE, o DELETE, utilizando el método cursor(), y hacer cambios persistentes utilizando el método commit() .

Una vez creada la instancia del cursor, puedes enviar comandos a la base de datos utilizando el método execute() y recuperar datos de una tabla utilizando fetchone(), fetchmany()o fetchall().

Por último, es importante que cierres el cursor y la conexión con la base de datos cuando hayas terminado tus operaciones. De lo contrario, seguirán reteniendo recursos del lado del servidor. Para ello, puedes utilizar elmétodo close() .

A continuación encontrarás el código para crear la tabla datacamp_courses:

# Open a cursor to perform database operations
cur = conn.cursor()
# Execute a command: create datacamp_courses table
cur.execute("""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);
            """)
# Make the changes to the database persistent
conn.commit()
# Close cursor and communication with the database
cur.close()
conn.close()

Este es un ejemplo muy básico de cómo crear tablas en PostgreSQL, pero las cosas pueden ser mucho más complejas. Si quieres saber más sobre cómo crear una base de datos PostgreSQL y explorar la estructura, los tipos de datos y cómo normalizar las bases de datos, consulta nuestro artículo Creación de bases de datos PostgreSQL.

Ejecutar consultas PostgreSQL básicas en Python

La tabla datacamp_courses está lista; ¡ahora es el momento de utilizar SQL para realizar algunas consultas básicas!

INSERTAR 

Te habrás dado cuenta de que la tabla no tiene valores hasta ahora. Para crear registros en la tabla datacamp_courses, necesitamos el comando INSERT.

cur = conn.cursor()

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Introduction to SQL','Izzy Weber','Julia')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Analyzing Survey Data in Python','EbunOluwa Andrew','Python')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Introduction to ChatGPT','James Chapman','Theory')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Introduction to Statistics in R','Maggie Matsui','R')");

cur.execute("INSERT INTO datacamp_courses(course_name, course_instructor, topic) VALUES('Hypothesis Testing in Python','James Chapman','Python')");

conn.commit()
cur.close()
conn.close()

La tabla resultante tiene este aspecto:

SELECCIONA 

Leer datos en bases de datos SQL es probablemente algo que harás mucho en tu viaje por la ciencia de datos. A esto se le suele llamar consulta SELECT. De momento, veamos cómo se mantiene la mesa datacamp_courses.

Llamaremos a la sentencia clásica SELECT * FROM database_name para leer todos los datos disponibles en la tabla. A continuación, utilizaremos el método fetchall() para obtener todas las filas disponibles. Observa que PostgreSQL crea automáticamente un índice numérico para la columna course_id .

cur = conn.cursor()
cur.execute('SELECT * FROM datacamp_courses;')
rows = cur.fetchall()
conn.commit()
conn.close()
for row in rows:
    print(row)

(1, 'Introduction to SQL', 'Izzy Weber', 'Julia')
(2, 'Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python')
(3, 'Introduction to ChatGPT', 'James Chapman', 'Theory')
(4, 'Introduction to Statistics in R', 'Maggie Matsui', 'R')
(5, 'Hypothesis Testing in Python', 'James Chapman', 'Python')

ACTUALIZACIÓN 

Los datos suelen contener errores. Habrás observado en la sección anterior que el tema asociado al curso "Introducción a SQLes "Julia". Tras comprobar la información sobre el curso, descubrimos el error. Tenemos que cambiarlo y escribir "SQL" en su lugar. Esto puede hacerse con la declaración UPDATE, de la siguiente manera:

cur = conn.cursor()
cur.execute("UPDATE datacamp_courses SET topic = 'SQL' WHERE course_name = 'Introduction to SQL';")
conn.commit()
conn.close()

BORRAR 

Por último, puede que quieras eliminar uno de los registros de tu tabla. Por ejemplo, vamos a eliminar el curso Introducción a la Estadística en R:

cur = conn.cursor()
cur.execute("""DELETE from datacamp_courses WHERE course_name = 'Introduction to Statistics in R'""");
conn.commit()
cur.close()

Consultas PostgreSQL avanzadas en Python

En la sección anterior, hemos examinado las consultas SQL más básicas. Pero hay mucho que aprender. Veamos algunas consultas más avanzadas.

ORDENAR POR

Supongamos que quieres ordenar tu base de datos por el nombre del instructor. Puedes utilizar la declaración ORDER BY:

cur = conn.cursor()

cur.execute('SELECT * FROM datacamp_courses ORDER BY course_instructor')
rows = cur.fetchall()
for row in rows:
    print(row)

(2, 'Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python')
(1, 'Introduction to SQL', 'Izzy Weber', 'SQL')
(3, 'Introduction to ChatGPT', 'James Chapman', 'Theory')
(4, 'Hypothesis Testing in Python', 'James Chapman', 'Python')

GRUPO POR

Puede que quieras realizar algunas funciones agregadas dentro de diferentes grupos de datos. Por ejemplo, puede interesarte calcular el número de cursos de los distintos instructores de cursos. Puedes hacer este tipo de operación con la función GROUP BY.

cur = conn.cursor()
cur.execute('SELECT course_instructor, COUNT(*) FROM datacamp_courses GROUP BY course_instructor')
rows = cur.fetchall()
for row in rows:
    print(row)

('James Chapman', 2)
('Izzy Weber', 1)
('EbunOluwa Andrew', 1)

JOIN

Hasta ahora, sólo hemos trabajado con la tabla datacamp_course. Sin embargo, sólo empiezas a aprovechar todo el potencial de las bases de datos relacionales, como PostgreSQL, cuando trabajas con varias tablas a la vez. 

La herramienta mágica para combinar varias tablas es la operación JOIN. Imagina que tenemos una segunda tabla en nuestra base de datos llamada programming_languages que contiene información básica sobre los principales lenguajes de programación para la ciencia de datos, incluyendo el nombre, la posición en el Índice TIOBE y el número de cursos sobre el lenguaje de programación en Datacamp. La tabla tiene este aspecto:

Imagina que quieres fusionar las dos tablas para obtener la información de los cursos, junto con la posición de la lengua en el índice TIOBE. Utilizaremos un INNER JOIN para obtener sólo la información de los lenguajes de programación que aparecen en la tabla datacamp_course. 

cur = conn.cursor()
cur.execute("""SELECT course_name, course_instructor, topic, tiobe_ranking
FROM datacamp_courses
INNER JOIN programming_languages
ON datacamp_courses.topic = programming_languages.language_name""")
rows = cur.fetchall()
for row in rows:
    print(row)

('Introduction to SQL', 'Izzy Weber', 'SQL', 8)
('Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python', 1)
('Hypothesis Testing in Python', 'James Chapman', 'Python', 1)

Éste es sólo un ejemplo, pero el potencial de las uniones SQL es infinito, por lo que es un tema que debes conocer. Nuestro curso, tutorialy hoja de trucos en joins te ayudarán a empezar.

Primeros pasos con SQLAlchemy

Psycopg2 es una gran herramienta para conectar con bases de datos PostgreSQL. Pero, ¿qué ocurre si tienes que trabajar con bases de datos alojadas en diferentes bases de datos SQL, como Oracle y MySQL? En estos casos, puede ser mejor utilizar una herramienta que pueda conectarse a distintos tipos de bases de datos SQL. Para ello, SQLAlchemy es un gran candidato. 

En palabras sencillas, SQLAlchemy permite a los usuarios conectar bases de datos utilizando lenguaje Python, ejecutar consultas SQL mediante programación basada en objetos y agilizar el flujo de trabajo. 

Aunque la sintaxis varía con respecto a psycopg2, puedes hacer prácticamente lo mismo. A continuación puedes encontrar un ejemplo sobre cómo crear un motor para conectar con nuestros conjuntos de datos datacamp_courses, y leer todos los datos disponibles en la tabla datacap_course

import sqlalchemy as db


engine = db.create_engine("postgresql://datacamp@localhost:5432/datacamp_courses")
conn = engine.connect() 
output = conn.execute("SELECT * FROM datacamp_courses")
print(output.fetchall())
conn.close()

[(3, 'Introduction to ChatGPT', 'James Chapman', 'Theory'),
 (5, 'Hypothesis Testing in Python', 'James Chapman', 'Python'),
 (2, 'Analyzing Survey Data in Python', 'EbunOluwa Andrew', 'Python'),
 (1, 'Introduction to SQL', 'Izzy Weber', 'SQL')]

SQLAlchemy es otra gran herramienta que merece la pena aprender. Para hacerte una idea de cómo funciona la herramienta, echa un vistazo a nuestro Tutorial de SQLALchemy.  

Utilizar PostgreSQL en DataLab de DataCamp

En DataCamp, trabajamos duro para que la ciencia de datos sea más fácil y accesible para todos. Esa fue la razón que impulsó la creación de DataCamp DataLab, un cuaderno basado en la nube diseñado específicamente para la ciencia de datos. Puedes pensar en DataCamp DataLab como una mezcla de Jupyter Notebook y Google Docs, que permite a los usuarios escribir código, analizar datos individual o colectivamente, y compartir ideas sobre los datos.

Conectar DataLab a bases de datos PostgreSQL, así como a otras bases de datos SQL, es extremadamente fácil. DataLab te permite conectarte a fuentes de datos de forma segura. Al mismo tiempo, puedes continuar sin problemas tus análisis de datos con Python o R. Sólo tienes que crear una integración, siguiendo un proceso sencillo, sin código, en el que tendrás que introducir tus credenciales de conexión.

Por último, si quieres probar la integración pero no tienes una base de datos PostgreSQL a la que conectarte, ¡puedes utilizar una de nuestras bases de datos PostgreSQL de ejemplo!

¿Tienes curiosidad por el DataCamp DataLab? Consulta este artículo para saber más sobre este portátil de nueva generación para la ciencia de datos.

Conclusión

¡Buen trabajo! En este tutorial, te has familiarizado con PostgreSQL y has aprendido a gestionar bases de datos sin salir de Python, utilizando el paquete psycopg2. Pero esto es sólo el principio del viaje. SQL es uno de los lenguajes de programación más utilizados en la ciencia de datos. Además, es bastante fácil de aprender, así que no tienes excusa.  

Afortunadamente, Datacamp te cubre. Aquí tienes algunos recursos que pueden ayudarte a dominar PostgreSQL y SQL en general:

• Tutorial de introducción a PostgreSQL para principiantes
• Curso de Creación de Bases de Datos PostgreSQL
• Curso Mejora del rendimiento de las consultas en PostgreSQL
• Curso de Gestión de Bases de Datos en SQL
• Curso Funciones para Manipular Datos en PostgreSQL