python sqlite3 query the table name and columns name of .db file

Question

I have a .db file to be processed. However, I have no idea of this file, including its table name and columns name, which makes me unable to execute the select operation.
What I use is the sqlite3 in python.

Answer 1

You can find table names by querying the sqlite_master table.

con = sqlite3.connect('database.db')
cursor = con.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
print(cursor.fetchall())

This is going to print out all of the tables in your db file.

To get the table structure of each, you need to execute the following for each table returned above:

pragma table_info('<tablename>')

This will return a table with the following structure:

• cid => Column ID
• name => Name of column
• type => The data type of the column
• notnull => Boolean value of whether the column can be null or not
• dflt_value => Default value of the column
• pk => Boolean value of whether the column is in the primary key