0

I am trying to get my rows as a list or a pandas data frame from a Query.all() function in SqlAlchemy.

    users = session.query(User).filter_by(name='steve').all();

the results came out as an array of models object.I need the values and columns to be shown as : 

   id|name|age

All other solutions proposed to do a for loop through the result and to extract each value (which it make no sense).

I am using python3.6

Class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(Integer)
    age = Column(Integer);
dkmostafa
  • 35
  • 6
  • 1
    `pandas.read_sql`? – Primusa Dec 19 '18 at 03:01
  • If you need to display the data in a specific way, you'll either have to a) loop over it and print in your desired format or b) format a string in the database and then loop over the results and print... Also, `Query.all()` returns a list. Not an [array](https://docs.python.org/3/library/array.html). – Ilja Everilä Dec 19 '18 at 07:20
  • @Primusa pandas.read_sql will take 2 parameters the SQL query and the connection, and in this way, I will lose the usage of the SQL alchemy ORM. is there another way? – dkmostafa Dec 19 '18 at 16:33
  • @IljaEverilä so there is no alternate way to get the data in pandas without looping ? – dkmostafa Dec 19 '18 at 16:38
  • 1
    Getting data is rather different from showing, @Primusa hinted at what you could do. Here is a Q/A about using `Query.statement` with `pandas.read_sql()`: https://stackoverflow.com/questions/29525808/sqlalchemy-orm-conversion-to-pandas-dataframe – Ilja Everilä Dec 19 '18 at 16:46
  • @IljaEverilä thanks thats work , i used the engine object which contains the connection string users = session.query(User).statement;#the sql query df = pd.read_sql(statement,db.engine);#binding it to a dataframe – dkmostafa Dec 19 '18 at 18:11

1 Answers1

0

The result code is :

users = session.query(User); #Query object
statement = users.statement;#Getting the statment
df = pd.read_sql(statement,db.engine);#binding it to a dataframe
dkmostafa
  • 35
  • 6