Db create all ошибка postgresql

This is probably not the main reason why the create_all() method call doesn’t work for people, but for me, the cobbled together instructions from various tutorials have it such that I was creating my db in a request context, meaning I have something like:

# lib/db.py
from flask import g, current_app
from flask_sqlalchemy import SQLAlchemy

def get_db():
  if 'db' not in g:
    g.db = SQLAlchemy(current_app)
  return g.db

I also have a separate cli command that also does the create_all:

# tasks/db.py
from lib.db import get_db

@current_app.cli.command('init-db')
def init_db():
  db = get_db()
  db.create_all()

I also am using a application factory.

When the cli command is run, a new app context is used, which means a new db is used. Furthermore, in this world, an import model in the init_db method does not do anything, because it may be that your model file was already loaded(and associated with a separate db).

The fix that I came around to was to make sure that the db was a single global reference:

# lib/db.py
from flask import g, current_app
from flask_sqlalchemy import SQLAlchemy

db = None
def get_db():
  global db
  if not db:
    db = SQLAlchemy(current_app)
  return db

I have not dug deep enough into flask, sqlalchemy, or flask-sqlalchemy to understand if this means that requests to the db from multiple threads are safe, but if you’re reading this you’re likely stuck in the baby stages of understanding these concepts too.

Пытаюсь запустить flask и postgresql в контейнере, но успешно запускается только postgresql, а flask падает с ошибкой:

Traceback (most recent call last):

  File "/app/app.py", line 12, in <module>

    db.create_all()

*Обновление* Вне докера тоже вылетает данная ошибка
Вот мои файлы:
models.py

from datetime import datetime

import flask_sqlalchemy

db = flask_sqlalchemy.SQLAlchemy()


class Quiz(db.Model):
    __tablename__ = 'quiz'
    id = db.Column(db.Integer, primary_key=True)
    question = db.Column(db.String(255))
    answer = db.Column(db.String(100))
    created = db.Column(db.DateTime, default=datetime.utcnow)

    def __repr__(self):
        return f"id: {self.id}, answer: {self.answer}, created: {self.created}"

app.py

import json
from flask import Flask, request

from models import db
import config

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = config.DATABASE_CONNECTION_URI
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.app_context().push()
db.init_app(app)
db.create_all()


# @app.route('/', methods=['GET'])
@app.route('/')
def hello_world():
    return 'Flask Dockerized!'

config.py

import os

user = "tzuser"
password = "password"
host = "postgres"
database = "tzdb"
port = "5432"

DATABASE_CONNECTION_URI = f'postgresql+psycopg2://{user}:{password}@{host}:{port}/{database}'

Dockerfile

FROM python:3.10

WORKDIR /app
COPY . .

RUN pip install -r requirements.txt

ENTRYPOINT ["python"]
CMD ["app.py"]

docker-compose.yml

version: '3.9'
services:
  app:
    build:
      context: ./app
    ports:
      - 5000:5000
    volumes:
      - ./app:/app
  postgres:
    image: postgres:13.3
    healthcheck:
      test: [ "CMD", "pg_isready", "-q", "-d", "postgres", "-U", "tzuser" ]
      timeout: 45s
      interval: 10s
      retries: 10
    restart: always
    environment:
      POSTGRES_DB: tzdb
      POSTGRES_USER: tzuser
      POSTGRES_PASSWORD: password
    ports:
      - 5432:5432
    volumes:
      # - ./db.sql:/docker-entrypoint-initdb.d/db.sql
      # - ./postgres-data:/var/lib/postgresql/data
      - ./db-data/:/var/lib/postgresql/data/
        # - ./init.sql:/docker-entrypoint-initdb.d/init.sql

database sqlite

To avoid errors while passing a single quote (‘) in a SQLite query, you can use SQL parameters.

Here is an example code:

import sqlite3

# connect to database
conn = sqlite3.connect('example.db')
c = conn.cursor()

# example query with parameter
query = "SELECT * FROM my_table WHERE name = ?"

# parameter to be passed in query
name_with_quote = "John O'Connor"

# execute the query with parameter
c.execute(query, (name_with_quote,))

# fetch the result
result = c.fetchall()

# print the result
print(result)

# close the connection
conn.close()

In this example, we use a parameter name_with_quote which contains a single quote. Instead of directly passing the parameter in the query string, we use a ? placeholder in the query. Then we pass the parameter as a tuple in the execute method. This way, the parameter is sanitized and any special character is properly escaped.

To summarize my problem, I try to generate my PostgreSQL database tables with the SQLAlchemy command db.create_all() and it returns the following error:

RuntimeError: No application found. Either work inside a view function or push an application context. See http://flask-sqlalchemy.pocoo.org/contexts/.

I have of course followed the documentation and threads on this platform and tried different alternatives:

from app import db, create_app
db.create_all()

This approach still fails with the same message. Another alternative was to include the create_app function inside the create_all() function so that it would have the correct context.

db.create_all(app=create_app())

This generated a new error because when the create_app() function is executed, it needs environment variables (I don’t know how to include the variables through the python terminal).

    db.init_app(app)
with app.app_context():
    db.create_all()
migrate.init_app(app, db)
mail.init_app(app) 

The result of this execution is NOTHING. Absolutely nothing happens inside the database, I am 100% sure that there is connectivity between my run and my database but no table or any kind of error is generated.

Of all the alternatives I think the most reasonable is to run the creation through the python terminal, however, I have the problem of environment variables that I already include by powershell when I run my application but it does not help me to initialize the database from the terminal.

Could someone give me some hint or help in some direction?

Solution

Greetings to everyone who has been interested in this thread and special thanks to mechanical_meat for his support and assistance.

I have learned a lot about python applications and the reasons why my application was failing. First of all I think that to understand the solution it is necessary that I comment my project structure:

├── entrypoint.py
├── config
├── app
│   ├── __init__.py
│   │   └── models.py
│   ├── module 1
│   │   └── models.py
│   ├── module 2
│   ├── module N
│   │   └── models.py

In case I needed to launch my application, I activated my virtual environment and added the necessary environment variables (FLASK_ENV, FLASK_APP or APP_SETTINGS_MODULE).

At this point if I try to run db.create_all() I get the error from this thread that the application context does not exist as only the following lines exist within my entrypoint:

from flask import send_from_directory
import os
from app import create_app

settings_module = os.getenv('APP_SETTINGS_MODULE')
app = create_app(settings_module)

if __name__ == '__main__':
    app.run(debug=True)

My solution in this case has been a silly one. The only thing I have done is to create two python files at the same height as my entrypoint. The first one contains information similar to my app generator but with the difference that all the variables in it are initialised with the configurations (from a security point of view it’s a bad approach):

from flask import Flask
from flask_login import LoginManager
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SECRET_KEY'] = 'SECRET'
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://postgres:...'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

login_manager = LoginManager(app)
login_manager.login_view = "login"
db = SQLAlchemy(app)

from models_db import User, Task, History

The other file includes the declaration of the classes to load the columns correctly. And, with this solution the generation of the database tables has worked, the only additional change has been to change the environment variable from app to my new class that is in charge of creating the DB. This is necessary because my goal is that it can be included in a dockerfile and the tables are created automatically.

I reiterate my thanks to all of you who have bothered to read this thread, if anyone has a more elegant solution I will be happy to analyse it in depth. If this can help someone else to reach a solution that would be great.

Answered By — Buscatrufas

Answer Checked By — Terry (WPSolving Volunteer)