¿Cómo definir una clave primaria de incremento automático en PostgreSQL usando Python?

Requisito previo : PostgreSQL

Python tiene varios controladores de bases de datos para PostgreSQL. Actualmente, la versión más utilizada es psycopg2 porque implementa completamente la especificación Python DB-API 2.0. El psycopg2 proporciona muchas funciones útiles, como cursores del lado del cliente y del lado del servidor, notificación y comunicación asíncronas, compatibilidad con el comando COPY, etc.

Instalación

psycopg2 se puede descargar como cualquier otro módulo usando el siguiente comando:

pip install psycopg2

Acercarse

La forma de PostgreSQL de crear una clave principal con función de incremento automático:

Una columna debe definirse con SERIAL PRIMARY KEY. Aquí SERIAL no es un tipo de datos verdadero, sino que es simplemente una notación abreviada que le dice a Postgres que cree un identificador único incrementado automáticamente para la columna especificada. Simplemente configurando una columna como SERIAL con la CLAVE PRIMARIA adjunta, Postgres manejará todo el complicado trabajo detrás de escena e incrementará automáticamente nuestra columna especificada con un valor de clave principal único para cada INSERCIÓN.

Información de la base de datos

Nombre de la base de datos: testdb

Nombre de la tabla: EMPLEADO

En la TABLA DE EMPLEADOS, la columna denominada EMPLOYEE_ID se implementará como una columna de clave principal de incremento automático. 

Sintaxis:

CREAR TABLA <nombre_tabla>(

<column1_name> CLAVE PRINCIPAL SERIE NOT NULL,

.

.

);

La implementación de la creación de una tabla con dicha especificación se proporciona a continuación:

import psycopg2
  
  
def create_table():
    conn = None
    try:
        # connect to the PostgreSQL server
        conn = psycopg2.connect(database="testdb", user="postgres",
                                password="password", host="127.0.0.1", port="5432")
        print("Opened database successfully")
        # create a cursor
        cursor = conn.cursor()
        # Droping EMPLOYEE table if already exists.
        cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
          
        # Creating table as per requirement, let us have EMPLOYEE table
        # and in order to have auto increment primary key, EMPLOYEE_ID SERIAL PRIMARY KEY
        # is used and it is explained before code
        sql = '''CREATE TABLE EMPLOYEE(
           EMPLOYEE_ID SERIAL PRIMARY KEY,
           FIRST_NAME CHAR(20) NOT NULL,
           LAST_NAME CHAR(20),
           AGE INT,
           SEX CHAR(1),
           INCOME FLOAT
        )'''
        cursor.execute(sql)
        print("Table created successfully........")
          
        # close communication with the PostgreSQL database server
        cursor.close()
          
        # commit the changes
        conn.commit()
          
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()
  
  
if __name__ == '__main__':
    create_table()

Podemos ver la tabla creada usando la herramienta pgadmin

Ahora, se debe realizar la inserción para ver si nuestra función de incremento automático funciona o no. Esto se puede hacer directamente a través de pgadmin o usando el código python.

manera pgadmin:

A continuación se muestra la captura de pantalla que muestra la ejecución de consultas de inserción y el conjunto de resultados resultante.

Explicación de la clave principal de incremento automático

Usando código python:

import psycopg2
try:
    connection = psycopg2.connect(user="postgres",
                                  password="password",
                                  host="127.0.0.1",
                                  port="5432",
                                  database="testdb")
      
    cursor = connection.cursor()
      
    # As Employee table is having auto incremented primary id column(employee_id), no need to specify about that value here
    postgres_insert_query = ''' INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, AGE,SEX,INCOME) VALUES (%s,%s,%s,%s,%s)'''
    record_to_insert = ('asd', 'wer', 19, 'f', 5000)
    cursor.execute(postgres_insert_query, record_to_insert)
  
    connection.commit()
    count = cursor.rowcount
    print(count, "Record inserted successfully into Employee table")
  
except (Exception, psycopg2.Error) as error:
    if(connection):
        print("Failed to insert record into Employee table", error)
  
finally:
    # closing database connection.
    if(connection):
        cursor.close()
        connection.close()
        print("PostgreSQL connection is closed")

Salida de la tabla de empleados después de ejecutar el programa anterior: