❮ Previous Next ❯

Creating Database and Tables in MySQL

  • Create MySQL databases and tables programmatically using Python.

  • What is a Database?

    A database is a place to store data in an organized way.
     Example: Student records, Employee details, Product list.

    Create Database Using Python

    We use Python’s mysql.connector to create a database in MySQL.

Create a Database in MySQL Using Python

This code connects Python to MySQL and creates a new database called college_db. 

import mysql.connector

# Connect to MySQL server (no database specified yet)
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root"
)

# Create a cursor object to execute SQL queries
cursor = conn.cursor()

# SQL command to create a database
cursor.execute("CREATE DATABASE college_db")

print("Database created successfully")

# Close the connection
conn.close()

  • Show All Databases

    We can also list all databases to check if our database is created.

Show Databases in MySQL Using Python

This code retrieves all databases from MySQL. 

import mysql.connector

# Connect to MySQL server
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root"
)

cursor = conn.cursor()

# Execute SQL query to show databases
cursor.execute("SHOW DATABASES")

# Print all databases
for db in cursor:
    print(db)

# Close connection
conn.close()

  • Creating Tables :  

    What is a Table?

    A table is like an Excel sheet where we store data in rows and columns.

    • Rows → Each row is a record (e.g., a student)

    • Columns → Each column is a field (e.g., name, age, course)

    Topic 2: MySQL Data Types

    When creating a table, we must specify the type of data each column will store.

    Data Type

    Use

    Example

    INT

    Integer numbers

    22, 100

    VARCHAR(size)

    Text

    'Hinal', 'MCA'

    CHAR(size)

    Fixed-length text

    'A', 'B'

    FLOAT

    Decimal numbers

    12.5, 99.99

    DATE

    Date

    '2026-01-10'

    DATETIME

    Date + Time

    '2026-01-10 12:30:00'

    BOOLEAN

    True/False

    1 (True), 0 (False)

    Important: Always choose the correct data type for efficiency.

    CREATE TABLE Syntax

    CREATE TABLE table_name (

        column1 datatype constraints,

        column2 datatype constraints,

        column3 datatype constraints,

        ...

    );


    table_name → Name of your table
    column1, column2, ... → Column names
    datatype → Data type (INT, VARCHAR, etc.)
    constraints → Optional (PRIMARY KEY, NOT NULL, UNIQUE)

    Create a Table in Python

Create Students Table in MySQL Using Python

This code connects Python to MySQL and creates a table called students in the college_db database. 

import mysql.connector

# Connect to MySQL and select database
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root",
    database="college_db"
)

cursor = conn.cursor()

# SQL command to create table
cursor.execute("""
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    course VARCHAR(50),
    enrollment_date DATE
)
""")

print("Table 'students' created successfully")

conn.close()

  • Explanation:

    • id INT AUTO_INCREMENT PRIMARY KEY → Unique ID for each student

    • name VARCHAR(50) → Student name (text, max 50 characters)

    • age INT → Student age (integer)

    • course VARCHAR(50) → Course name

    • enrollment_date DATE → Date of enrollment

    We can check if our table is created:

Show Tables in MySQL Using Python 

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root",
    database="college_db"
)

cursor = conn.cursor()
cursor.execute("SHOW TABLES")

for table in cursor:
    print(table)

conn.close()
❮ Previous Next ❯