Pandas Dataframes and SQL [How to write dataframes into a sql database/get sql table to dataframe]
Вставка
- Опубліковано 14 чер 2024
- In this video I am showing how to get sql data into Pandas dataframes and also how to store dataframes into sql databases. I am working with SQLite in this video.
Get the Notebook/Source code by becoming a Tier-2 Channel member:
/ @algovibes
More information for other Database systems (e.g. MySQL or MS SQL server or Postgres):
docs.sqlalchemy.org/en/14/cor...
Check out my introductory video on Python and SQL:
• How to connect Python ...
Please be so kind subscribing and liking the video in case it was helpful.
0:00 - 0:55 Intro
0:55 - 02:08 Create DB engine (read description for other DB)
02:08 - 03:49 read_sql (Both whole table and SQL syntax possible)
03:49 - 08:32 to_sql (and possible errors/mistakes/pains)
08:32 - 10:20 Pull and store financial data in a sql db
10:20 - 11:04 Outro / Potential topics
#Pandas #SQL #Python
![Create your own Finance DataBase with Python & SQL [perfect for backtesting trading strategies]](http://i.ytimg.com/vi/5bUn-D4eL4k/mqdefault.jpg)
![Create your own Finance DataBase with Python & SQL [perfect for backtesting trading strategies]](/img/tr.png)
![Importing a Pandas Dataframe to a Database in Python [For Your Data Science Project]](http://i.ytimg.com/vi/77IVf0zgmwI/mqdefault.jpg)
Thank you! Looking forward to working through all of your videos!
Thanks Jesse! Let me know if you need something and I keep my fingers crossed for you! Cheers Algovibes
I really really appreciate your videos. I love you accent , how you explain , your method ..everything is great. Already , subscribed and I am sharing your videos ;)
Thank you buddy, really appreciate your kind words and your support :-)
EXACTLY what i was looking for, thank you!
Awesome. Thanks a lot for watching.
Thank you! your videos have been a big help to get my Tranding Bot jurney started!
Thanks a lot for your feedback man. That's awesome to read!
Thank you. You gave me courage to challenge my project!
That's awesome man. Thanks a lot for using the Super Thanks feature and greetings to South Korea!
Another informative video sir , thanks and also very thankful to you replying our comments and providing information as suggested by us by spending your precious time to create such video , we are looking for more video , thanks again sir.
Thanks a lot for your support :-) Really appreciate it.
Main objective of this channel is to create a community where we can exchange about programming, finance and Data Science so I am doing my best to keep the comment section alive :D
gr8 upload and understandings for pandas!!
Awesome mate, thanks for your feedback :-)
Great video brother very useful
Thanks for watching mate :-) Appreciate your comment.
I can't tell you how enjoyable it is to listen and practice to your vids! You're doing a fantastic job and enrich every participant with the huge amount of learnings you offer us enthusiasts :)
I will use your learnings for my own db (also retrieved from yfinance). 2 questions occured, which I would like to ask you (your answers would be very much appreciated):
(1) When combining a database of Japanese or Chinese stocks with American or European stocks, the trading days are different. How do you personally go for database calculations like the 3M performance with stocks with diverging dates (used as indices)?
(2) Would it be possible to say sth like: I would like to store the price data from 2000-01-01 to 2021-12-01 and then only append the price data of that very day? Through the help of your video, it seems doable. What's your opinion on that?
Thank you so much for your great community contribution, Algo! :-)
Hey man, thank you so much for your kind words. That's truly awesome to read.
Regarding your first question:
Can't you do the calculation in kind of different "buckets"? So create a function pulling data and implement a conditional calculation?
Regarding the second one: Yes you can do that. I was covering daily updates of an example DataBase in my Stock recommendation playlist.
Thanks really helpful
Happy to read, thx mate!
Another great video..thank you! In case anyone gets stuck at 7:17..appending df1 with error message reading "database is locked"...just restart your PC and try again. Seems to fix the issue.
Yes that's an annoying error using sqlite. Thanks for sharing a possible solution! To avoid that in general as an additional solution just be sure you are not currently querying or are using the DB with another script. OR just use MySQL :-)
Works like butter.
Everyone needs a fresh start from time to time, even our machines
@@Algovibes I had the same error watching your videos back to back.
It took me some time to figure out i have to run connection.close() on previous notepad.
Thanks for the great work
You are the best
Thank you buddy
i did the "replace mistake" today! :D
RIP :D
Grate job. Go on
Thanks mate. I will do my best :-)
Thanks,your videos are great
What if want to add primary or foreign keys to the columns ?
Hi buddy, thanks a lot for you kind comment :-)
Is this covering your question?
stackoverflow.com/questions/38085624/pandas-create-foreign-id-column-based-on-name-column
9:14 How would u download the fundamental data of GME into the db?
thanks !
Thanks for watching :-)
Thank you! for sharing! But How can we establish a relationship(keys) in between the imported tables? Would really appreciate it!
Welcome! This is something you would do with SQL and not pandas (well you can execute SQL syntax with pandas tho) if I got your question right (adding e.g. primary keys).
You are providing very helpful content; I have been able to fix some issues in my codes thanks to you. I really appreciate what you are doing.
I have a question regarding resampling data if you do not mind assisting me on this please. what would be the best way to resample downloaded stock data from say 1m to 5m or 15m? Cheers
Thanks a lot for watching and your kind comment :-)
Apply this to your downloaded (1 minute) dataframe:
df.resample('5min').first()
Be kindly invited to provide me feedback if this solves your problem.
Best regards
@@Algovibes Hi, the code works. Thank you.
Another question if you don't mind, in case I would like to download multiple assets at once and save all of them to individually, to the same engine, how would you best run this.
for instance:
asset = 'AAPL', 'FB', 'TSLA'
Also when I run this code, all the assets appear all on the same rows & columns. can you view them separately
df = yf.download(asset,start='2021-04-06',end='2021-04-07',interval='1m')
df
How would you save all the assets in their respective engines?
df.to_sql('_______',engine)
Check out the most recent video on the channel. I am showing how I would do that there.
Hope this is solving your problem!
@@Algovibes Thank you very much for the new video, I have been trying to figure how to loop for the last 3 days but the way you doing is just awesome. I just finished working on the new video.
I like the way you write your codes and the thought process, do you have any recommendations on good materials eg books, links, etc? Your content is much better and quicker to grasp than what my lecturers offers
I honestly learned Python the hard way. I had some background in programming before but I started out with an university course and besides that I used some DataCamp. I was becoming proficient in Python by writing my masterthesis in it. The best way to learn is writing programs in it. I can only tell you it is a process which is taking time :-D
For the very basics I think we used Automate the boring stuff and Head first Python in university. I can personally recommend Sentdex as a role model of mine.
Hope this is helpful for you!
Thanks
Thanks for watching :-)
Hi, this was really helpful. I also wanted to know how to upsert data into sql server. Can you share some info about that? My requirement is, the table cannot be truncated at any point and therefore I cannot do truncate and load. I need to either update or insert the records into DB. Your suggestion is deeply appreciated.
Happy that it was helpful! :-) Thanks for watching and your interesting question regarding performing an upsert query.
Ad hoc I cannot answer it but this could help you out:
github.com/ryanbaumann/Pandas-to_sql-upsert
@@Algovibes Thank you! I will check the code. Appreciate your help!
Nice video and tutoria!. Do you know how I can do this with SQL Server? I don't really know what SQLlite and all this technical; stuff is, but I tried creating this on Spyder and I can't re-create the same results. I use SQL Server and SSMS. Your help would be greatly appreciated!
Edit: specifically, I am trying to recreate the part where you use yfinance to import the live ticker data into a SQL Server database that is made in SSMS
Hi mate, thanks a lot for watching and your comment. Sure, you can, I know this because I am also working with MSSQL.
Defining the engine is slightly different for MSSQL:
docs.sqlalchemy.org/en/14/core/engines.html
Thanks for the nice tutorial.
Is there a way to update rows without defining a SQL schema?
something like: df.to_sql('table', engine, if_exists='update)?
I do have a .csv files with +800 columns writing sql update statement is a pain i have seen the ubsert solution but it includes SQL as well.
Hi Felix,
yes, that's possible!
I have shown it in e.g. this video:
ua-cam.com/video/TJK-Ct9WnCw/v-deo.html
is this notebook available?
in matters of time and processing, is that way batter than using pyodbc?
Not really comparable imo. I am using a sqlite DB with sqlalchemy. As far as I know sqlite isn't compatible with pyodbc.
Thank so much. By the way I would like to creat a GUI that user can sign up and log in and have profiles of users to database with portfolio management. Please tell me if you have this course
Thank you for watching and your comment :-) May you please clarify what you mean with if I have this course? I don't get it :-D
Nevertheless he project sounds pretty nice! Would love to see it when you finalized it.
@@Algovibes Hi , I mean if you create a course that teach me how to solve my wish
I am getting "database is locked" error when I type "df1.to_sql('employees', engine, if_exists = 'append', index = False)". Any solution would be appreciated.
Delete the database and set it up again should do the job.
@@Algovibes Hi, getting the same issue too. I deleted and reset the database, the the error message saying database is locked once again. May I know any alternative solution? Appreciate the answer and help
Hello there, thank you for uploading! I am struggling with loading my pandas data frame with millions of rows into my Postgres database and I keep having issues! Would you be able to help me?
Hi and thank you very much for watching :-) In that case it is necessary to define a chunk size. Did you already give that a shot?
@@Algovibes yes I did! Nothing seems to load in the table but my csv file headers do load!
@@lilymaths4584 I need more information. You have a dataframe consisting of million rows right? And you are using the df.to_sql Syntax and define a chunksize like this: df.to_sql(„tablename“, engine, chunksize = 10000) ?
Did you already try to just write in a single row to see if that works out?
@@Algovibes hello algovibes, thank you for your help so much! My colleague helped me load the data in. It turns out the error was with an extra column being read in so we had to save the data as a csv and remove the pandas index away!
@@lilymaths4584 awesome :-)
Hi!!! Nice video … d’ont you have a "discord " community?? If yes please , the Access link…. Thanks!!!
Hi 👋
Thanks a a lot for you comment. Unfortunately, I haven't. Sorry!
HEY man, you are 100% a German :=), right?
🤫 Please don't tell anyone
@@Algovibes Diesen Akzent kriegt man aber auch nicht raus, wa? haha
@@nikeairforce9893 🥹
Are there no schemas? E.g. dbo.employees?
Good question! In sqlite there are no schemas. So the simple answer is: No. Instead of a schema you would need to create a new database file.
If you want to work with schemas I covered MySQL in my stock recommendation series, might be interesting for you:
ua-cam.com/video/qGAUw63p_uk/v-deo.html
@@Algovibes thank you