ProHoster > Blog > Utongi > PgGraph chinhu chinoshandiswa kuchengetedza uye kutsvaga tafura inotsamira muPostgreSQL

PgGraph chinhu chinoshandiswa kuchengetedza uye kutsvaga tafura inotsamira muPostgreSQL

PgGraph chinhu chinoshandiswa kuchengetedza uye kutsvaga tafura inotsamira muPostgreSQL
Nhasi ndinoda kuratidza vaverengi veHabr nechishandiso chakanyorwa muPython chekushanda nematafura muPostgreSQL DBMS.

Iyo yekushandisa's API iri nyore uye ine nzira nhatu:

  • archive_table - inodzokorodza kuchengetedza / kudzima mitsara ine yakatsanangurwa Makiyi Ekutanga
  • get_table_references - tsvaga kutsamira patafura (icharatidza matafura anotaridzwa neakatsanangurwa uye avo vanoaratidza)
  • get_rows_references - tsvaga mitsara mune mamwe matafura anonongedza mitsara yakatsanangurwa mutafura yaunoda

prehistory

Ini ndinonzi Oleg Borzov, ndiri mugadziri muchikwata cheCRM kune mamaneja ekukweretesa mbande muDomklik.

Iyo huru dhatabhesi yeCRM system yedu ndeimwe yakakura maererano nehuwandu mukambani. Iyo zvakare ndeimwe yekare: yakaonekwa pakuvhurwa chaiko kweprojekiti, apo miti yaive mikuru, Domklik yaive yekutanga, uye pachinzvimbo chemicroservice pane yefashoni Python asynchronous framework pakanga paine hombe monolith muPHP.

Shanduko kubva kuPHP kuenda kuPython yaive yakareba kwazvo uye yaida kutsigirwa panguva imwe chete yemasisitimu ese ari maviri, ayo akakanganisa dhizaini yedatabase.

Nekuda kweizvozvo, isu tine dhatabhesi ine nhamba huru yematafura akabatana zvakanyanya uye akakura ane boka rema indexes emhando dzakasiyana dzemibvunzo. Zvose izvi zvinokanganisa kushanda kwe database: nekuda kwematafura makuru uye boka rehukama pakati pavo, kuoma kwemibvunzo kunoramba kuchiwedzera, iyo inonyanya kukosha kune matafura akaremerwa.

Kuti tideredze mutoro padhatabhesi, takasarudza kunyora script yaizoendesa marekodhi ekare kubva kune akanyanya kusimba uye akaremerwa matafura kune akachengetwa (semuenzaniso, kubva task Π² task_archive).

Iri basa rinoomeswa nehuwandu hukuru hwehukama pakati pematafura: ingo fambisa mitsara kubva task Π² task_archive hazvina kukwana, zvisati zvaitika iwe unofanirwa kuita zvakafanana kudzokorora neavo vese mareferensi task matafura.

Ndicharatidza nemuenzaniso demo database kubva kune saiti postgrespro.ru:

PgGraph chinhu chinoshandiswa kuchengetedza uye kutsvaga tafura inotsamira muPostgreSQL
Ngatitii tinoda kudzima marekodhi kubva patafura Flights. Postgres haizotibvumidze kuita izvi sekudaro: isu chekutanga tinoda kudzima marekodhi kubva kune ese mareferensi matafura, uye zvichingodaro kudzokorora kudzika kumatafura asina kutaurwa nemunhu.

Mumuenzaniso wedu pa Flights zvinoreva Ticket_flights, uye paari - Boarding_passes.

Naizvozvo, iwe unofanirwa kuidzima nenzira iyi:

  1. Isu tinowana makiyi ekutanga (PK) kukosha kwemitsara mukati Ticket_flights, iyo inoreva mitsetse inofanira kudzimwa mukati Flights.
  2. Tinowana PK mitsara Boarding_passes, izvo zvinoreva Ticket_flights.
  3. Isu tinodzima mitsara nePK kubva padanho 2 patafura Boarding_passes.
  4. Bvisa mitsetse nePK kubva padanho 1 mukati Ticket_flights.
  5. Kubvisa mitsetse kubva Flights.

Mhedzisiro yacho yaive yekushandisa inonzi PgGraph, yatakafunga kugadzira yakavhurika sosi.

Nzira yekushandisa sei

Iyo yekushandisa inotsigira nzira mbiri dzekushandisa:

  • Dana kubva pamutsetse wekuraira (pggraph …).
  • Kushandiswa muPython kodhi (kirasi PgGraphApi).

Kuiswa uye kumisikidzwa

Kutanga iwe unofanirwa kuisa iyo yekushandisa kubva kuPypi repository:

pip3 install pggraph

Wobva wagadzira config.ini faira pamushini wemuno nekumisikidzwa kwedhatabhesi uye script yekuchengeta:

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

Mhanya kubva kune console

parameters

$ 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)

Positional nharo:

  • action -chiito chinodiwa: archive_table, get_table_references kana get_rows_references.

Mapoka ane mazita:

  • --config_path - nzira kune iyo config faira;
  • --table - tafura yaunoda kuita nayo chiito;
  • --ids - rondedzero yeid yakaparadzaniswa nemakoma, semuenzaniso, 1,2,3 (sarudzo parameter);
  • --log_path - nzira yekuenda kune dhairekitori yematanda (inosarudzika parameter, neyakagadzika - folda yekumba);
  • --log_level - danho rekutema matanda (inosarudza parameter, default ndeye INFO).

Mirairo mienzaniso

Kuchengeta tafura

Basa guru rekushandisa ndeye data archiving, i.e. kuendesa mitsara kubva patafura huru kuenda kutafura yekuchengetera (semuenzaniso, kubva patafura mabhuku Π² mabhuku_dura).

Kubvisa pasina kuchengetedza kunotsigirwawo: nokuda kweizvi unoda kuisa parameter mu config.ini ku_archive = nhema).

Zvinodiwa parameters - config_path, tafura uye maid.

Mushure mekutangwa, marekodhi anozodzimwa zvakare ids patafura table uye mumatafura ose anotaura nezvayo.

$ 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

Tsvaga zvinoenderana netafura yakatarwa

Basa rekutsvaga zvinoenderana netafura yakatarwa table. Zvinodiwa parameters - config_path ΠΈ table.

Mushure mekutangwa, duramazwi richaratidzwa pachiratidziri, apo:

  • in_refs - duramazwi rematafura rinonongedza yakapihwa, uko kiyi izita retafura, kukosha rondedzero yezvinhu zveKune dzimwe nyika Key (pk_main - kiyi yekutanga mutafura huru, pk_ref - kiyi yekutanga mutafura yekurevera, fk_ref - zita rekoramu inova kiyi yekune dzimwe nyika kune tafura yekubva;
  • out_refs β€” duramazwi rematafura rinorehwa naro.

$ 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')]}}

Kutsvaga mareferensi kune tambo ine yakataurwa Primary Key

Basa rekutsvaga mitsara mune mamwe matafura anoreva mitsara kuburikidza Nekunze Kiyi ids tafura table. Zvinodiwa parameters - config_path, table ΠΈ ids.

Mushure mekutangwa, duramazwi rine chimiro chinotevera richaratidzwa pachiratidziri:

{
	pk_id_1: {
		reffering_table_name_1: {
			foreign_key_1: [
				{row_pk_1: value, row_pk_2: value},
				...
			], 
			...
		},
		...
	},
	pk_id_2: {...},
	...
}

Muenzaniso kufona:

$ 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'}]}}}

Kushandiswa mukodhi

Pamusoro pekuimhanyisa mukoni, raibhurari inogona kushandiswa muPython kodhi. Mienzaniso yemafoni muPython inopindirana nharaunda inoratidzwa pazasi.

Kuchengeta tafura

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

Tsvaga zvinoenderana netafura yakatarwa

>>> 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')]}}

Kutsvaga mareferensi kune tambo ine yakataurwa Primary Key

>>> 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'}]}}}

Iyo library source code inowanikwa pa GitHub pasi peMIT rezenisi, uye mune repository P&PI.

Ini ndichafara kune makomendi, kuzvipira uye mazano.

Ini ndichaedza kupindura mibvunzo nekugona kwangu pano uye mune repository.

Source: www.habr.com

Voeg