Delete multiple postgreSQL records with python-CodePudding

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

class PersonaDAO:


  _SELECT = 'SELECT * FROM persona ORDER BY id_person'
  _INSERT = 'INSERT INTO person(name, lastname, email) VALUES(%s, %s, %s)'
  _UPDATE = 'UPDATE person SET name=%s, lastname=%s, email=%s WHERE id_person=%s'
  _DELETE = 'DELETE FROM person WHERE id_person=%s'



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

#Delete record
    person1 = Person(person_id = 18)
    person2 = Person(person_id = 13)
    deleted_persons = PersonDAO.delete(person1, person2)
    log.debug (f'Persons deleted: {persons_deleted}')

CodePudding user response：

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

CodePudding user response：

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