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
OS, Software, and DB used:
- OpenSuse Leap 15.1
- MySQL 8.0.19
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:
- MySQL Shell CRUD with Python: Create – with examples
- MySQL Shell CRUD with Python: Read – with examples
- MySQL Shell CRUD With Python: Update – with examples
- MySQL Shell CRUD With Python: Delete – with examples
For starters, I’ll assign the current session connection information to a variable:
MySQL localhost:33060+ ssl learning Py > db.get_session()
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
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
MySQL localhost:33060+ ssl learning Py > session.sql(TBL_CREATE).execute()
Did it work?
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:
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:
MySQL localhost:33060+ ssl learning Py > cols = rows.get_column_names()
MySQL localhost:33060+ ssl learning Py > cols
(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
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:
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.
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.