postgres SQL Python Pandas

user_8372455 0
Viewed 38

I have an SQL query that works through a Grafana dashboard is this possible to recreate in Pandas?

In Grafana:

SELECT
  "time" AS "time",
  metric AS metric,
  value
FROM volttron
WHERE
  $__timeFilter("time") AND
  kv_tags->'equip_name' = '["35201"]' AND
  'power' = any(m_tags)
ORDER BY 1,2

Trying to recreate in Pandas postgress connection with psycopg2:

eGauge35201 = pd.read_sql('SELECT "time" AS "time", metric AS metric, value FROM volttron WHERE $__timeFilter("time") AND kv_tags->equip_name = ["35201"] AND power = any(m_tags) ORDER BY 1,2', dbconn)

This throws a lot of errors:

DatabaseError: Execution failed on sql 'SELECT "time" AS "time", metric AS metric, value FROM volttron WHERE $__timeFilter("time") AND kv_tags->equip_name = ["35201"] AND power = any(m_tags) ORDER BY 1,2': syntax error at or near "$"
LINE 1: ...c AS metric, value FROM slipstream_volttron WHERE $__timeFil...

Im trying to build a dataframe directly... Sorry still learning db any tips greatly appreciated...

pandas postgresql python
1 Answers

Not using Grafana, I'm pretty sure that $__timeFilter and m_tags is something that should come from Grafana's end, and is then replaced by proper PostgreSQL expressions when when the query is actually made to the database after you defined it.

The query also uses several protected SQL words like "time" which is correctly escaped, and value, which isn't. This can lead to some unwanted behaviour.

I would recreate this query for Pandas to understand properly. But without knowing what e.g. $__timeFilter is replaced with, we cannot know what it should be. You could e.g. monitor Grafana's or the database's logs.

From a quick Google search, this looks promising.

user_98017800
Related