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?

Just open the DB in SQLite CLI and type “.schema”. Then you get to see the structure of your DB — Krumelur
– Krumelur, Commented Jun 12, 2020 at 22:08

SebNik · Accepted Answer · 2020-06-12 10:01:24Z

33

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.

answered Jun 12, 2020 at 10:01

SebNik

1,0233 gold badges14 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ho Jun Hong Over a year ago

hi @SebNik, thanks for the reply. The code works without any error, however, the output given to me is a mere [ ]... May I know what does that mean?

SebNik Over a year ago

so I tested this code with a database myself and I got the right tabels so considering the fact that your output is []. Would it be possible that your database has no table in it ?

Ho Jun Hong Over a year ago

I see... then is there another way to extract data and see what data is inside the database? @SebNik

SebNik Over a year ago

no, i don't think there is another way in python (but I am not 100% sure) but you could open your *db file in a editor then you could see more of it (e.g. VSCode). Are you sure that you database contains anything. If you could share it it would be great as well

Mika C. · Accepted Answer · 2021-12-10 12:15:11Z

7

If you want to see data for visual analysis as pandas dataframe, the below approach could also be used.

import pandas as pd
import sqlite3
import sqlalchemy 

try:
    conn = sqlite3.connect("file.db")    
except Exception as e:
    print(e)

#Now in order to read in pandas dataframe we need to know table name
cursor = conn.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
print(f"Table Name : {cursor.fetchall()}")

df = pd.read_sql_query('SELECT * FROM Table_Name', conn)
conn.close()

edited Dec 10, 2021 at 12:15

Mika C.

821 silver badge16 bronze badges

answered Sep 27, 2020 at 21:06

Aman

1112 silver badges5 bronze badges

2 Comments

ranemak Over a year ago

the Table_Name parameter is not defined, coult u correct it pls?

djreyab Oct 6 at 19:17

Is there a way to do a new line after each one with the fetch or does that require me to iterate over the list?

SebNik · Accepted Answer · 2020-06-12 22:05:49Z

0

from flask import Flask
app = Flask(__name__)

from sqlalchemy import create_engine, select, MetaData, Table
from sqlalchemy.sql import and_, or_

engine = create_engine('sqlite://username:password@host/databasename')
class UserModel():  
    def __init__(self):
        try:
            self.meta = MetaData()
            self.users = Table("users", self.meta, autoload=True, autoload_with=engine)
        except Exception as e:
            print(e)

    def get(self):
        stmt = select([self.users.c.name, self.users.c.email, self.users.c.password])
        print(stmt)         
        result = engine.execute(stmt)
        temp = [dict(r) for r in result] if result else None
        print(temp)         
        return temp

edited Jun 12, 2020 at 22:05

SebNik

1,0233 gold badges14 silver badges24 bronze badges

answered Jun 12, 2020 at 10:09

nandhini ram

1191 silver badge1 bronze badge

3 Comments

nandhini ram Over a year ago

this code is very useful for select all values from users table.

SebNik Over a year ago

first of all please format it and second: yes, it is useful for a flask server but in this case we have *.db file

SebNik Over a year ago

and discriptions would help as well

Collectives™ on Stack Overflow

Open database files (.db) using python

3 Answers 3

4 Comments

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related