Importing pandas df to SQLite with Python

Question

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

This is how it looks after import. It like deleted the stock_id column.

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.

Are you referring to the "filter" row? Isn't it built in for filtering the table? — gtomer
– gtomer, Commented Sep 13, 2020 at 9:07
Umm not sure what filter row is. I thought like if I had another table with like overview_data with market cap, stock price ect. The first column would be stock_id #, or would the foreign key be stock_ticker ? — Jakub
– Jakub, Commented Sep 13, 2020 at 9:08
Yea, I have no idea. In the team treehouse tutorial had to create a column for the id, so not sure what I am seeing with that number on the side. When I was manually adding entries to test things out it would auto-create the numbers in stock_id, but also showed those numbers on the side at the same time. So not sure why when I am importing data, it like deletes that column. — Jakub
– Jakub, Commented Sep 13, 2020 at 9:13
I found this post on stackoverflow but it also uses the word replace stackoverflow.com/questions/45120366/… — Jakub
– Jakub, Commented Sep 13, 2020 at 9:18

Jakub · Accepted Answer · 2020-09-13 19:24:45Z

2

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

answered Sep 13, 2020 at 19:24

Jakub

1,7483 gold badges26 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Areza Over a year ago

what does c = conn.cursor() do ?

CyberPlayerOne Over a year ago

is conn.commit() necessary? I saw many who don't use it after to_sql

Collectives™ on Stack Overflow

Importing pandas df to SQLite with Python

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related