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.

Loading IP Test Data Into Postgres

Recently, I was trolling around the internet looking for some IP address data to play with.  Fortunately, I stumbled across MaxMind's Geolite Database, which is available for free.    All I have to do is include this notice:

This product includes GeoLite2 data created by MaxMind, available from <a href="http://www.maxmind.com">http://www.maxmind.com</a>.

That's easy enough.  The next question is how to get this into my local Postgres database.  A bit over a year ago, I very happily gave up on my Dell computer and opted for a Mac.  One downside to a Mac is that SQL Server doesn't run on it (obviously my personal opinion).  Happily, Postgres does and it is extremely easy to install by going to postgresapp.com.   An interface similar enough to SQL Server Management Studio (called pgadmin3) is almost as easy to install by going here.

So, the next problem is getting the MaxMind data into Postgres.  Getting the two tables into Postgres is easy, using the copy command.  The challenge is IPV6 versus IPV4 addresses.  The data is in IPV6 format with a subnet mask to represent ranges.  Most of us who are familiar with IP addresses are familiar with IPV4 addresses.  These are 32 bits and look something like this:  173.194.121.17 (this happens to be an address for www.google.com attained by running ping www.google.com in a terminal window).  Alas, the data from MaxMind uses IPV6 values rather than IPV4.

In IPV6, the above would look like: ::ffff:173.194.121.17 (to be honest, this is a hybrid format for representing IPV4 addresses in IPV6 address space).  And the situation is a bit worse, because these records contain address ranges.  So the address range is really:  ::ffff:173.194.0.0/112.

The "112" is called a subnet mask.  And, IPV4 also uses them.  In IPV4, they represent the initial range of digits, so they range from 1-32, with a number like "24" being very common.  "112" doesn't make sense in a 32-bit addressing scheme.   To fast forward, the "112" subnet mask for IPV6 corresponds to 16 in the IPV4 world.  This means that the first 16 bits are for the main network and the last 16 bits are for the subnet.  That is, the addresses range from 173.194.0.0 to 173.194.255.255.  The relationship between the subnet mask for IPV6 and IPV4 is easy to express:  the IPV4 subnet mask is the IPV6 subnet mask minus 96.

I have to credit this blog for helping me understand this, even though it doesn't give the exact formula.  Here, I am going to shamelessly reproduce a figure from that blog (along with its original attribution):

Courtesy of ls-a.org

This figure says  the following.  The last 64 bits are new to IPV6, so they can be automatically subtracted out of the subnet mask.  Then, bits 0-32 also seem to be new, so they can also be subtracted out.  That totals 96 bits in the new version not in the old version.  To be honest, I am not 100% positive about my interpretation.  But it does seem to work.  Google does indeed own exactly this address range.

The Postgres code for creating the table then goes as follows:

create table ipcity_staging (
    network_start_ip varchar(255),
    network_mask_length int,
    geoname_id int,
    registered_country_geoname_id int,
    represented_country_geoname_id int,
    postal_code varchar(255),
    latitude decimal(15, 10),
    longitude decimal(15, 10),
    is_anonymous_proxy int,
    is_satellite_provider int
);

copy public.ipcity_staging
    from '...data/MaxMind IP/GeoLite2-City-CSV_20140401/GeoLite2-City-Blocks.csv'
    with CSV HEADER;

create table ipcity (
    IPCityId serial not null,
    IPStart int not null,
    IPEnd int not null,
    IPStartStr varchar(255) not null,
    IPEndStr varchar(255) not null,
    GeoNameId int,
    GeoNameId_RegisteredCountry int,
    GeoNameId_RepresentedCountry int,
    PostalCode varchar(255),
    Latitude decimal(15, 10),
    Longitude decimal(15, 10),
    IsAnonymousProxy int,
    IsSatelliteProvider int,
    unique (IPStart, IPEnd),
    unique (IPStartStr, IPEndStr)
);

insert into ipcity(IPStart, IPEnd, IPStartStr, IPEndStr, GeoNameId, GeoNameId_RegisteredCountry, GeoNameId_RepresentedCountry,
                   PostalCode, Latitude, Longitude, IsAnonymousProxy, IsSatelliteProvider
                  ) 
    select IPStart, IPEnd, IPStartStr, IPEndStr, GeoName_Id, registered_country_geoname_id, represented_country_geoname_id,
           Postal_Code, Latitude, Longitude, Is_Anonymous_Proxy, Is_Satellite_Provider
    from (select network_mask_length - 96,
                 hostmask(inet (substr(network_start_ip, 8) || '/' || network_mask_length - 96)) ,
                 inet(host(inet (substr(network_start_ip, 8) || '/' || network_mask_length - 96) )) |
                 hostmask(inet (substr(network_start_ip, 8) || '/' || network_mask_length - 96)
                ) as ipend_inet,
                substr(network_start_ip, 8) || '/' || network_mask_length - 96,
                ((split_part(IPStartStr, '.', 1)::int << 24) +
                 (split_part(IPStartStr, '.', 2)::int << 16) +
                 (split_part(IPStartStr, '.', 3)::int << 8) +
                 (split_part(IPStartStr, '.', 4)::int)
                ) as IPStart,
                ((split_part(IPEndStr, '.', 1)::int << 24) +
                 (split_part(IPEndStr, '.', 2)::int << 16) +
                 (split_part(IPEndStr, '.', 3)::int << 8) +
                 (split_part(IPEndStr, '.', 4)::int)
                ) as IPEnd,
                st.*
          from (select st.*,
                       host(inet (substr(network_start_ip, 8) || '/' || network_mask_length - 96)) as IPStartStr,
                       host(inet(host(inet (substr(network_start_ip, 8) || '/' || network_mask_length - 96) )) |
                            hostmask(inet (substr(network_start_ip, 8) || '/' || network_mask_length - 96))
                           ) as IPEndStr
                from ipcity_staging st 
                where network_start_ip like '::ffff:%'
               ) st
         ) st;