- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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
Advertisements