64 Bit Windows Quickbooks and sqlAlchemy

[E.Scott M.]

I am migrating my accounting from Microsoft Dynamics(Mssql) to Quickbooks Enterprise. I have had lots of custom code written to bridge between my website and Dynamics. I am trying to port that code over to quickbooks but I am having a very difficult time.

We used sqlAlchemy and python to create out custom code. I am trying to use sqlalchemy for the quickbooks port.

I am using QODBC on the quickbooks side and mxODBC on the python side.

I can use mxODBC native functions and direct connect to the quickbooks database - access the tables and data, but the mxodbc.directconnect() function will not work syntactically with sqlalchemy connection strings.

 

In sqlalchemy this is what I can do:

 

engine = create_engine('mssql+mxodbc://@QRQBwhoimp')

>>> metadata = MetaData('mssql+mxodbc://@QRQBwhoimp')

>>> metadata.bind = engine

>>> connection = engine.connect()

 

I dont receive any error messages until I try and connect - then I get:

sqlalchemy.exc.NotSupportedError: (NotSupportedError) ('IM001', 0, '[Microsoft][ODBC Driver Manager] Driver does not sup

port this function', 12173) None None

 

Also, if I try to reflect a table, I get the same error message. I have tried a lot of different connection strings and this one seems to work without error messages that other ones generate.

 

Any ideas?

[Michael Bayer]

Make sure you're on the very latest released version of mxodbc, and possibly also try pyodbc.    The error is being emitted from your ODBC driver but I'm not familiar with its meaning.

[E.Scott M.]

Thank you Michael,

I have the brand new eval version of Mxodbc because qbodbc has some kind of bug with pyodbc.

Qodbc tech support has added it to their bug list but I have no idea when it may be fixed.

Preliminary tests with mxodbc seemed promising as I can use their connect function and get access to the database which is more than I could do with pyodbc, but now I am having this problem with sqlAlchemy.

Is there any way to get around the connection string?

For example this works using mxodbc.

Example:

> qdb = mx.ODBC.Windows.DriverConnect('DSN=QRQBwhoimp')

That gives me a connection I can declare a cursor on and get data in and out.

 

Anyway to translate that into a viable sqlAlchemy connection?

 

[Michael Bayer]

there have been fixes and improvements to the mxodbc driver in 0.8, so you can try that, however there hasn't been a problem with connecting.

We connect like this:

from mx.ODBC import Windows

conn = Windows.connect('QRQBwhoimp', user='', password='')

I dont know what the "DriverConnect" function is.    I've developed and recently improved the mxodbc driver through direct collaboration with mxodbc's author.

You might want to enable "echo=True" on create_engine() to determine exactly what "function" is not supported, it may be attempting to execute a statement the driver doesn't like.

[E.Scott M.]

How do you translate

conn = Windows.connect('QRQBwhoimp', user='', password='')

into something that sqlalchemy uses?

 

I have not been able to do this - sqlalchemy wants something like

engine = create_engine('mssql+mxodbc://@QRQBwhoimp')

 

am I missing something? or did I misunderstand your meaning?

Scott

[Michael Bayer]

the idea is if Windows.connect(...) works for you, then SQLAlchemy will work for you, at least as far as connecting, also.

step 1:

see if conn = Windows.connect(..) works for you

step 2:

if so, run create_engine(..., echo=True), then see how far the engine gets when you connect()

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/JYGDp_bQSP8J.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[E.Scott M.]

Windows.connect does not work for me.

I get this

>>> conn = Windows.connect('QRQBwhoimp', user = '', password = '')

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
mx.ODBC.Error.NotSupportedError: ('IM001', 0, '[Microsoft][ODBC Driver Manager] Driver does not support this function',
12173)

Which I guess is the same message as before.

 

However,

DriverConnect lets me use the DSN=QRQBwhoimp form and that seems to work.

 

>>> conn = Windows.DriverConnect('DSN=QRQBwhoimp')
>>>
>>> dir(conn)
['__enter__', '__exit__', 'close', 'commit', 'cursor', 'getconnectoption', 'getinfo', 'nativesql', 'rollback', 'setconne
ctoption']
>>>

 

[Michael Bayer]

there's an awkward way we can work around that but if you bought mxodbc, they should be able to maybe help out with that ?   there might be some arguments that connect() needs for your ODBC drivers.     if mxodbc support can give you some guidance that would make this a lot easier to figure out.