0

I'm trying to get a list of columns from a database using python.

import sqlite3, os, sys
def list(self,search):
    conn = sqlite3.connect('a.db')
    c = conn.cursor()
    c.execute("SELECT sql 
               FROM sqlite_master
                WHERE tbl_name = '" + search + "' 
                     AND type = 'table'")
    return c.fetchall()

This doesn't return the column names, but instead returns the statements I used to create the table (which does include column names, but not as intended). (i.e. CREATE TABLE table (a, b, c, d PRIMARY KEY)) Can anyone explain why this is any give a better way to do this?

I've also tried PRAGMA statements, SELECT * FROM Table WHERE False, and SELECT * FROM tables.columns WHERE tbl_name = ' search ' but the above (from How to get a list of column names on sqlite3 / iPhone?) is the closest I've come to an answer as to returning column names. I NEED THE COLUMN NAMES NOT THE DATA INSIDE THE COLUMNS. I don't wish to download or use libraries not explicitly mentioned above. Is there a means of listing the column names and not the statements used to create the tables?

Community
  • 1
  • 1
NationWidePants
  • 447
  • 8
  • 33
  • Why did you re-ask this question? It is still a duplicate. – Martijn Pieters Oct 03 '14 at 14:46
  • It's not a duplicate, the post you pointed to doesn't answer the question I asked or even touch upon the question. They use a different library and list the data inside the columns/tables. I want column names. – NationWidePants Oct 03 '14 at 14:47
  • My answer there details *exactly* how you can use the pragma to get the column names. – Martijn Pieters Oct 03 '14 at 14:47
  • At the very least *tell us how my answer doesn't work for you* then. – Martijn Pieters Oct 03 '14 at 14:48
  • `cursor.execute('PRAGMA table_info({})'.format(table_name)`, then `[r[1] for r in cursor]` gives you a list of column names for that table. – Martijn Pieters Oct 03 '14 at 14:49
  • It doesn't matter that the other question is about SQL Alchemy, the same SQL query applies here. The iPhone question you linked to gives you the same information, but you don't tell us how the PRAGMA query doesn't work for you. – Martijn Pieters Oct 03 '14 at 14:50
  • I've tried your sample and I only get data, not the names of the tables. As I said, I've tried PRAGMA statements, many pragma statements with no success. – NationWidePants Oct 03 '14 at 14:50
  • The PRAGMA cannot *possibly* give you the data from the columns. It can *only* give you column names, column types, if they allow NULLs, the default if any, and if they are a primary key. – Martijn Pieters Oct 03 '14 at 14:51
  • Please then show us your attempt to use PRAGMA, what output you got, what output you expected instead. Give us a reproducable sample of your problem and we can help. Until that point, this remains a duplicate. – Martijn Pieters Oct 03 '14 at 14:52
  • 1
    Demo: https://gist.github.com/mjpieters/ddc8ea0a3dc41e07810e – Martijn Pieters Oct 03 '14 at 14:56
  • sorry, I don't know what I was doing wrong, but your solution worked in this last post. I swear I was doing the same thing, but I kept getting the column entries and not the columns. I also did `cursor.description[i][0]` which also returned the list, but your method is much less clunky. – NationWidePants Oct 03 '14 at 16:12

0 Answers0