CData Python Connector for FreshBooks

Build 20.0.7587

From Pandas

When combined with Pandas' DataFrames, the connector is used to generate numerous different graphics for the purpose of analytics.

Connecting

In order to use Pandas it will need to be imported. Pandas will also rely on a SQLAlchemy engine when executing queries, as below:

import pandas as pd
from sqlalchemy import create_engine
engine = create_engine("freshbooks:///?InitiateOAuth=GETANDREFRESH;OAuthClientId=MyClientId;OAuthClientSecret=MyClientSecret;CallbackURL=http://localhost:33333")

Querying Data

SELECT queries are provided in a call to the "read_sql()" method in pandas, alongside a relevant connection object. Pandas will execute the query on that connection, and return the results in the form of a DataFrame, which are used for a variety of purposes. In order to use pandas, it will need to be imported:

df = pd.read_sql("""
	SELECT
	   Email,
	   Username
	FROM Clients;""", engine)
print(df)

Modifying Data

To insert new records to a table, simply create a new DataFrame, and define its fields accordingly. From there, simply call "to_sql()" on the DataFrame to perform the INSERT operation with the connector, as in the below example. The "if _exists" argument must be set to "append" to prevent pandas from attempting building the table from scratch, set index=False if needed to prevent pandas from writing DataFrame index as a column:

df = pd.DataFrame({"Email": ["Jon Doe"], "Username": ["John"]})
df.to_sql("Clients", con=engine, if_exists="append", index=False)

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7587