Column meta-data in MySQL Shell with Python mode

Meta-data is important to SQL DBA’s and Developers, likely for different reasons. There are all sorts of ways to access meta-data. The powerful MySQL Shell in Python mode is no exception. What if you need the column meta-data on a result set? You can use the get_columns() method and obtain useful meta-data information about it (the result set). All in Python mode. Continue reading to see examples…

parking garage red columns
Photo by Adrian Trinkaus on Unsplash

OS and DB used:

  • OpenSuse Leap 15.1
  • MySQL 8.0.20


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 from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!


I’ll retrieve a listing of all the tables in the Sakila Sample Database I have installed on my local MySQL instance, by calling the get_tables() method on the db object:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 MySQL  localhost:33060+ ssl  sakila  Py > db.get_tables()
[
    <table:actor>,
    <table:address>,
    <table:category>,
    <table:city>,
    <table:country>,
    <table:customer>,
    <table:film>,
    <table:film_actor>,
    <table:film_category>,
    <table:film_text>,
    <table:inventory>,
    <table:language>,
    <table:payment>,
    <table:rental>,
    <table:staff>,
    <table:store>,
    <table:actor_info>,
    <table:customer_list>,
    <table:film_list>,
    <table:nicer_but_slower_film_list>,
    <table:sales_by_film_category>,
    <table:sales_by_store>,
    <table:staff_list>
]

Now that we know what tables are present, I’ll choose just one to work with for the duration of the post. Let’s target the ‘staff’ table, using the get_table() method, retrieving all rows and columns with an unfiltered select() method call:

1
2
 MySQL  localhost:33060+ ssl  sakila  Py > staff = db.get_table('staff')
 MySQL  localhost:33060+ ssl  sakila  Py > rows = staff.select().execute()
(Tip: Visit the post, MySQL Shell CRUD with Python: Read – with examples, to learn how to use the select() method.)

The get_column_count() method returns the number of columns in a result set:

1
2
 MySQL  localhost:33060+ ssl  sakila  Py > rows.get_column_count()
11

On the other hand, get_column_names(), returns a Python list with all the columns names of an associated result set:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 MySQL  localhost:33060+ ssl  sakila  Py > rows.get_column_names()
[
    "staff_id",
    "first_name",
    "last_name",
    "address_id",
    "picture",
    "email",
    "store_id",
    "active",
    "username",
    "password",
    "last_update"
]

For a more manageable working data set, I will target only the ‘staff_id’ column of the ‘staff’ table, passing the table name as an argument to the select() method. I’ll assign that data to a ‘staff_id’ object as shown through the shell commands below:

1
2
3
4
5
6
7
8
9
 MySQL  localhost:33060+ ssl  sakila  Py > staff_id = staff.select('staff_id').execute()
 MySQL  localhost:33060+ ssl  sakila  Py > staff_id
+----------+
| staff_id |
+----------+
|        1 |
|        2 |
+----------+
2 rows in set (0.0007 sec)

On to the focus of the post: the get_columns() method

.

I’ll call get_columns() on the ‘staff_id’ object to access the in-built properties, further assigning them to a ‘columns’ variable:

1
 MySQL  localhost:33060+ ssl  sakila  Py > columns = staff_id.get_columns()

In the examples that follow, I demonstrate several common attributes that you may find useful. Be sure and refer to the official documentation I have linked to in the closing section of the post for more information on get_columns() along with general MySQL Shell Python mode in-depth information.

There is just one column available in the query results, therefore, that columns’ meta-data is found in the 0th index. Accessing the schema_name attribute, we can see what schema name – or database name if in the relational sense – the column is associated with:

1
2
 MySQL  localhost:33060+ ssl  sakila  Py > columns[0].schema_name
sakila

A table_name attribute returns – you guessed it – the associated table name:

1
2
 MySQL  localhost:33060+ ssl  sakila  Py > columns[0].table_name
staff

It is worth mentioning you can go out of range, as in the next example, attempting to access an unavailable list element. Since I queried only one column from table ‘staff’, there is no 2nd indicie in the list:

1
2
3
4
 MySQL  localhost:33060+ ssl  sakila  Py > columns[1].table_name
Traceback (most recent call last):
  File "<string>", line 1, in <module>
IndexError: list index out of range

The column_name list element stores the column name. Here is the column name as it is in the ‘staff’ table:

1
2
 MySQL  localhost:33060+ ssl  sakila  Py > columns[0].column_name
staff_id

This is way cool to me… Need to know the data type? That’s found in the type attribute:

1
2
 MySQL  localhost:33060+ ssl  sakila  Py > columns[0].type
<Type.TINYINT>

Cool… That column data type is an TINYINT… What is the length?

1
2
 MySQL  localhost:33060+ ssl  sakila  Py > columns[0].length
3

It’s all there as you can see.

As I showed in a prior example, the column_name attribute reflects the name of a column. But, there is also a similar column_label attribute. So happens, if the column name has been aliased, that alias name is found by accessing column_label:

1
2
 MySQL  localhost:33060+ ssl  sakila  Py > columns[0].column_label
staff_id

We can see that both column_name and column_label return the same name, ‘staff_id’, in the current context. However, if we alias the column name in the select() method call, that value is reflected in the column_label attribute.

To demonstrate, I’ll reassign the ‘columns’ variable after aliasing the ‘staff_id’ column with name, ‘some_staff’:
1
2
 MySQL  localhost:33060+ ssl  sakila  Py > staff_id = staff.select('staff_id AS some_staff').execute()
 MySQL  localhost:33060+ ssl  sakila  Py > columns = staff_id.get_columns()
(

I honestly had no idea you could even alias a column when supplying the column name(s) argument(s) to the select() method. MySQL Shell Python mode just gets better and better the more I explore it.

)

The column_label attribute now has…

1
2
 MySQL  localhost:33060+ ssl  sakila  Py > columns[0].column_label
some_staff

As you can see, column_label now reflects the column alias, ‘some_staff’. Quite impressive I must say…

Not all of the attributes (or list elements) for get_columns() were covered here so be sure and visit this helpful resource I found absolutely awesome X DevAPI User Guide for MySQL Shell in Python Mode for the official documentation on them all.

I’ve written several blog posts about the MySQL Shell, in both SQL and Python modes. Have a look at some of the posts for more information on MySQL Shell:

What use cases have you used get_columns() for? Tell me all about them in the comments below.

Like what you have read? See anything incorrect? Please comment below and thank you for reading!!!

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

Have I mentioned how much I love a cup of coffee?!?!

To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)

Be sure and visit the “Best Of” page for a collection of my best blog posts.


Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

Advertisements

Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.