ProHoster > Blog > Tsamaiso > PgGraph ke sesebelisoa sa ho boloka le ho fumana litšepiso tsa litafole ho PostgreSQL

PgGraph ke sesebelisoa sa ho boloka le ho fumana litšepiso tsa litafole ho PostgreSQL

PgGraph ke sesebelisoa sa ho boloka le ho fumana litšepiso tsa litafole ho PostgreSQL
Kajeno ke batla ho hlahisa babali ba Habr ka sesebelisoa se ngotsoeng ho Python bakeng sa ho sebetsa ka ho itšetleha ka litafole ho PostgreSQL DBMS.

API ea ts'ebeliso e bonolo ebile e na le mekhoa e meraro:

  • archive_tafole - ho pheta-pheta / ho hlakola mela e nang le linotlolo tsa mantlha tse boletsoeng
  • get_table_references - batla ho itšetleha ka tafole (e tla bonts'a litafole tse boletsoeng ke e boletsoeng le ba e supang)
  • get_rows_references - batla mela e litafoleng tse ling e supang mela e boletsoeng tafoleng e batloang

prehistory

Lebitso la ka ke Oleg Borzov, ke moqapi sehlopheng sa CRM bakeng sa batsamaisi ba likalimo tsa ntlo Domklik.

Database ea mantlha ea sistimi ea rona ea CRM ke e 'ngoe ea tse kholo ka ho fetisisa mabapi le bophahamo ba k'hamphani. Hape ke e 'ngoe ea tsa khale ka ho fetisisa: e hlahile ha ho qalisoa morero, ha lifate li ne li le khōlō, Domklik e ne e le qalo,' me ho e-na le microservice ka mokhoa oa feshene oa Python asynchronous ho ne ho e-na le monolith e kholo ho PHP.

Phetoho ea ho tloha PHP ho ea ho Python e ne e le telele haholo 'me e hloka tšehetso e ts'oanang ea tsamaiso ka bobeli, e ileng ea ama moralo oa database.

Ka lebaka leo, re na le database e nang le palo e kholo ea litafole tse hokahaneng haholo le tse kholo tse nang le sehlopha sa li-index bakeng sa mefuta e fapaneng ea lipotso. Sena sohle se ama ts'ebetso ea database hampe: ka lebaka la litafole tse kholo le likamano tse ngata pakeng tsa bona, ho rarahana ha lipotso ho ntse ho eketseha, e leng ntho ea bohlokoa haholo bakeng sa litafole tse jereng haholo.

Ho fokotsa mojaro ho database, re nkile qeto ea ho ngola sengoloa se neng se tla fetisetsa lirekoto tsa khale ho tloha litafoleng tse matla le tse laetsoeng ho ea ho tse bolokiloeng (mohlala, ho tloha task в task_archive).

Mosebetsi ona o thatafatsoa ke palo e kholo ea likamano lipakeng tsa litafole: suthisa mela ho tloha feela task в task_archive ha hoa lekana, pele ho moo o hloka ho etsa se tšoanang ka ho pheta-pheta le litšupiso tseo kaofela task litafole.

Ke tla bontša ka mohlala database ea demo ho tsoa sebakeng sa postgrespro.ru:

PgGraph ke sesebelisoa sa ho boloka le ho fumana litšepiso tsa litafole ho PostgreSQL
Ha re re re hloka ho hlakola lirekoto tafoleng Flights. Li-postgres li ke ke tsa re lumella ho etsa sena ka tsela e joalo: re tlameha ho qala ka ho hlakola lirekoto ho tsoa litafoleng tsohle tsa litšupiso, joalo-joalo, re khutlela litafoleng tse sa buuoeng ke mang kapa mang.

Mohlala oa rona ho Flights e bolela Ticket_flights, le ho yena - Boarding_passes.

Ka hona, o hloka ho e hlakola ka tatellano ena:

  1. Re fumana linotlolo tsa mantlha (PK) boleng ba mela ho Ticket_flights, e bolelang mela e tla hlakolwa ho Flights.
  2. Re fumana mela ea PK Boarding_passes, tse buang ka Ticket_flights.
  3. Re hlakola mela ka PK ho tloha mohato oa 2 tafoleng Boarding_passes.
  4. Tlosa mela ka PK ho tloha mohato oa 1 ho Ticket_flights.
  5. Ho tlosa mela ho Flights.

Sephetho e bile sesebelisoa se bitsoang PgGraph, seo re ileng ra etsa qeto ea ho se etsa mohloli o bulehileng.

Mokhoa oa ho sebelisa

Sesebelisoa se tšehetsa mekhoa e 'meli ea tšebeliso:

  • Letsa ho tsoa moleng oa taelo (pggraph …).
  • Tšebeliso ea khoutu ea Python (class PgGraphApi).

Ho kenya le ho hlophisa

Pele o hloka ho kenya ts'ebeliso ho tsoa sebakeng sa polokelo ea Pypi:

pip3 install pggraph

Ebe u theha faele ea config.ini mochining oa lehae ka tlhophiso ea database le script ea polokelo:

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

Baleha ho tsoa ho console

entsprechen

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

Likhang tsa maemo:

  • action - ts'ebetso e hlokahalang: archive_table, get_table_references kapa get_rows_references.

Likhang tse rehelletsoeng:

  • --config_path - tsela ea faele ea config;
  • --table - tafole eo u hlokang ho etsa ketso ka eona;
  • --ids - lenane la id le arotsoe ka lifehelo, mohlala, 1,2,3 (khetho ea parameter);
  • --log_path - tsela e eang ho foldara bakeng sa li-log (parameter ea boikhethelo, ka ho sa feleng - foldara ea lapeng);
  • --log_level - boemo ba ho rema lifate (parameter ea boikhethelo, kamehla ke INFO).

Mehlala ea litaelo

Ho boloka tafole

Mosebetsi o ka sehloohong oa ts'ebeliso ke ho boloka boitsebiso, ke hore. ho fetisa mela ho tloha tafoleng e kholo ho ea tafoleng ea polokelo (mohlala, ho tloha tafoleng libuka в libuka_polokelo).

Ho tlosoa ntle le ho boloka boitsebiso ho boetse ho tšehetsoa: bakeng sa sena o hloka ho beha parameter ho config.ini to_archive = bohata).

Li-parameter tse hlokahalang - config_path, tafole le li-id.

Kamora ho qala, lirekoto li tla hlakoloa khafetsa ids tafoleng table le litafoleng tsohle tse buang ka eona.

$ 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

Fumana litšepeho bakeng sa tafole e boletsoeng

Mosebetsi oa ho fumana litšepiso tsa tafole e boletsoeng table. Li-parameter tse hlokahalang - config_path и table.

Kamora ho qala, buka e hlalosang mantsoe e tla hlahisoa skrineng, moo:

  • in_refs — dikishinari ea litafole e buang ka e fanoeng, moo senotlolo e leng lebitso la tafole, boleng ke lethathamo la lintho tsa bohlokoa tsa kantle ho naha (pk_main - senotlolo sa mantlha tafoleng e kholo, pk_ref - senotlolo sa mantlha tafoleng ea litšupiso, fk_ref - lebitso la kholomo eo e leng senotlolo sa kantle ho tafole ea mohloli);
  • out_refs — bukantswe ya ditafole ena e bua ka yona.

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

Ho fumana litšupiso tsa likhoele tse nang le Key Key e boletsoeng

Mosebetsi oa ho batla mela ho litafole tse ling tse buang ka mela ka Key Key ids litafole table. Li-parameter tse hlokahalang - config_path, table и ids.

Kamora ho qala, buka e hlalosang mantsoe e nang le sebopeho se latelang e tla hlahisoa skrineng:

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

Mohala oa mohlala:

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

Tšebeliso ka khoutu

Ntle le ho e tsamaisa ka har'a khomphutha, laeborari e ka sebelisoa ka khoutu ea Python. Mehlala ea mehala e tikolohong e sebelisanang ea iPython e bonts'itsoe ka tlase.

Ho boloka tafole

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

Fumana litšepeho bakeng sa tafole e boletsoeng

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

Ho fumana litšupiso tsa likhoele tse nang le Key Key e boletsoeng

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

Khoutu ea mohloli oa laebrari e fumaneha ho GitHub tlas'a laesense ea MIT, hammoho le sebakeng sa polokelo PyPI.

Ke tla thabela ho fana ka maikutlo, boitlamo le litlhahiso.

Ke tla leka ho araba lipotso ka hohle kamoo nka khonang mona le sebakeng sa polokelo.

Source: www.habr.com

Eketsa ka tlhaloso