ProHoster > Блог > Nchịkwa > PgGraph bụ akụrụngwa maka ịchekwa na ịchọta ndabere tebụl na PostgreSQL

PgGraph bụ akụrụngwa maka ịchekwa na ịchọta ndabere tebụl na PostgreSQL

PgGraph bụ akụrụngwa maka ịchekwa na ịchọta ndabere tebụl na PostgreSQL
Taa, achọrọ m iweta ndị na-agụ Habr nwere akụrụngwa edere na Python maka ịrụ ọrụ dabere na tebụl na PostgreSQL DBMS.

API ngwa ọrụ dị mfe ma nwee ụzọ atọ:

  • Archive_table - idekọ ndekọ/ihichapụ ahịrị ndị nwere igodo izizi akọwapụtara
  • nweta_table_reference - chọọ ihe ndabere maka tebụl (ga-egosi tebụl nke akọwapụtara na ndị na-ekwu ya)
  • nweta ntụaka_rows - chọọ ahịrị na tebụl ndị ọzọ na-ezo aka n'ahịrị ndị akọwapụtara na tebụl achọrọ

prehistory

Aha m bụ Oleg Borzov, abụ m onye nrụpụta na otu CRM maka ndị na-ahụ maka ịgbazinye ego na Domklik.

Isi nchekwa data nke usoro CRM anyị bụ otu n'ime ndị kasị ibu n'ihe gbasara olu na ụlọ ọrụ. Ọ bụkwa otu n'ime ndị kasị ochie: ọ pụtara na mmalite nke oru ngo, mgbe osisi ndị buru ibu, Domklik bụ mmalite, na kama a microservice na a fashionable Python asynchronous framework e nwere nnukwu monolith na PHP.

Mgbanwe site na PHP gaa Python dị ogologo ma chọọ nkwado nke sistemu abụọ a n'otu oge, nke metụtara imewe nchekwa data.

N'ihi ya, anyị nwere nchekwa data nwere ọnụ ọgụgụ buru ibu nke nwere njikọ dị ukwuu na nnukwu tebụl nwere ụyọkọ indexes maka ụdị ajụjụ dị iche iche. Ihe a niile na-emetụta arụmọrụ nke nchekwa data na-adịghị mma: n'ihi nnukwu tebụl na ụyọkọ mmekọrịta dị n'etiti ha, mgbagwoju anya nke ajụjụ na-arịwanye elu mgbe niile, nke dị oke mkpa maka tebụl ndị kacha ibu.

Iji belata ibu dị na nchekwa data, anyị kpebiri ide edemede nke ga-ebufe ihe ndekọ ochie site na tebụl ndị kachasị ụda na nke a na-ebunye na ndị echekwara (dịka ọmụmaatụ, site na. task в task_archive).

Ọrụ a gbagwojuru anya site na ọnụ ọgụgụ dị ukwuu nke mmekọrịta dị n'etiti tebụl: naanị bugharịa ahịrị site na task в task_archive ezughị ezu, tupu nke ahụ ịkwesịrị ime otu ihe ahụ recursively na ndị niile na-ezo aka task tebụl.

M ga-eji ihe atụ gosi nchekwa data ngosi sitere na saịtị postgrespro.ru:

PgGraph bụ akụrụngwa maka ịchekwa na ịchọta ndabere tebụl na PostgreSQL
Ka anyị kwuo na anyị kwesịrị ihichapụ ndekọ na tebụl Flights. Postgres agaghị ekwe ka anyị mee nke a dị ka nke ahụ: anyị kwesịrị ibu ụzọ ihichapụ ihe ndekọ na tebụl nrụtụ aka niile, na ihe ndị ọzọ na-aga n'ihu na tebụl ndị na-adịghị onye ọ bụla kwuru.

N'ihe atụ anyị na Flights na-ezo aka Ticket_flightsna ya - Boarding_passes.

Ya mere, ị ga-ehichapụ ya n'usoro a:

  1. Anyị na-enweta uru igodo mbụ (PK) nke ahịrị Ticket_flights, nke na-ezo aka na ahịrị ndị a ga-ehichapụ na Flights.
  2. Anyị na-enweta ahịrị PK Boarding_passes, nke na-ezo aka Ticket_flights.
  3. Anyị na-ehichapụ ahịrị site na PK site na nzọụkwụ 2 na tebụl Boarding_passes.
  4. Hichapụ ahịrị site na PK site na nzọụkwụ 1 in Ticket_flights.
  5. Na-ewepu ahịrị si Flights.

Nsonaazụ bụ akụrụngwa akpọrọ PgGraph, nke anyị kpebiri ime oghere mepere emepe.

Otu esi eji

Ngwa ahụ na-akwado ụdị ojiji abụọ:

  • Kpọọ site na ahịrị iwu (pggraph …).
  • Ojiji na koodu Python (klas PgGraphApi).

Ntinye na nhazi

Mbụ ị ga-achọ ịwụnye akụrụngwa site na ebe nchekwa Pypi:

pip3 install pggraph

Wee mepụta faịlụ config.ini na igwe mpaghara yana nhazi nke nchekwa data yana edemede nchekwa:

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

Gbaa na 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)

Esemokwu ọnọdụ:

  • action - ihe achọrọ: archive_table, get_table_references ma ọ bụ get_rows_references.

Arụmụka akpọrọ aha:

  • --config_path - ụzọ na faịlụ nhazi;
  • --table - tebụl nke ị chọrọ iji mee ihe;
  • --ids - ndepụta id nke rịkọm kewapụrụ, dịka ọmụmaatụ, 1,2,3 (nhọrọ oke);
  • --log_path - ụzọ na nchekwa maka ndekọ (nhọrọ oke, ndabara - nchekwa ụlọ);
  • --log_level - ọkwa ndekọ (nhọrọ oke, ndabara bụ INFO).

Ihe atụ iwu

Ịdebe tebụl

Isi ọrụ nke akụrụngwa bụ nchekwa data, ya bụ. na-ebufe ahịrị site na tebụl isi gaa na tebụl ebe nchekwa (dịka ọmụmaatụ, site na tebụl akwụkwọ в akwụkwọ_nchekwa).

A na-akwadokwa ihichapụ na-enweghị ebe nchekwa: maka nke a ịkwesịrị ịtọ paramita na config.ini to_archive = ụgha).

Ihe ndị achọrọ - config_path, tebụl na ids.

Mgbe emepechara, a ga-ehichapụ ndekọ ugboro ugboro ids na tebụl table na na tebụl niile na-ezo aka na ya.

$ 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

Chọta ndabere maka tebụl akọwapụtara

Ọrụ ịchọta ndabere nke tebụl akọwapụtara table. Ihe ndị achọrọ - config_path и table.

Mgbe emepechara, a ga-egosipụta ọkọwa okwu na ihuenyo, ebe:

  • in_refs - akwụkwọ ọkọwa okwu nke tebụl na-ezo aka nyere otu, ebe igodo bụ aha tebụl, uru bụ ndepụta nke ihe ndị mba ọzọ (pk_main - isi igodo na tebụl isi, pk_ref - isi igodo na tebụl ntụaka, fk_ref - aha nke kọlụm nke bụ igodo mba ọzọ na tebụl isi iyi);
  • out_refs - akwụkwọ ọkọwa okwu nke tebụl nke a na-ezo aka na ya.

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

Ịchọta ntụaka maka eriri nwere igodo isi akọwapụtara

Ọrụ iji chọọ ahịrị na tebụl ndị ọzọ na-ezo aka na ahịrị site na igodo mba ofesi ids tebụl table. Ihe ndị achọrọ - config_path, table и ids.

Mgbe emepechara, a ga-egosipụta ọkọwa okwu nwere usoro a na ihuenyo:

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

Ọmụmaatụ oku:

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

Ojiji na koodu

Na mgbakwunye na ịgba ya na njikwa, enwere ike iji ọba akwụkwọ na koodu Python. Ihe atụ nke oku na mpaghara mmekọrịta iPython ka egosiri n'okpuru.

Ịdebe tebụl

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

Chọta ndabere maka tebụl akọwapụtara

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

Ịchọta ntụaka maka eriri nwere igodo isi akọwapụtara

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

Koodu isi mmalite ọba akwụkwọ dị na GitHub n'okpuru ikike MIT, yana na ebe nchekwa P&IP.

Ọ ga-amasị m ịza ajụjụ, ntinye aka na aro.

M ga-agbalị ịza ajụjụ niile ike m ebe a na ebe nchekwa.

isi: www.habr.com

Tinye a comment