áá®áá±á· áá»áœááºáá±á¬áº PostgreSQL DBMS ááŸá¬ table dependencies áá²á· á¡áá¯ááºáá¯ááºááá¯á·á¡ááœáẠPython ááŸá¬áá±ážáá¬ážáá²á· utility áá
áºáá¯áá²á· Habr á
á¬áááºáá°ááœá±ááᯠáááºááŒáá»ááºáá«áááºá
utility á API ááẠááá¯ážááŸááºážááŒá®áž áááºážáááºážáá¯á¶ážáá¯áá«áááºáááº-
- archive_table - áááºááŸááºáá¬ážáá±á¬ Primary Keys áá»á¬ážááŒáá·áº áááºáá«ááá²áá² ááááºážáááºážááŒááºáž/áá»ááºááŒááºáž á¡áááºážáá»á¬áž
- get_table_ááá¯ážáá¬ážáá»ááºáá»á¬áž â ááá¬ážáá áºáá¯á¡ááœáẠááŸá®ááá¯ááŸá¯ááá¯ááŸá¬ááœá±áá« (áááºááŸááºáá¬ážááá·áºáá áºáá¯ááŸááá¯ážáá¬ážáá±á¬ááá¬ážáá»á¬ážááŸáá·áº áááºážááá¯ááá¯ážáá¬ážáá±á¬áá°áá»á¬ážááá¯ááŒááá«áááº)
- get_rows_ááá¯ážáá¬ážáá»ááºáá»á¬áž - á¡ááá¯ááŸááá±á¬ááá¬ážááŸá áááºááŸááºáá¬ážáá±á¬á¡áááºážáá»á¬ážááᯠáááºááœáŸááºážáá±á¬ á¡ááŒá¬ážááá¬ážáá»á¬ážááœáẠá¡áááºážáá»á¬ážááᯠááŸá¬ááœá±áá«á
á á±á¬ááá¯áá»ážáá¬á
áá»áœááºá¯ááºáá¡áááºááŸá¬ Oleg Borzov ááŒá áºáá«áááºá áá»áœááºá¯ááºááẠDomklik ááŸá á¡áá±á«ááºáá¶áá»á±ážááœá±áááºáá±áá»á¬áá»á¬ážá¡ááœáẠCRM á¡ááœá²á·ááœáẠdeveloper áá áºáŠážááŒá áºáááºá
áá»áœááºá¯ááºááá¯á·á CRM á áá áºá áááºááá±áá¬áá±á·á áºááẠáá¯áá¹ááá®á¡ááœááºážááŸá áá¯áááºááá¬áá¡ááœáẠá¡ááŒá®ážáá¯á¶ážáá áºáá¯ááŒá áºáááºá áááºážááẠááŸá±ážá¡áá»áá¯á¶ážáá»á¬ážáá²á០áá áºáá¯áááºážááŒá áºáááº- áá áºáááºááŒá®ážáá»á¬áž ááŒá®ážáá¬áá±á¬á¡áá«á Domklik ááẠááá±á¬áá»ááºá áááºáá»áááºááœáẠáá±á«áºáá¬áá²á·ááŒá®áž áá±ááºáááºáá±á¬ Python asynchronous framework ááœáẠmicroservice á¡á á¬áž PHP ááœáẠááŒá®ážáá¬ážáá±á¬ monolith áá áºáá¯ááŸááááºá
PHP á០Python ááá¯á· áá°ážááŒá±á¬ááºážááŸá¯ááẠá¡ááœááºááŸááºáá»á¬ážááŒá®áž áá±áá¬áá±á·á áºá áá®ááá¯ááºážááᯠááááá¯ááºá á±ááá·áº á áá áºááŸá áºáá¯áá¯á¶ážá áá áºááŒáá¯ááºááẠáá¶á·ááá¯ážááŸá¯ ááá¯á¡ááºáá«áááºá
ááááºá¡áá±ááŒáá·áºá áá»áœááºá¯ááºááá¯á·ááœáẠá¡áá»áá¯ážáá»áá¯ážáá±á¬ queries á¡áá»áá¯ážá¡á á¬ážáá»á¬ážá¡ááœáẠá¡ááœáŸááºážááááºážáá»á¬ážá áœá¬áá«áá±á¬ ááŒá®ážáá¬ážáá±á¬áá»áááºáááºááŸá¯ááŸáá·áº ááŒá®ážáá¬ážáá±á¬ááá¬ážáá»á¬ážáá«ááŸááá±á¬ áá±áá¬áá±á·á áºáá áºáá¯ááŸááááºá á€á¡áá¬á¡á¬ážáá¯á¶ážááẠáá±áá¬áá±á·á áºáá áœááºážáá±á¬ááºáááºááᯠááá¯ážááá¯ážááœá¬ážááœá¬ážááááá¯ááºá á±áááº- ááŒá®ážáá¬ážáá±á¬ááá¬ážáá»á¬ážááŸáá·áº áááºážááá¯á·ááŒá¬ážááŸá áááºáá¶áá±ážáá»á¬ážá áœá¬ááŒá±á¬áá·áºá áá±ážááŒááºážááŸá¯áááŸá¯ááºááœá±ážááŸá¯ááẠá¡áááºáááŒááºááá¯ážáá¬áá±ááŒá®ážá áááºáá«á¡áá»á¬ážáá¯á¶ážáááºáá¬ážáá±á¬ááá¬ážáá»á¬ážá¡ááœáẠá¡áá°ážá¡áá±ážááŒá®ážáá«áááºá
áá±áá¬áá±á·á
áºáá±á«áºááŸá áááºáá¯ááºáááºááá¯ážááᯠáá»áŸá±á¬á·áá»áááºá áá»áœááºá¯ááºááá¯á·ááẠá¡áá±á¬ááºááá¯á¶ážááŸáá·áº áááºáá±á¬ááºáá¬ážáá±á¬ ááá¬ážáá»á¬ážá០ááŸááºáááºážáá±á¬ááºážáá»á¬ážááᯠááááºážáááºážáá¬ážáá±á¬ ááŸááºáááºážáá»á¬ážáᶠááœáŸá²ááŒá±á¬ááºážáá±ážááá·áº script áá
áºáá¯ááᯠáá±ážááẠáá¯á¶ážááŒááºáá²á·ááẠ(á¥ááá¬á task
в task_archive
).
á€á¡áá¯ááºááẠááá¬ážáá»á¬ážááŒá¬ážááŸá áááºáá¶áá±ážá¡áá»á¬ážá¡ááŒá¬ážááŒá±á¬áá·áº ááŸá¯ááºááœá±ážáááº- ááá¯ážááá¯ážáááºážáá»á¬ážá០ááœáŸá±á·áá«á task
в task_archive
áááºááœáŸááºážááá¯ážáá¬ážáá°á¡á¬ážáá¯á¶ážáá²á· áááºáá«áááºáá« áááºáá«ááá²áá² áá¯ááºáááŸá¬á ááá¯á¶áá±á¬ááºáá«áá°ážá task
á
á¬ážááœá²áá»á¬ážá
á¥ááá¬áá
áºáá¯áá²á· ááá¯ááºááŒáááºá
ááá¬ážáá
áºáá¯á០ááŸááºáááºážáá»á¬ážááᯠáá»ááºááẠááá¯á¡ááºáááºááá¯áá«á
áá¯á· Flights
. Postgres ááẠá€áá²á·ááá¯á·áá¯ááºáá±á¬ááºááẠáá»áœááºá¯ááºááá¯á·á¡á¬áž ááœáá·áºáááŒá¯áá«- áá»áœááºá¯ááºááá¯á·ááẠááááŠážá
áœá¬ á¡ááá¯ážá¡áá¬áž ááá¬ážáá»á¬ážá¡á¬ážáá¯á¶ážá០ááŸááºáááºážáá»á¬ážááᯠáá»ááºááẠááá¯á¡ááºááŒá®áž áááºáá°á·ááá¯áá»áŸ áááºááœáŸááºážááŒááºážáááŸááá±á¬ ááá¬ážáá»á¬ážá¡áá áááºáá«ááá²áá² ááŒááºáááºážááẠááá¯á¡ááºáá«áááºá
áá»áœááºá¯ááºááá¯á·áá¥ááá¬ááœáẠFlights
áááºááœáŸááºážááẠTicket_flights
ááŸáá·áº áá°áá¡áá±á«áº - Boarding_passes
.
ááá¯á·ááŒá±á¬áá·áº á€á¡á á®á¡á á¥áºá¡ááá¯ááºáž áááºážááᯠáá»ááºááẠááá¯á¡ááºáááº-
- áá»áœááºá¯ááºááá¯á·ááẠá¡áááºážáá»á¬ážá á¡ááááá±á¬á·áá»á¬áž (PK) áááºááá¯ážáá»á¬ážááᯠáááŸááá«áááºá
Ticket_flights
áá»ááºáá áºáááá·áº á¡áááºážáá»á¬ážááᯠáááºááœáŸááºážáááºáFlights
. - PK áááºážáá»á¬áž áááŸááá«áááºá
Boarding_passes
áááºááœáŸááºážáááºáTicket_flights
. - áá»áœááºá¯ááºááá¯á·ááẠááá¬ážááŸá á¡ááá·áº 2 á០PK ááŒáá·áº á¡áááºážáá»á¬ážááᯠáá»ááºáá«áááºá
Boarding_passes
. - á¡ááá·áº 1 in á០PK ááŒáá·áº ááá¯ááºážáá»á¬ážááᯠáá»ááºáá«á
Ticket_flights
. - ááá¯ááºážáá»á¬ážááá¯áááºááŸá¬ážááŒááºážá
Flights
.
ááááºááŸá¬ PgGraph áá¯áá±á«áºáá±á¬ á¡áá¯á¶ážáááºááŸá¯áá áºáá¯ááŒá áºááŒá®ážá áá»áœááºá¯ááºááá¯á·ááẠopen source ááŒá¯áá¯ááºááẠáá¯á¶ážááŒááºáá²á·áááºá
áááºááá¯áá¯á¶ážá áœá²ááá¯á·
utility ááẠá¡áá¯á¶ážááŒá¯ááŸá¯áá¯á¶á á¶ááŸá áºáá»áá¯ážááᯠáá¶á·ááá¯ážáá±ážáááº-
- á¡áááá·áºáá±ážá
á¬ááŒá±á¬ááºážááŸáá±á«áºááá¯ááŸá¯ (
pggraph âŠ
). - Python áá¯ááºááœáẠá¡áá¯á¶ážááŒá¯ááŸá¯ (á¡áááºážá¡á
á¬áž
PgGraphApi
).
áááºáááºááŒááºážááŸáá·áºááœá²á·á ááºážááŸá¯
ááááŠážá áœá¬ áááºááẠPypi repository á០utility ááᯠinstall áá¯ááºáááºááá¯á¡ááºáááº-
pip3 install pggraph
ááá¯á·áá±á¬áẠáá±áá¬áá±á·á áºáááœá²á·á ááºážáá¯á¶ááŸáá·áº áá±á¬áºááœááºážáááºáá¬ážáá±á¬ script ááᯠá ááºááœááºážá ááºááœáẠconfig.ini ááá¯ááºáá áºáá¯áááºáá®ážáá«-
[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'
ááœááºááá¯ážááºá០ááŒá±ážáá«á
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)
á¡áá±á¡áá¬ážááá¯ááºáᬠááŒááºážáá¯á¶áá»ááºáá»á¬áž-
action
- ááá¯á¡ááºáá±á¬áá¯ááºáá±á¬ááºáá»ááºarchive_table
,get_table_references
ááá¯á·ááá¯ááºget_rows_references
.
á¡áááºáá±ážáá¬ážáá±á¬ á¡ááŒá±á¬ááºážááŒáá»ááºáá»á¬áž-
--config_path
- config ááá¯ááºááá¯á·áááºážááŒá±á¬ááºáž;--table
- áááºáá¯ááºáá±á¬ááºááẠááá¯á¡ááºááá·áº ááá¬ážáá áºáá¯á--ids
â á¥ááᬠáá±á¬áºáá¬áá»á¬ážááŒáá·áº ááŒá¬ážáá¬ážáá±á¬ id á á¬áááºážá1,2,3
(optional parameter);--log_path
â ááŸááºáááºážáá»á¬ážá¡ááœáẠááá¯ááºááœá²ááá¯á· áááºážááŒá±á¬ááºáž (áá»ááºááŸááºáá¬ážááá¯ááºáá±á¬ ááá·áºáááºáá»ááºáá»á¬ážá áá¯á¶ááŸááºá¡á¬ážááŒáá·áº â home folder);--log_level
â ááŸááºáááºážá¡ááá·áº (áá»ááºááŸááºáá¬ážááá¯ááºáá±á¬ ááá·áºáááºáá±á¬ááºá áá°áááºážááŸá¬ INFO)á
Command á¥ááá¬áá»á¬áž
ááá¬ážáá áºáᯠááááºážáááºážááŒááºážá
utility áá¡ááááá¯ááºáá±á¬ááºáá»ááºááŸá¬ data archive, i.e. áááºáááá¬ážá០áá±á¬áºááœááºážááá¬ážááá¯á· á¡áááºážáá»á¬ážááᯠááœáŸá²ááŒá±á¬ááºážááŒááºáž (á¥ááá¬á ááá¬ážá០á á¬á¡á¯ááºáá»á¬áž в books_archive).
ááááºážáááºážááŒááºážáááŒá¯áá² áá»ááºááŒááºážááá¯áááºáž áá¶á·ááá¯ážáá±ážáááº- áááºážá¡ááœáẠáááºááẠconfig.ini ááœáẠááá·áºáááºáááºááŸááºáá»ááºááᯠáááºááŸááºááẠááá¯á¡ááºáááºá to_archive = ááŸá¬ážáááºá).
ááá¯á¡ááºáá±á¬áá±á¬ááºáá»á¬áž - config_pathá ááá¬ážááŸáá·áº ids.
á
áááºááŒá®ážáá±á¬ááºá ááŸááºáááºážáá»á¬ážááᯠáááºáá«ááá²áá² áá»ááºááá¯ááºáá«áááºá ids
á
á¬ážááœá²áá±á«áºááŸá¬ááááºá table
áááºážááá¯áááºááœáŸááºážáá±á¬ááá¬ážáá»á¬ážá¡á¬ážáá¯á¶ážááœááºá
$ 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
áááºááŸááºáá¬ážáá±á¬ááá¬ážá¡ááœáẠááŸá®ááá¯ááŸá¯ááá¯ááŸá¬áá«á
áááºááŸááºáá¬ážáá±á¬ááá¬ážáááŸá®ááá¯ááŸá¯ááá¯ááŸá¬ááœá±ááẠFunction table
. ááá¯á¡ááºáá±á¬áá±á¬ááºáá»á¬áž - config_path
О table
.
á áááºááŒá®ážáá±á¬ááºá áááºáá¬ážááŒááºáá±á«áºááœáẠá¡áááá¬ááºáá áºáᯠááŒááá«áááºá
in_refs
â áá±ážáá¬ážáá±á¬áá áºáá¯ááᯠááá¯ážáá¬ážáá±á¬ ááá¬ážáá¡áááá¬ááºáá áºáá¯á áá±á¬á·ááẠááá¬ážáá¡áááºááŒá áºááŒá®ážá áááºááá¯ážááẠááá¯ááºáá¶ááŒá¬ážáá®ážá¡áá¬ááá¹áá¯áá»á¬ážá á¬áááºáž (pk_main
- main table ááœáẠprimary key ápk_ref
- ááá¯ážáá¬ážááŒááºážááá¬ážááŸá á¡ááááá±á¬á·áfk_ref
â á¡áááºážá¡ááŒá áºááá¬ážá¡ááœáẠááá¯ááºáá¶ááŒá¬ážáá±á¬á·ááŒá áºááá·áº áá±á¬áºáá¶á¡áááºáout_refs
â á€á á¬á¡á¯ááºááᯠáááºááœáŸááºážáá±á¬ ááá¬ážáá»á¬áž á¡áááá¬ááºá
$ 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')]}}
áááºááŸááºáá¬ážáá±á¬ Primary Key ááŒáá·áº á á¬ááŒá±á¬ááºážáá»á¬ážááᯠááá¯ážáá¬ážááŸá¬ááœá±ááŒááºážá
Foreign Key ááŸáááá·áºá¡áááºážáá»á¬ážááá¯áááºááœáŸááºážáá±á¬á¡ááŒá¬ážááá¬ážáá»á¬ážááŸáá¡áááºážáá»á¬ážááá¯ááŸá¬ááœá±ááẠFunction ids
ááá¬áž table
. ááá¯á¡ááºáá±á¬áá±á¬ááºáá»á¬áž - config_path
, table
О ids
.
á áááºááŒá®ážáá±á¬ááºá á¡á±á¬ááºáá«ááœá²á·á ááºážáá¯á¶áá«ááŸááá±á¬ á¡áááá¬ááºáá áºáá¯ááᯠá ááááºáá±á«áºááœáẠááŒááá«áááº-
{
pk_id_1: {
reffering_table_name_1: {
foreign_key_1: [
{row_pk_1: value, row_pk_2: value},
...
],
...
},
...
},
pk_id_2: {...},
...
}
ááá°áá¬áá±á«áºááá¯ááŸá¯-
$ 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'}]}}}
áá¯ááºá¡áá¯á¶ážááŒá¯ááŸá¯
áááºážááᯠconsole ááœááºáááºáááºááŒááºážá¡ááŒááºá á á¬ááŒáá·áºááá¯ááºááᯠPython áá¯ááºááŒáá·áºá¡áá¯á¶ážááŒá¯ááá¯ááºáá«áááºá iPython á¡ááŒááºá¡ááŸááºá¡áá»áá¯ážáááºáá±á¬ááºáá±á¬áááºáááºážáá»ááºááŸááá±á«áºááá¯ááŸá¯áá»á¬ážáá¥ááá¬áá»á¬ážááá¯á¡á±á¬ááºááœááºááŒááá¬ážáááºá
ááá¬ážáá áºáᯠááááºážáááºážááŒááºážá
>>> 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
áááºááŸááºáá¬ážáá±á¬ááá¬ážá¡ááœáẠááŸá®ááá¯ááŸá¯ááá¯ááŸá¬áá«á
>>> 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')]}}
áááºááŸááºáá¬ážáá±á¬ Primary Key ááŒáá·áº á á¬ááŒá±á¬ááºážáá»á¬ážááᯠááá¯ážáá¬ážááŸá¬ááœá±ááŒááºážá
>>> 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'}]}}}
á
á¬ááŒáá·áºááá¯áẠá¡áááºážá¡ááŒá
áºáá¯ááºááᯠááœáẠáááŸáááá¯ááºáá«áááºá
ááŸááºáá»ááºáá»á¬ážá áááááŒá¯ááŸá¯áá»á¬ážááŸáá·áº á¡ááŒá¶ááŒá¯áá»ááºáá»á¬ážááᯠáááºážááŒá±á¬ááºáááºážáᬠááŒá áºá á±áá«áááºá
á€áá±áá¬ááŸáá·áº ááá¯ááŸá±á¬ááºáááºážááœáẠáá»áœááºá¯ááºá áááºááá¯ááºááá»áŸ á¡áá±á¬ááºážáá¯á¶ážáá±ážááœááºážáá»á¬ážááᯠááŒá±ááẠááŒáá¯ážá
á¬ážáá«áááºá
source: www.habr.com