python - ejemplos - django

Postgres: INSERTAR si no existe ya

Question

Estoy usando Python para escribir en una base de datos postgres:

sql_string = "INSERT INTO hundred (name,name_slug,status) VALUES ("
sql_string += hundred + ", ''" + hundred_slug + "'', " + status + ");"
cursor.execute(sql_string)

Pero como algunas de mis filas son idénticas, aparece el siguiente error:

psycopg2.IntegrityError: duplicate key value
violates unique constraint "hundred_pkey"

¿Cómo puedo escribir una declaración SQL ''INSERT a menos que esta fila ya exista''?

He visto declaraciones complejas como esta recomendadas:

IF EXISTS (SELECT * FROM invoices WHERE invoiceid = ''12345'')
UPDATE invoices SET billed = ''TRUE'' WHERE invoiceid = ''12345''
ELSE
INSERT INTO invoices (invoiceid, billed) VALUES (''12345'', ''TRUE'')
END IF

Pero en primer lugar, ¿esto es una exageración para lo que necesito, y en segundo lugar, cómo puedo ejecutar uno de esos como una simple cadena?

Answer 1

¿Cómo puedo escribir una declaración SQL ''INSERT a menos que esta fila ya exista''?

Hay una buena manera de hacer INSERT condicional en PostgreSQL:

INSERT INTO example_table
(id, name)
SELECT 1, ''John''
WHERE
NOT EXISTS (
SELECT id FROM example_table WHERE id = 1
);

Sin embargo, este enfoque no es 100% confiable para operaciones de escritura simultáneas . Hay una condición de carrera muy pequeña entre el SELECT en el anti-semi-join NOT EXISTS y el INSERT . Puede fallar bajo tales condiciones.

Answer 2

Aquí hay una función python genérica que, dado un nombre de tabla, columnas y valores, genera el equivalente de upsert para postgresql.

importar json

def upsert(table_name, id_column, other_columns, values_hash):
template = """
WITH new_values ($$ALL_COLUMNS$$) as (
values
($$VALUES_LIST$$)
),
upsert as
(
update $$TABLE_NAME$$ m
set
$$SET_MAPPINGS$$
FROM new_values nv
WHERE m.$$ID_COLUMN$$ = nv.$$ID_COLUMN$$
RETURNING m.*
)
INSERT INTO $$TABLE_NAME$$ ($$ALL_COLUMNS$$)
SELECT $$ALL_COLUMNS$$
FROM new_values
WHERE NOT EXISTS (SELECT 1
FROM upsert up
WHERE up.$$ID_COLUMN$$ = new_values.$$ID_COLUMN$$)
"""
all_columns = [id_column] + other_columns
all_columns_csv = ",".join(all_columns)
all_values_csv = '',''.join([query_value(values_hash[column_name]) for column_name in all_columns])
set_mappings = ",".join([ c+ " = nv." +c for c in other_columns])
q = template
q = q.replace("$$TABLE_NAME$$", table_name)
q = q.replace("$$ID_COLUMN$$", id_column)
q = q.replace("$$ALL_COLUMNS$$", all_columns_csv)
q = q.replace("$$VALUES_LIST$$", all_values_csv)
q = q.replace("$$SET_MAPPINGS$$", set_mappings)
return q
def query_value(value):
if value is None:
return "NULL"
if type(value) in [str, unicode]:
return "''%s''" % value.replace("''", "''''")
if type(value) == dict:
return "''%s''" % json.dumps(value).replace("''", "''''")
if type(value) == bool:
return "%s" % value
if type(value) == int:
return "%s" % value
return value
if __name__ == "__main__":
my_table_name = ''mytable''
my_id_column = ''id''
my_other_columns = [''field1'', ''field2'']
my_values_hash = {
''id'': 123,
''field1'': "john",
''field2'': "doe"
}
print upsert(my_table_name, my_id_column, my_other_columns, my_values_hash)

Answer 3

Desafortunadamente, PostgreSQL no admite MERGE ni ON DUPLICATE KEY UPDATE , por lo que tendrás que hacerlo en dos frases:

UPDATE invoices
SET billed = ''TRUE''
WHERE invoices = ''12345''
INSERT
INTO invoices (invoiceid, billed)
SELECT ''12345'', ''TRUE''
WHERE ''12345'' NOT IN
(
SELECT invoiceid
FROM invoices
)

Puedes envolverlo en una función:

CREATE OR REPLACE FUNCTION fn_upd_invoices(id VARCHAR(32), billed VARCHAR(32))
RETURNS VOID
AS
$$
UPDATE invoices
SET billed = $2
WHERE invoices = $1;
INSERT
INTO invoices (invoiceid, billed)
SELECT $1, $2
WHERE $1 NOT IN
(
SELECT invoiceid
FROM invoices
);
$$
LANGUAGE ''sql'';

y solo llamalo

SELECT fn_upd_invoices(''12345'', ''TRUE'')

Answer 4

El enfoque con la mayoría de los votos positivos (de John Doe) funciona de alguna manera para mí, pero en mi caso de las 422 filas esperadas solo obtengo 180. No pude encontrar nada malo y no hay ningún error, por lo que busqué una diferente. enfoque simple.

Usar IF NOT FOUND THEN después de un SELECT simplemente funciona perfectamente para mí.

(descrito en la documentación de PostgreSQL )

Ejemplo de documentación:

SELECT * INTO myrec FROM emp WHERE empname = myname;
IF NOT FOUND THEN
RAISE EXCEPTION ''employee % not found'', myname;
END IF;

Answer 5

Es fácil con las reglas:

CREATE RULE file_insert_defer AS ON INSERT TO file
WHERE (EXISTS ( SELECT * FROM file WHERE file.id = new.id)) DO INSTEAD NOTHING

Pero falla con escrituras concurrentes ...

Answer 6

Estaba buscando una solución similar, intentando encontrar SQL que funcionara en PostgreSQL y en HSQLDB. (HSQLDB fue lo que lo hizo difícil.) Usando su ejemplo como base, este es el formato que encontré en otro lugar.

sql = "INSERT INTO hundred (name,name_slug,status)"
sql += " ( SELECT " + hundred + ", ''" + hundred_slug + "'', " + status
sql += " FROM hundred"
sql += " WHERE name = " + hundred + " AND name_slug = ''" + hundred_slug + "'' AND status = " + status
sql += " HAVING COUNT(*) = 0 );"

Answer 7

Hay una buena manera de hacer INSERT condicional en PostgreSQL usando la consulta WITH: Como:

WITH a as(
select
id
from
schema.table_name
where
column_name = your_identical_column_value
)
INSERT into
schema.table_name
(col_name1, col_name2)
SELECT
(col_name1, col_name2)
WHERE NOT EXISTS (
SELECT
id
FROM
a
)
RETURNING id

Answer 8

INSERTAR .. DONDE NO EXISTE es un buen enfoque. Y las condiciones de carrera se pueden evitar mediante la transacción "sobre":

BEGIN;
LOCK TABLE hundred IN SHARE ROW EXCLUSIVE MODE;
INSERT ... ;
COMMIT;

Answer 9

La clase de cursor psycopgs tiene el atributo rowcount .

Este atributo de solo lectura especifica el número de filas que la última ejecución * () produjo (para sentencias DQL como SELECT) o afectadas (para sentencias DML como UPDATE o INSERT).

Por lo tanto, puedes probar ACTUALIZAR primero e INSERTAR solo si el recuento de filas es 0.

Pero dependiendo de los niveles de actividad en su base de datos, puede alcanzar una condición de carrera entre ACTUALIZAR e INSERTAR, donde otro proceso puede crear ese registro en el ínterin.

Answer 10

La solución en simple, pero no inmediatamente.
Si desea utilizar esta instrucción, debe hacer un cambio en la base de datos:

ALTER USER user SET search_path to ''name_of_schema'';

después de estos cambios "INSERT" funcionará correctamente.

Answer 11

Postgres 9.5 (publicado desde 2016-01-07) ofrece un comando "upsert" , también conocido como una cláusula ON CONFLICT para INSERTAR :

INSERT ... ON CONFLICT DO NOTHING/UPDATE

Resuelve muchos de los problemas sutiles con los que puede encontrarse al usar operaciones concurrentes, que algunas otras respuestas proponen.

Answer 12

Puedes hacer uso de VALORES - disponibles en Postgres:

INSERT INTO person (name)
SELECT name FROM person
UNION
VALUES (''Bob'')
EXCEPT
SELECT name FROM person;

Answer 13

Sé que esta pregunta es de hace un tiempo, pero pensé que esto podría ayudar a alguien. Creo que la forma más fácil de hacerlo es a través de un disparador. P.ej:

Create Function ignore_dups() Returns Trigger
As $$
Begin
If Exists (
Select
*
From
hundred h
Where
-- Assuming all three fields are primary key
h.name = NEW.name
And h.hundred_slug = NEW.hundred_slug
And h.status = NEW.status
) Then
Return NULL;
End If;
Return NEW;
End;
$$ Language plpgsql;
Create Trigger ignore_dups
Before Insert On hundred
For Each Row
Execute Procedure ignore_dups();

Ejecute este código desde un indicador de psql (o como quiera ejecutar consultas directamente en la base de datos). Luego puedes insertarlo normalmente desde Python. P.ej:

sql = "Insert Into hundreds (name, name_slug, status) Values (%s, %s, %s)"
cursor.execute(sql, (hundred, hundred_slug, status))

Tenga en cuenta que como ya se mencionó @Thomas_Wouters, el código anterior aprovecha los parámetros en lugar de concatenar la cadena.

Answer 14

Si solo desea insertar o no insertar (y no actualizar de otra manera), puede hacerlo así (usando el ejemplo de factura):

INSERT INTO invoices (invoiceid, billed) SELECT ''12345'', ''TRUE''
WHERE NOT EXISTS (SELECT 1 FROM invoices WHERE invoiceid = ''12345'')

Answer 15

Su columna "cien" parece definirse como clave principal y, por lo tanto, debe ser única, lo que no es el caso. El problema no es con, es con sus datos.

Le sugiero que inserte un ID como tipo de serie para manejar la clave principal

Answer 16

Un enfoque sería crear una tabla no restringida (sin índices únicos) para insertar todos sus datos y hacer una selección distinta de esa para hacer su inserción en su tabla cien.

Tan alto nivel sería. Supongo que las tres columnas son distintas en mi ejemplo, por lo que para el paso 3, cambie la combinación NOT EXITS para unirse solo en las columnas únicas de la tabla cien.

1. Crear tabla temporal. Vea los documentos here .

   CREATE TEMPORARY TABLE temp_data(name, name_slug, status);

2. Insertar datos en la tabla temporal.

   INSERT INTO temp_data(name, name_slug, status);

3. Agregue cualquier índice a la tabla temporal.

4. Hacer inserto en la mesa principal.

   INSERT INTO hundred(name, name_slug, status)
   SELECT DISTINCT name, name_slug, status
   FROM hundred
   WHERE NOT EXISTS (
   SELECT ''X''
   FROM temp_data
   WHERE
   temp_data.name = hundred.name
   AND temp_data.name_slug = hundred.name_slug
   AND temp_data.status = status
   );