Re: [SQLObject] Altering/updating a database

[Timothy W. G. <tim...@si...>]

On 13/01/2011 10:12 PM, Oleg Broytman wrote:
> On Thu, Jan 13, 2011 at 09:36:03PM +0000, Timothy W. Grove wrote:
>> I want to update an existing database by adding a new column
>> (UnicodeCol) to an existing table. Can anyone suggest a way to
>> automatically accomplish this through sqlobject ?
>     It could be as simple as
>
> Table.sqlmeta.addColumn(UnicodeCol('name'), changeSchema=True)
>
> Oleg.
This has been working for me, but I've come across a problem in my 
approach to using it; perhaps someone can suggest an alternative?

When I first start my application and connect (sqlhub.processConnection) 
to a database (SQLite) I run the following function to update it:

    def SignEntryUpdate():
         try:
             SignEntry.sqlmeta.addColumn(PickleCol("explanatory_map"),
    changeSchema=True)
         except:
             pass #database already has this column

The first time I connect to a database which requires updating all is 
fine; the column is added and I can access it through the class with the 
SignEntry instance attribute 'explanatory_map'. But after the 
application is closed and I open it again connecting to the updated 
database, trying to access the column through the class just leads to 
the AttributeError: 'SignEntry' object has no attribute 
'explanatory_map'. But of course this would happen as it was through the 
updating process that the attribute was added, it would not be added if 
the database didn't need updating.

If I kept the attribute as part of the class definition as in the 
following code, then the update process fails and the new column is not 
added:

    class SignEntry(SQLObject):
         explanatory_map = PickleCol()

It seems to me that there should be a simple way to add the attribute to 
the class if the column has been added already, or not if it has been 
added through updating; the "how to" do that is escaping me at the 
moment. Any and all suggestions welcomed.

I've also experimented with reading the class description from the 
database using "class sqlmeta: fromDatabase = True" but I don't think 
this works with the sqlite backend. The "sqlobject-admin" tool also 
looks promising as a way to update databases, but I'm not too sure how 
far this has been developed after reading the documentation at 
http://sqlobject.org/sqlobject-admin.html ?

Thanks for any help you can give.

Best regards,
Tim

p.s. by the way, I had been using version 0.13 of sqlobject as I 
couldn't find an installer for 0.15 under python 2.7, but I have since 
downloaded and manually installed the version for 2.6 from 
http://pypi.python.org/pypi/SQLObject/0.15.0 and it seems to be working 
fine for me, under Windows 7.