Unable to use the 'mock' strategy: AttributeError: 'MockConnection' object has no attribute 'run_callable'

[Manuel Vázquez Acosta]

Hi,

I'm assessing how to use SA within another project that uses psycopg2 and maintains it's connection pool and the like.

I have the following code:

    @property
    def table(self):
        from sqlalchemy import create_engine
        from sqlalchemy.schema import Table
        table = self.meta.tables.get(self.obj._table, None)
        if table is None:
            global _engine
            if _engine is None:
                _engine = create_engine(
                    create_dsn(self.obj),
                    strategy='mock',
                    executor=self.execute
                )
            table = Table(self.obj._table, self.meta, autoload=True,
                          autoload_with=_engine)
        return table

    def execute(self, sql, *multiparams, **params):
        print(sql, multiparams, params)

But when I access the `.table` property of this object I get an error:
 
   AttributeError: 'MockConnection' object has no attribute 'run_callable'

I'm using SQLAlchemy 1.1.5.  Is this a bug or the 'executor' API has changed?

Best regards,
Manuel.

[mike bayer]

it's sort of a bug but you're attempting to do a thing that in any case
is not possible. The "mock" execution strategy does not support
operations that require result sets, because it isn't actually querying
a database. If we add the "run_callable" method onto the
MockConnection (the bug), you'll get an error soon after that where the
autoload process cannot access a result set.

If you'd like to intercept real SQL statements as they are emitted, the
quickest way is to use the before_cursor_execute() event listener:

http://docs.sqlalchemy.org/en/latest/core/events.html?highlight=before_cursor_execute#sqlalchemy.events.ConnectionEvents.before_cursor_execute





>
> Best regards,
> Manuel.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Manuel]

mike bayer <mik...@zzzcomputing.com> writes:
> it's sort of a bug but you're attempting to do a thing that in any case is not
> possible. The "mock" execution strategy does not support operations that
> require result sets, because it isn't actually querying a database. If we
> add the "run_callable" method onto the MockConnection (the bug), you'll get an
> error soon after that where the autoload process cannot access a result set.
>
> If you'd like to intercept real SQL statements as they are emitted, the
> quickest way is to use the before_cursor_execute() event listener:
>
> http://docs.sqlalchemy.org/en/latest/core/events.html?highlight=before_cursor_execute#sqlalchemy.events.ConnectionEvents.before_cursor_execute

Thanks, I'm still in the early stages of this project and any comments
are highly appreciated. What I'm trying to actually accomplish is to
build some complex queries to be executed against an Odoo [1] DB. Odoo
has it's own ORM, but it lacks some features I like the most about
SQLAlchemy:

- A clear API to define custom mappings.

- A clear low-level API to create SQL-like sentences (even coupled to
PostgreSQL) that would be a highly appreciated.

See [2] for a hard to maintain and test method.

I'd keep Odoo's models for the description of the DB layer. But I would
like more flexibility to represent the Python-side of some models.

Using the 'mock' strategy I thought I could run the SQL myself like and
funnel the SQL execution back to Odoo's cursors. Something like::

def execute(self, sql, *params, **other):
# self.obj.cr is wrapper around pyscopg2's cursor
self.obj.cr.execute(sql, params) # How to merge params and other?
return do_something_with(self.obj.cr.fetchall())

If the 'executor' returns a ResultProxy-like, the 'mock' strategy would
work? If it should work, then the problem would be to create a
ResultProxy compliant object that bridges Odoo's world to SA's.

I'm guessing the 'executor' is only called when the real query to the DB
is required. Not at "expression-build time". Am I right?

Maybe I need another approach. So far, I was trying to use SA's
introspection of tables to avoid having the describe the tables
myself. The goals are to be able to use SA's expression language to
build complex SQL queries.

Again, this is just the first stage of this project.

Best regards,
Manuel.


[1] https://github.com/odoo/odoo
[2] https://github.com/odoo/odoo/blob/8.0/addons/account/account_move_line.py#L37

[mike bayer]

On 02/14/2017 11:02 AM, Manuel wrote:
> mike bayer <mik...@zzzcomputing.com> writes:
>> it's sort of a bug but you're attempting to do a thing that in any case is not
>> possible. The "mock" execution strategy does not support operations that
>> require result sets, because it isn't actually querying a database. If we
>> add the "run_callable" method onto the MockConnection (the bug), you'll get an
>> error soon after that where the autoload process cannot access a result set.
>>
>> If you'd like to intercept real SQL statements as they are emitted, the
>> quickest way is to use the before_cursor_execute() event listener:
>>
>> http://docs.sqlalchemy.org/en/latest/core/events.html?highlight=before_cursor_execute#sqlalchemy.events.ConnectionEvents.before_cursor_execute
>
> Thanks, I'm still in the early stages of this project and any comments
> are highly appreciated. What I'm trying to actually accomplish is to
> build some complex queries to be executed against an Odoo [1] DB. Odoo
> has it's own ORM, but it lacks some features I like the most about
> SQLAlchemy:
>
> - A clear API to define custom mappings.
>
> - A clear low-level API to create SQL-like sentences (even coupled to
> PostgreSQL) that would be a highly appreciated.
>
> See [2] for a hard to maintain and test method.
>
> I'd keep Odoo's models for the description of the DB layer. But I would
> like more flexibility to represent the Python-side of some models.
>
> Using the 'mock' strategy I thought I could run the SQL myself like and
> funnel the SQL execution back to Odoo's cursors. Something like::

if Odoo gives you a "cursor", that implies you'd produce a dialect for
Odoo. Dialects can be produced for anything, while a pep249 DBAPI is
the easiest, it is possible to create limited dialects against anything
else.

The most exotic example is my proof of concept dialect against Pandas
dataframes: https://bitbucket.org/zzzeek/calchipan/ . It doesn't use
SQL at all, the SQL compiler produces objects that work on Pandas
objects.

>
> def execute(self, sql, *params, **other):
> # self.obj.cr is wrapper around pyscopg2's cursor
> self.obj.cr.execute(sql, params) # How to merge params and other?
> return do_something_with(self.obj.cr.fetchall())
>
> If the 'executor' returns a ResultProxy-like, the 'mock' strategy would
> work? If it should work, then the problem would be to create a
> ResultProxy compliant object that bridges Odoo's world to SA's.

"mock" is really a very quick one-off that isn't going to do much
outside of grabbing simple DDL. If you're looking to create full
front-to-back SQLAlchemy round trips over Odoo, your best bet is the
dialect, buliding on top of a pep-249-style DBAPI implementation against
whatever Odoo provides.

[Manuel]

I've cloned calchipan, and also looking at the implementation of the
standard dialects. Let's see if something comes up.

Thanks and best regards,
Manuel.