 |
VOOZH | about |
|
VOOZH | about |
OpenAI's Python SDK provides powerful capabilities for building AI applications that can interact with various data sources. When combined with CData Connect AI Remote MCP, you can build intelligent chat applications that interact with your Paylocity data in real-time through natural language queries. This article outlines the process of connecting to Paylocity using Connect AI Remote MCP and configuring an OpenAI-powered Python application to interact with your Paylocity data through conversational AI.
CData Connect AI offers a dedicated cloud-to-cloud interface for connecting to Paylocity data. The CData Connect AI Remote MCP Server enables secure communication between OpenAI applications and Paylocity. This allows your AI assistants to read from and take actions on your live Paylocity data. With its inherent optimized data processing capabilities, CData Connect AI efficiently channels all supported SQL operations, including filters and JOINs, directly to Paylocity. This leverages server-side processing to swiftly deliver the requested Paylocity data.
In this article, we show how to configure an OpenAI-powered Python application to conversationally explore (or Vibe Query) your data using natural language. With Connect AI you can build AI assistants with access to live Paylocity data, plus hundreds of other sources.
Connectivity to Paylocity from OpenAI applications is made possible through CData Connect AI Remote MCP. To interact with Paylocity data from your OpenAI assistant, we start by creating and configuring a Paylocity connection in CData Connect AI.
Set the following to establish a connection to Paylocity:
This property is required for executing Insert and Update statements, and it is not required if the feature is disabled.
Paylocity will decrypt the AES key using RSA decryption.
It is an optional property if the IV value not provided, The driver will generate a key internally.
You must use OAuth to authenticate with Paylocity. OAuth requires the authenticating user to interact with Paylocity using the browser. For more information, refer to the OAuth section in the Help documentation.
The Pay Entry API is completely separate from the rest of the Paylocity API. It uses a separate Client ID and Secret, and must be explicitly requested from Paylocity for access to be granted for an account. The Pay Entry API allows you to automatically submit payroll information for individual employees, and little else. Due to the extremely limited nature of what is offered by the Pay Entry API, we have elected not to give it a separate schema, but it may be enabled via the UsePayEntryAPI connection property.
Please be aware that when setting UsePayEntryAPI to true, you may only use the CreatePayEntryImportBatch & MergePayEntryImportBatchgtable stored procedures, the
InputTimeEntry table, and the OAuth stored procedures. Attempts to use other features of the product will result in an error. You must also store your OAuthAccessToken
separately, which often means setting a different OAuthSettingsLocation when using this connection property.
👁 Configuring a connection (Salesforce is shown)
A Personal Access Token (PAT) is used to authenticate the connection to Connect AI from your OpenAI application. It is best practice to create a separate PAT for each service to maintain granularity of access.
With the connection configured and a PAT generated, we are ready to connect to Paylocity data from your OpenAI application.
Follow these steps to configure your OpenAI Python application to connect to CData Connect AI. You can use our pre-built client as a starting point, available at https://github.com/CDataSoftware/openai-mcp-client, or follow the instructions below to create your own.
pip install openai python-dotenv httpx
git clone https://github.com/CDataSoftware/openai-mcp-client.git cd openai-mcp-client
OPENAI_API_KEY=YOUR_OPENAI_API_KEY MCP_SERVER_URL=https://mcp.cloud.cdata.com/mcp MCP_USERNAME=YOUR_EMAIL MCP_PASSWORD=YOUR_PAT OPENAI_MODEL=gpt-4Replace YOUR_OPENAI_API_KEY with your OpenAI API key, YOUR_EMAIL with your Connect AI email address, and YOUR_PAT with the Personal Access Token created in Step 1.
import os
import asyncio
import base64
from dotenv import load_dotenv
from mcp_client import MCPServerStreamableHttp, MCPAgent
# Load environment variables
load_dotenv()
async def main():
"""Main chat loop for interacting with Paylocity data."""
# Get configuration
api_key = os.getenv('OPENAI_API_KEY')
mcp_url = os.getenv('MCP_SERVER_URL', 'https://mcp.cloud.cdata.com/mcp')
username = os.getenv('MCP_USERNAME', '')
password = os.getenv('MCP_PASSWORD', '')
model = os.getenv('OPENAI_MODEL', 'gpt-4')
# Create auth header for MCP server
headers = {}
if username and password:
auth = base64.b64encode(f"{username}:{password}".encode()).decode()
headers = {"Authorization": f"Basic {auth}"}
# Connect to CData MCP Server
async with MCPServerStreamableHttp(
name="CData MCP Server",
params={
"url": mcp_url,
"headers": headers,
"timeout": 30,
"verify_ssl": True
}
) as mcp_server:
# Create AI agent with access to Paylocity data
agent = MCPAgent(
name="data_assistant",
model=model,
mcp_servers=[mcp_server],
instructions="""You are a data query assistant with access to Paylocity data through CData Connect AI.
You can help users explore and query their Paylocity data in real-time.
Use the available MCP tools to:
- List available databases and schemas
- Explore table structures
- Execute SQL queries
- Provide insights about the data
Always explain what you're doing and format results clearly.""",
api_key=api_key
)
await agent.initialize()
print(f"Connected! {len(agent._tools_cache)} tools available.")
print("
Chat with your Paylocity data (type 'exit' to quit):
")
# Interactive chat loop
conversation = []
while True:
user_input = input("You: ")
if user_input.lower() in ['exit', 'quit']:
break
conversation.append({"role": "user", "content": user_input})
print("Assistant: ", end="", flush=True)
response = await agent.run(conversation)
print(response["content"])
conversation.append({"role": "assistant", "content": response["content"]})
if __name__ == "__main__":
asyncio.run(main())
python client.py
With your OpenAI Python application configured and connected to CData Connect AI, you can now build sophisticated AI assistants that interact with your Paylocity data using natural language. The MCP integration provides your applications with powerful data access capabilities through OpenAI's advanced language models.
Your OpenAI assistant has access to the following CData Connect AI MCP tools:
Here are some examples of what your OpenAI-powered applications can do with live Paylocity data access:
Once running, you can interact with your OpenAI assistant through natural language. Example queries include:
Your OpenAI assistant will automatically translate these natural language queries into appropriate SQL queries and execute them against your Paylocity data through the CData Connect AI MCP Server, providing intelligent insights without requiring users to write complex SQL or understand the underlying data structure.
The OpenAI MCP integration supports advanced capabilities:
To get live data access to hundreds of SaaS, Big Data, and NoSQL sources directly from your OpenAI applications, try CData Connect AI today!
Learn more about CData Connect AI or sign up for free trial access:
Free Trial