How to create a custom REST API connector for Python

Learn how to quickly and efficiently connect Custom API with Python for smooth data access.

Read / write Custom API data without coding. You can do it all using the high-performance Custom API ODBC Driver for Python (often referred to as the Custom API Connector). We'll walk you through the entire setup.

Ready to dive in? Download the product to jump right in, or follow the step-by-step guide below to see how it works.

Create Custom API Connector

First of all, you will have to create your own API connector. For demonstration purposes, in this section we will create a simple Hello-World API connector that calls ZappySys Sandbox World API endpoint https://sandbox.zappysys.com/api/world/hello. When developing your Custom API Connector, just replace it with your real API method/endpoint. Let's dive in and follow these steps:

Search for odbc and open the ODBC Data Sources (64-bit):
Create a User data source (User DSN) based on the ZappySys JSON Driver driver:

ZappySys JSON Driver
Once the data source configuration window opens, enter this URL into the text box:

https://sandbox.zappysys.com/api/world/hello
Then go to the Preview tab and try to say "Hello!" to the World!
Since the test is successful, you are ready to create the Hello-World Connector:
The API Connector File Wizard opens, click Next:
Leave the default option, and click Next again:
Just click Next in the next window:
Let's give our new custom connector a name it deserves:
Then just click Next in the Connection Types window:
Let's name the hello endpoint (it deserves a name too!):
When the next window opens, delete the default table (we won't need it for now):
Finally, specify a path, where you want to save the newly created API Connector:

Create data source using Custom API ODBC Driver

Step-by-step instructions

To get data from Custom API using Python, we first need to create an ODBC data source. We will later read this data in Python. Perform these steps:

Download and install ODBC PowerPack (if you haven't already).
Search for odbc and open the ODBC Data Sources (64-bit):
Create a User data source (User DSN) based on the ZappySys API Driver driver:

ZappySys API Driver
When the Configuration window appears give your data source a name if you haven't done that already. Then set the path to your created Custom API Connector (in the example below, we use Hello-World Connector). Finally, click Continue >> to proceed:
Now it's time to configure the Connection Manager. Select Authentication Type, e.g. Token Authentication. Then select API Base URL (in most cases, the default one is the right one). Check your Custom API reference for more information on how to authenticate.
Once the data source connection has been configured, it's time to configure the SQL query. Select the Preview tab and then click Query Builder button to configure the SQL query:

ZappySys API Driver - Custom API

Read / write Custom API data without coding.

CustomApiDSN
Start by selecting the Table or Endpoint you are interested in and then configure the parameters. This will generate a query that we will use in Python to retrieve data from Custom API. Hit OK button to use this query in the next step.
```
SELECT * FROM Orders
```
Some parameters configured in this window will be passed to the Custom API, e.g. filtering parameters. It means that filtering will be done on the server side (instead of the client side), enabling you to get only the meaningful data much faster.
Now hit Preview Data button to preview the data using the generated SQL query. If you are satisfied with the result, use this query in Python:
ZappySys API Driver - Custom API

Read / write Custom API data without coding.

CustomApiDSN
```
SELECT * FROM Orders
```
You can also access data quickly from the tables dropdown by selecting <Select table>.

A WHERE clause, LIMIT keyword will be performed on the client side, meaning that the whole result set will be retrieved from the Custom API first, and only then the filtering will be applied to the data. If possible, it is recommended to use parameters in Query Builder to filter the data on the server side (in Custom API servers).
Click OK to finish creating the data source.

Video Tutorial

Read data in Python

Using ODBC DSN

Use this code snippet to read the data using CustomApiDSN data source:

CustomApiDSN')
When you run the code it will make the API call and read the data:

Here is Python program's code in text format:


    import pyodbc
    conn = pyodbc.connect('DSN=CustomApiDSN')

    cursor = conn.cursor()

    #execute query to fetch data from API service
    cursor.execute("SELECT id,title FROM products")

    row = cursor.fetchone()
    while row:
        print(row)
        row = cursor.fetchone()

    ##For loop example
    #for row in cursor:
    #    print(row)

Using a full ODBC connection string

If you want to avoid being dependent on a DSN and creating multiple DSNs for each platform (x86, x64), then you can use a fully qualified connection string. Simply go to your DSN and copy the Connection String:

Open ODBC data source configuration and click Copy settings:

ZappySys API Driver - Configuration [Version: 2.0.1.10418]

ZappySys API Driver - Custom API

Read / write Custom API data without coding.

CustomApiDSN
The window opens, telling us the connection string was successfully copied to the clipboard:

Then in your Python code use Connection String when initializing OdbcConnection object, for example:

conn = pyodbc.connect('DRIVER={ZappySys API Driver};ServiceUrl=https://yourservices.provider.com/api/xxxx....;AuthName=Http;')

How to install `pyodbc` in the Python?

You would need to install pyodbc in Python if you intend to establish connections to databases that support ODBC (Open Database Connectivity). This module facilitates communication between Python applications and various database management systems, enabling you to perform operations such as querying, retrieving data, and managing databases. Here's how you can install pyodbc in Python:

Installation Steps:

Ensure you have Python installed on your system. If not, download it from the official Python website and follow the installation instructions.
Open your terminal or command prompt.
Use the following command to install pyodbc using pip, the Python package installer:
```
python -m pip install "pyodbc"
```
Make sure you have a stable internet connection and the necessary permissions to install Python packages.

Reasons to Install:

If pyodbc is not installed, your Python script will generate the following error:
```
"ModuleNotFoundError: No module named 'pyodbc'"
```
.
Database Connectivity: pyodbc allows Python to connect to various databases that support ODBC, such as Microsoft SQL Server, PostgreSQL, MySQL, and more.
Data Operations: It facilitates the execution of SQL queries, retrieval of data, and other database operations from within Python scripts.
Cross-Platform Support: pyodbc is designed to work across different operating systems, including Windows, macOS, and various Linux distributions.
Simplicity and Efficiency: The module provides an intuitive interface for managing database transactions and connections, simplifying the process of working with databases in Python.

By installing pyodbc, you can seamlessly integrate your Python applications with a wide range of ODBC-supported databases, enabling efficient and effective data management and analysis.

Optional: Centralized data access via ZappySys Data Gateway

In some situations, you may need to provide Custom API data access to multiple users or services. Configuring the data source on a Data Gateway creates a single, centralized connection point for this purpose.

This configuration provides two primary advantages:

Centralized data access
The data source is configured once on the gateway, eliminating the need to set it up individually on each user's machine or application. This significantly simplifies the management process.
Centralized access control
Since all connections route through the gateway, access can be governed or revoked from a single location for all users.

	Data Gateway	Local ODBC data source
Simple configuration
Installation	Single machine	Per machine
Connectivity	Local and remote	Local only
Connections limit	Limited by License	Unlimited
Central data access
Central access control
More flexible cost

To achieve this, you must first create a data source in the Data Gateway (server-side) and then create an ODBC data source in Python (client-side) to connect to it.

Let's not wait and get going!

Create Custom API data source in the gateway

In this section we will create a data source for Custom API in the Data Gateway. Let's follow these steps to accomplish that:

Search for gateway in the Windows Start Menu and open ZappySys Data Gateway Configuration:
Go to the Users tab and follow these steps to add a Data Gateway user:
- Click the Add button
- In the Login field enter a username, e.g., john
- Then enter a Password
- Check the Is Administrator checkbox
- Click OK to save
Now we are ready to add a data source:
- Click the Add button
- Give the Data source a name (have it handy for later)
- Then select Native - ZappySys API Driver
- Finally, click OK
CustomApiDSN

ZappySys API Driver
When the ZappySys API Driver configuration window opens, go back to ODBC Data Source Administrator where you already have the Custom API ODBC data source created and configured, and follow these steps on how to Import data source configuration into the Gateway:
- Open ODBC data source configuration and click Copy settings:
  
  ZappySys API Driver - Configuration [Version: 2.0.1.10418]
  
  ZappySys API Driver - Custom API
  
  Read / write Custom API data without coding.
  
  CustomApiDSN
- The window opens, telling us the connection string was successfully copied to the clipboard:
- Then go to Data Gateway configuration and in data source configuration window click Load settings:
  
  CustomApiDSN
  
  ZappySys API Driver - Configuration [Version: 2.0.1.10418]
  
  ZappySys API Driver - Custom API
  
  Read / write Custom API data without coding.
  
  CustomApiDSN
- Once a window opens, just paste the settings by pressing CTRL+V or by clicking right mouse button and then Paste option.
Once done, go to the Network Settings tab and Add a firewall rule for inbound traffic:
- This will initially allow all inbound traffic.
- Click Edit IP filters to restrict access to specific IP addresses or ranges.
Crucial Step: After creating or modifying the data source, you must:
- Click the Save button to persist your changes.
- Hit Yes when prompted to restart the Data Gateway service.
This ensures all changes are properly applied:

Skipping this step may cause the new settings to fail, preventing you from connecting to the data source.

Create ODBC data source to connect to the gateway

In this part we will create an ODBC data source to connect to the ZappySys Data Gateway from Python. To achieve that, let's perform these steps:

Search for odbc and open the ODBC Data Sources (64-bit):
Create a User data source (User DSN) based on the ODBC Driver 17 for SQL Server driver:

ODBC Driver 17 for SQL Server

If you don't see the ODBC Driver 17 for SQL Server driver in the list, choose a similar version.
Then set a Name for the data source (e.g. Gateway) and the address of the Data Gateway:

ZappySysGatewayDSN

localhost,5000

Make sure you separate the hostname and port with a comma, e.g. localhost,5000.
Proceed with the authentication part:
- Select SQL Server authentication
- In the Login ID field enter the user name you created in the Data Gateway, e.g., john
- Set Password to the one you configured in the Data Gateway
Then set the default database property to CustomApiDSN (the one we used in the Data Gateway):

CustomApiDSN

CustomApiDSN

Make sure to type the data source name manually or copy/paste it directly into the field. Using the dropdown might fail because the Trust server certificate option is not enabled yet (next step).
Continue by checking the Trust server certificate option:
Once you do that, test the connection:
If the connection is successful, everything is good:
Done!

We are ready to move to the final step. Let's do it!

Access data in Python via the gateway

Finally, we are ready to read data from Custom API in Python via the Data Gateway. Follow these final steps:

Go back to Python.
Use this code snippet to read the data using ZappySysGatewayDSN data source:

ZappySysGatewayDSN')
Read the data the same way we discussed at the beginning of this article.
That's it!

Now you can connect to Custom API data in Python via the Data Gateway.

If you are asked for authentication details, use Database authentication, SQL authentication or Basic authentication option and enter the credentials you used when configuring the Data Gateway, e.g. john and your password.

Conclusion

In this article we showed you how to connect to Custom API in Python and integrate data without writing complex code — all of this was powered by Custom API ODBC Driver.

Download ODBC PowerPack now or ping us via chat if you have any questions or are looking for a specific feature (you can also reach out to us by submitting a ticket):

Download Chat

Explore Python connectors

All

Big Data & NoSQL

Database

CRM & ERP

Marketing

Collaboration

Cloud Storage

Reporting

Commerce

API & Files

Amazon Ads

Amazon Athena

Amazon Selling Partner (SP-API)

Google Search Console

ManageEngine ServiceDesk Plus (Zoho)

Outlook Mail (Office 365)

Build your own Custom API Connector

More Custom API integrations

All

Data Integration

Database

BI & Reporting

Productivity

Programming Languages

Automation & Scripting

ODBC applications

Informatica PowerCenter

Azure Data Factory (Pipeline)

Azure Data Factory (SSIS)