What are CData Python Connectors?

CData Python Connectors are standards-based connectivity solutions that simplify live data access to over 270 data sources. The connectors allow you to connect with relational databases, SaaS platforms, ERP suites, big data platforms, and NoSQL systems using a Standard SQL-92 compliant interface.

The SQL-92 interface combined with syntax enables you to query, migrate, replicate, and manipulate data as if it were a standard database, eliminating the need to perform complex API and protocol-specific requests while simplifying live connectivity in just a few lines of code.

Why choose CData Python Connectors?

Although the standard Python DB-API provides ways to connect to databases, it lacks built-in support for non-relational data sources, real-time querying, and advanced SQL operations. Developers often need to rely on complex API integrations, custom scripts, or third-party libraries to interact with cloud-based services, NoSQL databases, and enterprise applications.

CData Python Connectors bridge this gap by offering a unified SQL-92 interface that simplifies data access across 270+ sources. They enable seamless integration with relational and non-relational systems while optimizing performance through pushdown query processing, metadata discovery, and intelligent client-side execution.

Key features

Universal Python Data Connectivity: Connect to databases, SaaS platforms, NoSQL systems, BI tools, ETL workflows, and more using a standard interface.
Live Data Access: Retrieve real-time data without the need for replication.
Plug-and-Play Access: Instantly connect to Salesforce, Snowflake, Jira, NetSuite, Google Sheets, and other platforms.
SQL-92 Standard Interface: Write SQL queries against non-relational sources with built-in metadata discovery.
Pushdown Query Optimization: Enhances performance by pushing supported operations to the data source.
Comprehensive SQL Support: Execute aggregations, joins, and complex SQL functions effortlessly.
Client-Side Query Execution: Handles unsupported SQL operations natively for complete compatibility.
Seamless Library Integration: Works with pandas, SQLAlchemy, Dash, petl, and other data science tools.
Enterprise-Grade Performance: Secure, scalable, and optimized for high-speed querying.
Cross-Platform Support: Available for Windows, macOS, and Linux environments.
Community Edition: Free for non-commercial use, with full access to core features.

How Do CData Python Connectors work?

Once installed, CData Python Connectors function like any other Python module that can be imported and used within your scripts. The connection string may vary depending on the data source, but the methodology for working with data remains consistent across all supported sources.

Key operations with CData Python Connectors

Connecting to a data source

Developers can establish a connection with minimal configuration. Below is an example of connecting to a local directory containing CSV files:


        import cdata.csv as mod
        conn = mod.connect("URI=C:\CSVFolder;")

This code imports the CData Python CSV Connector module and creates a connection using a URI pointing to a local CSV file directory by specifying its path. Once connected, the data within the CSV files can be queried using standard SQL.

Discovering schema and metadata

Schema and Metadata calls allow developers to explore the structure of the connected data. While tables, views, and columns are regular parts of a database (which the DB-API was built to connect to), they don't always have meaning for file stores, services, and SaaS applications. CData's Python connectors convert the structure of these non-relational sources to a tabular data model:

Entities and objects are represented as views (read-only) and tables (read-write).
Fields and attributes get surfaced as columns.

For example, the metadata for a directory containing CSV-formatted files would include a table for each file and columns for the fields in each file.

The following example retrieves available tables (each of which represents a CSV-formatted file the directory specified in the URI connection property) using system tables:


        import cdata.csv as mod  
    
        conn = mod.connect("URI=C:\CSVFolder;")  
        cur = conn.cursor()
        cur.execute("SELECT * FROM sys_tables")  
    
        for row in cur.fetchall():  
            print(row)

This code initializes a cursor object, queries the sys_tables system table to retrieve all available tables in the data source, and prints their names.

To retrieve available views (each of which would represent a read-only entity or an object), use:


        cur.execute("SELECT * FROM sys_views")  
    
        for row in cur.fetchall():  
            print(row)

This fetches and prints all views available in the data model.

To retrieve and print the column names and attributes of the specified table, use:


        cur.execute("SELECT * FROM sys_tablecolumns WHERE TableName = 'YourTable'") 

        for column in cur.fetchall(): 
	        print(column)

Querying and modifying the data

Once connected, you can execute SQL queries against the data source using the execute() method:


        cur = conn.execute("SELECT Email, Username FROM YourTable") 
        rs = cur.fetchall() 
        for row in rs: 
	        print(row)

This query retrieves the Email and Username columns from the specified table and prints the results.

In addition to querying, developers can modify data using SQL commands like INSERT, UPDATE, and DELETE. Here's an example of inserting a new record into the table:


        cmd = "INSERT INTO NorthwindOData (Email, Username) VALUES (?, ?)" 
        params = ["ana.trujilo@northwind.org", "Ana Trujilo"] 
        cur = conn.execute(cmd, params)| 
            print("Records affected:", cur.rowcount)

This inserts a new record into the table, adding a new user with an email and username.

NOTE: The native DB-API supports commit() and rollback() for error handling and transactional management. While these are largely supported in database systems, the same features are rarely supported with Web Services, SaaS applications, file stores, and the other data sources that CData Python Connectors support. As such, rollback() is not implemented in CData Python Connectors.

Performing advanced actions with stored procedures

Stored procedures enable advanced operations beyond standard queries. In CData Python Connectors, you can execute stored procedures using either the execute() or callproc() method.

Available stored procedures

CData Python Connectors provide various stored procedures depending on the data source. Some common ones include:

GetOAuthAccessToken: Obtains the OAuth access token for authentication.
ListFiles: Lists all CSV files stored in a local or cloud-based directory.
CopyFile: Copies a file from a local or cloud storage.
RefreshOAuthAccessToken: Refreshes the OAuth token for authentication.

Stored procedures simplify complex data operations, allowing developers to perform tasks like authentication, bulk processing, and schema updates using SQL-like commands.

Performing the CopyFile stored procedure

As an example, let's see how the CopyFile stored procedure works. The CopyFile stored procedure allows you to copy a file from a specified source path to a destination path, whether on local or cloud storage. This is especially useful when working with files stored remotely, like on SFTP servers or cloud directories.

The procedure requires two parameters:

SourcePath: The full or relative path of the file you want to copy.
DestinationPath: The location where the file will be copied.

Understanding path parameters

The CopyFile stored procedure supports both absolute and relative paths for the SourcePath and DestinationPath parameters:

Absolute Path: This includes the complete location of the file, including the protocol and server details.


        EXEC COPYFILE @SourcePath = 'C:\Users\Public\Downloads\CSV Files\file1.csv', @DestinationPath = 'C:\Users\Public\Downloads\CSV Files\Folder2'

This will copy file1.csv from /folder1 to /folder2 on your local machine or on the SFTP server.

Relative Path: The path is relative to the URI specified in the connection string.


        EXEC COPYFILE @SourcePath = 'file1.csv', @DestinationPath = 'Folder2'

If the connection string URI is set to C:\Users\Public\Downloads\CSV Files\, this command will copy file1.csv from '/CSV Files' to '/CSV Files/folder2'.

Calling the CopyFile stored procedure

You can run the CopyFile stored procedure using either callproc() or execute(). Both methods will return a result set that includes a Success Boolean field indicating whether or not the file copy operation was successful.

Using execute()

You can use the execute() method to run the stored procedure using a raw SQL-like command:


        Cmd = "EXECUTE CopyFile SourcePath = ?, DestinationPath = ?" 
        params = ['C:\Users\Public\Downloads\CSV Files\file1.csv', 'C:\Users\Public\Downloads\CSV Files\Folder2'] 
        conn.execute(cmd, params)

This method gives you more flexibility in constructing dynamic SQL commands. The '?' placeholders are replaced by the values in the params list.

Using callproc()

The callproc() method is a straightforward way to execute stored procedures. It takes the procedure name and a list of parameters.


        cur = conn.cursor() 
        params = ['C:\Users\Public\Downloads\CSV Files\file1.csv', 'C:\Users\Public\Downloads\CSV Files\Folder2'] 
        cur.callproc("CopyFile", params) for result in cur.fetchall(): print(result)

The cur.callproc("CopyFile", params) executes the stored procedure, with the params list specifying the source and destination paths. The loop then iterates over the result set, which includes a Success flag indicating whether the file was copied successfully.

Differences between `execute()` and `callproc()`

The callproc() method executes stored procedures directly using the procedure name and a list of parameters. It is ideal for simple and straightforward stored procedure calls, offering a clean and concise way to run procedures without writing SQL commands.

The execute() method, on the other hand, runs stored procedures through an SQL-like command with parameterized queries. This approach provides more flexibility, making it suitable for dynamic SQL execution and handling complex logic.

Both methods will return a Success Boolean field if the stored procedure runs successfully.

Free Community License for data developers

CData Python Connectors further enhance the capabilities of the Python DB-API by offering consistent, SQL-based connectivity to more than 270 data sources beyond traditional databases, including SaaS, NoSQL, and big data systems.

With the CData Python Community License, you get free-forever libraries to access your data in personal Python projects, all through familiar SQL. Request a license and start creating better-connected projects today!