Sunday, May 31, 2015

Connecting Python to Postgres . . . An Arduous Task

I've decided that I want to learn a bit more about Python.  So, I downloaded Python onto my Mac running OS 10.8.x and decided to connect it to my local Postgres database.  Several days later, I have succeeded! Along the way, I may have learned a thing or two that would help others.  Hence, this description of my experience and my solutions.

Mac OS X comes with Python 2.7.  However, I figured that I like parentheses errors when I print, so I would go with the latest and greatest:  python 3.4.  For those who do not know, Python changed some of its syntactic conventions between versions 2 and 3.  A very notable one is that the commonly used print command is now a function and requires parentheses.  Previously, parentheses were not allowed.

An overview of the steps is basically:
  • Download Python 3.4 (easy).  Postgres was already installed.
  • Attempt to install psycopg2, the package that links Python to Postgres.
  • Attempt to install XCode
  • Attempt to install OS X 10.10
  • Attempt to install psycopg2
  • Finally, some success!
The script that I was attempting to run is rather easy:

#!/Applications/Python 3.4
#
# Small script to show PostgreSQL and Pyscopg together
#

import psycopg2

try:
    conn = psycopg2.connect("dbname='' user='' host='localhost' password=''")
    cur = conn.cursor()
    cur.execute("select count(*) from products")
    print(cur.fetchone())
    cur.close()
    conn.close()
except:
    print("Unexpected Error:", sys.exc_info()[0])

I counts the number of product in a table used for my book Data Analysis Using SQL and Excel.

What i encountered.

Getting and installing the latest version of Python is really easy.  Just go here.  (If the link doesn't work, it is easy to find with Google.)

Getting psycopg2 is really easy.  Just go here.  (If the link doesn't work, it is easy to find with Google.)

Installing psycopg2 should also be easy:

pip3 install psycopg2

But, this is where the trouble began.  The simplicity of installing Python lures you into an automated slumber, thinking "all this should work really easily".  For some reason, this installation requires the ability to compile code.  Fortunately, Apple offers a free package that has this and many other capabilities, located right here.  Alas, this download did not work for the Mac OS version I had.  And, Apple didn't seem to offer a version compatible with slightly out-of-date operating systems.

So, I bit the bullet to upgrade the operating system.  After all, my Mac has been asking about this for a long time.  I did some preliminary research on potential problems.  So far, the only issue upgrading to Mac OS 10.10 is that the OS forgot my network passwords.  That is a pain, because I don't really remember all of them, but a bearable pain.  An upgrade site is here.

Upgrading the operating system should be free.  I had problems with my AppleId, and had to verify my billing information.  So, I think it is free.  Eventually it all came through -- 5+ Gbytes worth.  Even on Verizon FIOS, that took several hours.  I remembered to change my Energy Saver Options so the computer would never turn off.  I don't know if this is necessary, but who wants to risk a partial download, even if the download can be restarted.

Once I downloaded the software (and had a nice dinner with a bit of wine, returning home late in the evening), I thought merely rebooting the computer would result in the new OS.   Nope.  I finally realized that I had to go back to the Apple Store and click the "update" button.  Then after a longish reboot (half an hour), the new operating system came up, and I was pretty soundly asleep by then.

With the new operating system, I could then load XCode.  That is another 2.5 Gbytes.  It too needs to be installed (I'm getting better at this).  I don't think the machine needed to be rebooted.

So, back to the simple code:

pip3 install psycopg2

And it failed.  This time with an error message saying that it could not find setup.py.  I searched my disk and I searched the web.  No such luck, although this seems like an important file.  Finally, I came across this very useful post, suggesting:

sudo PATH=$PATH:/Applications/Postgres.app/Contents/Versions/9.3/bin pip install psycopg2

I skeptically checked my path to Postgres.  I realized that I need pip3, changing the command to:


sudo PATH=$PATH:/Applications/Postgres.app/Contents/Versions/9.3/bin pip3 install psycopg2

And it worked!  Note that the "2" in psycopg2 refers to the version of this package, not to the version of Python.  As I write this, there is no psycopg3.

Note I have upvoted this answer on StackOverflow, as one should for super-helpful answers.

Then, I went back to Python to run my script.  This time, instead of saying that the psycopg2 was not installed, I got an error saying the connection failed.  Oh, what happiness!  A simple database error.

So, I started Postgres with:

/Applications/Postgres.app/Contents/Versions/9.3/bin/pg_ctl -D /Users/gordonlinoff/Library/Application\ Support/Postgres/var-9.3 start

And I fixed the database name, user name, and password in the connection script.  And, lo and behold, I got my answer:  4040.