# -*- coding: utf-8 -*-
#
"""
查询Oracle中数据
"""

import random
import string
import os
import subprocess
import cx_Oracle
import time
from datetime import date, datetime

ora87gbk = {
    'user' : '', 
    'password' : '',
    'host' : '127.0.0.1',
    'port' : '1521',
    'sid' : 'oratidb'
}

tab_schema = "ts_coms"
ora_query_sum = "select owner,object_type,count(1) from dba_objects where owner='%s' group by owner,object_type" %(tab_schema.upper())
ora_query_detail = "select distinct table_name from dba_tables where owner='%s' order by table_name " %(tab_schema.upper())
output = []

cnx = cx_Oracle.connect(user="",
        password="",
        dsn="127.0.0.1:1521/oratidb")
cursor = cnx.cursor()
cursor.execute(ora_query_detail)
#rows = cursorlob.fetchall()
for row in cursor:
    output.append(row[0])
    # print(row)

cursor.close()

t_gt0 = 0
t_le0 = 0

filename = "tab_count_ora.%s.txt" %(tab_schema)
f = open(filename, mode='a')

cursor = cnx.cursor()
for tab_name in output:
    ora_tablecount = "select count(1) from %s.%s" %(tab_schema, tab_name)
    try:
        cursor.execute(ora_tablecount)
        dataset = cursor.fetchall()
        result = ','.join([str(item) for row in dataset for item in row])
        # print("%s: %s" %(tab_name, result))
        for row in dataset:
            if row[0]>0:
                t_gt0 = t_gt0 + 1
                f.write(tab_schema.lower()+"."+tab_name.lower() + " " + result + "\n")
            else:
                t_le0 = t_le0 + 1
    except:
        print("query failed,exit %s" %(ora_tablecount))

cursor.close()
cnx.close()
f.close()
print("total tab:",len(output),"t_gt0:",t_gt0,"t_le0:",t_le0)
#print(output)