Open database files (.db) using python

user_12766193 0
Viewed 25483

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?

sqlite python
3 Answers

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.

user_132667360

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()
user_120030880 
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
user_130886770
Related