How to get columns' name from a table in sqlite3 database using python3?

Question

I already have read some answers on this. But all of them are giving me the same error.

Here are the solutions I read:

Link 1

import sqlite3 as sql

#connect to database
connection = sql.connect("database.db")

#make a cursor which will move in the database
cursor = connection.cursor()

#execute the different command
def execute(cursor, command):
    return cursor.execute(command)

#print the result
def print_result(result):
    for var in result:
        print(var)
# select columns' name from table

command = """select distinct emplyee from emplyee.information_schema.columns"""

result = execute(cursor, command)
print_result(result)

The table name is emplyee.

Error is: Traceback (most recent call last):

File "database.py", line 47, in

result = execute(cursor, command)

File "database.py", line 11, in execute

return cursor.execute(command)

sqlite3.OperationalError: near ".": syntax error

What database are you using? You tagged MySQL and SQL Server, but the code indicates SQLite. — David Browne - Microsoft, Jul 09 '17 at 16:54
I am using SQLITE3 but i didn't know that they are different. I thought that basic commands must be same — Adarsh Maurya, Jul 09 '17 at 16:57

score 6 · Accepted Answer · answered Jul 09 '17 at 17:03

6

SQLite doesn't support the information_schema, so you need to do something like this:

def table_columns(db, table_name)
    curs = db.cursor()
    sql = "select * from %s where 1=0;" % table_name
    curs.execute(sql)
    return [d[0] for d in curs.description]

answered Jul 09 '17 at 17:03

rd_nielsen

2,304
2
10
18

It worked. But can u pls tell me what does 1=0 does here. – Adarsh Maurya Jul 09 '17 at 17:17
@AdarshMaurya it prevents the query from returning any data. – Shadow Jul 09 '17 at 17:19
The expression `1=0` is not true for any row. Anyway, SQLite computes result rows on demand, so it's not needed. – CL. Jul 09 '17 at 17:20
The criteria of 1=0 does not select any rows because you aren't trying to get data; all you want is the table description that is in the cursor object. There's no point in wasting time selecting data that you don't need. – rd_nielsen Jul 09 '17 at 17:21

score 0 · Answer 2 · answered May 15 '21 at 17:26

Does the same thing but with more modern syntax. (You don't need to use cursors with execute() in sqlite3.)

import sqlite3

def get_col_names(file_name: str, table_name: str) -> List[str]:
    conn = sqlist3.connect(file_name)
    col_data = conn.execute(f'PRAGMA table_info({table_name});').fetchall()
    return [entry[1] for entry in col_data]

How to get columns' name from a table in sqlite3 database using python3?

2 Answers2