pyodbc connections string with DSN and autocommit Help Please!

[ScottyMac]

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

[Michael Bayer]

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.

--
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.

[ScottyMac]

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

[Michael Bayer]

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})

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/wc22ZhMavDUJ.

[ScottyMac]

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

[Michael Bayer]

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})

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/1S0LOWAysQ0J.

[ScottyMac]

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;' ()

"

[Michael Bayer]

yeah that's where the MSSQL dialect is doing things that aren't going to work with QODBC.   I don't have a quick fix for this for you, it would require creating a new dialect that doesn't perform these particular startup queries.   unless you want to get into doing a little bit of programming, you might have to work directly with pyodbc for now.

[Jorge Flávio Aquino da Costa]

yeah that's where the MSSQL dialect is doing things that aren't going to work with QODBC.   I don't have a quick fix for this for you, it would require creating a new dialect that doesn't perform these particular startup queries.   unless you want to get into doing a little bit of programming, you might have to work directly with pyodbc for now.

  Michael and Scotty,

  I´m facing a similar problem, but regarding to Intersystems Caché DB. I can connect through pyOdbc and proprietary cacheodbc driver for win64. At the moment, there´s no one single dialect of Caché DB for SA and that forced me to build one. I also didn´t find any documentation about how to do it so I tried to modify the existing mssql that used pyodbc too.

  I could figure some quirks out such as how to find the default schema but hardcoded others (currently loged user). The mssql dialect was renamed to cachedb and its new reencarnation managed to connect to my test database with a relative success (few non fatal errros). But, as of now, the compiled sql followed MS SQL conventions and surrounded the column names with a '[' as in:  "Select [Person].[Name], [Person].[Phone] From [dbo].[Person]" where it should be "Select Person.Name, Person.Phone From dbo.Person".

  The lack of documentation didn´t help me to discover where and how to alter that decoration behaviour not supported by Intersystems Caché. Any help about this problem? Is there a howto in creating SA dialects from scratch? Is this a better aproach?

Thanks,
Jorge Costa

[Michael Bayer]

people usually create dialect by following the examples of the existing ones.   The quoting behavior is controlled by the IdentifierPreparer provided by the dialect and is fairly self-explainatory.

[Jorge Flávio Costa]

  Thank you so much, Michael. It worked fine! Currently it shows me a few warnings about getting schemas and server version which I shall figure it out later. Raw Selects and Selects through Sessions also work.

  I really tried to find that quotations marks (that I knew was hardcoded somewhere but Eclipse didn´t find them. Maybe I did something wrong :( ).

  At the moment, I´ll keep improving the dialect to remove those errors and warning messages so it could be used by others interested in Caché development. Any tips regarding where I should start? What´s the least common denominator in dialects? Using MS SQL as my template is fine? Once again, thanks for your help.

[Michael Bayer]

On Feb 1, 2013, at 12:40 PM, Jorge Flávio Costa wrote:

>
> At the moment, I´ll keep improving the dialect to remove those errors and warning messages so it could be used by others interested in Caché development. Any tips regarding where I should start? What´s the least common denominator in dialects? Using MS SQL as my template is fine? Once again, thanks for your help.

usually people choose one of MySQL, MSSQL, Postgresql, or Oracle, depending on which one is closest to how their database works.

For examples of external dialects that follow the now "official" layout and testing practices, see:

https://github.com/zzzeek/ibm_db_sa

https://github.com/zzzeek/akiban_sqlalchemy