i have a function that will perform insert operation into a table. So basically im trying to insert a data every time a user eat a food.
i tried to post a data from postman with a body
{
"id_menu" : 4,
"id_user" : 4
}
it works fine, but the second time i tried to post the same request, the data won't be stored into the database. it returned status code 200, with success message, even though the data didn't come. But when i tried with different menu or user it works well.
So the problem is, im unable to post the data duplicate data. the problem only occur if the user already eat the menu. everytime i post the eaten menu through /addHidangan function, it will not be stored. what did i do wrong? is it the relationship ? or the code? thanks
Using postgresql as my database
I'm storing the data through function below, this function will request the id of the menu and id of the user, and it will store it on table menu_hidangan_user
@app.route('/hidangan', methods=['POST'])
def addHidangan():
try:
id_menu = request.json['id_menu']
id_user = request.json['id_user']
menu = Resep.query.filter_by(id=id_menu).first()
user = Users.query.filter_by(id=id_user).first()
menu.menu_user.append(user)
db.session.add(menu)
db.session.commit()
return jsonify({'statusCode': 200, 'message': 'Success'})
except Exception as e:
return jsonify({'statusCode': 400, 'err': True, 'message': str(e)})
a class of Users with id as primary key and menu_makanan as the relationship with the table menu_hidangan_user
class Users(db.Model):
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(30), unique=True, nullable=False)
nama = db.Column(db.String(30), nullable=False)
password = db.Column(db.String(100), nullable=False)
telepon = db.Column(db.String(20), nullable=False)
usia = db.Column(db.Integer, nullable=False)
status_diabetes = db.Column(db.String(30), nullable=False)
berat_badan = db.Column(db.Integer, nullable=False)
tinggi_badan = db.Column(db.Integer, nullable=False)
timestamp = db.Column(db.DateTime, nullable=False,
server_default=db.func.current_timestamp())
menu_makanan = db.relationship(
'Resep', secondary=menu_hidangan_user, backref="menu_user")
kadar_gula = db.relationship(
"KadarGulaDarahUser", backref="kadar_gula_user")
a class of recipe that contains a detail about a recipe
class Resep(db.Model):
id = db.Column(db.Integer, primary_key=True)
nama = db.Column(db.String(30), unique=True, nullable=False)
detail = db.Column(db.Text, nullable=False)
gula = db.Column(db.Integer, nullable=False)
kalori = db.Column(db.Integer, nullable=False)
protein = db.Column(db.Integer, nullable=False)
lemak = db.Column(db.Integer, nullable=False)
kategori = db.Column(db.String(25), nullable=False)
img_url = db.Column(db.Text, nullable=False)
timestamp = db.Column(db.DateTime, nullable=False,
server_default=db.func.current_timestamp())
and a table called menu_hidangan_user. a user can eat a food, everytime a user ate a food, the user id and the recipe id will be stored in this table.
menu_hidangan_user = db.Table('menu_hidangan_user',
db.Column('id', db.Integer,
primary_key=True),
db.Column('id_resep', db.Integer, db.ForeignKey(
'resep.id'), nullable=False, unique=False),
db.Column('id_user', db.Integer, db.ForeignKey(
'users.id'), nullable=False, unique=False),
db.Column('timestamp', db.DateTime, nullable=False,
server_default=db.func.current_timestamp())
)
Here is the entire code if you need to replicate it
from flask import Flask, json, request, jsonify, session
from flask_sqlalchemy import SQLAlchemy
from flask_marshmallow import Marshmallow
from dotenv import load_dotenv
import os
import datetime
# Init
load_dotenv()
POSTGRESQL_USERNAME = os.getenv("POSTGRESQL_USERNAME")
POSTGRESQL_PASSWORD = os.getenv("POSTGRESQL_PASSWORD")
POSTGRESQL_HOST = os.getenv("POSTGRESQL_HOST")
POSTGRESQL_DB_NAME = os.getenv("POSTGRESQL_DB_NAME")
ENDPOINT = '/api'
POSTGRESQL_URI = f"postgresql://{POSTGRESQL_USERNAME}:{POSTGRESQL_PASSWORD}@{POSTGRESQL_HOST}/{POSTGRESQL_DB_NAME}"
app = Flask(__name__)
basedir = os.path.abspath(os.path.dirname(__file__))
app.secret_key = "hehe"
# Database
app.config['SQLALCHEMY_DATABASE_URI'] = POSTGRESQL_URI
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# Init DB
db = SQLAlchemy(app)
# Init ma
ma = Marshmallow(app)
# Many to Many Relationship
menu_hidangan_user = db.Table('menu_hidangan_user',
db.Column('id', db.Integer,
primary_key=True),
db.Column('id_resep', db.Integer, db.ForeignKey(
'resep.id'), nullable=False, unique=False),
db.Column('id_user', db.Integer, db.ForeignKey(
'users.id'), nullable=False, unique=False),
db.Column('timestamp', db.DateTime, nullable=False,
server_default=db.func.current_timestamp())
)
## == 1. User Class ========= ##
class Users(db.Model):
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(30), unique=True, nullable=False)
nama = db.Column(db.String(30), nullable=False)
password = db.Column(db.String(100), nullable=False)
telepon = db.Column(db.String(20), nullable=False)
usia = db.Column(db.Integer, nullable=False)
status_diabetes = db.Column(db.String(30), nullable=False)
berat_badan = db.Column(db.Integer, nullable=False)
tinggi_badan = db.Column(db.Integer, nullable=False)
timestamp = db.Column(db.DateTime, nullable=False,
server_default=db.func.current_timestamp())
menu_makanan = db.relationship(
'Resep', secondary=menu_hidangan_user, backref="menu_user")
kadar_gula = db.relationship(
"KadarGulaDarahUser", backref="kadar_gula_user")
def __init__(self, email, nama, password, telepon, usia, status_diabetes, berat_badan, tinggi_badan):
self.email = email
self.nama = nama
self.password = password
self.telepon = telepon
self.usia = usia
self.status_diabetes = status_diabetes
self.berat_badan = berat_badan
self.tinggi_badan = tinggi_badan
# User Schema
class UserSchema(ma.Schema):
class Meta:
fields = ('email', 'nama', 'telepon', 'usia',
'status_diabetes', 'berat_badan', 'tinggi_badan', 'timestamp')
# Init Schema
user_schema = UserSchema()
users_schema = UserSchema(many=True)
## == End of User Class ========= ##
## == 2. Resep Class ========= ##
class Resep(db.Model):
id = db.Column(db.Integer, primary_key=True)
nama = db.Column(db.String(30), unique=True, nullable=False)
detail = db.Column(db.Text, nullable=False)
gula = db.Column(db.Integer, nullable=False)
kalori = db.Column(db.Integer, nullable=False)
protein = db.Column(db.Integer, nullable=False)
lemak = db.Column(db.Integer, nullable=False)
kategori = db.Column(db.String(25), nullable=False)
img_url = db.Column(db.Text, nullable=False)
timestamp = db.Column(db.DateTime, nullable=False,
server_default=db.func.current_timestamp())
def __init__(self, nama, detail, gula, kalori, protein, lemak, kategori, img_url):
self.nama = nama
self.detail = detail
self.gula = gula
self.kalori = kalori
self.protein = protein
self.lemak = lemak
self.kategori = kategori
self.img_url = img_url
# Resep Schema
class ResepSchema(ma.Schema):
class Meta:
fields = ('id', 'nama', 'detail', 'gula',
'kalori', 'protein', 'lemak', 'kategori', 'img_url', 'timestamp')
# Init Schema
resep_schema = ResepSchema()
reseps_schema = ResepSchema(many=True)
## == End of Resep Class ========= ##
## == 3. KadarGulaDarahUser Class ========= ##
class KadarGulaDarahUser(db.Model):
id = db.Column(db.Integer, primary_key=True)
id_user = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
kadar_gula_darah = db.Column(db.Integer, nullable=False)
timestamp = db.Column(db.DateTime, nullable=False,
server_default=db.func.current_timestamp())
def __init__(self, id_user, kadar_gula_darah):
self.id_user = id_user
self.kadar_gula_darah = kadar_gula_darah
# KadarGulaDarahUser Schema
class KadarGulaDarahUserSchema(ma.Schema):
class Meta:
fields = ('id', 'id_user', 'kadar_gula_darah', 'timestamp')
# Init Schema
kadarguladarahuser_schema = KadarGulaDarahUserSchema()
kadarguladarahusers_schema = KadarGulaDarahUserSchema(many=True)
class KonsumsiGulaUserSchema(ma.Schema):
class Meta:
fields = ('total_gula', 'tanggal')
konsumsigulausers_schema = KonsumsiGulaUserSchema(many=True)
## == End of KadarGulaDarah Class ========= ##
# FR-03. User dapat menambahkan hidangan untuk hari ini
@app.route(f'{ENDPOINT}/hidangan', methods=['POST'])
def addHidangan():
try:
id_menu = request.json['id_menu']
id_user = request.json['id_user']
menu = Resep.query.filter_by(id=id_menu).first()
user = Users.query.filter_by(id=id_user).first()
print(type(menu.menu_user))
menu.menu_user.append(user)
db.session.add(menu)
db.session.commit()
return jsonify({'statusCode': 200, 'message': 'Success'})
except Exception as e:
return jsonify({'statusCode': 400, 'err': True, 'message': str(e)})
if __name__ == "__main__":
app.run(debug=True)
Being unable to insert duplicate data is normal with many-to-many relationships with SQLAlchemy. The ORM considers that the foreigns keys are the primary keys: in data modelisation, it's not good to have an id for relation rows! See https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#many-to-many:
The “association table” above has foreign key constraints established that refer to the two entity tables on either side of the relationship. The data type of each of association.left_id and association.right_id is normally inferred from that of the referenced table and may be omitted. It is also recommended, though not in any way required by SQLAlchemy, that the columns which refer to the two entity tables are established within either a unique constraint or more commonly as the primary key constraint; this ensures that duplicate rows won’t be persisted within the table regardless of issues on the application side:
association_table = Table('association', Base.metadata,
Column('left_id', ForeignKey('left.id'), primary_key=True),
Column('right_id', ForeignKey('right.id'), primary_key=True)
)
The ORM is also not allow you to edit directly this relationship table (in order to set the menu_hidangan_user.id field manually with an incremented value). The only solution I found is to not use the ORM and skip his syntax and help.
@app.route(f'{ENDPOINT}/hidangan', methods=['POST'])
def addHidangan():
try:
id_menu = (request.get_json(force=True))['id_menu']
id_user = (request.get_json(force=True))['id_user']
menu = Resep.query.filter_by(id=id_menu).first()
user = Users.query.filter_by(id=id_user).first()
current_id = db.session.execute("select id from menu_hidangan_user order by id desc").fetchall()
db.session.execute("insert into menu_hidangan_user (id, id_user, id_resep) values ("+( str( current_id[0][0]+ 1) if current_id else str(1))+","+str(user.id)+","+str(menu.id)+")")
db.session.commit()
return jsonify({'statusCode': 200, 'message': 'Success'})
except Exception as e:
return jsonify({'statusCode': 400, 'err': True, 'message': str(e)})
Result:
id | id_resep | id_user | timestamp
----+----------+---------+----------------------------
1 | 1 | 1 | 2021-12-04 10:45:54.009602
2 | 1 | 1 | 2021-12-04 10:45:59.244704
3 | 1 | 1 | 2021-12-04 10:46:00.185482
4 | 1 | 1 | 2021-12-04 10:46:00.84306
(4 rows)
Last comment: ORMs can be very unusefull because you don't have the possibility to dialog directly with the database. The ORM layer is too generic.