[sqlalchemy] Re: Creating SQL Expression

[Gunnlaugur Thor Briem]

You can get the column object from the string, using:

xyz.c[column_name]

Do you mean that this:

columns = [a,b,c]
operators = ['+','-']

should result in xyz.c.a + xyz.c.b - xyz.c.c ?

To do that, something like this works:

columns = [a,b,c]
operators = ['+','-']
colnames_and_ops = zip(columns[1:], operators)
import operator
opdict = {
'+' : operator.add,
'-' : operator.sub
}
columns = [xyz.c[name] for name in columns]
operators = [opdict[name] for name in operators]
ops_cols = zip(operators, columns[1:])
expr = reduce(lambda expr, op_col: op_col[0](expr, op_col[1]), ops_cols,
columns[0])

Hope that helps,

- Gulli



On Thu, Feb 26, 2009 at 5:47 AM, Ashish Bhatia
ashishsinghbha...@gmail.comwrote:


 This works fine
 But in the mine case
 columns = [a,b,c]
 operator = ['+','-']

 comes in the list

 And it can go to n number.

 So while adding it creates a problem

 My approach

 looping on columns i append it in to the table and hence making the
 object

 i can join them with operator to form the a+b-c but in this a b c
 becomes string which is not desirable  i want object here

 i hope this will clear the picture

 On Feb 25, 6:40 pm, Gunnlaugur Thor Briem gunnlau...@gmail.com
 wrote:
  You can sum the column objects directly, a+b, producing a
  sqlalchemy.sql.expression._BinaryExpression object.
 
  t = Table('bobloblaw', MetaData(), Column('a', Integer), Column('b',
  Integer), Column('c', Integer))
  t.c.a + t.c.b
  # evaluates to sqlalchemy.sql.expression._BinaryExpression object at
  0x1ec9ff0
  print t.c.a + t.c.b
  #  bobloblaw.a + bobloblaw.b
 
  On Wed, Feb 25, 2009 at 1:25 PM, Ashish Bhatia
  ashishsinghbha...@gmail.comwrote:
 
 
 
   The problem is still their.
 
   The two seprate list of
   columns = List of sqlalchem object
   operator = ['+'','-']
 
   using join to join them will convert the columns object to string
   which is not desirable.
 
   Any way to fix this.
 
   On Feb 25, 3:54 pm, Ashish Bhatia ashishsinghbha...@gmail.com wrote:
sorry its resolved and working
 
On Feb 25, 12:20 pm, Ash ashishsinghbha...@gmail.com wrote:
 
 Hello ,
 
 I am trying to make query like
 
 select (a+b) from xyz;
 
 to do this
 
 xyz = sqlalchemy.Table('xyz',metadata)
 
 a = sqlalchemy.Column('a', sqlalchemy.Integer)
 xyz.append_column(a)
 b = sqlalchemy.Column('b', sqlalchemy.Integer)
 xyz.append_column(b)
 
 column = [(a + b)]
 select = sqlalchemy.select(from_obj=xyz,
 columns=column,distinct=True)
 
 This works fine for me.
 
 Now when the columns a and b are dynamic (Enter by the user in form
 of
 string) and the operator too comes from user
 
 columns_list = ['a','b']
 operator = ['+']
 
 like this i get the input
 
 so i make the loop and make
 
 for both the columns something like this
 columns = []
 for x in column_list :
 t  = sqlalchemy.Column(x, sqlalchemy.Integer)
 xyz.append_column(a)
 columns.append(t)
 
 so now
 how to add + to make the quer run
 
 Thanks in the 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
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Creating SQL Expression

[Ashish Bhatia]

sorry its resolved and working

On Feb 25, 12:20 pm, Ash ashishsinghbha...@gmail.com wrote:
 Hello ,

 I am trying to make query like

 select (a+b) from xyz;

 to do this

 xyz = sqlalchemy.Table('xyz',metadata)

 a = sqlalchemy.Column('a', sqlalchemy.Integer)
 xyz.append_column(a)
 b = sqlalchemy.Column('b', sqlalchemy.Integer)
 xyz.append_column(b)

 column = [(a + b)]
 select = sqlalchemy.select(from_obj=xyz, columns=column,distinct=True)

 This works fine for me.

 Now when the columns a and b are dynamic (Enter by the user in form of
 string) and the operator too comes from user

 columns_list = ['a','b']
 operator = ['+']

 like this i get the input

 so i make the loop and make

 for both the columns something like this
 columns = []
 for x in column_list :
     t  = sqlalchemy.Column(x, sqlalchemy.Integer)
     xyz.append_column(a)
     columns.append(t)

 so now
 how to add + to make the quer run

 Thanks in the 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
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Creating SQL Expression

[Ashish Bhatia]

The problem is still their.

The two seprate list of
columns = List of sqlalchem object
operator = ['+'','-']

using join to join them will convert the columns object to string
which is not desirable.

Any way to fix this.

On Feb 25, 3:54 pm, Ashish Bhatia ashishsinghbha...@gmail.com wrote:
 sorry its resolved and working

 On Feb 25, 12:20 pm, Ash ashishsinghbha...@gmail.com wrote:

  Hello ,

  I am trying to make query like

  select (a+b) from xyz;

  to do this

  xyz = sqlalchemy.Table('xyz',metadata)

  a = sqlalchemy.Column('a', sqlalchemy.Integer)
  xyz.append_column(a)
  b = sqlalchemy.Column('b', sqlalchemy.Integer)
  xyz.append_column(b)

  column = [(a + b)]
  select = sqlalchemy.select(from_obj=xyz, columns=column,distinct=True)

  This works fine for me.

  Now when the columns a and b are dynamic (Enter by the user in form of
  string) and the operator too comes from user

  columns_list = ['a','b']
  operator = ['+']

  like this i get the input

  so i make the loop and make

  for both the columns something like this
  columns = []
  for x in column_list :
      t  = sqlalchemy.Column(x, sqlalchemy.Integer)
      xyz.append_column(a)
      columns.append(t)

  so now
  how to add + to make the quer run

  Thanks in the 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
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Creating SQL Expression

[King Simon-NFHD78]

Could you use the python 'operator' module 
(http://docs.python.org/library/operator.html)?

Eg. (untested):

import operator

operations = {
'+': operator.add,
'-': operator.sub,
# etc.
}

def combine_columns(op, *cols):
return operations[op](*cols)

sum_column = combine_columns('+', a, b)

I think that should work.

Simon

 -Original Message-
 From: sqlalchemy@googlegroups.com 
 [mailto:sqlalch...@googlegroups.com] On Behalf Of Ashish Bhatia
 Sent: 25 February 2009 13:26
 To: sqlalchemy
 Subject: [sqlalchemy] Re: Creating SQL Expression
 
 
 The problem is still their.
 
 The two seprate list of
 columns = List of sqlalchem object
 operator = ['+'','-']
 
 using join to join them will convert the columns object to string
 which is not desirable.
 
 Any way to fix this.
 
 On Feb 25, 3:54 pm, Ashish Bhatia ashishsinghbha...@gmail.com wrote:
  sorry its resolved and working
 
  On Feb 25, 12:20 pm, Ash ashishsinghbha...@gmail.com wrote:
 
   Hello ,
 
   I am trying to make query like
 
   select (a+b) from xyz;
 
   to do this
 
   xyz = sqlalchemy.Table('xyz',metadata)
 
   a = sqlalchemy.Column('a', sqlalchemy.Integer)
   xyz.append_column(a)
   b = sqlalchemy.Column('b', sqlalchemy.Integer)
   xyz.append_column(b)
 
   column = [(a + b)]
   select = sqlalchemy.select(from_obj=xyz, 
 columns=column,distinct=True)
 
   This works fine for me.
 
   Now when the columns a and b are dynamic (Enter by the 
 user in form of
   string) and the operator too comes from user
 
   columns_list = ['a','b']
   operator = ['+']
 
   like this i get the input
 
   so i make the loop and make
 
   for both the columns something like this
   columns = []
   for x in column_list :
       t  = sqlalchemy.Column(x, sqlalchemy.Integer)
       xyz.append_column(a)
       columns.append(t)
 
   so now
   how to add + to make the quer run
 
   Thanks in the 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
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Creating SQL Expression

[Gunnlaugur Thor Briem]

Whoops, premature send, sorry. For an arbitrary list of columns, such as t.c
(the column collection) or other SQL selectables, such as the above binary
expressions, you can use sum(columns). E.g.:

t = Table('bobloblaw', MetaData(), Column('a', Integer), Column('b',
Integer), Column('c', Integer))
e = create_engine('sqlite:///:memory:')
e.execute(t.insert(), [{'a':1, 'b':2, 'c':4}])
e.execute(select([sum(t.c)])).fetchall() # equiv. to SELECT a+b+c FROM t
# [(7,)]

Regards,

- Gulli



On Wed, Feb 25, 2009 at 1:40 PM, Gunnlaugur Thor Briem gunnlau...@gmail.com
 wrote:

 You can sum the column objects directly, a+b, producing a
 sqlalchemy.sql.expression._BinaryExpression object.

 t = Table('bobloblaw', MetaData(), Column('a', Integer), Column('b',
 Integer), Column('c', Integer))
 t.c.a + t.c.b
 # evaluates to sqlalchemy.sql.expression._BinaryExpression object at
 0x1ec9ff0
 print t.c.a + t.c.b
 #  bobloblaw.a + bobloblaw.b



 On Wed, Feb 25, 2009 at 1:25 PM, Ashish Bhatia 
 ashishsinghbha...@gmail.com wrote:


 The problem is still their.

 The two seprate list of
 columns = List of sqlalchem object
 operator = ['+'','-']

 using join to join them will convert the columns object to string
 which is not desirable.

 Any way to fix this.

 On Feb 25, 3:54 pm, Ashish Bhatia ashishsinghbha...@gmail.com wrote:
  sorry its resolved and working
 
  On Feb 25, 12:20 pm, Ash ashishsinghbha...@gmail.com wrote:
 
   Hello ,
 
   I am trying to make query like
 
   select (a+b) from xyz;
 
   to do this
 
   xyz = sqlalchemy.Table('xyz',metadata)
 
   a = sqlalchemy.Column('a', sqlalchemy.Integer)
   xyz.append_column(a)
   b = sqlalchemy.Column('b', sqlalchemy.Integer)
   xyz.append_column(b)
 
   column = [(a + b)]
   select = sqlalchemy.select(from_obj=xyz, columns=column,distinct=True)
 
   This works fine for me.
 
   Now when the columns a and b are dynamic (Enter by the user in form of
   string) and the operator too comes from user
 
   columns_list = ['a','b']
   operator = ['+']
 
   like this i get the input
 
   so i make the loop and make
 
   for both the columns something like this
   columns = []
   for x in column_list :
   t  = sqlalchemy.Column(x, sqlalchemy.Integer)
   xyz.append_column(a)
   columns.append(t)
 
   so now
   how to add + to make the quer run
 
   Thanks in the 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
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Creating SQL Expression

[Ashish Bhatia]

This works fine
But in the mine case
columns = [a,b,c]
operator = ['+','-']

comes in the list

And it can go to n number.

So while adding it creates a problem

My approach

looping on columns i append it in to the table and hence making the
object

i can join them with operator to form the a+b-c but in this a b c
becomes string which is not desirable  i want object here

i hope this will clear the picture

On Feb 25, 6:40 pm, Gunnlaugur Thor Briem gunnlau...@gmail.com
wrote:
 You can sum the column objects directly, a+b, producing a
 sqlalchemy.sql.expression._BinaryExpression object.

 t = Table('bobloblaw', MetaData(), Column('a', Integer), Column('b',
 Integer), Column('c', Integer))
 t.c.a + t.c.b
 # evaluates to sqlalchemy.sql.expression._BinaryExpression object at
 0x1ec9ff0
 print t.c.a + t.c.b
 #  bobloblaw.a + bobloblaw.b

 On Wed, Feb 25, 2009 at 1:25 PM, Ashish Bhatia
 ashishsinghbha...@gmail.comwrote:



  The problem is still their.

  The two seprate list of
  columns = List of sqlalchem object
  operator = ['+'','-']

  using join to join them will convert the columns object to string
  which is not desirable.

  Any way to fix this.

  On Feb 25, 3:54 pm, Ashish Bhatia ashishsinghbha...@gmail.com wrote:
   sorry its resolved and working

   On Feb 25, 12:20 pm, Ash ashishsinghbha...@gmail.com wrote:

Hello ,

I am trying to make query like

select (a+b) from xyz;

to do this

xyz = sqlalchemy.Table('xyz',metadata)

a = sqlalchemy.Column('a', sqlalchemy.Integer)
xyz.append_column(a)
b = sqlalchemy.Column('b', sqlalchemy.Integer)
xyz.append_column(b)

column = [(a + b)]
select = sqlalchemy.select(from_obj=xyz, columns=column,distinct=True)

This works fine for me.

Now when the columns a and b are dynamic (Enter by the user in form of
string) and the operator too comes from user

columns_list = ['a','b']
operator = ['+']

like this i get the input

so i make the loop and make

for both the columns something like this
columns = []
for x in column_list :
    t  = sqlalchemy.Column(x, sqlalchemy.Integer)
    xyz.append_column(a)
    columns.append(t)

so now
how to add + to make the quer run

Thanks in the 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
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Creating SQL Expression

[Ashish Bhatia]

This works fine
Thanks for the idea i will try it for the case and get back to here in
the case of problem
On Feb 25, 6:32 pm, King Simon-NFHD78 simon.k...@motorola.com
wrote:
 Could you use the python 'operator' module 
 (http://docs.python.org/library/operator.html)?

 Eg. (untested):

 import operator

 operations = {
     '+': operator.add,
     '-': operator.sub,
     # etc.

 }

 def combine_columns(op, *cols):
     return operations[op](*cols)

 sum_column = combine_columns('+', a, b)

 I think that should work.

 Simon

  -Original Message-
  From: sqlalchemy@googlegroups.com
  [mailto:sqlalch...@googlegroups.com] On Behalf Of Ashish Bhatia
  Sent: 25 February 2009 13:26
  To: sqlalchemy
  Subject: [sqlalchemy] Re: Creating SQL Expression

  The problem is still their.

  The two seprate list of
  columns = List of sqlalchem object
  operator = ['+'','-']

  using join to join them will convert the columns object to string
  which is not desirable.

  Any way to fix this.

  On Feb 25, 3:54 pm, Ashish Bhatia ashishsinghbha...@gmail.com wrote:
   sorry its resolved and working

   On Feb 25, 12:20 pm, Ash ashishsinghbha...@gmail.com wrote:

Hello ,

I am trying to make query like

select (a+b) from xyz;

to do this

xyz = sqlalchemy.Table('xyz',metadata)

a = sqlalchemy.Column('a', sqlalchemy.Integer)
xyz.append_column(a)
b = sqlalchemy.Column('b', sqlalchemy.Integer)
xyz.append_column(b)

column = [(a + b)]
select = sqlalchemy.select(from_obj=xyz,
  columns=column,distinct=True)

This works fine for me.

Now when the columns a and b are dynamic (Enter by the
  user in form of
string) and the operator too comes from user

columns_list = ['a','b']
operator = ['+']

like this i get the input

so i make the loop and make

for both the columns something like this
columns = []
for x in column_list :
    t  = sqlalchemy.Column(x, sqlalchemy.Integer)
    xyz.append_column(a)
    columns.append(t)

so now
how to add + to make the quer run

Thanks in the 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
-~--~~~~--~~--~--~---