sql example code

is there anywhere an example python on how to access and use the sql data?

Are you referring to the SQL Logger plugin's data? If so, send me a PM with your email and I can send some code your way that you can pretty much drop in to a plugin.

Adam

thanks.. did send you an email

I am trying it out as we speak .. and I get

File "/Library/Application Support/Perceptive Automation/Indigo 6/Plugins/testsql.indigoPlugin/Contents/Server Plugin/reqhandler.py", line 20, in <module>
<type 'exceptions.ImportError'>: No module named indigopy

I am including reqhandler.py from the same directory as plugin.py and added dataAccess directory.

could not find anything thats indigopy

any suggestion welcome

Karl

I am trying it out as we speak .. and I get

....
could not find anything thats indigopy

any suggestion welcome

Karl

Should it be indigo.py

That request handler is from an IWS plugin... You'll just want to copy the sample functions into your plugin if you are just testing. The only extra include should be the dataAccess one (I forget the actual name, don't have it with me right at this moment).

You should be OK for testing but a full plugin should likely change the configuration from reading the file directly to using the IOM.

Adam

got that , running now but I don't get a connection

in getDatabaseConnection()

dbConfig = self.readSQLLoggerPreferences(sqlLoggerPrefPath)
gets back: {'dbType': 0, 'dbName': '/Library/Application Support/Perceptive Automation/Indigo 6/Logs/indigo_history.sqlite'}
= does not find/recognize sqlite .. dbType =0?

in a terminal window I can get to sqlite and get data

I have copied from getVariableHistory(self,...) down into my plugin and imported "from dataAccess import indigosql"

thanks

Karl

Update: when I "comment" self._Log(..) i.e. put # in front of it in IndigoSqlite(IndigoSql) it works..

I checked the code: the logfile is handed over as None:

indigosql.IndigoSqlite(dbConfig["dbName"], None, None, None)
and
def __init__(self, sql_lite_db_file, sleepFunc, logFunc, debugLogFunc):
IndigoSql.__init__(self, kDbType_sqlite, sleepFunc, logFunc, debugLogFunc)

is that not correct?

Karl

talking to my self again... that seems to work fine.. anyway

should I replace the log file parameter "None" (2. and 3 None) with "indigo.server.log" in
"indigosql.IndigoSqlite(dbConfig["dbName"], None, None, None)"
so that
"def __init__(self, sql_lite_db_file, sleepFunc, logFunc, debugLogFunc)"
has a log function?

Karl

[matt (support)]

should I replace the log file parameter "None" (2. and 3 None) with "indigo.server.log" in
"indigosql.IndigoSqlite(dbConfig["dbName"], None, None, None)"
so that
"def __init__(self, sql_lite_db_file, sleepFunc, logFunc, debugLogFunc)"
has a log function?

Yes, pass indigo.server.log for logFunc, and optionally pass it for debugLogFunc if you are trying to debug the SQL being sent/received.

thanks that seems to work


on to the next:

the variable query seems to return hex code
[(234, datetime.datetime(2014, 3, 5, 5, 54, 24), u'00:0E:58:41:6C:D6;14:8F:C6:74:87:58;00:1F:5B:30:E9:21;10:9A:DD:60:97:2C'), (233, datetime.datetime(2014, ...

while the device query seems to return numbers as strings:
[(u'False', u'False', u'False', 1, 76473, u'False', 0, u'False', datetime.datetime(2014, 3, 5, 22, 20, 55), u'73 \xb0F', u'False', u'False', u'False', 73, u'73', u'True', u'heat on', u'73 \xb0F', u'80 \xb0F', 80, u'False', u'True', u'False', 73, u'False', u'False', u'auto on'), (u'False', u'False', u'False', 1, 76472, u'False', 0, u'False', ...

although the query seems similar..

and the "same" difference when run in a terminal..

On purpose?

Thanks for all the help

Karl

sorry for the confusion ... the variable had the Mac's MAC address in hex code as value .. and that was returned ..

Ok, sounds like you got it working ok, then? Sorry for the delay, have been in the middle of the Smokey mountains this week, though heading back to "low lands" tomorrow so should be back fully connected in the evening.

Note that MUCH of that code comes from Perceptive Automation's SQL Logger data access code which Matt graciously agreed could be used in plugins (you may have noticed that in the copyright statement at the top). I have refactored some of it a bit but have not yet factored in their recent changes for keyword-based columns. Not likely to affect you in any way but I'll update that code just the same and send you the revisions.

Adam

thanks so much for your help.. i am now in the process of merging the sql data source into indigo plot...

Code: Select all

[['signalstrength','maxhumidity','onoffstate','temperature','brightnesslevel','humiditystatus','mintemperature','ts','lastupdated','humidity','minhumidity','voltage','maxtemperature','id','message','batterylevel','type','display','resetdayvalue'],['integer','integer','bool','integer','integer','text','integer','timestamp default current_timestamp','text','integer','integer','integer','integer','integer primary key','text','integer','integer','text','integer'],[(6, 34, None, 67.799999999999997, None, u'normal', 67.099999999999994, datetime.datetime(2014, 3, 7, 4, 6, 30), u'2014/03/06 22:05:47', 34, 33, None, 69.099999999999994, 139679, None, 10, 1, u'68.0 F / 34%', None), (6, 34, None, 67.799999999999997, None, u'normal', 67.099999999999994, datetime.datetime(2014, 3, 7, 4, 6, 30), u'2014/03/06 22:06:30', 34, 33, None, 69.099999999999994, 139680, None, 10, 1, u'67.8 F / 34%', None), (6, 34, None, 68, None, u'normal', 67.099999999999994, datetime.datetime(2014, 3, 7, 3, 59, 20), u'2014/03/06 21:58:37', 34, 33, None, 69.099999999999994, 139677, None, 10, 1, u'68.2 F / 34%', None), (6, 34, None, 68, None, u'normal', 67.099999999999994, datetime.datetime(2014, 3, 7, 3, 59, 20), u'2014/03/06 21:59:20', 34, 33, None, 69.099999999999994, 139678, None, 10, 1, u'68.0 F / 34%', None), (6, 34, None, 68.200000000000003, None, u'normal', 67.099999999999994, datetime.datetime(2014, 3, 7, 3, 57, 54), u'2014/03/06 21:57:11', 34, 33, None, 69.099999999999994, 139675, None, 10, 1, u'68.0 F / 34%', None), (6, 34, None, 68.200000000000003, None, u'normal', 67.099999999999994, datetime.datetime(2014, 3, 7, 3, 57, 54)...

I get this back from ret= self.getDeviceHistory(theDeviceId)

looks like a list but is just a string? How can I convert this to a list?

do I need to do: eval(ret, {'__builtins__':[]}, {}) or something like that? [EDIT] gives an error message datetime not defined (have included it)

Thanks

Karl