Pulogalamu ya ProHoster > Blog > Ulamuliro > PgGraph ndi chida chothandizira kusunga ndi kupeza zodalira patebulo mu PostgreSQL

PgGraph ndi chida chothandizira kusunga ndi kupeza zodalira patebulo mu PostgreSQL

PgGraph ndi chida chothandizira kusunga ndi kupeza zodalira patebulo mu PostgreSQL
Lero ndikufuna kuwonetsa owerenga a Habr ndi chida cholembedwa mu Python chogwira ntchito ndi kudalira patebulo mu PostgreSQL DBMS.

API ya utility ndiyosavuta ndipo ili ndi njira zitatu:

  • archive_table - kusungitsa zakale / kuchotsa mizere yokhala ndi Makiyi Oyambira
  • get_table_references - Sakani zodalira patebulo (ziwonetsa matebulo omwe atchulidwa ndi omwe akufotokozedwawo)
  • get_rows_references - Sakani mizere m'matebulo ena omwe amatchula mizere yomwe mukufuna

prehistory

Dzina langa ndine Oleg Borzov, ndine wopanga mu gulu la CRM la oyang'anira obwereketsa nyumba ku Domklik.

Nawonsonkhokwe yayikulu yamakina athu a CRM ndi amodzi mwamagawo akulu kwambiri pakampani. Ilinso imodzi mwa akale kwambiri: idawonekera pakukhazikitsidwa komwe kwa polojekitiyi, mitengo ikakhala yayikulu, Domklik inali yoyambira, ndipo m'malo mwa microservice pamawonekedwe apamwamba a Python asynchronous panali monolith yayikulu mu PHP.

Kusintha kuchokera ku PHP kupita ku Python kunali kotalika kwambiri ndipo kunkafunika kuthandizira panthawi imodzi ya machitidwe onse awiri, zomwe zinakhudza mapangidwe a database.

Zotsatira zake, tili ndi nkhokwe yokhala ndi matebulo olumikizana kwambiri komanso akuluakulu okhala ndi mindandanda yamitundu yosiyanasiyana yamafunso. Zonsezi zimakhudza kwambiri magwiridwe antchito a database: chifukwa cha matebulo akulu ndi maubwenzi ambiri pakati pawo, zovuta zamafunso zikuchulukirachulukira, zomwe ndizofunikira kwambiri pamatebulo odzaza kwambiri.

Kuti tichepetse katundu pankhokwe, tidaganiza zolemba zolemba zomwe zingasamutsire zolemba zakale kuchokera pamatebulo owoneka bwino kwambiri komanso opakidwa kupita ku zosungidwa (mwachitsanzo, kuchokera task Π² task_archive).

Ntchitoyi ndi yovuta chifukwa cha kuchuluka kwa maubwenzi pakati pa matebulo: ingosuntha mizere kuchokera task Π² task_archive sizokwanira, izi zisanachitike muyenera kuchita zomwezo mobwerezabwereza ndi mafotokozedwe onsewo task matebulo.

Ndikuwonetsa ndi chitsanzo Demo database kuchokera patsamba la postgrespro.ru:

PgGraph ndi chida chothandizira kusunga ndi kupeza zodalira patebulo mu PostgreSQL
Tinene kuti tikufunika kuchotsa zolemba patebulo Flights. Postgres sangatilole kuti tichite izi motere: choyamba tiyenera kuchotsa zolembedwa pamatebulo onse ofotokozera, ndi zina zambiri mpaka pamagome omwe sanatchulidwe ndi aliyense.

Mu chitsanzo chathu pa Flights amatanthauza Ticket_flights, ndi pa iye - Boarding_passes.

Chifukwa chake, muyenera kuyichotsa motere:

  1. Timapeza makiyi oyambira (PK) mizere mkati Ticket_flights, zomwe zimatanthawuza mizere yoti ichotsedwemo Flights.
  2. Timapeza mizere ya PK Boarding_passes, zomwe zikutanthauza Ticket_flights.
  3. Timachotsa mizere ndi PK kuchokera pagawo 2 patebulo Boarding_passes.
  4. Chotsani mizere ndi PK kuchokera pagawo 1 mkati Ticket_flights.
  5. Kuchotsa mizere ku Flights.

Chotsatira chake chinali chida chotchedwa PgGraph, chomwe tinaganiza zopanga gwero lotseguka.

Momwe mungagwiritsire ntchito

Njirayi imathandizira njira ziwiri zogwiritsira ntchito:

  • Imbani kuchokera pamzere wolamula (pggraph …).
  • Kugwiritsa ntchito Python code (class PgGraphApi).

Kuyika ndi kukonza

Choyamba muyenera kukhazikitsa zofunikira kuchokera ku Pypi repository:

pip3 install pggraph

Kenako pangani fayilo ya config.ini pamakina akomweko ndi kasinthidwe ka database ndi zolemba zakale:

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

Thamangani ku console

magawo

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

Zotsutsana:

  • action - zofunika kuchita: archive_table, get_table_references kapena get_rows_references.

Zotsutsana zotchulidwa:

  • --config_path - njira yopita ku fayilo ya config;
  • --table - tebulo limene muyenera kuchitapo kanthu;
  • --ids - mndandanda wa id wolekanitsidwa ndi koma, mwachitsanzo, 1,2,3 (ngati mukufuna chizindikiro);
  • --log_path - njira yopita ku foda ya zipika (zosankha, mwachisawawa - chikwatu chakunyumba);
  • --log_level - mulingo wodula mitengo (chosankha, chosasinthika ndi INFO).

Zitsanzo za malamulo

Kusunga tebulo

Ntchito yayikulu yogwiritsira ntchito ndikusunga deta, i.e. kusamutsa mizere kuchokera patebulo lalikulu kupita patebulo losungidwa (mwachitsanzo, kuchokera patebulo mabuku Π² mabuku_archive).

Kuchotsa popanda kusungidwa kumathandizidwanso: chifukwa cha izi muyenera kukhazikitsa chizindikiro mu config.ini to_archive = zabodza).

Zofunikira magawo - config_path, tebulo ndi ma ID.

Pambuyo poyambitsa, zolemba zidzachotsedwa mobwerezabwereza ids pagome table ndi m'magome onse amene amatchula izo.

$ 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

Pezani zodalira pa tebulo lotchulidwa

Ntchito kuti mupeze zodalira pa tebulo lodziwika table. Zofunikira magawo - config_path ΠΈ table.

Pambuyo poyambitsa, dikishonale idzawonetsedwa pazenera, pomwe:

  • in_refs - dikishonale ya matebulo ofotokoza zomwe zaperekedwa, pomwe fungulo ndi dzina la tebulo, mtengo wake ndi mndandanda wazinthu Zakunja Zakunja (pk_main - kiyi yoyamba mu tebulo lalikulu, pk_ref - fungulo loyambirira muzofotokozera, fk_ref - dzina la gawo lomwe ndi kiyi yakunja ku tebulo loyambira);
  • out_refs β€” dikishonale ya matebulo ameneyu akutanthauza.

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

Kupeza zolozera ku zingwe zokhala ndi Kiyi Yoyambira

Ntchito yofufuza mizere m'matebulo ena omwe amatchula mizere kudzera pa Key Key ids matebulo table. Zofunikira magawo - config_path, table ΠΈ ids.

Mukakhazikitsa, dikishonale yokhala ndi mawonekedwe otsatirawa idzawonetsedwa pazenera:

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

Kuitana kwachitsanzo:

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

Kugwiritsa ntchito code

Kuphatikiza pakuyiyendetsa mu kontrakitala, laibulale imatha kugwiritsidwa ntchito mu code ya Python. Zitsanzo zamayimbidwe mu malo ochezera a iPython akuwonetsedwa pansipa.

Kusunga tebulo

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

Pezani zodalira pa tebulo lotchulidwa

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

Kupeza zolozera ku zingwe zokhala ndi Kiyi Yoyambira

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

Laibulale source code ikupezeka pa GitHub pansi pa layisensi ya MIT, komanso m'malo osungira PyPI.

Ndidzasangalala ndi ndemanga, zochita ndi malingaliro.

Ndiyesetsa kuyankha mafunso momwe ndingathere pano komanso posungira.

Source: www.habr.com

Kuwonjezera ndemanga