복붙노트

[PYTHON] 포스트그레스 : 이미 존재하지 않는 INSERT

PYTHON

포스트그레스 : 이미 존재하지 않는 INSERT

나는 파이썬을 사용하여 포스트그레스 데이터베이스에 글을 쓴다 :

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

하지만 일부 행이 동일하기 때문에 다음과 같은 오류가 발생합니다.

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

이 행이 이미 존재하지 않는 한 'INSERT'를 쓰려면 어떻게해야합니까?

이처럼 복잡한 구문을 본 적이 있습니다.

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

그러나 첫째로, 내가 필요로하는 것에 대한 과잉인가? 둘째, 어떻게 그 중 하나를 간단한 문자열로 실행할 수 있습니까?

해결법

  1. ==============================

    1.PostgreSQL에서 조건부 INSERT를 수행하는 훌륭한 방법이 있습니다.

    PostgreSQL에서 조건부 INSERT를 수행하는 훌륭한 방법이 있습니다.

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

    CAVEAT이 접근법은 동시 쓰기 작업에 대해 100 % 신뢰할 수있는 것은 아닙니다. NOT EXISTS 반 semi-join의 SELECT와 INSERT 사이에는 매우 작은 경쟁 조건이 있습니다. 그러한 조건 하에서는 실패 할 수 있습니다.

  2. ==============================

    2.Postgres 9.5 (2016-01-07 이후에 릴리스 됨)는 INSERT 할 ON CONFLICT 절인 "upsert"명령을 제공합니다.

    Postgres 9.5 (2016-01-07 이후에 릴리스 됨)는 INSERT 할 ON CONFLICT 절인 "upsert"명령을 제공합니다.

    INSERT ... ON CONFLICT DO NOTHING/UPDATE

    동시 조작을 사용할 때 실행할 수있는 여러 가지 미묘한 문제를 해결하며 다른 해결책이 제시합니다.

  3. ==============================

    3.하나의 접근법은 모든 데이터를 삽입하기 위해 제약이없는 (고유 인덱스가 없음) 테이블을 생성하고 100 개의 테이블에 삽입하는 것과는 별개의 선택을하는 것입니다.

    하나의 접근법은 모든 데이터를 삽입하기 위해 제약이없는 (고유 인덱스가 없음) 테이블을 생성하고 100 개의 테이블에 삽입하는 것과는 별개의 선택을하는 것입니다.

    그래서 높은 수준이 될 것입니다. 내 3 개의 열 모두 내 예제에서는 별개라고 가정하므로 step3에서는 NOT EXITS 조인을 변경하여 100 개의 테이블에있는 고유 한 열만 조인합니다.

  4. ==============================

    4.불행하게도, PostgreSQL는 MERGE와 중복 된 KEY 업데이트를 지원하지 않으므로 다음과 같은 두 가지 명령문을 사용해야합니다.

    불행하게도, PostgreSQL는 MERGE와 중복 된 KEY 업데이트를 지원하지 않으므로 다음과 같은 두 가지 명령문을 사용해야합니다.

    UPDATE  invoices
SET     billed = 'TRUE'
WHERE   invoices = '12345'

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

    함수로 래핑 할 수 있습니다 :

    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';

    그냥 부르면됩니다.

    SELECT  fn_upd_invoices('12345', 'TRUE')
  5. ==============================

    5.삽입하거나 삽입하지 않으려는 경우 (그렇지 않으면 업데이트하지 않음) 다음과 같이 수행 할 수 있습니다 (송장 예제 사용).

    삽입하거나 삽입하지 않으려는 경우 (그렇지 않으면 업데이트하지 않음) 다음과 같이 수행 할 수 있습니다 (송장 예제 사용).

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

    6.VALUES를 사용할 수 있습니다 - Postgres에서 사용할 수 있습니다 :

    VALUES를 사용할 수 있습니다 - Postgres에서 사용할 수 있습니다 :

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

    7.나는이 질문이 얼마 전부터 있었지만 이것이 누군가를 도울 수 있다고 생각했습니다. 가장 쉬운 방법은 방아쇠를 통해하는 것입니다. 예 :

    나는이 질문이 얼마 전부터 있었지만 이것이 누군가를 도울 수 있다고 생각했습니다. 가장 쉬운 방법은 방아쇠를 통해하는 것입니다. 예 :

    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();

    psql 프롬프트에서이 코드를 실행하십시오 (또는 데이터베이스에서 직접 쿼리를 실행하고 싶을 수도 있습니다). 그런 다음 파이썬에서 정상적으로 삽입 할 수 있습니다. 예 :

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

    @Thomas_Wouters가 이미 언급했듯이 위의 코드는 문자열을 연결하는 대신 매개 변수를 사용합니다.

  8. ==============================

    8.INSERT .. 존재하지 않는 곳은 좋은 접근법입니다. 경쟁 조건은 트랜잭션 "봉투"로 피할 수 있습니다.

    INSERT .. 존재하지 않는 곳은 좋은 접근법입니다. 경쟁 조건은 트랜잭션 "봉투"로 피할 수 있습니다.

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

    9.WITH 쿼리를 사용하여 PostgreSQL에서 조건부 INSERT를 수행하는 좋은 방법이 있습니다. 처럼:

    WITH 쿼리를 사용하여 PostgreSQL에서 조건부 INSERT를 수행하는 좋은 방법이 있습니다. 처럼:

    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
  10. ==============================

    10.규칙은 쉽습니다.

    규칙은 쉽습니다.

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

    하지만 동시 쓰기가 실패합니다 ...

  11. ==============================

    11.가장 큰 upvotes (John Doe로부터의 접근법)은 어떻게 든 나를 위해 일하지만 예상 된 422 행에서 나의 경우에는 단지 180을 얻는다. 나는 틀린 것을 발견 할 수 없었고 전혀 오류가 없었기 때문에 나는 다른 간단한 접근법을 찾았다.

    가장 큰 upvotes (John Doe로부터의 접근법)은 어떻게 든 나를 위해 일하지만 예상 된 422 행에서 나의 경우에는 단지 180을 얻는다. 나는 틀린 것을 발견 할 수 없었고 전혀 오류가 없었기 때문에 나는 다른 간단한 접근법을 찾았다.

    SELECT를 사용한 후에 NOT FOUND를 사용하면 나를 완벽하게 처리 할 수 ​​있습니다.

    (PostgreSQL 문서에 설명되어있다)

    문서화의 예 :

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

    12.psycopgs 커서 클래스에는 rowcount 속성이 있습니다.

    psycopgs 커서 클래스에는 rowcount 속성이 있습니다.

    따라서 rowcount가 0 인 경우에만 UPDATE를 먼저 시도하고 INSERT를 시도 할 수 있습니다.

    그러나 데이터베이스의 활동 레벨에 따라 UPDATE와 INSERT 사이의 경쟁 조건에 부딪 힐 수 있습니다. 다른 프로세스가 그 레코드를 임시로 생성 할 수 있습니다.

  13. ==============================

    13.나는 유사한 해결책을 찾고 있었는데, PostgreSQL과 HSQLDB에서 SQL을 사용하고있다. (HSQLDB는 이것을 어렵게 만들었습니다.) 당신의 예제를 기초로, 이것은 제가 다른 곳에서 발견 한 형식입니다.

    나는 유사한 해결책을 찾고 있었는데, PostgreSQL과 HSQLDB에서 SQL을 사용하고있다. (HSQLDB는 이것을 어렵게 만들었습니다.) 당신의 예제를 기초로, 이것은 제가 다른 곳에서 발견 한 형식입니다.

    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 );"
  14. ==============================

    14."100"열은 기본 키로 정의 된 것으로 보이므로 고유하지 않아야합니다. 문제는 귀하의 데이터와 관련이 없습니다.

    "100"열은 기본 키로 정의 된 것으로 보이므로 고유하지 않아야합니다. 문제는 귀하의 데이터와 관련이 없습니다.

    난 당신이 handly 기본 키에 시리얼 형식으로 ID를 삽입하는 것이 좋습니다

  15. ==============================

    15.다음은 tablename, columns 및 values를 지정하고 postgresql에 해당하는 upsert를 생성하는 일반적인 파이썬 함수입니다.

    다음은 tablename, columns 및 values를 지정하고 postgresql에 해당하는 upsert를 생성하는 일반적인 파이썬 함수입니다.

    수입 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)
  16. ==============================

    16.솔루션은 간단하지만 즉시는 아닙니다. 이 명령어를 사용하려면 db에 하나의 변경 사항을 적용해야합니다.

    솔루션은 간단하지만 즉시는 아닙니다. 이 명령어를 사용하려면 db에 하나의 변경 사항을 적용해야합니다.

    ALTER USER user SET search_path to 'name_of_schema';

    이 변경 후 "INSERT"가 올바르게 작동합니다.

    17. from https://stackoverflow.com/questions/4069718/postgres-insert-if-does-not-exist-already by cc-by-sa and MIT license

'PYTHON' 카테고리의 다른 글

[PYTHON] 어떻게 여러 제출 버튼 장고 양식을 만들 수 있습니까?  (0) 2018.10.16
[PYTHON] virtualenv에 환경 변수 설정하기  (0) 2018.10.16
[PYTHON] pip 설치가 github에서 작동 할 수 있도록 구성  (0) 2018.10.16
[PYTHON] scikit-learn을 사용하여 여러 카테고리로 분류  (0) 2018.10.16
[PYTHON] 오류 : eventlet을 설치하는 동안 'gcc'명령이 종료 상태 1로 실패했습니다.  (0) 2018.10.16
,

티스토리툴바