I-ProHoster > Π‘Π»ΠΎΠ³ > Ukuphatha > I-PgGraph iyinsizakalo yokufaka kungobo yomlando nokuthola okuncikile kwetafula ku-PostgreSQL

I-PgGraph iyinsizakalo yokufaka kungobo yomlando nokuthola okuncikile kwetafula ku-PostgreSQL

I-PgGraph iyinsizakalo yokufaka kungobo yomlando nokuthola okuncikile kwetafula ku-PostgreSQL
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 Idatha yedemo evela kusayithi postgrespro.ru:

I-PgGraph iyinsizakalo yokufaka kungobo yomlando nokuthola okuncikile kwetafula ku-PostgreSQL
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:

  1. Sithola amanani okhiye abayinhloko (PK) bemigqa phakathi Ticket_flights, ebhekisela emigqeni ezosuswa kuyo Flights.
  2. Sithola imigqa ye-PK Boarding_passes, ezibhekisela ku Ticket_flights.
  3. Sisusa imigqa nge-PK esinyathelweni sesi-2 etafuleni Boarding_passes.
  4. Susa imigqa nge-PK kusukela kusinyathelo 1 phakathi Ticket_flights.
  5. 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 noma get_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.

ПослС запуска Π½Π° экран Π±ΡƒΠ΄Π΅Ρ‚ Π²Ρ‹Π²Π΅Π΄Π΅Π½ ΡΠ»ΠΎΠ²Π°Ρ€ΡŒ, Π³Π΄Π΅:

  • 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 GitHub ngaphansi kwelayisense ye-MIT, kanye nendawo yokugcina I-PyPI.

Ngizojabula ukuphawula, ukuzibophezela kanye neziphakamiso.

Ngizozama ukuphendula imibuzo ngokusemandleni ami lapha kanye nasenqolobaneni.

Source: www.habr.com

Engeza amazwana