Search code examples
pythonpandassqlitedataframepeewee

Importing pandas df to SQLite with Python

I tried to follow the pandas and sqlite3 documentation but I am not entirely sure if I am setting things up correctly.

I set up the database with peewee in this format. I wanted to make stock_id be the number that goes 1,2,3,4,5,6 automatically ect. When I manually add entries with SQLite Browser it seems to work like that.

class Stocks(Model):
    stock_id = IntegerField(primary_key=True, index=True, unique=True)
    stock_ticker = CharField(max_length = 10, unique=True) 
    stock_name = TextField()
    stock_exchange = TextField()
    stock_ipoDate = DateField()
    stock_delistingDate = DateField()
    stock_status = TextField()

    class Meta:
        database = db

I imported CSV file using pandas and converted them into dataframe. Now trying to send them to SQLite "stocks" table.

conn = sqlite3.connect('stockmarket.db')
c = conn.cursor()
df.to_sql('stocks', conn, if_exists='replace', index = False)

conn.commit()

Now sure on how to make it skip the first row? I thought it would import things into SQLite with the same way as they are named. Like if index is stock_ticker than it would add it to stock_ticker column ect.

This is the how the table looks like before import enter image description here

This is how it looks after import. It like deleted the stock_id column. enter image description here

I don't want to rebuild the table, but just update it and add new rows if the stocks don't exist. Along with having a unique stock_id that is auto generated so it could be the foreign key for another table. I tried making it be 'update' instead of 'replace' but it gave me an error.

Also in SQLite Browser I am seeing the #1 to #5 for the rows. Is that the auto generated id that I should be using for foreign key?

Any help much appreciated.

Solution

  • Finally figured it out

    https://www.excelcise.org/python-sqlite-insert-data-pandas-data-frame/

    conn = sqlite3.connect('stockmarket.db')
c = conn.cursor()

df.to_sql(name='stocks', con=conn, if_exists='append', index=False)

conn.commit()

    and it auto generates the stock_id enter image description here