O întrebare frecvent întrebat aici este cum să facă un upsert, care este ceea ce MySQL apeluri INSERT ... PE DUPLICAT UPDATE
și standard acceptă ca parte a MERGE
operațiune.
Având în vedere că PostgreSQL nu't de sprijin direct (înainte pg 9.5), cum faci asta? Luați în considerare următoarele:
CREATE TABLE testtable (
id integer PRIMARY KEY,
somedata text NOT NULL
);
INSERT INTO testtable (id, somedata) VALUES
(1, 'fred'),
(2, 'bob');
Acum imaginați-vă că doriți să "upsert" tupluri (2, 'Joe')
, `(3, 'Alan'), deci noul tabel conținutul ar fi:
(1, 'fred'),
(2, 'Joe'), -- Changed value of existing tuple
(3, 'Alan') -- Added new tuple
Ca's ceea ce oamenii vorbesc despre atunci când se discută o upsert
. În mod esențial, orice abordare trebuie să fie în condiții de siguranță, în prezența mai multor tranzacții de lucru pe aceeași masă - fie prin utilizarea explicită de blocare, sau altfel apărarea împotriva rezultă condițiile de rasă.
Acest subiect este discutat pe larg la https://stackoverflow.com/q/1109061/398670, dar care's despre alternative la MySQL sintaxă, și-l's crescut un pic echitabil de legătură detaliu de-a lungul timpului. Am'm lucrează la răspunsuri definitive.
Aceste tehnici sunt de asemenea utile pentru "a introduce, dacă nu există, altfel nu faci nimic", respectiv "introduce ... pe cheie duplicat ignora".
ON DUPLICATE KEY UPDATE
.
Explicație rapidă.
Pentru utilizare se vedea manual - în special conflict_action clauză în diagrama de sintaxă, și text explicativ.
Spre deosebire de soluțiile de 9.4 și mai în vârstă, care sunt prezentate mai jos, această caracteristică funcționează cu mai multe contradictorii rânduri și nu't nevoie exclusiv de blocare sau un retry buclă.
Commit adăugarea de caracteristica este here și discuția în jurul său de dezvoltare este aici. PostgreSQL nu't au orice built-in UPSERT "(sau " ÎMBINARE
) facilitate, și de a face în mod eficient în fața utilizarea concomitentă este foarte dificil.
Acest articol discută problema în detaliu util.
În general, trebuie să aleagă între două opțiuni:
Folosind individuale rând upserts într-un retry buclă este rezonabilă opțiune dacă doriți mai multe conexiuni simultan încercarea de a efectua insertii.
În PostgreSQL documentația conține o procedură utilă, care'll să faci asta într-o buclă în interiorul database. Aceasta protejează împotriva pierdut actualizări și introduce curse, spre deosebire de cele mai naiv soluții. Acesta va funcționa doar în CITIRE COMISmodul și este în siguranță doar dacă-l's singurul lucru pe care îl faci în tranzacție, deși. Funcția câștigat't funcționeze corect dacă declanșează sau secundar chei unice cauza unică de încălcări. Această strategie este foarte ineficient. Ori de câte ori practice ar trebui să stea la coadă de muncă și de a face un mai mare parte upsert așa cum este descris mai jos în loc. Mulți au încercat soluții la această problemă nu reușesc să ia în considerare un schimb de tura, astfel încât au rezultat în incompletă actualizări. Două tranzacții cursa cu altele; una dintre ele cu succes
INTRODUCE e; celălalt devine o cheie duplicat eroare și de a face un "UPDATE" în loc. Pe "UPDATE" blocuri de așteptare pentru "INSERT" pentru a rollback sau commit. Atunci când se rostogolește pe spate, pe "UPDATE" starea re-verifica meciuri zero rânduri, astfel încât, chiar dacă "ACTUALIZARE" a comis-o n't de fapt făcut upsert v-ați așteptat. Trebuie să verificați rezultatul rând contează și re-încerca, acolo unde este necesar.
Unii au încercat soluții eșua, de asemenea, să ia în considerare anumite curse. Dacă încercați evidente și simple:
-- THIS IS WRONG. DO NOT COPY IT. It's an EXAMPLE.
BEGIN;
UPDATE testtable
SET somedata = 'blah'
WHERE id = 2;
-- Remember, this is WRONG. Do NOT COPY IT.
INSERT INTO testtable (id, somedata)
SELECT 2, 'blah'
WHERE NOT EXISTS (SELECT 1 FROM testtable WHERE testtable.id = 2);
COMMIT;
atunci când două rula la o dată există mai multe moduri de eșec. Unul este deja discutat problema cu un update re-check. O alta este în cazul în care atât de "ACTUALIZARE" în același timp, de potrivire zero rânduri și continuă. Apoi, ambele au de a face EXISTĂ
test, care se întâmplă înainte "INSERT". Amândoi zero rânduri, astfel încât atât face "INSERT". Nimeni nu reușește cu o cheie duplicat de eroare.
Acest lucru este de ce ai nevoie de un re-încercați buclă. Ai putea crede că te poate preveni cheie duplicat erori sau pierdut actualizări cu inteligent SQL, dar poate't. Aveți nevoie pentru a verifica rând contează sau mâner cheie duplicat erori (în funcție de abordarea aleasă) și re-încercați.
Vă rugăm să don't rola propria soluție pentru acest lucru. Ca cu message queuing, l's, probabil, greșit.
Uneori vrei să faci o mare parte upsert, în cazul în care aveți un nou set de date pe care doriți să fuzioneze într-un mai vechi set de date existent. Acest lucru este mult mai eficientă decât individuale rând upserts și ar trebui să fie preferată ori de câte ori practice. În acest caz, de obicei urmeze următorul proces:
COMIT
, eliberarea de blocare.
De exemplu, pentru exemplul dat în cauză, folosind multi-evaluate "INSERT" pentru a popula temp tabel: BEGIN;
CREATE TEMPORARY TABLE newvals(id integer, somedata text);
INSERT INTO newvals(id, somedata) VALUES (2, 'Joe'), (3, 'Alan');
LOCK TABLE testtable IN EXCLUSIVE MODE;
UPDATE testtable
SET somedata = newvals.somedata
FROM newvals
WHERE newvals.id = testtable.id;
INSERT INTO testtable
SELECT newvals.id, newvals.somedata
FROM newvals
LEFT OUTER JOIN testtable ON (testtable.id = newvals.id)
WHERE testtable.id IS NULL;
COMMIT;
MERGE
pe PostgreSQL wiki SQL-standard MERGE
are de fapt slab definite concurenta semantică și nu este potrivit pentru upserting fără blocarea unui tabel în primul rând.
L's foarte utile OLAP declarație pentru unificarea datelor, dar's, de fapt, nu o soluție utilă pentru concurenta-în condiții de siguranță upsert. Nu's o mulțime de sfaturi pentru persoanele care utilizează alte DBMSes de a utiliza MERGE
pentru upserts, dar's, de fapt, greșită.
INTRODUCE ... ON DUPLICATE KEY UPDATE
în MySQL MERGE
la MS SQL Server (dar a se vedea mai sus despre MERGE
probleme) MERGE
la Oracle (dar a se vedea mai sus despre MERGE
probleme)Încerc să contribui cu o altă soluție pentru inserție unică problemă cu pre-9.5 versiuni de PostgreSQL. Ideea este pur și simplu să încercați să efectuați mai întâi de inserție, și în cazul în care înregistrarea este deja prezent, să actualizare:
do $$
begin
insert into testtable(id, somedata) values(2,'Joe');
exception when unique_violation then
update testtable set somedata = 'Joe' where id = 2;
end $$;
Rețineți că această soluție poate fi aplicată numai dacă nu există ștergerile de rânduri din tabelul.
Nu știu despre eficiența acestei soluții, dar mi se pare destul de rezonabil.
Aici sunt câteva exemple pentru a introduce ... pe conflict ...` (pg 9.5+) :
Introduce, pe conflict - nu fac nimic.
introduce într-dummy(id, nume, dimensiune) valori(1, 'new_name', 3) pe de conflict nu face nimic;
Introduce, pe conflict - actualizare, specifica conflict țintă prin *coloana***.
introduce într-dummy(id, nume, dimensiune) valori(1, 'new_name', 3) pe conflict(id) fac update set name = 'new_name', size = 3;
Introduce, pe conflict - actualizare, specifica conflict țintă prin intermediul nume de constrângere.
introduce într-dummy(id, nume, dimensiune) valori(1, 'new_name', 3) cu privire la conflictul de pe constrângere dummy_pkey fac update set name = 'new_name', size = 4;
De la mare post de mai sus se referă la mai multe diferite SQL abordări pentru Postgres versiuni (nu numai non-9.5 ca în întrebare), aș dori să adaug cum să-l facă în SQLAlchemy dacă utilizați Postgres 9.5. În loc de punere în aplicare propriile upsert, puteți utiliza, de asemenea, SQLAlchemy's funcții (care s-au adăugat în SQLAlchemy 1.1). Personal, mi-ar recomandăm să utilizați aceste, dacă este posibil. Nu numai din cauza de comoditate, dar, de asemenea, pentru că vă permite PostgreSQL se ocupe de orice rasă condiții care ar putea să apară.
Cross-posting de la un alt răspuns i-am dat ieri (https://stackoverflow.com/a/44395983/2156909)
SQLAlchemy sprijină PE CONFLICT acum cu două metode on_conflict_do_update () " și " on_conflict_do_nothing()
:
Copierea de documente:
from sqlalchemy.dialects.postgresql import insert
stmt = insert(my_table).values(user_email='[email protected]', data='inserted data')
stmt = stmt.on_conflict_do_update(
index_elements=[my_table.c.user_email],
index_where=my_table.c.user_email.like('%@gmail.com'),
set_=dict(data=stmt.excluded.data)
)
conn.execute(stmt)
De această întrebare a fost închis, am'm a posta aici cum o faci folosind SQLAlchemy. Prin recursivitate, încercări un bulk insert sau update pentru a combate condițiile de rasă și erori de validare.
În primul rând importurile
import itertools as it
from functools import partial
from operator import itemgetter
from sqlalchemy.exc import IntegrityError
from app import session
from models import Posts
Acum câteva funcții helper
def chunk(content, chunksize=None):
"""Groups data into chunks each with (at most) `chunksize` items.
https://stackoverflow.com/a/22919323/408556
"""
if chunksize:
i = iter(content)
generator = (list(it.islice(i, chunksize)) for _ in it.count())
else:
generator = iter([content])
return it.takewhile(bool, generator)
def gen_resources(records):
"""Yields a dictionary if the record's id already exists, a row object
otherwise.
"""
ids = {item[0] for item in session.query(Posts.id)}
for record in records:
is_row = hasattr(record, 'to_dict')
if is_row and record.id in ids:
# It's a row but the id already exists, so we need to convert it
# to a dict that updates the existing record. Since it is duplicate,
# also yield True
yield record.to_dict(), True
elif is_row:
# It's a row and the id doesn't exist, so no conversion needed.
# Since it's not a duplicate, also yield False
yield record, False
elif record['id'] in ids:
# It's a dict and the id already exists, so no conversion needed.
# Since it is duplicate, also yield True
yield record, True
else:
# It's a dict and the id doesn't exist, so we need to convert it.
# Since it's not a duplicate, also yield False
yield Posts(**record), False
Și în cele din urmă upsert funcție
def upsert(data, chunksize=None):
for records in chunk(data, chunksize):
resources = gen_resources(records)
sorted_resources = sorted(resources, key=itemgetter(1))
for dupe, group in it.groupby(sorted_resources, itemgetter(1)):
items = [g[0] for g in group]
if dupe:
_upsert = partial(session.bulk_update_mappings, Posts)
else:
_upsert = session.add_all
try:
_upsert(items)
session.commit()
except IntegrityError:
# A record was added or deleted after we checked, so retry
#
# modify accordingly by adding additional exceptions, e.g.,
# except (IntegrityError, ValidationError, ValueError)
db.session.rollback()
upsert(items)
except Exception as e:
# Some other error occurred so reduce chunksize to isolate the
# offending row(s)
db.session.rollback()
num_items = len(items)
if num_items > 1:
upsert(items, num_items // 2)
else:
print('Error adding record {}'.format(items[0]))
Aici's modul de utilizare
>>> data = [
... {'id': 1, 'text': 'updated post1'},
... {'id': 5, 'text': 'updated post5'},
... {'id': 1000, 'text': 'new post1000'}]
...
>>> upsert(data)
Avantajul aceasta are peste bulk_save_objects
este că se poate descurca relatii, verificarea erorilor, etc pe insert (spre deosebire de vrac operațiunile).