Python sqlite3: ระบุ transaction ชัดเพื่อไม่ให้ข้อมูลค้างครึ่งทาง

บันทึกคำสั่งซื้อแล้วโปรแกรมล้มก่อนบันทึกรายการสินค้า คือข้อมูลค้างครึ่งทางที่แก้ยากครับ sqlite3 ให้เราคุม transaction หรือชุดคำสั่งที่ต้องสำเร็จพร้อมกันได้ แต่ค่าเริ่มต้นปัจจุบันยังเป็น legacy transaction control และเอกสารเตือนว่าค่าเริ่มต้นจะเปลี่ยนในอนาคต โค้ดใหม่จึงควรระบุ autocommit=False ให้ชัด

เมื่อใช้โหมดนี้ commit() ยืนยันงาน, rollback() ย้อนงาน และการปิด connection ขณะยังมีงานค้างจะ rollback ครับ with connection: ช่วย commit เมื่อออกตามปกติและ rollback เมื่อมี exception แต่ ไม่ได้ปิด connection เราต้องปิดเองใน finally หรือใช้เครื่องมือจัดการทรัพยากรอีกชั้น

import sqlite3
import unittest


def make_db():
    db = sqlite3.connect(":memory:", autocommit=False)
    db.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, item TEXT NOT NULL)")
    db.commit()
    return db


class TransactionTests(unittest.TestCase):
    def test_commit_and_rollback(self):
        db = make_db()
        try:
            with db:
                db.execute("INSERT INTO orders(item) VALUES (?)", ("tea",))
            self.assertEqual(db.execute("SELECT COUNT(*) FROM orders").fetchone()[0], 1)

            with self.assertRaises(ValueError):
                with db:
                    db.execute("INSERT INTO orders(item) VALUES (?)", ("coffee",))
                    raise ValueError("cancel the whole order")
            self.assertEqual(db.execute("SELECT item FROM orders").fetchall(), [("tea",)])
        finally:
            db.close()


if __name__ == "__main__":
    unittest.main()

ขอบเขตที่ควรระวัง

ตัวอย่างใช้ :memory: เพื่อรัน self-contained ไม่แตะฐานข้อมูลจริง และใช้ ? parameter binding แทนต่อ SQL string จาก input ผู้ใช้ ในงานจริงควรกำหนด transaction boundary ให้ครอบชุดการเปลี่ยนที่ต้องไปด้วยกัน โดยไม่ครอบ network call นาน ๆ เพราะการถือ transaction ค้างอาจทำให้ writer อื่นรอ

autocommit=True เป็น SQLite autocommit mode ซึ่ง commit() และ rollback() ไม่มีผลตามเอกสาร จึงใช้แทน False โดยไม่แก้โครงสร้างโปรแกรมไม่ได้ อีกกับดักคือ with db: ไม่ใช่ with open(...): หลัง block จบ connection ยังเปิดอยู่ หากลืมปิดอาจถือ resource เกินจำเป็น ให้ทดสอบทั้งเส้นทางสำเร็จและเส้นทาง exception เหมือนในตัวอย่างครับ

Recap

ระบุ autocommit เอง ไม่พึ่งค่า default ที่กำลังจะเปลี่ยน
commit หรือ rollback ตามผลของงานทั้งชุด
ปิด connection เองแม้ใช้ with db:
ใช้ parameter binding และทดสอบ failure path ก่อนใช้งานจริง

ขอบเขตที่ควรระวัง

Recap

เลือกการใช้คุกกี้วิเคราะห์