On Wednesday, December 19, 2012 3:37:16 PM UTC-6, Michael Bayer wrote:
>
>
> 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})
>
>
> I suppose I am thick(stupid).
> Having just told me in a previous post not to use mssql+ unless my
> database was really MSSQl, I did not include that - and it gives me a
> module not found error.
> HOWEVER:
> If I use exactly the string you have listed above, my autocommit error
> goes away, replaced by another error:
> "
>
> >>> engine = create_engine('mssql+pyodbc://ADMIN@Quickbooks',
> connect_args={"aut
>
> ocommit":True})
>
> >>> con = engine.connect()
>
> Traceback (most recent call last):
>
> File "<stdin>", line 1, in <module>
>
> File "c:\Development\libs\trunk\sqlalchemy\engine\base.py", line 1811,
> in conn
>
> ect
>
> return self.Connection(self, **kwargs)
>
> File "c:\Development\libs\trunk\sqlalchemy\engine\base.py", line 832,
> in __ini
>
> t__
>
> self.__connection = connection or engine.raw_connection()
>
> File "c:\Development\libs\trunk\sqlalchemy\engine\base.py", line 1874,
> in raw_
>
> connection
>
> return self.pool.unique_connection()
>
> File "c:\Development\libs\trunk\sqlalchemy\pool.py", line 142, in
> unique_conne
>
> ction
>
> return _ConnectionFairy(self).checkout()
>
> File "c:\Development\libs\trunk\sqlalchemy\pool.py", line 369, in
> __init__
>
> rec = self._connection_record = pool.get()
>
> File "c:\Development\libs\trunk\sqlalchemy\pool.py", line 213, in get
>
> return self.do_get()
>
> File "c:\Development\libs\trunk\sqlalchemy\pool.py", line 734, in do_get
>
> con = self.create_connection()
>
> File "c:\Development\libs\trunk\sqlalchemy\pool.py", line 147, in
> create_conne
>
> ction
>
> return _ConnectionRecord(self)
>
> File "c:\Development\libs\trunk\sqlalchemy\pool.py", line 258, in
> __init__
>
> l.first_connect(self.connection, self)
>
> File "c:\Development\libs\trunk\sqlalchemy\engine\strategies.py", line
> 151, in
>
> first_connect
>
> dialect.initialize(c)
>
> File "c:\Development\libs\trunk\sqlalchemy\connectors\pyodbc.py", line
> 114, in
>
> initialize
>
> super(PyODBCConnector, self).initialize(connection)
>
> File "c:\Development\libs\trunk\sqlalchemy\dialects\mssql\base.py",
> line 1095,
>
> in initialize
>
> super(MSDialect, self).initialize(connection)
>
> File "c:\Development\libs\trunk\sqlalchemy\engine\default.py", line
> 155, in in
>
> itialize
>
> self._get_default_schema_name(connection)
>
> File "c:\Development\libs\trunk\sqlalchemy\dialects\mssql\base.py",
> line 1111,
>
> in _get_default_schema_name
>
> user_name = connection.scalar("SELECT user_name() as user_name;")
>
> File "c:\Development\libs\trunk\sqlalchemy\engine\base.py", line 1165,
> in scal
>
> ar
>
> return self.execute(object, *multiparams, **params).scalar()
>
> File "c:\Development\libs\trunk\sqlalchemy\engine\base.py", line 1191,
> in exec
>
> ute
>
> params)
>
> File "c:\Development\libs\trunk\sqlalchemy\engine\base.py", line 1287,
> in _exe
>
> cute_text
>
> return self.__execute_context(context)
>
> File "c:\Development\libs\trunk\sqlalchemy\engine\base.py", line 1302,
> in __ex
>
> ecute_context
>
> context.parameters[0], context=context)
>
> File "c:\Development\libs\trunk\sqlalchemy\engine\base.py", line 1401,
> in _cur
>
> sor_execute
>
> context)
>
> File "c:\Development\libs\trunk\sqlalchemy\engine\base.py", line 1394,
> in _cur
>
> sor_execute
>
> context)
>
> File "c:\Development\libs\trunk\sqlalchemy\engine\default.py", line
> 299, in do
>
> _execute
>
> cursor.execute(statement, parameters)
>
> sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', '[42000]
> [QODBC] E
>
> xpected lexical element not found: FROM (11015) (SQLExecDirectW)') 'SELECT
> user_
>
> name() as user_name;' ()
>
> "
>
>
>
> 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 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/-/-IdDP57V2K4J.
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.
