Python: MYSQLdb. how to get column name without doing select * in a large table?

Question

I want to get the column names of the table, but there are more than a million data. Therefore, I can not use:

cursor.execute("SELECT * FROM table_name") print cursor.description 

And in sqlite3 I do it like this

 crs.execute("PRAGMA table_info(%s)" %(tablename[0])) for info in crs: print info 

But this does not work in python mysqldb. Does anyone know how to do this?

Answer 1

You can use SHOW columns :

 cursor.execute("SHOW columns FROM table_name") print [columns[0] for column in cursor.fetchall()] 

FYI, this is essentially the same as using desc :

 cursor.execute("desc table_name") print [columns[0] for column in cursor.fetchall()] 

Answer 2

The proper way to do this is to use "SHOW columns FROM table_name" , however you can also just add LIMIT to an existing query:

 cursor.execute("SELECT * FROM table_name LIMIT 0") print cursor.description 

Answer 3

Try

 cursor.execute("SELECT * FROM table_name LIMIT 1") 

or

 cursor.execute("SELECT * FROM table_name WHERE 1=0") 

Both prevent a huge amount of data. The second is perhaps more elegant. I just checked and even this works:

 >>>cursor.execute("SELECT LEFT(long_text,5) as short_text FROM table_name WHERE 1=0") >>>print cursor.description (('short_text', 253, 0, 5, 5, 31, 0),) 

Answer 4

 cursor.execute("SELECT * FROM table_name LIMIT 0") cursor.column_names 

Use the following information to find other information.

 [v for v in dir(cursor) if v.find("_")]