Admittedly, I have jumped head-first into the world of JSON within MySQL. I also have a thing for Python, of which I find myself using a lot of in my day job as Pipeline Survey Data Analyst. CSV’s are everywhere: in data at work, on the web, even in my personal life (as you’ll see). Loading CSV data into MySQL is nothing new to anyone working with either of the technologies. As a matter of fact, there are several ways you can get your CSV data into MySQL. I wrote the post, Pyodbc SQL CRUD – Create: Examples with MySQL, you can read where I cover loading CSV data using the Python pyodbc module. But, how about type-casting CSV data – which typically are strings – to a compatible JSON data type? Then push the JSON data to a MySQL JSON column? And that my friends, is the focus of this post. Turns out, Python’s csv and json standard libraries makes the whole process virtually painless. Keep reading and see a simple script I devised…
JSON
SQL to JSON using the JSON_OBJECT() function in MySQL – with examples.
I have begun exploring JSON, the MySQL X Dev API, and the Document Store in earnest due to a requirement I am facing in my day job. The data model I am working with presents several challenges (don’t they all). Inspired in my own right by 2 fantastic books I am currently reading and working through: SQL Antipatterns: Avoiding the Pitfalls of Database Programming by Bill Karwin and Introducing MySQL Shell by Charles Bell. I am starting to see that JSON, the MySQL X Dev API, and the Document Store just might be my salvation. Based on my understanding gained from both books – for differing reasons – I have come to this conclusion. Both books have influenced my thinking in terms of different options, applicable to my particular data needs. In this post, I will go from SQL to JSON all within MySQL using built-in functionality….
[Keep reading for more SQL database and Python-centric content >>>]