Re: [SQLObject] Altering/updating a database

[Imri G. <lor...@gm...>]

Hi
I've had the same problem some time ago, however I was (and still am) using
mysql, so YMMV.
I asked a similar question on this mailing list, and Oleg gave an example of
what he was using.
What I did was adapt Oleg's code to some "generic" db updating code:
(this might need further adaptation to your needs).

g_used_classnums = 0

def col_params(*args, **kwargs):
    return (args, kwargs)

def get_table_class_for_name(python_name, style = None):
    global g_used_classnums
    if style is None:
        style = sqlobject.DefaultStyle()

    table_name = python_name

    s = '''
class Temp%d(model.SQLObject):
    class sqlmeta:
        table = style.pythonClassToDBTable(table_name)
        fromDatabase = True''' % g_used_classnums
    g = globals().copy()
    g['style'] = style
    g['table_name'] = table_name
    exec s in g, locals()
    temp_class = locals()['Temp%d' % g_used_classnums]
    g_used_classnums += 1
    return temp_class

def add_column(table_name, col_name, col_type, col_params, style = None):
    if style is None:
        style = sqlobject.DefaultStyle()
    temp_class = get_table_class_for_name(table_name, style)

    db_col_name = style.pythonAttrToDBColumn(col_name)
    py_col_name = style.dbColumnToPythonAttr(db_col_name)

    args, kwargs = col_params
    if 'dbName' not in kwargs:
        kwargs['dbName'] = db_col_name
    if py_col_name in temp_class.sqlmeta.columns:
        return

    if col_type == sqlobject.ForeignKey:
        if py_col_name + 'ID' in temp_class.sqlmeta.columns:
            return
        col = col_type(*args, **kwargs)
    else:
        col = col_type(col_name, *args, **kwargs)
    print ('adding column "%s"... ' % col_name),
    temp_class.sqlmeta.addColumn(col, changeSchema = True)
    print 'done.'

I also have other similar functions: del_column, del_table, add_table,
add_all_table_indexes, table_exists & table_has_column

If there is interest I will publish here the the full code.

Cheers,
Imri

PS lately I've been thinking of writing add_column in a smarter fashion,
it's quite doable. It can discover automatically the missing columns and
their properties and add them. But this is a subject for another discussion
:)

On Thu, Jan 13, 2011 at 11:36 PM, Timothy W. Grove <tim...@si...>wrote:

> Hi folks,
>
> I have an application which I am developing under Windows7, python2.7
> and wxpython2.9. The application uses sqlobject-0.13.0-py2.7 with an
> sqlite database.
>
> 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 ? I haven't found it too
> difficult to update an existing database with a completely new table,
> but adding a new column to an existing table is causing me some
> headaches. I haven't said much specifically about the application, but
> was wondering if there was a generally accepted "best" approach to this
> problem? Thanks.
>
> Best regards,
> Tim
>
>
> ------------------------------------------------------------------------------
> Protect Your Site and Customers from Malware Attacks
> Learn about various malware tactics and how to avoid them. Understand
> malware threats, the impact they can have on your business, and how you
> can protect your company and customers by using code signing.
> http://p.sf.net/sfu/oracle-sfdevnl
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>



-- 
Imri Goldberg
--------------------------------------
http://plnnr.com/ - automatic trip planning
http://www.algorithm.co.il/blogs/
--------------------------------------
-- insert signature here ----