[Python] SQLALCHEMY_BINDS doesn't work correctly with similar databases

Discussão em 'Python' iniciado por Stack, Fevereiro 11, 2025.

  1. Stack

    Stack Membro Participativo

    I am creating a server using flask and sqlalchemy, but when I try to use SQLALCHEMY_BINDS, it is unable to use the values assigned during queries. I tried to make some more detailed logs to identify some more specific data, but without results.

    Below is some information about the code and logs

    ===config.py


    import os

    class Config:
    SECRET_KEY = os.urandom(24)
    SESSION_COOKIE_HTTPONLY = True
    SESSION_COOKIE_SAMESITE = 'Lax'

    SQLALCHEMY_TRACK_MODIFICATIONS = False
    SQLALCHEMY_ECHO = True

    #SQLALCHEMY_DATABASE_URI = 'postgresql://postgres:postgres@localhost/teste'
    SQLALCHEMY_BINDS = {
    'db-teste': "postgresql://postgres:postgres@localhost/teste",
    'db-teste2': "postgresql://postgres:postgres@localhost/teste2"
    }


    ===__init__.py



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

    from app import config

    app = Flask(__name__, template_folder='/var/www/teste/html')

    app.config.from_object(config.Config)

    db = SQLAlchemy(app)
    bcrypt = Bcrypt(app)

    login_manager = LoginManager(app)
    login_manager.login_view = 'login'

    from app import views, models

    ===models.py

    from app import db, login_manager
    from flask_login import UserMixin

    def get_lista_acessos(tipo):
    return list(tipo)


    (Yes, I didn't define a explicit bind_key because the databases MUST HAVE the same structure, containing the same tables, it's a system restriction)

    class BaseModel(db.Model):
    __abstract__ = True
    __bind_key__ = None

    @classmethod
    def set_bind_key(cls, bind_key):
    cls.__bind_key__ = bind_key

    class User(BaseModel, UserMixin):
    __tablename__ = "usuarios"
    cpf = db.Column( db.String(14), unique=True, nullable=False, primary_key=True)
    nome = db.Column(db.String(150), nullable = False)
    tipo = db.Column( db.String(3), nullable=False )
    senha = db.Column( db.String(255), nullable = False)

    # Necessario uma vez que não é mais usado o campo "id" que seria o get_id default
    def get_id(self):
    return self.cpf

    def __repr__(self):
    return f'<User {self.nome}>'

    @login_manager.user_loader
    def load_user(user_id):
    return db.session.query(User).filter_by(cpf=user_id).first()#
    #return User.query.get(user_id) #Same result here


    ===Login route


    dicionarioComunidades = {
    'TESTE' : {'bindKey' : "db-teste", "entidadeBancaria" : "TESTE"},
    'TESTE2' : {'bindKey': "db-teste2", "entidadeBancaria" : "TESTE2"}
    }

    if comunidade in dicionarioComunidades:
    bind_key = dicionarioComunidades[comunidade]["bindKey"]
    entidadeBanc = dicionarioComunidades[comunidade]["entidadeBancaria"]

    if bind_key in app.config['SQLALCHEMY_BINDS']:
    db.session.remove()
    db.session.bind = db.get_engine(app, bind = bind_key)
    db.session.begin()

    BaseModel.set_bind_key(bind_key)#
    else:
    loggerSQL.debug(f"LOGIN-ROUTE::SQLALCHEMY_BIND: None")#
    else:
    loggerSQL.debug(f"LOGIN-ROUTE::COMUMITY: COMUNIDADE N SELECIONADA")#

    session['entidade-bancaria'] = entidadeBanc
    session['comunidade'] = comunidade

    try:
    db.session.remove()
    db.session.bind = db.get_engine(app, bind = bind_key)
    loggerSQL.debug(f"====LOGS ANTES DA QUERY====")
    loggerSQL.debug(f"LOGIN-ROUTE::BaseModel.__bind-key__: {BaseModel.__bind_key__}")#
    loggerSQL.debug(f"LOGIN-ROUTE::User.__bind_key__: {User.__bind_key__}")#
    {Residencia.__bind_key__}")#
    loggerSQL.debug(f"Current db.session.bind: {db.session.bind}")
    loggerSQL.debug(f"Current db.session.info: {db.session.info}")
    loggerSQL.debug(f"Expected Bind Key: {bind_key}")
    loggerSQL.debug(f"Configured SQLALCHEMY_BINDS: {app.config['SQLALCHEMY_BINDS']}")
    loggerSQL.debug(f"Configured SQLALCHEMY_BINDS: {app.config['SQLALCHEMY_BINDS'][bind_key]}")
    if bind_key is None:
    loggerSQL.error("O bind_key está None!")
    flash("Erro de configuração no banco de dados.")
    return render_template('login.html', versao=versao)
    else:
    loggerSQL.debug(f"Bind key is valid: {bind_key}")
    if bind_key not in app.config['SQLALCHEMY_BINDS']:
    loggerSQL.error(f"Bind key {bind_key} não encontrado em SQLALCHEMY_BINDS!")
    flash("Erro de configuração no banco de dados.")
    return render_template('login.html', versao=versao)
    loggerSQL.debug(f"Setting bind key manually: {bind_key}")
    loggerSQL.debug(f"Current db.session.bind: {db.session.bind}")
    loggerSQL.debug(f"Engine URL: {db.session.bind.url}")
    result = db.session.execute(text("SELECT 1")).fetchall()
    loggerSQL.debug(f"Test query result: {result}")
    user = db.session.query(User).filter_by(cpf=cpf).first()#
    #user = User.query.filter_by(cpf=cpf).first()
    except Exception as e:
    loggerSQL.error(f"Erro na consulta ao banco de dados: {e}")
    loggerSQL.debug(f"====LOGS DE ERRO====")
    loggerSQL.debug(f"LOGIN-ROUTE::BaseModel.__bind-key__: {BaseModel.__bind_key__}")#
    loggerSQL.debug(f"LOGIN-ROUTE::User.__bind_key__: {User.__bind_key__}")#
    loggerSQL.debug(f"Current db.session.bind: {db.session.bind}")
    loggerSQL.debug(f"Current db.session.info: {db.session.info}")
    loggerSQL.debug(f"Expected Bind Key: {bind_key}")
    loggerSQL.debug(f"Configured SQLALCHEMY_BINDS: {app.config['SQLALCHEMY_BINDS']}")
    loggerSQL.debug(f"Configured SQLALCHEMY_BINDS: {app.config['SQLALCHEMY_BINDS'][bind_key]}")
    flash("Erro na consulta ao banco de dados.")
    return render_template('login.html', versao=versao)

    ===LOGS WITHOUT SQLALCHEMY_DATABASE_URI

    2025-01-22 14:19:21,250 DEBUG === ====LOGS ANTES DA QUERY====
    2025-01-22 14:19:21,251 DEBUG === LOGIN-ROUTE::BaseModel.__bind-key__: db-teste
    2025-01-22 14:19:21,251 DEBUG === LOGIN-ROUTE::User.__bind_key__: db-teste
    2025-01-22 14:19:21,251 DEBUG === Current db.session.bind: Engine(postgresql://postgres:***@localhost/teste)
    2025-01-22 14:19:21,251 DEBUG === Current db.session.info: {}
    2025-01-22 14:19:21,251 DEBUG === Expected Bind Key: db-teste
    2025-01-22 14:19:21,251 DEBUG === Configured SQLALCHEMY_BINDS: {'db-teste': 'postgresql://postgres:postgres@localhost/teste', 'db-teste2': 'postgresql://postgres:postgres@localhost/teste2'}
    2025-01-22 14:19:21,252 DEBUG === Configured SQLALCHEMY_BINDS: postgresql://postgres:postgres@localhost/teste
    2025-01-22 14:19:21,252 DEBUG === Bind key is valid: db-teste
    2025-01-22 14:19:21,252 DEBUG === Setting bind key manually: db-teste
    2025-01-22 14:19:21,252 DEBUG === Current db.session.bind: Engine(postgresql://postgres:***@localhost/teste)
    2025-01-22 14:19:21,252 DEBUG === Engine URL: postgresql://postgres:***@localhost/teste
    2025-01-22 14:19:21,286 DEBUG === Test query result: [(1,)]
    2025-01-22 14:19:21,290 ERROR === Erro na consulta ao banco de dados: Bind key 'None' is not in 'SQLALCHEMY_BINDS' config.
    2025-01-22 14:19:21,290 DEBUG === ====LOGS DE ERRO====
    2025-01-22 14:19:21,290 DEBUG === LOGIN-ROUTE::BaseModel.__bind-key__: db-teste
    2025-01-22 14:19:21,290 DEBUG === LOGIN-ROUTE::User.__bind_key__: db-teste
    2025-01-22 14:19:21,291 DEBUG === Current db.session.bind: Engine(postgresql://postgres:***@localhost/teste)
    2025-01-22 14:19:21,291 DEBUG === Current db.session.info: {}
    2025-01-22 14:19:21,291 DEBUG === Expected Bind Key: db-teste
    2025-01-22 14:19:21,291 DEBUG === Configured SQLALCHEMY_BINDS: {'db-teste': 'postgresql://postgres:postgres@localhost/teste', 'db-teste2': 'postgresql://postgres:postgres@localhost/teste2'}
    2025-01-22 14:19:21,291 DEBUG === Configured SQLALCHEMY_BINDS: postgresql://postgres:postgres@localhost/teste


    I tried to put detailed logs to help The system only connects to SQLALCHEMY_DATABASE_URI, working normally I would like help to solve this problem, as I did not find any documentation or tutorial that would help me considering this specific restriction (using bind_key explicitly in models is not possible for me)

    Continue reading...
    Stack, Fevereiro 11, 2025
    #1

Compartilhe esta Página