Python MySQL: Creating Tables

After creating a database, you can create tables to store your data. Tables in MySQL are created using the CREATE TABLE statement. This section demonstrates how to create a table using Python.

Example

In this example, we will create a table named students with columns for id, name, age, and grade. 


import mysql.connector

# Establishing a connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='yourpassword',
    database='student_db'
)
cursor = connection.cursor()

# Creating a table
cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    grade CHAR(2)
)
""")
print("Table created successfully")

# Show tables in the database
cursor.execute("SHOW TABLES")
tables = cursor.fetchall()
print("\nTables in the database:")
for table in tables:
    print(table[0])

# Describe the structure of the 'students' table
cursor.execute("DESCRIBE students")
table_structure = cursor.fetchall()

connection.close()
print("\nConnection closed.")

Output

Table created successfully Tables in the database: students Structure of 'students' table:
Field Type Null Key Default Extra
id int NO PRI NULL auto_increment
name varchar(50) YES NULL
age int YES NULL
grade char(2) YES NULL
Connection Closed.

Explanation of Code Example

In this example: - We connected to the MySQL database named student_db.
- We created a table called students with columns id, name, age, and grade.
- The id column is set as the primary key with auto-increment functionality.
- We used SHOW TABLES to list all tables in the database and DESCRIBE students to show the structure of the students table.
- The structure of the table includes information like column names, data types, whether a column can be NULL, keys, default values, and extra details.