SUBSTRING_INDEX() function in MySQL – with examples.

I recently used the MySQL SUBSTRING_INDEX(), function for a requirement in a PHP/MySQL reporting dashboard I am developing, and thought to write about SUBSTRING_INDEX() general usage. Although this post has arbitrary examples, I’m sure you can find a use for this function in specific cases. Continue reading and see basic queries including SUBSTRING_INDEX in the SELECT clause…

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

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

MySQL DATE Calculations using INTERVAL – with examples

Recently in my day job, while developing the back-end of a reporting dashboard with PHP and MySQL, I noticed some interesting differences in DATE math calculations. The examples used in this post are purely arbitrary but stem from lessons learned, therefore, I feel they are definitely worth sharing…

[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 >>>]