PostgreSQL: SQL のサンプル （スキーマの一覧）

スキーマの一覧を表示する SQL

SELECT schema_name FROM information_schema.schemata;

スキーマ名が sch で始まるものだけを選ぶ

SELECT schema_name
FROM information_schema.schemata where schema_name LIKE 'sch%';

Python 版

.env

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

select_schema.py

#! /usr/bin/python
#
#	select_schema.py
#
#					Jun/13/2024
#
# --------------------------------------------------------
import sys
import os
import psycopg2
import psycopg2.extras
from dotenv import load_dotenv
#
# --------------------------------------------------------
# [6]:
def db_search_proc(cur):
	sql_str= "SELECT schema_name "
	sql_str += "FROM information_schema.schemata "
	sql_str += "where schema_name LIKE 'sch%';"
#
	print(sql_str)
#
	cur.execute (sql_str)
	rows = cur.fetchall()
#
	if len(rows) == 0:
		sys.stderr.write("*** error ***\n")
#
	schemas = []
	for row in rows:
		schemas.append(row[0])
#
	return sorted(schemas)
# --------------------------------------------------------
# [4]:
def db_process():
	conn = psycopg2.connect("dbname=" + DATA_BASE + " user=" + USER + " password=" + PASSWORD)
#
	cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
#
	schemas = db_search_proc(cur)

	for schema in schemas:
		print(schema)

	conn.commit ()
#
	cur.close ()
	conn.close ()
#
# --------------------------------------------------------
sys.stderr.write ("*** 開始 ***\n")
#
dotenv_path = '.env'
load_dotenv(dotenv_path)
USER=os.environ.get("user")
PASSWORD=os.environ.get("password")
DATA_BASE=os.environ.get("data_base")
#
db_process()
#
sys.stderr.write ("*** 終了 ***\n")
#
# --------------------------------------------------------