Instalación de Postgres y Anaconda

En este cuaderno se mostrará cómo conectarse a una base de datos PostgreSQL a usando un cuaderno de Jupyter y el lenguaje Python.

Se requiere instalar:

1. El Sistema de Gestión de Bases de Datos Postgres versión 10.14. Para esto descargar de la página oficial de Postgres el instalador que corresponda con su sistema operativo. Luego ejecutar el instalador, se siguen las instrucciones, prestar atención a seleccionar todos los componentes menos “Stack Builder”

Luego, cuando se lo solicite, ingrese una contraseña que usted pueda recordar, por ejemplo 1234. Finalmente, deje el puerto por defecto 5432 y la configuración regional por defecto.

2. El kit de herramientas de ciencia de datos, Anaconda. Descargar de la sección “Anaconda Installers” el instalador que corresponda página oficial Anaconda según su sistema operativo. La instalación es relativamente sencilla, se debe ejecutar el instalador y seguir las instrucciones.

Verificación de la Instalación

1. Conectarse mediante la SQL Shell y ejecutar el comando: select version();

2. Conectarse mediante pgAdmin y ejecutar: Select version();

Conexión de Postgres mediante Python

Además, se requiere instalar dos paquetes * SQLAlchemy : el conjunto de herramientas Python SQL y Object Relational Mapper que brinda a los desarrolladores de aplicaciones todo el poder y la flexibilidad de SQL. Tutorial Recomendado * Psycopg2: es el adaptador de sistema de gestión de base de datos PostgreSQL para el lenguaje de programación Python.

#Instalamos los paquetes
!pip install sqlalchemy psycopg2

Requirement already satisfied: sqlalchemy in c:\users\fernan\anaconda3\lib\site-packages (1.3.20)
Requirement already satisfied: psycopg2 in c:\users\fernan\anaconda3\lib\site-packages (2.8.6)

# Importamos las librerias correspondientes
import pandas as pd
import psycopg2
import sqlalchemy
import matplotlib as plt

%matplotlib inline

A continuación, configuraremos la conexión a nuestra base de datos de Postgres. Para conectarse, necesitará:

• El nombre de host o la dirección IP donde se encuentra la base de datos

• El puerto que escucha su base de datos (este valor predeterminado es 5439 para Postgres)

• Su nombre de usuario

• La contraseña

• El nombre de la base de datos

from sqlalchemy import create_engine

POSTGRES_ADDRESS = 'localhost' # Este es el servidor, puede ser una IP
POSTGRES_PORT = '5432'
POSTGRES_USERNAME = 'postgres'
POSTGRES_PASSWORD = '1234'
POSTGRES_DBNAME = 'postgres'

# Ahora se configura la cadena de conexión.
# Esta es una cadena que contiene los parámetros necesarios para establecer una
# conexión con posgres.
postgres_str = ('postgresql://{username}:{password}@{ipaddress}:{port}/{dbname}'.format(username=POSTGRES_USERNAME,
                                                                                        password=POSTGRES_PASSWORD,
                                                                                        ipaddress=POSTGRES_ADDRESS,
                                                                                        port=POSTGRES_PORT,
                                                                                        dbname=POSTGRES_DBNAME))
# A continuación se crea la conexión
cnx = create_engine(postgres_str)

# Se ejecuta una sentencia de selección y su resultado se almacena un DataFrame
resultado = pd.read_sql_query('SELECT version();', cnx)
print(type(resultado))

resultado

<class 'pandas.core.frame.DataFrame'>

	version
0	PostgreSQL 10.15, compiled by Visual C++ build...

Cargar una copia de respaldo

• Descargar la copia de respaldo desde aquí. Luego descomprimirla en una carpeta cuya ruta sea de fácil acceso.

• El Modelo de Datos está disponible aquí.

• El Tutorial Original se puede consultar aquí

Es posible por pgAdmin o por Consola. A continuación, los pasos por consola.

1. En caso de lo haberlo hecho. Agregar a las variables de entorno del sistema la carpeta donde se ha instalado Postgres: C:\Program `Files:nbsphinx-math:PostgreSQL`\10:nbsphinx-math:bin

2. Conectarse a la consola de posgres y Ejecutar el siguiente comando:

psql -h localhost -U postgres -d postgres -p 5432

3. Crear una nueva base de datos, dentro de la consola de posgres:

postgres=# CREATE DATABASE dvdrental;

4. Salir de la consola de postgres con ctr+c, y ejecutar:

pg_restore -U postgres -d dvdrental C:\mineria\alquiler.tar

5. Observación: Para crear una copia de respaldo en formato tar comprimido usar: pg_dump --file "C:\\mineria\\prueba.sql" --host "localhost" --port "5432" --username "postgres" --no-password --verbose --format=t --blobs "dvdrental"

Documentación sobre las copias de respaldo aquí.

6. En pgAdmin se puede explorar el contenido de la base de datos de alquiler de películas.

Importar y Exportar datos CSV en Postgres

• Es posible mediante consola o usando PgAdmin

from sqlalchemy import create_engine

POSTGRES_ADDRESS = 'localhost' # Este es el servidor, puede ser una IP
POSTGRES_PORT = '5432'
POSTGRES_USERNAME = 'postgres'
POSTGRES_PASSWORD = '1234'
POSTGRES_DBNAME = 'dvdrental'

# Ahora se configura la cadena de conexión.
# Esta es una cadena que contiene los parámetros necesarios para establecer una
# conexión con posgres.
postgres_str = ('postgresql://{username}:{password}@{ipaddress}:{port}/{dbname}'.format(username=POSTGRES_USERNAME,
                                                                                        password=POSTGRES_PASSWORD,
                                                                                        ipaddress=POSTGRES_ADDRESS,
                                                                                        port=POSTGRES_PORT,
                                                                                        dbname=POSTGRES_DBNAME))
# A continuación se crea la conexión
cnx = create_engine(postgres_str)

sql = '''
DROP TABLE IF EXISTS personas;

CREATE TABLE personas (
  id SERIAL,
  nombre VARCHAR(50),
  apellido VARCHAR(50),
  nacimiento DATE,
  correo VARCHAR(255),
  PRIMARY KEY (id)
)
'''

cnx.execute(sql)

<sqlalchemy.engine.result.ResultProxy at 0x1f3e3a5bdc0>

%%writefile personas.csv
nombre,apellido,nacimiento,correo
Ramiro,Osorio,1995-01-05,ramiruit@gmail.com
Mary,Duarte,1995-02-05,marye@gmail.com

Overwriting personas.csv

Importar Datos

Conectarse a la consola de posgres con el siguiente comando:

psql -h localhost -U postgres -d dvdrental -p 5432

y luego ejecutar

\COPY personas(nombre, apellido, nacimiento, correo) FROM 'C:\Users\Fernan\personas.csv' DELIMITER ',' CSV HEADER;

sql = '''
Select * from Personas;
'''

pd.read_sql_query(sql, cnx)

	id	nombre	apellido	nacimiento	correo
0	1	Ramiro	Osorio	1995-01-05	ramiruit@gmail.com
1	2	Mary	Duarte	1995-02-05	marye@gmail.com

Exportar datos

Supongamos que deseamos exportar los datos productos de la siguiente consulta

SELECT
       film_id as id_pelicula,
       title as titulo,
       (CASE
           WHEN length < 30 THEN 'corta'
           WHEN length < 90 THEN 'media'
           ELSE 'larga'
       END) as duracion
   FROM
       film

Primero, por facilidad y buena práctica se almacena la consulta en una VISTA.

sql = '''
    CREATE OR REPLACE VIEW las_pelis AS
    SELECT
        film_id as id_pelicula,
        title as titulo,
        (CASE
            WHEN length < 30 THEN 'corta'
            WHEN length < 90 THEN 'media'
            ELSE 'larga'
        END) as duracion
    FROM
        film
'''

cnx.execute(sql)

<sqlalchemy.engine.result.ResultProxy at 0x1f3e3a882e0>

# Verificamos el contenido de la vista
sql = '''
Select * from las_pelis;
'''

pd.read_sql_query(sql, cnx)

	id_pelicula	titulo	duracion
0	133	Chamber Italian	larga
1	384	Grosse Wonderful	media
2	8	Airport Pollock	media
3	98	Bright Encounters	media
4	1	Academy Dinosaur	media
...	...	...	...
995	996	Young Language	larga
996	997	Youth Kick	larga
997	998	Zhivago Core	larga
998	999	Zoolander Fiction	larga
999	1000	Zorro Ark	media

1000 rows × 3 columns

Finalmente en la consola de PLSQL ejecutar

\COPY ( select * from las_pelis) TO 'C:\\Users\\Fernan\\las_pelis_mias.csv' DELIMITER ',' CSV HEADER;