As an example for my class on the usefulness of Common Table Expressions (CTEs), I created three examples with Python. They extend an exercise in Chapter 9 on subqueries from Learning SQL by Alan Beaulieu. All of the examples work with the sakila sample database.

These bullets describe the examples:

1. Uses local variables and a range for loop and if statement that uses the variables to evaluate and add an element to the derived table (or query result set) from MySQL.
2. Uses a CTE with substitution variables from the Python program, which eliminates the need to evaluate and add an element to the query result set because the query does that.
3. Uses a table to hold the variables necessary to evaluate and add the element to the query result set.

This is the first Python program:

# Import the library.
import sys
import …

When you use MySQL Shell with extra Python plugins (like these available on GitHub), sometimes, you could need extra Python modules.

Some of my plugins require requests and prettytable for example.

MySQL 8.0.26 uses embedded Python 3.9 and if your system doesn’t have that version (like Oracle Linux 8), you won’t be able to install the missing module.

So how could we install the missing modules ? The easiest method is to use PIP, but if you don’t have PIP for Python 3.9 installed on the system, this will be more complicated…. not really !

This how to install PIP in MySQL Shell:

wget https://bootstrap.pypa.io/get-pip.py
mysqlsh --py -f get-pip.py 

And now you can use PIP to install extra modules:

mysqlsh --pym pip …

    Pandas is a Python software library for data analysis and manipulation.  It allows you to import data from CSV files, SQL, and Excel.  And it is open source.

    Pandas works well with the MySQL Shell.  After installing Pandas with pip install pandas on my Fedora test system, I was able to load Pandas.  The documentation is excellent at pandas.pydata.org   and I was quickly able to get one of their examples working from within mysqlsh.

Did I mention SQL as a source of data? Reading from MySQL is very easy.  

…

Sometimes clients want that the information they collected from the user should be encrypted and stored in a database. Data encryption and…

Continue reading on Thinkdiff »

…

Tweet

There was an excellent question from Francis on my MySQL X DevAPI tutorial on how to work with NULL values:

Is it possible to store an attribute with the null value, and how to find it (xxx is null ?

It may sound like a trivial question, but the answer is not all that simple, so instead of just replying directly to the comment, I think it is worth covering the subject of NULL values in the X DevAPI in a separate post.

The Short Story

In short there are two ways to work with “not a value” in the X DevAPI. One way is to not set a value for those fields in which case you can …

These days, I mostly program in PHP with MySQL as the database, which is just fine by me. I have had a long-time interest in MySQL (SQL in general), and after several steady months of programming in PHP, I must say I have really come into the language and developed a fondness for it. All that being said, I still enjoy using and learning the MySQL Shell in Python mode. As Database Developers, we often need the LAST INSERT ID value from a previous INSERT statement on a column that has the AUTO_INCREMENT attribute. MySQL Shell has a get_auto_increment_value() method we can call against a Shell object result and retrieve that value. Continue reading and see examples of the MySQL Shell get_auto_increment_value() method used in Python mode…

…

I recently watched a fantastic Python Pandas library tutorial series on YouTube. Without a doubt, Pandas is great for all sorts of data stuff. On the same token, MySQL Shell in Python mode is quite powerful in the sense that Python and the MySQL Shell (version >= 8.0) are somewhat united in the same environment. Although Pandas is in a league all its own when it comes to data analysis, between the power of MySQL and Python, we can also perform some basic analysis easily in MySQL Shell Python mode. In this blog post, I will cover some basic data analysis using Python mode in the MySQL Shell. Continue reading to see examples…

Business vector created by freepik – www.freepik.com

OS, Software, and DB used:

• OpenSuse Leap …

Over the last few months, I have written numerous blog posts on different features of the MySQL Shell ranging from basic CRUD to aggregate functions and DDL. As a part of the MySQL version 8 release, MySQL Shell is a powerful and alternative environment that you can manage and work with your data in using a choice of 3 languages: Python, Javascript, or SQL. So this blog post is a simple compilation of all the Python mode related posts, in one easy-to-access location…

Photo by Tamara Gore on Unsplash

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit …

We all know as SQL professionals that a common use of the ALTER TABLE command is that we can change a tables’ structure in a myriad number of ways. And, that’s a good thing too because chances are, you won’t always nail down the initial structure. Due to changing business or application requirements, you may even have to add additional columns that were not considered during the schema design phase. Suppose you have many tables that are structured similarly and they all need a specific column added to their already-existing design. Under certain circumstances, using the MySQL Shell in Python mode (\py), can reduce the number of manual ALTER TABLE statements you have to type. Continue reading to see examples in the MySQL Shell…

Photo by elCarito …

Dear MySQL users,

MySQL Connector/Python 8.0.21 is the latest GA release version of the
MySQL Connector Python 8.0 series. The X DevAPI enables application
developers to write code that combines the strengths of the relational
and document models using a modern, NoSQL-like syntax that does not
assume previous experience writing traditional SQL.

To learn more about how to write applications using the X DevAPI, see

http://dev.mysql.com/doc/x-devapi-userguide/en/

For more information about how the X DevAPI is implemented in MySQL
Connector/Python, and its usage, see

http://dev.mysql.com/doc/dev/connector-python

Please note that the X DevAPI requires at least MySQL Server version 8.0
or higher with the …