python如何实时查询oracle_Oracle如何用python批量查询各个表数据量

除了一个表一个表的查询。有没有其他办法？

我们当然是想用一个高大上的方法来实现。

巧用系统表user_tables拼接查询语句，并将语句导入一个临时表，

执行临时表中拼接好的sql，再次将sql的查询结果导入一个结果表。

就完成了！

0，拼接sql，生成批量脚本：

create table sptup_org_ct_sql as

select table_name,

'insert into sptup_org_ct select sysdate dt,''' || table_name ||

''' tablename, org_code, org_name, count(1) ct from ' ||

table_name || ' group by org_code, org_name;' insert_sql

from user_tables

where substr(table_name, 0, 3) = 'EMR'

and substr(table_name, -3, 3) <> '_IN';

1，创建结果表，准备接收查询结果：

create table SPTUP_ORG_CT

(

dt DATE,

tablename VARCHAR2(119),

org_code VARCHAR2(132) not null,

org_name VARCHAR2(170) not null,

ct NUMBER

);

2，开发python脚本批量执行sql脚本；

为了提高效率，此处我们还采用python的并行技术，

使用的python包是from multiprocessing import Pool,Processprocess

python脚本，如下：

import cx_Oracle

import sys

from openpyxl import Workbook,load_workbook

import datetime

import os

from multiprocessing import Pool,Process

def db_exe(p_sql):

#print(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))

#print(p_sql)

ora_conn = cx_Oracle.connect('username/password@ip:port/db')

ora_cursor = ora_conn.cursor()

for k,v in p_sql.items():

print(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),k%9,k,v)

ora_cursor.execute(v)

ora_conn.commit()

ora_cursor.close()

ora_conn.close()

print(k%9,'进程结束！！！')

if __name__=='__main__':

today = datetime.datetime.today().strftime("%Y-%m-%d")

#ws = wb.create_sheet(today, index=0)

ora_conn = cx_Oracle.connect('username/password@ip:port/db')

ora_cursor0=ora_conn.cursor()

#ora_cursor1=ora_conn.cursor()

#ora_cursor2 = ora_conn.cursor()

ora_cursor0.execute('truncate table sptup_r')

sql0='''select table_name, partition_name, q, ct from sptup_q'''

result0=ora_cursor0.execute(sql0)

#p = Pool(4)

p9sql = {}

p={}

for h in range(9):

p9sql[h]={}

i=10000

for row0 in result0:

sql1=row0[2].strip(';')

p9sql[i%9][i]=sql1

i = i + 1

#print(p0sql)

#exit(0)

for j in range(9):

p[j]=Process(target=db_exe, args=(p9sql[j],))

p[j].start()

for k in range(9):

p[k].join()

exit(0)

sql2='''select tab, tab_par, org_code, org_name, ct, dt from sptup_r'''

result2=ora_cursor2.execute(sql2)

for row2 in result2:

ws.append(row2)

wb.save('D:\Project\stpup_org_ct_{}.xlsx'.format(today))

exit(0)