Flask SQLalchemy Query, Specify Column Names

Flask SQLAlchemy query, specify column names

You can use the with_entities() method to restrict which columns you'd like to return in the result. (documentation)

result = SomeModel.query.with_entities(SomeModel.col1, SomeModel.col2)

Depending on your requirements, you may also find deferreds useful. They allow you to return the full object but restrict the columns that come over the wire.

sqlalchemy query: result column names

Add .label('desired_name') after each column. In your case it would look like

models = session.query(
    DevicesGps.ReceivedDateUtc.label("gps_telemetry_ReceivedDateUtc"), 
    DevicesGps.ReceivedTimeUtc.label("gps_telemetry_ReceivedTimeUtc"),   
    DevicesGps.Latitude.label("gps_telemetry_Latitude"),                                 
    DevicesGps.Longitude.label("gps_telemetry_Longitude")
)

Get column names along with table names for SQLAlchemy Query

The class corresponding to the table comes in the 'entity' key. So, if you want a "tablename" key in your fields hash, you can do:

fields = [
    StudentsTable.name,
    SubjectsTable.name
]

query = self.session()\
    .query(*fields)\
    .filter(StudentsTable.id_some_fk == SubjectsTable.id_some_pk)

descriptions = query.column_descriptions

for description in descriptions:
    description['tablename'] = description['entity'].__tablename__

return {
    "meta": {
        "fields": descriptions
    },
    "data": query.all()
}

Of course, you have the problem that your returns are null, at least in the return you have posted. This might have different reasons depending on your actual ORM and database data.

If instead you want the name of the column to be preceded by the name of the table, you might change the for loop above to

for description in descriptions:
    description['name'] = "{}.{}".format(description['entity'].__tablename__, description['name'])

reading column names from joined query in flask-sqlalchemy

Rename the columns using aliases:

showings = db.execute(
    """SELECT showings.cinema_id
            , showings.movie_id
            , cinemas.name AS cinema_name
            , movies.name AS movie_name
       FROM   showings 
       JOIN   cinemas ON showings.cinema_id=cinemas.id
       JOIN   movies ON showings.movie_id=movies.id
       WHERE  showings.id = :id""", {"id": cinema_id}).fetchall()

and use said aliases in the template:

<option value="{{ showing.id }}">
    {{ showing.cinema_name }} @ {{ showing.movie_name }}
</option>

Flask-SQLAlchemy: from selected row get list of column names where value equals something

So far, I've solved it with this list comprehension:

result = [col for col in progress.__table__.columns.keys() if getattr(progress, col) == "yes"]

It would be better if there was a way to use just one query if possible.

python - Handle column names in form for update query with Flask-SQLAlchemy

I would suggest you query your database first and pass the row id as the value for your select in html

In your flask app

@app.route('/exhibition', methods=['GET', 'POST'])
def exhibition():
    options = session.query(Exhibition) #however you query your db
    return render_template('exhibition.html', options = options)

in your html (I am guessing you are using jinja for your templating)

<select>
{%for option in options%}
  <option value={{option.id}}>{{option.text}}</option>
{endfor}
</select>

Then you can fetch the row to be modified by the row id which is what you get when the form is posted.

Then when you are updating the row, ensure you specify the specific column to be updated eg

row_to_edit = session.query(Exhibition).filter_by(id = request.form ['input_field']))
#specify what you are updating
row_to_edit.column_to_update = request.form ['input_field']
# Then you can comit column_to_edit

To make sure it is easy for the person editing to identify the column name, and to reduce the burden of having to validate a free text entry matching a column name, i suggest you add a second select field that displays the column names so that free text input is just for the column value
The view above becomes

@app.route('/exhibition', methods=['GET', 'POST'])
    def exhibition():
        options = session.query(Exhibition) #however you query your db
        cols = Exhibition.__table__.columns
        return render_template('exhibition.html', options = options, cols = cols)

the second select

<select>
{%for col in cols%}
  <option value={{col.key}}>{{col.key}}</option>
{endfor}
</select>

You can make the option text more readable by iterating through the cols and coming up with a list with a value and readable text for the option

specify the column you are updating like this

row_to_edit.getattr(row_to_edit, request.form ['select_field_for_column'] = request.form ['input_field_column_value']

I know the first part is confusing. ordinarily you would do it row_to_edit.column_to_update, but since in our case the column to update is a variable we use getattr() to get the col name equal to the variable

If you are familiar with wtforms, or you are a fast learner, they would give you a more elegant way of handing this

---

Related Topics