CODEWITHSUNDEEP
pythonsqlalchemy
Ronnie
Ronnie

Reputation: 391

How to query all rows of a table

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

Upvotes: 2

Views: 19716

Answers (2)

Miguel Bustamante
Miguel Bustamante

Reputation: 36

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

Upvotes: 0

yogi
yogi

Reputation: 266

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)

Upvotes: 4

Related Questions