[QODBC-Desktop] How to connect to QODBC using Python
Posted by Jack - QODBC Support on 09 March 2016 09:34 AM
|
|
How to connect to QODBC using PythonSample Code for using QODBC DSN:
Sample 1
# Replace with your actual DSN name
print("Fetching all");
print("Getting column");
# Display header (first 5 column names)
print("Printing column");
# Clean up
Sample 2 import pyodbc cn = pyodbc.connect('DSN=QuickBooks Data;') cursor = cn.cursor() cursor.execute("SELECT Top 10 Name FROM Customer") for row in cursor.fetchall(): print (row) cursor.close() cn.close() QODBC Configuration:Turn on the "SQL Server Support" and set ODBC Compatibility to "Default". OR Turn on the "Simulate Transaction support for SQL Server" option OR Turn on the "SQL Server Support" option
Script execution & Result in Command Prompt:Navigate to your Python script folder:
Result: Troubleshooting: If you receive an error [QODBC] Driver Not Capable when using "QuickBooks Data" DSN Start>>All Programs>> QODBC Driver for use with QuickBooks>> Configure QODBC Data Source>>Go To "System DSN" Tab>> select QuickBooks Data>> click "configure">> switch to Advanced tab>> enable "Simulate Transaction support for SQL Server" option. After enabling the above option, you can use "QuickBooks Data" DSN in the python script. Sample Code for using QRemote DSN:import pyodbc cn = pyodbc.connect('DSN=QuickBooks Data QRemote;') cursor = cn.cursor() cursor.execute("SELECT Top 10 Name FROM Customer") for row in cursor.fetchall(): print (row) cursor.close() cn.close()
Please Note: If you have a 64-bit application, you must use QRemote 64-bit DSN "QuickBooks Data 64-bit QRemote." Script execution & Result in Command Prompt:Navigate to your Python script folder:
Sample Code for parameterized insert statement using QRemote DSN:import pyodbc from decimal import * stmt = pyodbc.connect("DSN=QuickBooks Data QRemote", autocommit=True) CustomerRefListID = input("CustomerRefListID: ") ClassRefFullName = input("ClassRefFullName: ") TemplateRefListID = input("TemplateRefListID: ") RefNumber = input("RefNumber: ") CreditMemoLineItemRefListID = input("CreditMemoLineItemRefListID: ") CreditMemoLineDesc = input("CreditMemoLineDesc: ") Rate = input("CreditMemoLineRate: ") Quantity = input("CreditMemoLineQuantity: ") Amount = input("CreditMemoLineAmount: ") encoding = 'utf-8' P1 = CustomerRefListID.encode(encoding) P2 = ClassRefFullName.encode(encoding) P3 = TemplateRefListID.encode(encoding) P4 = RefNumber.encode(encoding) P5 = CreditMemoLineItemRefListID.encode(encoding) P6 = CreditMemoLineDesc.encode(encoding) P7= Decimal(Rate) P8= Decimal(Quantity) P9= Decimal(Amount) cursor = stmt.cursor() sql = "INSERT INTO CreditMemoLine (CustomerRefListID,ClassRefFullName, TemplateRefListID, RefNumber, CreditMemoLineItemRefListID, CreditMemoLineDesc, CreditMemoLineRate,CreditMemoLineQuantity, CreditMemoLineAmount) values (?,?,?,?,?,?,?,?,?)" cursor.execute(sql,(P1),(P2),(P3),(P4),(P5),(P6),(P7),(P8),(P9)) cursor.close() stmt.close() Navigate to your Python script folder: Run your Python script: CreditMemo was created in QuickBooks. | |
|
If your QuickBooks application is installed on another machine, then you can connect to QuickBooks from a remote machine using QRemote Server. You need to install QODBC on both machine.
Please refer below mentioned article for Accessing QuickBooks Data Remotely using QODBC &QRemote. (Video):
http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/View/2517/
You need to run QRemote Server on QuickBooks machine & try to connect from your workstation using QRemote Client. QRemote Client should point to QuickBooks Server IP & Port.
If you are still facing issue, I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:
http://support.flexquarters.com/esupport/index.php?/Tickets/Submit
We may need the following information, I kindly request you to attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages