App Engine连接Cloud SQL PostgreSQL（Flask框架+pg8000驱动）报错求助

Hey there, let's work through this connection issue together. You've already put in a ton of troubleshooting work, so let's zero in on the specific spots in your setup that might be causing the problem when deployed to App Engine.

First, Fix the SQLAlchemy Conflicts in Your Code #

Looking at your main.py, you're initializing SQLAlchemy twice—once by importing the db instance from __init__.py, and again with db = SQLAlchemy(app) later on. This creates conflicting connection pools and session managers, which is almost certainly contributing to your issues. Let's clean that up:

• Remove duplicate SQLAlchemy setup: Delete the lines db = SQLAlchemy(app) and migrate = Migrate(app, db) from main.py. Stick with the db instance you initialized in __init__.py.

• Fix model dependency: Update your BaseBall model to use the imported db from __init__.py instead of the local duplicate.

• Use built-in session management: Replace your manual Session setup with db.session—this ties into the correct connection pool linked to your app's context.

• Ditch the manual service account key: You're loading a key with ../perm/key-file, but relative paths don't work in App Engine's deployment environment. Worse, you don't need it! App Engine uses its default service account automatically. Just make sure this default account has the Cloud SQL Client IAM role assigned in your GCP project.

Update Your Database Connection Configuration #

Your pg8000 connection string is mostly correct, but let's tie it to your Flask app's config properly (using the __init__.py setup):

Option 1: Unix Socket (Recommended for App Engine) #

Add a config.py file (or update your existing one) with the database URL:

class Config:
    SQLALCHEMY_DATABASE_URI = "postgresql+pg8000://<db_user>:<db_pass>@/<db_name>?unix_sock=/cloudsql/<cloud_sql_connection_name>/.s.PGSQL.5432"
    SQLALCHEMY_TRACK_MODIFICATIONS = False

Replace placeholders with your actual database credentials and connection name.

Option 2: TCP (If Using Public IP) #

If you prefer TCP access, authorize App Engine's outgoing IPs in your Cloud SQL instance's "Authorized networks" settings. Then use this config:

class Config:
    SQLALCHEMY_DATABASE_URI = "postgresql+pg8000://<db_user>:<db_pass>@<db_host>:5432/<db_name>"
    SQLALCHEMY_TRACK_MODIFICATIONS = False

Fix Your App Engine Environment Configuration #

Your current app.yaml uses end-of-life Python 3.7—upgrade to a supported version like 3.10. Adjust the file based on your environment:

For App Engine Standard Environment: #

runtime: python310
instance_class: F1
env_variables:
  DB_USER: "user"
  DB_PASS: "123456"
  DB_NAME: "baseballpitchers"
  CLOUD_SQL_CONNECTION_NAME: "test-328103:us-east1:baseball"

For App Engine Flexible Environment: #

runtime: python310
env: flex
manual_scaling:
  instances: 1
resources:
  cpu: 1
  memory_gb: 0.5
  disk_size_gb: 10
env_variables:
  DB_USER: "user"
  DB_PASS: "123456"
  DB_NAME: "baseballpitchers"
  CLOUD_SQL_CONNECTION_NAME: "test-328103:us-east1:baseball"

Verify Your Dependencies #

Make sure your requirements.txt includes compatible versions of all necessary packages:

Flask==2.3.3
Flask-SQLAlchemy==3.1.1
pg8000==1.30.3
Flask-Migrate==4.0.5
sqlalchemy==2.0.23
pandas==2.1.4

Double-check that pg8000 works with your Python and SQLAlchemy versions.

Critical Step: Check App Engine Logs #

You mentioned connection errors but didn't share specific details. Head to GCP Console > App Engine > Services > Your Service > Logs. Look for lines with pg8000 or sqlalchemy—common issues include:

• Connection refused: Wrong socket path or stopped Cloud SQL instance.
• Authentication failed: Incorrect DB credentials or missing Cloud SQL Client permissions.
• Socket not found: Misspelled Cloud SQL connection name.

Cleaned-Up main.py Example #

Here's how your main.py should look after fixing the conflicts:

from __init__ import db, init_app
from flask import render_template, request, jsonify
from flask_migrate import Migrate
import logging

# Initialize app using your existing setup
app = init_app()
migrate = Migrate(app, db)

class BaseBall(db.Model):
    __tablename__ = 'baseballpitchers'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String())
    era = db.Column(db.Float())
    ip = db.Column(db.Float())
    sop9 = db.Column(db.Float())
    bbp9 = db.Column(db.Float())
    whip = db.Column(db.Float())

    def __init__(self, name, era, ip, sop9, bbp9, whip):
        self.name = name
        self.era = era
        self.ip = ip
        self.sop9 = sop9
        self.bbp9 = bbp9
        self.whip = whip

    def asdict(self):
        return {'name': self.name, 'era': self.era, 'ip': self.ip, 'sop9': self.sop9, 'bbp9': self.bbp9, 'whip': self.whip}

@app.route('/')
def home():
    return render_template('pubtemp/parent.html')

@app.route('/Player_Selection', methods=["POST", "GET"])
def data():
    if request.method == 'GET':
        fname = ''
        fera = request.args.get("ERA")
        fip = request.args.get("IP")
        fsop9 = request.args.get("SOp9")
        fbbp9 = request.args.get("BBp9")
        fwhip = request.args.get("WHIP")

        # Add safety checks for float conversions
        sname = BaseBall.name != fname
        sera = BaseBall.era < float(fera) if fera else True
        sip = BaseBall.ip > float(fip) if fip else True
        ssop9 = BaseBall.sop9 > float(fsop9) if fsop9 else True
        sbbp9 = BaseBall.bbp9 < float(fbbp9) if fbbp9 else True
        swhip = BaseBall.whip < float(fwhip) if fwhip else True

        # Use db.session instead of manual Session
        var = db.session.query(BaseBall).filter(sname, sera, sip, ssop9, sbbp9, swhip).all()
        final = [p.asdict() for p in var]
        p1 = sorted(final, key=lambda i: i['name'])
        return jsonify(p1)

@app.errorhandler(500)
def server_error(e):
    logging.exception('An error occurred during a request.')
    return """
    An internal error occurred: <pre>{}</pre>
    See logs for full stacktrace.
    """.format(e), 500

if __name__ == '__main__':
    app.run(host='127.0.0.1', port=8080)

Final Checks #

• Confirm your Cloud SQL instance is in the same region as your App Engine service.
• Ensure the baseballpitchers table exists (your __init__.py runs db.create_all() which should handle this).
• Double-check your DB user has permissions to access the baseballpitchers database.

Give these changes a try, and share any specific error messages from the logs if you still hit issues—I can help you narrow it down further!

内容的提问来源于stack exchange，提问作者drdrdr333