🥇PgGraph - he mea pono no ka mālama ʻana a me ka ʻimi ʻana i nā hilinaʻi papa ma PostgreSQL

I kēia lā makemake wau e hāʻawi i ka poʻe heluhelu Habr me kahi mea pono i kākau ʻia ma Python no ka hana ʻana me nā hilinaʻi papa ma ka PostgreSQL DBMS.

He mea maʻalahi ka API o ka pono a he ʻekolu mau ala:

waihona_papa - ka hoʻopaʻa ʻana a me ka holoi ʻana i nā lālani me nā kī kumu i kuhikuhi ʻia
loaʻa_papa_kauoha - e ʻimi i nā hilinaʻi no kahi pākaukau (e hōʻike i nā papa i kuhikuhi ʻia e ka mea i kuhikuhi ʻia a me nā mea e kuhikuhi ana)
kiʻi_lālani_kuʻina - huli i nā lālani ma nā papa ʻē aʻe e kuhikuhi ana i nā lālani i ʻōlelo ʻia ma ka papa i makemake ʻia

prehistory

ʻO Oleg Borzov koʻu inoa, he mea hoʻomohala wau i ka hui CRM no nā luna hoʻolimalima moraki ma Domklik.

ʻO ka ʻikepili nui o kā mākou ʻōnaehana CRM kekahi o nā mea nui loa i ka nui o ka nui o ka hui. ʻO ia kekahi o nā mea kahiko loa: ua ʻike ʻia ma ka hoʻomaka ʻana o ka papahana, i ka wā i nui ai nā kumulāʻau, ʻo Domklik kahi hoʻomaka, a ma kahi o kahi microservice ma kahi ʻano Python asynchronous framework aia kahi monolith nui ma PHP.

Ua lōʻihi loa ka hoʻololi ʻana mai PHP a Python a koi ʻia i ke kākoʻo like ʻana o nā ʻōnaehana ʻelua, i pili i ka hoʻolālā ʻana o ka waihona.

ʻO ka hopena, loaʻa iā mākou kahi waihona me ka helu nui o nā papa i hoʻopili nui ʻia a nui me nā pūʻulu kuhikuhi no nā ʻano nīnau like ʻole. Hoʻopilikia maikaʻi kēia mau mea a pau i ka hana o ka waihona: ma muli o nā papa nui a me kahi hui o nā pilina ma waena o lākou, ke piʻi mau nei ka paʻakikī o nā nīnau, ʻo ia ka mea koʻikoʻi no nā papa i hoʻouka ʻia.

No ka hōʻemi ʻana i ka ukana ma ka waihona, ua hoʻoholo mākou e kākau i kahi palapala e hoʻololi i nā moʻolelo kahiko mai nā papa nui loa a i hoʻouka ʻia i nā papa waihona (no ka laʻana, mai task в task_archive).

Paʻakikī kēia hana e ka nui o nā pilina ma waena o nā papa: e neʻe wale i nā lālani mai task в task_archive ʻAʻole lawa, ma mua o kēlā pono ʻoe e hana like me ka recursively me kēlā mau kuhikuhi task nā papaʻaina.

E hōʻike aku au me kahi laʻana hōkeo ʻikepili mai ka pūnaewele postgrespro.ru:

E ʻōlelo mākou pono mākou e holoi i nā moʻolelo mai kahi pākaukau Flights. ʻAʻole ʻae ʻo Postgres iā mākou e hana i kēia e like me ia: pono mākou e holoi mua i nā moʻolelo mai nā papa kuhikuhi āpau, a pēlā aku e hoʻihoʻi i nā papa ʻaʻole i kuhikuhi ʻia e kekahi.

Ma kā mākou laʻana ma Flights pili Ticket_flights, a ma luna ona - Boarding_passes.

No laila, pono ʻoe e holoi iā ia ma kēia ʻano:

Loaʻa iā mākou nā kī nui (PK) o nā lālani i loko Ticket_flights, e pili ana i nā lālani e holoi ʻia ai Flights.
Loaʻa iā mākou nā lālani PK Boarding_passes, e pili ana i Ticket_flights.
Holoi mākou i nā lālani e PK mai ka ʻanuʻu 2 i ka pākaukau Boarding_passes.
Holoi i nā laina e PK mai ka ʻanuʻu 1 i loko Ticket_flights.
Ke wehe nei i nā laina mai Flights.

ʻO ka hopena he mea hoʻohana i kapa ʻia ʻo PgGraph, a mākou i hoʻoholo ai e hana i kahi kumu wehe.

Pehea e hoʻohana ai

Kākoʻo ka mea hoʻohana i ʻelua ʻano hoʻohana:

Kāhea mai ka laina kauoha (pggraph …).
Hoʻohana i ka code Python (class PgGraphApi).

Hoʻonoho a hoʻonohonoho ʻana

Pono ʻoe e hoʻokomo i ka pono mai ka waihona Pypi:

pip3 install pggraph

A laila e hana i kahi faila config.ini ma ka mīkini kūloko me ka hoʻonohonoho ʻana o ka waihona a me ka palapala waihona:

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

Holo mai ka console

kiko'î

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

Nā hoʻopaʻapaʻa kūlana:

action - pono hana: archive_table, get_table_references ai ole ia, get_rows_references.

Nā hoʻopaʻapaʻa inoa:

--config_path - ala i ka faila config;
--table - he papaʻaina e pono ai ʻoe e hana i kahi hana;
--ids - ka papa inoa o nā id i hoʻokaʻawale ʻia e nā koma, no ka laʻana, 1,2,3 (koho koho);
--log_path - ke ala i ka waihona no nā lāʻau (koho koho, ma ka paʻamau - home folder);
--log_level - ka pae logging (koho koho, ʻo INFO paʻamau).

Nā laʻana kauoha

Ke hoʻopaʻa ʻana i kahi pākaukau

ʻO ka hana nui o ka pono ʻo ka waihona ʻikepili, i.e. ka hoʻoili ʻana i nā lālani mai ka pākaukau nui i ka papa waihona waihona (no ka laʻana, mai ka pākaukau puke в waihona_puke).

Kākoʻo ʻia ka holoi ʻana me ka ʻole o ka waihona: no kēia pono ʻoe e hoʻonohonoho i ka ʻāpana ma config.ini to_archive = wahahee).

Nā ʻāpana e pono ai - config_path, papa a me nā id.

Ma hope o ka hoʻomaka ʻana, e holoi ʻia nā moʻolelo ids i ka papa table a ma na papa a pau e pili ana ia mea.

$ 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

E huli i nā mea hilinaʻi no kahi papa i ʻōlelo ʻia

Hana no ka huli ʻana i nā hilinaʻi o kahi pākaukau i kuhikuhi ʻia table. Nā ʻāpana e pono ai - config_path и table.

Ma hope o ka hoʻomaka ʻana, e hōʻike ʻia kahi puke wehewehe ʻōlelo ma ka pale, kahi:

in_refs - he puke wehewehe'ōlelo o nā papa e kuhikuhi ana i ka mea i hāʻawiʻia, kahi o ke kī i ka inoa o ka papaʻaina,ʻo ka waiwai he papa inoa o nā mea'ē aʻe (pk_main - kī nui ma ka papa nui, pk_ref - kī nui ma ka papa kuhikuhi, fk_ref - ka inoa o ke kolamu ʻo ia ke kī haole i ka papa kumu);
out_refs - he puke wehewehe'ōlelo o nā papa e pili ana kēia.

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

Ke ʻimi nei i nā kuhikuhi i nā kaula me ke kī kumu i kuhikuhi ʻia

Hana e huli i nā lālani ma nā papa ʻē aʻe e pili ana i nā lālani ma o Foreign Key ids papa table. Nā ʻāpana e pono ai - config_path, table и ids.

Ma hope o ka hoʻokuʻu ʻana, e hōʻike ʻia kahi puke wehewehe ʻōlelo me kēia ʻano hana ma ka pale:

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

Kahea laʻana:

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

Hoʻohana ma ke code

Ma waho aʻe o ka holo ʻana i ka console, hiki ke hoʻohana ʻia ka waihona ma ka code Python. Hōʻike ʻia ma lalo nei nā hiʻohiʻona o nā kelepona i ka iPython interactive environment.

Ke hoʻopaʻa ʻana i kahi pākaukau

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

E huli i nā mea hilinaʻi no kahi papa i ʻōlelo ʻia

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

Ke ʻimi nei i nā kuhikuhi i nā kaula me ke kī kumu i kuhikuhi ʻia

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

Loaʻa ka code kumu waihona ma GitHub ma lalo o ka laikini MIT, a me ka waihona ʻO PyPI.

E hauʻoli wau i nā manaʻo, nā hana a me nā manaʻo.

E hoʻāʻo wau e pane i nā nīnau i ka maikaʻi o koʻu hiki ma ʻaneʻi a ma ka waihona.

Source: www.habr.com

ʻO PgGraph kahi mea pono no ka mālama ʻana a me ka ʻimi ʻana i nā hilinaʻi papa ma PostgreSQL

prehistory

Pehea e hoʻohana ai

Hoʻonoho a hoʻonohonoho ʻana

Holo mai ka console

kiko'î

Nā laʻana kauoha

Ke hoʻopaʻa ʻana i kahi pākaukau

E huli i nā mea hilinaʻi no kahi papa i ʻōlelo ʻia

Ke ʻimi nei i nā kuhikuhi i nā kaula me ke kī kumu i kuhikuhi ʻia

Hoʻohana ma ke code

Ke hoʻopaʻa ʻana i kahi pākaukau

E huli i nā mea hilinaʻi no kahi papa i ʻōlelo ʻia

Ke ʻimi nei i nā kuhikuhi i nā kaula me ke kī kumu i kuhikuhi ʻia

Pākuʻi i ka manaʻo hoʻopuka ana pane