CSV to JSON with Python for MySQL upload

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…

[Keep reading for more SQL database and Python-centric content >>>]

CREATE TABLE using Python in the MySQL Shell – with examples.

Not only are basic SQL CRUD operations supported in MySQL Shell Python mode, but you can also execute DDL commands such as CREATE TABLE. While there is not a specific create_table() method per se, there is a sql() method for this – and other – types of commands that don’t have an individual method such as those for each of the individual CRUD operations. Keep reading to see a simple example use of the sql() method…

[Keep reading for more SQL database and Python-centric content >>>]

MySQL Shell CRUD With Python: Delete – with examples

Throughout the MySQL Shell CRUD with Python series, we’ve visited specific methods to 1) Populate tables with new data through the use of insert(). 2) Read and see the existing data with select(). 3) Modify the present data using update(). In this post, I’ll cover examples of removing rows of data in MySQL with the Python Shell delete() method. As with other DML commands, the affected rows are dependent upon using the where() method. Keep reading to learn how…

[Keep reading for more SQL database and Python-centric content >>>]

MySQL Shell CRUD With Python: Update – with examples

Using MySQL Shell in Python mode to CREATE and SELECT records are straight-forward processes using the insert() and select() methods respectively. However, we are fully aware that seldom is the case in which some of that data does not change. So how do we modify or change the data we have on-hand? In SQL, UPDATE takes care of that. We can also apply the same UPDATE command in MySQL Shell Python mode with the update() method. Just as in SQL, you use the SET clause, so too do you use it with update() by way of the set() method. Keep reading to see examples of these methods combined with the where() method for specific, targeted updates…

[Keep reading for more SQL database and Python-centric content >>>]

MySQL Shell CRUD with Python: Read – with examples

In MySQL Shell CRUD with Python: Create – with examples, I visited the insert() method, demonstrating how simple it is to add new rows of data to a table using Python in the MySQL Shell. Now that the data is stored, if we want to retrieve any of it – for reading – we need to SELECT it, right? Luckily, there is a select() method available we can use in Python mode in the shell, making this operation relatively simple. But, as simple as it is, the power lies in the combinations of other similar class methods used for filtering. Interested? Keep reading… [Keep reading for more SQL database and Python-centric content >>>]