How to transfer data from one sqlite3 table to another?

Question

Python

I created a function that asks the user what cryptocurrency he bought, all answers are correctly transferred to the table. Data about the user's operation is added to this table (which token was taken, in what volume and at what price, the cost is determined automatically and the date). But I also want to add a separate table where the total number of a particular token and its cost will be calculated, the difference from the first table is that the token cannot be repeated, but only update the data inside itself (volume and cost)

import sqlite3
import datetime


with sqlite3.connect("crypto.db") as db:

  cursor = db.cursor()
  joining_date = datetime.datetime.now()

  cursor.execute("""CREATE TABLE IF NOT EXISTS history(
    crypto TEXT,
    how_m TEXT,
    price TEXT,
    cost TEXT,
    joiningDate timestamp
  ); 

  CREATE TABLE IF NOT EXISTS coins (
    coin TEXT PRIMARY KEY
    value TEXT
    price TEXT

  )
  """)

def add_crypto(): 
  global crypto_name
  crypto_name = input("Select which crypto you want to add to your wallet: ")
  how_much = float(input("how much is your contribution:  "))
  price_buy = float(input("At what price did you buy: "))
  cost = how_much * price_buy

  data_turple = (crypto_name, how_much,price_buy, cost,joining_date)
  sqlite_insert_param = "INSERT INTO history('crypto', 'how_m', 'price','cost', 'joiningDate') VALUES(?,?,?,?,?)"
  
  cursor.execute(sqlite_insert_param, data_turple)
  db.commit()
  print("Криптовалюта успешно добавлена! ")

add_crypto()

Answer 1

there is an insert or replace sql query that updates a record in the specified table according to the condition, but if the condition returns an empty list, it creates a record
. But I strongly recommend to simplify the code to exclude the situation when there are no necessary records in the updated table, at the moment when your database there is support for a new token, add an entry about it with zero volume to this table. In this case, a simple update mytable set amount=amount+? where cryptoname=?
And yet, I strongly recommend, for financial applications, to save any operations on balances as records in a table, as a machine-readable log, it will save you nerves and money in the future

Answer 2

Sorry of course, but the current implementation is monstrous. At least storing numeric values ​​as text is the most incorrect approach (for integer values, for monetary values ​​- decimal)
Slightly more correct structure*:
Coin:

• Coin_id:int PK
  
• Name:char
  

user:

• User_id:int PK
  
• ...
  

flow:

• Id:int PK
  
• date:datetime
  
• Amoung:int
  
• price:decimal
  
• Coin_id:int FK->Coin
  
• User_id:int FK->User
  

How to count? Fetch by coin key using sum() on Flow.amoung.
* - depending on the detailed, TK, the implementation may differ. Here is just an abstract schema drawn up according to your general description and taking into account the minimum norms of normalization.