使用Flask-SQLAlchemy与pyodbc连接MSSQL Server 2016失败求助

Hey there, since you mentioned pyodbc works perfectly on its own but Flask-SQLAlchemy is throwing that "Data source name not found" error, the issue almost always boils down to an incorrect connection URI format or missing driver specification in your SQLAlchemy config. Let's walk through the most likely fixes:

1. Use a DSN-less Connection URI (Most Reliable) #

Instead of relying on a pre-configured DSN, build the full connection URI directly using the same parameters that work for your standalone pyodbc code. This eliminates any DSN misconfiguration issues.

Example Conversion: #

If your working pyodbc code looks like this:

import pyodbc
# Working pyodbc connection
conn = pyodbc.connect(
    "DRIVER={ODBC Driver 11 for SQL Server};"
    "SERVER=YOUR_SERVER_NAME\\INSTANCE;"
    "DATABASE=YOUR_DB_NAME;"
    "UID=YOUR_USER;"
    "PWD=YOUR_PASSWORD"
)

Convert this to a Flask-SQLAlchemy URI by following this pattern:

app.config['SQLALCHEMY_DATABASE_URI'] = (
    "mssql+pyodbc://YOUR_USER:YOUR_PASSWORD@YOUR_SERVER_NAME\\INSTANCE/YOUR_DB_NAME"
    "?driver=ODBC+Driver+11+for+SQL+Server"
)

• Key Notes:
  • Replace spaces in the driver name with + (e.g., ODBC+Driver+11+for+SQL+Server)
  • If your server uses a non-default port, append it to the server name like YOUR_SERVER_NAME,1433
  • For Windows Authentication (trusted connection), skip the username/password and add &trusted_connection=yes at the end:

    app.config['SQLALCHEMY_DATABASE_URI'] = (
        "mssql+pyodbc://@YOUR_SERVER_NAME\\INSTANCE/YOUR_DB_NAME"
        "?driver=ODBC+Driver+11+for+SQL+Server&trusted_connection=yes"
    )
    

2. Verify DSN Configuration (If You Prefer Using DSN) #

If you're set on using a DSN, double-check these details:

• 32-bit vs 64-bit ODBC Manager: Since you're on Python 2.7 (which is likely 32-bit), you need to configure the DSN in the 32-bit ODBC Data Source Manager. Access it via C:\Windows\SysWOW64\odbcad32.exe (not the default 64-bit one in Control Panel).
• Match DSN Name Exactly: Ensure the DSN name in your URI matches the one you configured. The URI format for DSN is:

  # With credentials
  app.config['SQLALCHEMY_DATABASE_URI'] = "mssql+pyodbc://YOUR_USER:YOUR_PASSWORD@YOUR_DSN_NAME"
  # Windows Auth
  app.config['SQLALCHEMY_DATABASE_URI'] = "mssql+pyodbc://@YOUR_DSN_NAME?trusted_connection=yes"
  

3. Confirm Driver Compatibility #

Even though pyodbc works, make sure the driver name in your SQLAlchemy URI exactly matches what pyodbc is using. You can list all available drivers on your system with this pyodbc snippet:

import pyodbc
print(pyodbc.drivers())

Pick one of the drivers from the output (e.g., ODBC Driver 13 for SQL Server) and use it in your URI (replacing spaces with +).

Final Checks #

• Double-check for typos in server names, database names, or driver names—small mistakes here are common!
• Ensure SQLALCHEMY_DATABASE_URI is set before initializing your SQLAlchemy instance (e.g., before db = SQLAlchemy(app)).

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