comparing results of fetchall() with a known value

[Alex Martelli]

"Jeff" <jhardcastle at solarc.com> wrote in message
news:e33814e8.0108201528.57883ea1 at posting.google.com...
> Hi - I'm new to Python, and am having trouble reading the results of a
> database call using fetchall().  Here's my situation:
>
> I have a database table containing a list of names, and I have a
> string variable containing a name.  I need logic that will determine
> if my string exists in the database table.  So I have a simple sql
> like "select name from employees", and I have a string empname =
> 'Jones', and I need to verify that 'Jones' is in the employees list
> (assume duplicate names are not an issue in this case).

So, why not just "select name from employees where name='%s'"%empname
or similar approach with a placeholder "... where name=?" and the
value provided in the query call?  SQL and relational DB engines
are very good at such jobs!


> Here's what I've done: I created an odbc connect to the database,
> opened a cursor, executed the sql, then set a variable: namelist =
> cursor.fetchall().  What's got me stumped is that "namelist" is a list
> of tuples with sample data like: [('smith',), ('johnson',),
> ('jackson',), ('jones',)]
>
> If this were a normal tuple or list, I could easily use:
>
> "if empname in namelist:
>      ....execute code...."
>
> But with the list of tuples, I don't know how to easily do this. My

    if (empname,) in namelist:

should work just fine.  But it's a linear search, while the DB
engine might well be able to perform a much faster one -- it
IS, after all, its main job!


> hunch is that there must be some way to convert that list of tuples
> into a list of single values, since I only need the first element of
> the tuple like 'jackson' from ('jackson',).  Once I had the list of
> single values, my if statement above would work.

Yeah, but that's too much work -- transforming each of N tuples
into its first-element rather than transforming ONE string you're
looking for into a one-element tuple.  Anyway, if you're intent
on using this slower approach at all costs, it would be:

    if empname in [tuple[0] for tuple in namelist]:


Alex