所有表列信息

获取所有库名

select datname from pg_database where datistemplate=false

获取表名

select tablename from pg_tables where schemaname = 'public'

获取列名

SELECT column_name
FROM information_schema.columns 
WHERE table_name='表名' 
AND table_schema='public';

SELECT *
FROM information_schema.columns 
WHERE table_name='ddg_node_btn' 

关系外键列

SELECT x.table_name, x.column_name FROM information_schema.key_column_usage x INNER JOIN (SELECT t.relname, a.conname FROM pg_constraint a INNER JOIN pg_class ft ON ft.oid = a.confrelid INNER JOIN pg_class t ON t.oid = a.conrelid WHERE a.contype = 'f' AND a.confrelid = (select e.oid from pg_class e where e.relname = 'ddg_gain_assess') ) tp ON (x.table_name = tp.relname AND x.constraint_name = tp.conname)

以指定符号分隔（列转行）

SELECT ARRAY_TO_STRING(ARRAY_AGG(column_name),', ') 
FROM information_schema.columns 
WHERE table_name='表名' 
AND table_schema='public';

：https://blog.csdn.net/Yisermorn/article/details/130950052

SELECT
     tc.constraint_name, tc.table_name, kcu.column_name, 
     ccu.table_name AS foreign_table_name,
     ccu.column_name AS foreign_column_name,
     tc.is_deferrable,tc.initially_deferred
 FROM 
     information_schema.table_constraints AS tc 
     JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
     JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
 WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name = 'demo';

获取postgres数据库的所有表名

    def get(self):try:tables = connection.introspection.table_names()return JsonRes(tables, success=True, info="get database name Successfully.")except Exception as err:return JsonRes(None, success=False, info='get database name failed, please check the format.')

最后tables=[‘table1’, ‘table2’]

获取postgres数据库的一个表的所有column

    def get(self, request, tablename):try:with connection.cursor() as cursor:desc = connection.introspection.get_table_description(cursor, tablename)columns = [r[0] for r in desc]return JsonRes(columns, success=True, info="get column name Successfully.")except Exception as err:return JsonRes(None, success=False, info='get column name failed.')

这里区分以下flask和django下module的区别
django

从django引入module

from django.db import modelsclass USER(models.Model):name = models.CharField(db_index=True, max_length=200, null=False, default='')description = models.CharField(max_length=255, null=False, default='')teacher_id= models.ForeignKey('TEACHER', on_delete=models.CASCADE, default=1)# teacher_id作为user的外键class Meta:db_table = 'user'# 数据库名def __str__(self):return self.name
flask

from sqlalchemy import Column, String, ForeignKeyfrom cxcomm.db.model import BaseTable, HasId, HasTimeclass USER(BaseTable, HasId, HasTime):__tablename__ = 'user'name = Column(String(255), nullable=False)description = Column(String(255), nullable=False)teacher_id= Column(String(36), ForeignKey('teacher.id'), nullable=True, index=True)
from yactrl.models.user import USER

导入之前的模型

from cxcomm.db.ormobject import ORMBaseObject
from cxcomm.db import fieldsclass USERObj(ORMBaseObject):"""snmp object"""db_model = USERfields = {'id': fields.UUIDField(nullable=True),'name ': fields.StringField(nullable=False),'description ': fields.UUIDField(nullable=False),'teacher_id': fields.StringField(nullable=True)}allow_update_key = ['description ']

原文地址:https://blog.csdn.net/ambzheng/article/details/101372125