Master Using SQL with Python: Lesson 11 - Using JSON with SQLite

[@danmisener]

Thanks for making this video, Bryan. It's the clearest example of working with JSON in SQLite using Python I've seen! Much appreciated!

[@BryanCafferky]

Glad it helped!

[@user-lg4le8xr4s]

Definitely would like more videos working with both JSON and REGEX in SQL

[@BryanCafferky]

Yeah. Good topics! Thanks

[@notterboutuyer2781]

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.

[@michaeleidson9210]

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!

[@georgwagner5577]

I used to work with pandas - I hate it. Time to switch :) Thank you, I love your guidance.

[@sherlockholmes676]

exactly what I needed. Thanks Bryan!

[@BryanCafferky]

YW. Check out the whole series. I think you may find it useful.

[@OrigamOx]

Thank you very much. Your explanations are very good

[@BryanCafferky]

You're Welcome!

[@Imaginativeone_DF]

Would these efforts be this straightforward using JavaScript?

[@BryanCafferky]

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.

[@izthed9117]

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

[@BryanCafferky]

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.

[@tiagocostadossantos6379]

Thanks Bryan!

[@SAliGhasempouri]

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

[@BryanCafferky]

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.

[@SAliGhasempouri]

@@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

[@SAliGhasempouri]

@@BryanCafferky I fixed it. i upgraded and it works😎

[@BryanCafferky]

@@SAliGhasempouri Awesome!

[@CarlosMoreno-vw4ir]

Awesome video! Thanks so much…

[@jiweihe3413]

thank you. How can we json_extract a nested variable?

[@jiweihe3413]

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?

[@BryanCafferky]

YW. See this blog. adatis.co.uk/parsing-nested-json-lists-in-databricks-using-python/

[@paolorussi8739]

Why I'm getting "OperationalError: no such function: json_extract " without doing anything on the code! Getting mad:)

[@BryanCafferky]

Why are you getting mad?

[@paolorussi8739]

@@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

[@BryanCafferky]

@@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.

[@OrigamOx]

Thank you very much. You solved my problem ! :)

[@jiweihe3413]

@@OrigamOx same here! thank you