Open database files (.db) using python

Question

I have a data base file .db in SQLite3 format and I was attempting to open it to look at the data inside it. Below is my attempt to code using python.

    import sqlite3

    # Create a SQL connection to our SQLite database
    con = sqlite3.connect(dbfile)

    cur = con.cursor()

    # The result of a "cursor.execute" can be iterated over by row
    for row in cur.execute("SELECT * FROM "):
    print(row)

    # Be sure to close the connection
    con.close()

For the line ("SELECT * FROM ") , I understand that you have to put in the header of the table after the word "FROM", however, since I can't even open up the file in the first place, I have no idea what header to put. Hence how can I code such that I can open up the data base file to read its contents?

Answer 1

So, you analyzed it all right. After the FROM you have to put in the tablenames. But you can find them out like this:

SELECT name FROM sqlite_master WHERE type = 'table'

In code this looks like this:

# loading in modules
import sqlite3

# creating file path
dbfile = '/home/niklas/Desktop/Stuff/StockData-IBM.db'
# Create a SQL connection to our SQLite database
con = sqlite3.connect(dbfile)

# creating cursor
cur = con.cursor()

# reading all table names
table_list = [a for a in cur.execute("SELECT name FROM sqlite_master WHERE type = 'table'")]
# here is you table list
print(table_list)

# Be sure to close the connection
con.close()

That worked for me very good. The reading of the data you have done already right just paste in the tablenames.