Streamlit real time
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 streamlit real time chart[편집]
1.1 Build a real-time dashboard in Python with Tinybird and Dash[편집]
1.2 샘플[편집]
import streamlit as st
import time
import numpy as np
import pandas as pd
st.set_page_config(
page_title='Sirkülasyon Testi',
layout="wide",
initial_sidebar_state="collapsed",
)
st.sidebar.markdown('''
### Sirkülasyon Testi
Yapılacak değişiklikler programı resetler.
''')
marka = st.sidebar.text_input("Pompa Markası:","Brand")
model = st.sidebar.text_input("Pompa Modeli:","Model")
mod = st.sidebar.text_input("Çalıştırma Modu:","dP5.0")
zamandamgasi = time.strftime("]_%Y%b%d_%H.%M.%S")
isim = marka+"_"+model+"_["+mod+zamandamgasi
i = 0
t = 0
st.sidebar.write("Otomatik Vana Modunda Deney")
Dint = st.sidebar.number_input("Başlangıç Noktası:",value=15.0)
Dfin = st.sidebar.number_input("Bitiş Noktası:",value=70.0)
ds = st.sidebar.number_input("Adım Aralığı",value=0.25)
dt = st.sidebar.number_input("Adımlar Arası Periyot",value=8)
if st.sidebar.button("Otomatik Modda Kayıt Başlat"):
kaydet = 1
else:
kaydet = 0,
st.sidebar.write("Manuel Vana Ayarı")
D = st.sidebar.number_input("Vana Açıklığı [0:Kapalı -90:Açık]",value=0.0)
if st.sidebar.button("Ayarla"):
yaz(64,[int(D*4)])
sozluk =[
['Date' ,"Tarih" ,"YMD" ,"" ],
['Time' ,"Zaman" ,"HMS" ,"" ],
['Temperature' ,"Sıcaklık" ,"T" ,"°C" ],
['Flow_Rate' ,"Debi" ,"Q" ,"m³/saat" ],
['Valve_Aperture' ,"Vana Açıklığı" ,"D" ,"°" ],
['Static_Pressure' ,"Durağan Basınç" ,"P" ,"Bar" ],
['Suction_Pressure' ,"Emme Basıncı" ,"P1" ,"Bar" ],
['Discharge_Pressure' ,"Basma Basıncı" ,"P2" ,"Bar" ],
['Voltage' ,"Gerilim" ,"V" ,"V" ],
['Current' ,"Akım" ,"I" ,"A" ],
['Measured_Frequency' ,"Ölçülen Frekans" ,"f" ,"Hz" ],
['Active_Power' ,"Aktif Güç" ,"PAP" ,"W" ],
['Reactive_Power' ,"Reaktif Güç" ,"QRP" ,"VAr" ],
['Apperent_Power' ,"Görünür Güç" ,"SAP" ,"VA" ],
['Phase_Voltage_Angle' ,"Faz Gerilim Açısı" ,"PVA" ,"°" ],
['Phase_Current_Angle' ,"Faz Akım Açısı" ,"PCA" ,"°" ],
['Cos_Phi' ,"Cos_Phi" ,"PHI" ,"" ],
['Power_Factor' ,"Güç Faktörü" ,"PF" ,"" ],
['Differential_Pressure' ,"Fark Basıncı" ,"DP" ,"Bar" ],
['Head' ,"Basma Yüksekliği" ,"H" ,"m" ],
['Hydraulic_Power' ,"Hidrolik Güç" ,"PHYD" ,"W" ],
['Efficiency' ,"Verimlilik" ,"ETA" ,"%" ]
]
varis = []
for i in sozluk: varis.append(i[0]) #sozluk ilk sütun (Türkçe için 0 yerine 1. sütun seçilebilir.)
df = pd.DataFrame(columns=varis)
buffy = pd.DataFrame(columns=varis)
col = st.columns(4)
###################################### G R A F İ K L E R #############################################
col[0].subheader("Debi & Basma Yüksekliği [m³/h,m] \n Flow Rate & Head")
QHt = col[0].line_chart()
col[0].subheader("Hidrolik Güç & Verimlilik [W,%] \n Hydraulic Power & eta")
PHYDETAt = col[0].line_chart()
col[1].subheader("Emme & Basma Basınçları [Bar] \n Suction & Discharge Pressure")
P1P2t = col[1].line_chart()
col[1].subheader("Elektrik Güçleri [W, VA, VAr] \n Active, Reactive & Apperent Power" )
PAPQRPSAP = col[1].line_chart()
col[2].subheader("cos(φ) & Güç Faktörü \n cos(φ) & Power Factor")
PHIPFt = col[2].line_chart()
col[2].subheader("Faz Gerilim & Akım Açıları [°] \n Phase Voltage & Current Angles")
PVAPCAt = col[2].line_chart()
###################################### M E T R İ K L E R ##############################################
col[3].write(f"Deney Kimliği: \n {isim}")
YMDt = col[3].empty()
HMSt = col[3].empty()
Tt = col[3].empty()
Dt = col[3].empty()
Pt = col[3].empty()
Vt = col[3].empty()
ft = col[3].empty()
##################################### D E M O ##########################################################
df = pd.read_excel("demo_data.xlsx",index_col=0)
######################################### D Ö N G Ü ####################################################
for i in range(len(df)):
buff=df.iloc[i]
with Tt:
st.metric("Hat Sıcaklığı",f"{buff[2]} °C")
with YMDt:
st.metric("Tarih",buff[0])
with HMSt:
st.metric("Saat",buff[1])
with Dt:
st.metric("Vana Açıklığı",f"{buff[4]} °")
with Pt:
st.metric("Durağan Basınç",f"{buff[5]} Bar")
with Vt:
st.metric("Gerilim",f"{buff[8]} V")
with ft:
st.metric("Ölçülen Frekans",f"{buff[10]} Hz")
QHt.add_rows([[float(buff[3]),float(buff[19])]])
PHYDETAt.add_rows([[float(buff[20]),float(buff[21])]])
P1P2t.add_rows([[float(buff[6]),float(buff[7])]])
PHIPFt.add_rows([[float(buff[16]),float(buff[17])]])
PAPQRPSAP.add_rows([[float(buff[11]),float(buff[12]),float(buff[13])]])
PVAPCAt.add_rows([[float(buff[14]),float(buff[15])]])
time.sleep(0.1)
# if kaydet == 1:
# if t%dt==0 and Dint<Dfin:
# Dint=Dint+ds
# yaz(64,[int(Dint*4)])
# elif Dint>=Dfin:
# df.to_excel(f"{isim}.xlsx")
# kaydet=0
1.3 oracle ash real-time dash-board[편집]
- matplotlib 이용
pip install streamlit cx_Oracle pandas matplotlib
import cx_Oracle
import pandas as pd
import streamlit as st
import time
# Oracle 데이터베이스 연결 설정
def connect_to_oracle(user, password, dsn):
try:
connection = cx_Oracle.connect(user, password, dsn)
return connection
except cx_Oracle.DatabaseError as e:
st.error(f"Database connection error: {e}")
return None
# ASH 데이터를 가져오는 함수
def fetch_ash_data(connection):
query = """
SELECT sample_time,
session_id,
session_state,
event,
wait_class,
blocking_session,
current_obj#,
current_file#,
current_block#
FROM v$active_session_history
WHERE sample_time > sysdate - INTERVAL '5' MINUTE
"""
df = pd.read_sql(query, connection)
return df
import matplotlib.pyplot as plt
# Streamlit 페이지 구성
def main():
st.title("Oracle ASH Real-time Monitoring")
# Oracle 연결 정보 입력받기
st.sidebar.header("Database Connection")
user = st.sidebar.text_input("Username")
password = st.sidebar.text_input("Password", type="password")
host = st.sidebar.text_input("Host")
port = st.sidebar.text_input("Port", value="1521")
service_name = st.sidebar.text_input("Service Name")
dsn = cx_Oracle.makedsn(host, port, service_name=service_name)
if st.sidebar.button("Connect"):
connection = connect_to_oracle(user, password, dsn)
if connection:
st.success("Connected to Oracle Database!")
# 실시간 모니터링 시작
placeholder = st.empty()
while True:
# ASH 데이터 가져오기
df = fetch_ash_data(connection)
# ASH 데이터 프레임 출력
with placeholder.container():
st.subheader("Active Session History Data (Last 5 Minutes)")
st.dataframe(df)
# 세션 상태 분포 시각화
st.subheader("Session State Distribution")
session_state_counts = df['SESSION_STATE'].value_counts()
st.bar_chart(session_state_counts)
# 이벤트별 분포 시각화
st.subheader("Wait Events Distribution")
event_counts = df['EVENT'].value_counts().head(10)
st.bar_chart(event_counts)
# 주기적으로 데이터 갱신 (5초마다)
time.sleep(5)
if __name__ == "__main__":
main()
- echart 이용
pip install streamlit cx_Oracle pandas pyecharts streamlit-echarts
- 오라클 연결은 위와 동일
from pyecharts.charts import Bar
from pyecharts import options as opts
from streamlit_echarts import st_pyecharts
# 세션 상태 분포 차트 생성
def create_session_state_chart(session_state_counts):
bar = Bar()
bar.add_xaxis(session_state_counts.index.tolist())
bar.add_yaxis("Session State", session_state_counts.values.tolist())
bar.set_global_opts(title_opts=opts.TitleOpts(title="Session State Distribution"))
return bar
# 이벤트 분포 차트 생성
def create_event_chart(event_counts):
bar = Bar()
bar.add_xaxis(event_counts.index.tolist())
bar.add_yaxis("Wait Events", event_counts.values.tolist())
bar.set_global_opts(title_opts=opts.TitleOpts(title="Wait Events Distribution"))
return bar
# Streamlit 페이지 구성
def main():
st.title("Oracle ASH Real-time Monitoring with ECharts")
# Oracle 연결 정보 입력받기
st.sidebar.header("Database Connection")
user = st.sidebar.text_input("Username")
password = st.sidebar.text_input("Password", type="password")
host = st.sidebar.text_input("Host")
port = st.sidebar.text_input("Port", value="1521")
service_name = st.sidebar.text_input("Service Name")
dsn = cx_Oracle.makedsn(host, port, service_name=service_name)
if st.sidebar.button("Connect"):
connection = connect_to_oracle(user, password, dsn)
if connection:
st.success("Connected to Oracle Database!")
# 실시간 모니터링 시작
placeholder = st.empty()
while True:
# ASH 데이터 가져오기
df = fetch_ash_data(connection)
# ASH 데이터 프레임 출력
with placeholder.container():
st.subheader("Active Session History Data (Last 5 Minutes)")
st.dataframe(df)
# 세션 상태 분포 시각화
st.subheader("Session State Distribution")
session_state_counts = df['SESSION_STATE'].value_counts()
session_state_chart = create_session_state_chart(session_state_counts)
st_pyecharts(session_state_chart)
# 이벤트별 분포 시각화
st.subheader("Wait Events Distribution")
event_counts = df['EVENT'].value_counts().head(10)
event_chart = create_event_chart(event_counts)
st_pyecharts(event_chart)
# 주기적으로 데이터 갱신 (5초마다)
time.sleep(5)
if __name__ == "__main__":
main()