How to query all results in table and modify each row using python

Question

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!

nacho · Accepted Answer · 2019-05-19 11:38:04Z

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))

Paul Dickson · Accepted Answer · 2019-05-19 09:26:48Z

0

It would be good to know the kind of errors you are getting. But it seems fetchall will return a list of row tuples, your code is treating this result as a single row.

The docs (https://docs.python.org/3.7/library/sqlite3.html) suggest treating the result of execute as an iterator.

So you can do:

for row in c.execute(...):
   ...

Instead of a while True loop.

answered May 19, 2019 at 9:26

Paul Dickson

11 bronze badge

Comments

score 0 · Accepted Answer · 2019-05-19 09:37:32Z

Guessing you could use this, with changes as you need, to do what you want all in SQL, code not needed.

Would be used easy move to another system/language.

INSERT INTO ItemCodes_table 
    SELECT
        CASE WHEN Price <= 10 THEN 'low' ELSE 'high' END AS value_to_use,
        'whatever should be in Code_column1',
        'whatever should be in Code_column2',
        item
    FROM ItemPrice_Table
;

If ItemPrice_Tables data like

1 Itema 5
2 Itemb 15
3 Itemc 9
4 Itemd 11
5 Iteme 3
6 Itemf 20
7 Itemg 17

It gives data like

low whatever should be in Code_column1 whatever should be in Code_column2 Itema
high whatever should be in Code_column1 whatever should be in Code_column2 Itemb
low whatever should be in Code_column1 whatever should be in Code_column2 Itemc
high whatever should be in Code_column1 whatever should be in Code_column2 Itemd
low whatever should be in Code_column1 whatever should be in Code_column2 Iteme
high whatever should be in Code_column1 whatever should be in Code_column2 Itemf
high whatever should be in Code_column1 whatever should be in Code_column2 Itemg

Collectives™ on Stack Overflow

How to query all results in table and modify each row using python

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related