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
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]
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.
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
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
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:
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]
It is very easy.
First create a connection , lets name it, con.
Then run the following code.
cur =con.cursor()
cur.execute("select * from table_name limit 1")
col_name=[i[0] for i in cur.description]
print(col_name)
You will get column name as a list
Well, I may be very late to answer this but since people still follow this thread, I just wanted to share how I use to get the list of column names in python sqlite3.
import sqlite3
def getVarList(con, tableName)
return [fields[1] for fields in con.execute(f"PRAGMA table_info({tableName})").fetchall()]
conn = sqlite3.connect('foo.db')
varList = getVarList(conn, 'bar')
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)
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.
Since the question has a python flag. I feel free to post a python specific answer with pandas:
import sqlite3
import pandas as pd
path_to_db = 'path/to/db'
connect = sqlite3.connect(path_to_db, isolation_level=None)
table = 'table_name'
column_list = list(pd.read_sql_query(f"SELECT * FROM {table} limit 1", connect).columns)
import sqlite3
with sqlite3.connect('statics.db') as cur:
cur.execute("CREATE TABLE IF NOT EXISTS data(id INT PRIMARY KEY NOT NULL)")
pragmas = cur.execute("PRAGMA table_info(data);")
columns = [n for _, n, *_ in pragmas.fetchall()]
print(columns)
Another way of using pragma:
> table = "foo"
> cur.execute("SELECT group_concat(name, ', ') FROM pragma_table_info(?)", (table,))
> cur.fetchone()
('foo', 'bar', ...,)
If you are fine with using pandas, I recommend using pandas.read_sql_query, which will give you back a full DataFrame including the column names as header; this also nicely formats to stdout.
import pandas as pd
import sqlite3
with sqlite3.connect("my_db_file.db") as conn:
df = pd.read_sql_query("SELECT * FROM foo", conn)
print(df) # use df.columns.tolist() for only the header
