CODEWITHSUNDEEP
pythonsqlitesqlalchemy
ThP
ThP

Reputation: 185

Sqlalchemy: Print contents of table

I want to print all the contents of a table using sqalchemy. I can't seem to figure out how inspect works.

I want to achieve something like that:

column1: value1, column2: value4
column1: value2, column2: value5
column1: value3, column2: value6

In a table that looks like this:

Table_1:
+---------+---------+
| column1 | column2 |
+---------+---------+
| value1  | value4  |
| value2  | value5  |
| value3  | value6  |
+---------+---------+

Upvotes: 6

Views: 16710

Answers (5)

snakecharmerb
snakecharmerb

Reputation: 55699

You can use the tabulate package to generate the desired format. The simplest approach is to query a table, either directly or through a model's __table__ attribute.

import sqlalchemy as sa
from sqlalchemy import orm
from tabulate import tabulate

# Query a table and tabulate the results.
with engine.connect() as conn:
    rows = conn.execute(sa.select(users))

print(tabulate(rows, headers=users.columns.keys(), tablefmt='pretty'))

# Execute a text query and tabulate the results.
with engine.connect() as conn:
    rows = conn.execute(sa.text("""select * from users"""))

print(
    tabulate(
        rows, 
        headers=[c.name for c in rows.cursor.description],
        tablefmt='pretty'
    )
)

# Query a model's table and tabulate the results.
with orm.Session(engine) as s:
    objs = s.execute(sa.select(User.__table__))

    print(
        tabulate(objs, headers=User.__mapper__.columns.keys(), tablefmt='pretty')
    )

The output will be like this in each case:

+----+-------+-------------------+-----------+---------+
| id | name  | registration_date | encrypted | deleted |
+----+-------+-------------------+-----------+---------+
| 1  | Alice |    2023-07-11     |           |  False  |
| 2  |  Bob  |    2023-01-13     |           |  False  |
| 3  | Carol |    2023-11-25     |           |  False  |
| 4  | Dave  |    2023-09-29     |           |  False  |
| 5  |  Eve  |    2023-08-05     |           |  False  |
+----+-------+-------------------+-----------+---------+

Upvotes: 1

Rainer Glüge
Rainer Glüge

Reputation: 1901

If you work with a DB Model, try

entries = DBModel.query.all()
for entry in entries:
    print(entry)

Upvotes: 1

Shreyas Vedpathak
Shreyas Vedpathak

Reputation: 458

I don't know how useful this might be, but you can visualize the table in desperate times or you need a quick sneak peek at the table.

# create an engine using the following code and 
# replace it with the path to your .db file.

from sqlalchemy import create_engine
engine = create_engine('sqlite:///employee.db', echo = False)

# Import pandas and connect the engine
# use the lowercase representation of your table name for table_name parameter 
# For ex:
class Users(db.Model):
    ...
    ...
    ...

import pandas as pd

user_table = pd.read_sql_table(table_name="users", con=engine)
# This will load the table as dataframe and then you can display

I understand that in case the database is huge, visualizing it using pandas may not be the best idea but as I said above, desperate times !

Upvotes: 2

yardsale8
yardsale8

Reputation: 945

While I don't know how to do this with inspect, I achieve the desire output through regular queries. For this example, I created a sqlite table based on your example. First, we connect and reflect on this existing database.

from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import select
eng = create_engine("sqlite:///databases/example_table.db")

Base = automap_base()
Base.prepare(eng, reflect=True)
Table = Base.classes.example_table

To facilitate our query, we instantiate a session,

Session = sessionmaker(bind=eng)
session = Session()

and perform the query, saving the outcome to result.

stmt = select('*').select_from(Table)
result = session.execute(stmt).fetchall()

The elements of this query are instances of the sqlalchemy RowProxy class, which has a keys method that can be used to access the column names. Consequently, we can transform the result with a few short functions.

def result_dict(r):
    return dict(zip(r.keys(), r))

def result_dicts(rs): 
    return list(map(result_dict, rs))

result_dicts(result)

which returns

[{'id': 1, 'column1': 'value1', 'column2': 'value4'},
 {'id': 2, 'column1': 'value2', 'column2': 'value5'},
 {'id': 3, 'column1': 'value3', 'column2': 'value6'}]

Upvotes: 3

adriansdq
adriansdq

Reputation: 80

Maybe you have a look here:

http://docs.sqlalchemy.org/en/rel_1_0/core/reflection.html

The inspector object could help you solving your problem.

Upvotes: 1

Related Questions