[python]Query object from table with Flask SQLAlchemy

The purpose of using SQLAlchemy is to avoid using SQL syntax, and Flask’s extension of SQLAlchemy makes usage easier than the original SQLAlchemy.

So here are two demonstrations to query every row and query one row with a condition.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# The same can be done this way:
# app.config['SQLALCHEMY_DATABASE_URI'] = your DB connection string

# pass in the app into SQLAlchemy to create a db session.
db = SQLAlchemy(app)

# declare the class for the model.
class Test(db.Model):
    id = db.Column(db.Integer, primary_key=True)

class Members(db.Model):
    # the id does not need to add value as it is auto incrementing because it is primary key
    # each column is a position, you will need to specify username=. password= and date_joined=
    # for each db insertion.
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(60), unique=True)
    email = db.Column(db.String(100))
    date_joined = db.Column(db.DateTime)

if __name__ == '__main__':
    # The creation starts here.
    # create_all will not re-create table if table exists.


    # Get all rows, this is the same as SELECT * FROM MEMBERS;
    all_members = Members.query.all()
    for member in all_members:
        print(f"Username: {member.username}, Email: {member.email}")

    # Get specific row based on condition.
    # this is the same as SELECT * FROM MEMBERS WHERE USERNAME='TOM'
    # first() is used because username is unique as defined in the Members class
    one_member = Members.query.filter_by(username='tom').first()
# another way of query specific row if username is tom.
    one_member2 = Members.query.filter(Members.username == 'tom').first()

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s