SQLAlchemy 強制清空所有資料表(tables)

Posted on  Aug 4, 2019  in  Python 模組/套件推薦  by  Amo Chen  ‐ 2 min read

在產品開發時,有時候會有將所有的資料表進行清空的需求,如果是用 SQLAlchemy 清空單一資料表也很簡單,但如果在多個資料表都有用到 FOREIGN KEY 就很容易遇到 IntegrityError ,原因在於如果有其他紀錄(record)關聯到程式正在刪除的紀錄(record)時,就會導致那些紀錄違反 FOREIGN KEY Constraint 無法刪除成功。

本篇記錄如何忽視 FOREIGN KEY Constraint 強制刪除所有資料表。

本文環境

  • Python 3.6.5
  • SQLAlchemy 1.3.6

模擬 Models

以下為本文所模擬的 2 個 models - User & Address:

# -*- coding: utf-8 -*-
import hashlib
import sqlalchemy.orm
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref
from sqlalchemy.orm import sessionmaker
from sqlalchemy import desc
from sqlalchemy.orm import aliased
from sqlalchemy import func


Base = declarative_base()


class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    username = Column(String)
    password = Column(String)

    def __init__(self, name, username, password):
        self.name = name
        self.username = username
        self.password = hashlib.sha1(password).hexdigest()


class Address(Base):
    __tablename__ = 'user_address'

    id = Column(Integer, primary_key=True)
    address = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey('user.id'))
    user = relationship('User', backref=backref('address', order_by=id))

    def __init__(self, address):
        self.address = address
        
        
if __name__ == '__main__':
    engine = create_engine('sqlite:///:memory:', echo=True)
    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    session = Session()
    user_1 = User('user1', 'username1', 'password_1'.encode('utf-8'))
    user_2 = User('user2', 'username2', 'password_2'.encode('utf-8'))
    user_1.address = [Address('臺灣凱達格蘭大道')]
    user_2.address = [Address('美國紐約時代廣場'), Address('美國華盛頓DC')]
    session.add(user_1)
    session.add(user_2)
    session.commit()

上述不僅模擬 2 個 models, 還為 2 個 models 分別新增幾筆測試資料。

清空單一資料表

簡單來說,如果要清空 User 資料表的話,可以使用以下範例:

session.execute(
	Base.metadata.tables['user'].delete()
)

由於所有的 models 都繼承 Base ,因此可以從 Base.metadata.tables 取得所有的資料表, tables 是 1 個 immutabledict ,裡面的每個值都是 Table object ,對 Table 操作的方法實作於該類別之中,其中 delete() 在不代入任何條件時,就會執行類似以下的 SQL:

DELETE FROM <table>;

從而清空整個資料表。

清空多個資料表

但如果有使用到 MySQL, PostgreSQL 等資料庫所提供的 FOREIGN KEY 的功能時,在清空多個資料表時,很容易遇到 IntegrityError ,此時可以先暫時關掉 FOREIGN KEY CHECKS ,再使用 for 迴圈一一清空所有的資料表,以 MySQL 為例:

session.execute('SET FOREIGN_KEY_CHECKS = 0')
for table in Base.metadata.tables:
    session.execute(table.delete())
session.execute('SET FOREIGN_KEY_CHECKS = 1')
session.commit()

在此就不多贅述如何關閉 PostgreSQL 的 FOREIGN KEY CHECKS 囉,主要的程式思路是一樣的。

此外,清空所有資料表是相當危險的功能,使用前都必須再三小心注意,避免不小心就清空 Production 的資料。

References

https://docs.sqlalchemy.org/en/13/core/metadata.html#sqlalchemy.schema.Table

https://docs.sqlalchemy.org/en/13/core/dml.html#sqlalchemy.sql.expression.delete

對抗久坐職業傷害

研究指出每天增加 2 小時坐著的時間,會增加大腸癌、心臟疾病、肺癌的風險,也造成肩頸、腰背疼痛等常見問題。

然而對抗這些問題,卻只需要工作時定期休息跟伸展身體即可!

你想輕鬆改變現狀嗎?試試看我們的 PomodoRoll 番茄鐘吧! PomodoRoll 番茄鐘會根據你所設定的專注時間,定期建議你 1 項辦公族適用的伸展運動,幫助你打敗久坐所帶來的傷害!

贊助我們的創作

看完這篇文章了嗎? 休息一下,喝杯咖啡吧!

如果你覺得 MyApollo 有讓你獲得實用的資訊,希望能看到更多的技術分享,邀請你贊助我們一杯咖啡,讓我們有更多的動力與精力繼續提供高品質的文章,感謝你的支持!