How do you create a table in MySQL using Python?

How do you create a table in MySQL using Python, Also how do you handle if a table exists or does not exist?

To create a table in MySQL using Python, you can use the simple SQL basic table creation query.

Syntax:

# Create table query
create_table_query = """
CREATE TABLE table_name (
    column1_name column1_datatype,
    column2_name column2_datatype,
    column3_name column3_datatype
);
"""

# Execute the query
cursor.execute(create_table_query)

# Commit the changes
db.commit()

Complete Syntax:

import mysql.connector

# Establish connection to MySQL database
db = mysql.connector.connect(
  host="localhost",  # or the host where your MySQL server is
  user="your_username",
  password="your_password",
  database="your_database"  # Optional if you already have a database selected
)

# Create a cursor object
cursor = db.cursor()

# Create table query
create_table_query = """
CREATE TABLE table_name (
    column1_name column1_datatype,
    column2_name column2_datatype,
    column3_name column3_datatype
);
"""

# Execute the query
cursor.execute(create_table_query)

# Commit the changes
db.commit()

# Close the connection
cursor.close()
db.close()

Example:

Let's say you want to create a table named employees with the following columns:

• id (integer)
• name (varchar)
• age (integer)
• salary (float)

import mysql.connector

# Establish connection to MySQL database
db = mysql.connector.connect(
  host="localhost",  # or the host where your MySQL server is
  user="root",
  password="1234",
  database="eagleye"
)

# Create a cursor object
cursor = db.cursor()

# Create table query
create_table_query = """
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    salary FLOAT
);
"""

# Execute the query
cursor.execute(create_table_query)

# Commit the changes
db.commit()

# Close the connection
cursor.close()
db.close()

print("Table 'employees' created successfully.")

Output:

Table 'employees' created successfully.

---

 

How to handle when you are not sure table exit or not?
To handle the case where a table already exists, you can modify the CREATE TABLE query to include an IF NOT EXISTS clause. This ensures that the table is only created if it doesn't already exist in the database. Here's how you can implement it in Python:

Code with IF NOT EXISTS Approach:

import mysql.connector

db = mysql.connector.connect(
  host="localhost",  
  user="root",     
  password="1234", 
  database="eagleye"
)

cursor = db.cursor()

create_table_query = """
CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    salary FLOAT
);
"""

cursor.execute(create_table_query)

# Commit the changes
db.commit()

# Close the connection
cursor.close()
db.close()

print("Table 'employees' created successfully (if it didn't already exist).")

Output:

Table 'employees' created successfully (if it didn't already exist).

 

---

Some interview questions related to creating tables in MySQL using Python

Question 1. How do you connect to a MySQL database using Python?

Answer: To connect to a MySQL database using Python, you need to use the mysql-connector-python library. You can use the connect() method to establish a connection to the database by providing connection details like the host, username, password, and database name.

Example:

import mysql.connector

# Establish connection to MySQL database
db = mysql.connector.connect(
    host="localhost",
    user="root",
    password="1234",
    database="my_database"
)

# Print the connection status
if db.is_connected():
    print("Successfully connected to the database")

 

Question 2. How do you create a table in MySQL using Python?

Answer: To create a table in MySQL using Python, you can write a CREATE TABLE SQL query and execute it using a cursor. Use the execute() method of the cursor to run the query.

Example:

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="root",
    password="1234",
    database="my_database"
)

cursor = db.cursor()

# Create table query
create_table_query = """
CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    salary FLOAT
);
"""

# Execute the query
cursor.execute(create_table_query)

db.commit()  # Commit the changes to the database

cursor.close()
db.close()

 

 

Question 3. What is the significance of using IF NOT EXISTS in the CREATE TABLE statement?

Answer: The IF NOT EXISTS clause in the CREATE TABLE statement ensures that the table is created only if it does not already exist in the database. If the table already exists, it prevents an error from occurring.

Example:

create_table_query = """
CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    salary FLOAT
);
"""

 

 

Question 4. How do you handle exceptions while creating a table in MySQL using Python?

Answer: You can handle exceptions using a try-except block. The mysql.connector library throws errors as exceptions, so you can catch and display them if they occur during the execution of the query.

Example:

import mysql.connector
from mysql.connector import Error

try:
    db = mysql.connector.connect(
        host="localhost",
        user="root",
        password="1234",
        database="my_database"
    )

    cursor = db.cursor()

    create_table_query = """
    CREATE TABLE IF NOT EXISTS employees (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100),
        age INT,
        salary FLOAT
    );
    """

    cursor.execute(create_table_query)
    db.commit()

except Error as e:
    print(f"Error: {e}")

finally:
    if db.is_connected():
        cursor.close()
        db.close()
        print("MySQL connection is closed.")

 

 

Question 5. What are some common data types you would use when creating a table in MySQL with Python?

Answer: Common data types used in MySQL include:

• INT: Integer values.
• VARCHAR(length): Variable-length string, where length is the maximum number of characters.
• FLOAT: Floating-point numbers.
• DATE: Date in YYYY-MM-DD format.
• DATETIME: Date and time in YYYY-MM-DD HH:MM:SS format.

Example:

create_table_query = """
CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    salary FLOAT,
    join_date DATE
);
"""

 

 

Question 6. How do you check if the table already exists before attempting to create it?

Answer: You can check if a table exists in the database using the SHOW TABLES command, or you can use CREATE TABLE IF NOT EXISTS in your query, which checks for the existence of the table before creating it.

Example:

cursor.execute("SHOW TABLES LIKE 'employees'")
result = cursor.fetchone()

if result:
    print("Table 'employees' already exists.")
else:
    create_table_query = """
    CREATE TABLE employees (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100),
        age INT,
        salary FLOAT
    );
    """
    cursor.execute(create_table_query)
    print("Table 'employees' created.")

 

 

Question 7. How do you handle closing the database connection after creating the table in MySQL?

Answer: It’s important to close both the cursor and the database connection after executing the query to release resources.

Example:

cursor.close()  # Close the cursor object
db.close()      # Close the database connection

 

 

Question 8. How do you use the AUTO_INCREMENT feature in MySQL when creating a table?

Answer: The AUTO_INCREMENT feature is used to automatically generate unique values for a column, typically used for primary keys. This allows you to avoid manually specifying the value for that column when inserting records.

Example:

create_table_query = """
CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    salary FLOAT
);
"""

 

 

Question 9. What is the purpose of PRIMARY KEY in a table?

Answer: A PRIMARY KEY is a constraint that uniquely identifies each record in a table. It ensures that no two rows can have the same value in the primary key column(s). It also automatically creates a unique index on that column.

Example:

create_table_query = """
CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- id is the primary key
    name VARCHAR(100),
    age INT,
    salary FLOAT
);
"""