Your code goes totally against SA. You are manually constructing
entire query when you can let SA do it for you. This is how I would do
it given my current grasp of SA.
# First, assume that your database looks like this
from sqlalchemy import *
engine = create_engine('sqlite://filename=:memory:', echo=True)
document_table = Table('document_table', engine,
Column('id', Integer, primary_key=True),
Column('html_name', Unicode(100)),
Column('section', Unicode(100)),
Column('last_viewed', DateTime))
class Document(object): pass
assign_mapper(Document, document_table)
histogram_table = Table('histogram_table', engine,
Column('id', Integer, primary_key=True),
Column('html_name', Unicode(100)),#,
ForeignKey('document_table.html_name')),
Column('word', Unicode(100)),
Column('num', Integer))
class Histogram(object): pass
assign_mapper(Histogram, histogram_table)
# you would then make your function like this
def database_search(words, selected_section, limit=None):
restricted_section = restrict_section(selected_section)
where = and_(Document.c.html_name==Histogram.c.html_name) # join
if restricted_section:
where.append(Document.c.section==restricted_section) # restrict section
word_list = words.split()
if word_list:
where.append(Histogram.c.word.in_(*word_list)) # find words
return Document.select(where, order_by=[desc(Document.c.last_viewed),
desc(Histogram.c.num)], limit=limit)
# test
histogram_table.create()
document_table.create()
from datetime import datetime
now = datetime.now()
doc1 = Document(html_name=u'sa.html', section=u'orm', last_viewed=now)
h11 = Histogram(html_name=doc1.html_name, word=u'sqlalchemy', num=93)
h12 = Histogram(html_name=doc1.html_name, word=u'we', num=3)
h13 = Histogram(html_name=doc1.html_name, word=u'good', num=23)
h14 = Histogram(html_name=doc1.html_name, word=u'best', num=13)
doc2 = Document(html_name=u'safaq.html', section=u'orm', last_viewed=now)
h21 = Histogram(html_name=doc2.html_name, word=u'we', num=113)
h22 = Histogram(html_name=doc2.html_name, word=u'ask', num=13)
doc3 = Document(html_name=u'so.html', section=u'orm', last_viewed=now)
h31 = Histogram(html_name=doc3.html_name, word=u'they', num=3)
h32 = Histogram(html_name=doc3.html_name, word=u'also', num=3)
h33 = Histogram(html_name=doc3.html_name, word=u'good', num=1)
objectstore.commit()
documents = database_search(u'we all \tlove \nsqlalchemy', u'orm')
# process one by one (as with cursor)
for doc in documents:
print doc.html_name
for doc in database_search(u'', u'orm', 1):
print doc.html_name
There is more information here:
* http://sqlalchemy.org/docs/sqlconstruction.myt#sql_select_whereclause
* http://sqlalchemy.org/docs/sqlconstruction.myt#sql_building
On 3/31/06, Jonathan Hayward http://JonathansCorner.com
<[EMAIL PROTECTED]> wrote:
> No responses? Is my code good, clueless, or in between?
>
>
>
> On 3/30/06, Jonathan Hayward http://JonathansCorner.com
> <[EMAIL PROTECTED] > wrote:
> >
> > I have the following method; I am suspicious of the last line.
> >
> > What I want to do is build up a query to search for instances of the
> object "document" encapsulated by "document_table", and then get a cursor
> which I can use to move through the results. Is there a way I can get a
> cursor (presumably changing the last line), and call something like
> cursor.current_object() to get the object the cursor is over, and
> cursor.next_object() to move to the next one?
> >
> > def database_search(self):
> > query = []
> > query.append( \ "SELECT document_table.* FROM
> document_table, histogram_table ")
> > where_specified = 0
> > if selected_section in sequence(sections):
> > restricted_section = selected_section
> > #elif selected_section in sequence(subsections):
> > #restricted_section = subsections[selected_section]
> > else:
> > if configuration.get_program_name() == "searchlog":
> > restricted_section = get_default_section()
> > else:
> > restricted_section = None
> > if restricted_section != None:
> > if where_specified:
> > query.append("WHERE ")
> > else:
> > where_specified = 1
> > query.append ("AND ")
> > query.append("section = '" + restricted_section + "' ")
> > debug_log("database_search query words following:")
> > for word in sequence(re.split ("\W+")
> > debug_log("database_search query word " + word)
> > if word != "":
> > if where_specified:
> > query.append("WHERE ")
> > else:
> > where_specified = 1
> > query.append("AND ")
> > query.append("document_table.html_name = " + \
> > "histogram_table.html_name AND histogram_table.word = '"
> + \
> > word + "' ")
> > query.append("SORT BY document_table.last_viewed DESC;")
> > cursor =
> document_table.select("".join(query)).execute().cursor()
> >
> >
> >
> > --
> > ++ Jonathan Hayward, [EMAIL PROTECTED]
> > ** To see an award-winning website with stories, essays, artwork,
> > ** games, and a four-dimensional maze, why not visit my home page?
> > ** All of this is waiting for you at http://JonathansCorner.com
> >
> > ** If you'd like a Google Mail (gmail.com) account, please tell me!
>
>
>
> --
> ++ Jonathan Hayward, [EMAIL PROTECTED]
> ** To see an award-winning website with stories, essays, artwork,
> ** games, and a four-dimensional maze, why not visit my home page?
> ** All of this is waiting for you at http://JonathansCorner.com
>
> ** If you'd like a Google Mail (gmail.com) account, please tell me!
-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting language
that extends applications into web and mobile media. Attend the live webcast
and join the prime developer group breaking into this new coding territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid0944&bid$1720&dat1642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
