Database access via Python

In my ongoing quest to do absolutely everything through Python, I’ve been looking a lot lately at manipulating databases. I’ve been using arcpy to access GIS databases for years, and last year I finally got around to using pyodbc (and pypyodbc) for accessing SQL Server databases.

Now that I’m in an Oracle environment, Oracle has provided the cx_Oracle library to directly connect to databases. I have yet to test that though. What I’m interested in at the moment is creating and accessing databases for personal use.

I considered MongoDB for a while, but I don’t think I want to go NoSQL yet. This is why I have been experimenting with SQLite (through the sqlite3 library), as it is included in the Python install, and has the delightful SpatiaLite extension. The slogan goes against my one of my mottos (Spatial is Special) while supporting my other motto (Everything is Spatial).

INF3707 Advanced Database Development :: A strategy.

I’ve already mentioned previously that I am taking two modules this semester as part of my prep for Hons. I decided to tackle the assignments for this module first because while I am familiar with SQL Server and T-SQL, I have never delved into the Oracle side of things. Unless one counts Java, and since I’ve long since repressed the the three years I spent with it, I don’t.

Right off the bat, this module appears quirky. We’re using Oracle Express 10g, which a quick Google search and multiple forum posts on myUnisa confirm is a long outdated version of Oracle’s free database version. Also, it’s only available in 32-bit, so it absolutely refused to install on my 64-bit Windows 8.1 VM.

I tried the workarounds Google suggested, such as running in compatibility mode for XP SP3 as admin, and editing some registry key after installation. Nothing happened when I tried the first option, thereby rendering the second option moot.

After following some frustrated ramblings on myUnisa, I just installed the 11g version with no problems. Once I had it installed, and unlocked the default user and tables, I was ready to do the assignment.

The structure of SQL in Oracle is a little bit different to T-SQL, but thankfully not too much. We’re using the actual Oracle University manual as a textbook, so luckily the notes are quite detailed and specific to Oracle. The first assignment did give a taste of what the exam would be like though, and since that counts 80% of the module mark, I’m aiming to complete the written assignment next week so that I can start studying for the exam. Which is 3 months away, yes, but my paranoia is at an all-time high.

It’s also not good enough to just pass the module (or in my case, to get that 60%). I have to go all in, otherwise what is the point? To end this post, I will leave a quote from a thread on the forum:

Written by a guy, if that wasn’t obvious from the dripping sarcasm.