How can I select all rows with sqlalchemy?

Question

I am trying to get all rows from a table.

In controller I have:

meta.Session.query(User).all()

The result is [, ], but I have 2 rows in this table.

I use this model for the table:

import hashlib
import sqlalchemy as sa
from sqlalchemy import orm

from allsun.model import meta

t_user =  sa.Table("users",meta.metadata,autoload=True)

class Duplicat(Exception):
    pass
class LoginExistsException(Exception):
    pass
class EmailExistsException(Exception):
    pass

And next, in the same file:

class User(object):
    def loginExists(self):
        try:
            meta.Session
                .query(User)
                .filter(User.login==self.login)
                .one()
        except orm.exc.NoResultFound:
            pass
        else:
            raise LoginExistsException()

    def emailExists(self):
        try:
            meta
                .Session
                .query(User)
                .filter(User.email==self.email)
                .one()
        except orm.exc.NoResultFound:
            pass
        else:
            raise EmailExistsException()


    def save(self):
        meta.Session.begin()
        meta.Session.save(self)
        try:
            meta.Session.commit()
        except sa.exc.IntegrityError:
            raise Duplicat()

orm.mapper(User, t_user)

There is not enough information to reproduce your problem. What is `[, ]`? It's not valid python expression, but rather looks like you are printing a list of two items `[, ]` on HTML without properly escaping it. — Denis Otkidach, Apr 14 '10 at 03:46
To solve that you can `from flask import Markup` and then `return Markup("%r") % User.query.all()`. — Dr. Jan-Philip Gehrcke, Sep 23 '14 at 15:39

Nima Soroush · Answer 1 · 2014-12-22T13:19:10.787

124

You can easily import your model and run this:

from models import User

# User is the name of table that has a column name
users = User.query.all()

for user in users:
    print user.name

edited Dec 22 '14 at 13:19

answered Oct 06 '14 at 13:36

Nima Soroush

12,242
4
52
53

3

What's the difference between using this code and using ´session.query(SomeTable).all()´? – Progs Jul 24 '19 at 19:59
2

i believe 'User.query.all()' uses core. Where ´session.query(SomeTable).all()´ is ORM – Shoopyman Jan 12 '20 at 03:39
8

NOTE: This syntax, as far as I can see from the docs, is only available using Flask-SQLAlchemy, not SQLAlchemy directly, which uses `select(User)` function – OzzyTheGiant Oct 13 '21 at 19:37
When you go ahead and do `print(user)` . It prints it in the format ``. I thought this might help if you are wondering. It access a certain column entry per row, you can do `print(user.column_name)`. Have a Nice One! – Muneeb Ahmad Khurram Jan 16 '22 at 19:53

score 10 · Answer 2 · edited Jun 07 '18 at 01:51

I use the following snippet to view all the rows in a table. Use a query to find all the rows. The returned objects are the class instances. They can be used to view/edit the values as required:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Sequence
from sqlalchemy import String, Integer, Float, Boolean, Column
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class MyTable(Base):
    __tablename__ = 'MyTable'
    id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
    some_col = Column(String(500))

    def __init__(self, some_col):
        self.some_col = some_col

engine = create_engine('sqlite:///sqllight.db', echo=True)
Session = sessionmaker(bind=engine)
session = Session()

for class_instance in session.query(MyTable).all():
    print(vars(class_instance))

session.close()

How can I select all rows with sqlalchemy?

2 Answers2