Master Using SQL with Python: Lesson 11 - Using JSON with SQLite
HTML-код
- Опубликовано: 14 окт 2024
- In this video, you will learn how to create, store, and retrieve JSON objects using Python and SQLite. JSON is a very popular way to share data and SQLite has powerful features to store and query JSON. In this video, I'll show you how to get data from a website in JSON format, store it in a SQLite table, retrieve the JSON, and perform queries on it.
Slides and Code available at:
github.com/bca...
Download and Unzip the file.
My video on installing and using SQLite Studio
• Video
![VENEZUELA vs. ARGENTINA [1-1] | RESUMEN | ELIMINATORIAS SUDAMERICANAS | FECHA 9](http://i.ytimg.com/vi/SJfoPdeOPCQ/mqdefault.jpg)
Thanks for making this video, Bryan. It's the clearest example of working with JSON in SQLite using Python I've seen! Much appreciated!
Glad it helped!
I used to work with pandas - I hate it. Time to switch :) Thank you, I love your guidance.
Definitely would like more videos working with both JSON and REGEX in SQL
Yeah. Good topics! Thanks
i definitely like to see more about the json usage in sqlite/postgres with python. especially using twitter api v2. thanks for the series. it really helps. best.
Thanks Bryan! Add my vote to doing more sessions with Python/JSON --> into SQL Server, SQLite, & even Snowflake if you have the time!!! Thanks again for your great series!
exactly what I needed. Thanks Bryan!
YW. Check out the whole series. I think you may find it useful.
Thank you very much. Your explanations are very good
You're Welcome!
Would these efforts be this straightforward using JavaScript?
Well. It can be done using JavaScript but SQLite is built into Python and Python has a massive set of libraries for data analysis. I think Python is a stronger choice for data-centric work while JavaScript is the better choice for web/mobile apps. But either can be used in both cases.
Thanks Bryan!
Awesome video! Thanks so much…
Hi. Thanks for your great content.
I use sqlite3 version 3.34 with python 3.8.
I get error when using json extract:
cursor.execute('''select json_extract(data, '$.name', '$.iso2Code', '$.id') from countries;''').fetchmany(8)
OperationalError: no such function: json_extract
I used sqlite3.sqlite_version 3.37.0 and sqlite3.version 2.6.0.
I assume you are running this in the Jupyter Notebook I put in GitHub? If so, looks like a library versioning issue so upgrading the SQLite libraries may fix it.
@@BryanCafferky thanks. Yes i used that one.
By the way i tried to look for how to upgrade my sqlite but strangely i could not understand
@@BryanCafferky I fixed it. i upgraded and it works😎
@@SAliGhasempouri Awesome!
i get this issue when i try the panda lib
Execution failed on sql '
select json_extract(data, '$.id') as id,
order by id,
limit 5;': near "order": syntax error
Yep. It's a syntax error. I don't see a FROM clause and don't think the comma after order by id is needed. If that does not solve the issue I would reommend googling Python pnadas SQL jason_extract.
thank you. How can we json_extract a nested variable?
realize it can be done by json_extract(data, '$.var1.nested_var2') as var2. I wonder if we store the data in json format, will query be troublesome?
YW. See this blog. adatis.co.uk/parsing-nested-json-lists-in-databricks-using-python/
Why I'm getting "OperationalError: no such function: json_extract
" without doing anything on the code! Getting mad:)
Why are you getting mad?
@@BryanCafferky I found that SQLite package was not updated, so it was not able to call JSON functions. I solved installing the latest version.
Thanks a lot! Great video
@@paolorussi8739 Ok. The word "mad" threw me. I did not intend to anger people. But coding errors can happen from many causes and missing or out-of-date libraries is one. To solve errors, I had to get really good at googling. Calmly dealing with and solving bugs is a rare but essential skill to develop. Everybody hates hitting a bug but that is part of the work.
Thank you very much. You solved my problem ! :)
@@OrigamOx same here! thank you