ProHoster > Blog > Rêveberî > PgGraph ji bo arşîvkirin û dîtina girêdanên tabloyê di PostgreSQL de amûrek e.

PgGraph ji bo arşîvkirin û dîtina girêdanên tabloyê di PostgreSQL de amûrek e.

PgGraph ji bo arşîvkirin û dîtina girêdanên tabloyê di PostgreSQL de amûrek e.
Îro ez dixwazim ji xwendevanên Habr re karûbarek ku bi Python hatî nivîsandin pêşkêşî bikim ji bo ku bi girêdanên tabloyê re di PostgreSQL DBMS de bixebitin.

API-ya karûbar hêsan e û ji sê rêbazan pêk tê:

  • archive_table - Arşîvkirin/jinavbirina rêzikên bi Bişkojkên Seretayî yên diyarkirî vegere
  • get_table_references - li peywendiyên tabloyekê bigerin (dê tabloyên ku ji hêla diyarkirî ve hatine referanskirin û yên ku jê re referans dikin nîşan bide)
  • get_rows_references - di tabloyên din de li rêzan bigerin ku di tabloya xwestinê de rêzên diyarkirî referans dikin

pêşdîrok

Navê min Oleg Borzov e, ez pêşdebirek di tîmê CRM de ji bo rêveberên deyndana mortgage li Domklik.

Databasa sereke ya pergala meya CRM di pargîdaniyê de di warê qebareyê de yek ji herî mezin e. Ew di heman demê de yek ji kevintirîn e: di destpêka projeyê de xuya bû, dema ku dar mezin bûn, Domklik destpêkek bû, û li şûna mîkroxizmetek li ser çarçoveyek asînkron a moda ya Python-ê di PHP-ê de monolîtek mezin hebû.

Veguheztina ji PHP-ê berbi Python-ê pir dirêj bû û piştgirîya hevdem a her du pergalan hewce dikir, ku bandor li sêwirana databasê kir.

Wekî encamek, me databasek bi hejmareke mezin ji tabloyên pir girêdayî û mezin bi komek indexên ji bo cûrbecûr pirsan heye. Hemî ev bandorek neyînî li performansa databasê dike: ji ber tabloyên mezin û komek têkiliyên di navbera wan de, tevliheviya pirsan her ku diçe zêde dibe, ku bi taybetî ji bo tabloyên herî barkirî krîtîk e.

Ji bo kêmkirina barkirina databasê, me biryar da ku em skrîptek binivîsin ku dê tomarên kevn ji tabloyên herî mezin û barkirî veguhezîne yên arşîvkirî (mînak, ji task в task_archive).

Ev peywir ji hêla jimareya mezin a têkiliyên di navbera tabloyan de tevlihev e: bi tenê rêzan ji wan bar bikin task в task_archive ne bes e, berî wê hewce ye ku hûn bi hemî wan referansê re heman vegerî bikin task tables.

Ez ê bi mînakekê nîşan bidim databasa demo ji malpera postgrespro.ru:

PgGraph ji bo arşîvkirin û dîtina girêdanên tabloyê di PostgreSQL de amûrek e.
Em bibêjin ku divê em tomaran ji tabloyek jêbirin Flights. Postgres ê destûrê nede me ku em wiya wusa bikin: pêşî hewce ye ku em tomarên ji hemî tabloyên referansê jêbirin, û bi vî rengî bi paşverû berbi tabloyên ku ji hêla kesî ve nayên referans kirin.

Di mînaka me de li Flights amaje dike Ticket_flights, û li ser wê - Boarding_passes.

Ji ber vê yekê, hûn hewce ne ku bi vê rêzê jêbirin:

  1. Em bişkojên bingehîn (PK) nirxên rêzikan digirin Ticket_flights, ku behsa rêzikên ku tê de werin jêbirin vedibêje Flights.
  2. Em rêzên PK digirin Boarding_passes, ku behsa wan dike Ticket_flights.
  3. Em rêzikên bi PK-ê ji gava 2-ê di tabloyê de jêbirin Boarding_passes.
  4. Xetên ji hêla PK ve ji gava 1-ê de jêbirin Ticket_flights.
  5. Rakirina xetên ji Flights.

Encam amûrek bi navê PgGraph bû, ku me biryar da ku em çavkaniya vekirî çêbikin.

Meriv çawa bikar tîne

Vebijêrk du awayên karanîna piştgirî dike:

  • Ji rêzika fermanê bang bikin (pggraph …).
  • Bikaranîna di koda Python de (pol PgGraphApi).

Sazkirin û veavakirina

Pêşî hûn hewce ne ku amûrê ji depoya Pypi saz bikin:

pip3 install pggraph

Dûv re bi veavakirina databasê û skrîpta arşîvkirinê pelek config.ini li ser makîneya herêmî biafirînin:

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

Run ji konsolê

Parametreyên

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

Argumanên pozîsyonê:

  • action - çalakiya pêwîst: archive_table, get_table_references an get_rows_references.

Argumanên binavkirî:

  • --config_path - riya pelê mîhengê;
  • --table - tabloyek ku hûn hewce ne ku çalakiyek pê re bikin;
  • --ids - navnîşa id-ê ku bi kommasê veqetandî ye, mînakî, 1,2,3 (parametreya vebijarkî);
  • --log_path - rêça peldanka ji bo têketin (parametreya vebijarkî, ji hêla xwerû - peldanka malê);
  • --log_level - asta têketinê (parametreya vebijarkî, xwerû INFO ye).

Nimûneyên fermanê

Arşîvkirina tabloyekê

Fonksiyona sereke ya karûbar arşîvkirina daneyan e, yanî. veguheztina rêzan ji tabloya sereke berbi tabloya arşîvê (mînak, ji tabloyê pirtûkên в books_archive).

Jêbirina bêyî arşîvkirinê jî tê piştgirî kirin: ji bo vê yekê hûn hewce ne ku pîvanê li config.ini saz bikin to_archive = derewîn).

Parametreyên pêwîst - config_path, tablo û nasname.

Piştî destpêkirinê, tomar dê bi rengek vegerî werin jêbirin ids di sifrê de table û di hemû tabloyên ku behsa wê dikin.

$ 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

Ji bo tabloyek diyar girêdanan bibînin

Fonksiyon ji bo dîtina girêdanên tabloyek diyarkirî table. Parametreyên pêwîst - config_path и table.

Piştî destpêkirinê, ferhengek dê li ser ekranê were xuyang kirin, li wir:

  • in_refs - ferhengek tabloyan ku behsa yeka diyarkirî dike, ku kilît navê tabloyê ye, nirx navnîşek tiştên Klavyeya Biyanî ye (pk_main - mifteya bingehîn di tabloya sereke de, pk_ref - Mifteya bingehîn di tabloya referansê de, fk_ref - navê stûna ku mifteya biyanî ya tabloya çavkaniyê ye);
  • out_refs - ferhenga tabloyên ku ev yek jê re vedibêje.

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

Dîtina referansên rêzikên bi Mifteya Seretayî ya diyarkirî

Fonksiyona lêgerîna rêzan di tabloyên din de ku bi riya Key Biyanî ve rêzan vedibêje ids maseyên table. Parametreyên pêwîst - config_path, table и ids.

Piştî destpêkirinê, ferhengek bi avahiya jêrîn dê li ser ekranê were xuyang kirin:

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

Nimûne bang:

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

Bikaranîna di kodê de

Ji bilî xebitandina wê di konsolê de, pirtûkxane dikare di koda Python de were bikar anîn. Mînakên bangên di hawîrdora înteraktîf a iPython de li jêr têne xuyang kirin.

Arşîvkirina tabloyekê

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

Ji bo tabloyek diyar girêdanan bibînin

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

Dîtina referansên rêzikên bi Mifteya Seretayî ya diyarkirî

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

Koda çavkaniya pirtûkxaneyê li vir heye GitHub di bin lîsansa MIT de, û her weha di depoyê de PyPI.

Ez ê bi şîrove, soz û pêşniyaran kêfxweş bibim.

Ez ê hewl bidim ku li vir û di depoyê de bi qasî hêza xwe bersiva pirsan bidim.

Source: www.habr.com

Add a comment