-2

My goal is to add 3 columns from my transactions table into my portfolio table. I am using sqlite3 in my python code and I am wondering what the correct statement for this is. The following mysql code works in mysql, but unfortunately I need to use sqlite3 for my python code. I hope that the mysql code gives you some kind of idea what I'm trying to achieve:

INSERT INTO portfolio (ticker, purchase_price, quantity)(SELECT ticker, price, quantity from transactions);

So far, I have tried:

insert = cur.execute('select ticker, price, quantity from transactions')
cur.execute('INSERT INTO portfolio (ticker, purchase_price, quantity) VALUES (?,?,?)', insert)

AND:

cur.execute('INSERT INTO portfolio (ticker, purchase_price, quantity) VALUES (?,?,?)', ('SELECT ticker, price, quantity from transactions'))

The transactions table looks like this:

transaction_id | ticker  | quantity | price | date     | type     |
0001           | AAPL    | 5        | 104.0 | 10/24/22 | PURCHASE |
0002           | AAPL    |-8        | 98.0  | 10/24/22 | SALE     |
0003           | AAPL    | 7        | 100.0 | 10/24/22 | PURCHASE |
0004           | AAPL    | 10       | 104.0 | 10/24/22 | PURCHASE |

The portfolio table looks like this:

ticker| quantity| price| current_price| profit |

So my goal is to insert the columns (ticker, quantity & price) from the transactions table into the portfolio table.

I am new to python and sqlite3, so any help is greatly appreciated!

petezurich
  • 9,280
  • 9
  • 43
  • 57
Kevindelange
  • 11
  • 3
  • did you try to use full query `cur.execute("INSERT INTO portfolio (ticker, purchase_price, quantity)(SELECT ticker, price, quantity from transactions);")` – furas Oct 24 '22 at 23:31
  • [sqlite insert into table select \* from - Stack Overflow](https://stackoverflow.com/questions/16838896/sqlite-insert-into-table-select-from) – furas Oct 24 '22 at 23:32

1 Answers1

1

well if i were you i'd do something like this: you first fetch all the rows into memory and then feed it to the executemany method.

data = cur.execute(
    'select ticker, price, quantity from transactions'
).fetchall()

cur.executemany(
    'INSERT INTO portfolio (ticker, purchase_price, quantity) VALUES (?,?,?)',
    data
)
Andrew Menshicov
  • 91
  • 4