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

Pyodbc SQL CRUD – Read: Examples with MySQL

Reading the data in a database table is a fundamental SQL operation. I suppose you could just store the data and be done with it. But, where is the fun in that? To see stored data, you use the SELECT command. In the CRUD acronym, that is the Read aspect. I have written extensively on SQL CRUD operations in the past (see list of posts at the end of this piece). But, how do you read rows of data with pyodbc as the middleware (termed loosely here) between you and the database? Keep reading to see several simple examples…

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

Pyodbc SQL CRUD – Create: Examples with MySQL

In my day job as a Pipeline Survey Data Analyst, I lean heavily on the Python pyodbc package for interactions with an Access Database. To gain more knowledge and better proficiency with pyodbc, I thought to use it with one of my favorite open-source databases, MySQL. Having recently written a 4-part series on SQL CRUD operations using MySQL, this post is a continuation of sorts. However, Create operations are executed with the pyodbc driver instead of native SQL

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

Using the REFERENTIAL_CONSTRAINTS table in MySQL – Foreign Key Awareness

Using FOREIGN KEY‘s in database schema design assist in storing consistent, normalized, and sound data. Oftentimes, many tables wind up with many FOREIGN KEY constraints. However, keeping up with this (potential) maze of relationships doesn’t have to be a nightmare. Want to learn more? Keep reading…

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

Column ordering in MySQL using ALTER TABLE – with examples.

MySQL allows table columns to be placed in a specific location among the other present columns. You likely don’t care one iota about column ordering. But, if you do – and your table is already established – you can still have your cake and eat it too by using simple ALTER TABLE commands in conjunction with certain keywords and place those columns in a position to your liking. Continue reading to see example queries…

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