ProHoster > Блог > Whakahaerenga > Ko te PgGraph he taputapu mo te pupuri me te rapu i nga whakawhirinakitanga ripanga ki PostgreSQL

Ko te PgGraph he taputapu mo te pupuri me te rapu i nga whakawhirinakitanga ripanga ki PostgreSQL

Ko te PgGraph he taputapu mo te pupuri me te rapu i nga whakawhirinakitanga ripanga ki PostgreSQL
I tenei ra e hiahia ana ahau ki te whakaatu i nga kaipanui Habr me tetahi taputapu kua tuhia ki te Python mo te mahi me nga whakawhirinaki tepu i roto i te PostgreSQL DBMS.

He ngawari te API o te taputapu me nga tikanga e toru:

  • tepu_puranga - te whakapuranga / te whakakore i nga rarangi me nga Kī Tuatahi kua tohua
  • tiki_tepu_tohutohu — rapu mo nga whakawhirinakitanga mo tetahi ripanga (ka whakaatu nga ripanga e tohuhia ana e te mea kua tohua me era e tohutoro ana)
  • tiki_rarangi_tohutohu - rapua nga rarangi i etahi atu ripanga e tohutoro ana i nga rarangi i tohua i te ripanga e hiahiatia ana

prehistory

Ko Oleg Borzov toku ingoa, he kaiwhakawhanake ahau i te roopu CRM mo nga kaiwhakahaere mokete mokete i Domklik.

Ko te papaunga raraunga matua o ta maatau punaha CRM tetahi o nga mea nui rawa atu mo te rahinga o te roopu i roto i te kamupene. Koia ano tetahi o nga mea tawhito: i puta mai i te timatanga o te kaupapa, i te wa e nui ana nga rakau, ko Domklik he whakaoho, a, hei utu mo te microservice i runga i te anga hangahanga a Python, he nui te monolith i roto i te PHP.

He roa rawa te whakawhiti mai i te PHP ki te Python, me te tautoko i te wa kotahi mo nga punaha e rua, i pa ki te hoahoa o te putunga raraunga.

Ko te mutunga mai, kei a matou he papaa raraunga me te maha o nga ripanga tino hono me te nui me te paihere o nga tohu mo nga momo patai. Ko enei mea katoa ka pa kino ki te mahinga o te papaaarangi: na te nui o nga tepu me te maha o nga hononga i waenga i a raatau, kei te piki haere tonu te uaua o nga patai, he mea tino nui mo nga tepu kua utaina.

Hei whakaiti i te uta i runga i te papaaarangi, i whakatau matou ki te tuhi i tetahi tuhinga ka whakawhiti i nga rekoata tawhito mai i nga teepu tino nui me te utaina ki nga papapuranga (hei tauira, mai i task в task_archive).

He uaua tenei mahi na te maha o nga hononga i waenga i nga ripanga: nekehia nga rarangi mai task в task_archive kaore i te nui, i mua i tera me mahi ano koe me era tohutoro katoa task tepu.

Ka whakaatu ahau me tetahi tauira pātengi raraunga demo mai i te pae postgrespro.ru:

Ko te PgGraph he taputapu mo te pupuri me te rapu i nga whakawhirinakitanga ripanga ki PostgreSQL
Me kii me mukua nga rekoata mai i te ripanga Flights. Kare a Postgres e whakaae kia penei ta tatou mahi penei: me muku i nga rekoata mai i nga ripanga tohutoro katoa, me te ahu whakamua ki raro ki nga ripanga karekau i tohua e tetahi.

I to tatou tauira i Flights kōrero Ticket_flights, a ki runga ki a ia - Boarding_passes.

No reira, me muku koe i tenei raupapa:

  1. Ka whiwhi tatou i nga uara matua (PK) o nga rarangi kei roto Ticket_flights, e tohu ana ki nga rarangi ka mukua ki roto Flights.
  2. Ka whiwhi tatou i nga rarangi PK Boarding_passes, e tohu ana Ticket_flights.
  3. Ka mukua e matou nga rarangi a PK mai i te taahiraa 2 i te ripanga Boarding_passes.
  4. Mukua nga rarangi ma te PK mai i te taahiraa 1 i roto Ticket_flights.
  5. Te tango raina mai Flights.

Ko te hua ko te taputapu e kiia nei ko PgGraph, i whakatauhia e matou ki te hanga puna tuwhera.

Me pehea te whakamahi

Ka tautokohia e te taputapu e rua nga momo whakamahi:

  • Waea mai i te rarangi whakahau (pggraph …).
  • Te whakamahi i te waehere Python (class PgGraphApi).

Whakauru me te whirihoranga

Tuatahi me whakauru koe i te taputapu mai i te putunga Pypi:

pip3 install pggraph

Na ka hanga he konae config.ini i runga i te miihini a-rohe me te whirihoranga o te putunga raraunga me te tuhinga whakapuranga:

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

Rere mai i te papatohu

tawhā

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

Tohenga tūnga:

  • action - mahi e hiahiatia ana: archive_table, get_table_references ranei get_rows_references.

Nga tohenga ingoa:

  • --config_path — ara ki te konae whirihora;
  • --table — he ripanga e hiahia ana koe ki te mahi i tetahi mahi;
  • --ids — rarangi o nga id kua wehea e nga piko, hei tauira, 1,2,3 (tawhā kōwhiringa);
  • --log_path — ara ki te kōpaki mō ngā rākau (tawhā kōwhiringa, taunoa - kōpaki kāinga);
  • --log_level — taumata takiuru (tawhā kōwhiringa, taunoa ko INFO).

Tauira whakahau

Puranga tepu

Ko te mahi matua o te whaipainga ko te penapena raraunga, i.e. te whakawhiti haupae mai i te ripanga matua ki te ripanga pūranga (hei tauira, mai i te ripanga pukapuka в pukapuka_puranga).

Ka tautokohia hoki te whakakore me te kore whakapuranga: mo tenei me whakarite e koe te tawhā ki config.ini ki_pūranga = teka).

Tawhā e hiahiatia ana - config_path, ripanga me ids.

I muri i te whakarewatanga, ka mukua nga rekoata ids i roto i te tepu table me nga tepu katoa e korero ana.

$ 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

Kimihia nga whakawhirinakitanga mo te ripanga kua tohua

Taumahi ki te rapu i nga whakawhirinakitanga o te ripanga kua tohua table. Tawhā e hiahiatia ana - config_path и table.

Whai muri i te whakarewatanga, ka whakaatuhia he papakupu ki te mata, kei reira:

  • in_refs — he papakupu o nga ripanga e tohutoro ana i tetahi mea kua homai, ko te mea matua ko te ingoa o te ripanga, ko te uara he rarangi o nga ahanoa matua ke (pk_main - te kī matua i te ripanga matua, pk_ref - matua matua i roto i te ripanga tohutoro, fk_ref — te ingoa o te tīwae ko te kī ke ki te ripanga pūtake);
  • out_refs — he papakupu o nga papa e korero ana tenei.

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

Te kimi tohutoro ki nga aho me te Kī Matua kua tohua

Mahi ki te rapu rarangi i etahi atu ripanga e tohu ana ki nga rarangi ma te Kī Tawahi ids ripanga table. Tawhā e hiahiatia ana - config_path, table и ids.

Whai muri i te whakarewatanga, ka whakaatuhia he papakupu e whai ake nei ki te mata:

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

Tauira waea:

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

Te whakamahi i roto i te waehere

I tua atu i te whakahaere i roto i te papatohu, ka taea te whakamahi i te whare pukapuka ki te waehere Python. Ko nga tauira o nga waea i roto i te taiao tauwhitiwhiti iPython e whakaatuhia ana i raro nei.

Puranga tepu

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

Kimihia nga whakawhirinakitanga mo te ripanga kua tohua

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

Te kimi tohutoro ki nga aho me te Kī Matua kua tohua

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

Kei te waatea te waehere puna puna i GitHub i raro i te raihana MIT, me i roto i te putunga PyPI.

Ka koa ahau ki nga korero, ki te tuku whakaaro me nga whakaaro.

Ka ngana ahau ki te whakautu i nga patai i runga i taku kaha ki konei, ki roto hoki i te putunga.

Source: will.com

Tāpiri i te kōrero