One-to-one relationship DB Model not working

Question

Background:

I have a project where users are able to upload files. In the database, I want to store the file path to the uploaded file.

There are 2 tables within the DB; a Users & FileUploadPath. These tables have a one-to-one relationship.

The Flask-SQLAlchemy model I have currently is:

import os
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////{}'.format(os.path.join(os.getcwd(), "test.db"))
db = SQLAlchemy(app)


class User(db.Model):

    __tablename__ = 'Users'

    userid = db.Column(db.Integer, primary_key=True, autoincrement=True)
    fname = db.Column(db.String(80), nullable=False)
    lname = db.Column(db.String(80), nullable=False)
    phonenum = db.Column(db.String(10), nullable=False)
    email = db.Column(db.String(60), nullable=False)
    password = db.Column(db.String(60), nullable=False)
    validated = db.Column(db.Boolean, default=False)

    child = db.relationship('FileUploadPath', uselist=False, backref='Users')


class FileUploadPath(db.Model):

    __tablename__ = 'FileUploadPath'

    fileUploadID = db.Column(db.Integer, primary_key=True, autoincrement=True)
    w9FilePath = db.Column(db.String(60), nullable=False)
    gcFilePath = db.Column(db.String(60), nullable=False)
    livescanFilePath = db.Column(db.String(60), nullable=False)
    gcaFilePath = db.Column(db.String(60), nullable=False)

    userID = db.Column(db.Integer, db.ForeignKey('Users.userid'))

To create a new user I use the following:

newuser = User(fname='Bob', lname='Smith', phonenum='6551234567', email='bobsmith@gmail.com', password='fe287943hfbwiqey281')

With this command I would expect to see a userID within the FileUploadPath table; currently seeing only the created user in the Users table, but NULL inside the userID column.

With a query I would also want to see all the uploaded file paths information related to a specific user.

Is the relationship between the model's setup correctly? If not, how would I go about declaring the relationship?

EDIT:

What I want to do is store the fileUploadID inside of the Users table. A new row inside FileUploadPath table should be inserted for every new user that gets created. Because when I create a new user, all columns inside FileUploadPath table will be empty; since the new user has not uploaded any files yet.

Answer 1

The table relationships look ok, except that backref='Users' is an odd choice of name, as this will add a "Users" property to the FileUploadPath, which doesn't follow the naming convention of the rest of its properties. I would have expected this to be backref='user' instead.

Now, the main problem is that you haven't specified a FileUploadPath that newuser should be associated with, so it hasn't inserted any entry in FileUploadPath that references the ID of newuser. Or maybe you have inserted a new FileUploadPath that you intended to be linked to newuser, though you haven't shown that code in your example. In any case, it doesn't know which FileUploadPath to associate with which User, and it doesn't automatically create a new FileUploadPath for each User, and so when you only create a new User, or if you individually create new User and FileUploadPath but don't link them, you don't end up with a FileUploadPath associated with your new User.

So, what you need to do is to link a FileUploadPath to newuser either by adding a child=newFileUploadPath parameter (where newFileUploadPath is the already constructed FileUploadPath object) when you call the User constructor, or by individually constructing and adding the User and FileUploadPath objects, and setting newuser.child = newFileUploadPath, or newFileUploadPath.user = newuser. (Note that newFileUploadPath.user assumes my suggested backref='user' change.)

Edit: Here's an MVCE that shows how to add a file upload for a user. But the file upload does not exist when the user is initially created, which should be the expected behaviour. You definitely should not have an entry in FileUploadPath that contains all empty strings. Also, if you plan to allow up to 4 uploads per user, you need to make it a many-to-one relationship instead, by getting rid of that uselist=False and treating Users.child as a list, and maybe also renaming it to children or uploads.

(Note, this example uses plain SQLAlchemy rather than Flask-SQLAlchemy, so some parts of the Model definition and database setup may appear different to your implementation.)

import os
import sys
from sqlalchemy import Column, ForeignKey, Integer, String, Boolean
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy import create_engine

Base = declarative_base()

class User(Base):
    __tablename__ = 'Users'

    userid = Column(Integer, primary_key=True, autoincrement=True)
    fname = Column(String(80), nullable=False)
    lname = Column(String(80), nullable=False)
    phonenum = Column(String(10), nullable=False)
    email = Column(String(60), nullable=False)
    password = Column(String(60), nullable=False)
    validated = Column(Boolean, default=False)

    child = relationship('FileUploadPath', uselist=False, backref='user')


class FileUploadPath(Base):
    __tablename__ = 'FileUploadPath'

    fileUploadID = Column(Integer, primary_key=True, autoincrement=True)
    w9FilePath = Column(String(60), nullable=False)
    gcFilePath = Column(String(60), nullable=False)
    livescanFilePath = Column(String(60), nullable=False)
    gcaFilePath = Column(String(60), nullable=False)

    userID = Column(Integer, ForeignKey('Users.userid'))

engine = create_engine('sqlite:///')
Base.metadata.create_all(engine)
session_factory = sessionmaker(bind=engine)
session = session_factory()

# Insert a new user with no uploads:
newuser = User(fname='Bob', lname='Smith', phonenum='6551234567', email='bobsmith@gmail.com', password='fe287943hfbwiqey281')
session.add(newuser)
session.commit()

# Query for users and uploads:
print(session.query(FileUploadPath).count())
# 0

print(session.query(User).count())
# 1

print(session.query(User).one().child)
# None

# Insert an upload for the user:
newupload = FileUploadPath(w9FilePath='some_path', gcFilePath='another_path', livescanFilePath='yet_another_path', gcaFilePath='another_path_still', user=newuser)
session.add(newupload)
session.commit()

# Again, query for users and uploads:
print(session.query(FileUploadPath).count())
# 1

print(session.query(User).one().child)
# <FileUploadPath object>

print(session.query(User).one().child.w9FilePath)
# 'some_path'