You can translate the question and the replies:

How to use python automatically set up a new data source in denodo?

A json file which included DB credentials is provided as below. eg. List of DB credentials: * Database Adapter * Database URL * Database * User ID * Password How to use these related DB credentials automatically set up a new data source in denodo by using python?
user
30-05-2023 05:42:17 -0400
code

5 Answers

Hi, It is possible to connect to Denodo from Python and run queries like you would in the VQL Shell. There exists a [Knowledge Base article](https://community.denodo.com/kb/en/view/document/How%20to%20connect%20to%20Denodo%20from%20Python%20-%20a%20starter%20for%20Data%20Scientists?category=Northbound%20Connections) about this exact topic. Once you have established your connection you can parse your JSON file to get the necessary parameters for the VQL to create your data sources. You should find examples on how to pass queries to Denodo from Python in the KB article linked above. For more information about the syntax and parameters of the VQL to create data sources you can refer to the section [Creating Data Sources](https://community.denodo.com/docs/html/browse/latest/en/vdp/vql/generating_wrappers_and_data_sources/creating_data_sources/creating_data_sources ) of the Virtual DataPort VQL Guide. Note that although it is possible to create data sources using VQL, Denodo recommends using the Design Studio or Virtual DataPort Administration Tool instead when possible. Hope this helps!
Denodo Team
30-05-2023 10:48:12 -0400
code
Thank you a lot for your reply. I am trying to use below python code to process VQL to automaticlly create JDBC data source in denodo. But I got error as below: Traceback (most recent call last): File "denodo_test.py", line 50, in <module> cur.execute(sql) UnicodeDecodeError: 'utf-8' codec can't decode byte 0xb8 in position 2: invalid start byte 1. Can you please advise what is wrong with my sql? I don't know why it has this above error. Thank you 2. What is hash method for the user password? ########### Python Code ########### import pyodbc import pandas as pd import numpy as np conn = pyodbc.connect('DSN={};Database={};UID={};PWD={}'.format('DenodoODBC', 'xxxxx', 'goxxxxx', 'xxxxxxxx')) cur = conn.cursor() print("connection successfully") sql = """" CREATE OR REPLACE DATASOURCE JDBC "Snowflake dev" DRIVERCLASSNAME = 'com.snowflake.client.jdbc.SnowflakeDriver' DATABASEURI = 'jdbc:snowflake://nxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/?db=database&schema=schema&warehouse=warehouse' USERNAME = 'xxxxxxxxxxx' USERPASSWORD = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' CLASSPATH = 'snowflake-1.x' DATABASENAME = 'snowflake' DATABASEVERSION = '1_x' ISOLATIONLEVEL = TRANSACTION_READ_COMMITTED FETCHSIZE = 1000 VALIDATIONQUERY = 'Select 1' INITIALSIZE = 4 MAXIDLE = -1 MINIDLE = 0 MAXACTIVE = 20 EXHAUSTEDACTION = 1 TESTONBORROW = true TESTONRETURN = false TESTWHILEIDLE = false TIMEBETWEENEVICTION = -1 NUMTESTPEREVICTION = 3 MINEVICTABLETIME = 1800000 POOLPREPAREDSTATEMENTS = false MAXOPENPREPAREDSTATEMENTS = -1 DATA_LOAD_CONFIGURATION ( BATCHINSERTSIZE = 500000 USEEXTERNALTABLES ( ONMOVEREAD = false, ONMOVEWRITE = true ) ); """ cur.execute(sql) cur.close() conn.close()
user
30-05-2023 22:11:57 -0400
Hi, I can see there are four quotation marks instead of three at the beginning of your sql string. I think removing one should solve your issue. Let me know if that helps!
Denodo Team
31-05-2023 03:14:53 -0400
code
Hey Denodo Team, That's great. It works!!! In addition, in order to set up the below user password parameter, I have to use the encrypted password as required. Can I ask what kind of encryption method used here? I need to convert the password to required encryped type. USERPASSWORD = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
user
31-05-2023 03:24:50 -0400
Hi, It is not necessary to pass in an encrypted value there. Denodo will encrypt it itself. However if it fits your use case you can use the following query to encrypt your value before passing the value in as a password: encrypt_password '<your_password>' Note: that is the whole query, no SELECT statement is needed. Hope this helps!
Denodo Team
31-05-2023 03:45:56 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here