why Postgres password when using SQL Server? the more we speak, the more confused I’m getting here
Hi Anna, me too
I used:prefect agent local start --env PREFECT__CONTEXT__SECRETS__MYSECRET=DB_password
and made this code there:
from prefect import Flow, task
from prefect.tasks.sql_server import SqlServerExecute
from prefect.tasks.secrets import PrefectSecret
from prefect.client import Secret
import pyodbc
@task
def define_Connection(query,pwd):
cnxn =pyodbc.connect(driver=[x for x in pyodbc.drivers() if ' for SQL Server' in x][-1], server="@_server", database="db_name" , uid="user", pwd="DB_name")
cursor = cnxn.cursor()
#insert dataframe
cursor.execute(query)
for row in cursor:
print('row = %r' % (row,))
cnxn.commit()
cursor.close()
query='''SELECT * FROM table_name'''
@task
def my_task(credentials):
"""A task that requires credentials to access something. Passing the
credentials in as an argument allows you to change how/where the
credentials are loaded (though we recommend using `PrefectSecret` tasks to
load them."""
pass
with Flow("Connection_test") as flow:
postgres_pwd =PrefectSecret("MYSECRET")
define_Connection(query,"DB_password")
# Register the flow under the "tutorial" project
flow.register(project_name="Step1")
if __name__ == "__main__":
flow.run()
and I still have the same error message.
Again, please remove this line
it’s done, still same error
Error during execution of task: OperationalError(‘HYT00’, ‘[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)’)
I don’t know how else to help - I’d love to help as much as I can but this is not a Prefect error. It’s a pyodbc error related to how you define the connection, and I shared all my ideas on how to help via:
- installing your pyodbc driver
- I showed you how to define connection and how to use Prefect secrets and how you can fall back to environment variables in the worst case
can you post it on StackOverflow and tag it as a pyodbc issue?
Hello,
I managed to connect to the database and do a fetch, the problem was in MSSQL-Server I installed several things and I reconfigured several files.
Great to hear that! Looks like this meme is true