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…
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() |
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.
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:
- Transaction in MySQL Shell – Python mode with examples
- CREATE TABLE using Python in the MySQL Shell – with examples.
- SQL to JSON using the JSON_OBJECT() function in MySQL – with examples.
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.