there's many levels this could be failing, and the first step would be to make a raw PyODBC connection to the database, to see if there's any incompatibilities there.
here's all the detail on how to do that: http://code.google.com/p/pyodbc/wiki/ConnectionStrings its possible the autocommit=True flag is needed here. if that's the case, you can add that to create_engine() like: create_engine(<url>, connect_args={"autocommit":True}) On Dec 19, 2012, at 1:06 PM, ScottyMac wrote: > Michael, > It definitly is not MSSQL - I picked that up from somewhere out there on the > internet. > I am trying to access quickbooks enterprise and it is quickbooks proprietary > database > I am using Qodbc for the quickbooks connector and pyodbc as the python > connector. > > Also, Table Reflection is not a big deal, I am very willing to define the > tables manually - but I cannot query the engine at all. > > The big question is this: > If I can connect pyodbc with qodbc and access the database from my python > shell with this connect string "DSN=Blah Blah", autocommit = True > Then what is the correct syntax to connect with SQLAlchemy? > > Or are you saying since SQLAlchemy does not have a specific driver for > Quickbooks, that it cannot be done? I was under the impression that if a DSN > was "working" that I could connect to it with SQLAlchemy. > > Thanks - Scott > > > On Tuesday, December 18, 2012 6:10:27 PM UTC-6, Michael Bayer wrote: > there might not be a choice here as the QODBC driver might not support some > of the operations that table reflection needs. It's also critical that this > database is in fact Microsoft SQL Server, and not something more like Access, > because it won't work at all if not. > > You'd want to set echo=True on create_engine(), and observe the queries being > emitted to see which one is failing. From there, you'd have to seek help > with the specific quirks of QODBC. These "lesser" ODBC drivers, like the one > for Microsoft Access, frequently have a lot of these "dead end" situations > where not enough functionality is provided to perform the tasks SQLAlchemy > needs to do. > > Is this truly a Microsoft SQL Server database ? if so, I'd look into setting > up a traditional SQL Server ODBC connection which has full capabilities. If > QuickBooks does *not* use Microsoft SQL Server, then this definitely won't > work at all as SQLAlchemy's MSSQL driver is coded to the views and functions > that are specific to SQL Server. > > > > > > On Dec 18, 2012, at 3:13 PM, ScottyMac wrote: > >> pyodbc version = 2.1.9-beta16 >> Trying to connect sqlalchemy to quickbooks enterprise database. Have a DSN >> setup with Qodbc called "quickbooks" >> This connection string works for pyodbc >> cnxn = pyodbc.connect(‘dsn=quickbooks’, autocommit=True) #Must set >> autocommit flag or get error message found below. >> I can open tables and query database - everything seems to be ok. >> However, I cannot get the sqlAlchemy connection working. >> Here is my consolidated code >> (__iint__.py) >> import tables >> from session import getSession >> (session.py) >> engine = create_engine('mssql+pyodbc://ADMIN@Quickbooks') >> Session = sessionmaker(bind = engine, autocommit=True) >> (tables.py) >> metaData = MetaData() >> metaData.bind = engine >> vendorTable = Table('vendor', metaData, autoload=True) >> Which yields this error message: >> sqlalchemy.exc.DBAPIError: (Error) ('IM001', '[IM001] [QODBC] Driver not >> capable >> (11010) (SQLSetConnnectAttr(SQL_ATTR_AUTOCOMMIT))') None None >> >>> >> I have tried all manner of connection strings in sqlalchemy and also >> different kinds of autocommit syntax and so far I cannot get it to work. I >> am sure some smart folks out there can help me out!! >> Thanks, >> Scott >> >> -- >> 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/-/cOhqTKsDv94J. >> 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. > > > -- > 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/-/wc22ZhMavDUJ. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
