#!/usr/bin/env python3
import os
import psycopg2
import psycopg2.extras

import pandas as pd
import matplotlib.pyplot as plt

#    cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
#        cursor.execute(sql)
#        records = cursor.fetchall()
#        pprint.pprint(records)

server='rising'
server_label = server
# TODO add server name from lookup and use that for chart title
sets=">0"
sets="IN (20,23,5)"
sql="""
SELECT 
--  server_info,
  testset.info,
--  script,tests.set,
  clients,round(avg(tps)) AS tps 
FROM tests 
  JOIN server ON (tests.server=server.server)
  JOIN testset ON (tests.set=testset.set AND tests.server=testset.server)
WHERE
  tests.server='%s' AND
  tests.set %s
GROUP BY server_info,testset.info,tests.server,tests.set,tests.script,clients
ORDER BY server_info,testset.info,tests.server,tests.set,tests.script,clients
;""" % (server,sets);

def main():
    conn_string = "host='localhost' dbname='results' user='gsmith' password='secret'"
    print("Connecting to database\n	->%s" % (conn_string))
    conn = psycopg2.connect(conn_string)

    try:
        df = pd.read_sql_query(sql, conn)
        return df
    finally:
        conn.close()

if __name__ == "__main__":
    # TODO Create this directory if it doesn't exist
    base="images"
    df=main()
    df.set_index(['clients'], inplace=True)
    g=df.groupby('info')
    print(g)

    # Disable the warning generated by the rename    
    pd.options.mode.chained_assignment = None  # default='warn'

    fig=plt.figure();
    ax=fig.add_subplot(1,1,1)
    
    for k,v in g:
        print("Processing",k)
        v.rename(columns={'tps':k},inplace=True)
        print(v)
        ax=v.plot(grid=True,title=server_label,figsize=(8,6),ax=ax)
        ax.set_ylabel('TPS')
        ax.xaxis.grid(True, which="minor")

        # Overwrite this for now to get all 3 saved to one file
        k=server
        
        unslashed=k.replace("/","-")
        fn=os.path.join(base,unslashed)

        plt.savefig(fn,dpi=80)  # gives 640x480 figures
#        plt.close()
        print("saved to '%s.png'" % fn)
    ax.legend()
    plt.show()

#!/usr/bin/env python3
import os
import psycopg2
import psycopg2.extras

import pandas as pd
import matplotlib.pyplot as plt

#    cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
#        cursor.execute(sql)
#        records = cursor.fetchall()
#        pprint.pprint(records)

server='rising'
server_label = server
# TODO add server name from lookup and use that for chart title
sets=">0"
#sets="IN (20,23,5)"
sql="""
SELECT 
--  server_info,
  testset.info,
--  script,tests.set,
  clients,round(avg(tps)) AS tps 
FROM tests 
  JOIN server ON (tests.server=server.server)
  JOIN testset ON (tests.set=testset.set AND tests.server=testset.server)
WHERE
  tests.server='%s' AND
  tests.set %s AND
  testset.category='reference'
GROUP BY server_info,testset.info,tests.server,tests.set,tests.script,clients
ORDER BY server_info,testset.info,tests.server,tests.set,tests.script,clients
;""" % (server,sets);

def main():
    conn_string = "host='localhost' dbname='results' user='gsmith' password='secret'"
    print("Connecting to database\n	->%s" % (conn_string))
    conn = psycopg2.connect(conn_string)

    try:
        df = pd.read_sql_query(sql, conn)
        return df
    finally:
        conn.close()

if __name__ == "__main__":
    # TODO Create this directory if it doesn't exist
    base="images"
    df=main()
    df.set_index(['clients'], inplace=True)
    g=df.groupby('info')
    print(g)

    # Disable the warning generated by the rename    
    pd.options.mode.chained_assignment = None  # default='warn'

    fig=plt.figure();
    ax=fig.add_subplot(1,1,1)
    
    for k,v in g:
        print("Processing",k)
        v.rename(columns={'tps':k},inplace=True)
        print(v)
        ax=v.plot(grid=True,title=server_label,figsize=(8,6),ax=ax)
        ax.set_ylabel('TPS')
        ax.xaxis.grid(True, which="minor")

        # Overwrite this for now to get all 3 saved to one file
        k=server
        
        unslashed=k.replace("/","-")
        fn=os.path.join(base,unslashed)

        plt.savefig(fn,dpi=80)  # gives 640x480 figures
#        plt.close()
        print("saved to '%s.png'" % fn)
    ax.legend()
    plt.show()

Add testset.category to compare.py