SQLite User Forum

all python code:

https://colab.research.google.com/drive/1ZlNpPKK0ivnxaOclFe1RPEwelnCFrBDx

additionional file used:

https://drive.google.com/file/d/15yceflA_DjBn8LmB-uQLIPeNcy-s842m/view?usp=sharing

Database creation:
CREATE TABLE IF NOT EXISTS rsbuddy (
                                        id integer PRIMARY KEY,
                                        item_name text,
                                        item_id int,
                                        ts int,
                                        overallPrice int,
                                        overallQuantity int,
                                        buyingPrice int,
                                        buyingQuantity int,
                                        sellingPrice int,
                                        sellingQuantity int
                                    ); 
Create unique index rsbuddy_ts_itemid  on rsbuddy (ts,item_id);

to insert rows:

python:

cols = df.columns.to_list()

values = ','.join('?' for _ in cols)

cols = '","'.join(str(v) for v in cols)

with conn:

  records = df.to_records(index=False)

  sql = f'INSERT OR IGNORE INTO {table_name}("{cols}") values ({values})'

  print(sql)

  conn.executemany(sql, records)

it generates:
INSERT OR IGNORE INTO rsbuddy("item_id","ts","buyingPrice","buyingQuantity","sellingPrice","sellingQuantity","overallQuantity","overallPrice","item_name") values (?,?,?,?,?,?,?,?,?)


pragma check returned ok