Whenever I execute an update statement using a session with SqlAlchemy and then call commit(), it will rarely update the database.
Here is my environment: I have two servers running. One is for my database the other is for my python server.
Database Server:
Postgres v9.6- On Amazon's RDS
Server with Python
Linux 3.13.0-65-generic x86_64- On an Amazon EC2 InstanceSqlAlchemy v1.1.5Python v3.4.3Flask 0.11.1
Also, I use pgAdmin 4 for querying my table.
The files of importance:
server/models/category.py
from sqlalchemy.orm import backref
from .. import db
from flask import jsonify
class Category(db.Model):
__tablename__ = "categories"
id = db.Column(db.Integer, primary_key=True)
cat_name = db.Column(db.String(80))
includes = db.Column(db.ARRAY(db.String), default=[])
excludes = db.Column(db.ARRAY(db.String), default=[])
parent_id = db.Column(db.ForeignKey('categories.id', ondelete='SET NULL'), nullable=True, default=None)
subcategories = db.relationship('Category', backref=backref(
'categories',
remote_side=[id],
single_parent=True,
cascade="all, delete-orphan"
))
assigned_user = db.Column(db.String(80), nullable=True, default=None)
def to_dict(self):
return dict(
id=self.id,
cat_name=self.cat_name,
parent_id=self.parent_id,
includes=self.includes,
excludes=self.excludes,
assigned_user=self.assigned_user,
)
def json(self):
return jsonify(self.to_dict())
def __repr__(self):
return "<%s %r>" % (self.__class__, self.to_dict())
class CategoryOperations:
...
@staticmethod
def update_category(category):
return """
UPDATE categories
SET cat_name='{0}',
parent_id={1},
includes='{2}',
excludes='{3}',
assigned_user={4}
WHERE id={5}
RETURNING cat_name, parent_id, includes, excludes, assigned_user
""".format(
category.cat_name,
category.parent_id if category.parent_id is not None else 'null',
"{" + ",".join(category.includes) + "}",
"{" + ",".join(category.excludes) + "}",
"'" + category.assigned_user + "'" if category.assigned_user is not None else 'null',
category.id
)
@staticmethod
def update(category, session):
print("Updating category with id: " + str(category.id))
stmt = CategoryOperations.update_category(category)
print(stmt)
row_updated = session.execute(stmt).fetchone()
return Category(
id=category.id,
cat_name=row_updated[0],
parent_id=row_updated[1],
includes=row_updated[2],
excludes=row_updated[3],
assigned_user=row_updated[4]
)
...
server/api/category.py
from flask import jsonify, request
import json
from .api_utils.utils import valid_request as is_valid_request
from . import api
from ..models.category import Category, CategoryOperations
from ..models.users_categories import UsersCategoriesOperations, UsersCategories
from ..models.listener_item import ListenerItemOperations, ListenerItem
from ..models.user import UserOperations
from ..schemas.category import category_schema
from .. import get_session
...
@api.route('/categories/<int:id>', methods=['PUT'])
def update_category(id):
category_json = request.json
if category_json is None:
return "Bad Request: Request not sent as json", 400
valid_json, json_err = is_valid_request(category_json, ['cat_name', 'parent_id', 'includes', 'excludes', 'assigned_user'], "and")
if not valid_json:
return json_err, 400
category = Category(
id=id,
cat_name=category_json['cat_name'],
parent_id=category_json['parent_id'],
includes=category_json['includes'],
excludes=category_json['excludes'],
assigned_user=category_json['assigned_user'],
)
session = get_session()
try:
updated_category = CategoryOperations.update(category, session)
session.commit()
print(updated_category.to_dict())
return jsonify(updated_category.to_dict()), 200
except Exception as e:
print("ROLLBACK")
print(e)
session.rollback()
return str(e), 500
...
There is one more file that will probably be useful in this case:
server/__init__.py
import sqlalchemy as sa
from flask import Flask
from flask_marshmallow import Marshmallow
from flask_sqlalchemy import SQLAlchemy
from config import config
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from flask_cors import CORS, cross_origin
from .db_config import CONFIG
db = SQLAlchemy()
ma = Marshmallow()
Engine = sa.create_engine(
CONFIG.POSTGRES_URL,
client_encoding='utf8',
pool_size=20,
max_overflow=0
)
Session = sessionmaker(bind=Engine)
conn = Engine.connect()
def get_session():
return Session(bind=conn)
def create_app(config_name):
app = Flask(__name__, static_url_path="/app", static_folder="static")
app_config = config[config_name]()
print(app_config)
app.config.from_object(app_config)
from .api import api as api_blueprint
app.register_blueprint(api_blueprint, url_prefix='/api')
from .api.routes import routes
routes(app)
from .auth import authentication
authentication(app)
db.init_app(app)
ma.init_app(app)
CORS(app)
...
return app
To explain a little more with the environment and files I have given, let's say I have a row in my categories table like so:
{
"assigned_user": null,
"cat_name": "Category Name Before",
"excludes": [
"exclude1",
"excludeBefore"
],
"id": 2,
"includes": [
"include1",
"include2"
],
"parent_id": null
}
When I do a PUT request to /api/categories/2 with the body as:
{
"assigned_user": null,
"cat_name": "Category Name 1",
"excludes": [
"exclude1",
"exclude2"
],
"id": 2,
"includes": [
"include1",
"include2"
],
"parent_id": null
}
During the request, I print out the SQL Statement that my PUT request created (for testing) and I get this:
UPDATE categories
SET cat_name='Category Name 1',
parent_id=null,
includes='{include1,include2}',
excludes='{exclude1,exclude2}',
assigned_user=null
WHERE id=2
RETURNING cat_name, parent_id, includes, excludes, assigned_user
After it commits the UPDATE statement, it then returns the response. I get the updated object back like so:
{
"assigned_user": null,
"cat_name": "Category Name 1",
"excludes": [
"exclude1",
"exclude2"
],
"id": 2,
"includes": [
"include1",
"include2"
],
"parent_id": null
}
When I do a GET request with this URL: /api/categories/2 and I get the same object too like so:
{
"assigned_user": null,
"cat_name": "Category Name 1",
"excludes": [
"exclude1",
"exclude2"
],
"id": 2,
"includes": [
"include1",
"include2"
],
"parent_id": null
}
However, when I run the SQL command below in pgAdmin, I get the old version (it didn't update the row in the database):
SELECT * FROM categories WHERE id=2
Here is the object I get:
{
"assigned_user": null,
"cat_name": "Category Name Before",
"excludes": [
"exclude1",
"excludeBefore"
],
"id": 2,
"includes": [
"include1",
"include2"
],
"parent_id": null
}
This is the object I had before doing the PUT request. If I restart my python server and do the GET request, then I get the old object. It feels like in the session, it is storing the data, but for some reason it's not propagating to the database.
It might be good to know that if I run the update command in pgAdmin, it updates the row just fine.
UPDATE: I have also used these methods (as talked about here) to update, but still the same problem:
# using the session to update
session.query(Category).filter_by(id=category.id).update({
"cat_name": category.id,
"assigned_user": category.assigned_user,
"includes": category.includes,
"excludes": category.excludes,
"parent_id": category.parent_id
})
# using the category object to edit, then commit
category_from_db = session.query(Category).filter_by(id=category.id).first()
category_from_db.cat_name = category_json['cat_name']
category_from_db.assigned_user = category_json['assigned_user']
category_from_db.excludes = category_json['excludes']
category_from_db.includes = category_json['includes']
category_from_db.parent_id = category_json['parent_id']
session.commit()
Any ideas?
