Creating Tables using Python MySQL

In this article, we are going to learn how to create tables in databases using Python MySQL.

Introduction to Tables:

Generally, databases are used in order to store the information in the form of tables so that the stored information seems crystal clear and can be retrieved easily. In MySQL, a database is a collection of several tables. These tables consist of rows and columns, where the rows store the data and columns indicates the category of the data in general. So, on the whole, these rows and columns are used to categorize the data on a smoother note.

According to the sense of coding, the tables can be assumed to be classes. Similar to the Inheritance of the classes, tables can be linked to each other using the concept of Inheritance. Multiple tables can be linked to each other by columns that are present in the headers.

Everything inserted in a row is considered to be a piece of information entered, and that inserted information is known as Record. The information stored in a row is always associated with its respective column.

Integrated Connection of Python and MySQL:

We connect the database to the Python application using MySQL connector API. API refers to Application Programming Interface, which is used to connect the Programming language to the database using specific drivers that support both Programming language and database.

Here, we use Python API since we are going to implement Python MySQL. API is almost similar to a Satellite that helps in sending information and supports the gadgets in order to get the information. API acts like a mediator to build a database connection. To understand more briefly, API is a bridge between the front-end Python and back-end Database server.

What is the logic for creating a Connection?

The Python application will send a connection request to API. Then the request sent by the Python application is directly forwarded to the Database by API. Further, if the request seems to be genuine, the database will accept it and then grant the required permissions. After the completion of request approval, the database sends a successful connection message to API, and that message is again forwarded to the Python application.

This is a series of actions where API handles everything, hence known to be a mediator. This process is then followed by a request which is sent by python to implement the cursor. Here, the cursor can be considered as an object ( also known as an instance of MySQLcursor class ), and connection can be considered as a method which allows you to communicate with the MySQL server and also create your own database.  

You need to install the MySQL connector package in order to create a connection, and after that, create tables within the database. Let us discuss every step in the program.

Program to connect MySQL database server and Python:

# install and import mysql connector 
import mysql.connector
# initiating the connection to the database 
database = mysql.connector.connect(
    host = " localhost ", username = " scott ", password = " tiger "
)
# printing the connection message
print(database)

Output of the above Program:

<mysql.connector.connection_cext.CMySQLConnection object at 0x000001A33420D6A0>


Process finished with exit code 0

An explanation for the above Program:

This output indicates the connection message. But, in order to verify whether the connection is performed successfully or not, we need to go through the program below.

Program to confirm a successful connection of MySQL database server and Python:

# install and import mysql connector 
import mysql.connector
# initiating the connection to the database 
database = mysql.connector.connect(
    host = " localhost ", username = " scott ", password = " tiger "
)
# printing the connection message
print(database)


# confirmation message of successful connection
if(database):
    print(" Hey!! Your connection is Successful ")
else:
    print(" Sorry!! Your connection is unsuccessful ")

Output of the above Program:

<mysql.connector.connection_cext.CMySQLConnection object at 0x000001A33420D6A0>


Hey!! Your connection is Successful 


Process finished with exit code 0

An explanation for the above Program:

The output indicates the successful connectivity between the MySQL database server and the Python application. As the connection is built successfully, it entered the “ if “ block. If the connection isn't produced clearly, then it enters the else block, which indicates an unsuccessful connection.

In order to create tables, we need to establish a connection between the database server and the Python application and then create a new database. We have already gone through the establishment of a database connection to Python. Now, let us study the concept of creating a new database.

Note: In general, tables can be created in already existing databases, also. But, keeping the free memory space and permissions acceptancy in mind, we prefer creating new databases so that everything can be controlled and accessed.

Creation of new Database:

As soon as the MySQL connector driver is installed and imported successfully, and right after the database connection is established, we will be able to create a new database. Few methods are supposed to be used in order to convey the creation message to the Python application. After the connection is successfully established, creating a new database is very easy and can be done by following the queried syntax of " creation of database ".

SQL Query to create a new database:

CREATE DATABASE new_database_name;

In SQL, queries are ended with a semicolon at the end of every statement. But, in Python application, we do not use semicolons at the end of the statements. In order to overcome this, we use the cursor() method followed by the execute() method. The execute method in the Python program converts the given query into a String, understands the message from the query and then performs the operation that the query actually does.

Syntax of cursor() method by creating an object( or instance of MySQLcursor class ):

# creating an instance of MySQLcursor class and initiating the connection using cursor method implying to that object


cursor = connection.cursor()

Syntax of execute() method, which is called by cursor object:

# linking the statement “ db_query “ to the execute method using a cursor object in order to convert it into a string and then identify that particular string as a query
cursor.execute(db_query)

Close the database connection:

The connection to the database should be closed immediately after completing the desired operation to prevent unauthorized access. We use the close() method to stop the connection temporarily.

Syntax of close() method: 

# to close the connection or access to that particular database
db_name.close()

Let us understand the creation of a database through the program written below.

Program that determines all the steps to create a new database:

# install and import mysql connector 
import mysql.connector
# initiating the connection to the database 
database = mysql.connector.connect(
    host = " localhost ", username = " scott ", password = " tiger "
)
# creating an instance of MySQLcursor class and initiating the connection using the cursor method, implying that object
cursor = connection.cursor()


print("Creating a new database:") 
# database creation query being stored within a member so that it can be further converted into String
db_query = " CREATE DATABASE new_db "
print("Database of name new_db is created successfully !!") 


# linking the statement “ db_query “ to the execute method using a cursor object in order to convert it into a string and then identify that particular string as a query
cursor.execute(db_query)


# to close the connection or access to that particular database
database.close()

The output of the above Program:

Creating a new database: 
Database of name new_db is created successfully !!


Process finished with exit code 0

This output indicates the successful creation of a new database. To check whether the database is created or not within the system, we also have another way.

Program that shows all databases present in the system:

# install and import mysql connector 
import mysql.connector
# initiating the connection to the database 
database = mysql.connector.connect(
    host = " localhost ", username = " scott ", password = " tiger "
)
# creating an instance of MySQLcursor class and initiating the connection using the cursor method, implying that object
cursor = connection.cursor()


print(" The databases present in the system are: ") 
# query, which shows all databases that are present within the system being stored within a member so that it can be further converted into String
db_query = " SHOW DATABASES "


# linking the statement “ db_query “ to the execute method using a cursor object in order to convert it into a string and then identify that particular string as a query
cursor.execute(db_query)


# printing all databases present in the system
for x in cursor:
    print(x)


# to close the connection or access to that particular database
database.close()

Output of the Program:

The databases present in the system are : 
(' hello_world ',)
(' mysql ',)
(' mid_portion ',)
(' sem_portion ',)
(' sub_marks ',)
(' new_db ',)

With this output, we can confirm that our database " new_db " has been created successfully. After the creation of the database is successful, our next step is to create tables inside the database.

Creation of a Table within the newly created database:

When the database is created, we generally have more than one database within the system. So, the question arises within the program, i.e. in which database should the table be created. In order to overcome this, we need to mention the name of the newly created database within the program.

Mentioning the name of the database in the connection section:

# initiating the connection to the database 
database = mysql.connector.connet(
    host = " localhost ", username = " scott ", password = " tiger ", database = "database_name"
)

This step declares that the table must be created inside the mentioned database. The database is mentioned within the connection section, so it will be compulsorily executed as the connection plays a major role throughout the program. 

Query to create a table in the database:

CREATE TABLE table_name (variable1 datatype1, variable2 datatype2, variable3 datatype3, ........... , variablen datatypen );

Example:

CREATE TABLE new_table(id int, name varchar(20), loc varchar(20));

Syntax to create a table in the database:

database_query = " CREATE TABLE table_name(variable1 datatype1, variable2 datatype2, variable3 datatype3, ........... , variablen datatypen )"
# to convert the query into a string and then identify that particular string as a query
cursor.execute(database_query)

Example:

query = " CREATE TABLE new_table (id int, name varchar(20), loc varchar(20) ) "

cursor.execute(query)

Program that determines the creation of Table in a database:

# install and import mysql connector 
import mysql.connector
# initiating the connection to the database 
database = mysql.connector.connect(
    host = " localhost ", username = " scott ", password = " tiger", database = "new_db"
)
# creating an instance of MySQLcursor class and initiating the connection using the cursor method, implying that object
cursor = connection.cursor()


print("Creating a new table:") 
# query of creation of a table is being stored within a member so that it can be further converted into a String
db_query = " CREATE TABLE employee ( id int, name varchar(20), loc varchar(20), salary int ) "


# linking the statement “ db_query “ to the execute method using a cursor object in order to convert it into a string and then identify that particular string as a query
cursor.execute(db_query)


print("Table of name employee is created successfully in the new_db database !!") 


# to close the connection or access to that particular database
database.close()

The output of the above Program:

Creating a new table: 
Table of name employee is created successfully in the new_db database!!


Process finished with exit code 0

This output is a partial confirmation of the successful creation of a table within the database " new_db ". In order to have complete confirmation, we need to see all the tables that are present in the database using the " show " query, just like the one we made for showing databases.

Program that shows all tables present in the database:

# install and import mysql connector 
import mysql.connector
# initiating the connection to the database 
database = mysql.connector.connect(
    host = " localhost ", username = " scott ", password = " tiger", database = "new_db"
)
# creating an instance of MySQLcursor class and initiating the connection using the cursor method, implying that object
cursor = connection.cursor()


print("Showing all tables in the database:") 
# query of creation of a table is being stored within a member so that it can be further converted into a String
db_query = " SHOW TABLES "


# linking the statement “ db_query “ to the execute method using a cursor object in order to convert it into a string and then identify that particular string as a query
cursor.execute(db_query)


# printing all the tables present in the database
for x in cursor:
    print(x)


# to close the connection or access to that particular database
database.close()

The output of the above Program:

Showing all tables in the database: 
(employee,)
Process finished with exit code 0

The output defines that the table is created successfully. As we have created the table within the new database, we found only one table in the output. If we perform this program on already existing databases, we may find more than one table in the output.