1

I've been going through the sqlobject and sqlbuilder documentation and forums and I cannot seem to grasp the information there.

I have a specific SQL query that I need: 

select distinct author from blogtable where keyword = "dust";

Multiple authors can post about multiple subjects.

The query works on the MySQL database if I use the raw sql query. But I can't seem to understand what I must do to get this correctly working in SQLObject.

I see heaps of references to sqlbuilder, but the manual page is not very extensive. The examples provided in the google groups also talk as if SQLbuilder is the answer, but again, no specific example (for my problem) that I can understand.

Could someone well versed in SQLObject explain to me how I implement the above SQL in SQLObject ? If not possible, can I pass the raw sql in any way via SQLObject to the underlying db ?

Alex Boschmans
  • 515
  • 2
  • 12
  • 2
    I asked the same question on the sqlobject newslist, and got an answer from Oleg, the developer of SQLObject. His answer is the same as below, but explains a bit more about sqlbuilder. http://article.gmane.org/gmane.comp.python.sqlobject/10289 – Alex Boschmans Nov 09 '10 at 19:45

1 Answers1

1

I don't have much experience with SQLObject, but from the docs I deduce that it should be something like this:

class Blog(SQLObject):
    class sqlmeta:
        table = 'blogtable'

    author = StringCol()
    keyword = StringCol()

Blog.select(Blog.q.keyword=='dust', distinct=True)

Version 2

select = Select(
    [Blog.q.author],
    Blog.q.keyword=='dust',
    distinct=True,
)

sql = connection.sqlrepr(select)

for author in connection.queryAll(sql):
    print author
Wolph
  • 78,177
  • 11
  • 137
  • 148
  • Thank you for taking the time to respond. While that is certainly valid, in my table an author can discuss many things and more than one author can write about the same keyword -> the above will return a list of all authors while I only want a list of distinct authors. – Alex Boschmans Nov 08 '10 at 21:39
  • 1
    @Alex Boschmans: unfortunately it seems that the `distinct=True` on a full model will only help when you are joining other tables. In this case it seems that the only solution is to select it manually like I did in my updated answer. – Wolph Nov 08 '10 at 23:04
  • 1
    Thanks for your solution - it worked. I also got some more explanation from Oleg (see comment below my question for a link to his reponse on the sqlobject newsgroup). – Alex Boschmans Nov 09 '10 at 19:48