0

I imported a list of ticker symbols from from a csv list then created date dateframe

tickers = pd.read_sql_table('symbols',engine)

then I created a df from the returned data

df1 = pd.DataFrame(app.data,  columns=[ 'id','date', 'open', 'high', "low", 'close', 'volume' ])

Everything went fine for the initial loading and daily updates

if (key == 'symbol'):
                try:
                    df1.to_sql( contract.symbol, engine, if_exists='append', index=False)
                except:
                    df1.to_sql( contract.symbol, engine)
            else:
                try:
                    df1.to_sql( contract.symbol +"_"+ key, engine, if_exists='append', index=False)
                except:
                    df1.to_sql( contract.symbol +"_"+ key, engine)

Now I want to create a method to add new symbols to the symbols tables but no primary keys were created. I expected to find primary keys, autoincrement not null by default. I have a lot of data 300 tables with ~5000 rows. all of the related tables contain a common id column inherited from the symolstable. (BigInt) assigned when looping through their initial. Is there a simple way to automate using id and make it pk autoincrement not null, so I can simply add a new symbols to to the symbols table?

cottontail
  • 10,268
  • 18
  • 50
  • 51
BTT
  • 17
  • 4
  • 1
    Does this answer your question? [Python Pandas to\_sql, how to create a table with a primary key?](https://stackoverflow.com/questions/30867390/python-pandas-to-sql-how-to-create-a-table-with-a-primary-key) – Gord Thompson Nov 29 '22 at 18:43
  • I think it helps. I'm pretty new at this obviously. It looks to me like the suggestions require copying the the tables. If i were going to do that I think i would rather use straight sql and avoid the ORM since it does not work the way i thought it would. Does that make since? Thanks – BTT Nov 29 '22 at 19:03

1 Answers1

0

Problem effectively solved if not precisely. By making the id of the symbols table auto-increment and returning the id of the added row I can then propagate new tables.

BTT
  • 17
  • 4