Data Science: How to download Bitrix CRM with Python

Bitrix24

1. The reposity of this post
2. My Github
3. For all my posts

Intro

In my actual work, another problem showed up. Our managers requested a series of (Power BI) dashboards with our data from Bitrix24.

We find this link (it’s in BR-portuguese). In this link they show you how to connect PBI to the Data Base. But we found 2 problems:

1. The base document is in Russian;
2. PBI Service (online) can not auto update the data;

There was another problem, we wanted to bring the entire Bitrix24 data base to our dataware house.

Solution: Download the Data Base using Python

In order to acomplish this task it was required to investigate how the base file given by Bitrix24 download the data:

The following query is in M Language:

= (bx24_entity_name as text) as table =>
 let
 response = Web.Contents(
 "https://" & #"Server address",
 [
 RelativePath = "bitrix/tools/biconnector/pbi.php",
 Query = [
 //token = #"Secret key",
 table = bx24_entity_name
 ],
 Content = Json.FromValue([dateRange = [
 startDate = Date.AddYears(Date.From(DateTime.LocalNow()),-1),
 endDate = Date.From(DateTime.LocalNow())],
 key = #"Secret key"
 ]
 )
 ]
 ),
 jd = Json.Document(response)
 in
 Table.FromRows(
 List.Skip(jd),//data: >=1 row (index 0 based)
 List.First(jd)//header: 0 row (index 0 based)
 )

This code requires 3 variables:

1. #”Server Adress”: businessname.bitrix24.com.br
2. #”Secret Key”: a_very_long_key
3. #”bx_entity_name”: Entity name is the name of the table.

Entities (link in BR-Portuguese):

• crm_deal — deals
• crm_lead — leads
• crm_deal_uf — custom deals fields
• crm_lead_uf — custom leads fields
• crm_deal_stage_history —deals history
• crm_lead_status_history — leads history
• telephony_call — calls
• crm_company — companies
• crm_contact — contacts
• socialnetwork_group — projects
• crm_activity — activity

To understand which information each of them brings, you can access this link.

Translating M Language to Python

M Language is not my strongest skill, so before trying to directly write a Python request, I used Insomnia, but you can use Postman or whatever program or web app you want to test the requests.

To make it easier for you:

“https://#”Server address”.bitrix24.com.br/bitrix/tools/biconnector/pbi.php?token=#”Secret key”&table=bx24_entity_name”

Just replace the variables with the values and that it is, you can already send a request and the return will be a JSON.

In Python:

import pandas as pd
from requests import get

# insert here the entity names (tables) that you want to download
tables = ['crm_deal', 'crm_lead', 'crm_deal_uf']

def ta_bitrix(tables):
    # iterating of the variable "tables"

    for table in tables:
        # printing the table name
        print(f'Table: {table}')
        
        # request, replace # with the variable 
        req = get(f'https://##########.bitrix24.com.br/bitrix/tools/biconnector/pbi.php?token=############&table={table}')
        
        # transforming the JSON into a Pandas Data Frame
        df = pd.DataFrame(req.json())
        
        # renaming the columns using the first row
        df.columns = df.iloc[0]
        
        # dropping the first line, which was used to rename the columns
        df = df.drop(index=df.index[0], axis=0)

        # returning the table as csv
        df.to_csv(f'{table}.csv')

# execute the code
ta_bitrix(tables)

If you want, you access my GitHub account, here’s the link.

From this code, you can decide whether you upload it to your data warehouse or you keep it locally.

Thank-you,

Igor Comune | LinkedIn