From 2a70f52c823f26060f05da7be77d215f4b6a6555 Mon Sep 17 00:00:00 2001
From: Philipp Klaus <klaus@physik.uni-frankfurt.de>
Date: Wed, 19 Jul 2017 13:52:17 +0200
Subject: [PATCH] python_suite: New package css_psql_archive

---
 python_suite/css_psql_archive/README.md       |  5 +++
 .../css_psql_archive/css_psql_archive.py      | 43 +++++++++++++++++++
 python_suite/css_psql_archive/setup.py        | 19 ++++++++
 3 files changed, 67 insertions(+)
 create mode 100644 python_suite/css_psql_archive/README.md
 create mode 100644 python_suite/css_psql_archive/css_psql_archive.py
 create mode 100644 python_suite/css_psql_archive/setup.py

diff --git a/python_suite/css_psql_archive/README.md b/python_suite/css_psql_archive/README.md
new file mode 100644
index 0000000..46a55c2
--- /dev/null
+++ b/python_suite/css_psql_archive/README.md
@@ -0,0 +1,5 @@
+Python Package "css_psql_archive" - An interface to the CS-Studio PostgreSQL Archive for EPICS PVs
+==================================================================================================
+
+
+
diff --git a/python_suite/css_psql_archive/css_psql_archive.py b/python_suite/css_psql_archive/css_psql_archive.py
new file mode 100644
index 0000000..e6e53c4
--- /dev/null
+++ b/python_suite/css_psql_archive/css_psql_archive.py
@@ -0,0 +1,43 @@
+#!/usr/bin/env python
+
+import psycopg2, psycopg2.extras
+import pandas as pd
+from matplotlib import pyplot as plt
+
+class Archive:
+    def __init__(self, host, user="report", port=5432, dbname="archive"):
+        self.conn = psycopg2.connect(dbname=dbname, user=user, host=host, port=port)
+        self.cur = self.conn.cursor()
+
+    def dispose(self):
+        self.conn.close()
+        
+    def get_channel_id(self, pv_name):
+        sql = """SELECT channel_id FROM archive.channel
+                 WHERE archive.channel.name = '{}';""".format(pv_name)
+        self.cur.execute(sql)
+        return self.cur.fetchone()[0]
+    
+    def get_all_pv_names(self):
+        sql = "SELECT name FROM archive.channel;"
+        self.cur.execute(sql)
+        pv_names = self.cur.fetchall()
+        pv_names = [el[0] for el in pv_names]
+        return pv_names
+
+    def get_single_pv(self, pv_name):
+        channel_id = self.get_channel_id(pv_name)
+        sql = """SELECT smpl_time, float_val FROM archive.sample
+                 WHERE archive.sample.channel_id = {};""".format(channel_id)
+        df = pd.read_sql_query(sql, self.conn, index_col='smpl_time')
+        return df
+
+    def plot_single_pv(self, pv_name, **kwargs):
+        values = self.get_single_pv(pv_name)['float_val']
+        low =  values.resample('5min').min()
+        high = values.resample('5min').max()
+        times = high.index
+        values.resample('5min').mean().plot(label='mean', **kwargs)
+        plt.fill_between(list(times), list(low), list(high), alpha=0.3)
+        plt.legend()
+        
\ No newline at end of file
diff --git a/python_suite/css_psql_archive/setup.py b/python_suite/css_psql_archive/setup.py
new file mode 100644
index 0000000..fe5639d
--- /dev/null
+++ b/python_suite/css_psql_archive/setup.py
@@ -0,0 +1,19 @@
+try:
+    from setuptools import setup
+except ImportError:
+    from distutils.core import setup
+
+setup(name='css_psql_archive',
+      version='0.2',
+      description='An interface to the CS-Studio PostgreSQL Archive for EPICS PVs',
+      url='',
+      author='Philipp Klaus',
+      author_email='klaus@physik.uni-frankfurt.de',
+      py_modules=['css_psql_archive'],
+      install_requires=['psycopg2', 'pandas', 'matplotlib'],
+      #entry_points = {
+      #  'console_scripts': [
+      #    'css_psql_archive = css_psql_archive:main',
+      #  ],
+      #},
+      zip_safe=True)
-- 
2.43.0