0

I am trying to pull data from Dynamics CRM 365 Online via Python3. During my research, I read that JayDeBeAPi was a good library to pull data from Dynamics CRM 365 Online. I successfully pip installed the library and tested the "import jaydebeapi" command.

I manage to connect to CRM, as there is no error message coming, but my script is not returning any row. I tried to both fetch the information from JayDeBeApi and/or Pandas, but I am still struggling with pulling data from CRM.

I have tried so far: - Access to Dynamics CRM 365 Online by using the jaydebeapi.connect() method - Execute a SELECT query via execute() or pandas.read_sql() method - Retrieve the rows via fetchall() method or simply printing the resulting dataframe.

import pandas as pd
import jaydebeapi


conn = jaydebeapi.connect('https://org.dynamics.com','datos.jdbc.dynamicscrm.jar', 'jdbc:dynamicscrm:',
                  {'user': "myaccount@email.com", 'password':"some_password"},
                          "C:/Program Files/.../datos.jdbc.dynamicscrm.jar",)

curs = conn.cursor()
curs.execute("select Name, Amount from Opportunities")
curs.fetchall()
curs.close()
conn.close()

### I have tried also: 
df = pd.read_sql("select Name, Amount from Opportunities", conn)
print(df)

I expect something like this:

#   Name    Amount
1   Tina    2000
2   Amanda  3000
3   Joseph  5000
4   Erick   6000

Thanks for the help and suggestions you can provide on successfully pulling data from Dynamics CRM 365 Online.

John Hill Escobar
  • 1
  • 1

1 Answers1

0

I know neither pyhton nor JayDeBeAPi, but if i understand right, JayDeBeAPI is used to connect to a database via JDBC.

You cannot connect to Dynamics Online via JDBC and SQL. You should use WebAPI, like the following sample: Connect to Python with WebAPI

SGeis
  • 291
  • 1
  • 7
  • Thank you so much for your answer @Sgeis. It is possible to connect to Dynamics 365 Online via JDBC. I have managed to do it via R (RJDBC package) and via Sisense. I have the proper driver not only to connect with the database, but also to pulled data from multi select attributions. My question leans towards the way the method connect() works. The [JayDeBeApi documentation](https://pypi.org/project/JayDeBeApi/) is not explicit enough to help me tackle the issue. – John Hill Escobar Feb 15 '19 at 19:35
  • You cannot directly access CRM Online Database via JDBC, you can only use WebApi, as described above. There is now way to access CRM Online Database via SQL. – SGeis Feb 26 '19 at 16:16