Uso de PostgreSQL en Python

Los datos están en el corazón de casi todas las aplicaciones digitales que pueda imaginar. Desde aplicaciones móviles y juegos de ordenador hasta herramientas bancarias y vehículos autónomos, todo se basa en datos. ¿Dónde se almacenan todos estos datos? La respuesta son las bases de datos. 

Una base de datos es una colección organizada de datos estructurados, normalmente almacenados electrónicamente en un sistema informático. A pesar del reciente desarrollo de nuevos tipos de bases de datos (diseñadas para hacer frente al creciente volumen y variabilidad de los datos), una cantidad considerable de datos en todo el mundo sigue almacenándose en lo que se conoce como bases de datos relacionales. Las bases de datos relacionales almacenan los datos como colecciones de tablas predefinidas con filas y columnas que están conectadas a través de una o varias relaciones. 

Ser capaz de manejar y extraer datos de estas bases es una habilidad esencial en el sector de los datos y cada vez más demandada. La forma estándar de crear y gestionar bases de datos es SQL (Structured Query Language). SQL es la base de algunas de las bases de datos relacionales más populares del mercado, como PostgreSQL, Microsoft SQL Server, MySQL y SQLite.

En este tutorial, nos centraremos en las bases de datos basadas en PostgreSQL y en cómo puedes crearlas, conectarte a ellas y gestionarlas utilizando Python. El tándem SQL-Python es una de las habilidades imprescindibles que debes dominar a lo largo de tu viaje por la ciencia de datos. Para conectar Python con PostgreSQL, utilizaremos el paquete psycopg2, el adaptador de bases de datos PostgreSQL más popular para Python. ¿Listo para el reto? Empecemos.

Comprender PostgreSQL

PostgreSQL es una base de datos relacional ligera, gratuita y de código abierto. Gracias a su probada arquitectura, fiabilidad, integridad de los datos y fácil integración con otros lenguajes de programación populares, como Python y R, PostgreSQL goza de una gran aceptación en el sector, con empresas de todos los tamaños y regiones que lo utilizan. 

PostgreSQL utiliza y amplía el lenguaje SQL combinado con muchas funciones que almacenan y escalan con seguridad las cargas de trabajo de datos más complicadas. 

Para empezar a utilizar PostgreSQL, primero tienes que instalarlo en tu ordenador. En el sitio oficial de PostgreSQL, puede encontrar los numerosos paquetes e instaladores de PostgreSQL listos para usar y un archivo de código fuente disponible para diferentes plataformas y casos de uso.

Para este tutorial, vamos a utilizar Postgres App, una sencilla aplicación nativa de macOS que incluye todas las características necesarias para empezar con PostgreSQL, pero tenga en cuenta que todos los demás paquetes disponibles son igualmente válidos, y la sintaxis de PostgreSQL para ejecutar consultas es la misma en todos los paquetes.

Si quieres saber más sobre PostgreSQL, consulta nuestra Guía de PostgreSQL para principiantes y el buen número de cursos de SQL.

Comprender psycopg2

Para conectarte a una base de datos ya creada en tu sistema o en Internet, tendrás que indicarle a Python cómo detectarla. En otras palabras, tendrás que decirle a Python que la base de datos que te interesa es una base de datos PostgreSQL. 

En Python, tienes varias opciones entre las que puedes elegir. En este caso, utilizaremos psycopg2, probablemente el adaptador de base de datos PostgreSQL más popular para Python. Psycopg2 requiere algunos requisitos previos para funcionar correctamente en su ordenador. Una vez que los haya instalado (lea la documentación para más información), puede instalar psycopg2 como cualquier otro paquete Python:

pip install psycopg2

Sin embargo, si desea utilizar psycopg2 directamente, también puede instalar psycopg2-binary, una versión independiente del paquete, que no requiere compilador ni bibliotecas externas. Esta es la instalación preferida para los nuevos usuarios.

pip install psycopg2-binary

Por último, si está utilizando Python en un entorno Conda, debe instalar psycopg2 utilizando el comando Instalación en Anaconda:

conda install -c anaconda psycopg2

Ahora que ya está todo listo, ¡vamos a crear su primera conexión a su sesión PostgreSQL con psycopg2!

Conexión de Python a PostgreSQL

Con el fin de utilizar Python para interactuar con una base de datos PostgreSQL, tenemos que hacer una conexión. Esto se hace con la función psycopg2 connect(), que crea una nueva sesión de base de datos y devuelve una nueva instancia de conexión.

Para este tutorial, nos conectaremos con una base de datos llamada "datacamp_courses" que está alojada localmente. 

conn = psycopg2.connect(database = "datacamp_courses", 
                        user = "datacamp", 
                        host= 'localhost',
                        password = "postgresql_tutorial",
                        port = 5432)

Los parámetros básicos de conexión necesarios son:

• base de datos. El nombre de la base de datos.
• usuario. Nombre de usuario necesario para autenticarse.
• contraseña. Contraseña utilizada para autenticarse.
• anfitrión. Dirección del servidor de la base de datos (en nuestro caso, la base de datos está alojada localmente, pero podría ser una dirección IP).
• puerto. Número de puerto de conexión (por defecto 5432 si no se indica).

Creación de una tabla en PostgreSQL

Es hora de crear su 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. Laclave primaria de la tabla debe ser course_id (observe que sólo ésta está en negrita), y su tipo de datos debe ser un entero. Una clave primaria es una restricción que obliga a que los valores de las columnas no sean nulos y sean únicos. Permite identificar de forma unívoca una instancia 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 permite ejecutar comandos y consultas SQL, como SELECT, INSERT, CREATE, UPDATE, OR DELETE, utilizando el método cursor(), y hacer persistentes los cambios utilizando el método commit() .

Una vez creada la instancia del cursor, puede enviar comandos a la base de datos utilizando la función ejecutar() y recuperar datos de una tabla utilizando fetchone(), fetchmany()o fetchall().

Por último, es importante cerrar 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, puede utilizar elmétodo close() .

A continuación encontrará 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 desea obtener más información 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, consulte nuestro artículo Creando una Base de Datos PostgreSQL curso.

Ejecución de consultas básicas PostgreSQL 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:

SELECCIONE 

La lectura de datos en bases de datos SQL es probablemente algo que harás mucho en tu viaje por la ciencia de datos. Generalmente se denomina consulta a SELECT. De momento, veamos cómo se comporta la tabla datacamp_courses.

Llamaremos a la clásica sentencia SELECT * FROM nombre_base_de_datos para leer todos los datos disponibles en la tabla. A continuación, utilizaremos el método fetchall() para obtener todas las filas disponibles. Observe 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 venir con errores. Habrás notado en la sección anterior que el tema asociado al curso "Introducción a SQL"es Julia. Tras comprobar la información sobre el curso, descubrimos el error. Hay que cambiarlo y escribir "SQL" en su lugar. Esto puede hacerse con la sentencia 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 desee eliminar uno de los registros de la tabla. Por ejemplo, eliminemos 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

Digamos que desea ordenar su base de datos por el nombre del instructor. Puede 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

Es posible que desee realizar algunas funciones agregadas dentro de diferentes grupos de datos. Por ejemplo, puede interesarle calcular el número de cursos impartidos por los distintos instructores de los cursos. Puede realizar este tipo de operaciones 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. Pero sólo se empieza a aprovechar todo el potencial de las bases de datos relacionales, como PostgreSQL, cuando se trabaja con varias tablas a la vez. 

La herramienta mágica para combinar varias tablas es la operación JOIN. Imaginemos 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:

Imagine que desea fusionar las dos tablas para obtener la información de los cursos, junto con la posición del idioma 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 los JOINS de SQL es infinito, lo que lo convierte en un tema imprescindible. Nuestro curso, tutorialy hoja de trucos sobre JOINS le 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 en comparación con psycopg2, puede hacer prácticamente lo mismo. A continuación encontrará un ejemplo para crear un motor que se conecte con nuestro conjunto de datos datacamp_courses y lea 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 hacerse una idea de cómo funciona la herramienta, eche un vistazo a nuestro tutorial de SQLALchemy.  

Uso de PostgreSQL en DataCamp Workspace

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 Workspace, un cuaderno de notas en la nube diseñado específicamente para la ciencia de datos. Puedes pensar en DataCamp Workspace como una mezcla entre Jupyter Notebook y Google Docs, que permite a los usuarios escribir código, analizar datos de forma individual o colectiva y compartir información.

Conectar Workspace a bases de datos PostgreSQL, así como a otras bases de datos SQL, es extremadamente fácil. Workspace le permite conectarse a fuentes de datos de forma segura, mientras puede continuar sin problemas su análisis de datos con Python o R. Sólo tiene que crear una integración, siguiendo un proceso sencillo, sin código, en el que tendrá que introducir sus credenciales de conexión. 

La siguiente imagen muestra cómo crear una integración PostgreSQL. 

Por último, si desea probar la integración pero no dispone de una base de datos PostgreSQL a la que conectarse, puede utilizar una de nuestras bases de datos PostgreSQL de muestra disponibles.

¿Tiene curiosidad por saber más sobre DataCamp Workspace? Echa un vistazo a 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 familiarizaste con PostgreSQL y aprendiste a manejar bases de datos sin salir de Python, usando 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 de Mejora del rendimiento de las consultas en PostgreSQL
• Curso de Gestión de Bases de Datos en SQL
• Curso de Funciones para Manipular Datos en PostgreSQL