Creating The Table

Creating The Table

Creating database tables is one of the first steps when developing applications that store structured information. In this tutorial, we will create an Employee table inside a MySQL database named PythonDB and then modify its structure by adding a new column.

The examples use Python with the mysql.connector library, allowing SQL commands to be executed directly from a Python program.

Creating the Employee Table

For this example, the Employee table initially contains four fields. Each field has a specific purpose for storing employee information:

• name: Stores the employee’s name as text.
• id: Stores a unique employee identifier and works as the primary key.
• salary: Stores the employee’s salary value.
• Dept_Id: Stores the department ID associated with the employee.

The following SQL statement creates the table:

CREATE TABLE Employee (
    name VARCHAR(20) NOT NULL,
    id INT PRIMARY KEY,
    salary FLOAT NOT NULL,
    Dept_Id INT NOT NULL
);

Creating the Table with Python

Instead of executing the SQL command manually, Python can connect to the MySQL database and create the table programmatically.

Example: Creating the Employee Table

import mysql.connector

# Create the connection
myconn = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="google",
    database="PythonDB"
)

# Create the cursor
cur = myconn.cursor()

try:
    cur.execute("""
        CREATE TABLE Employee (
            name VARCHAR(20) NOT NULL,
            id INT PRIMARY KEY,
            salary FLOAT NOT NULL,
            Dept_Id INT NOT NULL
        )
    """)
    print("Table Employee created successfully.")

except Exception as e:
    print("Error:", e)
    myconn.rollback()

finally:
    myconn.close()

The program first establishes a connection with PythonDB. A cursor is then created to execute the SQL statement. If the table is created successfully, a confirmation message is displayed. If an error occurs, the exception is handled and the connection is closed in the finally block.

Checking Whether the Table Was Created

After running the Python program, you can check the available tables in the database by using:

SHOW TABLES;

This command displays the tables available in the selected MySQL database. If the operation was successful, Employee will appear in the result.

Modifying the Employee Table

Database requirements can change while an application is being developed. A table may need an additional field without recreating the entire table.

For example, suppose we want to store the branch associated with each employee. We can add a branch_name column to the existing table using the ALTER TABLE statement.

SQL Command to Add a New Column

ALTER TABLE Employee
ADD branch_name VARCHAR(20) NOT NULL;

This statement extends the existing table by adding the branch_name field.

Adding branch_name Using Python

The same schema modification can be performed through Python using the MySQL connector.

import mysql.connector

# Create the connection
myconn = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="google",
    database="PythonDB"
)

# Create the cursor
cur = myconn.cursor()

try:
    cur.execute(
        "ALTER TABLE Employee ADD branch_name VARCHAR(20) NOT NULL"
    )
    print("Column branch_name added successfully.")

except Exception as e:
    print("Error:", e)
    myconn.rollback()

finally:
    myconn.close()

Here, Python connects to the same database and executes the ALTER TABLE command. Once the operation finishes, the database connection is closed properly.

Understanding the Main SQL Commands

• CREATE TABLE: Creates a new table and defines its columns.
• PRIMARY KEY: Identifies each record uniquely.
• NOT NULL: Prevents a column from receiving a NULL value.
• ALTER TABLE: Changes the structure of an existing table.
• ADD: Adds a new column to an existing table.
• SHOW TABLES: Displays tables available in the selected database.

Important Points

Complete Advance AI Topics: Click Here
SQL Tutorial: Click Here
YT:- DecodeIT

When working with MySQL through Python, make sure the database name, username, password, and server details match your local configuration. The cursor is responsible for executing SQL statements, while the connection object manages communication with MySQL.

Using try-except-finally also makes the program easier to manage because errors can be handled without leaving the database connection open.

Frequently Asked Questions

1. How do I create a table in MySQL using Python?

Connect to MySQL using mysql.connector, create a cursor, and execute a CREATE TABLE SQL statement through cur.execute().

2. What is the purpose of the Employee table?

The Employee table in this example stores employee information such as name, ID, salary, and department ID.

3. Why is the id column a primary key?

The id field is defined as the primary key so that each employee can have a unique identifier.

4. How can I add another column to an existing MySQL table?

Use the ALTER TABLE statement with ADD. For example, ALTER TABLE Employee ADD branch_name VARCHAR(20) NOT NULL;.

5. Which Python library is used to connect MySQL?

The example uses the mysql.connector library to establish the database connection and execute SQL commands.

6. How can I check whether a MySQL table exists?

The SHOW TABLES; command displays the tables available in the selected database.

Conclusion

Creating and modifying MySQL tables through Python provides a practical way to manage database structures from application code. In this guide, we created an Employee table containing name, ID, salary, and department information, verified the table using SHOW TABLES, and then used ALTER TABLE to introduce a new branch_name column.

These commands provide a useful foundation for building Python applications that work with MySQL databases and dynamically manage structured data.

Keywords: creating a table in MySQL using Python, create table in SQL, create table in MySQL Python, Python MySQL create table, mysql.connector create table, CREATE TABLE Python, ALTER TABLE Python, MySQL Employee table, Python database operations, create table in DBMS