Python Conexión Postgresql

ejemplo simple:

import psycopg2
 
cnx = psycopg2.connect( user="<usuario>",
                        password="<password>",
                        host="<host>",
                        port="<port>",
                        database="<db>" )
 
print("Informarción PostgreSQL Server:")
print(cnx.get_dsn_parameters(), "\n")

Ejemplo simple con Try Catch:

import psycopg2
 
try:
 
  cnx = psycopg2.connect( user="<usuario>",
                        password="<password>",
                        host="<host>",
                        port="<port>",
                        database="<db>" )
 
  print("Informarción PostgreSQL Server:")
  print(cnx.get_dsn_parameters(), "\n")
 
except:
 
  print("No se pudo conectar")

• https://pynative.com/python-postgresql-tutorial/
• https://cloud.google.com/spanner/docs/pg-psycopg2-connect?hl=es-419
• https://www.datacamp.com/tutorial/tutorial-postgresql-python
• https://www.psycopg.org/docs/usage.html
• https://wiki.postgresql.org/wiki/Psycopg2_Tutorial

import psycopg2
 
try:
 
  cnx = psycopg2.connect( user="<usuario>",
                        password="<password>",
                        host="<host>",
                        port="<port>",
                        database="<db>" )
 
  print("Informarción PostgreSQL Server:")
  print(cnx.get_dsn_parameters(), "\n")
 
  cur = cnx.cursor()
  cur.execute("SELECT * FROM usuarios")
 
 
  row = cur.fetchone() #<-- FetchOne
  while row is not None:
    print( row )
    row = cur.fetchone()
 
 
except (Exception, psycopg2.DatabaseError) as error:
 
  print(error)
 

import psycopg2
 
try:
 
  cnx = psycopg2.connect( user="<usuario>",
                        password="<password>",
                        host="<host>",
                        port="<port>",
                        database="<db>" )
 
  print("Informarción PostgreSQL Server:")
  print(cnx.get_dsn_parameters(), "\n")
 
  cursor = cnx.cursor()
  cursor.execute("insert into usuarios( usuario, password, nombrecompleto) values('ABAN3', 'EBEN', 'ABAN BANBIN')")
 
  cnx.commit()
 
except:
 
  print("No se pudo conectar")

• https://stackoverflow.com/questions/19235686/psycopg2-insert-into-table-with-placeholders
• https://neon.com/postgresql/postgresql-python/insert

auto-commit:

con.autocommit = TRUE

Store procedure:

• https://www.geeksforgeeks.org/postgresql/how-to-write-a-normal-select-procedure-in-postgresql/
• https://neon.com/postgresql/postgresql-python/postgresql-python-call-postgresql-functions

Existen dos formas:

• cursor.fetchone()
• cursor.fetchall()

 row = cur.fetchone()
 
  while row is not None:
    print( row[0] )
    row = cur.fetchone()

• https://neon.com/postgresql/postgresql-python/query

# 1. Instalar librerías
!pip install psycopg2-binary sqlalchemy
 
# 2. Importar librerías
import psycopg2
import pandas as pd
from sqlalchemy import create_engine
 
# 3. Configurar parámetros de conexión
host = 'localhost'
dbname = 'tu_base_de_datos'
user = 'tu_usuario'
password = 'tu_password'
port = '5432'
 
# 4. Crear conexión con SQLAlchemy (recomendado para pandas)
engine = create_engine(f'postgresql://{user}:{password}@{host}:{port}/{dbname}')
 
# 5. Leer datos con pandas
query = "SELECT * FROM tu_tabla LIMIT 10;"
df = pd.read_sql(query, engine)
print(df)

Backups Automáticos:

• https://wiki.debian.org/PostgreSql