Avatar

Blogging tips

Avatar

Python, Django and Flask

Avatar

Latest tech posts

Monday, November 4, 2019

CRUD operations using Flask and SQLAlchemy



 

CRUD operations using Flask and SQLAlchemy


Every database-related application has to deal with CRUD operations. It is used to store and retrieve data from the database. The CRUD operations are,
  1. Create
  2. Insert
  3. Update
  4. Delete
SQLAlchemy provides us a method to perform the CRUD operations. We can see this with the help of the book table. The book model looks like this.


    Inserting the records

     

    New records can be inserted by following these three steps. They are
    • Create the python object
    • Add it to the session
    • Commit the session

     

    Example

     

    Data can be inserted into the book model by the following commands.


    Deleting the records


    Deleting the records is very similar to adding the records. Instead of using the session.add(), we will use session.delete(), and commit the changes.


    Querying the database


    Flask-SQLAlchemy provides a query attribute on model class. Querying refers to the action of retrieving data from the database. For example, we may need only the people living in a particular city or from a particular age group. For these purposes querying comes in handy.

    Get all the values of a model


    The "all()" method by the query attribute helps us in retrieving all the records of a table.

    CRUD operations using Flask and SQLAlchemy
    Flask SQLAlchemy all() method

     Get the values of a record using primary key


    Let us say the table books have two entries with id 1 and 2, where id is the primary key field.


    CRUD operations using Flask and SQLAlchemy
    Flask SQLAlchemy get by primary key


    The contents of the record with the primary key 1 can be accessed by using the following query.

    To get the first value from a list of values


    The "first()" method by the query attribute helps us to get the first value from the list of available values.

    CRUD operations using Flask and SQLAlchemy
    Flask SQLAlchemy first() method

    Filter records


    The query attribute provides the "filter_by()" method. The field and the value is provided as the argument to the filter_by() method.


    CRUD operations using Flask and SQLAlchemy
    Flask SQLAlchemy filter_by first method



    This query will get the first instance of all the books named "Twilight".
    CRUD operations using Flask and SQLAlchemy
    Flask SQLAlchemy filter all method
    This will return all the instances of the books named "Twilight".


    Limiting query results


    The query results can be limited by using the limit() method. It takes an integer as an argument. This integer is the number of results that we want our query to return instead of returning them all.

    CRUD operations using Flask and SQLAlchemy
    Flask SQLAlchemy limit method

    This will limit the search result to 5 records only.


    Ordering the query results


    In SQLAlchemy the query results can be ordered by using the order_by method provided by the query attribute. The field based on which the search results are to be ordered is passed to the order_by method as an argument.

    CRUD operations using Flask and SQLAlchemy
    Flask SQLAlchemy order_by method

    Order by desc


    First we need to import the desc module from the SQLAlchemy  for this purpose.

    CRUD operations using flask and SQLAlchemy
    flask SQLAlchemy order by desc

    In the first example, the result is sorted in descending order based on the name of the author who wrote the book.

    Before it was like this. "Robin Sharma, Stephanie Meyer".
    Now it is like this. "Stephanie Meyer, Robin Sharma".


    Updating the records


    Updating a record is also a three step process. The three steps are,
    • Get the object
    • Assign a new value to the object's field
    • commit the changes
    CRUD operations using Flask and SQLAlchemy
    update record SQLAlchemy

        After running this command, the name of the book will be changed from "Twilight" to "Harry Potter".

        No comments:

        Post a Comment