Although my day job has lately been filled primarily with MySQL and PHP development – which I absolutely enjoy – I still make spare time for MySQL and Python hacking; particularly in the MySQL Shell. For whatever reason, I am drawn back to this specific environment. SAVEPOINT, COMMIT, and START TRANSACTION are integral commands for working within a database transaction. MySQL Shell fully supports these commands. We can even execute them in Python mode! Continue reading to see them in action…
#Writing
SQL CRUD Compilation
SQL CRUD is everywhere in the database world. Be it application development or ad-hoc queries in analysis, there is a strong chance you are executing one of the CRUD commands: Create, Read, Update, or Delete. Having written several blog posts in the past on MySQL CRUD, I feel you the readers deserve an easy means to access them all. Therefore, consider this post the MySQL CRUD Compilation post. Enjoy!
[Keep reading for more SQL database and Python-centric content >>>]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…
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…
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…