Re: [sqlalchemy] Problems filtering on primary_join with ORM relationship

[Michael Bayer]

On Nov 9, 2012, at 10:55 AM, Benjamin Sims wrote:

> Hi,
> 
> We have a relationship that looks something like this:
> 
> User 1 - * Posts
> 
> Posts can have various states, let's say 'DRAFT', 'LIVE', 'DELETED'.
> 
> Sometimes, we want to get all of the posts in existence for a user by doing:
> 
> user.all_posts
> 
> Sometimes, we just want to get posts that are not deleted:
> 
> user.open_posts
> 
> We opted to do this by specifying the primary_join on the relationship, thus:
> 
> open_posts = relationship("Post", = "and_(User.id==Post.user_id,"·
>   "or_(Post.status=='DRAFT', 
> Post.status=='LIVE'))")
> 
> all_posts = relationship("Post")
> 
> (as suggested here: 
> http://stackoverflow.com/questions/2863786/how-do-i-specify-a-relation-in-sqlalchemy-where-one-condition-requires-a-column)
> 
> This worked, but we now have a problem in some of our processing - when we 
> use user.open_posts before changes have been sent to the database, all posts 
> including those deleted are returned.
> 
> Putting in session.refresh at various points now seems to do the trick, but I 
> wanted to ask:
> 
> 1. Is our approach generally correct as a way of doing filtered 
> relationships/joins
> 2. Are session.refresh calls the right way to make it work while changes are 
> still in memory

better ways:

1. put a @validates listener on Post, such that when the "status" flag changes, 
it looks at the parent User object's "open_posts" collection and removes 
itself.  open_posts should be viewonly=True so the history events shouldn't be 
an issue (and if they are, you can use 
sqlalchemy.orm.attributes.set_committed_value() to re-set a new collection with 
the item removed).

2. use @property and/or @hybrid_property for open_posts, and evaulate the 
"open_posts" collection as a simple in-Python subset of "user.all_posts" when 
invoked in Python.   This is probably how I'd do it.

3. If you truly want to hit the DB again if a Post.status has changed, then 
still use events like @validates on Post.status, but instead of using 
"refresh()" use "expire()" on Post.open_posts, so that SQL isn't emitted if and 
until the Post.open_posts collection is actually accessed.


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

Re: [sqlalchemy] Named tuples in SQLAlchemy

[Michael Bayer]

NamedTuple is a tough one - because with our result sets we need to create a 
new NamedTuple for every call to execute(), meaning it has to be performant not 
just on creating new instances of the tuple, but on creating new tuple types as 
well.

If you look at the source to NamedTuple, it is going through some very 
elaborate hoops to produce the usage contract it has, including defining a 
whole new custom "tuple" subclass as a Python string, then exec'ing that code 
to produce the new class.   This is a common technique of producing very 
well-behaved dynamic classes.

Then, not only does it exec() a whole Python source string, after that it 
actually does *stack frame* logic to further manipulate the object so that it 
is pickleable - again this is because it's a dynamically generated type.

NamedTuple starts at 
http://hg.python.org/cpython/file/f938d478359a/Lib/collections.py#l237.

I use NamedTuple a lot, but to have the generation of new NamedTuples added 
onto the latency of all Query executions, including stack frame poking as well 
as parsing and invoking 29 lines of Python code, kind of frightens me.

Our own "keyed tuple" has a different way of working, in that we just have a 
fixed object, not a custom type.  There is no latency for creating new types 
and its constructor is almost as simple as that of a plain NamedTuple (we just 
send the "keys" along each time, not a big deal since we only do this in one 
place).NamedTuple's custom type generation is why it has such involved 
hoops to jump through.

We can certainly add the methods you're describing to the object.  i've added 
http://www.sqlalchemy.org/trac/ticket/2601 for this.   Feel free to add your 
thoughts there.

it's also possibly worth it to performance check our own util.KeyedTuple 
against NamedTuple, just to get an idea for if/how much the performance 
differs.   My impression though is that there'd be a significant speed bump, 
though, and on the other end I often have to help people get Query to speed up 
as it is.


On Nov 9, 2012, at 6:49 AM, Christoph Zwerschke wrote:

> Query results in SQLAlchemy are returned as named tuples, but SQLAlchemy uses 
> its own flavor of named tuples which is not fully compatible with 
> collections.namedtuple in the standard lib. For instance, "_fields" is called 
> "_labels" in SQLAlchemy, and the method "_asdict()" which could be helpful to 
> convert query results to JSON is missing in SQLAlchemy. Wouldn't it be better 
> to use the standard collections.namedtuples. I know it's only available since 
> Py 2.6, but SQLAlchemy will eventually only work with Py 2.6 anyway, and 
> could just use a fallback implementation for Py 2.5 for the time being.
> 
> -- Christoph
> 
> -- 
> 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.
> 
> 

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

Re: [sqlalchemy] relationship problem

[Michael Bayer]

On Nov 9, 2012, at 3:45 AM, Werner wrote:

> Hi,
> 
> I don't understand why on one record I don't get the authuser relation.
> 
> My definition is:
> class Cellar(DeclarativeBase, mix.StandardColumnMixin):
>__tablename__ = u'cellar'
> 
>name = sa.Column(sa.Unicode(length=50), nullable=False)
>fk_authuser_id = sautils.reference_col('authuser')
> 
> Cellar.authuser = sao.relationship('Authuser', backref='cellars', primaryjoin=
>('Cellar.id==Authuser.fk_cellar_id'), uselist=False)
> 
> In my authuser class I have:
> class Authuser(DeclarativeBase, mix.StandardColumnMixin):
>__tablename__ = u'authuser'
> 
>name = sa.Column(sa.Unicode(30), nullable=False, index=True)
> 
> 
># not using reference_col due to the use of Authuser in cellar
>fk_cellar_id = sa.Column(sa.BigInteger(), sa.ForeignKey(u'cellar.id',
>name='fk_cellar_id', use_alter=True))
> 
> Authuser.cellar = sao.relationship('Cellar', primaryjoin=
>('Authuser.fk_cellar_id==Cellar.id'))
> 
> With this query I don't get the authuser relation on the second record even 
> so the fk_authuser_id is set to 1, which is the same as on the first record.

above, fk_authuser_id is used by the Authuser.cellar relationship, not the 
Cellar.authuser relationship.  You'd need fk_cellar_id to be meaningful in that 
case.

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

Re: [sqlalchemy] (SQLite) "Outside" auto-locking based on SQLAlchemy Events

[Michael Bayer]

On Nov 8, 2012, at 5:01 PM, Torsten Landschoff wrote:

> 
> My first tests with the SQLAlchemy core where promising, but when using
> the ORM I get a bunch of deadlocks where it seems like the session opens
> two connections A and B where A locks B out.

The Session never does this, assuming just one Engine associated with it.  It 
acquires one Connection from the Engine, holds onto it and uses just that 
connection, until commit() at which point the connection is released to the 
pool.   

SQLite supports a "SERIALIZABLE" mode of isolation, in conjunction with a 
workaround for a pysqlite bug 
(http://docs.sqlalchemy.org/en/rel_0_7/dialects/sqlite.html#serializable-transaction-isolation)
 which might be what you're looking for, though I generally try to steer users 
away from any usage of SQLite that depends on high concurrency (see "High 
Concurrency" at http://sqlite.org/whentouse.html).

To diagnose this code, you'd need to make use of the tools available - which 
includes connection pool logging, engine logging, and possibly usage of custom 
pools like sqlalchemy.pool.AssertionPool which ensures that only one connection 
is used at any time.


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

Re: [sqlalchemy] [Q] Move elements in one-to-many relationship to a new owner

[Michael Bayer]

there's no mapping or code example provided here, which makes a condition like 
this extremely difficult to diagnose, however I've prepared a test script that 
takes its best guess as to configuration, that is, a client_products collection 
with a "client" backref, and a cascade of "all, delete-orphan" on the 
one-to-many which maximizes the conditions under which a ClientProduct might be 
deleted.   The test case below does not exhibit this behavior; please alter it 
such that it reproduces the condition you are seeing so that we can diagnose 
this fully and ensure you aren't coming across any new SQLAlchemy bugs:


from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Client(Base):
__tablename__ = "a"

id = Column(Integer, primary_key=True)
data = Column(String)
client_products = relationship("ClientProduct",
backref='client',
cascade="all, delete-orphan")


class ClientProduct(Base):
__tablename__ = "b"

id = Column(Integer, primary_key=True)
data = Column(String)
a_id = Column(Integer, ForeignKey('a.id'))

e = create_engine("sqlite://", echo=True)

Base.metadata.create_all(e)

s = Session(e)


s.add_all([
Client(data='c1', client_products=[
ClientProduct(data='cp1'),
ClientProduct(data='cp2'),
ClientProduct(data='cp3'),
]),
Client(data='c2', client_products=[
ClientProduct(data='cp4'),
ClientProduct(data='cp5'),
ClientProduct(data='cp6'),
]),
])
s.commit()


c1 = s.query(Client).filter_by(data='c1').one()
c2 = s.query(Client).filter_by(data='c2').one()

cp1, cp2 = c1.client_products[0:2]

cp1.client = c2
cp2.client = c2
s.delete(c1)
s.commit()

assert s.query(Client.data).all() == [('c2',)]
assert s.query(ClientProduct.data).all() == [('cp1', ), ('cp2', ), ('cp4', ), 
('cp5', ), ('cp6', )]




On Nov 8, 2012, at 11:30 AM, Ladislav Lenart wrote:

> Hello.
> 
> I have a client which has a collection of ClientProduct-s (ClientProduct has a
> FK to Client). The following code:
> 
># Move some client products from a duplicate to the original.
># Remove duplicate clients afterwards (in cascade).
>#
># Note that client_map is a dict from a duplicate to its original.
>for each_duplicate, each_client in client_map.iteritems():
>for each_cp in each_duplicate.client_products:
>if some_condition(each_cp):
>each_cp.client = each_client
>session.delete(each_duplicate)
>session.flush()
> 
> deletes a client product that was moved from each_duplicate to each_client in
> the inner loop. Why? What can I do to prevent it?
> 
> 
> Thank you in advance,
> 
> Ladislav Lenart
> 
> -- 
> 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.
> 
> 

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

Re: [sqlalchemy] Best Practice for model discovery?

[Chris Withers]

I tend to use a venusian scan (a pyramid config scan does the same 
thing) which spiders your package and makes sure everything is imported


Check out my mortar_rdb package for some more fun and games in this 
area, although I need to give it a re-write to use alembic instead of 
the terrible sqlalchemy-migrate...


cheers,

Chris

On 01/08/2012 17:30, Michael Bayer wrote:

usually the pattern is you just import one module, like "from myapp
import model". "model/__init__.py" then has imports for everything
within, and the pattern repeats as you descend through the directory
tree, that is, every top level __init__.py imports the important bits
from within that package.

the only other way would be to do a find of .py files within a model
directory and then import them with importlib or similar, which is more
complicated, non-deterministic as far as ordering and more prone to
import resolution issues. Adding per-package imports as you go along
just creates this same traversal as part of the code.

if the Python interpreter is never told of the existence of some .py
file, it doesn't exist. it doesn't matter that "Base" is used in that
file, the classes within don't exist until their owning module is imported.



On Aug 1, 2012, at 12:07 PM, John Anderson wrote:


In my pyramid apps I have a create script that generates my database
for production and a different script that generates my database for
my tests.

But if I don't import the module the models are in then they aren't
discovered even though they all share the same Base which is the class
I get the metadata from to call create_all, like:

Base.metadata.create_all(engine)

If I import them then they are picked up but I don't want to have to
remember to import every new module I create. Is there a good way to
do model discovery for this?

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



__
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
__

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


--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk

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

[sqlalchemy] Problems filtering on primary_join with ORM relationship

[Benjamin Sims]

Hi,

We have a relationship that looks something like this:

User 1 - * Posts

Posts can have various states, let's say 'DRAFT', 'LIVE', 'DELETED'.

Sometimes, we want to get all of the posts in existence for a user by doing:

user.all_posts

Sometimes, we just want to get posts that are not deleted:

user.open_posts

We opted to do this by specifying the primary_join on the relationship,
thus:

open_posts = relationship("Post", = "and_(User.id==Post.user_id,"·
  "or_(Post.status=='DRAFT',
Post.status=='LIVE'))")

all_posts = relationship("Post")

(as suggested here:
http://stackoverflow.com/questions/2863786/how-do-i-specify-a-relation-in-sqlalchemy-where-one-condition-requires-a-column
)

This worked, but we now have a problem in some of our processing - when we
use user.open_posts before changes have been sent to the database, all
posts including those deleted are returned.

Putting in session.refresh at various points now seems to do the trick, but
I wanted to ask:

1. Is our approach generally correct as a way of doing filtered
relationships/joins
2. Are session.refresh calls the right way to make it work while changes
are still in memory

Thanks for any ideas,
Ben

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

[sqlalchemy] Named tuples in SQLAlchemy

[Christoph Zwerschke]

Query results in SQLAlchemy are returned as named tuples, but SQLAlchemy 
uses its own flavor of named tuples which is not fully compatible with 
collections.namedtuple in the standard lib. For instance, "_fields" is 
called "_labels" in SQLAlchemy, and the method "_asdict()" which could 
be helpful to convert query results to JSON is missing in SQLAlchemy. 
Wouldn't it be better to use the standard collections.namedtuples. I 
know it's only available since Py 2.6, but SQLAlchemy will eventually 
only work with Py 2.6 anyway, and could just use a fallback 
implementation for Py 2.5 for the time being.


-- Christoph

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

Re: [sqlalchemy] [Q] Move elements in one-to-many relationship to a new owner

[Ladislav Lenart]

Hello.

I managed to solve my problem. I use session.execute(...) instead of
session.delete(), because the second form deletes more things than it should.
The working code is:

# Move some client products from a duplicate to the original.
# Remove duplicate clients afterwards (in cascade).
#
# Note that client_map is a dict from a duplicate to its original.
for each_duplicate, each_client in client_map.iteritems():
for each_cp in each_duplicate.client_products:
if some_condition(each_cp):
each_cp.client = each_client
session.flush()
table = Client.__table__
duplicate_ids = [each.id for each in duplicate_clients.iterkeys()]
q = table.delete().where(table.c.id.in_(duplicate_ids))
session.execute(q)

I still want to know whether this is expected behaviour and if so why:
 1. ClientProduct has FK to Client. Client has relationship client_products with
ON DELETE CASCADE. Thus if a client is deleted, all its client_products are
deleted too.
 2. Suppose I have a client with two client_products.
 3. I move one of them to a different client: client.client_products[0].client =
other_client.
 4. I delete the client: session.delete(client)
 5. session.deleted now contains TWO ClientProduct instances instead of ONE even
though I moved one of them to a completely different client.
 6. Why?! Can I do anything to prevent this, e.g. insert call to
session.flush(), session.expunge(), session.refresh() or some such somewhere?
All my attempts with session.flush() failed (had no effect).


Thank you,

Ladislav Lenart


On 8.11.2012 17:30, Ladislav Lenart wrote:
> Hello.
> 
> I have a client which has a collection of ClientProduct-s (ClientProduct has a
> FK to Client). The following code:
> 
> # Move some client products from a duplicate to the original.
> # Remove duplicate clients afterwards (in cascade).
> #
> # Note that client_map is a dict from a duplicate to its original.
> for each_duplicate, each_client in client_map.iteritems():
> for each_cp in each_duplicate.client_products:
> if some_condition(each_cp):
> each_cp.client = each_client
> session.delete(each_duplicate)
> session.flush()
> 
> deletes a client product that was moved from each_duplicate to each_client in
> the inner loop. Why? What can I do to prevent it?
> 
> 
> Thank you in advance,
> 
> Ladislav Lenart

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

[sqlalchemy] relationship problem

[Werner]

Hi,

I don't understand why on one record I don't get the authuser relation.

My definition is:
class Cellar(DeclarativeBase, mix.StandardColumnMixin):
__tablename__ = u'cellar'

name = sa.Column(sa.Unicode(length=50), nullable=False)
fk_authuser_id = sautils.reference_col('authuser')

Cellar.authuser = sao.relationship('Authuser', backref='cellars', 
primaryjoin=

('Cellar.id==Authuser.fk_cellar_id'), uselist=False)

In my authuser class I have:
class Authuser(DeclarativeBase, mix.StandardColumnMixin):
__tablename__ = u'authuser'

name = sa.Column(sa.Unicode(30), nullable=False, index=True)


# not using reference_col due to the use of Authuser in cellar
fk_cellar_id = sa.Column(sa.BigInteger(), sa.ForeignKey(u'cellar.id',
name='fk_cellar_id', 
use_alter=True))


Authuser.cellar = sao.relationship('Cellar', primaryjoin=
('Authuser.fk_cellar_id==Cellar.id'))

With this query I don't get the authuser relation on the second record 
even so the fk_authuser_id is set to 1, which is the same as on the 
first record.


q = session.query(db.Cellar)

for i in q:
print i.name
print i.fk_authuser_id
print i.authuser.name

The output is:
Main Cellar
1
default
Special Reserve
1
--- attribute error NoneType object has no attribute 'name'

I am still on 0.7.9.

What am I doing wrong?

Werner

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

[sqlalchemy] Re: Using Sqlalchmy with pyodbc driver to access a MS SQL Server

[Andrea Cappelli]

Hi,
I solved the problem putting

[FreeTDS] 
Description = TDS driver (Sybase/MS SQL) 
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

in /etc/odbcinst.ini

and using the following uri
mssql+pyodbc://user:pass@host:1433/dbname?driver=FreeTDS

Hope this helps

Andrea


Il giorno venerdì 9 novembre 2012 08:35:33 UTC+1, Andrea Cappelli ha 
scritto:
>
> Hi,
> I'm trying to access a MS Sql Server from a python script using SQL 
> Alchemy.
>
> If I use pymssql driver everything works well, except I receive a warning 
> about Decimal field converted to float, with some possible data loss; so I 
> tried to switch to pyodbc driver that shouldn't have this issue, but was 
> not able to configure it on my Ubuntu 12.04 box, I receive the following 
> error:
>
> sqlalchemy.exc.DBAPIError: (Error) ('IM004', "[IM004] [unixODBC][Driver 
> Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed (0) 
> (SQLDriverConnect)") None None
>
> In my virtualenv there is:
> 1) pyodbc==3.0.6
> 2) SQLAlchemy==0.7.9
>
> and I have set up the conf file /etc/odbc.ini as follows
> [handle]
>
> Driver = /usr/lib/x86_64-linux-gnu/libodbc.so.1
>
>  Description = description
>
>  Server = $db_host
>
>  Port   = 1433
>
>  TDS_Version = 8.0
>
> I use the following uri when connecting: mssql+pyodbc://user:pass@handle
>
> I have the following packages installed:
> unixodbc 
> unixodbc-dev 
> freetds-dev 
> tdsodbc
> python-dev
>
> Anyone has a similar setup or can point me towards a saolution for thsi 
> issue?
>
> Best regards
>
>

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