dynamic if statement

Discussion in 'Python' started by upperdecksu@gmail.com, Jun 18, 2013.

  1. Guest

    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
     
    , Jun 18, 2013
    #1
    1. Advertising

  2. Tim Chase Guest

    On 2013-06-18 07:10, 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
     
    Tim Chase, Jun 18, 2013
    #2
    1. Advertising

  3. On 18/06/2013 15:56, Tim Chase wrote:
    > On 2013-06-18 07:10, 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
     
    Mark Lawrence, Jun 18, 2013
    #3
  4. Tim Chase Guest

    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
     
    Tim Chase, Jun 18, 2013
    #4
  5. Guest

    On Tuesday, June 18, 2013 10:10:42 AM UTC-4, wrote:
    > 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


    thanks for the help.. with a bit of tweaking i got it working as needed
     
    , Jun 20, 2013
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    3
    Views:
    807
  2. Jay McGavren
    Replies:
    11
    Views:
    1,144
    Alan Krueger
    Jan 16, 2006
  3. tedsuzman
    Replies:
    2
    Views:
    7,094
    Michel Claveau, résurectionné d'outre-bombe inform
    Jul 21, 2004
  4. Ted
    Replies:
    1
    Views:
    470
    Duncan Booth
    Jul 22, 2004
  5. Replies:
    21
    Views:
    1,060
    Giannis Papadopoulos
    Aug 2, 2005
Loading...

Share This Page