0

Trying to update all records with the sum of each existing field value plus a parameter being passed. If the field in record A = 4 and record B = 5, I want to update all records so A = 4 + X and B = 5 + X. This way I can quickly increment all record values by a given number (X). So far I can't figure out how to get the update statement to expand the parameter being passed.

I can add hard coded value with this line: c.execute("UPDATE level SET sell = sell + 1;") The code below works as is. But when I try to pass a value with the line update_sell(1) to def update_sell(margin): and reference the value passed like so, c.execute("UPDATE level SET sell = sell + margin;") it fails. What am I missing here?

Total python noob here.

Script to run:

#MainScript.py
import sqlite3
from LevelClass import level

conn = sqlite3.connect(':memory:')
c = conn.cursor()

c.execute("""CREATE TABLE level (
            buy integer,
            sell integer,
            quan integer
            )""")

def insert_level(level):
    with conn:
        c.execute("INSERT INTO level VALUES (:buy, :sell, :quan)", {'buy': level.buy, 'sell': level.sell, 'quan': level.quan})

def get_level_by_sell(sell):
    c.execute("SELECT * FROM level WHERE sell=:sell", {'sell': sell})
    return c.fetchall()

def update_sell(margin):
    with conn:
        # below works with "sell + 1", but fails with "sell + margin"
        c.execute("UPDATE level SET sell = sell + 1;")

trans_1 = level(1, 5, 50)
trans_2 = level(2, 10, 60)

insert_level(trans_1)
insert_level(trans_2)

# value to pass to update_sell as var "margin"
update_sell(1)

find = get_level_by_sell(6)
print(find)

find = get_level_by_sell(11)
print(find)

conn.close()

class:

# saved as LevelClass.py
class level:
    def __init__(self, buy, sell, quan):
        self.buy = buy
        self.sell = sell
        self.quan = quan

    def __repr__(self):
        return "Level('{}', '{}', {})".format(self.buy, self.sell, self.quan)
1

I think you're forgetting the : for the update function. This works for me.

def update_sell(margin):
    with conn:
        # below works with "sell + 1", but fails with "sell + margin"
        #c.execute("UPDATE level SET sell = sell + 1")
        c.execute("UPDATE level SET sell = sell + :margin", {"margin":margin})
  • Why is {"margin": margin} needed? – sadtank Aug 28 '17 at 21:29
  • 1
    Margin still needed to be inserted into the prepared sql statement. You had the statement built, {'margin':margin} tells SQLites api to safely inject that variable into the sql so it can be processed. – codykochmann Aug 28 '17 at 21:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.