Python Forum

My site loads but one can't login. The issue is with database connection. When running on localhost, I had 3 files where this connection took code to get established.

Extensions.py

db = SQLAlchemy()

In all other files, db was imported from extensions.py

In a file called db_handler.py, I had the create_connection function set out

db_config = {
    'host': os.getenv('DB_HOST'),  # Database host (e.g., localhost or Google Cloud)
    'user': os.getenv('DB_USER'),  # Database username
    'password': os.getenv('DB_PASSWORD'),  # Database password
    'database': os.getenv('DB_NAME')  # The name of your database
}

def create_connection():
    connection = None
    try:
        connection = mysql.connector.connect(
            host=db_config['host'],
            user=db_config['user'],
            password=db_config['password'],
            database=db_config['database']
        )
        if connection.is_connected():
            print("Connected to MySQL database")
    except Error as e:
        print(f"Error while connecting to database: {e}")
    return connection

and then in my main.py, I had the below code

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = (
        f"mysql+pymysql://{os.getenv('DB_USER')}:{os.getenv('DB_PASSWORD')}@"
        f"{os.getenv('DB_HOST')}/{os.getenv('DB_NAME')}"
    )# Set a secret key for session management
app.secret_key = os.getenv('SECRET_KEY', 'my_secret_key')  # You should store this in an environment variable

# Initialize extensions
db.init_app(app)

It worked perfectly. Now, I am trying to move to google app engine with the application files, environment variables and database all on google cloud. So, I have had to bring in some changes.

My create_connection function now uses the unix socket method suggested in google cloud SQL documentation.

def create_connection() -> sqlalchemy.engine.base.Engine:
    """Initializes a Unix socket connection pool for a Cloud SQL instance of MySQL."""
    db_user = os.getenv("DB_USER")  # e.g., 'my-database-user'
    db_pass = os.getenv("DB_PASS")  # e.g., 'my-database-password'
    db_name = os.getenv("DB_NAME")  # e.g., 'my-database'
    unix_socket_path = os.getenv("INSTANCE_UNIX_SOCKET")  # e.g., '/cloudsql/project:region:instance'

    pool = sqlalchemy.create_engine(
        sqlalchemy.engine.url.URL.create(
            drivername="mysql+pymysql",
            username=db_user,
            password=db_pass,
            database=db_name,
            query={"unix_socket": unix_socket_path},
        ),
    )
    return pool

the extensions.py stays the same

db = SQLAlchemy()

Now I have to figure out how to change my main.py

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = (
        f"mysql+pymysql://{os.getenv('DB_USER')}:{os.getenv('DB_PASSWORD')}@"
        f"{os.getenv('DB_HOST')}/{os.getenv('DB_NAME')}"
    )# Set a secret key for session management

app.secret_key = os.getenv('SECRET_KEY', 'my_secret_key')  # You should store this in an environment variable

# Initialize extensions
db.init_app(app)

Running this doesn't work on google app engine. I have tried a few variations.

1. Adding a db_sqlalchemy() in main.py - this says - you have multiple sqlalchemy instances.
2. Combining db_sqlachemy() and db.init(app) into db_sqlalchemy(app) - in this case, it says - why are you not using db.init(app)?
3. and if I just keep it the same - it says login fails due to missing context.

My login code doesn't mention any context but Flask automatically captures context, so it was working on localhost. I assumed that flask on google app engine can't capture context automatically, so I added the app context in the login code, but it still fails to identify the app even though app is being imported from flask.

Can someone guide me on what the issue might be? and how can I approach to solve this problem? I am working on a production grade app, so I will prefer a clean solution even if more tedious.

Thanks.