[SOLVED]: How to query a Postgres DB -> value in variable

[DrLaban]

Hi,

I have searched the forum and google for several hours now

I got a postgres db up running on my indigo server. I have made the SQL query and are able to pull out the value that I want using a Postgres Client outside Indigo.

Could someone help me with the script that pulls the result from the query and populates a variable?
I'm not that familiar to Python or Applescript. So I need a pretty A to Z guide I'm affraid

The postgres server is running locally on the indigo server.

Regards
Thomas

[DaveL17]

Here is an example script, but it requires an external library which does not ship with OSX (called psycopg2) that will need to be installed in order for this to work. I don't know how to do what you want to do with bone-stock Python, unfortunately. If anyone else knows, please feel free to modify my example.

Code: Select all

import psycopg2

try:
	conn = psycopg2.connect(database="indigo_history", user="postgres", password="", host="127.0.0.1", port="5432")
	cursor = conn.cursor()
	cursor.execute("SELECT pressure FROM device_history_1899035475 ORDER BY ts DESC")
	rows = cursor.fetchall()
except:
	indigo.server.log(u"Unable to open database.")

foo = str(rows[0][0])
indigo.variable.updateValue(296635249, foo)

The above code assumes the default settings of the Indigo SQL Logger Plugin and creates a list where 'pressure' is the device state named 'pressure' from device number '1899035475'. The list in this example is called 'rows' and contains all of the values that Indigo has saved to the database for that device state. In addition, the script sorts the data in 'rows' to have the most recent entry in the list appear first. That way we know where the most recent value is located in our code. We then extract the most recent value from the data we've just pulled into the temporary variable named 'foo'. To be safe, we convert it to a string by using the str() method and we pull the first element from our list 'rows' which is located at position [0][0] (in Python the first thing in a list is the zeroth thing.) You don't have to use this step (saving the value to an interim variable), but it's common form to do it that way in Python. We then finally save the value of 'foo' to the Indigo variable '296635249'. Of course, your device number and state as well as variable number will all be different.

There's a lot going on here I realize and unfortunately, this is about as simple as I know how to make it. The code is tested and does work on my Indigo server. Since you are able to pull the value you want, you may only need the last part of the code above (and perhaps modify your 'SELECT' statement to match this example.) Please feel free to ask questions if you have them.

Cheers,
Dave

[DrLaban]

Awsome! Thanks Dave. I will test it when I get home after work.

[DaveL17]

Awsome! Thanks Dave. I will test it when I get home after work.

Good luck and don't be shy if you have any questions.
Dave

[DrLaban]

I have installed psycopg2 using FINK on OSX 10.9

But I get this error running the script:
Embedded script executor host started
Script Error embedded script: No module named psycopg2
Script Error Exception Traceback (most recent call shown last):

embedded script, line 1, at top level
ImportError: No module named psycopg2

Any tips?

[DaveL17]

I have installed psycopg2 using FINK on OSX 10.9

But I get this error running the script:
Embedded script executor host started
Script Error embedded script: No module named psycopg2
Script Error Exception Traceback (most recent call shown last):

embedded script, line 1, at top level
ImportError: No module named psycopg2

Any tips?

I don't know FINK, but I'm actually struggling with a similar issue to this with another package myself. It gets complicated quickly.

For starters, open a terminal window and start Python 2.6 by typing "python2.6' at the command prompt (this test presumes that you're using Indigo 6.1):

Code: Select all

Last login: Sat May  9 11:33:40 on ttys000
COMPUTERNAME:~ USERNAME$ python2.6
Python 2.6.9 (unknown, Sep 20 2014, 19:55:43) 
[GCC 4.2.1 Compatible Apple LLVM 5.1 (clang-503.0.40)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> 

Then at the Python command prompt, type this:

Code: Select all

>>> import psycopg2

Do you get an error when you do this? If yes, it could be that you installed psycopg2 against the default Python installation on your machine (which is Python 2.7 on mine) and not the one that Indigo uses.

Dave

[DrLaban]

I get this:

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
ImportError: No module named psycopg2
>>>

If I just start python with the command "python" (and not python2.6" I do not get an error message...

[DaveL17]

I get this:

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
ImportError: No module named psycopg2
>>>

If I just start python with the command "python" (and not python2.6" I do not get an error message...

That tells me that the psycopg2 module is not installed against the default Python that Indigo uses.

Your server likely has several versions of Python installed. My server has:

• Python 2.5 (default for Indigo 6.0)
  Python 2.6 (default for Indigo 6.1)
  Python 2.7 (default for OS X terminal shell)

At the shell prompt, if I type "Python" I get 2.7. If I install something with no instructions, it gets installed against 2.7. I have to expressly tell it to install against Python 2.6. I suspect that this is what has happened in your case.

I'm not familiar with FINK, but you should look to see if you can direct it to a particular Python version.

[DrLaban]

I uninstalled the version installed by fink and used macports instead. Macports did have a python2.6 version of psycopg2.

Now I do not get an error message running the "python2.6" command

indigo:/ thomas$ python2.6
Python 2.6.9 (unknown, Sep 20 2014, 19:55:43)
[GCC 4.2.1 Compatible Apple LLVM 5.1 (clang-503.0.40)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg2
>>>


But I still got the module not found running the script in indigo... strange.

[DrLaban]

How did you install psycopg2, Dave? Compiling it manually or through any scripts/packages?

[DaveL17]

I used pip.

Code: Select all

sudo pip2.6 install psycopg2

[DrLaban]

Did you also install the Postgres server using PIP?

Also did you install postgres and psycopg2 before installing Indigo?
I think maybe I have installed things in the wrong order...

[DaveL17]

I honestly don't remember installing Postgres--but that doesn't mean that I didn't. If the installation order matters (although I'm not sure that it does), I would've installed them in this order:

1. SQL Logger plugin. I ran this plugin using SQLite for some time before switching to Postgres.
2. Postgres. If I installed it myself, I would've installed this second for the switch.
3. Psycopg2. I didn't install this until I was interested in querying the Postgres database directly.

Dave

[DrLaban]

I think I have screwed up my python install after all this testing. I'm going to backup my Indigo config and do a fresh install of the OS.

If I start the scripting shell through the Indigo menu, I now get this in the LOG

Interactive Shell Error Error in plugin execution InitializeMain:

Traceback (most recent call last):
File "/Library/Application Support/Perceptive Automation/Indigo 6/IndigoPluginHost.app/Contents/PlugIns/plugin_base.py", line 9, in <module>
<type 'exceptions.ImportError'>: dlopen(/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/lib-dynload/select.so, 2): no suitable image found. Did find:
/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/lib-dynload/select.so: mach-o, but wrong architecture

[DaveL17]

I'm getting an error in the same family with Pillow that I haven't been able to correct (although I haven't given up yet.)

Code: Select all

Script Error                    embedded script: dlopen(/Library/Python/2.6/site-packages/PIL/_imaging.so, 2): Symbol not found: _jpeg_resync_to_restart
  Referenced from: /Library/Python/2.6/site-packages/PIL/_imaging.so
  Expected in: flat namespace
 in /Library/Python/2.6/site-packages/PIL/_imaging.so
  Script Error                    Exception Traceback (most recent call shown last):

     embedded script, line 1, at top level
     File "/Library/Python/2.6/site-packages/PIL/Image.py", line 63, in <module>
       from PIL import _imaging as core
ImportError: dlopen(/Library/Python/2.6/site-packages/PIL/_imaging.so, 2): Symbol not found: _jpeg_resync_to_restart
  Referenced from: /Library/Python/2.6/site-packages/PIL/_imaging.so
  Expected in: flat namespace
 in /Library/Python/2.6/site-packages/PIL/_imaging.so

In the interim, what I have been doing is running my script on its own by using:

Code: Select all

from os import system

system('python2.6 /Library/Application\ Support/Perceptive\ Automation/Indigo\ 6/Scripts/imageDraw.py')

Which may or may not work for you depending on what you're trying to do.

Good luck!
Dave