2

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

Here are the solutions I read:

  1. Link 1

  2. Link 2

    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

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
Adarsh Maurya
  • 348
  • 1
  • 4
  • 14

2 Answers2

7

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]
rd_nielsen
  • 2,407
  • 2
  • 11
  • 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
  • imho this answer is slightly more superior because it is very specific to the query. if in your query you only have considered some of the columns, this will give your exactly those. – Mehrad Mahmoudian May 03 '23 at 17:36
0

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]
rbasham
  • 191
  • 1
  • 5