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

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

2019 At a glance…

What a fantastic year it has been for me here on my blog. In my opinion, writing about a topic that interest you is one of the best ways to learn about it. Needless to say, I have learned a great deal throughout the year in my continued devotion to writing and SQL. Applying learned SQL knowledge – and Python – in my day job, inspired several posts with even more to come next year.

With the holidays upon us, I thought that it only fitting for the final post of 2019 to be a recap or best of all of my post. According to my websites’ analytics, these were the top 5 most visited posts this year and the one thing they all have in common is, none of them were written this year. All that said, enjoy!

  1. ALTER TABLE: Examples with MySQL – Beginner Series.
  2. Two handy examples of the psql \copy meta-command.
  3. Filtering SELECT queries with the WHERE clause in MySQL.
  4. MySQL SELECT INTO Syntax Part 2 – Saving to OUTFILE with examples
  5. Populating a MySQL table with the INSERT statement – Beginner Series.

Wherever you are in the world and no matter how you choose to celebrate, I wholeheartedly wish you and yours a safe and happy holiday season and prosperous new year.

See you in 2020 and thank you so much for reading my blog!!!

Pyodbc meta-data methods you should be using – interactions with MySQL.

In my day job, I have recently begun to leverage the Python pyodbc package for MS Access related database tasks. Working with any database, it goes without saying that understanding the schema is paramount. What tables are present? What are their columns and types? How are they related? Among the many methods pyodbc provides, to answer these types of questions, it provides 3 ‘meta-data’ methods you simply cannot live without. Want to know which ones they are? Keep reading…

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