pandas collector add `read_sql()` support (#14563)

* import sqlalchemy and os to support read_sql

3 files changed, 39 insertions, 1 deletions

diff --git a/collectors/python.d.plugin/pandas/README.md b/collectors/python.d.plugin/pandas/README.md
index 24eddb6ee9..bc680dac8e 100644
--- a/collectors/python.d.plugin/pandas/README.md
+++ b/collectors/python.d.plugin/pandas/README.md
@@ -28,6 +28,12 @@ This collector depends on some Python (Python 3 only) packages that can usually
 sudo pip install pandas requests
 ```
 
+Note: If you would like to use [`pandas.read_sql`](https://pandas.pydata.org/docs/reference/api/pandas.read_sql.html) to query a database, you will need to install the below packages as well.
+
+```bash
+sudo pip install 'sqlalchemy<2.0' psycopg2-binary
+```
+
 ## Configuration
 
 Below is an example configuration to query some json weather data from [Open-Meteo](https://open-meteo.com), 
diff --git a/collectors/python.d.plugin/pandas/pandas.chart.py b/collectors/python.d.plugin/pandas/pandas.chart.py
index 8eb4452fb9..968b1fe668 100644
--- a/collectors/python.d.plugin/pandas/pandas.chart.py
+++ b/collectors/python.d.plugin/pandas/pandas.chart.py
@@ -3,6 +3,7 @@
 # Author: Andrew Maguire (andrewm4894)
 # SPDX-License-Identifier: GPL-3.0-or-later
 
+import os
 import pandas as pd
 
 try:
@@ -11,6 +12,12 @@ try:
 except ImportError:
     HAS_REQUESTS = False
 
+try:
+    from sqlalchemy import create_engine
+    HAS_SQLALCHEMY = True
+except ImportError:
+    HAS_SQLALCHEMY = False
+
 from bases.FrameworkServices.SimpleService import SimpleService
 
 ORDER = []
@@ -48,6 +55,9 @@ class Service(SimpleService):
         if not HAS_REQUESTS:
             self.warn('requests library could not be imported')
 
+        if not HAS_SQLALCHEMY:
+            self.warn('sqlalchemy library could not be imported')
+
         if not self.chart_configs:
             self.error('chart_configs must be defined')
 
diff --git a/collectors/python.d.plugin/pandas/pandas.conf b/collectors/python.d.plugin/pandas/pandas.conf
index 6684af9d57..ca523ed36c 100644
--- a/collectors/python.d.plugin/pandas/pandas.conf
+++ b/collectors/python.d.plugin/pandas/pandas.conf
@@ -188,4 +188,26 @@ update_every: 5
 #         df_steps: >
 #           pd.read_xml('http://metwdb-openaccess.ichec.ie/metno-wdb2ts/locationforecast?lat=54.7210798611;long=-8.7237392806', xpath='./product/time[1]/location/temperature', parser='etree')|
 #           df.rename(columns={'value': 'dublin'})|
-#           df[['dublin']]|
\ No newline at end of file
+#           df[['dublin']]|
+
+# example showing a read_sql from a postgres database using sqlalchemy.
+# note: example assumes a running postgress db on localhost with a netdata users and password netdata.
+# sql:
+#     name: "sql"
+#     update_every: 5
+#     chart_configs:
+#       - name: "sql"
+#         title: "SQL Example"
+#         family: "sql.example"
+#         context: "example"
+#         type: "line"
+#         units: "percent"
+#         df_steps: >
+#           pd.read_sql_query(
+#             sql='\
+#                 select \
+#                     random()*100 as metric_1, \
+#                     random()*100 as metric_2 \
+#                ',
+#             con=create_engine('postgresql://localhost/postgres?user=netdata&password=netdata')
+#             );