Connect to MySQL Server and Create Database Using Python

In this 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 (optional)

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	# Import modules from the MySQL Connector library

# Creating a connection to the MySQL Server:
mydb = mysql.connector.connect(
host="localhost",	# Change it to your MySQL Host - can be IP 
user="root",	# Can use root or Best practice: create another User with limited access only for the Application
passwd="password",	# Replace "password" with the declared above User's Password
)

Checkpoint 1:

Create Database (Method 1)

Now that we have made the connection to the MySQL Server, we will now create a databasetest. 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: