where clause construction using variable names and values

[Tejaswi]

I have a dict of keys to values which have to go into my where clause
with an and_.

Say dict = {"key1": value1, "key2": value2}

my select statement should look like select * from blah where key1 =
value1 and key2 = value2

I know this has to do with constructing the right where clause
element, but I cannot seem to find documentation on it.

select([table], meta.c.<columnname> == value) doesn't take a variable
key.

Any help would be greatly appreciated.

-T

[Conor]

You can treat meta.c as a dict, e.g. meta.c[key] == value.

-Conor

[werner]

Are you using SA.orm?

If yes, then you probably want to look at query.Query.filter and/or
query.Query.filter_by.

Werner

[Tejaswi]

I am not using sa.orm. I want to use only the sql expression syntax.

@Conor: I tried the dict approach. The problem is, I don't know how
many key value pairs I will have. I will have to use a map, or map* to
construct the full set of where clauses. This is the syntax I am not
able to figure out.

Thanks for the replies.
-T

[Conor]

Tejaswi wrote:
> I am not using sa.orm. I want to use only the sql expression syntax.
>
> @Conor: I tried the dict approach. The problem is, I don't know how
> many key value pairs I will have. I will have to use a map, or map* to
> construct the full set of where clauses. This is the syntax I am not
> able to figure out.
>
>

How about this:

clauses = [meta.c[key] == value for (key, value) in dict.iteritems()]
select([table], and_(*clauses))

or, generatively:

s = select([table])
for (key, value) in dict.iteritems():
s = s.where(meta.c[key] == value)

-Conor

[Tejaswi]

@Conor: This might be what I am looking for. I cannot try it right
now, but will reply to this thread in 3-4 hours.

Can you please point me to the documentation that discusses the
different ways of constructing select statements, where clauses, etc.
I have not seen the generative way before. I tried really hard on
Google, this forum specifically, stackoverflow, etc. The API
documentation is sufficient, I am sure; but is not "tutorial" like.

Thanks again. This is greatly appreciated.

-T

[Tejaswi]

On second thought, it's nothing to do with SA, and just a python
feature that I am not familiar with. The idiom of clause construction,
and passing arguments using the *list is new to me. And most of my
Google queries were prefixed with sqlalchemy, and in retrospect, that
was hurting more than helping.

Thanks agian,
-T

[Tejaswi]

@Conor - the where clause construction works now. Thanks for the reply
on that. I have run into a new problem now. My select clauses have
unicode names even when I construct them from vanilla strings. Here's
my code:

column_list = ["url", 'html']
what_fields = [meta.c[x] for x in column_list]
print what_fields
[Column(u'url', VARCHAR(length=None, convert_unicode=False,
assert_unicode=None, unicode_error=None, _warn_on_bytestring=False),
table=<html_frontpage>), Column(u'html', TEXT(length=None,
convert_unicode=False, assert_unicode=None, unicode_error=None,
_warn_on_bytestring=False), table=<html_frontpage>)]

My table doesn't have column names that are unicode strings.

Am I missing something here?

-T