Category: Allgemein

A dataframe in Python can be compared to a table in a database. I also get idea that database functionalities are translated into functions that can be found in dataframes. It is also true that a database table can be quickly read into a dataframe. See: con = pyodbc.connect(‘DSN=AzureSQL;UID=tomvanmaanen;PWD=*******’) cur = con.cursor() sql = ‘SELECT…

It can be handy to copy data from Salesforce to SQL Server if one would like to analyse the data. After all, the query language in Salesforce is not easy to use, whereas SQL Server allows easy access to data, good analytical possibilities and rapid response time. Currently, Python is my favorite tool. It allows…

I now play with Python to communicate with HTTP servers. The idea that signals are sent to a server and the answer is subsequently analysed. My first attempt is shown below: import urllib3 http = urllib3.PoolManager() resp = http.request('GET', 'http://van-maanen.com/') print(resp.data) print(resp.status) the output can be seen as a series of symbols that can be…

I wrote four programmes in Python that enable us to maintain tables in Salesforce. The four functionalities are: creating data, reading data, updating data and deleting data. The programmes The programmes are stored in accompanying zip file. The programmes follow more or less the same logic. The first part is getting access to the system.…

Salesforce is a CRM tool that allows monitoring customer contacts. It also allows following leads. For back ground technicians, it is interesting to know that it is possible to query the data. The structure of the query language resembles a bit SQL. The structure “select fields from table”may sound familiar to our ears. However SOQL…

Herewith, I provide another script to set up a user in the SQL Server environment. The idea is that a role is created on the database. The user that is subsequently created is then added as a member to that role. First, the login is created. It is created on the master database. CREATE LOGIN…

Python has Pandas its tool to handle matrices, also known as dataframes in Pyhon speak. It is possible to join dataframes, much like joining tables. I found a nice example on the internet with several possible choices. As a first step, two dataframes were declared: import pandas as pd The first dataframe: customer=pd.DataFrame({‘id’:[1,2,3,4,5,6,7,8,9],’name’:[‘Olivia’,’Aditya’,’Cory’,’Isabell’,’Dominic’,’Tyler’,’Samuel’,’Daniel’,’Jeremy’],’age’:[20,25,15,10,30,65,35,18,23],’Product_ID’:[101,0,106,0,103,104,0,0,107],’Purchased_Product’:[‘Watch’,’NA’,’Oil’,’NA’,’Shoes’,’Smartphone’,’NA’,’NA’,’Laptop’],’City’:[‘Mumbai’,’Delhi’,’Bangalore’,’Chennai’,’Chennai’,’Delhi’,’Kolkata’,’Delhi’,’Mumbai’]}) The second…

Granting privs in SQL Server is done in three steps. As first step, one must create a login to the SQL Server. This can be seen as getting the entrance to the data fort. CREATE LOGIN lezer WITH PASSWORD = ‘password’ GO One must then get entrance to the database. One must create a user…

SQL Server is has several possibilities to calculate elapsed time (such as age). Two approaches are possible. The first idea is to calculate the number of hours and divide this by the number of hours in a year. The other possibility is to make a distinction between the number of a day in a month…

Below, I provide a simple Python programme which allows to add data from an external table to Salesforce. The programme uses simple_salesforce as a library. In the first few lines, the necessary libraries are imported. from simple_salesforce import Salesforceimport pyodbcimport pandas as pd Then the connection to Salesforce is created. sf = Salesforce(username=’datamigrationuser@pension4you.nl.pensiond2′, password=’password’, security_token=’security’,domain=’test’)…