In this series of tutorial, we’ll be learning how to connect to a database in Python using the MySQL Connector library, execute SQL commands and perform the basic CRUD (Create, Read, Update, Delete) operations.

In this first part we will learn how to Connect to the MySQL Database and how to Create A Database Using Python

Prerequisites

• Python3 installed
• A basic knowledge of Python3
• An IDE or at least a text to write codes (IDLE is readily available when installing Python)
• MySQL ConnectorLibrary
• A Visual Database Viewer would be good (optional)

Step One: Connecting to the MySQL Server

To start open the your IDE or text editor (I will be using Python’s IDLE) and type the following code:
import mysql.connector
This will import modules from the MySQL Connector library
We’ll start by creating a connection to the MySQL Server:
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="password",
)

host would usually be localhost, it is actually the name of the host of your database

user would usually be root, it is actually the admin username of your database

passwd would be the password to your database

Checkpoint 1:

Step Two: Create Database
(Method 1)

Now that we have made the connection to the MySQL Server, we will now create a database. We will add the following two line of codes:
mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE test")

Checkpoint 2:

You may have noticed that I have used the .cursor() method. It is a method which is used to communicate to the entire MySQL Server.

Since it has been assigned to mycursor now we will use the mycursor to execute the our SQL Statement using the .execute() method. The second line of code Creates a Database with the name of mydatabase. Here, we have already written the name of the database in the code.

Create Database
(Method 2)

Now let’s take the name of the Database as an input from the user and assign it to a variable.
mycursor = mydb.cursor()
name = input("Name of the database:")
sql = "CREATE DATABASE " + name
mycursor.execute(sql)
print( name + " has been successfully created" )

Checkpoint 2:

Using the input() method the program allows the user to input the name to be given to the database which is assigned to the variable name. It displays a message (in this case ” Name of the database: “ ) to indicate the user what is expected from him.

Then we have assigned the SQL statement CREATE DATABASE to the variable sql and I have concatenated the input name (name) using the + sign

Then the .execute() method is used to execute the MySQL statement.

Step Three: Add Database name to connection

Add the following code to mydb :
database="test"

Checkpoint 3:

Now the program will connect to the database test.