CODEWITHSUNDEEP
pythonsqlregexinfluxdbinfluxdb-python
hk_03
hk_03

Reputation: 311

Regex like syntax in SQL query python

I have some code which I have to write again in order to be production ready. The code is messy and I have a few lines that indicate sql queries, but I can not resolve the exact statements.

qs = 'DEVICENAME1|DEVICENAME2|DEVICENAME3'

qstring='SELECT "lat", "lon", "device", "location" FROM "locations" WHERE ("device" =~ /^('+qs+')/) AND time > now()-60h'

qstring='SELECT "device", "lat" FROM "locations" WHERE ("location"=~ /^'+loc+'$/) AND time > now()-60h'

I would greatly appreciate if someone could resolve the sql query statements for me. To be clear I am interested in these parts: ("device" =~ /^('+qs+')/) ("location"=~ /^'+loc+'$/)

Upvotes: 0

Views: 257

Answers (2)

hk_03
hk_03

Reputation: 311

A complete explanation can be found at the documentation: https://docs.influxdata.com/influxdb/v1.2/query_language/data_exploration/#syntax-15 Influxdb supports Golang's regular expression syntax.

Upvotes: 0

GMB
GMB

Reputation: 222642

Conceptually, "device" =~ /^('+qs+')/ means: check if the device that starts with qs (^ represents the beginning of the string), and "location"=~ /^'+loc+'$/ checks that the location is equal to loc ($ is the end of string).

So, in almost any database, these conditions could be rewritten without the use of regexes:

"device" like ' + qs+ '%  -- influxdb does not support like
"location" = ' + loc + '

Side note: you should be using prepared statements and parameterized queries everywhere, for you security and efficiency. Since you are rewriting legacy code, this is a good spot to do so now.

Upvotes: 1

Related Questions