-1

I am new to SQLAlchemy. I am trying to make it work with a MariaDB. I am trying to get all the records from the DB and cant seem to get the correct syntax for this.

I have tried Employee.query.all(), and other variations and none seem to work for me. I either get an error with 'all' attribute or with the the 'query' attribute

  all_employee = session.query.all(Employee)
AttributeError: 'function' object has no attribute 'all'

############################# CODE ##############################

# Define the MariaDB engine using MariaDB Connector/Python
engine = sqlalchemy.create_engine(f"mariadb+pymysql://{DB_USER}:{DB_PASS}@{DB_HOST}:{DB_PORT}/{DB}")

Base = declarative_base()

class Employee(Base):

   __tablename__ = 'employees'
   id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
   firstname = sqlalchemy.Column(sqlalchemy.String(length=100))
   lastname = sqlalchemy.Column(sqlalchemy.String(length=100))
   active = sqlalchemy.Column(sqlalchemy.Boolean, default=True)


class EmployeeSchema(ma.Schema):
    class Meta:
        #all fields from DB
        fields = ('id', 'firstname', 'lastname', 'active')

#creates db model
Base.metadata.create_all(engine)

#initialize Session
Session = sqlalchemy.orm.sessionmaker()
Session.configure(bind=engine)
session = Session()


employee_schema = EmployeeSchema()


#Get Employee
@app.route('/', methods=['GET'])
def get_employee():
    all_employee = session.query.all(Employee)
    app.logger.info(f"employees is {str(all_employee)}")
    result = employee_schema.dump(all_employee)
    return jsonify(result)
davidism
  • 121,510
  • 29
  • 395
  • 339
Texia Plazaola
  • 41
  • 1
  • 5
  • `session.query(Employee).all()` – snakecharmerb Sep 21 '21 at 14:45
  • When I do that I get a blank dictionary and when I print out the contents it just memory id's, not any readable data. – Texia Plazaola Sep 21 '21 at 15:04
  • @TexiaPlazaola, do you mean your `logger.info` statement prints `{}`? Also, I can't find any reference to the `.dump()` function in the sqlalchemy docs, are you using marshmallow or similar serialization library to get that function? – Sparrow1029 Sep 21 '21 at 15:15
  • Yes I am using Marshmallow and jsonify, but I didn't include the rest of the code. Only the function that I am having an issue with. The logger.info statement prints a whole bunch of memory id's – Texia Plazaola Sep 21 '21 at 15:19
  • 1
    The "memory ids" are the default representations of the objects. Try `app.logger.info(f"employees is {str([e.lastname for e in all_employee])}")`. If you are getting objects in the schema dump there is something wrong with the schema definition – snakecharmerb Sep 21 '21 at 15:19
  • That worked. I got the last names. How can I then get all the info? Do I need to use a loop or can I get all the info with .all() attribute? – Texia Plazaola Sep 21 '21 at 15:22
  • You already have all the info in the model instances returned by `.all()`. If you want to see the data in your logs you can add a `__repr__` method to your models, as described in the answers to [this question](https://stackoverflow.com/q/54026174/5320906), for example. – snakecharmerb Sep 21 '21 at 16:21
  • Please trim your code to make it easier to find your problem. Follow these guidelines to create a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example). – Community Sep 29 '21 at 04:14

1 Answers1

1

Edit: Looked through the marshmallow docs and found that there is a keyword argument you can pass to the dump() function: many. This will treat your passed-in object as a collection rather than a single object (which is the reason you were getting the "empty dict" object). This should return you a JSON-able list of dicts.

#...snip...

#Get Employee
@app.route('/', methods=['GET'])
def get_employee():
    all_employee = session.query(Employee).all()
    app.logger.info(f"employees is {[e.__dict__ for e in all_employee]}")
    result = employee_schema.dump(all_employee, many=True)
    return jsonify(result)

If you look at the SQL Alchemy docs on Querying, I think you just have a slight syntax error. query is a method/function, which is why you are getting the 'function' object has no attribute 'all' when you attempt to access the all() member function.

session.query.all(Employee)

should work instead as

session.query(Employee).all()

I suggest poking around the SQL Alchemy docs for a while if you're just learning it, they are really exhaustive and well structured!

Sparrow1029
  • 592
  • 5
  • 13