วันนี้ฉันต้องการนำเสนอโปรแกรมอ่าน Habr ด้วยยูทิลิตี้ที่เขียนด้วย Python สำหรับการทำงานกับการพึ่งพาตารางใน PostgreSQL DBMS
API ของยูทิลิตี้นั้นเรียบง่ายและประกอบด้วยสามวิธี:
- archive_table - การเก็บถาวร/การลบแถวแบบเรียกซ้ำด้วยคีย์หลักที่ระบุ
- get_table_references - ค้นหาการขึ้นต่อกันของตาราง (จะแสดงตารางที่อ้างอิงโดยตารางที่ระบุและตารางที่อ้างอิง)
- get_rows_references - ค้นหาแถวในตารางอื่นที่อ้างอิงแถวที่ระบุในตารางที่ต้องการ
ประวัติศาสตร์
ฉันชื่อ Oleg Borzov เป็นนักพัฒนาในทีม CRM สำหรับผู้จัดการฝ่ายสินเชื่อจำนองใน Domklik
ฐานข้อมูลหลักของระบบ CRM ของเราเป็นหนึ่งในฐานข้อมูลที่ใหญ่ที่สุดในแง่ของปริมาณในบริษัท นอกจากนี้ยังเป็นหนึ่งในโครงการที่เก่าแก่ที่สุด: ปรากฏในการเปิดตัวโครงการเมื่อต้นไม้มีขนาดใหญ่ Domklik เป็นสตาร์ทอัพและแทนที่จะเป็นไมโครเซอร์วิสบนเฟรมเวิร์กแบบอะซิงโครนัส Python ที่ทันสมัย กลับกลายเป็นเสาหินขนาดใหญ่ใน PHP
การเปลี่ยนจาก PHP ไปเป็น Python ใช้เวลานานมากและต้องการการสนับสนุนพร้อมกันของทั้งสองระบบ ซึ่งส่งผลต่อการออกแบบฐานข้อมูล
ด้วยเหตุนี้ เราจึงมีฐานข้อมูลที่มีตารางจำนวนมากที่เชื่อมต่อกันอย่างมากและมีดัชนีมากมายสำหรับการสืบค้นประเภทต่างๆ ทั้งหมดนี้ส่งผลเสียต่อประสิทธิภาพของฐานข้อมูล: เนื่องจากตารางขนาดใหญ่และความสัมพันธ์มากมายระหว่างตารางเหล่านั้น ความซับซ้อนของการสืบค้นจึงเพิ่มขึ้นอย่างต่อเนื่อง ซึ่งเป็นสิ่งสำคัญอย่างยิ่งสำหรับตารางที่มีการโหลดมากที่สุด
เพื่อลดภาระในฐานข้อมูลเราจึงตัดสินใจเขียนสคริปต์ที่จะถ่ายโอนบันทึกเก่าจากตารางที่มีจำนวนมากและโหลดมากที่สุดไปยังตารางที่เก็บถาวร (ตัวอย่างเช่นจาก task
в task_archive
).
งานนี้ซับซ้อนเนื่องจากความสัมพันธ์จำนวนมากระหว่างตาราง: เพียงแค่ย้ายแถวจาก task
в task_archive
ยังไม่เพียงพอ ก่อนหน้านั้นคุณต้องทำแบบเดิมซ้ำกับการอ้างอิงทั้งหมดเหล่านั้น task
ตาราง
ฉันจะสาธิตด้วยตัวอย่าง
สมมติว่าเราจำเป็นต้องลบระเบียนออกจากตาราง Flights
. Postgres จะไม่อนุญาตให้เราทำสิ่งนี้: ก่อนอื่นเราต้องลบบันทึกออกจากตารางอ้างอิงทั้งหมด และต่อไปเรื่อย ๆ จนถึงตารางที่ไม่มีใครอ้างอิง
ในตัวอย่างของเราที่ Flights
หมายถึง Ticket_flights
และกับเธอ - Boarding_passes
.
ดังนั้นคุณต้องลบมันตามลำดับนี้:
- เราได้รับค่าคีย์หลัก (PK) ของแถวต่างๆ
Ticket_flights
ซึ่งหมายถึงแถวที่จะลบFlights
. - เราได้แถว PK
Boarding_passes
ซึ่งอ้างถึงTicket_flights
. - เราลบแถวด้วย PK จากขั้นตอนที่ 2 ในตาราง
Boarding_passes
. - ลบบรรทัดด้วย PK จากขั้นตอนที่ 1 ใน
Ticket_flights
. - การลบบรรทัดออกจาก
Flights
.
ผลลัพธ์ที่ได้คือยูทิลิตี้ที่เรียกว่า PgGraph ซึ่งเราตัดสินใจสร้างโอเพ่นซอร์ส
วิธีใช้
ยูทิลิตี้นี้รองรับการใช้งานสองโหมด:
- โทรจากบรรทัดคำสั่ง (
pggraph …
). - การใช้งานในโค้ด Python (class
PgGraphApi
).
การติดตั้งและการกำหนดค่า
ก่อนอื่นคุณต้องติดตั้งยูทิลิตี้จากที่เก็บ Pypi:
pip3 install pggraph
จากนั้นสร้างไฟล์ 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'
เรียกใช้จากคอนโซล
พารามิเตอร์
$ 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
— เส้นทางไปยังไฟล์ปรับแต่ง--table
— ตารางที่คุณต้องดำเนินการ--ids
— รายการ ID คั่นด้วยเครื่องหมายจุลภาค เช่น1,2,3
(พารามิเตอร์ทางเลือก);--log_path
— เส้นทางไปยังโฟลเดอร์สำหรับบันทึก (พารามิเตอร์ทางเลือกตามค่าเริ่มต้น — โฟลเดอร์บ้าน)--log_level
— ระดับการบันทึก (พารามิเตอร์ทางเลือก ค่าเริ่มต้นคือ INFO)
ตัวอย่างคำสั่ง
การเก็บถาวรตาราง
หน้าที่หลักของยูทิลิตี้นี้คือการเก็บข้อมูลเช่น การโอนแถวจากตารางหลักไปยังตารางเก็บถาวร (เช่น จากตาราง หนังสือ в หนังสือ_เอกสารสำคัญ).
รองรับการลบโดยไม่เก็บถาวรด้วยเหตุนี้คุณต้องตั้งค่าพารามิเตอร์ใน config.ini to_archive = เท็จ).
พารามิเตอร์ที่จำเป็น - config_path ตาราง และรหัส.
หลังจากเปิดตัว บันทึกจะถูกลบซ้ำ 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
ค้นหาการอ้างอิงสำหรับตารางที่ระบุ
ฟังก์ชันเพื่อค้นหาการขึ้นต่อกันของตารางที่ระบุ table
. พารามิเตอร์ที่จำเป็น - config_path
и table
.
หลังจากเปิดตัว พจนานุกรมจะแสดงบนหน้าจอ โดยที่:
in_refs
— พจนานุกรมของตารางที่อ้างอิงถึงตารางที่กำหนด โดยที่คีย์คือชื่อของตาราง ค่าคือรายการของออบเจ็กต์ Foreign Key (pk_main
- คีย์หลักในตารางหลัก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')]}}
ค้นหาการอ้างอิงถึงสตริงด้วยคีย์หลักที่ระบุ
ฟังก์ชั่นค้นหาแถวในตารางอื่นที่อ้างอิงแถวผ่าน Foreign Key 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'}]}}}
การใช้งานในโค้ด
นอกเหนือจากการรันบนคอนโซลแล้ว ไลบรารียังสามารถใช้ในโค้ด 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')]}}
ค้นหาการอ้างอิงถึงสตริงด้วยคีย์หลักที่ระบุ
>>> 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'}]}}}
ซอร์สโค้ดของห้องสมุดมีอยู่ที่
ฉันยินดีที่จะแสดงความคิดเห็นความมุ่งมั่นและข้อเสนอแนะ
ฉันจะพยายามตอบคำถามอย่างสุดความสามารถที่นี่และในที่เก็บข้อมูล
ที่มา: will.com