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.