https://prefect.io logo
Title
r

RAISS Zineb

04/18/2022, 11:39 PM
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?
k

Kevin Kho

04/19/2022, 12:48 AM
Let’s see if someone in the community knows more about this
👍 1
a

Anna Geller

04/19/2022, 10:14 AM
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
r

RAISS Zineb

04/19/2022, 11:53 AM
I have already installed ODBC 17
a

Anna Geller

04/19/2022, 11:55 AM
can you share your flow code?
r

RAISS Zineb

04/19/2022, 12:01 PM
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)')
a

Anna Geller

04/19/2022, 1:55 PM
You can try to automatically infer driver using this:
driver=[x for x in pyodbc.drivers() if ' for SQL Server' in x][-1]
r

RAISS Zineb

04/19/2022, 2:21 PM
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)')
a

Anna Geller

04/19/2022, 5:42 PM
can you share your updated flow? (in text not image)
r

RAISS Zineb

04/19/2022, 6:51 PM
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()
a

Anna Geller

04/19/2022, 6:54 PM
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()
r

RAISS Zineb

04/19/2022, 9:01 PM
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
k

Kevin Kho

04/19/2022, 9:05 PM
Did you add MYSECRET as an env var to the agent?
r

RAISS Zineb

04/19/2022, 9:41 PM
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)")"
a

Anna Geller

04/19/2022, 10:29 PM
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?
r

RAISS Zineb

04/19/2022, 11:10 PM
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")
a

Anna Geller

04/19/2022, 11:13 PM
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?
r

RAISS Zineb

04/19/2022, 11:18 PM
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)')🙁🤔