Re: [sqlalchemy] Changing primary key's value in a multi-to-multi relation

[mike bayer]

On 02/14/2017 08:15 PM, Zsolt Ero wrote:

I would like to change a primary key's value, to be deterministic, based
on a multi-to-multi relation. Thus I'm populating the tables with a
temporary ids (just random strings), then calculating the right, unique
id, and changing it afterwards.


the examples seem to move "transaction.manager" around, which we assume 
is the Zope transaction manager and that by using the context manager 
the Session.commit() method is ultimately called, which raises this 
error.   One guess is that in the second two examples, the Session is 
not actually getting committed, because no invocation of "dbsession" is 
present within the "with transaction.manager" block and I have a vague 
recollection that zope.transaction might work this way.  Another guess 
is that in the second two examples, maybe you already changed the data 
in the DB and the operation you're doing has no net change to the rows.


In any case, all three examples you should echo the SQL emitted so you 
can see what it's doing.   Setting up the onupdate="CASCADE" should fix 
this problem.  As to why that didn't work from you, keep in mind that is 
a CREATE TABLE directive so if you just changed it in your model and 
didn't recreate the tables, or at least recreate the foreign key 
constraints using ALTER to drop and create them again with the CASCADE 
rule set up; this is a server side rule.


Here's the MCVE to demonstrate:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
import md5 as _md5
import random
import string


def md5(text):
return str(_md5.md5(text))


def random_string(num):
return ''.join(random.choice(
string.ascii_uppercase + string.digits) for _ in range(num))

Base = declarative_base()


class Image(Base):
__tablename__ = 'images'
id = Column(String, primary_key=True, default=lambda: 
random_string(16))

collections = relationship(
'Collection', secondary='collections_images', 
back_populates='images')

date_created = Column(DateTime, default=func.now())


class Collection(Base):
__tablename__ = 'collections'
id = Column(String, primary_key=True, default=lambda: 
random_string(16))

name = Column(String)
images = relationship(
'Image', secondary='collections_images',
back_populates='collections', order_by='desc(Image.date_created)',
lazy='dynamic')


collections_images = Table(
'collections_images', Base.metadata,
Column('collection_id',
   ForeignKey('collections.id', onupdate="CASCADE"),
   primary_key=True),
Column('image_id', ForeignKey('images.id'), primary_key=True)
)

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)

Base.metadata.drop_all(e)
Base.metadata.create_all(e)

s = Session(e)

with s.transaction:
s.add(Collection(name='c1', images=[Image(), Image(), Image()]))

with s.transaction:
collections = s.query(Collection).all()

for collection in collections:
image_ids = [i.id for i in collection.images.all()]
image_ids_string = ','.join(sorted(image_ids)) + collection.name
collection.id = md5(image_ids_string)[:16]






I have the following models:

|classImage(Base):id
=Column(String,primary_key=True,default=lambda:random_string(16))collections
=relationship('Collection',secondary='collections_images',back_populates='images')classCollection(Base):id
=Column(String,primary_key=True,default=lambda:random_string(16))images
=relationship('Image',secondary='collections_images',back_populates='collections',order_by='desc(Image.date_created)',lazy='dynamic')Table('collections_images',Base.metadata,Column('collection_id',ForeignKey('collections.id'),primary_key=True),Column('image_id',ForeignKey('images.id'),primary_key=True))|

My problem is the following:

 1.

Out of the 3 examples below, only one triggers an integrity
exception, the other two does not.

Why?

In all three I'm trying to write to this primary key, which is
referenced, thus should produce an exception. Yet, in 2. and 3. it
seem nothing is happening when |collection.id =| is set. When I
debug via SQL queries it shows absolutely nothing called for
the |collection.id =| line.

 2.

How can I solve this problem? I mean how can I change a primary
key's value which is also used in a multi-to-multi relation?

The DB is PostgreSQL 9.5 with psycopg2.

The examples are:


A. triggers exception:

|withtransaction.manager:collections
=dbsession.query(Collection).all()forcollection incollections:image_ids
=[i.id fori incollection.images.all()]image_ids_string
=','.join(sorted(image_ids))+collection.name collection.id
=md5(image_ids_string)[:16]|


B. does not trigger exception

|collections =dbsession.query(Collection).all()# ^ and v only these two
lines are swapped withtransaction.manager:forcollection
incollections:image_ids =[i.id fori

[sqlalchemy] Changing primary key's value in a multi-to-multi relation

[Zsolt Ero]

I would like to change a primary key's value, to be deterministic, based on 
a multi-to-multi relation. Thus I'm populating the tables with a temporary 
ids (just random strings), then calculating the right, unique id, and 
changing it afterwards.

I have the following models:

class Image(Base):
id = Column(String, primary_key=True, default=lambda: random_string(16))
collections = relationship('Collection', secondary='collections_images', 
back_populates='images')

class Collection(Base):
id = Column(String, primary_key=True, default=lambda: random_string(16))
images = relationship('Image', secondary='collections_images', 
back_populates='collections', order_by='desc(Image.date_created)', 
lazy='dynamic')

Table('collections_images', Base.metadata,
Column('collection_id', ForeignKey('collections.id'), primary_key=True),
Column('image_id', ForeignKey('images.id'), primary_key=True))

My problem is the following:

   1. 
   
   Out of the 3 examples below, only one triggers an integrity exception, 
   the other two does not.
   
   Why?
   
   In all three I'm trying to write to this primary key, which is 
   referenced, thus should produce an exception. Yet, in 2. and 3. it seem 
   nothing is happening when collection.id = is set. When I debug via SQL 
   queries it shows absolutely nothing called for the collection.id = line.
   2. 
   
   How can I solve this problem? I mean how can I change a primary key's 
   value which is also used in a multi-to-multi relation?
   
The DB is PostgreSQL 9.5 with psycopg2.

The examples are:


A. triggers exception:

with transaction.manager:
collections = dbsession.query(Collection).all()

for collection in collections:
image_ids = [i.id for i in collection.images.all()]
image_ids_string = ','.join(sorted(image_ids)) + collection.name
collection.id = md5(image_ids_string)[:16]


B. does not trigger exception

collections = dbsession.query(Collection).all()# ^ and v only these two lines 
are swapped with transaction.manager:
for collection in collections:
image_ids = [i.id for i in collection.images.all()]
image_ids_string = ','.join(sorted(image_ids)) + collection.name
collection.id = md5(image_ids_string)[:16]


C. also does not trigger exception

collections = dbsession.query(Collection).all()
for collection in collections:
image_ids = [i.id for i in collection.images.all()]
image_ids_string = ','.join(sorted(image_ids)) + collection.name
with transaction.manager:
collection.id = md5(image_ids_string)[:16]


The exception for the first one is:
sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) update or delete 
on table "collections" violates foreign key constraint 
"fk_collections_images_collection_id_collections" on table 
"collections_images" DETAIL: Key (id)=(jC3sN8952urTGrqz) is still 
referenced from table "collections_images".


I've also tried onupdate='CASCADE' for both columns in collections_images 
but didn't change anything.

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Unable to use the 'mock' strategy: AttributeError: 'MockConnection' object has no attribute 'run_callable'

[mike bayer]

On 02/14/2017 11:02 AM, Manuel wrote:

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




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



--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Unable to use the 'mock' strategy: AttributeError: 'MockConnection' object has no attribute 'run_callable'

[Manuel]

mike bayer  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

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Unable to use the 'mock' strategy: AttributeError: 'MockConnection' object has no attribute 'run_callable'

[mike bayer]

On 02/14/2017 09:53 AM, Manuel Vázquez Acosta wrote:

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?


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+unsubscr...@googlegroups.com
.
To post to this group, send email to sqlalchemy@googlegroups.com
.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] 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.

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.