I want to connect to a SQL-Server database that I have Locally on Windows, I’m using Uuntu WSL, I’ve installed Prefect…
I created a flow, and on my code I offset the connection with pyodbc.connect.
During a quick run, I have 3 errors each time: -ValueError: Local Secret “MYSECRET” was not found -Error during execution of task: Error(‘01000’, “[01000] [unixODBC][Driver Manager]Can’t open lib ‘[x for x in pyodbc.drivers() if ’ for SQL Server’ in x][ -1]Server=EQ -EQ6288793\SQLEXPRESS’: file not found (0) (SQLDriverConnect)”) -OperationalError(‘HYT00’, ‘[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)’)
I have already created the var env and I have added it in my code, the agent and in run, I have ODBC DRIVER installed…!
Can you help me at this point or share with me the procedure it takes to connect to SQL server with Prefect
N.B: apart from Prefect on local python, the connection to the database works well.
Thank you Anna.
I’m on Prefect Server, this is how I create my env var “MYSECRET” follows what is on the doc:
1: export PREFECT__CONTEXT__SECRETS__MYSECRET=“MYSECRET”
2: prefect agent docker start --env PREFECT__CONTEXT__SECRETS__MYSECRET=“MYSECRET”
3: I added this line in my script in Flow to call the env var
my_secret_value = Secret(“MYSECRET”).get()
and for agent: python3 -m prefect agent local start --env PREFECT__CONTEXT__SECRETS__POSTGRES_PASSWORD=passwordXXXX
I tested and it’s good I don’t have the error for Secret.
Now I have the third error which is: OperationalError(‘HYT00’, ‘[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)’)
@RAISS_Zineb: Hello community,
I tried to connect Prefecta with my SQL server database, but I got this error. who has already had this error, or has an idea about this error?
@Kevin_Kho: Let’s see if someone in the community knows more about this
@Anna_Geller: it’s a driver issue - either it’s not installed in your execution environment, or you don’t point at the proper driver in your pyodbc connection. check those docs for more info
Install the Microsoft ODBC driver for SQL Server (Linux) - ODBC Driver for SQL Server
@RAISS_Zineb: normally in the code I work with in DRIVE I use: SQL Server Native Client 11.0
I made some change, and now I have another error message: Error during execution of task: OperationalError(‘HYT00’, ‘[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0 ) (SQLDriverConnect)’)
@Anna_Geller: You can try to automatically infer driver using this:
driver=[x for x in pyodbc.drivers() if ' for SQL Server' in x][-1]
@RAISS_Zineb: Not working! the same error Task ‘define_Connection’: Exception encountered during task execution!
Traceback (most recent call last):
File “/home/zineb/.local/lib/python3.8/site-packages/prefect/engine/task_runner.py”, line 880, in get_task_run_state
value = prefect.utilities.executors.run_task_with_timeout(
File “/home/zineb/.local/lib/python3.8/site-packages/prefect/utilities/executors.py”, line 468, in run_task_with_timeout
return task.run(**args, ***kwargs) # type: ignore
File “sql_connection”, line 10, in define_Connection
cnxn = pyodbc.connect(
pyodbc.OperationalError: (‘HYT00’, ‘[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)’)
@Anna_Geller: can you share your updated flow? (in text not image)
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={ODBC Driver 17 for SQL Server};"
"Server=EQ-EQ6288793\SQLEXPRESS;"
"Database=OTI_Djoliba;"
"UID=sa;"
"Trusted_Connection=no;")
cursor = cnxn.cursor()
#insert dataframe
cursor.execute(query)
for row in cursor:
print('row = %r' % (row,))
cnxn.commit()
@ Task
def define_query():
return """
SELECT * FROM {Trafic_OTI_Djoliba};
"""
@ 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("postgres_example") as flow:
postgres_pwd = PrefectSecret("POSTGRES_PASSWORD")
query = define_query()
define_Connection(query=query,pwd=postgres_pwd)
my_secret = PrefectSecret("MYSECRET")
res = my_task(credentials=my_secret)
# Register the flow under the "tutorial" project
flow.register(project_name="Step1")
if __name__ == "__main__":
flow.run()
@Anna_Geller: so you didn’t implement my suggestion then. I was suggesting:
@task
def define_connection(query,pwd):
cnxn = pyodbc.connect(driver=[x for x in pyodbc.drivers() if ' for SQL Server' in x][-1], server="EQ-EQ6288793\SQLEXPRESS", database="OTI_Djoliba", uid="sa", pwd="xxx")
cursor = cnxn.cursor()
#insert dataframe
cursor.execute(query)
for row in cursor:
print('row = %r' % (row,))
cnxn.commit()
@RAISS_Zineb: I made the changes requested, but I have an error related to the Secret variable that I created with the value MYSECRET: “ValueError: Local Secret “MYSECRET” was not found.”
Normally it is already created and I added it to the code
@Kevin_Kho: Did you add MYSECRET as an env var to the agent?
@RAISS_Zineb: yes @Kevin_Kho I checked and I found that this line is missing so I added “my_secret = PrefectSecret(“MYSECRET”)” . Thank you @Anna_Geller I made the change at the Driver level and I got an error related to driver=[x for x in pyodbc.drivers() if ’ for SQL Server’ in x][-1]: “Error during execution of task : Error(‘01000’, “[01000] [unixODBC][Driver Manager]Can’t open lib ‘[x for x in pyodbc.drivers() if ’ for SQL Server’ in x][-1]Server=EQ -EQ6288793\SQLEXPRESS’: file not found (0) (SQLDriverConnect)”)”
@Anna_Geller: you still didn’t implement the same syntax I suggested. I suggested using normal keyword arguments such as driver, uid etc, not the single string syntax. Can you look more closely at the syntax I sent you and compare it to your current code? Can you see the difference in how the pyodbc connection is defined in each of those?
@RAISS_Zineb: Yes I did the same as you, the only change in the Driver so I used what you suggested: driver=[x for x in pyodbc.drivers() if ’ for SQL Server’ in x][-1]
here are my connection parameters: cnxn = pyodbc.connect(driver=[x for x in pyodbc.drivers() if ’ for SQL Server’ in x][-1], server=“EQ-EQ6288793\SQLEXPRESS”, database= “OTI_Djoliba”, uid=“sa”, pwd=“xxx”)
@Anna_Geller: exactly, this syntax should work. perhaps you can run in iPython pyodbc.drivers() to see what drivers do you have in your environment and to confirm that pyodbc can find those?
@RAISS_Zineb: with pyodbc.drivers() command, return this
[‘ODBC Driver 17 for SQL Server’]
And now I see another error: Error during execution of task: OperationalError(‘HYT00’, ‘[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)’)
I did what we discuss on Slack, but it doesn’t give me results, for sharing it’s not the same problem as me, I can’t connect to my database!
On the logs I find that there is a problem with the define connection function on which there are the parameters to connect to the database :
Task ‘define_connection’: Finished task run for task with final state: ‘Failed’; pyodbc.OperationalError: (‘HYT00’, ‘[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)’)
Can you try that without Prefect? Does it work this way? If so, can you share the code without Prefect that works for you but doesn’t work with Prefect?
Yes,
I tested the same code without Prefect, on Jupyter notebook, it works
def define_Connection(query,pwd):
cnxn =pyodbc.connect(driver=[x for x in pyodbc.drivers() if ’ for SQL Server’ in x][-1], server=“EQ-EQ6288793\SQLEXPRESS”, database=“OTI_Djoliba”, uid=“sa”, pwd=“Zineb@21”)
cursor = cnxn.cursor() #insert dataframe
cursor.execute(query)
for row in cursor:
print(‘row = %r’ % (row,))
cnxn.commit()
cursor.close()
query=’’‘SELECT * FROM Trafic_OTI_Djoliba’’’
define_Connection(query,“Zineb@21”)
No it’s not the real user & password, Thanks Anna.
I created a new script, on which I created a new flow.
But I can’t find my flow on UI prefect server and when I execute it with the command: python3 file_name this error shows me: " my_secret_value = Secret(“MYSECRET”).get()
File “/home/zineb/.local/lib/python3.8/site-packages/prefect/client/secrets.py”, line 131, in get
raise ValueError(
ValueError: Secrets should only be retrieved during a Flow run, not while building a Flow."
I just copy the same code and I add the part of the SECRET and the flow
Hi Anna,
I did the same thing from : Error during execution of task: OperationalError(‘HYT00’, ‘[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)’)the flow, please find the code below, but still the same error message.
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="aaaaaaaa\SQLEXPRESS", database="DB_name", uid="name_user", pwd="XXXXX")
cursor = cnxn.cursor()
#insert dataframe
cursor.execute(query)
for row in cursor:
print('row = %r' % (row,))
cnxn.commit()
cursor.close()
with Flow("Connection") as flow:
postgres_pwd = PrefectSecret("POSTGRES_PASSWORD")
#query = define_query()
query='''SELECT * FROM Table_name''
define_Connection(query,"PPPPPPP")
my_secret_value = Secret("MYSECRET").get()
flow.register(project_name="Step1")
if __name__ == "__main__":
flow.run()
for the error:ValueError: Secrets should only be retrieved during a Flow run, not while building a Flow." the problem was in the line: my_secret_value = Secret(“MYSECRET”).get()
I deleted it and the flow was runner and create. but I still get the error: pyodbc.OperationalError: ('HYT00', '[HYT00][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')
I see that my connection on Prefect cannot communicate with my database which is local.
Is there anyone who is already connected to a SQL DB with Prefect?
For sure, but the issue is with your DB connection definition, not with Prefect or SQL Server You need to do the same what you did in your local script in your Prefect flow - perhaps switch to environment variables instead of using Prefect Secrets?
Yoop, a problem that I have is not prefect or sql or my BD but earlier to communicate between the two, something in the config I miss I don’t know is what in Secret or what!
For the creation of the Secret I followed the doc: Secrets | Prefect Docs
Ok, For information, before runner flow I run the Prefect Agent which calls the env var with the command: python -m prefect agent local start --env PREFECT__CONTEXT__SECRETS__POSTGRES_PASSWORD=password_of_DB