导出oracle表信息,python3导出oracle表信息到表格

#!/usr/bin/python

# -*- coding: UTF-8 -*-

#author xiaoyi

import cx_Oracle as oracle

import sys

import os

import io

import xlwt

sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8')

os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8' #或者os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.AL32UTF8'

class ExportOracle:

def __init__(self,odbc,user):

self.odbc = odbc

self.user = user

self.xlsx = xlwt.Workbook(encoding="utf-8")

self.curIndex = 0;

def start(self):

db = oracle.connect(self.odbc)

cursor = db.cursor()

cursor.execute("SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = '%s'" % (self.user))

datas = cursor.fetchall()

for tabName in datas:

self.getTableInfo(cursor,tabName[0]);

cursor.close()

db.close();

self.xlsx.save('./tables/tables.xlsx')

print("导出完毕！")

def getPrimary(self,mcursor,tableName):

strsqlp = """

select COLUMN_NAME from user_cons_columns where table_name = '%s' GROUP BY COLUMN_NAME

"""%(tableName)

mcursor.execute(strsqlp);

res = mcursor.fetchall();

resStr = "";

for item in res:

resStr += item[0]+','

return resStr.rstrip(',')

def getTableInfo(self,mcursor,tableName):

print("表名称:%s"%tableName)

#获取表字段

strsql = """

SELECT

b.COLUMN_NAME,b.DATA_TYPE,b.DATA_LENGTH,a.COMMENTS

FROM

USER_TAB_COLUMNS b,USER_COL_COMMENTS a

WHERE

b.TABLE_NAME = '%s' AND b.TABLE_NAME = a.TABLE_NAME AND b.COLUMN_NAME = a.COLUMN_NAME

"""%(tableName)

#初始化xls

sheet = self.xlsx.add_sheet(tableName, True )

self.curIndex = 0;

sheet.write(self.curIndex, 0,"字段名")

sheet.write(self.curIndex, 1,"字段属性")

sheet.write(self.curIndex, 2,"主键")

sheet.write(self.curIndex, 4,"字段解释")

mcursor.execute(strsql);

res = mcursor.fetchall();

prik = self.getPrimary(mcursor, tableName)

for x in res:

self.curIndex = self.curIndex+1

remark = "";

pk = ""

if(x[0] == prik ):

pk="Y"

if(x[3]!=None and x[3]!='None'):

try:

remark = x[3]

print(remark)

except Exception as e:

print(e)

sheet.write(self.curIndex, 0,x[0])

sheet.write(self.curIndex, 1,x[1])

sheet.write(self.curIndex, 2,pk)

sheet.write(self.curIndex, 4,remark)

if __name__ == '__main__':

orcleDb_config = {

'odbc':'xiaoyi/xiaoyi@127.0.0.1:1521/orcl',

'user': 'XIAOYI',

}

mtables = ExportOracle(orcleDb_config['odbc'],orcleDb_config['user']);

mtables.start();

另附上xlwt官网

python连接oracle链接