To manage tables in MySQL using Python, you'll use the mysql.connector module to connect to the MySQL server and execute SQL queries. Here’s how you can create a new table and modify its schema:
To create a new table named Employee in the PythonDB database with columns name, id, salary, and department_id, follow these steps:
import mysql.connector
# Create the connection object
myconn = mysql.connector.connect(
host="localhost",
user="root",
passwd="your_password",
database="PythonDB" # Specify the database name
)
# Create the cursor object
cur = myconn.cursor()
# SQL query to create the table
create_table_query = """
CREATE TABLE Employee (
name VARCHAR(255),
id INT AUTO_INCREMENT PRIMARY KEY,
salary DECIMAL(10, 2),
department_id INT
)
"""
# Execute the query
cur.execute(create_table_query)
print("Table 'Employee' created successfully.")
# Close the cursor and connection
cur.close()
myconn.close()
If you need to add a new column branch_name to the Employee table, you can use the ALTER TABLE statement. Here’s how to do it:
import mysql.connector
# Create the connection object
myconn = mysql.connector.connect(
host="localhost",
user="root",
passwd="your_password",
database="PythonDB" # Specify the database name
)
# Create the cursor object
cur = myconn.cursor()
# SQL query to alter the table
alter_table_query = """
ALTER TABLE Employee
ADD branch_name VARCHAR(255)
"""
# Execute the query
cur.execute(alter_table_query)
print("Column 'branch_name' added to table 'Employee'.")
# Close the cursor and connection
cur.close()
myconn.close()
• Creating the Connection: Use the mysql.connector.connect() with parameters like host, user, passwd, and database to connect to the specific database.
• Creating the Cursor Object: Use myconn.cursor() to create a cursor object for executing SQL queries.
• Creating the Table: Define the table schema using the CREATE TABLE statement and execute it with cur.execute().
• Altering the Table: Use the ALTER TABLE statement to modify the table schema, such as adding new columns.
• Closing Resources: Always close the cursor and connection after performing database operations.
This code provides a basic approach for creating and modifying tables. You may need to handle exceptions and manage other aspects of database interactions depending on your application needs.