ZYwUUgFf

1DROP EXTENSION IF EXISTS www_fdw CASCADE;
2CREATE EXTENSION www_fdw;
3CREATE SERVER crm FOREIGN DATA WRAPPER www_fdw OPTIONS
4    (uri 'http://localhost:12345');  -- proxy server
5CREATE USER MAPPING FOR current_user SERVER crm;
6
7-- for testing trying to get 'name' out of the CRM 'accounts' table and
8   naming the foreign table the same as the table in CRM
9CREATE FOREIGN TABLE accounts (
10    name varchar(255)
11) SERVER crm;
12	
13import socketserver
14import http.server
15import urllib
16
17PORT = 12345
18
19class Proxy(http.server.SimpleHTTPRequestHandler):
20    def do_GET(self):
21        self.copyfile(urllib.urlopen(self.path), self.wfile)
22
23httpd = socketserver.ForkingTCPServer(('', PORT), Proxy)
24print ("serving at port", PORT)
25httpd.serve_forever()
26	
27import requests  
28import json
29
30#set these values to retrieve the oauth token
31crmorg = 'https://ORG.crm.dynamics.com' #base url for crm org  
32clientid = '00000000-0000-0000-0000-000000000000' #application client id  
33client_secret = 'SUPERSECRET'
34username = 'asd@asd.com' #username  
35userpassword = 'qwerty' #password
36authorizationendpoint =  'https://login.windows.net/ZZZZZZZZ-ZZZZ-ZZZZ-ZZZZ-ZZZZZZZZZZZZ/oauth2/authorize'
37tokenendpoint = 'https://login.windows.net/ZZZZZZZZ-ZZZZ-ZZZZ-ZZZZ-ZZZZZZZZZZZZ/oauth2/token' #oauth token endpoint
38
39#set these values to query your crm data
40crmwebapi = 'https://ORG.api.crm.dynamics.com/api/data/v8.2' #full path to web api endpoint  
41crmwebapiquery = '/accounts?$select=name&$orderby=name' #web api query (include leading /)
42
43#build the authorization token request
44tokenpost = {  
45    'client_id':clientid,
46    'client_secret': client_secret,
47    'resource':crmorg,
48    'oauthUrl': authorizationendpoint,
49    'username':username,
50    'password':userpassword,
51    'grant_type':'password'
52    }
53
54#make the token request
55tokenres = requests.post(tokenendpoint, data=tokenpost)
56
57#check the value of tokenres
58print(tokenres)
59
60#set accesstoken variable to empty string
61accesstoken = ''
62
63#extract the access token
64try:  
65    accesstoken = tokenres.json()['access_token']
66except(KeyError):  
67    #handle any missing key errors
68    print('Could not get access token')
69
70# check point for debugging
71# print(accesstoken)
72
73#if we have an accesstoken
74if(accesstoken!=''):  
75    #prepare the crm request headers
76    crmrequestheaders = {
77        'Authorization': 'Bearer ' + accesstoken,
78        'OData-MaxVersion': '4.0',
79        'OData-Version': '4.0',
80        'Accept': 'application/json',
81        'Content-Type': 'application/json; charset=utf-8',
82        'Prefer': 'odata.maxpagesize=500',
83        'Prefer': 'odata.include-annotations=OData.Community.Display.V1.FormattedValue'
84        }
85
86    #make the crm request
87    crmres = requests.get(crmwebapi+crmwebapiquery, headers=crmrequestheaders)
88
89    try:
90        #get the response json
91        crmresults = crmres.json()
92
93        #loop through it
94        for x in crmresults['value']:
95            # print (x['fullname'] + ' - ' + x['contactid'])
96            print (x['name'])
97    except KeyError:
98        #handle any missing key errors
99        print('Could not parse CRM results')