sqlobject-discuss

[SQLObject] removing a unique constraint

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

I have a table in an sqlite database that was created using the 
following class:

class Sentence(SQLObject):
     video = UnicodeCol(alternateID=True, default="", unique=True)
     lang1 = UnicodeCol(default="", unique=True)
     lang2 = UnicodeCol(default="")

I now would like to remove the unique constraint on the 'lang1' column. 
How would I do this? Thanks.

Best regards,
Tim

Re: [SQLObject] removing a unique constraint

[Oleg B. <ph...@ph...>]

On Tue, Jul 12, 2011 at 12:05:40AM +0100, Timothy W. Grove wrote:
> I now would like to remove the unique constraint on the 'lang1' column. 
> How would I do this? Thanks.

   SQLObject can only add/drop column, explicit indices and joins.
Unique constraint creates an implicit index which SQLObject doesn't know
about and cannot drop. You are to drop it yourself - list indices for
your table and issue DROP INDEX.

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] removing a unique constraint

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

Thanks for your advice, Oleg, but could you help me out just a little 
bit more? By 'You must drop it yourself', are you meaning that I need to 
go back to using plain old SQL? (I haven't had to do that for awhile 
thanks to SQLObject!!!) Can I issue SQL commands to my database through 
SQLObject? Thanks again for your help.

Best regards,
Tim

On 12/07/2011 11:23, Oleg Broytman wrote:
> On Tue, Jul 12, 2011 at 12:05:40AM +0100, Timothy W. Grove wrote:
>> I now would like to remove the unique constraint on the 'lang1' column.
>> How would I do this? Thanks.
>     SQLObject can only add/drop column, explicit indices and joins.
> Unique constraint creates an implicit index which SQLObject doesn't know
> about and cannot drop. You are to drop it yourself - list indices for
> your table and issue DROP INDEX.
>
> Oleg.

Re: [SQLObject] removing a unique constraint

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

Thanks. That all sounds like what I am looking for. I'm using sqlite3 
for my backend (with Windows 7, python 2.7, wxpython 2.9, 
SQLObject-1.0.0-py2.6).

Tim
>     Plain old SQL and perhaps tricky one. Can I ask - what backend
> do you use, MySQL?
>> Can I issue SQL commands to my database through
>> SQLObject? Thanks again for your help.
>     Yes, from a script or even from python interactive prompt. Just open
> your connection the way you do in your programs and call
> connection.query() or connection.queryAll(). Examples:
>
> from sqlobject import connectionForURI
> connection = connectionForURI('mysql://...')
> print connection.queryAll('SHOW INDEX FOR sentence')
> connection.query('DROP INDEX sentence_video_unique')
>
> Oleg.

Re: [SQLObject] removing a unique constraint

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

Thanks again for your time and help. I've been able to identify the 
index, but it doesn't appear that sqlite will allow such an index to be 
dropped:

raise OperationalError(ErrorMessage(e))
sqlobject.dberrors.OperationalError: index associated with UNIQUE or 
PRIMARY KEY constraint cannot be dropped

Reading further, it looks like I'll have to do things the hard way:
http://stackoverflow.com/questions/1884787/how-do-i-drop-a-constraint-from-a-sqlite-3-6-21-table

Best regards,
Tim

On 13/07/2011 02:01, Oleg Broytman wrote:
> On Wed, Jul 13, 2011 at 01:17:42AM +0100, Timothy W. Grove wrote:
>> Thanks. That all sounds like what I am looking for. I'm using sqlite3
>> for my backend (with Windows 7, python 2.7, wxpython 2.9,
>> SQLObject-1.0.0-py2.6).
>     For SQLite the query is
>
> SELECT * FROM sqlite_master WHERE type='index'
>
>     You can use console program sqlite3.exe to connect to your database.
> Or you can use GUI program SQLite Database Browser:
> http://sqlitebrowser.sourceforge.net/
>
> Oleg.

Re: [SQLObject] removing a unique constraint

[Oleg B. <ph...@ph...>]

On Wed, Jul 13, 2011 at 02:24:48AM +0100, Timothy W. Grove wrote:
> Thanks again for your time and help. I've been able to identify the 
> index, but it doesn't appear that sqlite will allow such an index to be 
> dropped:
> 
> raise OperationalError(ErrorMessage(e))
> sqlobject.dberrors.OperationalError: index associated with UNIQUE or 
> PRIMARY KEY constraint cannot be dropped
> 
> Reading further, it looks like I'll have to do things the hard way:
> http://stackoverflow.com/questions/1884787/how-do-i-drop-a-constraint-from-a-sqlite-3-6-21-table

   That's what I meant when I said "tricky SQL".

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] removing a unique constraint

[Oleg B. <ph...@ph...>]

On Tue, Jul 12, 2011 at 10:45:56PM +0100, Timothy W. Grove wrote:
> Thanks for your advice, Oleg, but could you help me out just a little 
> bit more? By 'You must drop it yourself', are you meaning that I need to 
> go back to using plain old SQL? (I haven't had to do that for awhile 
> thanks to SQLObject!!!)

   Yes, plain old SQL. SQLObject doesn't map any Python operation to SQL
or any SQL to Python (no ORM does due to impedance mismatch). SQLObject
maps only limited subsets of operations, and the area of schema changes
is very limited.
   Plain old SQL and perhaps tricky one. Can I ask - what backend
do you use, MySQL?

> Can I issue SQL commands to my database through 
> SQLObject? Thanks again for your help.

   Yes, from a script or even from python interactive prompt. Just open
your connection the way you do in your programs and call
connection.query() or connection.queryAll(). Examples:

from sqlobject import connectionForURI
connection = connectionForURI('mysql://...')
print connection.queryAll('SHOW INDEX FOR sentence')
connection.query('DROP INDEX sentence_video_unique')

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] removing a unique constraint

[Oleg B. <ph...@ph...>]

On Wed, Jul 13, 2011 at 01:17:42AM +0100, Timothy W. Grove wrote:
> Thanks. That all sounds like what I am looking for. I'm using sqlite3 
> for my backend (with Windows 7, python 2.7, wxpython 2.9, 
> SQLObject-1.0.0-py2.6).

   For SQLite the query is

SELECT * FROM sqlite_master WHERE type='index'

   You can use console program sqlite3.exe to connect to your database.
Or you can use GUI program SQLite Database Browser:
http://sqlitebrowser.sourceforge.net/

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.