CODEWITHSUNDEEP
pythonsqlitepython-3.x
HalfIrish
HalfIrish

Reputation: 61

how to add data to a table in sqlite 3 without going to a new line

I'm using sqlite to hold a series of data about stocks such as buy price, sell price, trend buy price etc. I add the prices and thats fine but when I add the trends it goes on to a new line, how do I add this in line with the other data? I have tried using replace and update but it still adds on causing my other columns to return none which is causing problems with my code.

My code is as follows

def plot():
    for a in stock:
        count1 = 0
        count2 = 1
        index = 0
        value = ["0"]
        cursor.execute("""SELECT BuyPrice FROM """+a+"""""")
        trend = []
        rows = cursor.fetchall()
        for row in rows:
            print(row[0])
            trend.append(float(row[0]))
            index = index + 1
            if index == len(web):
                percentage = []
                for i in range(len(web)-1):
                    change = trend[count2]-trend[count1]
                    print(trend[count2],trend[count1])
                    percent = (change/trend[count1])*100
                    print(percent)
                    percentage.append(percent)
                    count1 = count1 + 1
                    count2 = count2 + 1
                for i in percentage:
                    print(i)
                    if i <= 0:
                        if i == 0:
                            value.append(0)
                        elif i <= -15:
                            value.append(-4)
                        elif i <= -10:
                            value.append(-3)
                        elif i <= -5:
                            value.append(-2)
                        elif i < 0:
                            value.append(-1)
                    else:
                        if i >= 15:
                            value.append(4)
                        elif i >= 10:
                            value.append(3)
                        elif i >= 5:
                            value.append(2)
                        elif i >= 0:
                            value.append(1)

                for i in value:
                    t = str(i)
                    cursor.execute("""
                        REPLACE INTO """+ a +"""
                        (TrendBuy)
                        VALUES
                        ("""+ t +""")
                        """)

this is what i mean if its a bit hard to understand

Upvotes: 1

Views: 45

Answers (1)

Parfait
Parfait

Reputation: 107577

SQLite's REPLACE INTO needs a sort of "lookup column" to find a value and then replace the next corresponding value(s). If no matches exist, it will insert a new row. Consider incorporating BuySell in your action query. Also below formats SQL string and parameterizes query:

cursor.execute("REPLACE INTO {} (BuySell, TrendBuy) VALUES (?, ?)".format(a), (row[0], t))

Alternatively, you could run an update query to avoid unintentional inserts and since no new BuySell values generate in looping logic.

cursor.execute("UPDATE {} SET TrendBuy = ? WHERE BuySell = ?".format(a), (t, row[0]))

Aside - you seem to be storing each Stock in its own table. For efficiency, scalability, and easier querying, consider one Stock table with a StockName or Symbol column.

Upvotes: 1

Related Questions