On Dec 19, 2012, at 2:49 PM, ScottyMac wrote:
> 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
as I said, adding it to create_engine:
from sqlalchemy import create_engine
engine = create_engine('mssql+pyodbc://ADMIN@Quickbooks',
connect_args={"autocommit":True})
>
>
>
> 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.
>> 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/-/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.
--
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.
