How to count SQL table columns using Python?

Counting the number of columns in a SQL table is a common database administration task. Python provides an efficient way to do this using the MySQL connector and the information_schema.columns system table.

Syntax

The SQL query to count columns uses the information_schema.columns table ?

SELECT COUNT(*) FROM information_schema.columns WHERE table_name='your_table_name'

Steps to Count Columns

• Import MySQL connector

• Establish connection with the database

• Create cursor object using cursor() method

• Execute the column count query

• Fetch and display results

• Close the connection

Sample Table Structure

Let's assume we have a table named "Students" with the following structure ?

+----------+---------+-----------+------------+
|   Name   | Class   | City      | Marks      |
+----------+---------+-----------+------------+
|   Karan  |   4     | Amritsar  |   95       |
|   Sahil  |   6     | Amritsar  |   93       |
|   Kriti  |   3     | Batala    |   88       |
|  Khushi  |   9     | Delhi     |   90       |
|   Kirat  |   5     | Delhi     |   85       |
+----------+---------+-----------+------------+

Example Implementation

Here's how to count the columns in the Students table ?

import mysql.connector

# Establish database connection
db = mysql.connector.connect(
    host="localhost",
    user="your_username", 
    password="your_password",
    database="your_database"
)

cursor = db.cursor()

# Query to count columns in Students table
query = "SELECT COUNT(*) FROM information_schema.columns WHERE table_name='Students'"
cursor.execute(query)

# Fetch the result
result = cursor.fetchone()
column_count = result[0]

print(f"Number of columns in Students table: {column_count}")

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

Output

Number of columns in Students table: 4

Alternative Method Using DESCRIBE

You can also count columns using the DESCRIBE statement ?

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password", 
    database="your_database"
)

cursor = db.cursor()

# Alternative method using DESCRIBE
cursor.execute("DESCRIBE Students")
columns = cursor.fetchall()
column_count = len(columns)

print(f"Number of columns: {column_count}")

cursor.close()
db.close()

Key Points

• The information_schema.columns table contains metadata about all columns

• Always close database connections to free up resources

• Use fetchone() when expecting a single result

• Handle database connection errors with try-except blocks in production code

Conclusion

Counting SQL table columns in Python is straightforward using MySQL connector and the information_schema.columns table. This method works reliably across different MySQL database versions and provides accurate column counts for any table.