I have no problem at all with pyodbc and qodbc
Here is a sample I just did:
I include a call to connect without autocommit - which throws an error, and
then the one what works.
The error message is exactly the same one I get trying to connect
SQLAlchemy.
I do not know how to translate cx = pyodbc.connect('DSN=Quickbooks',
autocommit=True)
into an equivalent connection string for SQLAlchemy.
>>> cx = pyodbc.connect('DSN=Quickbooks')
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
pyodbc.Error: ('IM001', '[IM001] [QODBC] Driver not capable (11010)
(SQLSetConnn
ectAttr(SQL_ATTR_AUTOCOMMIT))')
>>> cx = pyodbc.connect('DSN=Quickbooks', autocommit=True)
>>> cursor = cx.cursor()
>>> cursor.execute("select * from vendor")
<pyodbc.Cursor object at 0x03237FA8>
>>> cursor.fetchone()
('8000056F-1349473233', datetime.datetime(2012, 10, 5, 16, 40, 33),
datetime.dat
etime(2012, 11, 23, 10, 37, 18), '1353688638', '7 Accessories
**06-08-10**', Tru
On Wednesday, December 19, 2012 12:11:05 PM UTC-6, Michael Bayer wrote:
> 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 sqlal...@googlegroups.com<javascript:>
> .
> To unsubscribe from this group, send email to
> sqlalchemy+...@googlegroups.com <javascript:>.
> 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/-/1S0LOWAysQ0J.
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.
