Delete multiple postgreSQL records with python

Tomm1s
October 25, 2022
164 views
0 votes
2 Answers

My problem is that when I try to delete records from a table in postgreSQL with python I can’t do it, the method I created deletes 1 by 1, but when I want to delete 2 or more at once I get an error, which is this,
TypeError: PersonaDAO.delete() takes 2 positional arguments but 3 were given

The method I create to delete is this one, inside the PersonaDAO class.
In addition I have the classes of connection, that has the method to connect to the bd and of cursor.
Person class that has the initializer method with the attributes (id_person, name, last name, email).

conexion
from logger_base import log
import psycopg2 as bd
import sys

class Conexion:
    _DATABASE = 'test_db'
    _USERNAME = 'postgres'
    _DB_PORT = '5432'
    _HOST = '127.0.0.1'
    _conexion = None
    _cursor = None

    @classmethod
    def obtenerConexion(cls):
        if cls._conexion is None:
            try:
                cls._conexion = bd.connect(host=cls._HOST,
                                            user=cls._USERNAME,
                                            port=cls._DB_PORT,
                                            database=cls._DATABASE)
                log.debug(f'Conexión exitosa: {cls._conexion}')
                return cls._conexion
            except Exception as e:
                log.error(f'Ocurrió una excepción al obtener la conexión: {e}')
                sys.exit()
        else:
            return cls._conexion

    @classmethod
    def obtenerCursor(cls):
        if cls._cursor is None:
            try:
                cls._cursor = cls.obtenerConexion().cursor()
                log.debug(f'Se abrió correctamente el cursor: {cls._cursor}')
                return cls._cursor
            except Exception as e:
                log.error(f'Ocurrió una excepción al obtener el cursor: {e}')
                sys.exit()
        else:
            return cls._cursor

if __name__ == '__main__':
    Conexion.obtenerConexion()
    Conexion.obtenerCursor()


----------------
Persona and methods get and set
from logger_base import log

class Persona:
    def __init__(self, id_persona=None, nombre=None, apellido=None, email=None):
        self._id_persona = id_persona
        self._nombre = nombre
        self._apellido = apellido
        self._email = email

    def __str__(self):
        return f'''
            Id Persona: {self._id_persona}, Nombre: {self._nombre},
            Apellido: {self._apellido}, Email: {self._email}
        '''

--------------------
class PersonaDAO:
    '''
    DAO (Data Access Object)
    CRUD (Create-Read-Update-Delete)
    '''
    _SELECCIONAR = 'SELECT * FROM persona ORDER BY id_persona'
    _INSERTAR = 'INSERT INTO persona(nombre, apellido, email) VALUES(%s, %s, %s)'
    _ACTUALIZAR = 'UPDATE persona SET nombre=%s, apellido=%s, email=%s WHERE id_persona=%s'
    _ELIMINAR = 'DELETE FROM persona WHERE id_persona = %s'


 @classmethod
    def eliminar(cls, persona):
        with Conexion.obtenerConexion():
            with Conexion.obtenerCursor() as cursor:
                valores = (persona.id_persona,)
                cursor.execute(cls._ELIMINAR, valores)
                log.debug(f'Objeto eliminado: {persona}')
                return cursor.rowcount

with this method I can delete one at a time, but not multiple ids at once.
`

Answers

i don’t know your framework, but i’d try this

the problem is in delete():

One way of doing it:

@classmethod
def delete (cls, *people):
    with Conexion.getConexion():
        with Conexion.getCursor() as cursor:
            count = 0
            for person in people:
                values = (person.id_person)
                cursor.execute(cls._DELETE, values)
                log.debug(f'object deleted: {persona}')
                count += cursor.rowcount
            return count

Better way (but i don’t know if your framework supports passing the list as a parameter):


_DELETE = 'DELETE FROM person WHERE id_person=ANY(%s)'

@classmethod
def delete (cls, *people):
    with Conexion.getConexion():
        with Conexion.getCursor() as cursor:
            values = [person.id_person for person in people]
            cursor.execute(cls._DELETE, values)
            log.debug(f'object deleted: {persona}')
            return cursor.rowcount

You did not say what Postgres Python driver you are using, but assuming psycopg2 you can do the following.

Using a test example table.

select * from animals where pk_animals < 24;
 pk_animals | cond | animal |             ts             
------------+------+--------+----------------------------
         16 | fair | heron  | 
          2 | good | eagle  | 
          3 | good | mole   | 
         22 | poor | not    | 
         23 | good | not    | 2022-09-21 14:54:22.987311
          1 | good | crow   | 

import psycopg2

con = psycopg2.connect("dbname=test user=postgres host=localhost port=5432") 
cur = con.cursor()
values = [16, 23]

Using psycopg2 execute functions:

for id_val in values:
    cur.execute("delete from animals where pk_animals = %s", [id_val])



# Doing basically same thing using executemany

values_many = [[16], [23]] 
cur.executemany("delete from animals where pk_animals = %s", values_many) 

# Using execute_batch which is more performant then executemany

from psycopg2.extras import execute_batch

execute_batch(cur, "delete from animals where pk_animals = %s", values_many) 

# All the above will result in:
cur.execute("select * from animals where pk_animals < 24")
cur.fetchall()

[(2, 'good', 'eagle', None),
 (3, 'good', 'mole', None),
 (22, 'poor', 'not', None),
 (1, 'good', 'crow', None)]

Using an array of ids:

cur.execute("delete from animals where pk_animals = ANY(%s)", [values])
cur.execute("select * from animals where pk_animals < 24")
cur.fetchall()
[(2, 'good', 'eagle', None),
 (3, 'good', 'mole', None),
 (22, 'poor', 'not', None),
 (1, 'good', 'crow', None)]

Please signup or login to give your own answer.

Click here to cancel reply.