CODEWITHSUNDEEP
pythonsqliteloopsselectwhile-loop
Oldtimer
Oldtimer

Reputation: 45

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".

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

Upvotes: 0

Views: 393

Answers (3)

nacho
nacho

Reputation: 5397

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

Upvotes: 1

user11478861
user11478861

Reputation:

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

Upvotes: 0

Paul Dickson
Paul Dickson

Reputation: 1

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.

Upvotes: 0

Related Questions