Re: [sqlalchemy] association proxy and eager loading

[Jonathan Vanasco]

Thanks Simon!

I tried your method and it didn't work... because I had been resetting my 
query all along and not noticing (!!!)   FFS.

After extensive testing this morning, I found that mistake because these 3 
forms will generate the same exact sql in the current 1.x release:


# Chained joinedload
q1 = session.query(Collection)\
 .options(joinedload('to_items').joinedload('item'))

# Grouped joinedload
q2 = session.query(Collection)\
 .options(joinedload('to_items'), joinedload('to_items.item'))

# Compounded joinedload
q3 = session.query(Collection)\
 .options(joinedload('to_items'))\
 .options(joinedload('to_items.item'))

The same sql is generated and the same relationships are handled in each 
case.  association_proxy works as expected (seamlessly).


-- 
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] association proxy and eager loading

[Jonathan Vanasco]

I'm trynig to use association_proxy to map a collection of items through an 
intermediary table

I'm running into a problem where I can't figure out a way to eagerload the 
collection through the extension.

if i do:
query.options(joinedload('to_items', 'to_items.item'))

I can loop through to_items and item fine.

When I loop '.items', I hit the DB.

I tried eagerloading the 'items' relation, and that didn't work either.

I must be using the association_proxy wrong.  Does anything here jump out 
to others?




class Collection()
__tablename__ = 'collection'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.Unicode(32), nullable=False)

to_items = sa.orm.relationship("item_2_collection",

primaryjoin="collection.id==Item2Collection.collection_id",
back_populates='collection',
   )

items = association_proxy('to_items', 'item')


class Item():
__tablename__ = 'item'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.Unicode(32), nullable=False)


class Item2Collection()
__tablename__ = 'item_2_collection'
__primarykey__ = ['item_id', 'collection_id']
item_id = sa.Column(sa.Integer, sa.ForeignKey("item.id"), 
nullable=False, primary_key=True)
collection_id = sa.Column(sa.Integer, 
sa.ForeignKey("collection.id"), nullable=False, primary_key=True)

# relations
item = sa.orm.relationship("Item",
  
 primaryjoin="Item2Collection.item_id==Item.id",
   uselist=False,
   )
collection = sa.orm.relationship("Collection",

 primaryjoin="Item2Collection.collection_id==Collection.id",
 back_populates='to_items',
 uselist=False,
 )

-- 
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] Re: how to use postgresql json type in sql expression language

[Jonathan Vanasco]

http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSON

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Determination of string lengths

[Jonathan Vanasco]

`sqlalchemy.func` does not map anything.  It is a namespace for a factory 
generator.   anything you access with it becomes a function of that 
caller's name.

for example:

 filter( func.foo(table.column) > 1 )

produces

  WHERE foo(table.column) > 1

sqlalchemy generates the `foo` function dynamically though the `func` 
namespace.

In your example, `func.length` creates the sql "LENGTH()" not "LEN()".  It 
works because your backend supports "LENGTH" not "LEN".  Most databases use 
LENGTH (postgres, mysql, oracle, sqlite).  Only MsSQL uses LEN, and 
firebird has a completely different approach with CHAR_LENGTH, BIT_LENGTH, 
etc.

I don't think any more portability has ever been needed, because the 
functions are either:

* standardized across most databases due to common standards
* highly specfiic to a single database

Trying to create a system that standardizes how every database handles 
ancillary internal function "concepts" would be overwhelming and of little 
real utility.   

If you really wanted to pursue this, I'd imagine you would work on the SQL 
compliler or just inspect the db connection when you generate the query and 
conditionally use different forms.  

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: How to refer to fields in nested queries?

[Jonathan Vanasco]

If you don't use a subquery, you can use the `desc("column")` in 
`sqlalchemy`.  (there is an `asc()` as well).  Below I use `label('name')` 
to specify the result column name for computed field, then order by it.

results = dbSession.query(
   model.Foo.a,
   sqlalchemy.func.count(Model.Foo.b).label('count_b')
 )\
 .group_by(model.Foo.a)\
 .order_by(sqlalchemy,desc('count_b')\
 .all()

If you are using a subquery for additional processing or joins, you can use 
`label()` to specify the name for the computed column, then access that 
column as an attribute on the `.c` columns attribute of the subquery 
object.  

inner_query = dbSession.query(
   model.Foo.a,
   sqlalchemy.func.count(Model.Foo.b).label('count_b')
 )
 inner_query = inner_query.subquery('query_foo')
 results = 
dbSession.query(inner_query.c.count_b.label('count_results')).all()

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: How to refer to fields in nested queries?

[Jonathan Vanasco]

On Tuesday, December 8, 2015 at 12:14:46 PM UTC-5, SF Markus Elfring wrote:
>
> Will it become easier to recognise the relevant relationships 
> from the corresponding programming interface documentation? 
>

You should read through the tutorial/narrative documentation on the ORM and 
"Core", as well as the FAQ.  That will familiarize you a lot with where to 
find the information.

The SqlAlchemy library is incredibly robust and large; even after years of 
usage people find new features.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Selection of sort directions

[Jonathan Vanasco]

Odd- this isn't the narrative docs

You can use column attributes... 
 
(http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.ColumnElement.desc)

session.query(model.Foo).order_by(model.Foo.id.desc())
session.query(model.Foo).order_by(model.Foo.id.asc())

session.query(model.Foo).order_by(model.Foo.id.desc(). 
model.Foo.id_b.asc())

There's also some sql expression in another 
package... 
http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.desc

session.query(model.Foo).order_by(desc(model.Foo.id))

If you have a column the standard way is to just do `column.asc()`

Using the `desc(column)` syntax is really only necessary when using literal 
sql or very complex queries.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: inserts rows in wrong order

[Jonathan Vanasco]

Sorry, hadn't finished my morning coffee and this "registered" as a dict 
ordering.

Without seeing code, it's hard to understand what is going on.  Here's my 
guess:

* When you call the insert(), the `output_batch` is inserted into the DB in 
that order.  This is expected and you should see that happen if your engine 
is set to `echo=True`
* When you query the table, any table, the database is free to return rows 
in any order it desires -- unless you explicitly pass in an "ORDER BY" 
clause.

So you'd have to tell the database how to return the rows.  Usually people 
will have a numeric `id` field that is a serial/sequence/autoincrement and 
will order a query based on that.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: inserts rows in wrong order

[Jonathan Vanasco]

Python dictionars do not preserve the order.

You could try using an 
OrderedDict https://docs.python.org/2/library/collections.html

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: inserts rows in wrong order

[Jonathan Vanasco]

Yeah, you need an "id" field to do that and "ORDER BY" it.  It's a 
benefit/limitation of SQL -- save time by giving random rows, or spend time 
sorting them.

Something that you should be wary of though...

If you do this:

INSERT INTO table (id) values (1,2,3,4...10);

Your select would be:

SELECT id FROM table LIMIT 10 ORDER BY id ASC;

However...

SqlAlchemy works like this:

`.flush()` will write to the database
`.commit()` will write to the database and commit the transaction

if you select/query after a commit(), you're in a new transaction.  You may 
need to use some other markers or track objects differently so that your 
"last 10" objects are related to the last 10 objects YOU inserted and not 
ones that anyone could have stored in the table since you stored yours. 



-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: load_only doesn't affect joins

[Jonathan Vanasco]

Thanks for posting a full self-contained working example of your problem!

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Splitting a table ?

[Jonathan Vanasco]

I recently had to "split" or partition another table into 2 -- one of high 
write and low write access.  The new table is just the high-write columns 
fkey'd onto the original table, and handled with a relationship.

I was wondering if there was any "shortcut" in sqlalchemy to automatically 
handle stuff like this, or a common pattern.

The best I could think of is using an association proxy to map the columns back 
-- but that must be done for every column, and doesn't handle the creation of 
the new table as a dependency.

My current manual solution works, just wondering if there are better ways.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: set next value of postgres sequence

[Jonathan Vanasco]

On Tuesday, December 1, 2015 at 3:39:02 PM UTC-5, Chris Withers wrote:
>
> Indeed, but that's not quite what I asked ;-)
>
> I'm after setting the next value using, eg, setval:
>
>
Ah, sorry.  I saw "next value" and thought "nextval", not setval.   

The `Sequence` object just seems to support a 
'next_value()' 
http://docs.sqlalchemy.org/en/latest/core/defaults.html#sqlalchemy.schema.Sequence

I looked in the source, and it looks like the postgres dialect only has a 
`nextval` operation.  Sqlalchemy's compiler and dialects seem to only 
support `create sequence` and `nextval` (there's no "alter sequence 
[restart with]" or "setval" equivalents).   It looks like you can only pass 
in a start value for the sequence on creation.

I think you may need to handle this with literal sql, and create a ticket 
or try to patch.  

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] non-table DDL elements and MetaData objects

[Jonathan Vanasco]

I think this story may have some related info - 
https://bitbucket.org/zzzeek/sqlalchemy/issues/3442/no-control-of-ddl-sequences-for-indexes-fk
 

disclaimer -- I merely saw this earlier today and remembered it when seeing 
this post.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] non-table DDL elements and MetaData objects

[Jonathan Vanasco]

On Tuesday, December 1, 2015 at 5:02:28 PM UTC-5, Chris Withers wrote:
>
> Does the .listen example there work or does it need the patch to land?
>

The ticket is still open, so the patch is probably needed (and looks to be 
Michael's first attempt at making something pass).

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: postgresql, jsob and like operator

[Jonathan Vanasco]

On Tuesday, December 1, 2015 at 7:12:15 PM UTC-5, Michael Bayer wrote:

see also the JSON operators which have some built-in text casting stuff: 
>
>
> http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html?highlight=json#sqlalchemy.dialects.postgresql.JSON
>  
>
> this is also getting improved in 1.1. 
>

Oh yeah.  I naively assumed this was a search that purposefully wanted to 
avoid the Postgres JSON operators.  If you can use one of the postgres 
native searches, that will be MUCH faster.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: postgresql, jsob and like operator

[Jonathan Vanasco]

I don't think you can get that exact query staying within the ORM's 
cross-platform functionality -- I don't think there is anything that can 
generate the `::` version of casting... but I think something like this 
should produce the same output:

r = session.query( Device.id, Device.name, Device.details )\
.filter( sqlalchemy.sql.expression.cast(Device.details, 
sqlalchemy.types.String).like('99') )\
.all()

I'm doing this from memory, so i could be off -- but the general idea is 
that you'll `cast` into a `String`, and then run the `like` operator on 
that column.  The API docs will have the correct format.


That should generate something like :

   + SELECT id,name,details FROM Device WHERE cast(details as text) LIKE 
'%99%';

Instead of:

   - SELECT id,name,details FROM Device WHERE details::text LIKE '%99%';

If you need to use the `details::text` format, you could use a text literal 
in the WHERE clause. 
 see http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#using-textual-sql

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Creating a derived class object without inserting into base class table

[Jonathan Vanasco]

You should ask on the flask/flask-sqlalcemy support channel.  They'll be 
better able to help.  According to the docs :

~ Where can I get help?

  Join us on the #pocoo IRC channel on irc.freenode.net.


According to their API docs and examples, setting __tablename__ (standard 
sqlalchemy approach) should work.  

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Creating a derived class object without inserting into base class table

[Jonathan Vanasco]

It should still work as a reference because the pacakge you use doesn't 
override this.  

The extension's API makes this clear:
 http://flask-sqlalchemy.pocoo.org/2.1/api/#models


_tablename__ 


The name of the table in the database. This is required by SQLAlchemy; 
however, Flask-SQLAlchemy will set it automatically if a model has a 
primary key defined. If the __table__ or __tablename__ is set explicitly, 
that will be used instead.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Quick way to deep copy an object?

[Jonathan Vanasco]

Look into `cascade`

Here's a related thread

https://groups.google.com/forum/#!searchin/sqlalchemy/cascade|sort:date/sqlalchemy/eIOkkXwJ-Ms/JLnpI2wJAAAJ

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: set next value of postgres sequence

[Jonathan Vanasco]

There is a `Sequence` object that can be delcared like a table

>From the docs:

http://docs.sqlalchemy.org/en/latest/core/defaults.html#defining-sequences

seq = Sequence('some_sequence')nextid = connection.execute(seq)

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Creating a derived class object without inserting into base class table

[Jonathan Vanasco]

On Wednesday, November 25, 2015 at 5:01:38 PM UTC-5, amit geron wrote:
 

> In Flask-SQLAlchemy there are default table names, and in this case they 
> would be just 'User', 'A' and 'B', which are already unique.
>


flask-sqlachemy doesn't overrwrite this feature of SqlAlchemy.  See this 
recipe:

http://flask.pocoo.org/docs/0.10/patterns/sqlalchemy/ 

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Best way to use SERIALIZABLE READ ONLY DEFERRABLE transaction with the ORM?

[Jonathan Vanasco]

On Tuesday, November 17, 2015 at 9:14:51 AM UTC-5, Michael Bayer wrote:
>
>
> conn = session.connection() 
> conn.detach() 
> conn.execute("set session isolation ") 
>
> < work with connection> 


I use this strategy, but without `detatch`.  I do this with SqlAlchemy in a 
webapp context, so I have a "setup" and cleanup hook.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: N-Triples to MySQL

[Jonathan Vanasco]

You probably need to ask this on an rdflib forum.

Looking at your code, you're just tossing a SqlAlchemy engine into rdflib. 
 You probably need to integrate some sort of plugin, such as 
https://github.com/RDFLib/rdflib-sqlalchemy

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Created edge-case bug with `contains_eager`, can't reproduce

[Jonathan Vanasco]

Ah ha!

I figured this out.  It was a mix of a lazy eye and some peculiarities 
between Postgres(live) and sqlite(test-case).

Given the query:

session.query(Foo).options(contains_eager('bar')).order_by(Foo.id.desc()).offset(0).limit(100).all()]

The SQL is (approx)
select foo.*, bar.* from foo, bar order by foo.id desc limit 100;

I didn't notice that there wasn't a `join` from foo to bar until I 
reformatted the SQL.

SqlAlchemy just drops in the 'bar' on the query.  

In such a case, shouldn't the library ideally:
  • raise some error
  or
  • not add in the "join"

?

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Created edge-case bug with `contains_eager`, can't reproduce

[Jonathan Vanasco]

On Friday, October 30, 2015 at 11:38:50 AM UTC-4, Michael Bayer wrote:

> you wouldn't typically want to specify contains_eager('bar') if the SQL 
> has no JOIN in it, which you'd do with query.join(). 


There's no valid reason for using the `contains_eager` here -- it's just a 
typo.  

I simply ended up in a scenario where I SqlAlchemy doesn't raise an error 
for an obviously wrong thing... and the behavior isn't entirely predictable 
-- so it's hard to write tests to safeguard against this.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Created edge-case bug with `contains_eager`, can't reproduce

[Jonathan Vanasco]

I've spent an hour trying to reproduce this bug and can't.   I'm hoping 
someone can suggest what might be going on so I can make this reproducable 
for a bug report

I have 2 classes:

class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
bar_id = Column(Integer, ForeignKey("bar.id"), nullable=True)

class Bar(Base):
__tablename__ = 'bar'
id = Column(Integer, primary_key=True)
is_x = Column(Boolean, nullable=True)

# monkeypatched relationship
Foo.bar = sqlalchemy.orm.relationship("Bar", 
primaryjoin=sqlalchemy.and_(Foo.bar_id == Bar.id, Bar.is_x.op("IS 
NOT")(True)))

The not-reproducable problem is the following:

If I pass in a `contains_eager` clause WITHOUT doing a join, I end up 
limited to all the Foo's with a Bar.


session.query(Foo).options(contains_eager('bar')).order_by(Foo.id.desc()).offset(0).limit(100).all()

However if I join, or do a joinedload, I correctly end up with all the Foos 
(their Bar status is irrelevant)

The generated SQL looks fine 00 it seems that there's some post-processing 
bit in sqlalchemy that is dropping this.

My production version has several dozen attributes and class functions. 
 Testing one-by-one isn't working well.

Does anyone have hints on a better way to troubleshoot this?

Also, shouldn't `contains_eager` raise some sort of error if you don't 
actually contain that object?







-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Created edge-case bug with `contains_eager`, can't reproduce

[Jonathan Vanasco]

On Thursday, October 29, 2015 at 7:33:47 PM UTC-4, Michael Bayer wrote:
>
> i dont have an immediate answer, though i wonder why you aren't using 
> ~Bar.is_x.is(True) for that "IS NOT"... 
>

Old habits and styleguide.  The `~` ended up being missed on too many 
glances and code-reviews.
 

> why would contains_eager("bar") do anything here if you aren't joining 
> out to Bar ? 
>
...

> mixing joinedload and contains_eager?  youd be using join() 
>

I meant to do an `options(joinedload('bar'))` and mistakingly 
got `options(contains_eager('bar'))`.  it was from a copy/paste error - 
grabbing the wrong line.

On my development code, joinedload or no-`options` nets a 26 item 
resultset; but putting in `contains_eager` drops it down to 3 items that 
have that property.

The difference is between these 2 forms:

[(f.id, f.bar_id) for f in 
session.query(Foo).options(joinedload('bar')).order_by(Foo.id.desc()).offset(0).limit(100).all()]
[(f.id, f.bar_id) for f in 
session.query(Foo).options(contains_eager('bar')).order_by(Foo.id.desc()).offset(0).limit(100).all()]

In my actual codebase, something drops the 26 items from `joinedload` to 
only contain the 3 that have a `bar` on `contains_eager`.

I'd love to figure out WTF is doing that, because it seems to be some bug 
or gotcha. The "fix" is simple -- use `joinedload` like I intended.


eager loading takes what it can find at result processing time but 
> doesn't assume anything is there.  it looks for the columns it expects.


but if the colums aren't there, shouldn't there be an error like 
"`contains_eager` called, but query does not contain any usable columns" ?

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: best way to query from a tuple of parameters

[Jonathan Vanasco]

Ah, sorry about that.  I (almost) always nest "or_" in "and_".  I don't 
think I've ever done the inverse!

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: best way to query from a tuple of parameters

[Jonathan Vanasco]

Are you looking for the `or_`

from sqlalchemy.sql import and_, or_

abcd = or_((table.col_1 == a, table.col_2 == b),
  (table.col_1 == b, table.col_2 == c),
  )
session.query(table).filter(**abcd).all()

that should generate something like 

   SELECT * FROM table WHERE (col_1 == a and col_2 == b) OR (col_1 == c and 
col_2 == d);

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: running parallel migrations using sharded/partioned/spaced queries?

[Jonathan Vanasco]

Crap. That's way simpler.  Thanks!

I could even use Postgres as the orchestrator -- just create a table of ids 
and loop through those based on migration status.  (new table would avoid 
the re-write on a full record from Postgres).

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] running parallel migrations using sharded/partioned/spaced queries?

[Jonathan Vanasco]

I have to run a script on 2MM objects to update the database.  Not really a 
schema migration, more like changing the internal data representation in 
the fields.

There's a bit of post-processing and bottlenecks involved, so doing 
everything one-at-a-time will take a few days.

I'd like to split this out into 5-10 'task runners' that are each 
responsible for a a section of the database (ie, every 5th record).  That 
should considerably drop the runtime.

I thought I had seen a recipe for this somewhere, but checked and couldn't 
find anything.  That leads me to question if this is a good idea or not. 
 Anyone have thoughts/pointers?

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] emulating psql's "\copy" function?

[Jonathan Vanasco]

As part of an archiving routine that uses SqlAlchemy, I need to execute 
some pretty specific commands using `\copy` to archive a selection of 
columns, in a special order, into a csv.

Doing some digging, psycopg2 provides an interface to `COPY` -- but that 
doesn't work for my needs.  
I'd rather not use a subprocess to handle run `\copy` in psql, because then 
it's not in the transaction.

I'll be running this to partition 10GB of data into a lot of 10-50MB 
chunks... so I'd like to avoid piping this through sqlalchemy.  i'm not 
opposed to pulling this in row-by-row, but I'd really rather avoid it.  I 
have almost 250MM rows right now, and the future "nightly" build will be 
doing about 1MM at a time.

Does anyone have a suggestion for a workaround?

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: getting the identity for objects not in a session

[Jonathan Vanasco]

I'll try to explain, hopefully this is the right answer.

My guess is that you are seeing the identity as a tuple of primary keys and 
that can be confusing. If so, that's because 
'orm.state.InstanceState.identity` just returns the primary key. SqlAlchemy 
actually tracks it the identity in a more complex way.

Once you call `session.flush()` after adding the record to one of your 
sessions(s), it is (hopefully) created in the db. A primary key is 
generated (if its a serial) or the provided key is entered.  Any backend DB 
logic (such as constraints) executes and SqlAlchemy gets a "OK!".  If the 
key is a serial, your driver will probably show sql calls that use the 
`RETURNING colname` syntax.  I think I saw other methods of 
getting/generating sequence ids, but I can't remember.

SqlAlchemy doesn't seem to track objects in the map until it talks to the 
db.  If you add an object to a session, it won't be in the identity map -- 
regardless of you specifying a primary key or expecting one to be 
autogenerated.  It's not until flush() is called (or implied via commit()) 
that `.identity` has a value.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Tips for schema based db traversal and building

[Jonathan Vanasco]

On Tuesday, September 8, 2015 at 9:00:12 PM UTC-4, Michael Bayer wrote:
>
> q = select([tab.c[name] for name in ["id", "name", "address"]])
>

adding...

If you want to load particular columns off a relationship, you even do that 
using the `load_only` method.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Handle multiple relations between two rows/objects

[Jonathan Vanasco]

Most ORMs, SqlAlchemy included, require a primary key.

This page in the documentation describes 
why: 
http://docs.sqlalchemy.org/en/latest/faq/ormconfiguration.html#how-do-i-map-a-table-that-has-no-primary-key

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] DB sync on application level

[Jonathan Vanasco]

I don't like this idea.

but...

You should familiarize yourself with two-phase commits.  sqlalchemy 
supports this on mysql and postgresql.  basically everyone votes to commit 
yay/nay in phase 1, then a commit is made (if unanimous yays) or rollback 
executed in phase 2.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] funky session usage to add join conditions and where clauses

[Jonathan Vanasco]

fwiw, I struggled with this a while back and then gave up.

i ended up writing a few filter__xyz() functions that accept/return a 
query.  in the def, I join needed tables and filter.  instead of inspecting 
the query for tables, I just pass in some flags on how to act.

It's not pretty, but it works reliably and was fast to implement.

my queries now look like:

q = session.query(A).filter(A.id>10)
q = filter__as_at(q)

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] how to dynamically work with an aliased table?

[Jonathan Vanasco]

It looks like I imported the `sqlalchemy.alias` instead of 
`sqlalchemy.orm.aliased`, and just typod when posting here.

switching to sqlalchemy.orm.aliased -- which I used in an identical manner 
7 other times in this file -- immediately fixed things.

thanks for setting me straight.  looks like I had really tired eyes.


-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] funky session usage to add join conditions and where clauses

[Jonathan Vanasco]

It's a code management style that we ended up on :(

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] how to dynamically work with an aliased table?

[Jonathan Vanasco]

On Thursday, September 24, 2015 at 3:05:56 AM UTC-4, David Allouche wrote:

> That looks like the right approach. There is probably something else in 
> your actual code that is causing "it [to] not work". 
>
> To get a better understanding of "it did not work", I would look at the 
> "str(query)" before and after the stuff with the aliased table. 
>

The _aliased object (returned by `sqlalchemy.orm.alias()` does not have 
addressable columns.  touching _aliased.string_id and _aliased.c.string_id 
both raise errors.

The rest of the code works fine in production, I just can't seem to figure 
out how to add this table onto the query under a different name which can 
be queried against.  The closest thing I could do was to nest everything 
into subselects -- but the sql is grossly inefficient.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Auto filter a relationship?

[Jonathan Vanasco]

I hit send early.  That form should/will work for reading - i use it 
heavily.  I'm not sure how/if it will interact with the collections 
management system (backref/backpopulates, etc).  

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Auto filter a relationship?

[Jonathan Vanasco]

Your use of association_proxy seems odd to me.  That plugin is normally 
used to "cut out" the middleman when using a join with an association 
pattern and avoid a hop/remount a relationship from one object onto another.

With my understanding and use of that plugin, you're basically setting the 
following shortcut, and most other behaviors are a side-effect.

Reservation.packages == Reservation._packages_rel.package

Since ReservationPackage has a primary key on `id`, it should be able to 
handle non-unique combinations.

These 2 both jumps out to me as potential problems :

ReservationPackage-
   package - there is no backref or back-populates.
   package - should this have a uselist=False setting?  
   __init__ - this handles the package, but not the reservation ?

Reservation-
package_rel - no backref or back-populates

In my experience, the bulk of problems with 'collections' have happened 
because of incomplete or improper relationships.  I'd try setting up 
everything with fully declared relationships (ie,  back_populates) and see 
how that affects the design of your relations and how the code runs.

You can pretty much expect the collections to not work properly unless you 
have backref/back-populates declared.  That's the biggest issue and warning 
sign to me -- sqlalchemy only knows half of your relationship info.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Auto filter a relationship?

[Jonathan Vanasco]

packages = sa.orm.relationship("Package", 
primaryjoin="and_(ReservationPackage.package_id==Package.id, 
Package.deleted IS NOT NULL)")

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] how to dynamically work with an aliased table?

[Jonathan Vanasco]

I have a query where I derive an object based on some dynamic filters on a 
relationship:

sql_ors = [
sqlalchemy.func.lower(Bar.string_id) == id_search.lower(),
sqlalchemy.func.lower(Bar.numeric_id) == id_search.lower(),
]
query = dbSession.query(Foo)\
.join(Bar,
  Foo.bar_id == Bar.id
)\
.filter(
Foo.bash.op('IS')(True),
sqlalchemy.sql.expression.or_(*sql_ors),
)

This generally works fine.

Because of how the app stores data, I need to expand this query in the 
following way:

1. I need to join another instance of Bar onto the query
2. I need to filter against that instance of Bar

After reading the docs, I was hoping something like this would work -- it 
did not, but I'm sharing this to explain the actions I was trying to 
accomplish

_aliased = sqlalchemy.orm.alias(Bar, name='bar2')
sql_ors.extend([
sqlalchemy.func.lower(_aliased.string_id) == id_search.lower(),
sqlalchemy.func.lower(_aliased.numeric_id) == id_search.lower(),
])
query = query.join(_aliased, Foo.bar_id == _aliased.id)


A better query would handle this entire section with some CASE clauses, but 
I'm hoping to just patch in the right functionality for a bit without 
rewriting too much.

Anyone have a clue which ORM api elements I should be using?

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: How to get specific attribute from last row?

[Jonathan Vanasco]

You should share your SqlAlchemy model along with an executable script that 
illustrates the query and others can comment-on or fix.

You are simply showing a mixture of raw data and random results that are 
without any context.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] has anyone used/abused events to reflect db changes on cached orm objects?

[Jonathan Vanasco]

This is, admittedly, an abuse of SqlAlchemy.  I'm wondering if anyone else 
has dealt with this situation before and how they handled it.

We have a handful of situations where SqlAlchemy generates a raw sql update 
against a table.  Something like

 _table = model.Foo.__table__
 session.execute(_table.update().where(_table.c.id == 
foo_id).values(_table.c.count_a = (_table.c.count_a + 1)))

This is done because we may or may-not have the instance of Foo(foo_id) 
loaded, and it doesn't make sense to load if not already present.

What I'm wondering is this: 

has anyone in a similar situation looked at using events or other hooks to 
inspect the current session cache and update the objects if they have been 
loaded?  [as well as marking the attribute as clean -- since it already 
will have updated on the database]  

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] has anyone used/abused events to reflect db changes on cached orm objects?

[Jonathan Vanasco]

What haven't you thought of, Michael Bayer?  Is there anything SqlAlchemy 
can't do?!?!?

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Mixed data type for a column

[Jonathan Vanasco]

If I understand you correctly, I would just do this:

1. Use 3 different columns for the value -- each a native data type.
2. Use `coalesce` to handle sorting
3. When displaying in sqlalchemy, just use a property to proxy displaying 
the set column (and explicitly work with the correct columns otherwise)

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: expire_on_commit and relationships

[Jonathan Vanasco]

Immediately I notice that the attributes  `father`/`mother` have no 
relationship to the `Parent` object.  I think specifying 
backref/back_populates should solve your issue:   
http://docs.sqlalchemy.org/en/rel_1_0/orm/backref.html


Also, FYI

You can use `expire` on just the relationships 
(http://docs.sqlalchemy.org/en/rel_1_0/orm/session_api.html#sqlalchemy.orm.session.Session.expire)

session.expire(father, ['childs',])

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] migrating from backref to back_populates -- advice?

[Jonathan Vanasco]

I'm about to migrate about 70 relationships from backref to back_populates 
so the model reads cleaner/better documented..

Most are straightwordard, but a handful are more complex -- order_by or 
uselist are on the backref, or there may be a handful of args or 
primaryjoin on the relationship.

are there any gotchas I should look out for, or should I expect this be 
straightforward?

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] migrating from backref to back_populates -- advice?

[Jonathan Vanasco]

On Wednesday, September 16, 2015 at 4:14:35 PM UTC-4, Michael Bayer wrote:
>
> maybe you can run through all the mappings with a script and produce a 
> textfile indicating the settings for all the relationships.  then you can 
> verify that the back_populates code change produces the same thing.
>
>
 !  great idea!

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] most correct way to get the columns in an object?

[Jonathan Vanasco]

given the object `source`, these both work

cols = [c.key for c in list(source.__table__.columns)]
cols = [c.name for c in 
sqlalchemy.orm.class_mapper(source.__class__).mapped_table.c]

I'm sure there are other ways.

is there an ideal / canonical way of getting this data?

for this particular instance, my biggest concern is speed.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] most correct way to get the columns in an object?

[Jonathan Vanasco]

Thanks.

For this bit of code, I just need the column names.  We ran into an 
edge-case during some new tests where instead of having one of a 
read-through cache object (a dogpile managed dict!) a hot SqlAlchemy object 
got used.  This bit of code just cleans up and reformats some column data 
for the public API.  it's basically for a glorified JSON encoder.

There's some other - more complex - code that uses the inspect and class 
mapper but here I just need an efficient way of grabbing the table's 
column names.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] possible bug/docs deficiency on classes with multiple identical relationships

[Jonathan Vanasco]

This drove me crazy for an hour today, until I finally figured out what was 
going on.

I have a class with a few relationships:

class Foo(base):
  # relationships have a prefix that describe the relation l_ 
(list) or o_ (scalar)
  l_Bars = relationship("Bars")
  o_Biz = relationship("Biz", uselist=False)

I'm linking together a few APIs and needed to make the relationships 
accessible under another name:

class Foo(base):
  l_Bars = relationship("Bars")
 the_bars = l_Bars

When committing a session, SqlAlchemy raises an error and is incredibly 
unhappy.

Under 1.8 the error is:

  File "build/bdist.macosx-10.6-intel/egg/sqlalchemy/orm/state.py", line 
429, in _expire
[impl.key for impl in self.manager._scalar_loader_impls
  File "build/bdist.macosx-10.6-intel/egg/sqlalchemy/util/langhelpers.py", 
line 747, in __get__
obj.__dict__[self.__name__] = result = self.fget(obj)
  File 
"build/bdist.macosx-10.6-intel/egg/sqlalchemy/orm/instrumentation.py", line 
111, in _scalar_loader_impls
self.values() if attr.impl.accepts_scalar_loader])
AttributeError: 'NoneType' object has no attribute 'accepts_scalar_loader'

The error was different on earlier versions.  (I think I tested on .9.9 and 
1.0.6)

The .9.9 error is :
... intel.egg/sqlalchemy/orm/state.py", line 394, in _expireif 
impl.accepts_scalar_loader and \
AttributeError: 'NoneType' object has no attribute 'accepts_scalar_loader'

Once I figured out what caused it/ what is going on -- this makes perfect 
sense, and I could just use a class property to proxy the relationship 
under a different name.

However I think there may be a docs deficiency or bug involved:

1. I couldn't find anything in the docs that said "Don't do this!" in terms 
of duplicating a class relationship
2. This seems to be an uncaught error/edge-case.
- `attr.impl` is None , yet was allowed to progress this far into the 
orm logic without getting caught.
- I'm not sure if anything could have been done to alert me "YOU ARE 
DOING SOMETHING WRONG/STUPID" in this error, but I'm actually amazed I was 
able to pinpoint this error during a test-run.  Based on the error message, 
a dozen other things from the checkin should/could have caused this.

And just to be clear -- by "bug" I mean that SqlAlchemy doesn't seem to be 
correctly catching or reporting this User Error.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] possible bug/docs deficiency on classes with multiple identical relationships

[Jonathan Vanasco]

On Wednesday, September 16, 2015 at 6:56:20 PM UTC-4, Michael Bayer wrote:
>
> OK, what's great here is (in keeping with bugs are always reported in 
> twos, no matter how long theyve been around) that someone just hit this 
> yesterday: 
>

Ha!  I missed reading that one!

this should definitely be caught under #2.should be easy. I've 
> worked this out in 
>
> https://bitbucket.org/zzzeek/sqlalchemy/issues/3532/detect-property-being-assigned-to-more.
>  
>


Nice.  That's the exact use-case.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] What about bit fields / bit masks?

[Jonathan Vanasco]

postgres wise:

I use an INT field to store bitwise data.  I wrote a class that declares 
enumerated sets and manages the the bitwise operations, then serializes it 
to an int.  I use an @property to deserialize reads and manually serialize 
the writes (not enough time/need to integrate with SqlAlchemy deeper)

I haven't had to search on these fields, and likely won't ever need to.  My 
understanding though is that:
1. the planner would perform bitwise operations on an index if the column 
is indexed
2. you can create partial/function indexes on the bits, and I believe there 
are some plugins in this area, including a fork of postgis

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Is it possible to set the constraint unique = True except for empty string into SQLAlchemy?

[Jonathan Vanasco]

If you're using Postgres, you actually wouldn't even need to use a partial 
index if you can save NULL values instead of empty strings -- a unique 
constraint would work as intended because Postgres does not compare NULL 
values to one another.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Does twophase=True limit to only two databases at the same time?

[Jonathan Vanasco]

twophase deals with the transaction commit protocol , and is unlreated to 
anything else in your example. 
 
(http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.params.twophase)

You'd simply create an engine3 and bind whatever object classes to it.

FWIW.  Sqlite does not support two-phase commits.   Fore more info on 
two-phase commits - https://en.wikipedia.org/wiki/Two-phase_commit_protocol


-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Does twophase=True limit to only two databases at the same time?

[Jonathan Vanasco]

On Friday, August 14, 2015 at 5:16:48 PM UTC-4, Jinghui Niu wrote:


 If I still want to use SQLite, and I still need to do vertical partition, 
 what can I do? Am I out of luck?


You can, but not with a two-phase commit. 

Two-phase commit basically works like this:

- round 1, everyone locks-state and votes COMMIT! or N!
- round 2, if commit in round 1 was unanimous, it commits. otherwise 
everyone is told to roll back.

Since SQLlite doesn't support that, you'd need to write that logic in at 
the application level.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Does twophase=True limit to only two databases at the same time?

[Jonathan Vanasco]

Well, this problem doesn't really have anything to do with SqlAlchemy -- 
you should probably ask people for advice on the Sqlite lists or Stack 
Overflow.

You can segment out your database into 3 files using the example above. 
 You will just run into an issue where -- because there isn't a 
two-phase-commit available in Sqlite, you will need to decide how to handle 
situations like (but not limited to):

- the first and second databases committed, but the third database raised 
an error (you need to undo in the application)
- the first and second databases committed, but your application was quit 
before the third database could commit (you need to undo from another 
application)

You will have to decide how to handle that at the application and database 
levels, and then SqlAlchemy can be used to implement that strategy. 

I just want to be clear -- your concern right now is on the best way to use 
Sqlite to solve your problem -- not use Sqlalchemy.  Once you figure that 
out, people here can be more helpful.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] advice sought - handling a relationship cascade

[Jonathan Vanasco]

On Wednesday, August 5, 2015 at 10:08:35 PM UTC-4, Michael Bayer wrote:

 viewonly=True?


That was the EXACTLY what I needed.  Thank you! 

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] advice sought - handling a relationship cascade

[Jonathan Vanasco]

I have 2 related classes

• ProvisionedAccess (auth policy)
• Credentials (public/private key)

ProvisionedAccess has 2 relationships:

• keyset_active -- the active keyset, identified by having an is_active 
flag
• keys_all -- all historical keys

I've implemented them as below.

the problem is when generating a new keyset.  i do the following (in order)

* deactivate existing Credentials (is_active=False)
* flush
* generate new Credentials
* flush

everything is fine, until i try to stash the relationship for some 
operations
* access.keyset_active = new_Credentials
* flush

this flush removes the `provisioned_access_id` from the old Credentials, 
which causes an error.

I'm not sure the best way to proceed.  I need to figure out a way in which:

a- i don't generate SQL when updating that relationship. i only use it as a 
utility for selects and passing around objects.
b- i let the ORM do everything


any suggestions on either approach would be appreciated.  i know nothing 
about cascading relationships in the ORM.


--

keyset_active = sa.orm.relationship(Credentials, 
primaryjoin=and_(ProvisionedAccess.id==Credentials.provisioned_access_id, 
Credentials.is_active==True),
uselist=False
)

keys_all = sa.orm.relationship(Credentials, 
  primaryjoin=ProvisionedAccess.id==Credentials.provisioned_access_id,
  order_by=Credentials.id.desc()
  )

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Why is there no SQLA-List class?

[Jonathan Vanasco]

On Tuesday, August 4, 2015 at 11:24:06 AM UTC-4, Michael Bayer wrote:
 

 it's a recipe.  Please tailor it to your needs.


Michael's recipe is rather advantageous though.  This encodes a `null` JSON 
string into a Python None, which corresponds to a NULL database field. 
 Encoding as the json-valid string null would require a lot of additional 
SqlAlchemy configurations to persist into the database as a `NULL` value 
instead of the string `'null'`. Most situations would want to deal with 
NULL/None instead of null.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Bizzarre insert behavior: NULL constraint violation with non-null value, column changes every run.

[Jonathan Vanasco]

Have you tried toggling the sqlalchemy connection string?

http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html#sqlalchemy.create_engine.params.case_sensitive

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Bizzarre insert behavior: NULL constraint violation with non-null value, column changes every run.

[Jonathan Vanasco]

Also, check this:

http://docs.sqlalchemy.org/en/rel_1_0/dialects/oracle.html#identifier-casing

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Bizzarre insert behavior: NULL constraint violation with non-null value, column changes every run.

[Jonathan Vanasco]

Glad it worked out!  Sorry my suggestions didn't pan out -- 90% of oracle 
issues i've had have come down to some default case setting in a file I 
didn't know about.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: set vs after_flush events

[Jonathan Vanasco]

On Friday, July 24, 2015 at 1:20:15 PM UTC-4, Richard Kuesters wrote:

  well, as a general non-specific view yes, it can be another approach. 
 but, for the piece of code that drove me to this question, i really need to 
 use after_flush  :)



Well I mean... you could use that pattern to catch and annotate the object 
with I've changed! info, then do your cleanup in the after_flush.

What popped into my mind as another use-case is this: touching an 
object's property to mark it dirty (even if SqlAlchemy doesn't interpret 
it as such, because the value is the same), then if it's not updated in the 
flush event, send the update anyways -- so a db-side stored procedure runs.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: set vs after_flush events

[Jonathan Vanasco]

Couldn't you handle much of this with the Descriptors/Hybrids pattern?

http://docs.sqlalchemy.org/en/latest/orm/mapped_attributes.html#using-descriptors-and-hybrids

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: performance of SA

[Jonathan Vanasco]

Are you comparing the speed of SqlAlchemy Core operations or SqlAlchemy ORM 
operations?

The ORM is considerably slower.  The core engine is much faster.

There is also this: 
 http://docs.sqlalchemy.org/en/latest/faq/performance.html

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Syntax Checking

[Jonathan Vanasco]

flake8 is super simple - it checks your code for mistakes (undeclared vars, 
non-runnable code, etc) and pushes you to write pep8 style code.  

the only things you need to do really are:
* write a .cfg for various projects, so you can turn off some warnings
* get in the habit of running it before checkins and ALWAYS before 
merge/deploy.

In terms of unit tests, SqlAlchemy implements a lot -- as do other packages 
you use.  Take a look at their source repos -- it's the easiest way to 
learn.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Syntax Checking

[Jonathan Vanasco]

In terms of linters, `flake8` (https://pypi.python.org/pypi/flake8) catches 
most mistakes I've made with SqlAlchemy.

It's also useful to start writing Unit Tests that will interact with your 
SqlAlchemy models in predicted ways -- in addition to continually checking 
core functionality.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: set vs after_flush events

[Jonathan Vanasco]

On Friday, July 24, 2015 at 2:06:15 PM UTC-4, Richard Kuesters wrote:

  well, application-wise it is really to run other procedures, not from the 
 database or python side, but from a message broker that's expecting 
 anything to happen to that value -- even if it's just a touch :)

 err ... it's quite a specific architecture for dumb clients, so i'm just 
 taking some extra security measures ;)


It's not really that dump of an architecture.  I picked up on the 
value/importance of a simple touch.

Just throwing out some more ideas...

We have a caching system in place for public data for a pyramid app using 
SqlAlchemy and Dogpile(redis).  When objects are fetched form the cache, a 
`postcache` hook is performed and... if the object requires a lot of 
processing... it can register the object and an action into a global pool. 
 We then use an event in Pyramid to pop and process everything in the pool.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] marking an object read-only / recursive expunge?

[Jonathan Vanasco]

So my code above is just completely wrong.  

This code actually does what one expects:

def recursive_expunge(obj, dbSession):
def _recursive_expunge(_obj):
_instance_state = sqlalchemy.inspection.inspect(_obj)
_mapper = _instance_state.mapper
try:
dbSession.expunge(_obj)
# print expunge | %s % _obj
except sqlalchemy.orm.exc.UnmappedInstanceError:
# print sqlalchemy.orm.exc.UnmappedInstanceError | %s % 
_obj
pass
except sqlalchemy.exc.InvalidRequestError:
# print sqlalchemy.exc.UnmappedInstanceError | %s % _obj
pass
if _mapper:
# _unloaded = [(_name, _rel) for (_name, _rel) in 
_mapper.relationships.items() if _name in _instance_state.unloaded]
_loaded_rels = [i for i in _mapper.relationships.items() if 
i[0] not in _instance_state.unloaded]
for (_name, _rel) in _loaded_rels:
_loaded_rel_data = getattr(_obj, _name)
if _loaded_rel_data:
if not _rel.uselist:
_recursive_expunge(_loaded_rel_data)
else:
for _i in _loaded_rel_data:
_recursive_expunge(_i)
_recursive_expunge(obj)

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] question about `sqlalchemy.orm.mapper.Mapper` and objects

[Jonathan Vanasco]

Yeah, no error.

I'll guess that:

* My code isn't doing what I intended
* but
*SqlAlchemy isn't raising an error

So I can work with that. 

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] question about `sqlalchemy.orm.mapper.Mapper` and objects

[Jonathan Vanasco]

This is an extension of my question about recursive object expunging 
(https://groups.google.com/forum/#!topic/sqlalchemy/lUhCDfkPc9k)

Is the mapper that is accessed via 
`sqlalchemy.inspection.inspect(obj).mapper` and stored in 
`object.__mapper__` specific to the instance?

I thought it was the global mapper, but looking at how it behaves as I use 
it... it seems local to the object.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] marking an object read-only / recursive expunge?

[Jonathan Vanasco]

On Friday, July 17, 2015 at 2:46:42 PM UTC-4, Michael Bayer wrote:

  
 well then yeah you need to do your own thing :)


i foolishly thought this was something others may have experienced ;)
 

 i'd use inspect(obj) to get the mapper.but also you might want to use 
 cascade_iterator: 
 http://docs.sqlalchemy.org/en/rel_1_0/orm/mapping_api.html?highlight=cascade_iterator#sqlalchemy.orm.mapper.Mapper.cascade_iterator

 
i'll look into the cascade_iterator.  I keep forgetting that inspect is not 
just for debugging.
 

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] marking an object read-only / recursive expunge?

[Jonathan Vanasco]

I'm updating our visual preview tool for edits, and ran into an issue.  

In order to best mimic the production view, I can't simply clone the 
objects any longer (they have way too many attributes and relationships) 
and must apply edits onto the real object.  

I'd like to ensure that changes can't persist, so the easiest way to mark 
the object read only was to expunge it from the session.  Perfect.

The problem I've run into, is that `expunge()` only applies to the actual 
object -- it doesn't apply to any of the loaded (lazy/eager/etc) 
relationships.  

Has anyone figured out way to do a recursive expunge?  I don't want to 
alter the cascades on the relationship, I just have one (or more) 
context(s) where I need to disassociate an object and any loaded children 
from the session.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] marking an object read-only / recursive expunge?

[Jonathan Vanasco]

editing the cascade isn't an option.  

for now this seems to work, though it's ugly.

def recursive_expunge(obj, dbSession):
def _recursive_expunge(_obj):
if hasattr(_obj, '__mapper__'):
for rel in obj.__mapper__.relationships:
try:
dbSession.expunge(rel)
except sqlalchemy.orm.exc.UnmappedInstanceError:
pass
_recursive_expunge(obj)
recursive_expunge(postingObject, self.request.dbSession.writer)

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: How to jsonify Query result?

[Jonathan Vanasco]

The tuples are a variant of python's NamedTuple 
(sqlalchemy.utils._collections.KeyedTuple) and contain the column names in 
a `keys` method.  

You can just write a json adapter that handles instances of KeyedTuple and 
iterates over their K/V pairs.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Trying to join a session query to a union

[Jonathan Vanasco]

you probably need to modify the various objections with a `.select()` or 
`.subquery()`

e.g:
  query_select = query.select()
  q = intersect.join(query_select, query_select.c.sid, intersect.c.sid)

pay attention to the docs on what the various methods return.  some return 
a selectable, others don't.  you can usually toggle around the different 
forms with .select, .subquery, .alias (and there are a few others)

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] extreme/aggressive query simplification with load_only

[Jonathan Vanasco]

wondering- has anyone tried ways to mimic the load_only feature but not 
fetch the primary key (which is autoloaded, even if not specified)?

I know that the orm needs this to make load-only work as intended, which is 
why I used the work 'mimic'.

trying to get some data-transfer down, and some sections of our base have a 
lot of querys + rows from tables with composite primary keys -- so we end 
up pulling 3x-4x as much data as we needed.   This *could* be easily 
rewritten as pure-sql, but then we lose a bunch of the automagic syntax.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: sqlaclhemy ORM don't update jsonb[postgres-psycopg2]

[Jonathan Vanasco]

This sounds like you may be having issues with mutation tracking on edits.
 


 http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html?highlight=jsonb#sqlalchemy.dialects.postgresql.JSONB


The JSON 
http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html?highlight=jsonb#sqlalchemy.dialects.postgresql.JSON
 type, 
when used with the SQLAlchemy ORM, does not detect in-place mutations to 
the structure. In order to detect these, the sqlalchemy.ext.mutable 
http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/mutable.html#module-sqlalchemy.ext.mutable
 extension 
must be used. This extension will allow “in-place” changes to the 
datastructure to produce events which will be detected by the unit of work. 
See the example at HSTORE 
http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html?highlight=jsonb#sqlalchemy.dialects.postgresql.HSTORE
 for 
a simple example involving a dictionary.

Mutation Tracking
* 
http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/mutable.html#module-sqlalchemy.ext.mutable

HSTORE example
* 
http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html?highlight=jsonb#sqlalchemy.dialects.postgresql.HSTORE
 

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: how to recycle connections grown stale after checkout

[Jonathan Vanasco]

http://docs.sqlalchemy.org/en/latest/core/pooling.html#dealing-with-disconnects

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: SQLAlchemy 1.0.6 Released

[Jonathan Vanasco]

Michael,

I had no idea about this versioning.

Looking it up, I first found this in the docs: 
 http://docs.sqlalchemy.org/en/latest/orm/examples.html#versioning-objects

That didn't look right, so I checked the changelogs and saw some specifics 
in the 09 release note.

Looking up those terms, I found this relevant docs: 
 
http://docs.sqlalchemy.org/en/latest/orm/versioning.html#server-side-version-counter

Should the orm/examples file have a link to the orm/versioning file?


-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: SQLAlchemy 1.0.6 Released

[Jonathan Vanasco]

I guess they are related.  I was just thinking about a link on each page 
that says For another type of versioning within SqlAlchemy, click here.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Query hangs after a while

[Jonathan Vanasco]

Are you positive there isn't some transactional locking going on?

What does mysql say about locked tables/rows when things are stalled?

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: What is the SQLAlchemy terminology for ..?

[Jonathan Vanasco]

Your question and example are both a bit confusing.  I'll try to impart 
some knowledge that can help you re-focus an ask.

1. In your example, `Session.query(CaseTestInstance)` will return all the 
data in the `cases_test_instance` table.
2. Recursive Queries can mean a few things (and is often used with 
Contextual Table Expressions or functions that use the `with recursive` 
operator), and are usually a bit of an advanced topic.  I think you mean 
something else.
3. It sounds like you want to load associated or related data to the 
foreign keys.  In your example, I you would need to create classes that 
reflect the tables 'cases_test`, `auth_user` and `slaves_groups`.  In 
SqlAlchemy to get this data you must define the classes/tables and then set 
a `relationship` (and optionally a backref, so the relationship is 
accessible from both objects).  You can then automatically load the 
relationship with one of the many techniques, or use a table join.

The ForeignKey merely states that the column in your table should/must 
correspond to the column in the other table.  It implies that there is a 
relationship, but you must explicitly declare the relationship in your 
code.  a column like `slave_group_id` would usually be paired with a 
relationship called `slave_group`.  

advanced db designs might have multiple declared relationships that are 
built off the ForeignKey used in conjunction with multiple modifiers.


-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: .any() queries and MySQL performance

[Jonathan Vanasco]

FWIW, the first thing I noticed is that this will rarely work well, as the 
index is on a 255length column.

if your table is big, you would definitely see a large improvement by 
storing a 32char md5 version of that column, creating a partial index on 
the first 5-8 characters, and then adding a filter to match the partial 
index too.  

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Session is commiting without an add()

[Jonathan Vanasco]

You're experiencing exactly what Michael pointed you to.  

Person has a relationship+backref to SourceDatabase though 
`Person.source_database`.  When you create a `John` and assign the existing 
source_database, sqlalchemy is implicitly adding the Person so that the 
update you explicitly defined will persist on a commit.  In order to 
disable this, you need to read the section on Cascades that he pointed you 
to.  Specifically look at the section on backrefs.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: How to find out if an object is a SQLAlchemy mapped one?

[Jonathan Vanasco]

You should be able to handle this by inspecting the object.

http://docs.sqlalchemy.org/en/latest/core/inspection.html 

It will raise `sqlalchemy.exc.NoInspectionAvailable` if you can't inspect.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: ...and MVC

[Jonathan Vanasco]

On Sunday, May 31, 2015 at 4:48:09 AM UTC-4, Fayaz Yusuf Khan wrote:

 I do the former in cases which involve very long transactions. (Batch 
 uploads and processing. I'm counting batch and background tasks as part of 
 the controller layer here.)

 
I manage transactions manually in the controllers too.

I used multiple transactions in the controller a for a while for long 
transactions, but then decided to offload all that stuff onto another 
process (via Celery).

Now I only use multiple transactions for batch processing where not every 
component involved in the transaction supports savepoints.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] how to return an array of dicts

[Jonathan Vanasco]

All my models inherit from an additional base class with this method:

def columns_as_dict(self):
return a dict of the columns; does not handle relationships
return dict((col.name, getattr(self, col.name)) for col in 
sqlalchemy_orm.class_mapper(self.__class__).mapped_table.c)

so when returning a JSON array, I just do something like;

 return [i.columns_as_dict() for i in results]

I prefer this for several reasons:

- I don't think (anymore) that sqlalchemy should return raw data. I'm not 
fine with it's internal constructs after departing from the recommended 
usage a few times and finding myself creating more problems than I solved.

- I easily can override columns_as_dict() on classes to define only those 
columns that I want returned.

- IIRC, The result_proxy/row_proxy aren't always fetched from the database, 
there could still be data on the connection - or you could be on an 
unloaded lazy-loaded relation.  Running a list comprehension lets me slurp 
all that data, and close up the DB resources sooner.  This has made 
pinpointing bugs a lot easier than having unloaded data accessed in a 
template (which often produces hard-to-figure out tracebacks as the db is 
nestled in the template, which is nestled in your app).

There are probably a dozen better reasons for why I prefer this method , 
these just popped up in my head.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Session.close() implicite

[Jonathan Vanasco]

Michael- FYI, memoized_property isn't documented.  it looks to be in the 
active source, and there is a recipe for a roll-your-own memoized orm 
properties.


On Monday, May 25, 2015 at 11:43:58 AM UTC-4, c.b...@posteo.jp wrote:

 I am quite new to Python and not familiar with the decorator concept. 
 What do you mean with memoized thing? And why could the decorators 
 help me here? 


SqlAlchemy and Pyramid both offer decorators that memoize/cache the results 
of a property call.  They do all the case-logic your example uses.

An example would be using a memoizing decorator like this:

  @memoized
def session(self): 
return self.CreateSession() 

which would only execute the first time, and use a memoized/cached value on 
subsequent calls.

The different frameworks that offer memoizing decorators implement them 
differently.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: ...and MVC

[Jonathan Vanasco]

In most implementations, the answer would be: None of the above.

Many MVC style frameworks (including all the near variants) will place the 
SqlAlchemy session management in a WSGI middleware layer or a tween. 
 Usually it will look like (pseudocode):

try:
 sqlalchemy_session_setup(request)
 handle_request(request)
 finally:
 sqlalchemy_session_cleanup(request)

The Transaction and Session are bound to the lifecycle of the request, and 
often implemented with a two-phase commit to ensure other application 
components commit.

It is incredibly non-standard to have more than one transaction within a 
given request.  It is also relatively non-standard to explicitly manage 
transactions in the application code.

I do both of those (the former in very few circumstances) but am in a tiny 
minority of users.

Dealing with multiple sessions (aside from separate read/write connections) 
is very rare.

If you decide to handle session state yourself, you will need to address 
the intricacies of lazy-loading and collections.  If a collection/attribute 
is not eager loaded, accessing it in a template will trigger a database 
query. If you have already closed the connection or session, sqlalchemy 
will reconnect and reload.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: restrict child count?

[Jonathan Vanasco]

I think I might understand you...

You have a BOX, which could be a variable amount of sizes, and each size 
can hold a variable amount of items.

You want to create a rule(s) that will ensure you do not have too many 
things in each box.

If that is that case:

1. You could use Triggers in PostgreSQL to ensure that you are within the 
correct dimensions on insert and update.
2. You might be able use events in sqlalchemy to inspect the collection and 
figure out if the number of items is allowablw

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] flake8 tips?

[Jonathan Vanasco]

I just found a bug in my code that is related to this, and wanted to circle 
back...

I keep forgetting that these are not the same comparison on Postgres:

Foo != True 
Foo IS NOT True


They match as follows:
`!= True` = [False, ]
`IS NOT True` = [False, NULL]



-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: Authoring Dialects: How to Fix AssertionError: assert Decimal('15.7563') in [15.7563] ?

[Jonathan Vanasco]

Can you share the test and any relevant code (ie, anything in the dialect 
that deals with this)?

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] python customer function

[Jonathan Vanasco]

Would you be able to use a TypeDecorator? 
 
http://docs.sqlalchemy.org/en/latest/core/custom_types.html#sqlalchemy.types.TypeDecorator

That will allow you to define a function for handling how sqlalchemy 
inserts and accesses the data from sql.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: contains_eager limited by .first()?

[Jonathan Vanasco]

The problem is a behavioral quirk.  I'm not sure if this is a regression or 
not, but I've seen it brought up recently...

IIRC: 

The join creates a matrix result (many rows), but `first()` only pulls the 
first item from the result.

Calling 'one()' will pull all the results, but raise an error if the 
results are 0 or greater than 1.  (ensures one and only one result).  

I'd speculate that ensuring the result for `one()` doesn't apply to the 
number of rows that concern child objects – but I could be wrong.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.