Working with Multiple Databases from Python

6 08 2013

As a database developer, I work with many different databases in the course of a week. Our ERP database is on Oracle and has over 4,000 tables and I spend the bulk of my time working with Oracle. But we also have databases on Postgres, MySQL, MS SQL Server and maybe even some MS Access lurking around. And when I have to script against the databases, or load a one-off spreadsheet the tool I typically reach for is Python.

But even though Python has a nice specification in DB API 2, there is enough variability between the different adapters that you can’t switch from an database adapter that you use all the time to one used infrequently without consulting Google. Is the connection parameter named ‘pass’ or ‘password’, ‘passwd’ or ‘pwd’? What was the MySQL adapter’s name and how many upper case letters were there? And how do you make a DSN for Oracle again?

Additionally, the extra features of each adapter vary quite a bit. PostgreSQL’s psycopg2 adapter has very nice dictionary and namedtuple cursor support. Oracle’s cx_Oracle adapter gives you exactly what the spec mandates, result sets returned as a list of tuples and nothing more.

And because I spend much more time working with Oracle than the others, I set out to level the playing field. So I wrote the Python DBMS (DataBases Made Simpler) module. I wanted memory efficient dictionary-style and namedtuple cursors but I needed to do it in a way that would work with any DB API 2.0 compatible adapter, not just cx_Oracle. And I wanted a uniform connection method regardless of the underlying database adapter. And while I’m at it, I might as well add database inspection so I can see what databases, schemas, tables, views and columns are available.

Because we don’t know what type of connection or cursor object we will be using, I couldn’t just subclass one of the adapter’s connection and cursor classes. But Python makes this very easy to handle using delegation. The connection and cursor objects store a reference to the actual adapter’s objects in _connection and _cursor respectively.

Lets see it in action:

>>> import dbms
>>> db = dbms.OraConnect('myUser', 'myPass', 'myInstance')
>>> cur = db.cursor()
>>> dir(db)
['action', 'autocommit', 'begin', 'cancel', 'changepassword', 'client_identifier',
'clientinfo', 'close', 'commit', 'current_schema', 'cursor', 'dsn', 'encoding',
'inputtypehandler', 'interface', 'maxBytesPerCharacter', 'module',
'nencoding', 'outputtypehandler', 'parmHelp', 'password', 'ping', 'placeholder',
'prepare', 'register', 'rollback', 'server', 'showColumns', 'showDatabases', 'showSchemas',
'showServerVersion', 'showTables', 'showViews', 'shutdown', 'startup', 'stmtcachesize',
'subscribe', 'tnsentry', 'unregister', 'username', 'version']
>>> db.interface.__name__

The call to dbms.OraConnect imports the adapter cx_Oracle, sets the environmental variable “NSL_LANG” (something that is easy to forget and causes lots of problems if not set) and returns a Connection object. For the most part it looks and behaves like a typical DB API 2 Connection object.  The first difference is that the Connection object keeps a reference to the adapter in the interface attribute.  This is important when debugging queries and writing dynamic queries.  The other significant difference is that there are several functions to help us find our way around the database.  Lets try them out on a Postgres database

>>> import dbms
>>> pg = dbms.PgConnect('myUser', 'myPass', 'myDatabase')
>>> pg.showServerVersion()
Record1(version='PostgreSQL 9.2.3, compiled by Visual C++ build 1600, 32-bit')
>>> pg.showSchemas()
>>> pg.showTables('information_schema')
Record3(schema='information_schema', table_name='sql_features')
Record3(schema='information_schema', table_name='sql_sizing_profiles')
>>> pg.showViews('information_schema')
Record4(schema='information_schema', name='_pg_foreign_data_wrappers')
Record4(schema='information_schema', name='views')
>>> pg.showColumns('information_schema.tables')
['table_catalog', 'table_schema', 'table_name', 'table_type', 'self_referencing_column_name', 'reference_generation', 'user_defined_type_catalog', 'user_defined_type_schema', 'user_defined_type_name', 'is_insertable_into', 'is_typed', 'commit_action']
>>> pg.showParmHelp()
Your adapter's parameter style is "pyformat"
'SELECT * FROM people WHERE last_name = %(name)s AND age > %(age)s', {'name': 'Smith', 'age': 30}
'SELECT * FROM people WHERE last_name = %s AND age > %s', ('Smith', 30)

Easy enough. You can see from the examples above that the results are being returned as namedtuples. Lets take a closer look at the Cursor object. The dbms module defines a memory efficient DictRow type that is a cross between a dict and a namedtuple. It is the default return type for dbms cursors and is subclassed for each recordset.

>>> cur = pg.cursor()
>>> cur.execute('SELECT * FROM information_schema.tables')
>>> row = cur.fetchone()
>>> print row
Record12{'table_catalog': 'pods', 'table_schema': 'pg_catalog', 'table_name': 'pg_type', 'table_type': 'BASE TABLE', 'self_referencing_column_name': None, 'reference_generation': None, 'user_defined_type_catalog': None, 'user_defined_type_schema': None, 'user_defined_type_name': None, 'is_insertable_into': 'YES', 'is_typed': 'NO', 'commit_action': None}
>>> row.table_name
>>> row['table_name']
>>> row[2]
>>> row[0:3]
['pods', 'pg_catalog', 'pg_type']

Frugal Plan

29 10 2012

In my last post, I talked about using an Andriod smartphone as a WiFi device . But that still leaves us carrying another phone so we can talk and text when we don’t have access to WiFi. So lets fix that. In the process, we can dump our expensive phone company, their expensive plans and long-term contracts. Instead, I’m on a $12/mo plan from PagePlus Cellular that more than meets my needs.

The first thing you need to do is find a new provider. There are companies like Cricket that have their own networks and you will have to take care that you can get decent coverage in your area. But you will probably be much better off going with a major network reseller like PagePlus a Verizon reseller or Boost a Sprint reseller. PagePlus seems to have the best deals around, but it is not by any means the only option out there.

Your choice of providers will depend on what type of phone you have. The easiest transition will be to use a Verizon phone on a Verizon reseller or Sprint phone with a Sprint reseller etc. These will be able to be programmed OTA (over the air). The resellers don’t seem to mind if your phone has a bad ESN on the network they are reselling. But I can’t guarantee that will always be the case.

You can put a Sprint phone on a Verizon reseller or vice versa because they are both CDMA networks. But the phones will have to be unlocked and flashed. You can’t mix the two types of technologies CDMA (Verizon, Sprint, MetroPCS) and GSM (AT&T, T-Mobile).

Unlocking and flashing your phone can be  a DIY job if you are technically savvy. But for $30-40, it is probably better left to a technician. Your new provider will probably help you find a service center in your area.

Now we’ve got a phone set up with limited minutes, text and data. The next step is to get unlimited calling and text when we are on WiFi. There are a variety of services that allow you to make low cost VOIP calls to regular phones, including Skype and Tango. But keeping with the frugal theme, we are going to be using Google Voice because it gives us free calling to any number in the US and Canada.

If you have fulfilled your contract with your current cell provider, you can port your current number over to Google Voice. Google will charge you $20 to do this and it will cancel your current cell service. If you are still under contract, you will most likely need to sign up for a new Google Voice number (free). You can then add your new phone number   (from your PagePlus or whoever your new provider is) and optionally your home and work numbers too, to your Google Voice account. Once it is configured you can send and receive calls and text over WiFi when it is available and have them routed to your cell number when it is not.

The 10MB of data that comes with the PagePlus plan isn’t going to allow you to jog around the neighborhood listening to Pandora. But it is enough to occasionally check your email or do a few lookups when out and about. But you will have to be sure that you disable mobile data when you aren’t using it. Otherwise apps running in the background will use up all of your data. You can get an app like Quick Settings that lets you easily turn mobile data off and on.

Frugal Fone (or how to get an Android without being extorted)

28 09 2012

I really love the concept of “smart” phones.  But wireless vendors have taken this great idea and used it as an excuse to extort their customers. Recently I had to upgrade my daughter’s broken phone and Verizon gave us a paltry selection of three phones that did not require a data plan. They were ugly and expensive phones at that ($30 was the cheapest) . However they had an excellent selection of “smart” phones, eleven of which were offered free of charge, and all of which REQUIRED an expensive data plan.

“Not a problem,” you say, “I’ll just get a nice smart phone from ebay or some other third party, join it to their network as a regular phone and just not use 3G/4G.  But our gracious providers will detect that it is a smart phone and will automatically enroll you in a data plan. I guess they couldn’t bare the thought of you possibly facing data overage charges. Isn’t that great?

But here’s a revolutionary idea, and the point of this post, get a smart phone and skip the cell provider. Consider how much of your day you spend in WiFi coverage.   If you’re like me, its probably upwards of 90-95% of your day. There are wireless networks everywhere; at home, at work, on campus, at restaurants and hotels.

With WiFi access, I can do pretty much everything on my Frugal Fone; including sending and receiving email, phone calls and text messages (talkatone), video conferencing (skype), watching Netflix and of course web browsing. And even offline it can still do some pretty cool stuff like turn-by-turn GPS navigation (CoPilot), play music and movies, listen to audio books (LibriVox), read eBooks (Moon+ Reader), and play games (Angry Birds of course).

I am assuming you are frugal as well or you wouldn’t still be here, so lets talk dollars and cents. A shiny new Galaxy S III will set you back $200 up front and $720 minimum in data charges over the next two years that you just committed yourself to. (That DOESN’T include the cost of regular cell phone service that will cost you at least $50 a month, $1200 for the contract.) But $920 is really the price tag on that shiny new Android phone from your cell provider. Not at all “smart” in my book.

If we were to buy pieces of Android phone functionality individually it might look something like this:

  • GPS navigation system – $150
  • Camera/Camcorder – $90
  • 32GB MP3/Video Player – $140
  • 5″ eBook Reader – $80
  • Handheld Gaming System – $125

That totals $585 if you were to buy them all. But again, being well beyond the threshold of being frugal, it is unlikely that we would go this route either.

Enter the Frugal Fone, and ebay or craigslist. The truth is you can buy a used Android phone and NOT connect it to your provider’s network. And because we don’t need to connect it to the cell provider, we can buy phones that have been black-listed. When a customer doesn’t fulfill their contract, cell providers will black-list their phone so it can never again join their network. And the major carriers will not allow you to use a phone sold by another carrier on their network. As such, these black-listed phones sell for much less but suit our purposes just fine. You should be able to pick up a nice one for less than it would cost you for a month or two worth of data charges.

Overriding default constructors

11 05 2010

When you create a custom data type in Oracle, it creates a default constructor for you. But you don’t have access to modify this constructor. This can be a problem if you need some error checking or business logic. The documentation only mentions that it is possible to override the default constructor. However, every time I tried to do it I would get an error message complaining that two functions signatures matched this call. And because it didn’t know which function to use, it wouldn’t let me use either.

After reading and rereading the documentation, googling, and asking questions on help forums, I finally discovered the solution by accident. What they don’t tell you is that in order to override the default constructor, you not only have to match the position and type of attributes but also their NAMES. Once I changed the names to match that of the actual attributes, it worked like a charm.

Flex Printing

7 12 2009

You’ve worked for months on your shiny new flex app. It has all the bells and whistles. Incredible data visualization that in just a few clicks delivers interactive graphs allowing the user to navigate to information they never thought possible. The customer is absolutely blown away. Then your phone rings a few days later. There is a problem, your flex app doesn’t print! “Print”, you say, “why on Earth would you want to do that?”

The customer now has a beautiful app that either prints out extremely pixelated and ugly or more often, not at all. And you are left scrambling trying to work around Flex’s Achilles heel, printing. Read the rest of this entry »

Scheduling with Date Mod Part 2

20 10 2009

So as the title suggests, this is the second part of a series on scheduling. In the first part we did some basic setup. Now we are ready to dig in. The goal here is to be able to model any recurring pattern no matter the cycle.

The linchpin in this solution is to define an anchor date and the cycle length for every schedule. Given those two pieces of information, we can calculate the day of cycle for any given date using date mod. So lets create a couple of tables.
Read the rest of this entry »

Scheduling with Date Mod Part 1

19 10 2009

One question that seems to come up a lot is how to model recurring schedules. That’s understandable, because schedules can get out of hand pretty quickly.

We need a template so that we can define a schedule and then some how project it out on a repeating basis. The single week schedules are pretty easy. We just record the day of week and for each schedule and day we look up that day of week to get the shift for that day.
Read the rest of this entry »


Get every new post delivered to your Inbox.