Ad

How To Query All Results In Table And Modify Each Row Using Python

I'm trying to cycle through all rows in an sqlite3 database table and based on the column variables enter different information into columns and rows inside another table.

I have tried loops using fetchone() and fetchall() and a simple "while True:" -- it all has resulted in different errors.

Below is a general example of isolated code that I think at least illustrates what I'm trying to do.

c.execute('SELECT * FROM {0}'.\
        format(ItemPrice_table))

    while True:
        row = c.fetchall()
        if(row == None):
            break

        Item = row[0]
        Price = row[1]

        if(Price <= 10):
            Category = "Low"

        if(Price > 10):
            Category = "High"

        if(Category == High):
            c.execute("INSERT INTO {0} ({1}, {2}, {3}) VALUES ('{4}', 'Expensive' 'Red')".\
                format(ItemCodes_table, Code_column0, Code_column1, Code_column2, Item))

        if(Category == Low):
            c.execute("INSERT INTO {0} ({1}, {2}, {3}) VALUES ('{4}', 'Cheap' 'Blue')".\
                format(ItemCodes_table, Code_column0, Code_column1, Code_column2, Item))

So I would expect it to cycle through each row in the "ItemPrice_table" and based on the price assign a category, then write this category write specific information to the "ItemCodes_table".

  • It seems like it should be simple
  • I could write a function like this in php easily enough
  • but as I've said no matter what I do I just can't get it running

After several hours of experimentation I am all out of ideas and would really appreciate any help I can get!

Ad

Answer

When you do a fetchall, you get a list with your results. For each item of the list, you get an element is in the order your select returns. You should change your code to:

c.execute('SELECT * FROM {0}'.\
        format(ItemPrice_table))
data = c.fetchall()
for row in data:    

    Item = row[0]
    Price = row[1]

    if (Price <= 10):
        Category = "Low"

    if (Price > 10):
        Category = "High"

    if (Category == "High"):
        c.execute("INSERT INTO {0} ({1}, {2}, {3}) VALUES ('{4}', 'Expensive' 'Red')". \
                  format(ItemCodes_table, Code_column0, Code_column1, Code_column2, Item))

    if (Category == "Low"):
        c.execute("INSERT INTO {0} ({1}, {2}, {3}) VALUES ('{4}', 'Cheap' 'Blue')". \
                  format(ItemCodes_table, Code_column0, Code_column1, Code_column2, Item))

I have changed too the Category == High by Category == "High" and the same with the Low. Bu as you don ´t use the Category, you could do:

c.execute('SELECT * FROM {0}'.\
        format(ItemPrice_table))
data = c.fetchall()
for row in data:    
    Item = row[0]
    Price = row[1]

    if (Price <= 10):
        c.execute("INSERT INTO {0} ({1}, {2}, {3}) VALUES ('{4}', 'Cheap' 'Blue')". \
                  format(ItemCodes_table, Code_column0, Code_column1, Code_column2, Item))

    if (Price > 10):
        c.execute("INSERT INTO {0} ({1}, {2}, {3}) VALUES ('{4}', 'Expensive' 'Red')". \
                  format(ItemCodes_table, Code_column0, Code_column1, Code_column2, Item))
Ad
source: stackoverflow.com
Ad