Thread: [SQLObject] Altering/updating a database
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
Menu
▾
▴
sqlobject-discuss
|
From: Timothy W. G. <tim...@si...> - 2011-01-13 21:36:26
|
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 |
|
From: Imri G. <lor...@gm...> - 2011-01-13 21:59:00
|
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 ----
|
|
From: Oleg B. <ph...@ph...> - 2011-01-13 22:12:52
|
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.
--
Oleg Broytman http://phdru.name/ ph...@ph...
Programmers don't die, they just GOSUB without RETURN.
|
|
From: Petr J. <pet...@tp...> - 2011-01-14 00:57:46
|
> It could be as simple as
>
> Table.sqlmeta.addColumn(UnicodeCol('name'), changeSchema=True)
>
>
Yes we are using addColumn method and it works great (Firebird SQL).
The other question still remains: "How to find if the table was updated
already". We are maintaining hundreds of the databases with the same
structure on different machines and we were not sure where the table was
already updated and where not.
Because of that, we were using following (but I do not know if it is the
best way):?
from kinterbasdb import ProgrammingError
try:
Table.select()[0]
except IndexError:
# this means table Table is OK, new column(s) was created already, no
data in the column
pass
except ProgrammingError:
Table.sqlmeta.addColumn(UnicodeCol('name'), changeSchema=True)
Cheers
Petr
|
|
From: Imri G. <lor...@gm...> - 2011-01-14 13:27:53
|
On Fri, Jan 14, 2011 at 2:57 AM, Petr Jakeš <pet...@tp...> wrote: > Yes we are using addColumn method and it works great (Firebird SQL). > > The other question still remains: "How to find if the table was updated > already". We are maintaining hundreds of the databases with the same > structure on different machines and we were not sure where the table was > already updated and where not. > > The code I listed is more complicated, but it's mainly there to achieve a similar goal (it has a few more tricks though). Yours might be better, as a matter of programming philosophy: yours is "it's better to ask forgiveness" while mine is "look before you leap". Cheers, Imri -- Imri Goldberg -------------------------------------- http://plnnr.com/ - automatic trip planning http://www.algorithm.co.il/blogs/ -------------------------------------- -- insert signature here ---- |
|
From: Timothy W. G. <tim...@si...> - 2011-03-04 12:50:17
|
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.
|
|
From: Timothy W. G. <tim...@si...> - 2011-03-05 08:30:13
|
On 04/03/2011 2:00 PM, Oleg Broytman wrote:
> On Fri, Mar 04, 2011 at 12:49:50PM +0000, Timothy W. Grove wrote:
>> 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
> There is no a simple way. You have to create the table class without
> the column, and then look into the database to determine if the column
> is there. You can use the same mechanism SQLObject uses to implement
> fromDatabase: connection.columnsFromSchema(tableName, soClass); the
> result is a list of SOCol instances.
Thanks for the advice, Oleg. I found a solution which may not be
elegant, but it works for me! Instead of trying to work with two
different class definitions I've just kept one which declares the column
I wish to add to the database and run an update function which deletes
the column from the class before adding it again to both the class and
the database. My example follows.
Best regards,
Tim
class SignEntry(SQLObject):
explanatory_map = PickleCol()
def SignEntryUpdate():
cols = [i.name for i in
sqlhub.processConnection.columnsFromSchema("sign_entry", SignEntry)]
if 'explanatoryMap' not in cols:
SignEntry.sqlmeta.delColumn('explanatory_map') # I know
this exists in the class definition
SignEntry.sqlmeta.addColumn(PickleCol("explanatory_map"),
changeSchema=True)
|
|
From: Oleg B. <ph...@ph...> - 2011-03-05 08:50:05
|
On Sat, Mar 05, 2011 at 08:29:54AM +0000, Timothy W. Grove wrote:
> Thanks for the advice, Oleg. I found a solution which may not be
> elegant, but it works for me! Instead of trying to work with two
> different class definitions I've just kept one which declares the column
> I wish to add to the database and run an update function which deletes
> the column from the class before adding it again to both the class and
> the database. My example follows.
Sometimes I do the same.
> Best regards,
> Tim
>
> class SignEntry(SQLObject):
> explanatory_map = PickleCol()
>
> def SignEntryUpdate():
> cols = [i.name for i in
> sqlhub.processConnection.columnsFromSchema("sign_entry", SignEntry)]
> if 'explanatoryMap' not in cols:
> SignEntry.sqlmeta.delColumn('explanatory_map') # I know
> this exists in the class definition
> SignEntry.sqlmeta.addColumn(PickleCol("explanatory_map"),
> changeSchema=True)
Minor optimization in case you are going to test many columns - a set
is perhaps faster for repeated 'in' testing:
cols = set([i.name for i in sqlhub.processConnection.columnsFromSchema("sign_entry", SignEntry)])
Oleg.
--
Oleg Broytman http://phdru.name/ ph...@ph...
Programmers don't die, they just GOSUB without RETURN.
|
|
From: Oleg B. <ph...@ph...> - 2011-03-04 14:00:47
|
On Fri, Mar 04, 2011 at 12:49:50PM +0000, Timothy W. Grove wrote:
> 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
There is no a simple way. You have to create the table class without
the column, and then look into the database to determine if the column
is there. You can use the same mechanism SQLObject uses to implement
fromDatabase: connection.columnsFromSchema(tableName, soClass); the
result is a list of SOCol instances.
> 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.
Well, database introspection can't be precise; for example, SQLObject
cannot determine on per-column basis if you want StringCol or
UnicodeCol. SQLObject's implementation is also quite simple, it cannot
determine ForeignKeys, e.g. But it can get the list of columns with
their types. Do you have problems with that?
Oleg.
--
Oleg Broytman http://phdru.name/ ph...@ph...
Programmers don't die, they just GOSUB without RETURN.
|
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X