[sqlalchemy] Re: passive deletes

2011-08-12 Thread erikj
Hello Michael,

thank you for your answer.  You are right that this behavior can be
enforced by setting the viewonly attribute of the relation, and the
others are not needed.

However, I do need to set it on both sides of the relation, or it does
not work.  But this not working might be triggered by another part
of the model where there is a join between B and another table to
form C.  I will have to investigate further to get a definitive
answer.

Regards,

Erik

On Aug 10, 4:55 pm, Michael Bayer mike...@zzzcomputing.com wrote:
 On Aug 10, 2011, at 6:56 AM, erikj wrote:



  Hi,

  I have a table and a view mapped to objects
  as such :

  class A(object):
    mapped to a table
    with a primary key column id
    with a onetomany attribute list_of_b, being a relation
    to all rows of B with a_id == self.id

  class B(object):
    mapped to a view
    with a column a_id being a reference to the id column of A

  Since B is mapped to a view, no updates should happen on B.

  So, in A, the list_of_b relation is configured with :

   passive_updates = True,
   passive_deletes = 'all',
   viewonly = True,
   cascade = False

  But whenever an object of A is deleted, sqlalchemy tries to update
  the a_id columns of all rows of B that were loaded in memory, which
  of course should not happen, since B is a view.

 You should only need viewonly=True, which disables any persistence activity 
 on the relationship.   A.list_of_bs would not participate in the flush 
 including no foreign key sync operations.

 A full test case demonstrating this behavior, using SQLite so that a B 
 table can be created with a non-enforcing FK, is attached.   I thought 
 perhaps setting a backref might not propagate the viewonly but this also 
 works as expected.    Check your configuration again to ensure it isn't doing 
 something else.

  viewonly_doesnt_affect_b.py
 1KViewDownload

-- 
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] joining to a from_statement

2011-08-12 Thread NiL
Hi,

thank you for your help, and the effort at writing a good piece of code.

however, while playing around with, and trying to execute it in PGAdmin, I 
stumbled upon some problems

the q = Session().query(Group).join(all_parents, all_parents.c.id==Group.id)

generates a SQL like (I've inserted real values):

SELECT groups_recursive.id AS groups_recursive_id, groups_recursive.name AS 
groups_recursive_name, groups_recursive.display_name AS 
groups_recursive_display_name 
FROM groups_recursive JOIN (WITH RECURSIVE all_parents(id, rank) AS (
(SELECT groups_recursive.id AS id, 1 
FROM groups_recursive_parents__groups_recursive_children 
WHERE groups_recursive.id = 
groups_recursive_parents__groups_recursive_children.parents_id 
AND groups_recursive_parents__groups_recursive_children.children_id = 4
UNION SELECT groups_recursive.id AS id, rank + 1 AS anon_2 
FROM groups_recursive_parents__groups_recursive_children 
WHERE groups_recursive_parents__groups_recursive_children.children_id = id 
AND groups_recursive_parents__groups_recursive_children.parents_id = 
groups_recursive.id)
)
SELECT * FROM all_parents) AS anon_1 ON anon_1.id = groups_recursive.id

it chokes on :

ERROR:  invalid reference to FROM-clause entry for table groups_recursive
LINE 3: (SELECT groups_recursive.id AS id, 1 
^
HINT:  There is an entry for table groups_recursive, but it cannot be 
referenced from this part of the query.


** Error **

ERROR: invalid reference to FROM-clause entry for table groups_recursive
SQL state: 42P01
Hint: There is an entry for table groups_recursive, but it cannot be 
referenced from this part of the query.
Character: 242

obviously, there is a problem at the JOIN stage

... SELECT groups_recursive.id AS id, 1 
FROM groups_recursive_parents__groups_recursive_children 
WHERE 

I tried

 s = select([groups.c.id, column(1)],from_obj=groups)
 
to no better luck

many thanks again
NiL 

-- 
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/-/4YkzRT9s2bAJ.
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] Can pk_col function be adapted for Firebird?

2011-08-12 Thread werner
I can't figure out how I could adapt the pk_col function on this page 
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/NamingConventions to 
handle the Sequence definition needed for e.g. Firebird.


At the point when Column is instantiated I don't have access to 
table.name and I can't figure it out either how to do it in 
on_table_attach.


Would appreciate any tips on this.

Werner


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



[sqlalchemy] Simple select is really slow when executed via ORM

2011-08-12 Thread Massi
Hi everyone,

I'm doing some test to evaluate the performance of querying with
sqlalchemy via ORM. I wrote a simple script to measure the execution
time of a simple select query made on relatively small table (300 000
records, 6 columns) in sqlite. Here is the script:

from sqlalchemy import *
from sqlalchemy.orm import mapper, sessionmaker
from time import clock

engine = create_engine('sqlite:///test.db', echo=False)
metadata = MetaData()

table = Table('projects', metadata,
Column('id', Integer, primary_key=True),
Column('inp1', String(50)),
Column('inp2', String(50)),
Column('inp3', String(50)),
Column('inp4', String(50)),
Column('inp5', String(50)),
)

class Project(object) :
pass

mapper(Project, table)
metadata.create_all(engine)

t = []
for i in range(30) :
t.append({inp1:str(i), inp2:str(i), inp3:str(i),
  inp4:str(i), inp5:str(i)})

c = clock()
engine.execute(table.insert(), t)
print Insert: +str(clock()-c)
session = sessionmaker(bind=engine)()
c = clock()
res = engine.execute(table.select()).fetchall()
print Sql query: +str(clock()-c)
c = clock()
res = session.query(Project).all()
print Session query: +str(clock()-c)

On my PC (windows 7, 64-bit, intel i7 2.93 Ghz) this is the output:
Insert: 3.41080167807
Sql query: 1.26728367673
Session query: 19.6452334842

The execution time of the ORM query is about 20 times the SQL one, and
this is definitely discouraging. So I guess if I'm doing something
wrong or if there are some tricks when using ORM that I'm not
considering. Any help is really appreciated.
Thanks in advance!

-- 
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] Can pk_col function be adapted for Firebird?

2011-08-12 Thread werner

On 08/12/2011 02:18 PM, werner wrote:
I can't figure out how I could adapt the pk_col function on this page 
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/NamingConventions to 
handle the Sequence definition needed for e.g. Firebird.


At the point when Column is instantiated I don't have access to 
table.name and I can't figure it out either how to do it in 
on_table_attach.


Would appreciate any tips on this.

Did a bit more searching and trying and came up with this:

def pk_col(cls, **kw):
Produce a primary key column for a table.

e.g.::

pk_col()

is equivalent to::

Column(id, sa.BigInteger,
 doc = Primary key column for tablename,
 primary_key=True,
 sequence=sa.Sequence('tablename_id')
  )


kw['primary_key'] = True
c = sa.Column(sa.BigInteger(), sa.Sequence('seq_%s_%s' % 
(cls.__tablename__,

   dbg.pkId)), **kw)

@sa.event.listens_for(c, before_parent_attach)
def on_table_attach(column, table):
column.name = column.key = dbg.pkId
column.doc = Primary key column for %r % table.name

c._creation_order = 0 # forces it to the top when using declarative
return c

I.e. pass cls in so I can get to __tablename__.

Is this an o.k. way of doing it or is there a better/cleaner way?

Werner

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



Re: [sqlalchemy] joining to a from_statement

2011-08-12 Thread Michael Bayer

On Aug 12, 2011, at 6:00 AM, NiL wrote:

 Hi,
 
 thank you for your help, and the effort at writing a good piece of code.
 
 however, while playing around with, and trying to execute it in PGAdmin, I 
 stumbled upon some problems
 
 the q = Session().query(Group).join(all_parents, all_parents.c.id==Group.id)
 
 generates a SQL like (I've inserted real values):
 
 SELECT groups_recursive.id AS groups_recursive_id, groups_recursive.name AS 
 groups_recursive_name, groups_recursive.display_name AS 
 groups_recursive_display_name 
 FROM groups_recursive JOIN (WITH RECURSIVE all_parents(id, rank) AS (
 (SELECT groups_recursive.id AS id, 1 
 FROM groups_recursive_parents__groups_recursive_children 
 WHERE groups_recursive.id = 
 groups_recursive_parents__groups_recursive_children.parents_id 
 AND groups_recursive_parents__groups_recursive_children.children_id = 4
 UNION SELECT groups_recursive.id AS id, rank + 1 AS anon_2 
 FROM groups_recursive_parents__groups_recursive_children 
 WHERE groups_recursive_parents__groups_recursive_children.children_id = id 
 AND groups_recursive_parents__groups_recursive_children.parents_id = 
 groups_recursive.id)
 )
 SELECT * FROM all_parents) AS anon_1 ON anon_1.id = groups_recursive.id
 
 it chokes on :
 
 ERROR:  invalid reference to FROM-clause entry for table groups_recursive
 LINE 3: (SELECT groups_recursive.id AS id, 1 
 ^
 HINT:  There is an entry for table groups_recursive, but it cannot be 
 referenced from this part of the query.
 
 
 ** Error **
 
 ERROR: invalid reference to FROM-clause entry for table groups_recursive
 SQL state: 42P01
 Hint: There is an entry for table groups_recursive, but it cannot be 
 referenced from this part of the query.
 Character: 242
 
 obviously, there is a problem at the JOIN stage
 
 ... SELECT groups_recursive.id AS id, 1 
 FROM groups_recursive_parents__groups_recursive_children 
 WHERE 
 
 I tried
 
  s = select([groups.c.id, column(1)],from_obj=groups)
  
 to no better luck

OK so I'd need you to wrestle with the PG side here, and figure out exactly 
what query PG accepts on this.   I've little experience with CTEs, not sure if 
perhaps the WITH always needs to be the outermost expression, etc.

Perhaps the query for the full span of mapped columns needs to be where we're 
putting the select * from all_parents part, and it joins to all_parents 
right there.   The CTE construct will ultimately have to work that way anyway, 
in that you can put any SELECT you want at that point.

-- 
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] Softcoding .filter(...)

2011-08-12 Thread RVince
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 received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.



Re: [sqlalchemy] Relationship spanning on multiple tables

2011-08-12 Thread Michael Bayer

On Aug 11, 2011, at 6:45 PM, neurino wrote:

 Sorry if I bother again but adding some others relationships like this 
 spanning on 5 tables:
 
 mapper(UserLayer, inherits=base_user_layer_mapper,
 polymorphic_identity=CLASS_LAYER,
 properties={
 ...
 'lca_transport': relationship(LCATransport,
 primaryjoin=and_(
 user_layers.c.id_mat==lca_transport.c.id_mat,
 user_layers.c.id_user_str==user_stratigraphies.c.id,
 user_stratigraphies.c.id_prov==provinces.c.id,
 provinces.c.id_reg==regions.c.id,
 regions.c.id_mr==lca_transport.c.id_mr),
 foreign_keys=(user_layers.c.id_mat,
 user_layers.c.id_user_str)),
 })


if you take a look at 
http://www.sqlalchemy.org/trac/attachment/ticket/2255/very_long_m2o.py the 
mappings succeed in compiling but many-to-one lazyloads on a long chain like 
that have never been tested.   Overall we don't have strong documentation / 
methodologies written down for long chain relationships like these since this 
is not really the primary use case of relationship.   

I'd advise not relying upon relationship() to join out that long for now.In 
my own applications, I reserve all my long chains of joins as explicitly 
written in session.query() calls.  The relationships themselves are simple.

The issue here is one of those where I have to set aside 2 hours of time and 
focus on it so I'll try to get to it when I can.


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

2011-08-12 Thread Mark Erbaugh

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] Simple select is really slow when executed via ORM

2011-08-12 Thread Michael Bayer
Here's a comparison that begins to be slightly fair regarding the work of 
fetching raw rows versus generating and identity-managing full object rows.   
On my mac the SQL query takes 7 seconds and the Session query 13.7, so 
twice as slow, rather than 20.  The difference is we are actually fetching the 
*data* from the SQLite result row, and additionally generating some real Python 
objects, with a check in the session identity map to simulate a small amount of 
ORM bookkeeping.The ORM maintains an additional object known as 
InstanceState for each mapped object, and establishing this object adds a 
significant chunk of time as well.  Python is very slow in creating objects, 
and in calling functions.   Here, each tiny little additional thing the ORM 
does is multiplied by 30, so it adds up. As you can see, just adding a 
small handful of steps to the SQL version gets it much closer very quickly.

For a 300K row grab you are better off fetching tuples, that is 
query(Project.id, Project.inp1, Project.inp2), etc., which eliminates all the 
ORM bookkeeping associated with mapped objects.

from sqlalchemy import *
from sqlalchemy.orm import mapper, sessionmaker
from time import clock
import os

if os.path.exists(test.db):
os.remove(test.db)

engine = create_engine('sqlite:///test.db', echo=False)
metadata = MetaData()

table = Table('projects', metadata,
   Column('id', Integer, primary_key=True),
   Column('inp1', String(50)),
   Column('inp2', String(50)),
   Column('inp3', String(50)),
   Column('inp4', String(50)),
   Column('inp5', String(50)),
)

class Project(object) :
   pass

mapper(Project, table)
metadata.create_all(engine)

t = []
for i in range(30) :
   t.append({inp1:str(i), inp2:str(i), inp3:str(i),
 inp4:str(i), inp5:str(i)})

c = clock()
engine.execute(table.insert(), t)
print Insert: +str(clock()-c)
session = sessionmaker(bind=engine)()

class UnmappedProject(object):
pass

c = clock()
res = []
for row in engine.execute(table.select()):
identity_key = row[table.c.id]
if identity_key in session.identity_map:
# here we'd use existing object
assert False
else:
obj = UnmappedProject()
for col in table.c:
setattr(obj, col.key, row[col])
res.append(obj)

print Sql query: +str(clock()-c)
c = clock()
res = session.query(Project).all()
print Session query: +str(clock()-c)



On Aug 12, 2011, at 8:51 AM, Massi wrote:

 Hi everyone,
 
 I'm doing some test to evaluate the performance of querying with
 sqlalchemy via ORM. I wrote a simple script to measure the execution
 time of a simple select query made on relatively small table (300 000
 records, 6 columns) in sqlite. Here is the script:
 
 from sqlalchemy import *
 from sqlalchemy.orm import mapper, sessionmaker
 from time import clock
 
 engine = create_engine('sqlite:///test.db', echo=False)
 metadata = MetaData()
 
 table = Table('projects', metadata,
Column('id', Integer, primary_key=True),
Column('inp1', String(50)),
Column('inp2', String(50)),
Column('inp3', String(50)),
Column('inp4', String(50)),
Column('inp5', String(50)),
 )
 
 class Project(object) :
pass
 
 mapper(Project, table)
 metadata.create_all(engine)
 
 t = []
 for i in range(30) :
t.append({inp1:str(i), inp2:str(i), inp3:str(i),
  inp4:str(i), inp5:str(i)})
 
 c = clock()
 engine.execute(table.insert(), t)
 print Insert: +str(clock()-c)
 session = sessionmaker(bind=engine)()
 c = clock()
 res = engine.execute(table.select()).fetchall()
 print Sql query: +str(clock()-c)
 c = clock()
 res = session.query(Project).all()
 print Session query: +str(clock()-c)
 
 On my PC (windows 7, 64-bit, intel i7 2.93 Ghz) this is the output:
 Insert: 3.41080167807
 Sql query: 1.26728367673
 Session query: 19.6452334842
 
 The execution time of the ORM query is about 20 times the SQL one, and
 this is definitely discouraging. So I guess if I'm doing something
 wrong or if there are some tricks when using ORM that I'm not
 considering. Any help is really appreciated.
 Thanks in advance!
 
 -- 
 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] joining to a from_statement

2011-08-12 Thread NiL
hi again,

after playing a while with PG, here is a SQL statement that outputs the 
expected result

SELECT groups_recursive.id AS groups_recursive_id, groups_recursive.name AS 
groups_recursive_name, groups_recursive.display_name AS 
groups_recursive_display_name
FROM groups_recursive JOIN (WITH RECURSIVE all_parents(id, rank) AS (
SELECT groups_recursive.id AS groups_recursive_id, 1
FROM groups_recursive, 
groups_recursive_parents__groups_recursive_children
WHERE groups_recursive_parents__groups_recursive_children.children_id = 
4
  AND groups_recursive_parents__groups_recursive_children.parents_id 
= groups_recursive.id
UNION
SELECT groups_recursive.id, rank+1
FROM all_parents, groups_recursive, 
groups_recursive_parents__groups_recursive_children
WHERE 
groups_recursive_parents__groups_recursive_children.children_id = 
all_parents.id
  AND groups_recursive_parents__groups_recursive_children.parents_id 
= groups_recursive.id
)
SELECT * FROM all_parents) AS anon_1 ON anon_1.id = groups_recursive.id;



and FYI, here is what the code is issuing (not functionnal)

SELECT groups_recursive.id AS groups_recursive_id 
FROM groups_recursive JOIN (WITH RECURSIVE all_parents(id, rank) AS (
(SELECT groups_recursive.id AS id, 1 
FROM groups_recursive_parents__groups_recursive_children 
WHERE groups_recursive.id = 
groups_recursive_parents__groups_recursive_children.parents_id AND 
groups_recursive_parents__groups_recursive_children.children_id = :groupid 
UNION SELECT groups_recursive.id AS id, rank + :rank_1 AS anon_2 
FROM groups_recursive_parents__groups_recursive_children 
WHERE groups_recursive_parents__groups_recursive_children.children_id = id 
AND groups_recursive_parents__groups_recursive_children.parents_id = 
groups_recursive.id)
)
SELECT * FROM all_parents) AS anon_1 ON anon_1.id = groups_recursive.id


we have problems such as 
column 1 does not exists
if we add groups_recursive in both FROM clauses

regards
Nil

-- 
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/-/cTkUMo18h_IJ.
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] Can pk_col function be adapted for Firebird?

2011-08-12 Thread Michael Bayer

On Aug 12, 2011, at 9:04 AM, werner wrote:

 On 08/12/2011 02:18 PM, werner wrote:
 I can't figure out how I could adapt the pk_col function on this page 
 http://www.sqlalchemy.org/trac/wiki/UsageRecipes/NamingConventions to handle 
 the Sequence definition needed for e.g. Firebird.
 
 At the point when Column is instantiated I don't have access to table.name 
 and I can't figure it out either how to do it in on_table_attach.
 
 Would appreciate any tips on this.
 Did a bit more searching and trying and came up with this:
 
 def pk_col(cls, **kw):
Produce a primary key column for a table.
 
e.g.::
 
pk_col()
 
is equivalent to::
 
Column(id, sa.BigInteger,
 doc = Primary key column for tablename,
 primary_key=True,
 sequence=sa.Sequence('tablename_id')
  )
 

kw['primary_key'] = True
c = sa.Column(sa.BigInteger(), sa.Sequence('seq_%s_%s' % 
 (cls.__tablename__,
   dbg.pkId)), **kw)
 
@sa.event.listens_for(c, before_parent_attach)
def on_table_attach(column, table):
column.name = column.key = dbg.pkId
column.doc = Primary key column for %r % table.name
 
c._creation_order = 0 # forces it to the top when using declarative
return c
 
 I.e. pass cls in so I can get to __tablename__.
 
 Is this an o.k. way of doing it or is there a better/cleaner way?

you should be able to set the Sequence name directly in the attach event 
(starting with a fake name).   not sure how you're using pk_col() above (how it 
gets at 'cls').

Its also possible to create + attach the Sequence to the Column after the fact 
but I don't know that the public API is there for that quite yet.   (i think 
calling seq._set_parent(column) would be sufficient ...)






-- 
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] joining to a from_statement

2011-08-12 Thread Michael Bayer
sure, couple of small adjustments, attached




On Aug 12, 2011, at 10:44 AM, NiL wrote:

 hi again,
 
 after playing a while with PG, here is a SQL statement that outputs the 
 expected result
 
 SELECT groups_recursive.id AS groups_recursive_id, groups_recursive.name AS 
 groups_recursive_name, groups_recursive.display_name AS 
 groups_recursive_display_name
 FROM groups_recursive JOIN (WITH RECURSIVE all_parents(id, rank) AS (
 SELECT groups_recursive.id AS groups_recursive_id, 1
 FROM groups_recursive, groups_recursive_parents__groups_recursive_children
 WHERE groups_recursive_parents__groups_recursive_children.children_id = 4
   AND groups_recursive_parents__groups_recursive_children.parents_id 
 = groups_recursive.id
 UNION
 SELECT groups_recursive.id, rank+1
 FROM all_parents, groups_recursive, 
 groups_recursive_parents__groups_recursive_children
 WHERE groups_recursive_parents__groups_recursive_children.children_id 
 = all_parents.id
   AND groups_recursive_parents__groups_recursive_children.parents_id 
 = groups_recursive.id
 )
 SELECT * FROM all_parents) AS anon_1 ON anon_1.id = groups_recursive.id;
 
 
 
 and FYI, here is what the code is issuing (not functionnal)
 
 SELECT groups_recursive.id AS groups_recursive_id 
 FROM groups_recursive JOIN (WITH RECURSIVE all_parents(id, rank) AS (
 (SELECT groups_recursive.id AS id, 1 
 FROM groups_recursive_parents__groups_recursive_children 
 WHERE groups_recursive.id = 
 groups_recursive_parents__groups_recursive_children.parents_id AND 
 groups_recursive_parents__groups_recursive_children.children_id = :groupid 
 UNION SELECT groups_recursive.id AS id, rank + :rank_1 AS anon_2 
 FROM groups_recursive_parents__groups_recursive_children 
 WHERE groups_recursive_parents__groups_recursive_children.children_id = id 
 AND groups_recursive_parents__groups_recursive_children.parents_id = 
 groups_recursive.id)
 )
 SELECT * FROM all_parents) AS anon_1 ON anon_1.id = groups_recursive.id
 
 
 we have problems such as 
 column 1 does not exists
 if we add groups_recursive in both FROM clauses
 
 regards
 Nil
 
 -- 
 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/-/cTkUMo18h_IJ.
 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.

On Aug 12, 2011, at 10:44 AM, NiL wrote:hi again,after playing a while with PG, here is a SQL statement that outputs the expected resultSELECT groups_recursive.id AS groups_recursive_id, groups_recursive.name AS groups_recursive_name, groups_recursive.display_name AS groups_recursive_display_nameFROM groups_recursive JOIN (WITH RECURSIVE all_parents(id, rank) AS ( SELECT groups_recursive.id AS groups_recursive_id, 1 FROM groups_recursive, groups_recursive_parents__groups_recursive_children WHERE groups_recursive_parents__groups_recursive_children.children_id = 4 AND groups_recursive_parents__groups_recursive_children.parents_id = groups_recursive.id UNION SELECT groups_recursive.id, rank+1 FROM all_parents, groups_recursive, groups_recursive_parents__groups_recursive_children WHERE groups_recursive_parents__groups_recursive_children.children_id = all_parents.id AND groups_recursive_parents__groups_recursive_children.parents_id = groups_recursive.id )SELECT * FROM all_parents) AS anon_1 ON anon_1.id = groups_recursive.id;and FYI, here is what the code is issuing (not functionnal)SELECT groups_recursive.id AS groups_recursive_id FROM groups_recursive JOIN (WITH RECURSIVE all_parents(id, rank) AS ((SELECT groups_recursive.id AS id, "1" FROM groups_recursive_parents__groups_recursive_children WHERE groups_recursive.id = groups_recursive_parents__groups_recursive_children.parents_id AND groups_recursive_parents__groups_recursive_children.children_id = :groupid UNION SELECT groups_recursive.id AS id, rank + :rank_1 AS anon_2 FROM groups_recursive_parents__groups_recursive_children WHERE groups_recursive_parents__groups_recursive_children.children_id = id AND groups_recursive_parents__groups_recursive_children.parents_id = groups_recursive.id))SELECT * FROM all_parents) AS anon_1 ON anon_1.id = groups_recursive.idwe have problems such as column "1" does not existsif we add groups_recursive in both FROM clausesregardsNil

-- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view 

Re: [sqlalchemy] Can pk_col function be adapted for Firebird?

2011-08-12 Thread werner

On 08/12/2011 04:46 PM, Michael Bayer wrote:

On Aug 12, 2011, at 9:04 AM, werner wrote:


On 08/12/2011 02:18 PM, werner wrote:

I can't figure out how I could adapt the pk_col function on this page 
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/NamingConventions to handle 
the Sequence definition needed for e.g. Firebird.

At the point when Column is instantiated I don't have access to table.name 
and I can't figure it out either how to do it in on_table_attach.

Would appreciate any tips on this.

Did a bit more searching and trying and came up with this:

def pk_col(cls, **kw):
Produce a primary key column for a table.

e.g.::

pk_col()

is equivalent to::

Column(id, sa.BigInteger,
 doc = Primary key column fortablename,
 primary_key=True,
 sequence=sa.Sequence('tablename_id')
  )


kw['primary_key'] = True
c = sa.Column(sa.BigInteger(), sa.Sequence('seq_%s_%s' % (cls.__tablename__,
   dbg.pkId)), **kw)

@sa.event.listens_for(c, before_parent_attach)
def on_table_attach(column, table):
column.name = column.key = dbg.pkId
column.doc = Primary key column for %r % table.name

c._creation_order = 0 # forces it to the top when using declarative
return c

I.e. pass cls in so I can get to __tablename__.

Is this an o.k. way of doing it or is there a better/cleaner way?

you should be able to set the Sequence name directly in the attach event 
(starting with a fake name).   not sure how you're using pk_col() above (how it 
gets at 'cls').

I had this as part of the declarative base.
@sad.declared_attr
def id(cls):
# use a method so that the pk_col() returned
# here is the one used instead of a copy
return pk_col(cls)


Its also possible to create + attach the Sequence to the Column after the fact 
but I don't know that the public API is there for that quite yet.   (i think 
calling seq._set_parent(column) would be sufficient ...)


Yeap, great.

got this now:
@sa.event.listens_for(c, before_parent_attach)
def on_table_attach(column, table):
column.name = column.key = dbg.pkId
column.doc = Primary key column for %r % table.name
seq = sa.Sequence('seq_%s_%s' % (table.name,
 dbg.pkId))
seq._set_parent(column)

I guess I just have to watch out for whenever you provide a public API 
for _set_parent.


Thanks
Werner

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



Re: [sqlalchemy] joining to a from_statement

2011-08-12 Thread NiL
thank you so much Michael !! much better

few last things are 

WITH RECURSIVE all_parents(id, rank) AS 
SELECT groups_recursive.id, 1 
FROM groups_recursive, groups_recursive_parents__groups_recursive_children 

quotes around the 1, this leads to ERROR:  column 1 does not exist

+

the param :rank_1 feels weird, it is generated by the rank + 1 in
 union(
 select([groups.c.id, rank + 1]).\

but it feels really close to the solution

best
NiL

-- 
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/-/Q0jzhkVnW6gJ.
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: Softcoding .filter(...)

2011-08-12 Thread RVince
Mark, yes, in part. What I cannot figure out -- and am not sure this
is possible with SQLAlchemy, is to go from strings, as returned from
HTTP Post's, representing the fields in the table (for the left side
of the relation) to the actual statemetn itself. In other words, if
the HTTP post calls for campring the Lastname column for equality to
the value smith, I can build the == 'smith' portfion, but how do I
Ibuild a String for the column name, into the value for that column
name so as to comport with SQLAlchemy's sytnax requirements. I don;t
think I can just have a query, q, and then say:
q = q.filter(lastname == 'smith')
can I? RVince

On Aug 12, 10:33 am, Mark Erbaugh m...@microenh.com wrote:
 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.



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

2011-08-12 Thread NiL
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 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/-/DWEguyVv4dIJ.
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(...)

2011-08-12 Thread Mark Erbaugh

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.



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

2011-08-12 Thread Wichert Akkerman

On 08/12/2011 05:52 PM, 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


Alternatively if you are only interested in equality you can skip the 
getattr and use filter_by in combination with python's keyword argument 
handling:


Session.query(klass).filter_by(**{field: value})

Wichert.

--
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] joining to a from_statement

2011-08-12 Thread Michael Bayer
its at the point where you should be able to tweak it using documented 
processes.  column() applies quotes for example, whereas literal_column(1) 
would not.  Same for an expression x + 1 will turn the 1 into a bind, would 
not if you again use literal_column()

http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.expression.column
http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.expression.literal_column



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

 thank you so much Michael !! much better
 
 few last things are 
 
 WITH RECURSIVE all_parents(id, rank) AS 
 SELECT groups_recursive.id, 1 
 FROM groups_recursive, groups_recursive_parents__groups_recursive_children 
 
 quotes around the 1, this leads to ERROR:  column 1 does not exist
 
 +
 
 the param :rank_1 feels weird, it is generated by the rank + 1 in
  union(
  select([groups.c.id, rank + 1]).\
 
 but it feels really close to the solution
 
 best
 NiL
 
 -- 
 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/-/Q0jzhkVnW6gJ.
 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] bulk selection

2011-08-12 Thread Eduardo
Dear All,
I have a list of elements for which I need to establish if they are in
a tadabase. I can make for each element a separate query but I am
afraid that that is not the best approach what is the best practice in
this case?
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] joining to a from_statement

2011-08-12 Thread NiL
yes, many thanks

I now have a method in my Group class (still in elixir syntax)

def hierarchy_ng(self):
with CommonTableExpression.create(
all_parents, [id, rank]) as all_parents:
rank = literal_column(rank)
groups = Group.table
groups_assoc = 
Group._descriptor.find_relationship('parents').table

s = select([groups.c.id, literal_column(1)]).\
where(groups.c.id==groups_assoc.c.parents_id).\

where(groups_assoc.c.children_id==bindparam(groupid)).\
correlate(None).\
union(
select([groups.c.id, rank + 
literal_column(1)]).\
where(groups_assoc.c.children_id==
all_parents.c.id).\
where(groups_assoc.c.parents_id==groups.c.id
).\
correlate(None)
).params(groupid=self.id)

all_parents = SelectFromCTE(all_parents, s)
all_parents = all_parents.alias()
q = Group.query.join((all_parents, all_parents.c.id==Group.id))
return q.all()

and it's ok now !

-- 
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/-/ji9w6r2l09IJ.
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: Softcoding .filter(...)

2011-08-12 Thread RVince
Wickert, can you give me an example ? I'm a little confused by this
posts of yours. RVince

On Aug 12, 12:20 pm, Wichert Akkerman wich...@wiggy.net wrote:
 On 08/12/2011 05:52 PM, 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

 Alternatively if you are only interested in equality you can skip the
 getattr and use filter_by in combination with python's keyword argument
 handling:

      Session.query(klass).filter_by(**{field: value})

 Wichert.

-- 
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: Softcoding .filter(...)

2011-08-12 Thread RVince
Thanks to all you guys. Really. I didn't think I would be able to do
this! RVince

-- 
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_by argument as a result of a query

2011-08-12 Thread Eduardo
Dear all,
I am trying to limit group_by function only on the rows that satisfy
certain query
if I use group_by(table.c.something) this pertains to whole table.
I tried to use elements of the tuple rendered as a result of a query
as arguments (as it has been suggested on this forum) but it did not
work.
Is this possible at all?
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] The capacity of the session

2011-08-12 Thread Eduardo
Dear all,
How can I determine the number of objects (the memory capacity) that a
session can take? How can I determine the size of an object?
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] bulk selection

2011-08-12 Thread Mariano Mara
On 12.08.11 09:41, Eduardo wrote:
 Dear All,
 I have a list of elements for which I need to establish if they are in
 a tadabase. I can make for each element a separate query but I am
 afraid that that is not the best approach what is the best practice in
 this case?
 Thanks
 

Depending on the size of the list I would use the in_ operator[1]. If you're
talking about a really long list [2] you might drop everything to a temporary 
list
and join from there.

Mariano

[1] http://www.sqlalchemy.org/docs/orm/tutorial.html#common-filter-operators
[2] in old days there were limits to the amount of items in an in operator,
I'm not sure if it still holds nowadays.


-- 
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_by argument as a result of a query

2011-08-12 Thread Michael Bayer
can you please illustrate a simple SQL statement that illustrates what you are 
trying to achieve ?   


On Aug 12, 2011, at 1:00 PM, Eduardo wrote:

 Dear all,
 I am trying to limit group_by function only on the rows that satisfy
 certain query
 if I use group_by(table.c.something) this pertains to whole table.
 I tried to use elements of the tuple rendered as a result of a query
 as arguments (as it has been suggested on this forum) but it did not
 work.
 Is this possible at all?
 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.
 

-- 
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] The capacity of the session

2011-08-12 Thread Michael Bayer
I'm not familiar with any method for doing that, and it depends mostly on how 
much data each of your objects contains - the Session/ORM will have a small 
amount of memory overhead beyond that which is of a fixed size.

At best I'd say you can estimate the memory size per object by running a 
program, put 1000 objects in the Session, then run top and see the memory, 
then put 5000 in, compare, 1, compare, to get an approximation.This 
would be specific to the mappings you're using as well as the data being loaded 
into them.




On Aug 12, 2011, at 1:03 PM, Eduardo wrote:

 Dear all,
 How can I determine the number of objects (the memory capacity) that a
 session can take? How can I determine the size of an object?
 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.
 

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

2011-08-12 Thread Mark Erbaugh
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

2011-08-12 Thread Michael Bayer

On Aug 12, 2011, at 5:05 PM, Mark Erbaugh 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].


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

2011-08-12 Thread Mark Erbaugh

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.



[sqlalchemy] string concatentation of multiple columns in select statement

2011-08-12 Thread nospam
I'm trying to do something like this in sqlalchemy:

select (score/10)*10 || '-' || (score/10)*10+9 as scorerange,
count(*)from scoresgroup by score/10 order by 1

which should give:
scorerange | count
 +---
 0-9|11
10-19 |14
20-29 | 3
30-39 | 2

Any ideas on how to do the string concatentation in the ranges?

-- 
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] string concatentation of multiple columns in select statement

2011-08-12 Thread Michael Bayer

On Aug 12, 2011, at 6:43 PM, nospam wrote:

 I'm trying to do something like this in sqlalchemy:
 
 select (score/10)*10 || '-' || (score/10)*10+9 as scorerange,
 count(*)from scoresgroup by score/10 order by 1
 
 which should give:
 scorerange | count
 +---
 0-9|11
 10-19 |14
 20-29 | 3
 30-39 | 2
 
 Any ideas on how to do the string concatentation in the ranges?

the + operator resolves to the concatenation operator if used against an 
expression with a string type, or if using untyped elements concat() will get 
you there:

from sqlalchemy.sql import column

column('foo', String) + bar
column('foo').concat(bar)

your expression above appears to be evaluating a numeric against a string 
(so...this is MySQL ? :) )   you might consider calling cast(expr, String) on 
the numeric value before evaluating in a string context.

((score / 10) * 10).concat(-).concat((score / 10)*10) + 9).label('scorerange')


from sqlalchemy import cast, String
cast((score / 10) * 10, String).concat(-).concat(cast((score / 10)*10) + 9, 
String)).label('scorerange')










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