如何在Heroku部署含MySQLdb的多请求Flask REST API应用？

I get it—most tutorials stick to basic "Hello World" Flask apps on Heroku, but you've got a real app with POST/GET endpoints and MySQL database calls. Let's walk through exactly how to get this up and running.

Step 1: Update Your Dependencies & Code for Heroku #

First, a quick heads-up: MySQLdb is a bit outdated and can be finicky on Heroku. Swap it out for mysqlclient—it's a drop-in replacement (your code won't need major changes) and plays nicer with modern Python and Heroku's build system.

1.1 Update Your Requirements File #

Create or update a requirements.txt file in your project root with these lines (use the latest versions that work for your app):

Flask==2.3.3
mysqlclient==2.2.0
gunicorn==21.2.0

1.2 Adjust Your Database Connection Code #

Hardcoding localhost, root, etc., won't work on Heroku—you need to use environment variables provided by your MySQL add-on. Modify your DB connection logic like this:

import os
from urllib.parse import urlparse
from flask import Flask, jsonify
import MySQLdb  # This still works with mysqlclient

app = Flask(__name__)

def get_db_connection():
    # Get the database URL from Heroku's environment variables
    db_url = os.environ.get('CLEARDB_DATABASE_URL') or os.environ.get('JAWSDB_URL')
    
    if db_url:
        # Parse the URL to extract credentials
        url = urlparse(db_url)
        db_host = url.hostname
        db_user = url.username
        db_pass = url.password
        db_name = url.path[1:]  # Remove the leading slash from the path
    else:
        # Fallback for local development
        db_host = "localhost"
        db_user = "root"
        db_pass = "root"
        db_name = "db_name"
    
    # Establish the connection
    db = MySQLdb.connect(host=db_host, user=db_user, passwd=db_pass, db=db_name)
    return db

@app.route('/display_table', methods=['POST'])
def display_webstats():
    db = get_db_connection()
    cursor = db.cursor()
    cursor.execute("select * from table_name")
    ws = cursor.fetchall()
    cursor.close()
    db.close()  # Don't forget to close connections to avoid leaks!
    return jsonify(ws), 200

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

1.3 Create a Procfile #

Heroku needs a Procfile to know how to run your app. Create a file named Procfile (no extension) in your root with this line:

web: gunicorn app:app

(Replace app:app with your_file_name:your_flask_instance_name if your main file isn't app.py or your Flask app isn't named app.)

Step 2: Add a MySQL Database to Your Heroku App #

Heroku doesn't include MySQL by default, so you'll use an add-on. Two popular free options are ClearDB MySQL and JawsDB MySQL.

Option A: ClearDB #

Run this command in your terminal (make sure you have the Heroku CLI installed):

heroku addons:create cleardb:ignite

This adds a free MySQL database and sets the CLEARDB_DATABASE_URL environment variable automatically.

Option B: JawsDB #

If ClearDB's free tier is full, try JawsDB:

heroku addons:create jawsdb:kitefin

This sets the JAWSDB_URL environment variable.

Step 3: Prepare Your Database Schema #

Your Heroku MySQL database is empty—you need to create your table_name table. Here's how:

1. Run this command to open a Python shell on Heroku:

   heroku run python
   

2. In the shell, connect to your DB and run your CREATE TABLE statement:

   import os
   from urllib.parse import urlparse
   import MySQLdb
   
   db_url = os.environ.get('CLEARDB_DATABASE_URL') or os.environ.get('JAWSDB_URL')
   url = urlparse(db_url)
   db = MySQLdb.connect(url.hostname, url.username, url.password, url.path[1:])
   cursor = db.cursor()
   # Replace this with your actual CREATE TABLE statement
   cursor.execute("CREATE TABLE table_name (id INT AUTO_INCREMENT PRIMARY KEY, column1 VARCHAR(255), column2 INT)")
   db.commit()
   cursor.close()
   db.close()
   exit()
   

(Or if you have existing data, export your local DB and import it into the Heroku DB using mysqldump.)

Step 4: Deploy to Heroku #

Assuming you have Git initialized in your project:

1. Commit your changes:

   git add .
   git commit -m "Prepare app for Heroku deployment"
   

2. Create a Heroku app (if you haven't already):

   heroku create your-app-name
   

3. Push your code to Heroku:

   git push heroku master
   

Step 5: Test Your App #

• Open your app in the browser: heroku open
• Test your POST endpoint using tools like curl or Postman:

  curl -X POST https://your-app-name.herokuapp.com/display_table
  

Troubleshooting Tips #

• If you get DB connection errors, check that the environment variable is set: heroku config (look for CLEARDB_DATABASE_URL or JAWSDB_URL)
• Check Heroku logs for detailed errors: heroku logs --tail
• Make sure your MySQL add-on is active: heroku addons

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