Namuhla ngifuna ukwethula abafundi be-Habr ngesisetshenziswa esibhalwe ku-Python sokusebenza ngokuncika kwetafula ku-PostgreSQL DBMS.
I-API yensiza ilula futhi ihlanganisa izindlela ezintathu:
- i-archive_table - Ukufaka kungobo yomlando okuphindaphindayo/imigqa enokhiye Abayinhloko abashiwo
- get_table_references - sesha okuncikile kwetafula (lizokhombisa amathebula ashiwo yilelo elishiwo kanye nalabo abawakhombayo)
- thola_imigqa_izithenjwa - sesha imigqa kwamanye amathebula ekhomba imigqa ecacisiwe kuthebula olifunayo
prehistory
Igama lami ngingu-Oleg Borzov, ngingunjiniyela eqenjini le-CRM labaphathi ababolekisa ngemali yezindlu e-Domklik.
Isizindalwazi esiyinhloko sesistimu yethu ye-CRM ingenye enkulu kakhulu ngokwevolumu enkampanini. Futhi ingenye yezindala kakhulu: ivele ekuqalisweni kwephrojekthi, lapho izihlahla zinkulu, i-Domklik yayiyisiqalo, futhi esikhundleni se-microservice kuhlaka lwe-Python asynchronous olunemfashini kwakukhona i-monolith enkulu ku-PHP.
Ushintsho olusuka ku-PHP luye ku-Python lwalulude kakhulu futhi ludinga ukusekelwa ngasikhathi sinye kwazo zombili izinhlelo, okuthinte ukwakheka kwesizindalwazi.
Njengomphumela, sinesizindalwazi esinenombolo enkulu yamathebula axhumene kakhulu futhi amakhulu anenqwaba yezinkomba zezinhlobo ezahlukene zemibuzo. Konke lokhu kuthinta kabi ukusebenza kwesizindalwazi: ngenxa yamatafula amakhulu kanye nenqwaba yobudlelwano phakathi kwabo, ubunzima bemibuzo bukhula njalo, okubaluleke kakhulu kumatafula alayishwe kakhulu.
Ukuze sinciphise umthwalo kusizindalwazi, sinqume ukubhala umbhalo ozodlulisa amarekhodi amadala ukusuka kumathebula agqamile kakhulu futhi alayishiwe aye kulawo agcinwe kungobo yomlando (isibonelo, ukusuka ku- task
Π² task_archive
).
Lo msebenzi uhlanganiswa nenani elikhulu lobudlelwano phakathi kwamathebula: vele uhambise imigqa ukusuka task
Π² task_archive
akwanele, ngaphambi kwalokho udinga ukwenza okufanayo ngokuphindaphindiwe ngazo zonke lezo zireferensi task
amatafula.
Ngizobonisa ngesibonelo
Ake sithi sidinga ukususa amarekhodi etafuleni Flights
. I-Postgres ngeke isivumele ukuthi senze lokhu ngale ndlela: okokuqala sidinga ukususa amarekhodi kuwo wonke amathebula ayireferensi, njalo njalo ngokuphindisela phansi kumathebula angakhonjiswanga muntu.
Esibonelweni sethu ku Flights
kubhekisa Ticket_flights
, futhi kuye - Boarding_passes
.
Ngakho-ke, udinga ukuyisusa ngale ndlela:
- Sithola amanani okhiye abayinhloko (PK) bemigqa phakathi
Ticket_flights
, ebhekisela emigqeni ezosuswa kuyoFlights
. - Sithola imigqa ye-PK
Boarding_passes
, ezibhekisela kuTicket_flights
. - Sisusa imigqa nge-PK esinyathelweni sesi-2 etafuleni
Boarding_passes
. - Susa imigqa nge-PK kusukela kusinyathelo 1 phakathi
Ticket_flights
. - Isusa imigqa ku
Flights
.
Umphumela waba insiza ebizwa nge-PgGraph, esinqume ukuyenza umthombo ovulekile.
Ungayisebenzisa kanjani
Uhlelo lokusebenza lusekela izindlela ezimbili zokusebenzisa:
- Shayela kusuka kulayini womyalo (
pggraph β¦
). - Ukusetshenziswa kukhodi yePython (class
PgGraphApi
).
Ukufakwa nokumiswa
Okokuqala udinga ukufaka insiza kusuka endaweni yokugcina ye-Pypi:
pip3 install pggraph
Bese udala ifayela le-config.ini emshinini wendawo ngokucushwa kwedathabhesi kanye neskripthi sokugcina kungobo yomlando:
[db]
host = localhost
port = 5432
user = postgres
password = postgres
dbname = postgres
schema = public ; ΠΠ΅ΠΎΠ±ΡΠ·Π°ΡΠ΅Π»ΡΠ½ΡΠΉ ΠΏΠ°ΡΠ°ΠΌΠ΅ΡΡ, ΡΠΊΠ°Π·Π°Π½ΠΎ Π·Π½Π°ΡΠ΅Π½ΠΈΠ΅ ΠΏΠΎ ΡΠΌΠΎΠ»ΡΠ°Π½ΠΈΡ
[archive] ; ΠΠ°Π½Π½ΡΠΉ ΡΠ°Π·Π΄Π΅Π» Π·Π°ΠΏΠΎΠ»Π½ΡΡΡ Π½Π΅ΠΎΠ±ΡΠ·Π°ΡΠ΅Π»ΡΠ½ΠΎ, Π½ΠΈΠΆΠ΅ ΡΠΊΠ°Π·Π°Π½Ρ Π·Π½Π°ΡΠ΅Π½ΠΈΡ ΠΏΠΎ ΡΠΌΠΎΠ»ΡΠ°Π½ΠΈΡ
is_debug = false
chunk_size = 1000
max_depth = 20
to_archive = true
archive_suffix = 'archive'
Baleka ku-console
Amapharamitha
$ pggraph -h
usage: pggraph action [-h] --table TABLE [--ids IDS] [--config_path CONFIG_PATH]
positional arguments:
action required action: archive_table, get_table_references, get_rows_references
optional arguments:
-h, --help show this help message and exit
--table TABLE table name
--ids IDS primary key ids, separated by comma, e.g. 1,2,3
--config_path CONFIG_PATH path to config.ini
--log_path LOG_PATH path to log dir
--log_level LOG_LEVEL log level (debug, info, error)
Izimpikiswano zokuma:
action
- isenzo esidingekayo:archive_table
,get_table_references
nomaget_rows_references
.
Izimpikiswano eziqanjiwe:
--config_path
- indlela eya kufayela lokumisa;--table
- itafula okudingeka wenze ngalo isenzo;--ids
- uhlu lwe-id luhlukaniswe ngokhefana, isibonelo,1,2,3
(ipharamitha ozikhethela);--log_path
- indlela eya kufolda yezingodo (ipharamitha yokuzikhethela, ngokuzenzakalelayo - ifolda yasekhaya);--log_level
- Izinga lokungena (ipharamitha ozikhethela, okuzenzakalelayo ULWAZI).
Izibonelo zomyalo
Ukugcina itafula kungobo yomlando
Umsebenzi oyinhloko wensiza ukugcinwa kwedatha, i.e. ukudlulisa imigqa isuka kuthebula elikhulu iye kuthebula lengobo yomlando (isibonelo, ukusuka kuthebula izincwadi Π² ingobo_yezincwadi).
Ukususa ngaphandle kokufaka kungobo yomlando kuyasekelwa futhi: kulokhu udinga ukusetha ipharamitha ku-config.ini to_archive = amanga).
Amapharamitha adingekayo - config_path, ithebula nama-id.
Ngemva kokwethulwa, amarekhodi azosuswa ngokuphindaphindiwe ids
etafuleni table
nakuwo wonke amatafula akhuluma ngawo.
$ pggraph archive_table --config_path config.hw.local.ini --table flights --ids 1,2,3
2020-06-20 19:27:44 INFO: flights - START
2020-06-20 19:27:44 INFO: flights - start archive_recursive 3 rows (depth=0)
2020-06-20 19:27:44 INFO: START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO: ticket_flights - start archive_recursive 3 rows (depth=1)
2020-06-20 19:27:44 INFO: START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO: boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO: START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO: END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO: boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO: boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO: START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO: END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO: boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO: boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO: START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO: END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO: boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO: boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO: START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO: END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO: boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO: END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO: ticket_flights - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO: END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO: flights - archive_by_ids 3 rows by id
2020-06-20 19:27:44 INFO: flights - END
Thola ukuncika kwetafula elishiwo
Umsebenzi wokuthola ukuncika kwetafula elishiwo table
. Amapharamitha adingekayo - config_path
ΠΈ table
.
Ngemva kokwethulwa, isichazamazwi sizovezwa esikrinini, lapho:
in_refs
- isichazamazwi samathebula esibhekisela kwelinikeziwe, lapho ukhiye kuyigama letafula, inani liwuhlu lwezinto ezibalulekile zangaphandle (pk_main
- ukhiye oyinhloko etafuleni elikhulu,pk_ref
- ukhiye oyinhloko kuthebula lereferensi,fk_ref
β igama lekholomu elingukhiye wangaphandle kuthebula lomthombo);out_refs
β isichazamazwi samathebula lona abhekisela kuso.
$ pggraph get_table_references --config_path config.hw.local.ini --table flights
{'in_refs': {'ticket_flights': [ForeignKey(pk_main='flight_id', pk_ref='ticket_no, flight_id', fk_ref='flight_id')]},
'out_refs': {'aircrafts': [ForeignKey(pk_main='aircraft_code', pk_ref='flight_id', fk_ref='aircraft_code')],
'airports': [ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='arrival_airport'),
ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='departure_airport')]}}
Ukuthola izinkomba zeyunithi yezinhlamvu ezinokhiye Oyinhloko oshiwo
Umsebenzi wokusesha imigqa kwamanye amathebula abhekisela emigqeni ngokhiye Wangaphandle ids
amatafula table
. Amapharamitha adingekayo - config_path
, table
ΠΈ ids
.
Ngemva kokwethulwa, isichazamazwi esinesakhiwo esilandelayo sizovezwa esikrinini:
{
pk_id_1: {
reffering_table_name_1: {
foreign_key_1: [
{row_pk_1: value, row_pk_2: value},
...
],
...
},
...
},
pk_id_2: {...},
...
}
Ucingo lwesibonelo:
$ pggraph get_rows_references --config_path config.hw.local.ini --table flights --ids 1,2,3
{1: {'ticket_flights': {'flight_id': [{'flight_id': 1,
'ticket_no': '0005432816945'},
{'flight_id': 1,
'ticket_no': '0005432816941'}]}},
2: {'ticket_flights': {'flight_id': [{'flight_id': 2,
'ticket_no': '0005433101832'},
{'flight_id': 2,
'ticket_no': '0005433101864'},
{'flight_id': 2,
'ticket_no': '0005432919715'}]}},
3: {'ticket_flights': {'flight_id': [{'flight_id': 3,
'ticket_no': '0005432817560'},
{'flight_id': 3,
'ticket_no': '0005432817568'},
{'flight_id': 3,
'ticket_no': '0005432817559'}]}}}
Ukusetshenziswa kwekhodi
Ngaphezu kokuyisebenzisa kukhonsoli, umtapo wezincwadi ungasetshenziswa ngekhodi yePython. Izibonelo zezingcingo endaweni esebenzisanayo ye-iPython ziboniswa ngezansi.
Ukugcina itafula kungobo yomlando
>>> from pg_graph.main import setup_logging
>>> setup_logging(log_level='DEBUG')
>>> from pg_graph.api import PgGraphApi
>>> api = PgGraphApi('config.hw.local.ini')
>>> api.archive_table('flights', [4,5])
2020-06-20 23:12:08 INFO: flights - START
2020-06-20 23:12:08 INFO: flights - start archive_recursive 2 rows (depth=0)
2020-06-20 23:12:08 INFO: START ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 DEBUG: ticket_flights - ForeignKey(pk_main='flight_id', pk_ref='flight_id, ticket_no', fk_ref='flight_id')
2020-06-20 23:12:08 DEBUG: SQL('SELECT flight_id, ticket_no FROM bookings.ticket_flights WHERE (flight_id) IN (%s, %s)')
2020-06-20 23:12:08 INFO: ticket_flights - start archive_recursive 30 rows (depth=1)
2020-06-20 23:12:08 INFO: START ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 DEBUG: boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: boarding_passes - archive_by_fk 30 rows by ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: DELETE FROM boarding_passes by FK flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 INFO: END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: ticket_flights - archive_by_ids 30 rows by flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: DELETE FROM ticket_flights by flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 DEBUG: INSERT INTO ticket_flights_archive - 30 rows
2020-06-20 23:12:08 INFO: ticket_flights - start archive_recursive 30 rows (depth=1)
2020-06-20 23:12:08 INFO: START ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 DEBUG: boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: boarding_passes - archive_by_fk 30 rows by ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: DELETE FROM boarding_passes by FK flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 INFO: END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: ticket_flights - archive_by_ids 30 rows by flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: DELETE FROM ticket_flights by flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 DEBUG: INSERT INTO ticket_flights_archive - 30 rows
2020-06-20 23:12:08 INFO: ticket_flights - start archive_recursive 30 rows (depth=1)
2020-06-20 23:12:08 INFO: START ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 DEBUG: boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: boarding_passes - archive_by_fk 30 rows by ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: DELETE FROM boarding_passes by FK flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 INFO: END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: ticket_flights - archive_by_ids 30 rows by flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: DELETE FROM ticket_flights by flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 DEBUG: INSERT INTO ticket_flights_archive - 30 rows
2020-06-20 23:12:08 INFO: ticket_flights - start archive_recursive 3 rows (depth=1)
2020-06-20 23:12:08 INFO: START ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 DEBUG: boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: boarding_passes - archive_by_fk 3 rows by ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: DELETE FROM boarding_passes by FK flight_id, ticket_no - 3 rows
2020-06-20 23:12:08 INFO: END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: ticket_flights - archive_by_ids 3 rows by flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: DELETE FROM ticket_flights by flight_id, ticket_no - 3 rows
2020-06-20 23:12:08 DEBUG: INSERT INTO ticket_flights_archive - 3 rows
2020-06-20 23:12:08 INFO: END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: flights - archive_by_ids 2 rows by flight_id
2020-06-20 23:12:09 DEBUG: SQL('CREATE TABLE IF NOT EXISTS bookings.flights_archive (LIKE bookings.flights)')
2020-06-20 23:12:09 DEBUG: DELETE FROM flights by flight_id - 2 rows
2020-06-20 23:12:09 DEBUG: INSERT INTO flights_archive - 2 rows
2020-06-20 23:12:09 INFO: flights - END
Thola ukuncika kwetafula elishiwo
>>> from pg_graph.api import PgGraphApi
>>> from pprint import pprint
>>> api = PgGraphApi('config.hw.local.ini')
>>> res = api.get_table_references('flights')
>>> pprint(res)
{'in_refs': {'ticket_flights': [ForeignKey(pk_main='flight_id', pk_ref='flight_id, ticket_no', fk_ref='flight_id')]},
'out_refs': {'aircrafts': [ForeignKey(pk_main='aircraft_code', pk_ref='flight_id', fk_ref='aircraft_code')],
'airports': [ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='arrival_airport'),
ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='departure_airport')]}}
Ukuthola izinkomba zeyunithi yezinhlamvu ezinokhiye Oyinhloko oshiwo
>>> from pg_graph.api import PgGraphApi
>>> from pprint import pprint
>>> api = PgGraphApi('config.hw.local.ini')
>>> rows = api.get_rows_references('flights', [1,2,3])
>>> pprint(rows)
{1: {'ticket_flights': {'flight_id': [{'flight_id': 1,
'ticket_no': '0005432816945'},
{'flight_id': 1,
'ticket_no': '0005432816941'}]}},
2: {'ticket_flights': {'flight_id': [{'flight_id': 2,
'ticket_no': '0005433101832'},
{'flight_id': 2,
'ticket_no': '0005433101864'},
{'flight_id': 2,
'ticket_no': '0005432919715'}]}},
3: {'ticket_flights': {'flight_id': [{'flight_id': 3,
'ticket_no': '0005432817560'},
{'flight_id': 3,
'ticket_no': '0005432817568'},
{'flight_id': 3,
'ticket_no': '0005432817559'}]}}}
Ikhodi yomthombo welabhulali iyatholakala kokuthi
Ngizojabula ukuphawula, ukuzibophezela kanye neziphakamiso.
Ngizozama ukuphendula imibuzo ngokusemandleni ami lapha kanye nasenqolobaneni.
Source: www.habr.com