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

 

Sample 1

 


import pyodbc

 

# Replace with your actual DSN name
dsn_name = 'QuickBooks Data'
print("connecting to the DSN" , dsn_name);
# Connect using the DSN
conn_str = f'DSN={dsn_name};'
conn = pyodbc.connect(conn_str)
print("Connected to the DSN" , dsn_name);
# Create a cursor from the connection
cursor = conn.cursor()
print("Cursor Created");
print("Executing select * from customer");
# Execute the query
cursor.execute("SELECT * FROM Customer")

 

print("Fetching all");
# Fetch all rows
rows = cursor.fetchall()

 

print("Getting column");
# Get column names
columns = [column[0] for column in cursor.description]

 

# Display header (first 5 column names)
print(" | ".join(columns[:5]))

 

print("Printing column");
# Display each row (first 5 columns)
for row in rows:
print(" | ".join(str(row[i]) for i in range(min(5, len(row)))))

 

# Clean up
cursor. Close()
conn.close()

 

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:


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).