CREATE TABLE using Python in the MySQL Shell – with examples.

Not only are basic SQL CRUD operations supported in MySQL Shell Python mode, but you can also execute DDL commands such as CREATE TABLE. While there is not a specific create_table() method per se, there is a sql() method for this – and other – types of commands that don’t have an individual method such as those for each of the individual CRUD operations. Keep reading to see a simple example use of the sql() method…

round wooden table
Photo by Suhyeon Choi on Unsplash

OS, Software, and DB used:

  • OpenSuse Leap 15.1
  • MySQL 8.0.19

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!

MySQL Shell CRUD with Python Series

Visit any of the below posts if you’re interested in learning more about basic SQL CRUD with MySQL Shell in Python mode:

For starters, I’ll assign the current session connection information to a variable:

1
2
3
4
5
 MySQL  localhost:33060+ ssl  learning  Py > db.get_session()
<session:j2112o@localhost:33060>

 MySQL  localhost:33060+ ssl  learning  Py > session = db.get_session()
Query OK, 0 rows affected (0.8175 sec)

Also, I’ll store the CREATE TABLE DDL command in a variable, so that it can easily be passed as an argument to the sql() method:

1
 MySQL  localhost:33060+ ssl  learning  Py > TBL_CREATE = "CREATE TABLE name_dates(id int, some_date date)"

Then, I call the sql() method, supplying the ‘TBL_CREATE’ variable, followed by the execute() method:

1
 MySQL  localhost:33060+ ssl  learning  Py > session.sql(TBL_CREATE).execute()

Did it work?
Let’s see…

Passing in the ‘name_dates’ table name string to the get_table() method, I store that information in an ‘n_dates’ object. Calling an unfiltered select() on ‘n_dates’, I can then access the rows to get the column names:

1
2
 MySQL  localhost:33060+ ssl  learning  Py > n_dates = db.get_table('name_dates')
 MySQL  localhost:33060+ ssl  learning  Py > rows = n_dates.select().execute()

We can see the 2 columns of table ‘name_dates’ below:

1
2
3
4
5
6
 MySQL  localhost:33060+ ssl  learning  Py > cols = rows.get_column_names()
 MySQL  localhost:33060+ ssl  learning  Py > cols
[
    "id",
    "some_date"
]

(An Aside: I feel like there has to be an easier – and quicker – way to do this. If anyone knows how, feel free to comment below and point me in that directions.)

And finally, I use that ‘cols’ variable in the insert() method:

1
2
3
4
5
6
7
8
 MySQL  localhost:33060+ ssl  learning  Py > n_dates.insert(cols).values(1,'2020-02-24').execute()
Query OK, 1 item affected (0.2184 sec)
 MySQL  localhost:33060+ ssl  learning  Py > n_dates.insert(cols).values(2,'2020-02-24').execute()
Query OK, 1 item affected (0.2286 sec)
 MySQL  localhost:33060+ ssl  learning  Py > n_dates.insert(cols).values(3,'2020-02-20').execute()
Query OK, 1 item affected (0.2126 sec)
 MySQL  localhost:33060+ ssl  learning  Py > n_dates.insert(cols).values(4,'2020-02-19').execute()
Query OK, 1 item affected (0.1938 sec)

Shown below are the present rows:

1
2
3
4
5
6
7
8
9
10
 MySQL  localhost:33060+ ssl  learning  Py > n_dates.select().execute()
+----+------------+
| id | some_date  |
+----+------------+
|  1 | 2020-02-24 |
|  2 | 2020-02-24 |
|  3 | 2020-02-20 |
|  4 | 2020-02-19 |
+----+------------+
4 rows in set (0.0009 sec)

Using just Python in the MySQL Shell, we have created a table and inserted some rows of data into it. Check out all the awesomeness MySQL Shell in Python mode has to offer the next time you log in. It is a fantastic, alternative way to work with MySQL…

Like what you have read? See anything incorrect? Please comment below and thanks 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.

2 thoughts on “CREATE TABLE using Python in the MySQL Shell – with examples.”

  1. Pingback: Basic Data Analysis with MySQL Shell Python mode - Digital Owl's Prose
  2. Pingback: MySQL Shell Python mode blog posts compilation

Hey thanks for commenting! Leave a Reply

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