Python/Postgresql – Query Database and present data in a plot

How to query a database using a Python script and present the data on a plot.

import pandas.io.data
import matplotlib.pyplot as plt
import pandas as pd
from pandas import DataFrame
import datetime
import pandas.io.data
import psycopg2
import re
import os


conn = psycopg2.connect("host='172.31.98.161' dbname='servers' user='seeker'")
cur = conn.cursor() #Create the cursor
cur.execute("select total_ram_raw, used_ram_raw, time_captured  from server_perf  where hostname='localhost.localdomain'")
rows = cur.fetchall()
conn.close()

df = pd.DataFrame(rows, columns=['TotalRam','UsedRAM', 'Time'])

df.to_csv("file.csv")
df.TotalRam = df.TotalRam.astype(float)
df.UsedRAM  = df.UsedRAM.astype(float)

df = pd.read_csv('file.csv', index_col='Time')
df[['TotalRam', 'UsedRAM']].plot()
plt.show()
Share Button