How to get one record with SQLAlchemy?

Question

I'm trying to develop a web api using flask and sqlite. For communication with db I'm using sqlalchemy.

In the code that I post below I have create a GET method to retrieve all data into a specific table into db:

from flask import Flask, g, Response, request, jsonify, abort
from flask_restful import Resource, Api
from sqlalchemy import create_engine
from flask.ext.restless import APIManager
from flask.ext.sqlalchemy import SQLAlchemy
from json import dumps
import sqlite3
import json


app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///climb.db"
db = SQLAlchemy(app)

class falesie(db.Model):
    __tablename__ = 'falesie'
    id = db.Column(db.Integer, primary_key=True)
    regione = db.Column(db.String(20))
    citta = db.Column(db.String(20))
    n_settori = db.Column(db.Integer)
    lat = db.Column(db.Float)
    lon = db.Column(db.Float)

    def __init__(self, regione, citta, n_settori, lat, lon):
      self.regione = regione
      self.citta = citta
      self.n_settori= n_settori
      self.lat = lat
      self.lon = lon

@app.route('/dev', methods = ['GET'])
def get_falesie():
    Falesie = falesie.query.all()
    formatted_falesie = []
    for f in Falesie:
        formatted_falesie.append({
        'id': f.id,
        'regione': f.regione,
        'citta': f.citta,
        'n_settori': f.n_settori,
        'lat': f.lat,
        'lon': f.lon})
    return json.dumps({'Falesie': formatted_falesie}), 200, {'Content-  Type': 'application/json'}


if __name__ == "__main__":
    db.create_all()
    app.run(debug=True)

I would like to create a GET method to retrieve a specific record with a specific value, as in this example:

@app.route('dev/<string:name>')
def get_data(name):

I don't know how to retrieve a single record. Any help please?

Which field do you want to use when querying falesie? It doesn't have a field name. — Forge
– Forge, Commented Mar 3, 2016 at 14:14

Forge · Accepted Answer · 2021-08-14 01:15:48Z

20

This should help with getting a single result of falesie:

try:
    user = session.query(falesie).filter(name=name).one()  # filter on name
except MultipleResultsFound, e:
    print e
    # Deal with it
except NoResultFound, e:
    print e
    # Deal with that as well

Where session is obtained as following:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# an Engine, which the Session will use for connection resources
engine = create_engine('sqlite:///climb.db')

# create a configured "Session" class
Session = sessionmaker(bind=engine)

# create a Session
session = Session()

See SQLAlchemy documentation for more details.

edited Aug 14, 2021 at 1:15

answered Mar 3, 2016 at 14:35

Forge

6,8727 gold badges49 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

vidstige Over a year ago

This should be filter_by, right? Maybe the API has changed since 2016?

Wayne Werner · Accepted Answer · 2016-03-03 14:15:52Z

15

If you read the tutorial, you'll see that you can filter your query. Something like this should be what you want:

falesie.query().filter_by(id=name).first()

answered Mar 3, 2016 at 14:15

Wayne Werner

52.3k35 gold badges213 silver badges304 bronze badges

1 Comment

Shemogumbe Over a year ago

Remember query is not callable, use falesie.query.filter_by(id=name).first()

Abhishek · Accepted Answer · 2020-03-12 09:15:47Z

4

Question is very old but one more technique can help to others having problem.

def load_user(id):
    falesie.query.get(int(id))

answered Mar 12, 2020 at 9:15

Abhishek

6827 silver badges8 bronze badges

1 Comment

Erdős-Bacon Over a year ago

docs.sqlalchemy.org/en/13/orm/…

sequoia · Accepted Answer · 2024-01-25 02:56:57Z

2

For those now here looking for a way with SQLAlchemy 2.0--you can use this method (get_one()):

from sqlalchemy.orm import Session
from sqlalchemy.orm.exc import NoResultFound

# create your session

try:
    obj = session.get_one(YourDbModel, pk_id)
except NoResultFound:
    # handle

answered Jan 25, 2024 at 2:56

sequoia

1732 silver badges9 bronze badges

Collectives™ on Stack Overflow

How to get one record with SQLAlchemy?

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related