Maanta waxaan rabaa inaan u soo bandhigo akhristayaasha Habr oo wata qalab ku qoran Python si ay ula shaqeeyaan ku tiirsanaanta miiska PostgreSQL DBMS.
API utility waa mid fudud wuxuuna ka kooban yahay saddex hab:
- miiska_ kaydka - kaydinta/tirtirka safafka leh furayaasha aasaasiga ah ee la cayimay
- hel_tixraacyada_miiska - raadi ku tiirsanaanta miis (waxay tusi doontaa jaantusyada laga tixraacay midka la cayimay iyo kuwa tixraacaya)
- hel_tixraacyo_safka - Ka raadi safafka miisaska kale ee tixraacaya safafka la cayimay ee shaxda la rabo
prehistory
Magacaygu waa Oleg Borzov, waxaan ahay horumariye ka tirsan kooxda CRM ee maamulayaasha amaahda amaahda guryaha ee Domklik.
Xogta ugu weyn ee nidaamkayaga CRM waa mid ka mid ah kuwa ugu weyn marka loo eego mugga shirkadda. Sidoo kale waa mid ka mid ah kuwa ugu da'da weyn: waxay ka soo muuqatay bilawga mashruuca, markii geeduhu weynaayeen, Domklik wuxuu ahaa bilawga, iyo halkii microservice ee qaabka Python asynchronous ee moodada ah waxaa jiray monolith weyn oo PHP ah.
Ka gudubka PHP una gudubtay Python aad buu u dheeraa wuxuuna u baahday taageero isku mar ah labada nidaam, taas oo saamaysay naqshadaynta xogta.
Natiijo ahaan, waxaan haysanaa xog ururin leh tiro badan oo aad isugu xiran oo miisas waaweyn leh oo leh farabadan tusiyaal noocyo kala duwan ah. Waxaas oo dhan waxay si xun u saameeyaan waxqabadka xogta xogta: sababtoo ah miisaska waaweyn iyo xidhidhka u dhexeeya iyaga, kakanaanta su'aalaha ayaa si joogto ah u kordhaya, taas oo si gaar ah muhiim ugu ah miisaska ugu badan ee la raro.
Si loo dhimo culayska ku jira kaydka xogta, waxaanu go'aansanay inaanu qorno qoraal ka soo wareejinaya diiwaanadii hore ee miisaska ugu mug iyo rarnaa una gudbiya kuwa kaydsan (tusaale, ka task
Π² task_archive
).
Hawshani waxay ku adag tahay tirada badan ee xidhiidhada u dhexeeya miisaska: si fudud uga guuri safafka task
Π² task_archive
kuma filna, intaa ka hor waxaad u baahan tahay inaad si la mid ah u sameyso si isdaba joog ah dhammaan tixraacyadaas task
miisaska.
Waxaan ku muujin doonaa tusaale
Aynu nidhaahno waxaan u baahanahay inaan ka tirtirno diiwaanada miiska Flights
. Postgres noo ogolaan mayso inaan sidan oo kale samayno: waxaan marka hore u baahanahay inaan tirtirno diiwaanada dhamaan miisaska tixraaca, iyo wixii la mid ah si isdaba joog ah ilaa miisaska aan cidna tixraacin.
Tusaalahayaga at Flights
waxaa loola jeedaa Ticket_flights
iyo iyada - Boarding_passes
.
Sidaa darteed, waxaad u baahan tahay inaad u tirtirto sidatan:
- Waxaan helnaa furayaasha aasaasiga ah (PK) qiimayaasha safafka
Ticket_flights
, kuwaas oo tixraacaya safafka lagu tirtirayoFlights
. - Waxaan helnaa safafka PK
Boarding_passes
, kuwaas oo tixraacayaTicket_flights
. - Waxaan ka tirtirnaa safafka PK tallaabada 2 ee shaxda
Boarding_passes
. - Ka tirtir khadadka PK ka tilaabada 1 in
Ticket_flights
. - Ka saarida khadadka
Flights
.
Natiijadu waxay ahayd utility loo yaqaan PgGraph, kaas oo aanu go'aansanay inaanu samayno ilo furan.
Sida loo isticmaalo
Utility wuxuu taageeraa laba nooc oo isticmaalka:
- Ka wac khadka taliska (
pggraph β¦
). - Isticmaalka koodka Python (class
PgGraphApi
).
Rakibaadda iyo qaabeynta
Marka hore waxaad u baahan tahay inaad ku rakibto utility-ga kaydka Pypi:
pip3 install pggraph
Ka dib ku samee faylka config.ini mashiinka deegaanka oo leh qaabeynta xogta iyo qoraalka kaydinta:
[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'
Ka orod konsole
xuduudaheedu
$ 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)
Doodaha mawqifka ah:
action
- ficil loo baahan yahay:archive_table
,get_table_references
amaget_rows_references
.
Doodaha la magacaabay:
--config_path
- jidka loo maro faylka config;--table
- miiska oo aad u baahan tahay inaad ku qabato ficil;--ids
- liiska id ay u kala qaybsan yihiin hakad, tusaale ahaan,1,2,3
(halbeegga ikhtiyaariga ah);--log_path
- Jidka loo maro galka diiwaanka (xaddiga ikhtiyaariga ah, sida caadiga ah - galka guriga);--log_level
- heerka goynta (halbeegga ikhtiyaariga ah, default waa INFO).
Tusaalooyinka amarka
Kaydinta miis
Shaqada ugu weyn ee utility waa kaydinta xogta, i.e. ka wareejinta safafka miiska weyn una wareejinaya miiska kaydka (tusaale, miiska buugaag Π² kayd_buugaagta).
Tirtir la'aanta kaydinta sidoo kale waa la taageeray: tan waxaad u baahan tahay inaad dejiso cabbirka config.ini to_archive = been).
Xuduudaha loo baahan yahay - config_path, miiska iyo ids.
Bilawga ka dib, diiwaanada si isdaba joog ah ayaa loo tirtiri doonaa ids
miiska table
iyo dhammaan jaantusyada tilmaamaya.
$ 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
Soo hel waxyaalaha ku tiirsanaanta miis cayiman
Shaqada lagu helo ku tiirsanaanta miis cayiman table
. Xuduudaha loo baahan yahay - config_path
ΠΈ table
.
Daah-furka ka dib, qaamuuska ayaa lagu soo bandhigi doonaa shaashadda, halkaasoo:
in_refs
- qaamuuska shaxanka tixraacaya mid la bixiyay, halkaasoo furuhu yahay magaca miiska, qiimuhu waa liiska walxaha furaha shisheeye (pk_main
- furaha aasaasiga ah ee miiska weyn,pk_ref
- furaha aasaasiga ah ee miiska tixraaca,fk_ref
- magaca tiirka kaas oo ah furaha shisheeye ee miiska isha;out_refs
- Qaamuuska miisaska middani waxa ay tilmaamaysaa.
$ 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')]}}
Helitaanka tixraacyada xargaha leh furaha aasaasiga ah ee la cayimay
Shaqada lagu raadiyo safafka shaxanka kale ee tixraacaya safafka loo maro Furaha Shisheeye ids
miisaska table
. Xuduudaha loo baahan yahay - config_path
, table
ΠΈ ids
.
Daah-furka ka dib, qaamuuska qaabkan soo socda ayaa lagu soo bandhigi doonaa shaashadda:
{
pk_id_1: {
reffering_table_name_1: {
foreign_key_1: [
{row_pk_1: value, row_pk_2: value},
...
],
...
},
...
},
pk_id_2: {...},
...
}
Tusaale ahaan wac:
$ 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'}]}}}
Isticmaalka koodka
Marka laga soo tago in lagu dhex wado console-ka, maktabadda waxaa loo isticmaali karaa koodka Python. Tusaalooyinka wicitaanada ee deegaanka isdhexgalka iPython ayaa lagu muujiyay hoos.
Kaydinta miis
>>> 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
Soo hel waxyaalaha ku tiirsanaanta miis cayiman
>>> 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')]}}
Helitaanka tixraacyada xargaha leh furaha aasaasiga ah ee la cayimay
>>> 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'}]}}}
Koodhka isha maktabadda ayaa laga heli karaa
Waan ku farxi doonaa faallooyinka, ballan-qaadyada iyo soo-jeedinta.
Waxaan isku dayi doonaa inaan ka jawaabo su'aalaha sida ugu fiican ee aan awoodo halkan iyo kaydka.
Source: www.habr.com