from:"Mark"

I can't seem to find a way to do this without passing raw SQL to .filter()

I could just do:

.filter(column == func.substring('string', 1, func.char_length(column)))

but is it possible to do it with LIKE?

I.e. I need to return all rows that match the beginning of a string, so for 
'string' I could match 's', 'st', 'str', etc.

Thanks,

Mark.


-- 
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 create a filter 'string' LIKE column + '%'?

Hi, thanks but I'm not talking about calling like on a column - I need to
call like on a string literal. I tried doing text('string').like() but that
doesn't work either.

On Thursday, 10 April 2014 11:31:23 UTC+1, Gunnlaugur Briem wrote:

Hi,

See ColumnElement docs:

http://docs.sqlalchemy.org/en/rel_0_9/core/sqlelement.html#sqlalchemy.sql.expression.ColumnElement

... for your specific example you can call .like(...) on column clauses:

print Column('foo', Text).like('bar%baz')
foo LIKE :foo_1

More generally, if you wanted some operator other than LIKE, existing in
your DB dialect but not yet in SQLAlchemy, then you can use .op(...):

print Column('foo', Text).op('FNORD')('foo%')
foo FNORD :foo_1

Cheers,

Gulli

On Thu, Apr 10, 2014 at 12:19 PM, Mark Bird mark@gmail.comjavascript:
wrote:

I can't seem to find a way to do this without passing raw SQL to .filter()

I could just do:

.filter(column == func.substring('string', 1, func.char_length(column)))

but is it possible to do it with LIKE?

I.e. I need to return all rows that match the beginning of a string, so
for 'string' I could match 's', 'st', 'str', etc.

Thanks,

Mark.

--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.comjavascript:
.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
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 create a filter 'string' LIKE column + '%'?

I'm sorry, forget my last response, I see what you mean now.

Thanks a lot!

On Thursday, 10 April 2014 11:31:23 UTC+1, Gunnlaugur Briem wrote:

Hi,

See ColumnElement docs:

http://docs.sqlalchemy.org/en/rel_0_9/core/sqlelement.html#sqlalchemy.sql.expression.ColumnElement

... for your specific example you can call .like(...) on column clauses:

print Column('foo', Text).like('bar%baz')
foo LIKE :foo_1

More generally, if you wanted some operator other than LIKE, existing in
your DB dialect but not yet in SQLAlchemy, then you can use .op(...):

print Column('foo', Text).op('FNORD')('foo%')
foo FNORD :foo_1

Cheers,

Gulli

On Thu, Apr 10, 2014 at 12:19 PM, Mark Bird mark@gmail.comjavascript:
wrote:

I can't seem to find a way to do this without passing raw SQL to .filter()

I could just do:

.filter(column == func.substring('string', 1, func.char_length(column)))

but is it possible to do it with LIKE?

I.e. I need to return all rows that match the beginning of a string, so
for 'string' I could match 's', 'st', 'str', etc.

Thanks,

Mark.

--
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 create a filter 'string' LIKE column + '%'?

That's perfect. Thanks!

On Thursday, 10 April 2014 11:38:24 UTC+1, Simon King wrote:

You can also use sqlalchemy.literal, which returns an object that you
can treat like a column:

sqlalchemy.literal('string').like('whatever')

You may also be interested in the 'startswith' shortcut, which calls
.like under the hood

sqlalchemy.literal('string').startswith(yourcolumn)

Simon

On Thu, Apr 10, 2014 at 11:35 AM, Mark Bird mark@gmail.comjavascript:
wrote:
I'm sorry, forget my last response, I see what you mean now.

Thanks a lot!

On Thursday, 10 April 2014 11:31:23 UTC+1, Gunnlaugur Briem wrote:

Hi,

See ColumnElement docs:

http://docs.sqlalchemy.org/en/rel_0_9/core/sqlelement.html#sqlalchemy.sql.expression.ColumnElement

... for your specific example you can call .like(...) on column
clauses:

print Column('foo', Text).like('bar%baz')
foo LIKE :foo_1

More generally, if you wanted some operator other than LIKE, existing
in
your DB dialect but not yet in SQLAlchemy, then you can use .op(...):

print Column('foo', Text).op('FNORD')('foo%')
foo FNORD :foo_1

Cheers,

Gulli

On Thu, Apr 10, 2014 at 12:19 PM, Mark Bird mark@gmail.com
wrote:

I can't seem to find a way to do this without passing raw SQL to
.filter()

I could just do:

.filter(column == func.substring('string', 1,
func.char_length(column)))

but is it possible to do it with LIKE?

I.e. I need to return all rows that match the beginning of a string,
so
for 'string' I could match 's', 'st', 'str', etc.

Thanks,

Mark.

Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google
Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send
an
email to sqlalchemy+...@googlegroups.com javascript:.
To post to this group, send email to
sqlal...@googlegroups.comjavascript:.

Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
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] Using flask-sqlalchemy BaseQuery and Pagination with multiple tables.

2014-01-08 Thread Mark S

Hi 

I can successfully use pagination with the following - 

mydata=Article.query.filter(Article.author_id==User.id).filter(User.id==g.user.id).paginate(page,
 
POSTS_PER_PAGE, False)

However, I need to fetch columns from multiple tables. In that case how can 
I modify the code above in order to use pagination? 

Here is what I need to do - 

mydata = db.session.query(id,title,Author).from_statement(\
 SELECT 
a.id,a.title,u.author \
 FROM article a, user u\
 where a.user_id=u.id \
 and u.id=:userid)\

 .params(userid=g.user.id).all()

However, with this , pagination does not work and I get an error 
- AttributeError: 'Query' object has no attribute 'paginate'


Can you please help?




-- 
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/groups/opt_out.

[sqlalchemy] Possible bug with Postgres and ILIKE operator?

2014-01-03 Thread Mark Bird

I am using SQLAlchemy 0.8.1 with a Postgres backend, and have noticed that 
the ilike operator does not seem to be compiling to an ILIKE operator in 
raw SQL, but instead it is doing lower() on both sides of the comparison:

E.g. (names changed to protect the innocent)

 s = session()

 print s.connection().engine
Engine(postgresql://user:passwd@server:port/database)

 q = s.query(Table).filter(Table.column.ilike(FISH%))

 print q
SELECT column
FROM table
WHERE lower(column) LIKE lower(:column_1)

This could be a bug with what is printing out the SQL? I looked at the 
source code and the Postgres dialect is supposed to use ILIKE here. Any 
idea why this is not happening?

-- 
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/groups/opt_out.

Re: [sqlalchemy] Possible bug with Postgres and ILIKE operator?

2014-01-03 Thread Mark Bird

Thanks, that's brilliant. Very much obliged for the fast response. I can
now resolve an issue as nothing to be done, which is always nice :)


On 3 January 2014 18:00, Michael Bayer mike...@zzzcomputing.com wrote:


 On Jan 3, 2014, at 11:53 AM, Mark Bird mark.a.b...@gmail.com wrote:

 I am using SQLAlchemy 0.8.1 with a Postgres backend, and have noticed that
 the ilike operator does not seem to be compiling to an ILIKE operator in
 raw SQL, but instead it is doing lower() on both sides of the comparison:

 E.g. (names changed to protect the innocent)

  s = session()

  print s.connection().engine
 Engine(postgresql://user:passwd@server:port/database)

  q = s.query(Table).filter(Table.column.ilike(FISH%))

  print q
 SELECT column
 FROM table
 WHERE lower(column) LIKE lower(:column_1)

 This could be a bug with what is printing out the SQL? I looked at the
 source code and the Postgres dialect is supposed to use ILIKE here. Any
 idea why this is not happening?


 the select() construct produced by the Query is not bound to the engine’s
 session, so it has no dialect and thus calling str() will use the
 DefaultDialect.  Compiling it with the Postgresql dialect produces the
 desired result, which is the same as that which would occur if you executed
 the statement against the postgresql-bound engine.

  from sqlalchemy import create_engine
  from sqlalchemy import Table, Column, MetaData, String
  t1 = Table('t1', MetaData(), Column('data', String))
  engine = create_engine(postgresql://scott:tiger@localhost/test,
 echo=True)
  from sqlalchemy.orm import Session
  q = Session().query(t1).filter(t1.c.data.ilike('HELLO%'))
  print q
 SELECT t1.data AS t1_data
 FROM t1
 WHERE lower(t1.data) LIKE lower(:data_1)
  print q.statement.compile(engine)
 SELECT t1.data
 FROM t1
 WHERE t1.data ILIKE %(data_1)s







 --
 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/groups/opt_out.




-- 
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/groups/opt_out.

[sqlalchemy] Is it possible to obtain actual values inserted into database in a SessionExtension?

2013-11-13 Thread Mark Bird

I am trying to write a generic auditing extension, but I have some 
inconsistencies I'd like to iron out if possible.

The problem I have is that when an object is updated via a relationship 
rather than a Column attribute, the value returned from 
attributes.get_history is a SQLAlchemy object, and not the value that is in 
the database. This is more pronounced on a many-to-many relationship - the 
secondary table isn't marked as new/dirty, even though that is the table 
that will actually have data inserted into it. Instead the 2 tables joined 
by the secondary table are marked as dirty, although no data is changed in 
those tables. I understand this makes sense at the SQLAlchemy level - but 
is there a way to get the actual database changes? 

A code example to illustrate this:

from sqlalchemy.types import Integer
from sqlalchemy import Column, ForeignKey, create_engine
from sqlalchemy.orm import relationship, backref, attributes, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.interfaces import SessionExtension

BASE = declarative_base()

ENGINE = create_engine(sqlite://)

class AuditListener(SessionExtension):
def after_flush(self, session, *args):
for attr in (new, dirty, deleted):
print attr
for obj in getattr(session, attr):
print %s % obj.__class__.__name__
for col in obj.__mapper__.iterate_properties:
added, unchanged, deleted = attributes.get_history(obj, 
col.key)
if added:
print %s: %s % (col.key, str(added))

SESSION = sessionmaker(
bind=ENGINE,
extension=(AuditListener())
)

class FooBar(BASE):
__tablename__ = 'foobar'
foo_id = Column('foo_id',
ForeignKey('foo.foo_id'),
primary_key=True)

bar_id = Column('bar_id',
ForeignKey('bar.bar_id'),
primary_key=True)

class Foo(BASE):
__tablename__ = 'foo'
foo_id = Column('foo_id',
Integer,
primary_key=True)

bars = relationship('Bar',
secondary=FooBar.__table__,
backref=backref('foos'))

class Bar(BASE):
__tablename__ = 'bar'
bar_id = Column('bar_id',
Integer,
primary_key=True)


def example():
BASE.metadata.create_all(ENGINE, checkfirst=False)

session = SESSION()
f = Foo(foo_id=1)
b1 = Bar(bar_id=1)
b2 = Bar(bar_id=2)
session.add(f)
session.add(b1)
session.add(b2)
session.commit()

# add relationship between f and b1  b2:
f.bars = [b1, b2]
session.commit()

if __name__ == '__main__':
example()

If you run this code you will see this output:

new
Bar
bar_id: [2]
Foo
foo_id: [1]
Bar
bar_id: [1]
dirty
deleted
new
dirty
Bar
Foo
bars: [__main__.Bar object at 0x27b4910, __main__.Bar object at 
0x277b050]
Bar
deleted

As you can see, the output when the many-to-many relationship is committed 
is to denote an update to the bars relationship of Foo. There is nothing 
indicating there are 2 new FooBar entries created.

Any ideas?

Thanks,

Mark.

-- 
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/groups/opt_out.

Re: [sqlalchemy] update using query - joint table inheritance

2013-08-10 Thread Mark Eastwood

Hi Michael,

Thankyou very much for your reply, which is exactly what I needed. I also 
saw the new ticket that was raised for documentation of this, thankyou 
zzzeek.

Mark

-- 
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/groups/opt_out.

[sqlalchemy] update using query - joint table inheritance

2013-08-09 Thread Mark Eastwood

Hi all,

I am getting some unexpected behaviour when trying to update selected rows 
from a query when using joint table inheritance. Using MySQL, a query that 
is filtered based on items in the parent table, does not honour this filter 
when updating items in the child table (all children are updated, not only 
those matching the filter). It is easier to describe in code than in words. 
The code is attached.

In an attempt to make it easier to run, I did try and make the script use 
sqlite instead of MySQL, however in this case an entirely different error 
resulted. I have therefore included both (MySQL enabled, sqlite disabled by 
default).

Can anyone please tell me if there is something that I am doing wrong? is 
this a bug with sqlalchemy?

Thankyou,

Mark Eastwood

-- 
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/groups/opt_out.
'''
Demonstrate a (bug?) in updating records via a query


I am getting some unexpected behaviour when trying to update selected rows from
a query when using joint table inheritance. Using MySQL, a query that is
filtered based on items in the parent table, does not honour this filter when
updating items in the child table (all children are updated, not only those
matching the filter).

In an attempt to make it easier to run, I did try and make the script use
sqlite instead of MySQL, however in this case an entirely different error
resulted. I have therefore included both (MySQL enabled, sqlite disabled by
default).

With MySQL
--

All records within the Child table get updated. What should happen is just the
one record that matches the query gets updated

With SQLite
---

The query raises an OperationalError

Other Debug Info


I have tried this on two different systems with the same result. There are:

System 1:

Operating system: Windows 7 64 bit
Python version = 3.3.1
SQLAlchemy version = 0.8.0
MySQL version = 5.6.13
- included mysqlconnector from windows installer (v1.0.11)

System 2:

Operating system: Windows 7 32 bit
Python v3.3.1
SQLAlchemy v0.8.2
Mariadb version = 5.5.32
MySQLConnector version = 1.0.11
 
To run this script, MySQL (or Mariadb) needs to be installed and a user by the
name of 'tester' with password 'test_password' needs to have access to the
database 'test'@'localhost'. Of course, you can change these.

'''

from sqlalchemy import *
from sqlalchemy.exc import DatabaseError
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

###
# Uncomment one of the following engines
###

#engine = create_engine('sqlite://')

engine = create_engine('mysql+mysqlconnector://%s:%s@%s:%s/%s' % \
  ('tester', 'test_password', '127.0.0.1', '3306', 'test'))

#--

Base = declarative_base()
Base.metadata.bind = engine
session = sessionmaker(bind=engine)()

class Parent(Base):
__tablename__ = 'parent'

id = Column(INTEGER(), primary_key=True)
name = Column(VARCHAR(255), unique=True)
type = Column(VARCHAR(255))

__mapper_args__ = {'polymorphic_identity': 'Parent',
   'polymorphic_on': type}

class Child(Parent):
__tablename__ = 'child'
__mapper_args__ = {'polymorphic_identity': 'Child'}

id = Column(INTEGER(), ForeignKey(Parent.id), primary_key=True)
value = Column(INTEGER(255))

def __repr__(self):
return '%s %s' % (self.name, self.value)

###
# Start with a clean database
###

# MySQL warns if these tables don't exist, despite using IF EXISTS
# SQLAlchemy interprets these as errors, just ignore
try:
session.execute('DROP TABLE IF EXISTS child, parent;')
except DatabaseError:
pass

###
# Build a simple table
###

Base.metadata.create_all()
session.add_all([Child(name='Steven', value=1), 
 Child(name='Mark', value=2), 
 Child(name='Daniel', value=3)])
session.flush()
print('We have three records in the database')
print(session.query(Child).all())

query = session.query(Child).filter

[sqlalchemy] SQLAlchemy 0.7.9 breaks SQLAlchemy-ORM-Tree flush behavior

SQLAlchemy 0.7.9 seems to have broken SQLAlchemy-ORM-tree 
(http://pypi.python.org/pypi/SQLAlchemy-ORM-tree/). Specifically, 
SQLAlchemy-ORM-Tree has a dependency on flush behavior prior to the fix for 
#2566. I'm currently investigating a way to detect (and ignore) the 2nd 
flush.

But more generally I'm wondering what motivated #2566 in the first place? 
It has huge compatibility implications for anyone doing tricky things with 
flush and insert/update/delete events.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/nJU2GFvsATYJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] SQLAlchemy 0.7.9 breaks SQLAlchemy-ORM-Tree flush behavior

Well it's hard to boil it down to a specific test case, as it affects the
underlying assumptions that went into the design of ORM-tree. Here's an
explanation of what I'm doing, and perhaps you can tell me if I'm (ab)using
the API correctly:

The meat of the code is a mapper extension whose insert, update, and delete
hooks execute SQL expressions directly to update the nested-interval tree
parameters. For efficiency I use the SQL expression layer and then manually
update the working set of ORM objects to reflect the new state.

In essence:

connection.execute(...update in sql expression language...)
for obj in session.identity_map:
...same update, as python...

(The session.identity_map is accessible to the mapper extension because it
was tucked away as a hidden attribute in the object in a session extension
before_flush handler.)

As far as I can tell, the update to the session objects is now triggering a
2nd flush, even though the purpose of the update was to refresh the objects
with their current database values. On the 2nd flush the SQL expression
updates get executed again, resulting in a corrupt database.

Any red flags in what I'm doing?


All the relevant code is in this file:

https://github.com/monetizeio/sqlalchemy-orm-tree/blob/master/sqlalchemy_tree/orm.py


On Tue, Oct 2, 2012 at 12:07 PM, Michael Bayer mike...@zzzcomputing.comwrote:


 On Oct 2, 2012, at 2:14 PM, Mark Friedenbach wrote:

 SQLAlchemy 0.7.9 seems to have broken SQLAlchemy-ORM-tree (
 http://pypi.python.org/pypi/SQLAlchemy-ORM-tree/). Specifically,
 SQLAlchemy-ORM-Tree has a dependency on flush behavior prior to the fix for
 #2566. I'm currently investigating a way to detect (and ignore) the 2nd
 flush.

 But more generally I'm wondering what motivated #2566 in the first place?
 It has huge compatibility implications for anyone doing tricky things with
 flush and insert/update/delete events.


 #2566 was a serious issue.  If dirty state remains in the session after a
 flush(), then calling commit() had the effect that *two COMMITs are
 emitted*, meaning, one COMMIT, then a brand new transaction, then another
 one, and then any dirty state left by that second commit would just be
 garbage.   The commit() call flushes all remaining dirty state and it is
 essential that the session is clean after a commit occurs.2566's fix
 should only effect when commit() is called.

 Feel free to send me a test case showing a valid usage that is broken by
 this change.


  --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 To unsubscribe from this group, send email to
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at
 http://groups.google.com/group/sqlalchemy?hl=en.


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] SQLAlchemy 0.7.9 breaks SQLAlchemy-ORM-Tree flush behavior

I forgot to mention, this is picked up by SQLAlchemy-ORM-tree's unit tests,
if you want to see the failure:

git clone git://github.com/monetizeio/sqlalchemy-orm-tree.git
cd sqlalchemy-orm-tree  make check

On Tue, Oct 2, 2012 at 12:49 PM, Mark Friedenbach m...@monetize.io wrote:

 Well it's hard to boil it down to a specific test case, as it affects the
 underlying assumptions that went into the design of ORM-tree. Here's an
 explanation of what I'm doing, and perhaps you can tell me if I'm (ab)using
 the API correctly:

 The meat of the code is a mapper extension whose insert, update, and
 delete hooks execute SQL expressions directly to update the nested-interval
 tree parameters. For efficiency I use the SQL expression layer and then
 manually update the working set of ORM objects to reflect the new state.

 In essence:

 connection.execute(...update in sql expression language...)
 for obj in session.identity_map:
 ...same update, as python...

 (The session.identity_map is accessible to the mapper extension because it
 was tucked away as a hidden attribute in the object in a session extension
 before_flush handler.)

 As far as I can tell, the update to the session objects is now triggering
 a 2nd flush, even though the purpose of the update was to refresh the
 objects with their current database values. On the 2nd flush the SQL
 expression updates get executed again, resulting in a corrupt database.

 Any red flags in what I'm doing?


 All the relevant code is in this file:


 https://github.com/monetizeio/sqlalchemy-orm-tree/blob/master/sqlalchemy_tree/orm.py



 On Tue, Oct 2, 2012 at 12:07 PM, Michael Bayer 
 mike...@zzzcomputing.comwrote:


 On Oct 2, 2012, at 2:14 PM, Mark Friedenbach wrote:

 SQLAlchemy 0.7.9 seems to have broken SQLAlchemy-ORM-tree (
 http://pypi.python.org/pypi/SQLAlchemy-ORM-tree/). Specifically,
 SQLAlchemy-ORM-Tree has a dependency on flush behavior prior to the fix for
 #2566. I'm currently investigating a way to detect (and ignore) the 2nd
 flush.

 But more generally I'm wondering what motivated #2566 in the first place?
 It has huge compatibility implications for anyone doing tricky things with
 flush and insert/update/delete events.


 #2566 was a serious issue.  If dirty state remains in the session after a
 flush(), then calling commit() had the effect that *two COMMITs are
 emitted*, meaning, one COMMIT, then a brand new transaction, then another
 one, and then any dirty state left by that second commit would just be
 garbage.   The commit() call flushes all remaining dirty state and it is
 essential that the session is clean after a commit occurs.2566's fix
 should only effect when commit() is called.

 Feel free to send me a test case showing a valid usage that is broken by
 this change.


  --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 To unsubscribe from this group, send email to
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at
 http://groups.google.com/group/sqlalchemy?hl=en.




-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] SQLAlchemy 0.7.9 breaks SQLAlchemy-ORM-Tree flush behavior

Indeed, session.dirty is non-empty within after_flush_postexec().

I'm working on a fix for sqlalchemy-orm-tree first before I can think about
doing a (smaller) regression test. Besides, it now occurs to me that in
some cases I might be setting attributes on objects in the session but
outside of the flush plan (child nodes of a parent that gets moved around,
for example). That could legitimately cause an undesired 2nd
flush. `set_committed_value` looks like what I want to fix that.

On Tue, Oct 2, 2012 at 1:08 PM, Michael Bayer mike...@zzzcomputing.comwrote:


 On Oct 2, 2012, at 4:05 PM, Michael Bayer wrote:

 In theory it would be only .new and .dirty that might have state
 after the flush completes.

 correction, .new and .deleted lists that might have any state.


 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 To unsubscribe from this group, send email to
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at
 http://groups.google.com/group/sqlalchemy?hl=en.



-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] SQLAlchemy 0.7.9 breaks SQLAlchemy-ORM-Tree flush behavior

I'm not 100% sure that's what was going on (it was hard to get a debug
shell open at the moment of the error, as opposed to the assertion much
later), but switching those setattr() calls to set_committed_value()
certainly fixed it.

I admit I don't understand the session internals well enough to grok what
you're saying about resetting session.dirty or it being a deeper bug I've
uncovered. If I can be of any assistance with that please let me know.
Regardless, for this project it is no longer an issue as objects are no
longer getting dirtied by setattr() calls in the mapper extension.

Thanks for your help in pointing me down the right track; I'm pushing out a
new version of SQLAlchemy-ORM-tree momentarily.

Cheers,
Mark

On Tue, Oct 2, 2012 at 1:38 PM, Michael Bayer mike...@zzzcomputing.comwrote:

 oh, I know what you're doing, you're modifying the attributes of objects
 that aren't even involved - so yes, the flush normally doesn't go finding
 those, and set_committed_value() would be your workaround for now.

 However, I can modify flush to do this reset for everything that's in
 dirty, rather than just what it knows to have changed.I'd have to
 think about this as I'm not sure it's appropriate.



 On Oct 2, 2012, at 4:28 PM, Mark Friedenbach wrote:

 Indeed, session.dirty is non-empty within after_flush_postexec().

 I'm working on a fix for sqlalchemy-orm-tree first before I can think
 about doing a (smaller) regression test. Besides, it now occurs to me that
 in some cases I might be setting attributes on objects in the session but
 outside of the flush plan (child nodes of a parent that gets moved around,
 for example). That could legitimately cause an undesired 2nd
 flush. `set_committed_value` looks like what I want to fix that.

 On Tue, Oct 2, 2012 at 1:08 PM, Michael Bayer mike...@zzzcomputing.comwrote:


 On Oct 2, 2012, at 4:05 PM, Michael Bayer wrote:

 In theory it would be only .new and .dirty that might have state
 after the flush completes.

 correction, .new and .deleted lists that might have any state.


 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 To unsubscribe from this group, send email to
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at
 http://groups.google.com/group/sqlalchemy?hl=en.



 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 To unsubscribe from this group, send email to
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at
 http://groups.google.com/group/sqlalchemy?hl=en.


  --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 To unsubscribe from this group, send email to
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at
 http://groups.google.com/group/sqlalchemy?hl=en.


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Introducing SQLAlchemy-ORM-tree: a generic API for hierarchical data

2012-02-28 Thread Mark Friedenbach

I'd like to introduce a package I've been working on for a little
while now: SQLAlchemy-ORM-tree, “an implementation for SQLAlchemy-
based applications of the nested-sets/modified-pre-order-tree-
traversal technique for storing hierarchical data in a relational
database.” It's gone through a couple of semi-public releases already,
but now for the first time the API is complete and semi-stable, and I
would like to get some feedback from developers on this list who have
used or would like to use hierarchical data in an RDBMS through
SQLAlchemy. Some example code to show it in action:


from sqlalchemy_tree import TreeManager
node_table = Table('node', metadata,
  Column('id', Integer, primary_key=True),
  Column('parent_id', Integer, ForeignKey('node.id')))
class Node(object):
  tree = TreeManager(node_table)
mapper(Node, node_table, properties={
  'parent': relationship(Node,
backref = backref('children'),
remote_side = node_table.c.id),
})
Node.tree.register()

root = Node()
session.add(root)
session.flush()
child = Node()
child.parent = root
session.commit()

 child.tree.is_leaf_node
True
 child.tree.query_ancestors().one() == root
True
 child2 = Node()
 Node.tree.insert(child2, root, Node.tree.POSITION_LAST_CHILD)
 session.flush()
 root.tree.query_leaf_nodes() \
 .order_by(Node.tree) \
 .all() == [child, child2]
True
 child2.parent = None
 session.flush()
 child2.tree.is_root_node
True



The PyPI page:

http://pypi.python.org/pypi/SQLAlchemy-ORM-tree

and github (pull requests accepted):

https://github.com/rokusigma/sqlalchemy-orm-tree

Some things to note about the project as it is now: I am operating
under the principles of “1) make it work; 2) make it beautiful; then
finally 3) make it fast”. It works, and to prove it I've written over
300 unit tests in 3.4kLOC covering the expected behavior of all API
entry points. Now I'm soliciting the community's input in making it
“beautiful”--making sure the API is both functional and elegant,
refactoring areas of the code base that need it, making algorithmic
changes, and generally ensuring that the SQLAlchemy-ORM-tree package
is useful to anyone and everyone working with hierarchical data. I'll
then concern myself with low-level modifications for speed.

Some ugly warts I'm aware of (and reasons it's not yet 1.0):

* Making any changes to the structure of the tree requires flushing
before the various tree properties are updated (tree_id, left, right,
depth, etc.), as these updates are handled by a mapper before_flush
event handler and session (before/after)_(insert/update/delete) event
handlers. More subtlety, the introspective APIs `is_leaf_node`,
`is_descendant_of()`, etc. may possibly return incorrect results until
the session is flushed as they rely upon these tree property values.
I'm considering having `insert()` make changes to the database
directly and immediately without waiting for a flush event.

* There are no bulk insert, update, or delete operations. Within the
mapper event handlers each update, insert, and delete is handled in
turn, with all session objects updated accordingly at each stage. I
could use some input as to how best to structure a bulk-load/bulk-
update API from the user's perspective, as I lack experience in this
area.

* The tree manager needs access to the table at initialization time...
which in the declarative style means that the tree manager has to be
monkey-patched into the class object after it is defined. Suggestions
for improving this would be appreciated.

* The API for both inserting nodes AND updating the location of
existing nodes is `insert()`, the analogy being inserting into a
specific position in a list. It's confusing however as one uses it for
updates as well as insertions, and it also has nothing to do with the
SQL INSERT statement. `update()` or `move()` wouldn't be any better
for the same or similar reasons. Nomenclature suggestions would be
much appreciated.

* I just today learned about Tropashko's nested interval tree encoding
using continued fractions. I admit that I don't (yet) fully understand
the underlying maths, but appears that switching to continued
fractions over integers for the left/right fields could lead to
significant algorithmic gains in performance. Thankfully this could be
a transparent switch with no external API changes.

Happy hacking,
Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Set-based association proxy through AppenderQuery?

2012-02-11 Thread Mark Friedenbach

Hi,

Is it possible to have an association_proxy (in the association object
pattern) that emulates a set-based collection if it goes through a
lazy='dynamic' relationship? I can't for the life of me find a way to
make this work (setting collection_class on the dynamic relationship
doesn't seem to do anything).

Here's some example code of what I'm trying to do, extracted from the
actual project:

class ProofOfWork(object):
  blocks = association_proxy('Intermediatory_nodes', 'block')
proof_of_work = Table('proof_of_work', db.metadata)
mapper(ProofOfWork, proof_of_work, properties={
  'Intermediatory_nodes': relationship(lambda: Intermediatory,
lazy = 'dynamic'),
})

class Block(object):
  proof_of_works = association_proxy('Intermediatory_nodes',
'proof_of_work')
block = Table('block', db.metadata)
mapper(Block, block, properties={
  'Intermediatory_nodes': relationship(lambda: Intermediatory,
lazy = 'dynamic'),
})

class Intermediatory(object):
  pass
intermediatory = Table('intermediatory', db.metadata,
  Column('proof_of_work_id', Integer,
ForeignKey('proof_of_work.id'),
nullable = False),
  Column('block_id', Integer,
ForeignKey('block.id')),
)
mapper(Intermediatory, intermediatory, properties={
  'proof_of_work': relationship(lambda: ProofOfWork,
back_populates = 'Intermediatory_nodes',
remote_side= lambda: proof_of_work.c.id),
  'block': relationship(lambda: Block,
back_populates = 'Intermediatory_nodes',
remote_side= lambda: block.c.id),
})

How can I make ProofOfWork.blocks and Block.proof_of_works return an
_AssociationSet instead of _AssociationList?

Cheers,
Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Re: Expiring only unchanged stale data

2012-02-05 Thread Mark Friedenbach

Thanks Michael, the 'solution' is seems was to refactor my code so
that operations are clearly performed in the correct order, and in
some cases to explicitly load and/or update the tree parameters when
they might have changed. Now I'm not only doing before_flush (which
I've restricted to session/ORM queries only), but also before/after
insert, update, and delete (where I only do SQL expression queries).

On Feb 4, 8:52 am, Michael Bayer mike...@zzzcomputing.com wrote:
 On Feb 4, 2012, at 11:23 AM, Mark Friedenbach wrote:
  Hi, I'm running into a problem with my nested sets implementation.
  Inserting, moving, or removing a node can potentially affect one or
  more of many other nodes' tree properties (tree id, left, right,
  depth, or parent relationship). For efficiency's sake this change
  occurs as a single, rather complex SQL expression query that handles
  the magic of updating all the other node values.

  Just as a precaution I've added a session.expire_all() after the
  session.execute(query), so that the tree values will be reloaded as
  I move on to process other node operations in the same transaction.
  However what I've discovered is that expire_all() causes *all* as-of-
  yet unpersisted changes to be lost. As an example of what I mean,
  here's an actual shell log:

  obj = session.query(...)
  obj.name
  u'root1'
  obj.name = 'root66'
  session.add(obj)
  session.expire_all()
  session.commit()
  obj.name
  u'root1'

  It may be possible that I can restructure the order in which I do
  things so that stale data isn't an issue. But out of curiosity, is
  there a way to expire only *unchanged* stale data? This is how I
  naïvely expected expire_all() to work.

 all means everything, that method is called typically after rollback() or 
 commit() in conjunction with the transaction.

 While there is a way to detect history on all attributes and expire just 
 those with no net change, this is a time consuming operation and should not 
 be necessary.

 In this case, you know that the only values that are being updated outside of 
 the normal flush process are the left and right columns (and whatever 
 denormalized data you're storing such as depth), so you should just be 
 expiring those, and it should be either within the after_flush event:

 http://docs.sqlalchemy.org/en/latest/orm/events.html?highlight=after%...

 These attributes will refresh themselves when next accessed.

 Or if you have a means available of populating some of these attributes with 
 their correct value instead of just expiring, you can use 
 attributes.set_committed_value():

 http://docs.sqlalchemy.org/en/latest/orm/session.html?highlight=set_c...

 Reading your paragraph again, if you're actually doing the math for 
 left/right/depth in Python and need the value of those attributes to be 
 correct as the flush() proceeds, I'd consider doing the math in SQL, as you 
 can't assume all the nodes are going to be loaded into memory.

  Alternatively, a good API for this case would have been an
  expire_all(mapped_class, ['attribute', 'names']), a sort of compromise
  between expire() and expire_all().

 You can roll this yourself:

 for obj in session.identity_map.values():
     if isinstance(obj, myclass):
         session.expire(obj, ['a', 'b'])









  --
  You received this message because you are subscribed to the Google Groups 
  sqlalchemy group.
  To post to this group, send email to sqlalchemy@googlegroups.com.
  To unsubscribe from this group, send email to 
  sqlalchemy+unsubscr...@googlegroups.com.
  For more options, visit this group 
  athttp://groups.google.com/group/sqlalchemy?hl=en.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Expiring only unchanged stale data

2012-02-04 Thread Mark Friedenbach

Hi, I'm running into a problem with my nested sets implementation.
Inserting, moving, or removing a node can potentially affect one or
more of many other nodes' tree properties (tree id, left, right,
depth, or parent relationship). For efficiency's sake this change
occurs as a single, rather complex SQL expression query that handles
the magic of updating all the other node values.

Just as a precaution I've added a session.expire_all() after the
session.execute(query), so that the tree values will be reloaded as
I move on to process other node operations in the same transaction.
However what I've discovered is that expire_all() causes *all* as-of-
yet unpersisted changes to be lost. As an example of what I mean,
here's an actual shell log:

 obj = session.query(...)
 obj.name
u'root1'
 obj.name = 'root66'
 session.add(obj)
 session.expire_all()
 session.commit()
 obj.name
u'root1'

It may be possible that I can restructure the order in which I do
things so that stale data isn't an issue. But out of curiosity, is
there a way to expire only *unchanged* stale data? This is how I
naïvely expected expire_all() to work.

Alternatively, a good API for this case would have been an
expire_all(mapped_class, ['attribute', 'names']), a sort of compromise
between expire() and expire_all().

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Column Mixin

2011-11-27 Thread Mark Erbaugh


 Am 26.11.2011 15:26 schrieb Mark Erbaugh m...@microenh.com:
 
 I'm using a ColumnMixin to have a subset of columns common in two tables.  Is 
 there an easy way to populate the common columns in one descendent table with 
 the corresponding columns in a row of the other descendent tables, and can 
 this be a method of the ColumnMixin class?  Ideally, I'd like the copy method 
 to dynamically respond to changes in the ColumnMixin class (i.e. if I add a 
 column defiinition to the mixin, I don't want to have to modify the copy 
 method).
 
 Thanks,
 Mark

On Nov 27, 2011, at 4:06 AM, Robert Forkel wrote:

 Hi,
 I'm doing something similar and ended up giving all columns contributed by a 
 mixin a common prefix, and have the copy method loop over all columns of an 
 object, picking out the right ones.
 Regards
 Robert
 

Robert,

Thanks for the reply. I ended up with a different approach, illustrated by the 
code snippet:

_sizing_pump_columns = (
('pump', String(6)),
('mod_date', String(19)),
('curve', String(15)),
('eq_gpm', Float),
('eq_psi', Float),
)

current_pump_table = Table('current_pump', Base.metadata,
Column('id', Integer, primary_key=True),
Column('user', String, ForeignKey('user._user')),
*[Column(*i) for i in _sizing_pump_columns]
)

pump_table = Table('pump', Base.metadata,
Column('id', Integer, primary_key=True),
Column('sizing_id', Integer, ForeignKey('sizing.id')),
*[Column(*i) for i in _sizing_pump_columns]
)

class _SizedPumpBase(object):
def copy(self, other):
 
copy data from other into self

for i in _sizing_pump_columns:
self.__dict__[i[0]] = other.__dict[i[0]]


class SizedPump(_SizedPumpBase):
pass

class SizedPumpCurrent(_SizedPumpBase):
pass

mapper(SizedPumpCurrent, current_pump_table)
mapper(SizedPump, pump_table)

I switched from SA declarative to the separate table and mapper, so I could use 
the _sizing_pump_columns tuple for the definition of the current_pump_table and 
pump_table objects and use field names in the copy method.  As of now, it's 
passing my unit tests, but I'd appreciate any comments specifically on 
something I might have missed.

Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Column Mixin

2011-11-26 Thread Mark Erbaugh

I'm using a ColumnMixin to have a subset of columns common in two tables.  Is 
there an easy way to populate the common columns in one descendent table with 
the corresponding columns in a row of the other descendent tables, and can this 
be a method of the ColumnMixin class?  Ideally, I'd like the copy method to 
dynamically respond to changes in the ColumnMixin class (i.e. if I add a column 
defiinition to the mixin, I don't want to have to modify the copy method).

Thanks,
Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Dynamic data member instrumentation with declarative

2011-11-10 Thread Mark Erbaugh

I'm trying to use data from a sequence to add columns to a SQLAlchemy table 
created declaratively. Here's what I'm doing:

class Sizing(Base):
__tablename__ = 'sizing'
id = Column(Integer, primary_key=True)

[...]


for name in ('column1', 'column2', 'column3', ...):
x = Column(type_=Integer)
x.name = name
Sizing.__table__.append_column(x)

This works as far as creating the table in the database, i.e. viewing the 
database shows columns named column1, column2, column3, etc, but code like 
getattr(sizing, 'column1')  (sizing is an instance of Sizing) is failinging 
with a message 'Sizing' object has no attribute 'column1'

While code like:

Sizing.colum1 = Column(Integer)

works, but

Sizing.__dict__['column1'] = Column(Integer)
or

Sizing.__setattr__(Sizing, 'column1', Column(Integer))

fails

The reason I'm trying to use the sequence to create the colums is that data 
from the sequence containing the column names is used in other parts of the 
application and I'd like to maintain that data in just one place. It's okay if 
I have to rebuild the database when the columns in the sequence change.

Thanks,
Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Dynamic data member instrumentation with declarative

2011-11-10 Thread Mark Erbaugh


On Nov 10, 2011, at 12:57 PM, Michael Bayer wrote:

 
 On Nov 10, 2011, at 9:34 AM, Mark Erbaugh wrote:
 
 I'm trying to use data from a sequence to add columns to a SQLAlchemy table 
 created declaratively. Here's what I'm doing:
 
 class Sizing(Base):
   __tablename__ = 'sizing'
   id = Column(Integer, primary_key=True)
 
   [...]
 
 
 for name in ('column1', 'column2', 'column3', ...):
   x = Column(type_=Integer)
   x.name = name
   Sizing.__table__.append_column(x)
 
 
 that will just add the column to the table but won't map it.   the mapper 
 isn't aware of this operation.
 
 
 This works as far as creating the table in the database, i.e. viewing the 
 database shows columns named column1, column2, column3, etc, but code like 
 getattr(sizing, 'column1')  (sizing is an instance of Sizing) is failinging 
 with a message 'Sizing' object has no attribute 'column1'
 
 While code like:
 
 Sizing.colum1 = Column(Integer)
 
 works, but
 
 right so that hits the __setattr__ of the DeclarativeMeta class which 
 receives the Column object, checks it out, and assigns it correctly to the 
 mapper and table.
 
 
 Sizing.__dict__['column1'] = Column(Integer)
 
 In general, you should never set attributes this way from the outside, that's 
 just a Python thing, as you're bypassing whatever attribute set mechanics may 
 be present on the target object.
 
 or
 
 Sizing.__setattr__(Sizing, 'column1', Column(Integer))
 
 this is not much different as again you're bypassing instrumentation that may 
 be available on the class.   Use the Python setattr() function instead:  
 setattr(Sizing, name, object).


Thanks so much!

Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] select count(*)

2011-11-04 Thread Mark Erbaugh


On Nov 4, 2011, at 4:54 AM, Stefano Fontanelli wrote:

 Il 04/11/11 03.08, Mark Erbaugh ha scritto:
 
 On Nov 3, 2011, at 2:54 PM, Stefano Fontanelli wrote:
 
 Il 03/11/11 19.18, Mark Erbaugh ha scritto:
 Using the query object count() method generates a sub-query (as per the 
 docs). The docs say to use func.count to avoid the subquery. func.count 
 seems to require a field object. Is there a way to generate a query that 
 essentially becomes 'select count(*) from table' using the ORM, not the 
 SQL generator?
 
 Thanks,
 Mark
 
 Did you try func.count('*')?
 How would you specify the table you want counted?  I trued 
 func.count('table.*') and that didn't work.
 
 Check the manual: http://www.sqlalchemy.org/docs/orm/tutorial.html#counting
 
 To achieve our simple SELECT count(*) FROM table, we can apply it as:
 
 SQL session.query(func.count('*')).select_from(User).scal
 ar()
 
 Is that right for you?

Stefano,

Thanks. I missed that in the documentation and it does indeed generate the 
expected SQL (at least with SQLite).

Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] select count(*)

2011-11-03 Thread Mark Erbaugh

Using the query object count() method generates a sub-query (as per the docs). 
The docs say to use func.count to avoid the subquery. func.count seems to 
require a field object. Is there a way to generate a query that essentially 
becomes 'select count(*) from table' using the ORM, not the SQL generator?

Thanks,
Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] select count(*)

2011-11-03 Thread Mark Erbaugh


On Nov 3, 2011, at 2:54 PM, Stefano Fontanelli wrote:

 Il 03/11/11 19.18, Mark Erbaugh ha scritto:
 Using the query object count() method generates a sub-query (as per the 
 docs). The docs say to use func.count to avoid the subquery. func.count 
 seems to require a field object. Is there a way to generate a query that 
 essentially becomes 'select count(*) from table' using the ORM, not the SQL 
 generator?
 
 Thanks,
 Mark
 
 Did you try func.count('*')?

How would you specify the table you want counted?  I trued 
func.count('table.*') and that didn't work.

Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] select count(*)

2011-11-03 Thread Mark Erbaugh


On Nov 3, 2011, at 3:31 PM, werner wrote:

 Mark,
 
 On 11/03/2011 07:18 PM, Mark Erbaugh wrote:
 Using the query object count() method generates a sub-query (as per the 
 docs). The docs say to use func.count to avoid the subquery. func.count 
 seems to require a field object. Is there a way to generate a query that 
 essentially becomes 'select count(*) from table' using the ORM, not the SQL 
 generator?
 Just the other day I thought I needed the same, initially I just used the 
 id column which all my tables had, but as count(anything) is pretty 
 expensive (using Firebird SQL - so might be different for other dbs) I wanted 
 to find a way without using count().  In my case I needed at some point to 
 get all the id values of that table (to build a virtual listctrl in 
 wxPython), so instead of doing the count and starting feeling the list I got 
 the id and did a len(onresult) to get my count.
 
 Point I am trying to make with a lot of words, maybe there is a solution 
 which doesn't need count() at all:-) .

I never considered that a count(*) was that expensive especially if there is no 
where clause. I would think that it would be less expensive than actually 
retrieving all the rows and counting them.  What if there are millions of rows? 
The result set could fill up memory. In my case, I just need to know how many 
rows. I don't care about any other details. In one case, I'm checking to see if 
there are zero rows, in which case, I populate the table with initial rows. In 
another case, I'm just unittesting some code and I want to make sure that there 
are the proper number of rows in the table as one of the test conditions.

Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] count rows in a table

2011-10-25 Thread Mark Erbaugh

What's the recommended way to count the rows in a table. In SQL,  I would 
typically use select count(*) from table;

The statement session.query(table).count()  issues a count(*) on a sub-query. 
 The docs say for finer control to use func.count

i.e. session.query(func.count(table.column)). That works, but you do have to 
specify a table column.

session.query(func.count('table.*')) also appears to work, but issues a 
parameterized query.

Is getting the count (however it's done) and checking for 0 the best way to 
check for an empty table?

Mark


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Degenerate relationship?

2011-10-15 Thread Mark Erbaugh


On Oct 15, 2011, at 10:17 AM, Michael Bayer wrote:

 
 On Oct 14, 2011, at 9:45 PM, Mark Erbaugh wrote:
 
 There are two tables pump and curve. The curve table has three fields, 
 curve_pn, head and gpm. The design is that the rows with the same curve_pn 
 value represent x,y points (head,gpm) on a pump performance curve. Each row 
 in the pump table has a curve_pn column that links to the performance curve 
 for that pump. The same performance curve can apply to multiple pumps.
 
 To me it seems that there is a many-many relationship, yet there is no 
 association table.  This design works fine in straight SQL. To model it in 
 SQLAlchemy, do I need to add an association table?  For the purposes of this 
 application, the data is read-only, but if it were not, if the data for a 
 curve were to change, I would want it to change for all the pumps that use 
 that curve_pn.
 
 SQLAlchemy's rules are more relaxed than relational database rules here, 
 which would definitely require that you use proper foreign keys.   In SQLA's 
 case it populates local to remote from A-B as the configuration tells it to, 
 does a join on lookup, and primaryjoin/foreign_keys does what you need:
 

Thanks

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Degenerate relationship?

2011-10-14 Thread Mark Erbaugh

There are two tables pump and curve. The curve table has three fields, 
curve_pn, head and gpm. The design is that the rows with the same curve_pn 
value represent x,y points (head,gpm) on a pump performance curve. Each row in 
the pump table has a curve_pn column that links to the performance curve for 
that pump. The same performance curve can apply to multiple pumps.

To me it seems that there is a many-many relationship, yet there is no 
association table.  This design works fine in straight SQL. To model it in 
SQLAlchemy, do I need to add an association table?  For the purposes of this 
application, the data is read-only, but if it were not, if the data for a curve 
were to change, I would want it to change for all the pumps that use that 
curve_pn.

TIA,
Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Get value from field class and instance

2011-09-06 Thread Mark Erbaugh

Let's say there is a mapped (declaratively, but that shouldn't matter) class, 
Data, that has fields Data.value1, ... Data.value10.

There is also an instance of this class, data that is populated from the data 
table.

Obviously, you can get the values using data.value1, ...

But is there a simple way to get a data value using the instance object (data) 
and a class field (Data.value1).

What's the easiest way given data and Data.value1 to get / set that value of 
data.value1? 

So far I've come up with:

Data.__getattribute__(data, Data.value1.property.columns[0].name)  but is there 
a more direct way?

==

If you're curious, here's what I'm trying to do.  I have an calculation that 
sums a calculation on all of a particular type of field. If I add a new field 
of this type to the table, it would be nice if it were automatically included 
in the calculation.

I've created a custom descendent of Column for this type of column. When the 
constructor of this custom class is called during the table construction, it 
adds the created field to a list. The calculation should then step through the 
columns in this list when calculating the value.


Thanks,
Mark



-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Get value from field class and instance

2011-09-06 Thread Mark Erbaugh


On Sep 6, 2011, at 2:48 PM, Michael Bayer wrote:

 
 On Sep 6, 2011, at 2:38 PM, Mark Erbaugh wrote:
 
 Let's say there is a mapped (declaratively, but that shouldn't matter) 
 class, Data, that has fields Data.value1, ... Data.value10.
 
 There is also an instance of this class, data that is populated from the 
 data table.
 
 Obviously, you can get the values using data.value1, ...
 
 But is there a simple way to get a data value using the instance object 
 (data) and a class field (Data.value1).
 
 What's the easiest way given data and Data.value1 to get / set that value of 
 data.value1? 
 
 Data.value1 is a Python descriptor, so Data.value1.__get__(data, Data) would 
 do it.Or getattr(data, Data.value1.key) as key is present on the SQLA 
 instrumented attribute.
 

Thanks.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Repetitive Fields in declarative


On Aug 18, 2011, at 7:01 PM, Mark Erbaugh wrote:

 
 On Aug 18, 2011, at 6:06 PM, Mark Erbaugh wrote:
 
 want to create a table that has several similar fields. For example, assume 
 the fields are field1, field2, ...
 
 Is there a way in the declarative class that I can do something like:
 
 for i in range(10):
  'field%d' % i = Column( ... )
 
 
 Thanks,
 Mark
 
 
 Figured it out:
 
 after the class definition:
 
 for i in range(10):
   class.__table__.append_column(Column('field%d' % i, ...))


I guess not: while the above code adds the fields to the database table, it 
doesn't add them as named data members of the class.  Here's my latest effort:

class Preferences:
...

for i in range(10):
setattr(Preferences, 'field%d' % i, Column(...

This also answers my question about relationships

setattr(Preferences 'relationship%d' % i, relationship(...


Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Repetitive Fields in declarative


On Aug 19, 2011, at 10:41 AM, Michael Bayer wrote:

 Id use a mixin so that a superclass can be generated in a data driven manner:
 
 
 MyCols = type(MyCols, (object, ), dict((field%d % i, Column(Integer)) for 
 i in xrange(1, 10)))
 
 class MyClass(MyCols, Base):
...
 
 otherwise if you want to go the append_column() route, you can just tack them 
 on the class, declarative will call append_column() as well as 
 mapper.add_property():
 
 for name, col in (field%d % i, Column(Integer)) for i in xrange(1, 10)):
setattr(MyClass, name, col)


Michael,

Thanks for the info.

Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Handling optional relationship

I have a table that has a foreign key field that is optional.  IOW, the current 
row may be linked to at most one row in the foreign table. If the foreign key 
field is not NULL, it must point to a valid row in the foreign table, but if it 
is NULL that means that it it not linked.

Is there an automatic way to have the value of the foreign key field set to 
NULL if the linked row in the foreign table is deleted?

Thanks,
Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Handling optional relationship


On Aug 19, 2011, at 2:10 PM, Mike Conley wrote:

 class Parent(Base):
 __tablename__ = 'parent'
 id = Column(Integer, primary_key=True)
 class Child(Base):
 __tablename__ = 'child'
 id = Column(Integer, primary_key=True)
 p_id = Column(Integer, ForeignKey(Parent.id))
 parent = relation(Parent, backref=backref('children', 
 cascade=save-update,merge))
 
 sess.add(Parent(children=[Child(),Child()]))
 sess.commit()
 p = sess.query(Parent).first()
 sess.delete(p)
 sess.commit()


Mike,

Thanks. that does indeed work.  For some reason, it didn't seem to be work in 
my schema.  I'll have to do some more testing.

Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Repetitive Fields in declarative

2011-08-18 Thread Mark Erbaugh

I want to create a table that has several similar fields. For example, assume 
the fields are field1, field2, ...

Is there a way in the declarative class that I can do something like:

for i in range(10):
'field%d' % i = Column( ... )


Thanks,
Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Repetitive Fields in declarative

2011-08-18 Thread Mark Erbaugh


On Aug 18, 2011, at 6:06 PM, Mark Erbaugh wrote:

  want to create a table that has several similar fields. For example, assume 
 the fields are field1, field2, ...
 
 Is there a way in the declarative class that I can do something like:
 
 for i in range(10):
   'field%d' % i = Column( ... )
 
 
 Thanks,
 Mark


Figured it out:

after the class definition:

for i in range(10):
class.__table__.append_column(Column('field%d' % i, ...))

Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Repetitive Fields in declarative

2011-08-18 Thread Mark Erbaugh

Me again (see below):

On Aug 18, 2011, at 7:01 PM, Mark Erbaugh wrote:

 
 On Aug 18, 2011, at 6:06 PM, Mark Erbaugh wrote:
 
 want to create a table that has several similar fields. For example, assume 
 the fields are field1, field2, ...
 
 Is there a way in the declarative class that I can do something like:
 
 for i in range(10):
  'field%d' % i = Column( ... )
 
 
 Thanks,
 Mark
 
 
 Figured it out:
 
 after the class definition:
 
 for i in range(10):
   class.__table__.append_column(Column('field%d' % i, ...))

Some of the fields that I am adding this way are foreign keys to another table. 
Is there a way to specify a relationship based on these foreign key fields?

Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Group / Order by field in relationship?

2011-08-17 Thread Mark Erbaugh


On Aug 17, 2011, at 10:15 AM, Conor wrote:

 On 08/17/2011 12:01 AM, Mark Erbaugh wrote:
 
 Is it possible to group or order by a field in a many to one related table?
 
 class Rental(Base):
 __tablename__ = 'rental'
 
 rental_id = Column(Integer, autoincrement=True, primary_key=True)
 inventory_id = Column(Integer, ForeignKey(Inventory.inventory_id), 
 nullable=False)
 
 inventory = relation(Inventory,
 uselist=False,
 backref='rentals',
 )
 
 class Inventory(Base):
 __tablename__ = 'inventory'
 
 inventory_id = Column(Integer, autoincrement=True, primary_key=True)
 film_id = Column(Integer, ForeignKey(Film.film_id), nullable=False)
 
 film = relation(Film,
 uselist=False,
 backref='inventory',
 )
 
 
 
 session.query(Rental).order_by(Rental.inventory.film_id)  generates the 
 error:
 
 Neither 'InstrumentedAttribute' object nor 'Comparator' object has an 
 attribute 'film_id'
 You have to explicitly join to the related table, e.g.:
 
 session.query(Rental).join(Rental.inventory).order_by(Inventory.film_id)
 For bonus points, you can tell SQLAlchemy that Rental.inventory has been 
 eagerloaded. This may reduce the number of lazy loads when you access a 
 Rental instance's inventory:
 
 q = session.query(Rental)
 q = q.join(Rental.inventory)
 q = q.options(sqlalchemy.orm.contains_eager(Rental.inventory))
 q = q.order_by(Inventory.film_id)
 -Conor
 
Conor,

Thanks for the information / confirmation. I had found that the explicit union 
worked, but I know there's a lot of SA that I don't understand and was 
concerned I was missing something.

Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Group / Order by field in relationship?

2011-08-16 Thread Mark Erbaugh

Is it possible to group or order by a field in a many to one related table?

 class Rental(Base):
 __tablename__ = 'rental'
 
 rental_id = Column(Integer, autoincrement=True, primary_key=True)
 inventory_id = Column(Integer, ForeignKey(Inventory.inventory_id), 
 nullable=False)
 
 inventory = relation(Inventory,
 uselist=False,
 backref='rentals',
 )

 class Inventory(Base):
 __tablename__ = 'inventory'
 
 inventory_id = Column(Integer, autoincrement=True, primary_key=True)
 film_id = Column(Integer, ForeignKey(Film.film_id), nullable=False)
 
 film = relation(Film,
 uselist=False,
 backref='inventory',
 )



session.query(Rental).order_by(Rental.inventory.film_id)  generates the error:

Neither 'InstrumentedAttribute' object nor 'Comparator' object has an attribute 
'film_id'

Thanks,
Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Softcoding .filter(...)


On Aug 12, 2011, at 10:21 AM, RVince wrote:

 I'm trying to discern a means of creating a .filter(A rel B) where the
 values for A, rel and B come from an parameters passed in to the web
 page.
 
 I already have an SQLAlchemy statement, say
 query = Session.query(table).filter(A==B)
 
 and I want to be able to allow for a drilldown of sorts by the, such
 that from the web page they can pick a value from a dropdown, a
 relation (from a dropdown) and a textbox to compare to. But my problem
 is once I have these three values, how do I get them into
 the .filter() function? That's not going to merely accept string
 values -- is there a way to do this?
 
 Thanks, RVince
 

You can build your SQLAlchemy queries dynamically, i.e.

q1 = query.Session.query(table).filter(A == B)

q2 = q1.filter(C == D)

q3 = q2.filter(E == F)

you could apply different relationships using conditional Python statements:

if rel == 'eq':
q4 = q3.filter(G == H)
elif rel == 'neq':
q4 = q3.filter(G != H)

is this what you're looking for?

Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Re: Softcoding .filter(...)


On Aug 12, 2011, at 11:52 AM, NiL wrote:

 say you want to filter on the 'field' (field would be a string representing 
 the name of the field) on objects of class == Klass
 
 field_attr = getattr(Klass, field)
 
  would give you the instrumented attribute
 
 then
 
 Session.query(Klass).filter(field_attr == searchString)
 
 or
 
 Session.query(Klass).filter(field_attr.endswith(searchString))
 
 would run
 
 HTH
 
 NiL

You can also use the class's __dict__ member:

field_attr = Klass.__dict__['field']

It really amazes me how Pythonic SQLAlchemy makes database access.

Mark



-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] declarative table columns

Is there a way to access the parameters to the Column() call used to set up a 
database table when given either an instance field or class field?

For example:

class MyClass(Base):
...
f1 = Column(Integer, nullable=False, info={'min':0})
...

If I have MyClass.f1 or my_class.f1 (where my_class is an instance of MyClass) 
is there a way to get nullable or info?

The only way I've come up with so far is to match the __table__.columns 
elements on the name parameter.

Thanks,
Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] declarative table columns


On Aug 12, 2011, at 5:26 PM, Michael Bayer wrote:

 
 Is there a way to access the parameters to the Column() call used to set up 
 a database table when given either an instance field or class field?
 
 For example:
 
 class MyClass(Base):
  ...
  f1 = Column(Integer, nullable=False, info={'min':0})
  ...
 
 If I have MyClass.f1 or my_class.f1 (where my_class is an instance of 
 MyClass) is there a way to get nullable or info?
 
 The only way I've come up with so far is to match the __table__.columns 
 elements on the name parameter.
 
 if you have MyClass.fi, column is MyClass.f1.property.columns[0].


Thanks - just what I was looking for!

Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Default values

2011-08-06 Thread Mark Erbaugh


On Aug 6, 2011, at 7:18 AM, Mike Conley wrote:

 You can get to the column default value.
 
 class MyTable(Base):
 __tablename__ = 'table'
 id = Column(Integer, primary_key=True)
 name = Column(String, default='new name')
 def __init__(self, name=None):
 if name is not None:
 self.name = name
 else:
 self.name = getDefault(self, 'name')
 def getDefault(instance, colName):
 col = instance.__table__.c[colName]
 if col.default is not None:
 dflt = col.default.arg
 else:
 dflt = None
 return dflt


Mike,

Thanks.  I adapted your code:

def __init__(self):
for col in self.__table__.c:
if col.default is not None:
self.__setattr__(col.key, col.default.arg)


Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Default values

In a declaratively created table, is there an automatic way to get a new 
instance of the class object to be populated with values specified in a 
'default' clause?

i.e.

class MyTable(Base):
__tablename__ = 'table'
name = Column(String, default='new name')
...


newRow = MyTable()

is there a way to have newRow.name automatically have the value 'new name' 
before it is committed to the database?  The best I've been able to come up 
with so far is to use a 'CONSTANT' in the default clause and use that same 
CONSTANT to initialize the field in the class' __init__, but this doesn't seem 
very DRY.

Or, maybe is this the wrong question?  Maybe I'm trying to do things the wrong 
way. I'm trying to use mostly the same code add a new row or edit an existing 
row.  If the user is adding a record, I create a new instance of the class and 
use the add/edit screen to edit the data. If the user is editing an existing 
row, I retrieve the row, then use the add/edit screen with it.

Thanks,
Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Declarative Field Type 'Alias'

In my application, some tables have several fields that need to have the same 
type and default value, i.e.:

field1 = Column(Integer, default=2)
field2 = Column(Integer, default=2)
...

Is there some way to refactor the Common(Integer, default=2), short of creating 
a custom column type?  I could see the possibility that in a future version of 
the application, I would want to globally change the column type or default 
value for all these fields at once.

So far, I've come up with creating a function that returns the column.

def common_field():
return Column(Integer, default=2)

field1 = common_field()
field2 = common_field()

Is there a better way?

Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Default values


On Aug 5, 2011, at 2:00 PM, Stefano Fontanelli wrote:

 Il 05/08/11 19.29, Mark Erbaugh ha scritto:
 In a declaratively created table, is there an automatic way to get a new 
 instance of the class object to be populated with values specified in a 
 'default' clause?
 
 i.e.
 
 class MyTable(Base):
  __tablename__ = 'table'
  name = Column(String, default='new name')
 ...
 
 
 newRow = MyTable()
 
 is there a way to have newRow.name automatically have the value 'new name' 
 before it is committed to the database?  The best I've been able to come up 
 with so far is to use a 'CONSTANT' in the default clause and use that same 
 CONSTANT to initialize the field in the class' __init__, but this doesn't 
 seem very DRY.
 
 Or, maybe is this the wrong question?  Maybe I'm trying to do things the 
 wrong way. I'm trying to use mostly the same code add a new row or edit an 
 existing row.  If the user is adding a record, I create a new instance of 
 the class and use the add/edit screen to edit the data. If the user is 
 editing an existing row, I retrieve the row, then use the add/edit screen 
 with it.
 
 Thanks,
 Mark
 
 
 Hi Mark,
 to fill with defaults you can do:
 
 newRow = MyTable()
 session.add(newRow)
 session.flush()
 print newRow.name
 
 'print newRow.name' will display 'new name'
 
 To use the same code for create/update I suggest you to use session.merge: 
 http://www.sqlalchemy.org/docs/orm/session.html#merging

Stefano,

Thanks for the reply. The problem I see with this approach is that I think it 
actually commits the new row to the database.  In the app, it's possible that 
the user could decide to cancel before inserting the new row.  Of course, I 
could back out the addition, but it seems like it would be better to not insert 
in the first place.

Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Declarative Field Type 'Alias'


On Aug 5, 2011, at 3:51 PM, Michael Bayer wrote:

 On Aug 5, 2011, at 1:36 PM, Mark Erbaugh wrote:
 
 In my application, some tables have several fields that need to have the 
 same type and default value, i.e.:
 
 field1 = Column(Integer, default=2)
 field2 = Column(Integer, default=2)
 ...
 
 Is there some way to refactor the Common(Integer, default=2), short of 
 creating a custom column type?  I could see the possibility that in a future 
 version of the application, I would want to globally change the column type 
 or default value for all these fields at once.
 
 So far, I've come up with creating a function that returns the column.
 
 def common_field():
  return Column(Integer, default=2)
 
 field1 = common_field()
 field2 = common_field()
 
 Is there a better way?
 
 What's the issue with using a function to generate a Column of a certain 
 pre-determined configuration (what are functions in a procedural language for 
 if not this) ?  


No issue at all.  I just wanted to make sure I was doing it 'the right way'.  I 
just noticed that in several places, SA will let you pass in a class or an 
instance of a class and figures out what to do with it.  I thought that 
something like that might be working here.

Actually, there is a small issue with using a function: Where should the 
function live?  Obviously for some schema, this field type is used in multiple 
tables and belongs in a global namespace, but for others (as in my 
application), the field type is unique to an individual table. It would be nice 
if the function could live in the class's namespace.

This is more of a Python issue than a SA issue, but I had trouble getting this 
to work. I did, but the code seems a little awkard to me sigh.  In addition 
to the requirements already, I also wanted toe default value to be a class 
level 'constant'.  The problem, as I see it, is that since the class definition 
isn't complete, it's namespace isn't avaialble.  Since the default value 
'constant' is a class data member, it would make sense if the function were a 
@classmethod, but I couldn't get python to accept:

class  Table(Base):

...

DEFAULT = 2

@classmethod
def CustomColumn(cls):
return Column(Integer, default=DEFAULT)

...

field1 = CustomColumn()

Python complained 'classmethod object is not callable' on the last line above.

Next I tried changing that line to:

field1 = Table.CustomColumn()

Now Python complained 'Table' is not  defined

If I leave the @classmethod decroator off, I couldn't figure out how to 
reference the class level data DEFAULT.

Python complained on the return Column(... line ' global name DEFAULT is not 
defined.

What I finally ended up with that works is:

class Table(Base):
...
DEFAULT = 2

def CustomColumn(default=DEFAULT):
return Column(Integer, default=default)

...

field1 = CustomColumn()

Mark


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Populate sample data


On Aug 4, 2011, at 9:22 AM, Michael Bayer wrote:

 The range of speedups here would be between 30% and 80%, with direct usage of 
 connection/session .execute() with Table metadata giving you the 80%.

Thanks. I'll look into your suggestions

 
 I'm not sure what transaction is in transaction.begin() , if you're using a 
 regular SQLAlchemy Session in it is always in a transaction in that it uses 
 a single connection until rollback() or commit() is called.

Originally, I thought transaction was from the standard Python library, but 
upon research, it looks like it's from the transaction package that is part of 
Zope. It's included in the Pyramid installation.

Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Populate sample data


On Aug 4, 2011, at 9:22 AM, Michael Bayer wrote:

 
 On Aug 3, 2011, at 8:38 PM, Mark Erbaugh wrote:
 
 I'm using SA (with SQLite) with a schema like:
 
 A - B - C - D
 
 where - means that the tables have a one to many relationship
 
 I'm populating  a sample data set where there are 25 rows in A, 25 rows in B 
 for each row in A, 25 rows in C for each row in B and 25 rows in D for each 
 row in C.  This results in about 390k rows in D.  The database itself is 
 only about 12 MB, but it takes a long time (several minutes) to write the 
 data to the file.
 
 I'm taking the approach of appending items to the table's relationship 
 column.
 
 for i in range(25):
  x = A()
  session.add(A)
  for j in range(25):
  y = B()
  x.b.append(y)
  for k in range(25):
  z = C()
  y.c.append(z)
  for  l in range(25):
  xx = D()
  z.d.append(xx)
 session.flush()


Thanks again for the help.  I decided to time the various approaches.  My 
original approach took 4:23 (minutes: seconds). Note: all my times included 
data generation and insertion into a SQLite on-disk database.

 The biggest speed variable in a mass INSERT operation is whether or not 
 individual cursor.execute() calls, or a small handful of cursor.executemany() 
 calls each with thousands of rows, are used.
 
 With the ORM, a mass executemany() is used for INSERT in the case that 
 primary key values are already present in the given objects.   If not, the 
 ORM has to INSERT each A() row one at a time, get the new primary key value, 
 and then later populate 25*25 B() object's foreign key with the A.id value; 
 then this goes down to the B()-C() area, etc.
 
 So if A(), B(), C(), D() can be created with id=5, id=6, etc., assuming 
 id is the primary key, the ORM should be able to bunch lots of rows 
 together into one cursor.executemany() call and you'll see an immediate, 
 dramatic speedup.


This took 3:36

 
 The next level would be if you populated the a_id, b_id, 
 foreign-key-to-parent columns directly instead of using append().If you 
 did a profile on your script you'd see lots of time taken in many places, but 
 all those append() operations would be one of them, as well as lots of event 
 firing and bookkeeping that SQLAlchemy has to do when they occur, both at 
 append() time as well as within the flush() (populating the primary key 
 values to foreign key attributes).


This took 2:28

 By far the fastest way to do this would be to use 
 session.execute(a_table.insert(), [rows]), 
 session.execute(b_table.insert(), [rows]), etc.   That is, assemble the 
 whole set of A, B, C, D, directly in terms of the mapped table,  or better 
 yet do it in chunks, perhaps drill down through B, C, D for a single A then 
 insert everything, etc.That way you optimize how these rows are 
 constructed in Python exactly to the pattern that corresponds directly to the 
 database structure, instead of having SQLAlchemy decode the database 
 structure from an object hierarchy.  An insertmany is documented at 
 http://www.sqlalchemy.org/docs/core/tutorial.html#executing-multiple-statements.
The ORM Session has an execute() method just like Connection does.

I did this with one session.execute for each table, rather than chunks. It took 
0:46

 The range of speedups here would be between 30% and 80%, with direct usage of 
 connection/session .execute() with Table metadata giving you the 80%.

Mark


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] One to many, but only load one

Table A has a one to many relationship with Table B.  There may be zero or more 
rows in B for each row in A.

I would like to have a query that retrieves all the rows in table A joined with 
the first related row in table B (if one exists). In this case, each row in 
table B has a DATE field and I want to retrieve the row with the latest date.  
Is this possible using joinedload?

Thanks,
Mark

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] One to many, but only load one