Discover how a bimodal integration strategy can address the major data management challenges facing your organization today.
Get the Report →Using the CData ODBC Driver for NetSuite in PyCharm
Connect to NetSuite as an ODBC data source in PyCharm using the CData ODBC Driver for NetSuite.
The CData ODBC Drivers can be used in any environment that supports loading an ODBC Driver. In this tutorial we will explore using the CData ODBC Driver for NetSuite from within PyCharm. Included are steps for adding the CData ODBC Driver as a data source, as well as basic PyCharm code to query the data source and display results.
To begin, this tutorial will assume that you have already installed the CData ODBC Driver for NetSuite as well as PyCharm.
About NetSuite Data Integration
CData provides the easiest way to access and integrate live data from Oracle NetSuite. Customers use CData connectivity to:
- Access all editions of NetSuite, including Standard, CRM, and OneWorld.
- Connect with all versions of the SuiteTalk API (SOAP-based) and SuiteQL, which functions like SQL, enabling easier data querying and manipulation.
- Access predefined and custom reports through support for Saved Searches.
- Securely authenticate with Token-based and OAuth 2.0, ensuring compatibility and security for all use cases.
- Use SQL stored procedures to perform functional actions like uploading or downloading files, attaching or detaching records or relationships, retrieving roles, getting extra table or column info, getting job results, and more.
Customers use CData solutions to access live NetSuite data from their preferred analytics tools, Power BI and Excel. They also use CData's solutions to integrate their NetSuite data into comprehensive databases and data warehouse using CData Sync directly or leveraging CData's compatibility with other applications like Azure Data Factory. CData also helps Oracle NetSuite customers easily write apps that can pull data from and push data to NetSuite, allowing organizations to integrate data from other sources with NetSuite.
For more information about our Oracle NetSuite solutions, read our blog: Drivers in Focus Part 2: Replicating and Consolidating ... NetSuite Accounting Data.
Getting Started
Add Pyodbc to the Project
Follow the steps below to add the pyodbc module to your project.
- Click File -> Settings to open the project settings window.
- Click Project Interpreter from the Project: YourProjectName menu.
- To add pyodbc, click the + button and enter pyodbc.
- Click Install Package to install pyodbc.
Connect to NetSuite
You can now connect with an ODBC connection string or a DSN. See the Getting Started section in the CData driver documentation for a guide to creating a DSN on your OS.
The User and Password properties, under the Authentication section, must be set to valid NetSuite user credentials. In addition, the AccountId must be set to the ID of a company account that can be used by the specified User. The RoleId can be optionally specified to log in the user with limited permissions.
See the "Getting Started" chapter of the help documentation for more information on connecting to NetSuite.
Below is the syntax for a DSN:
[CData NetSuite Source]
Driver = CData ODBC Driver for NetSuite
Description = My Description
Account Id = XABC123456
Password = password
User = user
Role Id = 3
Version = 2013_1
Execute SQL to NetSuite
Instantiate a Cursor and use the execute method of the Cursor class to execute any SQL statement.
import pyodbc
cnxn = pyodbc.connect('DRIVER={CData ODBC Driver for NetSuite};Account Id = XABC123456;Password = password;User = user;Role Id = 3;Version = 2013_1;')
cursor = cnxn.cursor()
cursor.execute("SELECT CustomerName, SalesOrderTotal FROM SalesOrder WHERE Class_Name = 'Furniture : Office'")
rows = cursor.fetchall()
for row in rows:
print(row.CustomerName, row.SalesOrderTotal)
After connecting to NetSuite in PyCharm using the CData ODBC Driver, you will be able to build Python apps with access to NetSuite data as if it were a standard database. If you have any questions, comments, or feedback regarding this tutorial, please contact us at [email protected].