In this tutorial, we’ll show how to perform Create MySQL Database and insert some data init using Python step by step.

1. Setup Required to connect MySQL

To set up the environment, we need to connect MySQL with Python, go through this tutorial to see how to connect MySQL using mysql connector.

If you follow along with the tutorial link, we have connected everything and have code below in our IDE.

mysqldb_connector.py
# Importing the MySQL-Python-connector
import mysql.connector as mysqlConnector
# Creating connection with the MySQL Server Running 
conn = mysqlConnector.connect(host='localhost',user='root',passwd='root')
# Checking if connection is made or not
if conn:
    print("Connection Successful :)")
else:
    print("Connection Failed :(")
# Creating a cursor object to traverse the resultset
cur = conn.cursor()
cur.execute("SHOW DATABASES")
for row in cur:
    print(row)
# Closing the connection
conn.close()

Output:

Connection Successful 🙂
(‘information_schema’,)
(‘mysql’,)
(‘performance_schema’,)
(‘sys’,)

So far so good, now we are able to connect with MySQL database, let’s create a database and table init.

2. Python MySQL Create:

2.1 Python Create Database

Let us create a database using the create database <db-name> query. Run the following Python code to create a database named cse according to our scenario. To verify we can use the show databases query as above.

mysqldb_connector.py
import mysql.connector as mysqlConnector
conn = mysqlConnector.connect(host='localhost',user='root',passwd='root')
if conn:
    print("Connection Successful :)")
else:
    print("Connection Failed :(")
cur = conn.cursor()
try:
    cur.execute("create database cse")
    print("Query Executed Successfully !!!")
except Exception as e:
    print("Invalid Query")
    print(e)
conn.close()

Output:

Connection Successful 🙂
Query Executed Successfully !!!

2.2 Creating a table

Now we want to use this database by setting the database argument to <dbname-name> (cse in our case) inside the connect() method and then create a table inside this database using create table <table-name> (<schema>). Run the following Python code to achieve the same

mysqldb_connector.py
import mysql.connector as mysqlConnector
conn = mysqlConnector.connect(host='localhost',user='root',passwd='root',database='cse')  
if conn:print("Connection Successful :)")
else:print("Connection Failed :(")
cur = conn.cursor()
try:
    cur.execute("create table students (std_id int, name varchar(100),number int, section varchar(100))")
    print("Query Executed Successfully !!!")
except Exception as e:
    print("Invalid Query")
    print(e)
conn.close()

Output:

Connection Successful 🙂
Query Executed Successfully !!!

3. References:

Happy Learning 🙂

About the Author:

Aspiring Data Scientist who loves Python Programming, Software Development and wants to Solve Real-world Problems.

Leave A Comment