How to query all rows of a table

Question

I'm using Python Sqlalchemy for MYSQL db. I wrote the following script to create the class object and then added some rows in the table.

from sqlalchemy import create_engine, MetaData, Table, Column, ForeignKey
from sqlalchemy.dialects.mysql.base import VARCHAR, LONGTEXT, INTEGER
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine("mysql+mysqldb://root:@localhost/mydb")
connection = engine.connect()

Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()
metadata = MetaData()

class User(Base):
    __tablename__ = 'User'
    id = Column('id', INTEGER(display_width=11), primary_key=True, nullable=False)
    email = Column('email', VARCHAR(charset='utf8mb4', collation='utf8mb4_0900_ai_ci', length=100), unique=True)
    password = Column('password', VARCHAR(charset='utf8mb4', collation='utf8mb4_0900_ai_ci', length=45))
    name = Column('name', VARCHAR(charset='utf8mb4', collation='utf8mb4_0900_ai_ci', length=100))

Now, I need to get all the rows from the table "User" so I am doing this:

user = session.query(User).all()
print(user)

but the output I am getting is not the table data but this:

[<__main__.User object at 0x7f10b0c6ebe0>, <__main__.User object at 0x7f10b0c6ec50>]

How would I get the actual data from the table? Any help would be appreciated

The query _does_ return all data in your table, it's just not printed nicely. You could add a [`__str__`](https://docs.python.org/3/reference/datamodel.html#object.__str__) or [`__repr__`](https://docs.python.org/3/reference/datamodel.html#object.__repr__) to the `User` model to overcome this. — Nelewout, Jul 31 '18 at 12:04
You are trying to print the entire list of objects. Instead, try to access the individual item in the list. — mad_, Jul 31 '18 at 12:42
You should read ["Understanding repr() function in Python"](https://stackoverflow.com/questions/7784148/understanding-repr-function-in-python). This is almost a "duplicate", since the answer is pretty much that. — Ilja Everilä, Jul 31 '18 at 17:04

yogi · Answer 1 · 2018-07-31T12:08:06.090

4

The output you will get is a tuple of records. So, use a loop

   users = session.query(User).all()
   for user in users:
        print (user)
        print (user.id, user.email, user.password, user.name)

edited Jul 31 '18 at 12:08

answered Jul 31 '18 at 12:02

yogi

266
2
11

score 0 · Answer 2 · answered Nov 24 '20 at 17:29

you should write __str__ method in User class something like:

class User(Base):
    ...

    def __str__(self):
        str_out = 'id={} email={} password={} name={}'
        str_formated = str_out.format(self.id,self.email,self.password,self.name)
        return str_formated

How to query all rows of a table

2 Answers2