Barys Rutman

08/24/2022, 11:45 AM
Hi guys, is it still relevant? Can we connect to MSSQL in Prefect 2.0?

Jeff Hale

08/24/2022, 4:22 PM
Hi Barys. If you mean for a Prefect datastore, Postgres or SQLite are the options. See this issue. As Ryan notes below, you can connect to MSSQL with method he describes.

Ryan Peden

08/25/2022, 1:27 AM
Barys, note that issue 6077 is about using other databases as a Prefect datastore. If you want to connect to and read data from a MSSQL database in Prefect flows and tasks using either pyodbc or pymssql, that should work. pyodbc is Microsoft's preferred way of connecting to SQL Server from Python, and they actively contribute to its development. I don't think it is async-friendly, however, so I'd suggest using synchronous tasks. Just make sure you don't return anything containing a cursor from a Prefect 2 task. Task return values need to be serializable and pyodbc cursors will throw an exception if you try to serialize them.
:gratitude-thank-you: 1

Jeff Hale

08/25/2022, 1:05 PM
Good point, Ryan! I edited my answer.