How to count SQL table columns using Python?

It may be required to count the number of columns present in a SQL table.

This is done using count(*) function with information_schema.columns and the WHERE clause. The WHERE clause is used to specify the name of the table whose columns are to be counted.

Syntax

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

Steps to count columns in a table using MySQL in python

• import MySQL connector

• establish connection with the connector using connect()

• create the cursor object using cursor() method

• create a query using the appropriate mysql statements

• execute the SQL query using execute() method

• close the connection

Suppose we have a table named “Students” as below −

+----------+---------+-----------+------------+
|    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

We want to count the number of columns in the above table.

import mysql.connector

db=mysql.connector.connect(host="your host", user="your username", password="your
password",database="database_name")

cursor=db.cursor()

query="SELECT COUNT(*) FROM information_schema.columns WHERE table_name= "Students" "
cursor.execute(query)

col=cursor.fetchall()

for x in col:
   print(x)

db.close()

The above returns the number of columns present in the table named “Students”.

Output

4