如何在Flask-SQLAlchemy中获取已存在数据库条目的ID

I'm developing a Flask web app in app.py using Flask-SQLAlchemy with a SQLite database at sqlite:///mydb.db. Here's my relevant code:

from flask import Flask, render_template, request, redirect
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///mydb.db'
db = SQLAlchemy(app)
class Mydb(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    userName = db.Column(db.String(30), nullable = False, unique = True)
    pwd = db.Column(db.String(30), nullable = False)
    created = db.Column(db.DateTime, default=datetime.utcnow)
# ... rest of code omitted

I've already created the database, can add entries via HTML forms, and the database is working with several existing entries. I know I can check if a user like "John" exists using bool(Model.query.filter_by(user='John').first()), but now I want to know how to get the ID of that (or those) existing entry(s) without using any new libraries. The ID is defined as a primary key integer.

Great question! Since you already confirmed the entries exist, grabbing their IDs is totally straightforward using Flask-SQLAlchemy's built-in tools—no extra libraries needed. Here are the most common scenarios you'll run into:

1. Get the ID of a single existing entry #

If you're targeting one specific entry (like the user "John"), once you fetch the object with .first(), you can just access its id attribute directly:

# Fetch the user object (you already know it exists, so no None checks needed)
user = Mydb.query.filter_by(userName='John').first()
# Grab the ID directly from the object
user_id = user.id
print(user_id)  # Outputs the integer ID of John's entry

2. Get IDs of multiple existing entries #

If you're filtering for a group of entries (e.g., all users created in the last month), use .all() to get a list of objects, then extract their IDs with a simple list comprehension:

from datetime import timedelta

# Fetch all matching entries
recent_users = Mydb.query.filter(Mydb.created >= datetime.utcnow() - timedelta(days=30)).all()
# Extract IDs into a list of integers
recent_user_ids = [user.id for user in recent_users]
print(recent_user_ids)  # Looks like [2, 4, 7, 9]

3. Optimized: Query only for IDs (skip full objects) #

If you don't need the entire model object and just want the IDs, you can query specifically for the id column to save on database load:

# Get a single ID (returns the integer directly)
single_id = Mydb.query.with_entities(Mydb.id).filter_by(userName='John').scalar()

# Get multiple IDs (returns a list of tuples, so we flatten it)
multiple_ids = Mydb.query.with_entities(Mydb.id).filter(Mydb.created >= datetime(2024, 1, 1)).all()
flattened_ids = [id_tuple[0] for id_tuple in multiple_ids]

This is perfect for large datasets where loading full objects would be unnecessary overhead.

All of these approaches rely solely on Flask-SQLAlchemy's core functionality, so you won't need to install anything extra.

内容的提问来源于stack exchange，提问作者Alfha