CODEWITHSUNDEEP
pythonpostgresqlflaskflask-sqlalchemy
David J.
David J.

Reputation: 1913

relation does not exist in flask sqlalchemy, although create function called

I'm attempting to modularize my flask app by splitting models, the db, routers, etc into separate files. This has been giving me some trouble, because it seems that my tables are not being created as I expect.

I have this file called flask_postgres_server.py

from . import flask_server
from flask_sqlalchemy import SQLAlchemy
from flask import jsonify, request

from . import models
Tree = models.tree.Tree_postgres.Tree
app = flask_server.app  # this simply exports an instance of a flask app, so I can use it in another server with mongo


@app.route("/trees", methods=['POST'])
def create_tree_postgres():
    label = request.get_json['label']
    tree = Tree(label=label)
    tree.save()
    return jsonify(tree.get_dict())

I am importing a Tree model:

from ... import postgres_db
db = postgres_db.db

class Tree(db.Model):
    __tablename__ = 'trees'
    id = db.Column(db.Integer, primary_key=True)
    label = db.Column(db.String(), nullable=False)

    def save(self):
        db.session.add(self)
        db.session.commit()
        db.session.expunge_all()
        db.session.close()

    def get_dict(self):
        return {"label": self.label, "_id": self.id}

which in turn imports the db:

from . import flask_server
app = flask_server.app
import os
from dotenv import load_dotenv, find_dotenv
from flask_sqlalchemy import SQLAlchemy


"""
Imported by a server in order to set up a postgres connection for use by a Flask server
"""

load_dotenv(find_dotenv())

DB_NAME = os.environ.get("DB_NAME")
POSTGRES_HOST = os.environ.get("POSTGRES_HOST")
POSTGRES_PORT = os.environ.get("POSTGRES_PORT")
POSTGRES_USER = os.environ.get("POSTGRES_USER")
POSTGRES_PASSWORD = os.environ.get("POSTGRES_PASSWORD")

DB_URI = f'postgresql://{POSTGRES_USER}:{POSTGRES_PASSWORD}@{POSTGRES_HOST}:{POSTGRES_PORT}/{DB_NAME}'
app.config['SQLALCHEMY_DATABASE_URI'] = DB_URI

db = SQLAlchemy(app)
db.create_all()

When I run my server and get post data at /trees, I get this error message:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation "trees" does not exist
LINE 1: INSERT INTO trees (label) VALUES ('countries') RETURNING tre...
                    ^

[SQL: INSERT INTO trees (label) VALUES (%(label)s) RETURNING trees.id]
[parameters: {'label': 'countries'}]

Shouldn't this relation have been created automatically?

I can go into psql and create this table manually. Then I get another error saying:

DetachedInstanceError: Instance <Tree at 0x10d2facd0> is not bound to a Session

Is there something wrong with the way I've structured my code?

Upvotes: 1

Views: 1548

Answers (2)

Arnold Samuel Chan
Arnold Samuel Chan

Reputation: 106

DetachedInstanceError: Instance <Tree at 0x10d2facd0> is not bound to a Session implicitly give users clue that the object 0x10d2facd0 that you've created is already "disconnected" with the session.

You've already closed the session with db.session.close() but then tried to access the "closed" object afterward. It is better to save the get_dict result before you close it. It can be rewritten like this:

@app.route("/trees", methods=['POST'])
def create_tree_postgres():
    label = request.get_json['label']
    tree = Tree(label=label)
    response = tree.get_dict()
    tree.save()
    return jsonify(response)

Upvotes: 0

snakecharmerb
snakecharmerb

Reputation: 55630

The trees table does not get created because db.create_all() is called before the Tree model is imported.

In the code in the question, you can fix this by moving db.create_all() to after the definition of the Tree model. In your overall structure, db.create_all() will need to be called after all the model definitions have been executed (usually by importing the files that contain them).

Note that it's not a good idea to close the session in the save method - you won't be able to access the Tree instance afterwards when calling its get_dict method. I'd suggest leaving session life-cycle management to flask-sqlalchemy, Tree.save should just add and commit.

Finally label = request.get_json['label'] in the route handler should be label = request.get_json()['label'].

Upvotes: 3

Related Questions