PostgreSQL: スキーマ内のテーブルのデータを総て削除

プログラム

truncate_tables.py

#! /usr/bin/python
#
#	truncate_tables.py
#
#				Jul/11/2024
#
# --------------------------------------------------------
import os
import sys
import psycopg2
from dotenv import load_dotenv
#
# --------------------------------------------------------
def get_tables_proc(cursor_aa,schema):
	sql_str = "select table_name from information_schema.tables where table_schema = '" + schema + "'"
	cursor_aa.execute(sql_str)
#	print(sql_str)
	rows = cursor_aa.fetchall()
#	print(rows)
	array_aa = []
	for row in rows:
		array_aa.append(row[0])
#
	return array_aa
# --------------------------------------------------------
def truncate_table(cursor_aa,schema_in,table_in):
	sql_str = "truncate " + schema_in + "." + table_in
	print(sql_str)
	cursor_aa.execute(sql_str)
# --------------------------------------------------------
# sys.stderr.write ("*** 開始 ***\n")
schema_in = sys.argv[1]

dotenv_path = '.env'
load_dotenv(dotenv_path)
USER=os.environ.get("user")
PASSWORD=os.environ.get("password")
DATA_BASE=os.environ.get("data_base")
#
dsn="dbname=" + DATA_BASE + " user=" + USER + " password=" + PASSWORD
conn = psycopg2.connect(dsn)
cursor_aa = conn.cursor()
#
tables = get_tables_proc(cursor_aa,schema_in)
print(len(tables))
# print(tables)
for table in tables:
	truncate_table(cursor_aa,schema_in,table)
#
conn.commit ()
cursor_aa.close ()
conn.close ()
#
# sys.stderr.write ("*** 終了 ***\n")
#
# --------------------------------------------------------

text.env

user='scott'
password='***'
data_base='****'

実行方法

./truncate_tables.py sch001