Knowledgebase: Tutorials
[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 Python

Download Sample

Sample Code for using QODBC DSN:

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 "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:


Run your Python script:

 

Result:

Troubleshooting:

If you receive an error  [QODBC] Driver Not Capable when using "QuickBooks Data" DSN

To resolve this error, you need to enable the "Simulate Transaction support for SQL Server" or "SQL Server Support"  option from:

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 refer: To how to configure QRemote.

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:


Run your Python script:


Result:

Sample Code for parameterized insert statement using QRemote DSN:

Download Sample

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.

(1 vote(s))
Helpful
Not helpful
Comments (2)
BTall
24 January 2017 09:12 AM
How would I connect to through Python from a remote computer?
Jack
24 January 2017 02:04 PM
Hi,

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

Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).