PostgreSQL: プライマリーキーの取得

プログラム

get_primary_key.py

#! /usr/bin/python
#
#	get_primary_key.py
#
#				Jun/03/2024
#
# --------------------------------------------------------
import os
import sys
import psycopg2
from dotenv import load_dotenv
#
# --------------------------------------------------------
# [8]:
def get_primary_key_proc(cursor_aa,schema,table_target):
#	sql_str = "SELECT tc.table_name, kcu.column_name "
	sql_str = "SELECT kcu.column_name "
	sql_str += "FROM information_schema.table_constraints AS tc "
	sql_str += "JOIN information_schema.key_column_usage AS kcu "
	sql_str += "ON tc.constraint_name = kcu.constraint_name "
	sql_str += "AND tc.table_schema = kcu.table_schema "
	sql_str += "WHERE tc.constraint_type = 'PRIMARY KEY' "
	sql_str += "AND tc.table_name = '" + table_target + "' "
	sql_str += "AND tc.table_schema = '" + schema + "' "
	sql_str += ""
	print(sql_str)
	array_primary = []
	try:
		cursor_aa.execute(sql_str)
		rows = cursor_aa.fetchall()
		print(len(rows))
		for row in rows:
			array_primary.append(row[0])
	except Exception as ee:
		sys.stderr.write("*** error *** in get_primary_key_proc ***\n")
		sys.stderr.write("*** error *** in cursor_aa.execute ***\n")
		sys.stderr.write(str(ee) + "\n")
	return array_primary
# --------------------------------------------------------
sys.stderr.write ("*** 開始 ***\n")
schema = "sch001"
table_target = sys.argv[1]
sys.stderr.write ("%s\n" % table_target)
#
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()
#
array_primary_keys = get_primary_key_proc(cursor_aa,schema,table_target)
print(array_primary_keys)
#
conn.commit ()
cursor_aa.close ()
conn.close ()
#
sys.stderr.write ("*** 終了 ***\n")
#
# --------------------------------------------------------

.env

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

実行方法

./get_primary_key.py grp001001

参考にした SQL

SELECT tc.table_name, kcu.column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
  ON tc.constraint_name = kcu.constraint_name
  AND tc.table_schema = kcu.table_schema
WHERE tc.constraint_type = 'PRIMARY KEY'
  AND tc.table_name = 'grp001001'
AND tc.table_schema = 'sch001';