To Interact with MySQL Database

3 Ways to interact with MySQL Database

Content: In this article, discussing about scenarios for What, Why, When and How with MySQL Database.

What: MySQL is a RDBMS software that lets us store or retrieve data that are stored in tabular format that uses SQL (Structured Query Language) to interact with database and its tables.

Why: We need RDBMS and SQL for developing and deploying full-stack web applications or softwares and data related projects in all most all sectors/industries today.

When: Briefing about use-cases of MySQL in the below two scenarios are:

i) Full-stack web applications (integration of both front-end and back-end programming technologies) or standalone software to perform CRUD operations to store and retrieve data.

CRUD - Create, Read, Update & Delete

ii) Exploratory Data Analysis with SQL

How: On how to use MySQL, its required tools (softwares and libraries) are detailed in the below explaination.

Tools are: MySQL Server, Python3, MySQL Shell & MySQL Workbench and their installation.

Installation: Pre-requisite is to install MySQL Server then connect to it and create database, create table and insert data to retrieve. Python needs to be installed for Method-2 as below. This server installation is the pre-requisite for interacting with MySQL database and executing its statements.

I have installed MySQL Server 8.0.12 which is compatible with my OS Mac i.e., High Sierra. As a note please check the server version to be compatible with your OS. Community versions of MySQL Server, Shell, Workbench are available at https://dev.mysql.com/downloads/

MySQL Workbench is a GUI application to interact with MySQL database.

Here I have curated 3 ways to interact/communicate with the MySQL database to store or retrieve data from tables locally i.e., with development machine (laptop/desktop).

In this article I have explained on Method-1 at high-level and in detail with Python language using Method-2 for CRUD operations.

In the next article will walk you through main CRUD operations for beginner level Data Analysis with MySQL Shell along with its SQL syntaxes & execution.

Note: Once the server is installed, then start the server to write & execute SQL statements in Shell or Editor.

To start & connect to the server via terminal on MAC or PC:

After installing the server, next connect to server by clicking on System Preferences (from Apple icon on top-left)-> MySQL (at the bottom row) -> Start MySQL Server button then it would prompt you to enter your machine’s (laptop/desktop) password, once you finish entering it, that would start the server on.

OR you can use ‘mysqld’ command to start MySQL Server from PC and MAC both. Below is the method I generally follow on MAC to start this server. Current directory/folder location in the terminal/cmd should not matter as we install this server globally in the machine!

MySQL Window

3 Ways to interact/communicate with MySQL database:

Method-1: Interact with MySQL shell, in MAC or PC — we can use Terminal or CMD respectively to execute SQL commands.

Method-2: To interact with this server/database using Python programming language, you need to install third-party Python library as below and import it accordingly in the .py file.

$pip3 install mysql-connector

Note: I have worked with VS Code editor, You can also choose Atom or editor of your choice to write these Python scripts and Python can be installed from here for Method-2 https://www.python.org/downloads/

Method-3: With MySQL Workbench (Graphical User Interface) software, here I have added instructions with this software. There is another option i.e., phpMyAdmin which is web-browser based interface to interact with MySQL database.

Now let’s dive into writing SQL statements and scripts using Method-1 & Method-2:

Method-1: For starting MySQL Shell from MAC (OS X), to write and execute SQL statements for CRUD operations:

For the terminal of Mac machine and type in the below commands:

$/usr/local/mysql/bin/mysql -u root -p

Type the above command in the terminal to enter to the MySQL Shell mode for executing SQL statements. Below is the screenshot with the above command and its desired output.

MySQL Shell activated once executed the commands `/usr/local/mysql/bin/mysql -u root -p’ and hit enter and enter your password you would have given during the installation of MySQL Server.

Note: When you install the MySQL server onto your machine, the default user name would be ‘root’ and password can be skipped (optional). I have provided the password during the installation thus I need to enter in anytime when I need to connect to the server.

However, MongoDB Shell can be directly opened in the PC by typing in ‘mongodb shell’ from start menu or as below.

To start MySQL shell from PC (Windows OS) once the server has been started using mysqld command, below will be the screenshot with desired output for Shell. As a note you have to start the server in a separate cmd window and use another cmd tab to enter into MySQL Shell mode!

To start MySQL Shell from PC.

Basic statement execution to do once connected to server, such as to check the list of existing databases, selecting required database and displaying its existing list of tables.

Please not that MySQL statements require semicolon (;) at the end of each statement to get executed.

Code snippet with Command to enter MySQL Shell mode and SQL statement to display existing Databases.

Here ‘JobPortal’, ‘SportsFifaDB’ are the databases I have created, rest of them are created by default.

To select/switch to the database to be worked with:

$ use SportsFifaDB;

To display the list of tables in the above database:

$ show tables;

Input of SQL statement and its respective outputs in MySQL Shell.

Method-2: To connect to MySQL database using Python. It requires below module/library to be installed to connect to MySQL Database if not yet done so using ‘pip’. Remember to start the server as detailed above with System Preferences or mysqld command.

(‘pip’ is a Python’s package manager)

$pip3 install mysql-connector

Below are screenshots for Python scripts to perform Read, Create, Update and Delete operations with Python. These are the four main operations used for Database actions. Next import the ‘mysql-connector’ module as below to get to connect to the database and to perform executions.

I have created super user and password for the database ‘SportsFifaDB’ using MySQL Workbench and need to provide the ‘auth_plugin’ attribute for the version I have used here.

Once finished with the sql executions, to free-up the memory, it’s advised to close the cursor object and database connection.

Note: ‘pprint’ is a Python’s standard library to pretty print the output.

Below are the code snippets with all the necessary modules and required methods/functions in a sequence to perform SQL executions for CRUD operations with Python and these to be written in a file and saved as .py, for example crud.py (based on if you organize your code all-in-one file or each statement in individual .py file):

READ operation from CRUD actions with Python to fetch top 5 rows/records.

READ/Fetch all players with nationality from Spain.

CREATE operation from CRUD actions with Python.

UPDATE operation in CRUD actions with Python.

DELETE operation in CRUD actions with Python.

Note: To install package, use ‘mysql-connector’ but to import ‘mysql.connector’, please note the differences. And anytime when you make changes to the database, it needs to be commited to it explicitly using ‘db.commit()’ statement as in the above snippets. However it’s not required for ‘select’ statement!

Method-3: To connect to the database instance of MySQL server using the GUI software MySQL Workbench, however remember to start/turn on the server as shown above from System Preferences or mysqld prior to connect!

When you open the MySQL Workbench, below is the screen pops-up so select the local instance and ‘Connect to MySQL Server’ window pops-up as seen and enter the password if you have provided at the time of installation and hit OK button.

MySQL Workbench home screen.

Next below screen would appear, navigate between ‘Administration’ and ‘Schemas’ tabs accordingly. As an example, you can see below screenshot with mysql_queries.sql tab that allows SQL statements execution in Workbench too.

This screen appears once you connect to MySQL instance from the above screen.

In short, with MySQL Workbench, you can connect to MySQL Database as well as execute SQL statements, if you prefer GUI software as an option to Shell commands.

Using this interface, super user for MySQL database can be created too!

Summary: Once you have done the scripts or developments locally in the machine, especially for full-stack applications and database related CRUD statements and performed testing, it would be ready to deploy to the cloud or traditional server - based on your organization’s infrastructure provision.

Thank you for reading…!