Creating The Table

Creating the Table

In this tutorial, we will cover the process of creating a new table named Employee in the PythonDB database. Additionally, we’ll learn how to modify the table schema when updates are required. This practical guide demonstrates SQL commands alongside Python code, ensuring seamless integration for developers working with MySQL.

Download New Real Time Projects :-Click here

Creating the Employee Table

The Employee table will initially consist of four columns:

name: Stores the name of the employee (VARCHAR).
id: Unique identifier for each employee (INT, primary key).
salary: Stores the salary of the employee (FLOAT).
Dept_Id: Represents the department ID of the employee (INT).

We use the SQL CREATE TABLE statement to define the table structure. Here’s the query:

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

Let’s implement this using Python:

Example: Creating the Employee Table

import mysql.connector  

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

# Create the cursor object  
cur = myconn.cursor()  

try:  
    # Creating the Employee table with four columns  
    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()

Verifying the Table Creation

Once the code executes successfully, the Employee table will be created in the PythonDB database. You can verify this by running the following SQL query in your MySQL client:

SHOW TABLES;

Modifying the Employee Table

Sometimes, you may need to update the schema of an existing table. For example, we might need to add a new column named branch_name to the Employee table.

The SQL ALTER TABLE statement is used to modify table structures. Here’s the query to add the branch_name column:

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

Example: Adding a New Column

Here’s how to implement this schema update using Python:

import mysql.connector  

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

# Create the cursor object  
cur = myconn.cursor()  

try:  
    # Adding a new column branch_name to the Employee table  
    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()

Summary

In this guide, we:

Created the Employee table in the PythonDB database with four columns: name, id, salary, and Dept_Id.
Verified the successful creation of the table.
Used the ALTER TABLE statement to add a new column branch_name to the table.

PHP PROJECT:- CLICK HERE

insert into table sql
create table in sql with primary key
how to create a table in sql
create table and insert data sql
create table student details in sql
how to create a table in excel with multiple columns
create table as select sql server
create student table in sql and insert values
creating the table oracle
creating the table in sql
creating the table in dbms
creating the table in sql server

Post Views: 739