====== Python Conexión Postgresql ======

===== Psycopg2 =====

===== Python : psycopg2 : conexión : ejemplo simple =====

ejemplo simple:
<code python>
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")

</code>

Ejemplo simple con Try Catch:
<code python>
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")

</code>




  * [[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]]


===== Python : psycopg2 : fetchone : ejemplo simple =====

<code python>

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)
  
</code>

===== Python : psycopg2 : fetchall : ejemplo simple =====






===== Python : psycopg2 : ejecutar : ejemplo simple =====

<code python>
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")


</code>

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

auto-commit:
<code sql>
con.autocommit = True
</code>

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]]

===== Leer tabla =====

Existen dos formas:

  * cursor.fetchone()
  * cursor.fetchall()

<code python>
 row = cur.fetchone()

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

<code python>

</code>

  * [[https://neon.com/postgresql/postgresql-python/query]]

===== Ejemplos psycopg2 =====

<code python>

# 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)

</code>


Backups Automáticos:
  * [[https://wiki.debian.org/PostgreSql]]