Šiandien noriu Habr skaitytojams pristatyti Python parašytą įrankį, skirtą darbui su lentelių priklausomybėmis PostgreSQL DBVS.
Priemonės API yra paprasta ir susideda iš trijų būdų:
- archyvas_lentelė - rekursyvus eilučių su nurodytais pirminiais raktais archyvavimas / trynimas
- get_table_references — ieškoti lentelės priklausomybių (bus rodomos nurodytos lentelės ir jos nurodančios lentelės)
- gauti_eilučių_nuorodas - ieškoti eilučių kitose lentelėse, kurios nurodo nurodytas norimos lentelės eilutes
priešistorė
Mano vardas Olegas Borzovas, esu Domkliko būsto paskolų vadybininkų CRM komandos kūrėjas.
Pagrindinė mūsų CRM sistemos duomenų bazė yra viena didžiausių pagal apimtis įmonėje. Jis taip pat yra vienas iš seniausių: jis pasirodė pačioje projekto pradžioje, kai medžiai buvo dideli, Domklik buvo startuolis, o vietoj mikropaslaugos madingoje Python asinchroninėje sistemoje buvo didžiulis PHP monolitas.
Perėjimas nuo PHP prie Python buvo labai ilgas ir reikėjo tuo pačiu metu palaikyti abi sistemas, o tai turėjo įtakos duomenų bazės dizainui.
Dėl to turime duomenų bazę su daugybe labai sujungtų ir didžiulių lentelių su daugybe indeksų, skirtų įvairių tipų užklausoms. Visa tai neigiamai veikia duomenų bazės našumą: dėl didelių lentelių ir daugybės ryšių tarp jų užklausų sudėtingumas nuolat didėja, o tai ypač svarbu labiausiai apkrautoms lentelėms.
Norėdami sumažinti duomenų bazės apkrovą, nusprendėme parašyti scenarijų, kuris perkeltų senus įrašus iš talpiausių ir įkeltų lentelių į archyvuotas (pvz. task
в task_archive
).
Šią užduotį apsunkina daugybė ryšių tarp lentelių: tiesiog perkelkite eilutes iš task
в task_archive
neužtenka, prieš tai tą patį reikia padaryti rekursyviai su visomis nuorodomis task
lenteles.
Parodysiu pavyzdžiu
Tarkime, kad turime ištrinti įrašus iš lentelės Flights
. „Postgres“ neleis mums to daryti tiesiog taip: pirmiausia turime ištrinti įrašus iš visų nuorodų lentelių ir taip toliau rekursyviai iki lentelių, kurių niekas nenurodo.
Mūsų pavyzdyje adresu Flights
nurodo Ticket_flights
, ir ant jos - Boarding_passes
.
Todėl jį reikia ištrinti tokia tvarka:
- Gauname eilučių pirminių raktų (PK) reikšmes
Ticket_flights
, kurie nurodo eilutes, kurias reikia ištrintiFlights
. - Gauname PK eilutes
Boarding_passes
, kurie nurodoTicket_flights
. - Ištriname eilutes pagal PK iš 2 lentelės veiksmo
Boarding_passes
. - Ištrinkite eilutes pagal PK nuo 1 veiksmo
Ticket_flights
. - Pašalinamos linijos iš
Flights
.
Rezultatas buvo programa, vadinama PgGraph, kurią nusprendėme padaryti atviro kodo.
Kaip naudotis
Priemonė palaiko du naudojimo būdus:
- Skambinkite iš komandinės eilutės (
pggraph …
). - Naudojimas Python kode (klasė
PgGraphApi
).
Diegimas ir konfigūravimas
Pirmiausia turite įdiegti įrankį iš Pypi saugyklos:
pip3 install pggraph
Tada vietiniame kompiuteryje sukurkite failą config.ini su duomenų bazės konfigūracija ir archyvavimo scenarijumi:
[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'
Paleisti iš konsolės
Parametrai
$ 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)
Poziciniai argumentai:
action
- būtini veiksmai:archive_table
,get_table_references
arbaget_rows_references
.
Vardiniai argumentai:
--config_path
- kelias į konfigūracijos failą;--table
— lentelė, su kuria reikia atlikti veiksmą;--ids
— ID sąrašas, atskirtas kableliais, pavyzdžiui,1,2,3
(neprivalomas parametras);--log_path
— kelias į žurnalų aplanką (pasirenkamas parametras, pagal numatytuosius nustatymus – namų aplankas);--log_level
— registravimo lygis (pasirenkamas parametras, numatytasis parametras yra INFO).
Komandų pavyzdžiai
Lentelės archyvavimas
Pagrindinė naudingumo funkcija yra duomenų archyvavimas, t.y. eilučių perkėlimas iš pagrindinės lentelės į archyvo lentelę (pavyzdžiui, iš lentelės knygos в knygų_archyvas).
Taip pat palaikomas ištrynimas be archyvavimo: tam reikia nustatyti parametrą config.ini į_archyvuoti = false).
Reikalingi parametrai - config_path, lentelė ir ID.
Po paleidimo įrašai bus rekursyviai ištrinti ids
į lentelę table
ir visose su juo susijusiose lentelėse.
$ 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
Raskite nurodytos lentelės priklausomybes
Funkcija rasti nurodytos lentelės priklausomybes table
. Reikalingi parametrai - config_path
и table
.
Po paleidimo ekrane bus rodomas žodynas, kuriame:
in_refs
— lentelių, nurodančių duotą, žodynas, kur raktas yra lentelės pavadinimas, reikšmė yra svetimo rakto objektų sąrašas (pk_main
- pagrindinis raktas pagrindinėje lentelėje,pk_ref
- pirminis raktas nuorodų lentelėje,fk_ref
— stulpelio, kuris yra šaltinio lentelės išorinis raktas, pavadinimas);out_refs
- lentelių žodynas, į kurį remiasi šis.
$ 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')]}}
Rasti nuorodas į eilutes su nurodytu pirminiu raktu
Funkcija ieškoti eilučių kitose lentelėse, kurios nurodo eilutes naudojant užsienio raktą ids
stalai table
. Reikalingi parametrai - config_path
, table
и ids
.
Po paleidimo ekrane bus rodomas tokios struktūros žodynas:
{
pk_id_1: {
reffering_table_name_1: {
foreign_key_1: [
{row_pk_1: value, row_pk_2: value},
...
],
...
},
...
},
pk_id_2: {...},
...
}
Skambučio pavyzdys:
$ 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'}]}}}
Naudojimas kode
Biblioteka gali būti naudojama ne tik konsolėje, bet ir Python kode. Toliau pateikiami skambučių interaktyvioje iPython aplinkoje pavyzdžiai.
Lentelės archyvavimas
>>> 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
Raskite nurodytos lentelės priklausomybes
>>> 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')]}}
Rasti nuorodas į eilutes su nurodytu pirminiu raktu
>>> 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'}]}}}
Bibliotekos šaltinio kodą rasite adresu
Maloniai lauksiu pastabų, įsipareigojimų ir pasiūlymų.
Čia ir saugykloje pasistengsiu atsakyti į klausimus pagal savo galimybes.
Šaltinis: www.habr.com