Is there a way to get a list of column names in sqlite

Question

I want to get a list of column names from a table in a database  Using pragma I get a list of tuples with a lot of unneeded information  Is there a way to get only the column names  So I might end up with something like this       Column1  Column2  Column3  Column4    The reason why I absolutely need this list is because I want to search for a column name in the list and get the index because the index is used in a lot of my code   Is there a way of getting a list like this   Thanks

User · Answer

Assuming that you know the table name  and want the names of the data columns you can use the listed code will do it in a simple and elegant way to my taste    import sqlite3  def get col names     this works beautifully given that you know the table name     conn   sqlite3 connect  t db       c   conn cursor       c execute  select   from tablename       return  member 0  for member in c description

User · Answer

I use this   import sqlite3      db   sqlite3 connect    foo sqlite       dbc   db cursor       dbc execute  PRAGMA table info  bar        ciao   dbc fetchall        HeaderList        for i in ciao          counter 0         for a in i              counter  1             if  counter  2                   HeaderList append a   print HeaderList

User · Answer

Another way of using pragma    gt  table    foo   gt  cur execute  SELECT group concat name        FROM pragma table info       table     gt  cur fetchone     foo    bar

User · Answer

You can use sqlite3 and pep-249  import sqlite3 connection   sqlite3 connect    foo sqlite   cursor   connection execute  select   from bar     cursor description is description of columns  names   list map lambda x  x 0   cursor description     Alternatively you could use a list comprehension   names    description 0  for description in cursor description

User · Answer

As far as I can tell Sqlite doesn t support INFORMATION SCHEMA  Instead it has sqlite master   I don t think you can get the list you want in just one command  You can get the information you need using sql or pragma  then use regex to split it into the format you need  SELECT sql FROM sqlite master WHERE name  tablename     gives you something like  CREATE TABLE tablename          col1 INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL          col2 NVARCHAR 100  NOT NULL          col3 NVARCHAR 100  NOT NULL      Or using pragma  PRAGMA table info tablename     gives you something like  0 col1 INTEGER 1  1 1 col2 NVARCHAR 100  1  0 2 col3 NVARCHAR 100  1  0

User · Answer

Quick  interactive way to see column names  If you re working interactively in Python and just want to quickly  see  the column names  I found cursor description to work   import sqlite3 conn   sqlite3 connect  test-db db   cursor   conn execute  select   from mytable   cursor description   Outputs something like this      Date   None  None  None  None  None  None      Object-Name   None  None  None  None  None  None      Object-Count   None  None  None  None  None  None     Or  quick way to access and print them out   colnames   cursor description for row in colnames      print row 0    Outputs something like this   Date Object-Name Object-Count

User · Answer

It is very easy  First create a connection   lets name it  con  Then run the following code   get column names con execute  select   from table name limit 1   col name  i 0  for i in get column names description  print col name    You will get column name as a list

User · Answer

I m not a sqlite user  so take this with a grain of salt  most RDBM s support the ANSI standard of the INFORMATION SCHEMA views   If you run the following query   SELECT   FROM INFORMATION SCHEMA columns WHERE table name    your table    You should get a table which lists all of the columns in your specified table   IT may take some tweaking to get the output you want  but hopefully its a start

User · Answer

You can get a list of column names by running   SELECT name FROM PRAGMA TABLE INFO  your table    name       tbl name   rootpage   sql   You can check if a certain column exists by running   SELECT 1 FROM PRAGMA TABLE INFO  your table   WHERE name  sql   1   Reference   https   www sqlite org pragma html pragfunc

User · Answer

An alternative to the cursor description solution from smallredstone could be to use row keys     import sqlite3 connection   sqlite3 connect    foo sqlite   connection row factory   sqlite3 Row cursor   connection execute  select   from bar     instead of cursor description  row   cursor fetchone   names   row keys     The drawback  it only works if there is at least a row returned from the query    The benefit  you can access the columns by their name  row  your column name     Read more about the Row objects in the python documentation

User · Answer

I like the answer by  thebeancounter  but prefer to parameterize the unknowns  the only problem being a vulnerability to exploits on the table name   If you re sure it s okay  then this works   def get col names cursor  tablename          Get column names of a table  given its name and a cursor         or connection  to the database              reader cursor execute  SELECT   FROM     format tablename       return  x 0  for x in reader description     If it s a problem  you could add code to sanitize the tablename

[python] Is there a way to get a list of column names in sqlite?

Examples related to python

Examples related to database

Examples related to sqlite