使用python删除多个PostgreSQL记录

k5ifujac  于 2022-11-04  发布在  PostgreSQL
关注(0)|答案(2)|浏览(183)

我的问题是,当我尝试用python从PostgreSQL的表中删除记录时,我无法做到,我创建的方法逐个删除记录,但当我想一次删除2个或更多记录时,我会收到一个错误,即TypeError:PersonaDAO.delete()接受2个位置参数,但给出了3个
在PersonaDAO类中,我创建了一个删除方法。此外,我还有connection类,它有连接到bd的方法,还有cursor. Person类,它有初始化方法,属性为(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

用这个方法我可以一次删除一个id,但不能一次删除多个id。

whitzsjs

whitzsjs1#

我不知道你的框架,但我会试试这个
问题出在delete()中:
一种方法是:

@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

更好的方法(但我不知道你的框架是否支持将列表作为参数传递):

_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
zqry0prt

zqry0prt2#

您没有说明您使用的是什么Postgres Python驱动程序,但假设psycopg2,您可以执行以下操作。
使用测试示例表。

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]

使用psycopg2执行函数:

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

使用id数组:

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

相关问题