Python: Mysqldb. How To Get Columns Name Without Executing Select * In A Big Table?

I want to get the column names of a table, but there a over million data in it. So I cannot use: cursor.execute('SELECT * FROM table_name') print cursor.description And in sqlite3

Solution 1:

You can use SHOW columns:

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

FYI, this is essentially the same as using desc:

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

Solution 2:

The correct way to do this would be to use "SHOW columns FROM table_name" however, you could also simply add a LIMIT to your existing query:

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

Solution 3:

Try

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

or

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

Both prevent massive amounts of data being rattled. The second one is perhaps more elegant. I've 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),)

Solution 4:

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

Use the following to find other information

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

Solution 5:

data = pd.read_sql("""SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TABLE_NAME'""", conn)
print(data.COLUMN_NAME)

After creating the connection, using the information schema, we call pull the column names without extracting any observations.