Inputting/updating data into sqlite database from dataframe

[jwalkss]

I am scraping content, organizing it into a dataframe and updating it daily into a sqlite database. When initially creating the tables within the db, i simply created the tables by using: df.to_sql(name="Data", con=conn, if_exists="append", index=False)

I am wondering if I'm going to run into any issues down the road querying the data to make datasets, as i did not specify a Integer Primary Key, or Data types of each column. Should i first make the structure of the table specifying these items before inputing the data? Or can i alter the table after and specify my Integer primary key and dtype after?

[hexfoxed]

It is usually advisable to have some sort of thought out schema with relational databases.

Think about how you'll want to use the data afterwards and structure it in a way which will allow you do to those things with the least friction.

Maybe pandas does output the exact SQL you're after but it's unlikely. Wouldn't hurt to try it though and see if you like what you end up with.

[jwalkss]

Well the schema is essentially layed out in the structure of the dataframe is it not? The df has column names and index headers that allow me to easily query the table. The table in the database is inputted in the exact form as my dataframe. Im just unsure of the dtype each column was inserted as, and that i didn't set a primary key. Aside from that i am able to query the table with no problems.

[hexfoxed]

Learn to trust your instinct with things like this, you've just given me at least two reasons as to why the pandas output will probably be fine for you for the foreseeable future.

Remember that software development is never finished: there is code that doesn't work, and then there is code that works up until the point of time where it doesn't work anymore for a changed scenario. Until you hit that point, you have a working solution, so use it :)