dynamic if statement

[upper...@gmail.com]

I am new to python and struggling with creating a dynamic if statement.

I have a set of queries that are run against various databases/tables. The result is all the same in that I always get back the same field names.

What I want to do is total the results differently based on the table. so for instance

I query fld1, fld2, fld3, qty, qty2 from table1
then I loop thru the results
if fld1 = 'a' add qty to some_total1

I query fld1, fld2, fld3, qty, qty2 from table2
then I loop thru the results
if fld2 = 'b' add qty to some_total1

I query fld1, fld2, fld3, qty, qty2 from table3
then I loop thru the results
if fld3 = 'c' add qty2 to some_total1

I created a database pair that contains (table1,fld1 = 'a',add qty to some_total1)
(table2,fld2 = 'b',qty to some_total1)
(table3,fld3 = 'c',qty2 to some_total1)

So I know which table I am using, I query my list pair but I cant see how to build the if statement using the results from the query.

something like this would be the result
var1 = "fld1 = 'a'"
result = "add qty to some_total1"

if var1:
result

[Tim Chase]

On 2013-06-18 07:10, upper...@gmail.com wrote:
> I have a set of queries that are run against various
> databases/tables. The result is all the same in that I always get
> back the same field names.
>

> I query fld1, fld2, fld3, qty, qty2 from table1
> then I loop thru the results
> if fld1 = 'a' add qty to some_total1

...

> I created a database pair that contains (table1,fld1 = 'a',add qty
> to some_total1) (table2,fld2 = 'b',qty to some_total1)
> (table3,fld3 = 'c',qty2 to
> some_total1)

Given the data-structure you have, and that it's hard-coded (rather
than able to take dynamic/dangerous user input) for the table-name,
I'd do something like this (untested)

for tablename, compare_field, compare_value, source_field in (
("table1", "fld1", "a", "qty"),
("table2", "fld2", "b", "qty"),
("table3", "fld3", "c", "qty2"),
):
# using string-building rather than escaping because
# 1) we know the tablenames are hard-coded/safe from above, and
# 2) this sort of escaping often chokes query parsers
query = "SELECT fld1, fld2, fld3, qty, qty2 from %s" % tablename
cursor.execute(query)

name_index_map = dict(
(info[0], i)
for info, i in enumerate(cursor.description)
)
for row in cursor.fetchall():
db_value = row[name_index_map[compare_field]]
if db_value == compare_value:
addend = row[name_index_map[source_field]]
some_total_1 += addend

-tkc

[Mark Lawrence]

On 18/06/2013 15:56, Tim Chase wrote:
> On 2013-06-18 07:10, upper...@gmail.com wrote:

>> I have a set of queries that are run against various
>> databases/tables. The result is all the same in that I always get
>> back the same field names.
>>

>> I query fld1, fld2, fld3, qty, qty2 from table1
>> then I loop thru the results
>> if fld1 = 'a' add qty to some_total1

> ...

>> I created a database pair that contains (table1,fld1 = 'a',add qty
>> to some_total1) (table2,fld2 = 'b',qty to some_total1)
>> (table3,fld3 = 'c',qty2 to
>> some_total1)
>

> Given the data-structure you have, and that it's hard-coded (rather
> than able to take dynamic/dangerous user input) for the table-name,
> I'd do something like this (untested)
>
> for tablename, compare_field, compare_value, source_field in (
> ("table1", "fld1", "a", "qty"),
> ("table2", "fld2", "b", "qty"),
> ("table3", "fld3", "c", "qty2"),
> ):
> # using string-building rather than escaping because
> # 1) we know the tablenames are hard-coded/safe from above, and
> # 2) this sort of escaping often chokes query parsers
> query = "SELECT fld1, fld2, fld3, qty, qty2 from %s" % tablename
> cursor.execute(query)
>
> name_index_map = dict(
> (info[0], i)
> for info, i in enumerate(cursor.description)

Looks like this should be :-
for i, info in enumerate(cursor.description)

> )
> for row in cursor.fetchall():
> db_value = row[name_index_map[compare_field]]
> if db_value == compare_value:
> addend = row[name_index_map[source_field]]
> some_total_1 += addend
>
> -tkc
>

--
"Steve is going for the pink ball - and for those of you who are
watching in black and white, the pink is next to the green." Snooker
commentator 'Whispering' Ted Lowe.

Mark Lawrence

[Tim Chase]

On 2013-06-18 16:27, Mark Lawrence wrote:
> On 18/06/2013 15:56, Tim Chase wrote:
> > name_index_map = dict(
> > (info[0], i)
> > for info, i in enumerate(cursor.description)
>
> Looks like this should be :-
> for i, info in enumerate(cursor.description)

Doh, indeed, you're correct. As forewarned though, it *was*
completely untested, so anything remotely approaching working code is
merely a tribute to how easy it is to write Python that actually runs.

:-)

-tkc

[upper...@gmail.com]

thanks for the help.. with a bit of tweaking i got it working as needed