O le asō ou te manaʻo e tuʻuina atu i le aufaitau Habr se mea aoga na tusia i le Python mo le galue ma faʻalagolago i laulau i le PostgreSQL DBMS.
Ole API ole aoga e faigofie ma e aofia ai auala e tolu:
- archive_table - fa'amaufa'ailogaina fa'amaumauga/tapē'ese laina fa'atasi ma Ki Primary ma'oti
- get_table_references - su'esu'e mo fa'alagolago mo se laulau (o le a fa'aalia laulau o lo'o fa'asino i ai ma i latou o lo'o fa'asino i ai)
- get_rows_references - su'e laina i isi laulau o lo'o fa'asino i laina fa'amaonia i le laulau mana'omia
prehistory
O loʻu igoa o Oleg Borzov, o aʻu o se atinaʻe i le vaega o le CRM mo pule mokesi mokesi i Domklik.
O le fa'amaumauga autu o la matou polokalama CRM o se tasi lea o le tele i tulaga o le voluma i totonu o le kamupani. O se tasi foi o le matua: na aliali mai i le amataga o le poloketi, ina ua lapopoa laau, o Domklik o se amataga, ma nai lo se microservice i luga o se faiga faʻavae asynchronous Python sa i ai se monolith tele i le PHP.
O le suiga mai le PHP i le Python sa umi tele ma manaʻomia le lagolago tutusa o faiga uma e lua, lea na aʻafia ai le mamanu o le database.
O se taunuuga, o loʻo i ai a matou faʻamaumauga faʻatasi ma se numera tele o laulau sili ona fesoʻotaʻi ma lapopoa faʻatasi ai ma le tele o faʻamatalaga mo ituaiga eseese o fesili. O nei mea uma e afaina ai le faʻatinoina o faʻamaumauga: ona o laulau tetele ma le tele o sootaga i le va oi latou, o le lavelave o fesili o loʻo faʻatupulaʻia pea, lea e sili ona taua mo laulau sili ona utaina.
Ina ia faʻaitiitia le uta i luga o faʻamaumauga, na matou filifili e tusi se tusitusiga e faʻafeiloaʻi ai faʻamaumauga tuai mai le tele o laulau ma faʻapipiʻiina i luga o faʻamaumauga (mo se faʻataʻitaʻiga, mai task в task_archive).
O lenei galuega e faigata ona o le tele o fesoʻotaʻiga i le va o laulau: naʻo le faʻanofoina o laina mai task в task_archive e le lava, aʻo leʻi oʻo i lena e manaʻomia ona e faia le mea lava lea e tasi faʻatasi ma na faʻasinomaga uma task laulau.
O le a ou faʻaalia i se faʻataʻitaʻiga :
Fa'apea e tatau ona tape fa'amaumauga mai se laulau Flights. E le fa'atagaina i tatou e Postgres e faia lenei mea e pei o lena: e mana'omia muamua ona tape fa'amaumauga mai laulau fa'asino uma, ma fa'asolo atu i lalo i laulau e le o fa'asinoina e se tasi.
I la tatou faataitaiga i Flights e faatatau Ticket_flights, ma i luga o ia- Boarding_passes.
O le mea lea, e tatau ona e tapeina i le faasologa lenei:
- Matou te maua ki autu (PK) tau o laina i totonu
Ticket_flights, lea e faasino i laina e tapeina i totonuFlights. - Matou te maua laina PK
Boarding_passes, lea e faasino iTicket_flights. - Matou te tape laina e PK mai le laasaga 2 i le laulau
Boarding_passes. - Aveese laina e le PK mai le laasaga 1 i totonu
Ticket_flights. - Aveese laina mai
Flights.
O le taunuuga o se aoga e taʻua o le PgGraph, lea na matou filifili e fai faʻamatalaga tatala.
Auala e faʻaoga ai
E lagolagoina e le faʻaoga auala e lua o le faʻaaogaina:
- Valaau mai le laina fa'atonu (
pggraph …). - Fa'aoga ile Python code (vasega
PgGraphApi).
Faʻapipiʻiina ma faʻatulagaina
Muamua e te manaʻomia le faʻapipiʻiina o le aoga mai le faleoloa Pypi:
pip3 install pggraphOna fatuina lea o se faila config.ini i luga o le masini i le lotoifale ma le faʻatulagaina o faʻamaumauga ma le faʻamaumauga faʻamaumauga:
[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'Tamomoe mai le faamafanafanaga
le faataamilosaga
$ 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)Fa'aupuga tulaga:
action- gaioiga mana'omia:archive_table,get_table_referencespoʻoget_rows_references.
Fa'aigoaina finauga:
--config_path- ala i le faila faila;--table- se laulau e te manaʻomia e fai ai se gaioiga;--ids— lisi o id ua vavaeeseina e koma, mo se faataitaiga,1,2,3(parakalafa faitalia);--log_path- ala i le faila mo ogalaau (faʻailoga filifiliga, e ala i le faaletonu - fale faila);--log_level- tulaga fa'amauina (fa'ailoga filifiliga, fa'aletonu ole INFO).
Fa'atonu fa'ata'ita'iga
Fa'amaumauga o se laulau
O le galuega autu a le aoga o le teuina o faʻamaumauga, i.e. fesiita'i laina mai le laulau autu i le laulau fa'amaumauga (mo se fa'ata'ita'iga, mai le laulau tusi в books_archive).
E lagolagoina foi le tapeina e aunoa ma le teuina: mo lenei mea e te manaʻomia le setiina o le parakalafa i config.ini to_archive = sese).
Fa'ailoga mana'omia - config_path, laulau ma id.
A maeʻa le faʻalauiloaina, o faʻamaumauga o le a toe tapeina ids i le laulau table ma i laulau uma e faasino i ai.
$ 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 - ENDSu'e fa'alagolago mo se laulau fa'apitoa
Galuega e su'e ai fa'alagolago i se laulau fa'apitoa table. Fa'ailoga mana'omia - config_path и table.
A maeʻa le faʻalauiloaina, o le a faʻaalia se lomifefiloi i luga o le lau, lea:
in_refs- o se lomifefiloi o laulau e faʻasino i se tasi, lea o le ki o le igoa o le laulau, o le tau o se lisi o mea mai fafo (pk_main- ki autu i le laulau autu,pk_ref- ki autu i le laulau fa'asinomaga,fk_ref- le igoa o le koluma o le ki mai fafo i le laulau puna);out_refs— o se lomifefiloi o laulau e faasino i ai.
$ 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')]}}Su'eina o fa'asinomaga i manoa fa'atasi ma le Ki Primary fa'apitoa
Galuega e su'e ai laina i isi laulau e fa'asino ile laina e ala ile Foreign Key ids laulau table. Fa'ailoga mana'omia - config_path, table и ids.
A maeʻa le faʻalauiloaina, o le a faʻaalia se lolomifefiloi ma le fausaga o loʻo i lalo i luga o le lau:
{
pk_id_1: {
reffering_table_name_1: {
foreign_key_1: [
{row_pk_1: value, row_pk_2: value},
...
],
...
},
...
},
pk_id_2: {...},
...
}Fa'ata'ita'iga vala'au:
$ 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'}]}}}Fa'aoga ile code
I le faaopoopo atu i le taʻavale i totonu o le faʻamafanafanaga, e mafai ona faʻaogaina le faletusi i le Python code. O faʻataʻitaʻiga o telefoni i le iPython interactive environment o loʻo faʻaalia i lalo.
Fa'amaumauga o se laulau
>>> 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 - ENDSu'e fa'alagolago mo se laulau fa'apitoa
>>> 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')]}}Su'eina o fa'asinomaga i manoa fa'atasi ma le Ki Primary fa'apitoa
>>> 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'}]}}}O lo'o maua le code source library ile i lalo ole laisene MIT, fa'apea fo'i ile fale teu oloa .
O le a ou fiafia e fai faʻamatalaga, tautinoga ma fautuaga.
O le a ou taumafai e tali fesili i le mea sili ou te mafaia iinei ma totonu o le fale teu oloa.
puna: www.habr.com