怎么从Oracle数据库中获得表字段的注释
发布网友
发布时间：2022-04-22 21:08
共3个回答
懂视网
时间：2022-04-08 15:06
# coding=utf-8
__author__ = ‘jspdba‘
u‘‘‘
读取oracle注释
‘‘‘
import cx_Oracle # 导入模块
SQL=r"""
select * from user_col_comments t where 1=1
and t.comments is not null
and t.table_name = ‘@TABLE‘
"""
words=["custom","code","trans","mode","date","confirm","balance","amount","after",
"type","area","game","flow","is","big","win","status","flag","num","client",
"reward","id","before","sum","check","time","name","channel","father","content",
"open","bet","cash","pay","open"]
def conn():
db = cx_Oracle.connect(‘username‘, ‘password‘, ‘192.168.1.101:1521/orcl‘) #建立连接,3个参数分开写
print db.version
return db
# 美化
def prettify(str=None):
global words
if str!=None and len(str)>0:
for word in words:
str = str.replace(word,word.capitalize())
return str[0].lower()+str[1:]
def run(table=None,db=None):
if table!=None:
global SQL
table = table.upper()
SQL = SQL.replace("@TABLE",table)
try:
if db==None:
db = conn()
tableComment(table,db=db,closeDb=False)
cursor = db.cursor() #建立一个cursor
cursor.execute(SQL) # 执行一条sql
# row=cursor.fetchone() #取一行结果，元组(a,b,c,d)
row=cursor.fetchall() #获取所有结果，列表[(a,b,c,d),(e,f,g,h),...]
print ‘==‘*100
for x in row:
print prettify(x[1].lower())+" "*4+x[2]
# cursor.rowcount() #获取输出记录数量
print ‘==‘*100
except Exception,e:
print e
finally:
cursor.close()
db.close()
# sql = "insert into person(name, age, telephone) values(%s, %s, %s)"
# tmp = ((‘ninini‘, 89, ‘888999‘), (‘koko‘, 900, ‘999999‘))
# conn.executemany(sql, tmp) #执行多条sql
"""
打印表注释
"""
def tableComment(tableName=None,sql="select * from user_tab_comments",db=None,closeDb=True):
if not tableName==None:
sql="select * from user_tab_comments where TABLE_NAME=‘%s‘" %(tableName.upper())
try:
if db==None:
db = conn()
cursor = db.cursor() #建立一个cursor
cursor.execute(sql) # 执行一条sql
row=cursor.fetchone()
if row:
print prettify(row[0].lower())+" "*4+row[2] if row[2]!=None else ‘‘
except Exception , e:
print e
finally:
cursor.close()
if closeDb:
db.close()
# 打印单张表注释及字段注释
run("saleDetail")
# 打印单张表注释
# tableComment("saleDetail")
# 打印所有表注释（无字段注释）
# tableComment()
读取oracle注释
标签：
热心网友
时间：2022-04-08 12:14
获取字段注释：
select * from user_col_comments user_col_comments：table_name,column_name,comments相应的还有dba_col_comments，all_col_comments，这两个比user_col_comments多了ower列。
热心网友
时间：2022-04-08 13:32
