https://prefect.io logo
Join Slack
Powered by
<@ULVA73B9P> Why would this flow result in this er...
# ask-marvin
k
@Marvin Why would this flow result in this error?
Copy code
from prefect import flow, task, get_run_logger
from prefect.blocks.system import Secret
import sqlalchemy as sa
import pandas as pd

@task(name = "Query SQL Database")
def query_database(block_name: str, sql_query: str):
    logger = get_run_logger()
    <http://logger.info|logger.info>("Getting database credentials from Prefect Secret block...")
    try:
        credentials = Secret.load(block_name).get()
    except Exception as e:
        logger.error(f"Failed to get credentials: {e}")
        raise
    <http://logger.info|logger.info>("Connecting to database...")
    try:
        engine = sa.create_engine(credentials)
    except Exception as e:
        logger.error(f"Failed to connect to database: {e}")
        raise
    <http://logger.info|logger.info>("Running query on the database...")
    try:
        with engine.connect() as connection:
            result = pd.read_sql(sql_query, connection)
    except Exception as e:
        logger.error(f"Failed to run query: {e}")
        raise
    <http://logger.info|logger.info>(f"Query result: {result}")
    return result

@flow(name = "Simple Database Query Flow")
def flow(block_name: str, sql_query: str):
    result = query_database(block_name, sql_query)
    return result
Copy code
Failed to run query: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
(Background on this error at: <https://sqlalche.me/e/20/rvf5>)
m
✅ thought for 56.9 seconds
Short answer: the error is coming from pyodbc, not Prefect. It means the runtime where your flow is executing does not have an ODBC driver or DSN that matches your connection string. In other words, your 
Secret
likely contains either: - a DSN name that isn’t defined in the environment running the flow, or - a SQLAlchemy URL that uses 
mssql+pyodbc
but doesn’t specify a valid installed driver. Why this happens with Prefect flows - Your flow runs wherever your worker/agent runs (local machine, Docker image, Kubernetes pod, etc.). If that environment doesn’t have the ODBC driver (e.g., “ODBC Driver 17/18 for SQL Server”) installed or the DSN configured, pyodbc raises IM002. - If your secret is just 
DSN=Something;...
but there is no DSN named “Something” in that environment, you’ll get the same error. - If you use a URL like 
<mssql+pyodbc://user:pass@host/db>
without 
?driver=...
, pyodbc will also complain because there’s “no default driver specified.” How to fix it 1) Ensure the ODBC driver is installed in the runtime environment - Linux (e.g., in a Docker image): install Microsoft’s ODBC driver (17/18) and unixODBC. Example (Debian/Ubuntu):
Copy code
RUN apt-get update && apt-get install -y curl gnupg apt-transport-https
  RUN curl <https://packages.microsoft.com/keys/microsoft.asc> | apt-key add -
  RUN curl <https://packages.microsoft.com/config/debian/12/prod.list> > /etc/apt/sources.list.d/mssql-release.list
  RUN apt-get update && ACCEPT_EULA=Y apt-get install -y msodbcsql18 unixodbc unixodbc-dev
Docs: Install Microsoft ODBC Driver for SQL Server - macOS: 
brew install --cask msodbcsql17
(or 18) and 
brew install unixodbc
. - Windows: install “ODBC Driver 17/18 for SQL Server” from Microsoft. 2) Use a full SQLAlchemy URL with a driver parameter (avoid DSNs if you can) - Store a connection string like this in your Prefect Secret:
Copy code
<mssql+pyodbc://username:password@server:1433/database?driver=ODBC+Driver+18+for+SQL+Server&Encrypt=yes&TrustServerCertificate=yes>
Note: - Spaces in the driver name must be encoded with 
+
- Driver name must match exactly what’s installed (e.g., “ODBC Driver 18 for SQL Server”) - Add `Encrypt`/`TrustServerCertificate` as needed for your environment SQLAlchemy docs: SQL Server + pyodbc connection strings 3) If you must use a DSN, define it in the environment that runs the flow - You’ll need 
odbc.ini
and 
odbcinst.ini
(Linux) or a System DSN (Windows) inside the container/host where the Prefect worker runs. Your Secret must then reference that DSN, e.g., 
mssql+pyodbc://@MyDsn
. A couple of smaller notes on your snippet - It looks like Slack mangled your logging lines; make sure they’re actually 
<http://logger.info|logger.info>("...")
in your code and not 
<http://logger.info|logger.info>(...)
. - Naming your flow function 
flow
works but shadows the decorator name. Consider renaming it to avoid confusion:
Copy code
@flow(name="Simple Database Query Flow")
  def simple_db_query_flow(block_name: str, sql_query: str):
      return query_database(block_name, sql_query)
If you share: - what database you’re connecting to (SQL Server/Postgres/etc.), - the exact connection string format stored in your Secret (feel free to redact credentials), and - where the flow is running (local, Docker image, Kubernetes, OS), I can help you craft the correct connection URL and, if needed, a Dockerfile snippet to install the right drivers.
2 Views
Open in Slack
PreviousNext
Powered by