import pandas as pd
import sqlite3
conn = sqlite3.connect('Db-IMDB.db')
imdb = pd.read_sql_query('''SELECT table_name FROM information_schema.tables''',conn)

but it is giving error:

DatabaseError: Execution failed on sql 'SELECT table_name FROM information_schema.tables LIMIT 20 ': no such table: information_schema.tables

any help would be great help

| improve this question | | | | |

There is no information_schema in SQLite as pointed out by @mustaccio.

However, you can get the information you require by using this SQL:

    type ='table' AND 
    name NOT LIKE 'sqlite_%';

See the link here. Tables which begin with sqlite_ are SQLite's system tables and are explained here. Remember that SQLite databases are single files with all of the data and metadata stored in that one file!

Anyway, there's another problem with your SQL - a LIMIT clause makes no sense without being preceded by an ORDER BY clause. Quite why one would want the first 20 of an alphabetical list of tables is not clear to me? Normally, LIMITs apply to numerical data - I'm not saying it's an absolute requirement, just puzzling, that's all!

If this is, as your comment suggests, an exploratory look at the data, then use the .output command from the sqlite3 client programme and get a list of all the tables (see here). Then you can use the more command to browse through this list of tables?

There are a few articles about how to ask questions here on my profile - you might want to take a look?

| improve this answer | | | | |
  • yes you are right LIMIT here doesn't make sense. Limit i have been using for some other querry. mistakenly typed in this. – Lalit Jain Jan 14 at 14:40
  • Verace i didn't understand you query written above. Since i don't know any table name which exists in database what would i write in name in your query – Lalit Jain Jan 14 at 14:44
  • @LalitJain - see my additions to my answer. – Vérace Jan 14 at 16:09
  • @ypercubeᵀᴹ - yes, but when one is looking at a dictionary, one generally knows the word one is searching for. It's like starting a dictionary search at a and moving down through the tome! I think browsing the entire table list as I've suggested in an edit is the way to go here? – Vérace Jan 14 at 16:11
  • 1
    Then please mark my answer as correct so that others with the same issue will be able to find the answer easily - answers marked correct rise to the top! Also, I will get a few points! :-) – Vérace Jan 14 at 18:27

Not the answer you're looking for? Browse other questions tagged or ask your own question.