by Jerod Johnson | April 02, 2020

New CData Python Connectors

With its reputation as an easy-to-learn high-productivity programming language, Python has rapidly become one of the most popular tools for working with data. Python developers can make use of a vast ecosystem of tools, frameworks, and freely available modules to simplify data processing. These extensions streamline all kinds of data manipulations tasks, from data visualization and reporting, to data movement, to delivering new insight from AI / ML processing.

We are excited to extend the Python community with hundreds of new Python Connectors that dramatically simplify the way that Python users connect to SaaS, Big Data, and NoSQL data sources. Our Python Connectors offer simple Python Database interfaces (DB-API), making it easy to connect with popular tooling like Jupyter Notebook, SQLAlchemy, pandas, Dash, Apache Airflow, petl, and more.

Ultimately our Connectors provide Python developers with universal data connectivity layer, enabling them to easily connect systems and data with advanced Python processing.

Simplified Python Data Integration

CData Python Connectors create a SQL wrapper around APIs and data protocols, simplifying data access from within Python applications. Below are a handful of use-cases built around popular Python modules and toolkits, with links to more in-depth content.

1. pandas Visualizations
2. SQLAlchemy ORMs
3. Dash Web Apps
4. petl ETL Operations

The examples below each reference a different data source, but the connectivity is available for more than 150 SaaS, Big Data, and NoSQL data sources.

pandas Data Visualization and NetSuite

The CData Python Connectors integrate seamlessly with pandas and SQLAlchemy for visualizations and analytics. For example, you can create a SQLAlchemy engine and use the pandas read_sql function to visualize a DataFrame of NetSuite data.

engine = create_engine("netsuite///?Account Id=XABC123456&Password=password&User=user&Role Id=3&Version=2013_1")

df = pandas.read_sql("SELECT BillingState, SUM(Revenue) AS TotalRevenue FROM SalesOrder WHERE Class_Name = 'Furniture : Office' GROUP BY BillingState", engine)

df.plot(kind="bar", x="BillingState", y="Revenue")
plt.show()

SQLAlchemy ORM and Google BigQuery

The data interfaces of our Python Connectors pair easily with SQLAlchemy Object-Relational Mapping (ORM). Using the code below, we use an ORM to query and filter Google BigQuery data.

engine = create_engine("googlebigquery///?DataSetId=MyDataSetId&ProjectId=MyProjectId&InitiateOAuth=GETANDREFRESH")

factory = sessionmaker(bind=engine)
session = factory()

for instance in session.query(Orders).filter_by(ShipCity="New York"):
  print("OrderName: ", instance.OrderName)
  print("Freight: ", instance.Freight)
  print("---------")

Dash Web Apps with Snowflake

Using the CData Python Connectors and Dash framework allow you to easily create data-connected web applications for analyzing and visualizing data. The code below shows how to connect to Snowflake and build a simple bar chart using Dash.

cnxn = mod.connect("User=Admin; Password=test123; Server=localhost; Database=Northwind; Warehouse=TestWarehouse; Account=Tester1;")

df = pd.read_sql("SELECT Id, ProductName FROM Products WHERE Id = '1'", cnxn)
app_name = 'dash-snowflakedataplot'

external_stylesheets = ['https://codepen.io/chriddyp/pen/bWLwgP.css']

app = dash.Dash(__name__, external_stylesheets=external_stylesheets)
app.title = 'CData + Dash'
trace = go.Bar(x=df.Id, y=df.ProductName, name='Id')

app.layout = html.Div(children=[html.H1("CData Extension + Dash", style={'textAlign': 'center'}),
	dcc.Graph(
		id='example-graph',
		figure={
			'data': [trace],
			'layout':
			go.Layout(title='Snowflake Products Data', barmode='stack')
		})
], className="container")

petl ETL Operations with Salesforce

Pairing petl and the CData Python Connectors allows you to quickly build ETL pipelines in Python. For example, you can query and sort Salesforce data and store the results in a CSV file.

cnxn = mod.connect("User=user@domain.com; Password=password;")

sql = "SELECT Name, AnnualRevenue FROM Account WHERE Industry = 'Technology'"

table1 = etl.fromdb(cnxn,sql)

table2 = etl.sort(table1,'AnnualRevenue')

etl.tocsv(table2,'mycustomlist_data.csv')

Live Data Integration in Python Apps

The CData Python Connectors give you live access to all of your data from Python, granting easy access to all of the modules and toolkits you already use to work with databases. To learn more about how the Python Connectors work and download a free trial, visit our Python Connector page. As always, our world-class Support Team is here to answer any questions you have.