使用Python Flask无法从数据库查询INT类型数据

Hey there! Let's figure out why your max year query isn't returning an integer as expected, and how to fix it.

First, I'm assuming you're using Flask-SQLAlchemy (the most common ORM for Flask projects) since you didn't specify, but I'll cover raw SQL cases too.

The Root Cause #

When you run a query with an aggregate function like MAX(), most database queries (including SQLAlchemy) return a tuple by default instead of a single value. That's probably why you're seeing something like (2024, <class 'int'>) instead of just the integer 2024.

Solutions #

1. Use scalar() for Clean Single Value Extraction #

The simplest fix is to use SQLAlchemy's scalar() method instead of first() or all(). It directly returns the single value from your aggregate query, or None if there's no data.

Here's a complete example:

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import func

# Assuming your app and db are set up already
db = SQLAlchemy(app)

class YourDataModel(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    year = db.Column(db.Integer, nullable=False)  # Your integer year column

def get_max_year():
    # Fetch the max year as a single integer
    max_year = db.session.query(func.max(YourDataModel.year)).scalar()
    
    # Handle case where no years exist in the database
    if max_year is None:
        return 2020  # Replace with your default starting year
    return max_year

2. If You Prefer first(), Extract the Tuple Value #

If you want to stick with first(), you just need to pull the first element from the returned tuple:

def get_max_year():
    result = db.session.query(func.max(YourDataModel.year)).first()
    if result:
        return result[0]  # Extract the integer from the tuple
    return 2020

3. For Raw SQL Queries #

If you're using raw SQL instead of the ORM, the same logic applies—extract the value from the tuple or use scalar():

def get_max_year():
    # Using raw SQL
    result = db.session.execute("SELECT MAX(year) FROM your_table_name").scalar()
    return result if result else 2020

Verify the Result #

After making these changes, test your function to confirm:

# Within your app context
latest_year = get_max_year()
print(latest_year)          # Should output an integer like 2024
print(type(latest_year))    # Should print <class 'int'>

This should resolve the issue where your query wasn't returning a pure integer, and you'll be able to safely use the result to add subsequent year data.

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