ProHoster > Блог > Gudanarwa > PgGraph kayan aiki ne don adanawa da gano abubuwan dogaro da tebur a cikin PostgreSQL

PgGraph kayan aiki ne don adanawa da gano abubuwan dogaro da tebur a cikin PostgreSQL

PgGraph kayan aiki ne don adanawa da gano abubuwan dogaro da tebur a cikin PostgreSQL
A yau ina so in gabatar da masu karatun Habr tare da kayan aiki da aka rubuta a cikin Python don aiki tare da masu dogaro da tebur a cikin PostgreSQL DBMS.

API ɗin mai amfani yana da sauƙi kuma ya ƙunshi hanyoyi uku:

  • Archive_tebur - recursive archiving/share layuka tare da ƙayyadadden Maɓallai na Farko
  • samun_tambayoyi - Nemo abubuwan dogaro ga tebur (zai nuna allunan da aka ƙayyade da waɗanda ke magana da shi)
  • samun_labarai_references - Nemo layuka a cikin wasu teburi waɗanda ke nuni da ƙayyadaddun layuka a cikin teburin da ake so

prehistory

Sunana Oleg Borzov, Ni mai haɓakawa ne a cikin ƙungiyar CRM don masu kula da lamuni na jinginar gida a Domklik.

Babban bayanan tsarin mu na CRM shine ɗayan mafi girma dangane da girma a cikin kamfani. Hakanan yana ɗaya daga cikin tsofaffi: ya bayyana a farkon ƙaddamar da aikin, lokacin da bishiyoyi suke da girma, Domklik ya kasance farkon farawa, kuma a maimakon microservice akan tsarin tsarin Python na zamani, akwai babban monolith a cikin PHP.

Canji daga PHP zuwa Python yana da tsayi sosai kuma yana buƙatar tallafi na lokaci guda na tsarin biyu, wanda ya shafi ƙirar bayanan.

A sakamakon haka, muna da rumbun adana bayanai tare da adadi mai yawa na haɗe-haɗe da manyan teburi tare da tarin fihirisa don nau'ikan tambayoyi daban-daban. Duk wannan yana haifar da mummunar tasiri ga aikin bayanan: saboda manyan tebur da gungun dangantaka tsakanin su, rikitattun tambayoyin suna karuwa akai-akai, wanda ke da mahimmanci ga mafi yawan nauyin tebur.

Don rage nauyin da ke kan ma'ajin bayanai, mun yanke shawarar rubuta rubutun da zai canja wurin tsofaffin bayanai daga mafi girman teburi da lodi zuwa waɗanda aka adana (misali, daga task в task_archive).

Wannan aikin yana da rikitarwa ta hanyar ɗimbin alaƙa tsakanin tebur: kawai matsar da layuka daga task в task_archive bai isa ba, kafin haka kuna buƙatar yin haka akai-akai tare da duk waɗanda ke magana task teburi.

Zan nuna da misali demo database daga shafin postgrespro.ru:

PgGraph kayan aiki ne don adanawa da gano abubuwan dogaro da tebur a cikin PostgreSQL
Bari mu ce muna buƙatar share bayanan daga tebur Flights. Postgres ba zai ƙyale mu mu yi haka kamar haka ba: da farko muna buƙatar share bayanan daga duk tebur ɗin bincike, da sauransu akai-akai har zuwa teburin da ba kowa ya ambata ba.

A cikin misalinmu a Flights yana nufin Ticket_flights, kuma akan ta - Boarding_passes.

Don haka, kuna buƙatar share shi ta wannan tsari:

  1. Muna samun ƙimar maɓallan farko (PK) na layuka a ciki Ticket_flights, wanda ke nufin layuka da za a goge a ciki Flights.
  2. Muna samun layuka na PK Boarding_passes, wanda ke nufin Ticket_flights.
  3. Muna share layuka ta PK daga mataki na 2 a cikin tebur Boarding_passes.
  4. Share layi ta PK daga mataki 1 in Ticket_flights.
  5. Cire layi daga Flights.

Sakamakon ya kasance mai amfani da ake kira PgGraph, wanda muka yanke shawarar yin buɗaɗɗen tushe.

Yadda zaka yi amfani

Mai amfani yana goyan bayan hanyoyin amfani guda biyu:

  • Kira daga layin umarni (pggraph …).
  • Amfani a cikin lambar Python (class PgGraphApi).

Shigarwa da daidaitawa

Da farko kuna buƙatar shigar da kayan aiki daga ma'ajin Pypi:

pip3 install pggraph

Sannan ƙirƙiri fayil ɗin config.ini akan na'urar gida tare da daidaita bayanan bayanai da rubutun adanawa:

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

Gudu daga console

sigogi

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

Hujjoji na matsayi:

  • action - aikin da ake buƙata: archive_table, get_table_references ko get_rows_references.

Hujjoji masu suna:

  • --config_path - hanyar zuwa fayil ɗin daidaitawa;
  • --table - tebur wanda kuke buƙatar yin aiki tare da shi;
  • --ids - jerin id da aka ware ta waƙafi, misali, 1,2,3 ( siga na zaɓi);
  • --log_path - hanyar zuwa babban fayil don rajistan ayyukan (madaidaicin zaɓi, ta tsohuwa - babban fayil na gida);
  • --log_level - matakin shiga (madaidaicin zaɓi, tsoho shine INFO).

Misalin umarni

Ajiye tebur

Babban aikin mai amfani shine adana bayanai, watau. canja wurin layuka daga babban tebur zuwa teburin adana bayanai (misali, daga tebur littattafai в littattafai_takardar bayanai).

Hakanan ana goyan bayan sharewa ba tare da adanawa ba: don wannan kuna buƙatar saita sigogi a cikin config.ini to_archive = karya).

Abubuwan da ake buƙata - config_path, tebur da ids.

Bayan ƙaddamarwa, za a goge bayanan akai-akai ids a cikin tebur table kuma a cikin dukkan allunan da suke magana akai.

$ 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

Nemo abin dogaro ga takamaiman tebur

Ayyuka don nemo abubuwan dogaro na takamaiman tebur table. Abubuwan da ake buƙata - config_path и table.

Bayan ƙaddamarwa, za a nuna ƙamus akan allon, inda:

  • in_refs - ƙamus na allunan da ke magana akan abin da aka bayar, inda maɓalli shine sunan tebur, ƙimar shine jerin abubuwan Maɓalli na Ƙasashen waje (pk_main - maɓallin farko a cikin babban tebur, pk_ref - maɓalli na farko a cikin tebur na nuni, fk_ref - sunan ginshiƙi wanda shine maɓalli na ƙasashen waje zuwa teburin tushe;
  • out_refs - ƙamus na tebur wannan yana nufin.

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

Nemo nassoshi ga kirtani tare da ƙayyadadden Maɓalli na Farko

Ayyuka don nemo layuka a cikin wasu teburi waɗanda ke nufin layuka ta Maɓallin Waje ids alluna table. Abubuwan da ake buƙata - config_path, table и ids.

Bayan ƙaddamarwa, ƙamus mai tsari mai zuwa za a nuna akan allon:

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

Misali kira:

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

Amfani a cikin code

Baya ga gudanar da shi a cikin na'ura wasan bidiyo, ana iya amfani da ɗakin karatu a cikin lambar Python. Ana nuna misalan kira a cikin mahallin hulɗar iPython a ƙasa.

Ajiye tebur

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

Nemo abin dogaro ga takamaiman tebur

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

Nemo nassoshi ga kirtani tare da ƙayyadadden Maɓalli na Farko

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

Ana samun lambar tushen ɗakin karatu a GitHub ƙarƙashin lasisin MIT, da kuma a cikin ma'ajin PyPI.

Zan yi farin cikin yin tsokaci, ƙaddamarwa da shawarwari.

Zan yi ƙoƙarin amsa tambayoyi gwargwadon iyawata anan da kuma cikin ma'ajiyar.

source: www.habr.com

Add a comment