On 17/09/2010 00:56, Vlastimil Brom wrote:
2010/9/17 MRAB<pyt...@mrabarnett.plus.com>:
On 16/09/2010 23:11, Vlastimil Brom wrote:
...
I put together some code, which works as expected, but I suspect
somehow, that there must be better ways of doing it.
Two things I am not quite clear about are using the placeholders for
the data identifiers and "chaining" the SELECT parameters.
I Couldn't find a way to use "?" placeholder for table or column
names, hence I ended up using string interpolation for them and
placeholders for the data values, like.
curs.execute('SELECT * FROM "%s" WHERE "%s"==?' % (text_name,
index_col), (text_index,))
is there a better way or is it not supposed to supply these
identifiers programatically?
You would normally expect the structure of the database to be fixed and
only the contents to vary.
For getting the matching text indices given the tags, tag_values
combination I ended up with a clumsy query:
combined_query_list = ['SELECT "%s" FROM "%s" WHERE "%s"==?' %
(index_col, text_name, tag) for tag in tags]
sql_query = " INTERSECT ".join(combined_query_list)
curs.execute(sql_query, tag_values)
which produces e.g.:
SELECT "ind" FROM "n" WHERE "KC"==? INTERSECT SELECT "ind" FROM "n"
WHERE "VN"==?
or alternatively:
select_begin = 'SELECT "%s" FROM "%s" WHERE ' % (index_col, text_name)
where_subquery = " AND ".join('"%s"==?' % (tag,) for tag in tags)
sql_query = select_begin + where_subquery
with the resulting query string like:
SELECT "ind" FROM "n" WHERE "KC"==? AND "VN"==? ('12', '1')
(BTW, are these queries equivalent, as the outputs suggest, or are
there some distinctions to be aware of?)
Anyway, I can't really believe, this would be the expected way ...
If you're selecting rows of a table then using 'AND' would seem the
obvious way.
Thanks for the answer,
Well, that may be a part of the problem, the database structure is
going to be fixed once I'll have the text sources complete, but I was
trying to keep it more general, also allowing the identifiers to be
passed programmatically (based on the tagged text in question).
yes, I am just selecting rows - based on the combination of the column
values (which, I guess, might be an usual database approach(?).
However, I was unsure, whether it is usual to construct the query
string this way - partly using string interpolation or sequence
joining.
Or should there normally be no need for construct like the above and I
am doing something wrong in a more general sense?
In general you'd have a fixed database structure and fixed queries.
You'd design it so that you wouldn't have different columns for
different tagged texts, forcing you to change the database structure
when texts were added or removed, but no-one will come around to your
house to stop you creating queries dynamically. :-)
--
http://mail.python.org/mailman/listinfo/python-list
