[sqlalchemy] Re: Dynamic making of the where clause
Thanks for the reply but still m not able to make as i stated in the example regarding the input On Jun 26, 8:27 pm, "Michael Bayer" wrote: > Ashish Bhatia wrote: > > > :( Is it possible ! > > > On Jun 23, 3:32 pm, Ash wrote: > >> Hello, > > >> I am tryin to make the dynamic where clause using append_whereclause. > > >> But how i can do that, For eg : > > >> I have a==b and c in (1,2,3,4) or d like %s > > >> So i made three sqlalchemy expression > > >> 1. a==b > >> 2. c in (1,2,3,4) [ using in_] > >> 3. d like %s [using like] > > >> now i want this 3 to stuff in where clause . > > >> I created select like sel = select() > > >> How can i make the where clause which have and and or both uisng > >> append_whereclause. > > construct using and_() and or_(), then use a single where() to set it. > > > > > > >> Thnks 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Dynamic making of the where clause
Thanks But i get input as
[['a==b'],'and', [],'and','(',
['x==y'],'or',['t==y'],')']
for a==b and id in(1,2,3,4,5) and (x==y or t==y)
Now from this ;list input i want to make the where expression.
I tried making all to the string but the objects (binary expression)
create problem.
On Jun 26, 5:16 pm, "King Simon-NFHD78"
wrote:
> > On Jun 23, 3:32 pm, Ash wrote:
> > > Hello,
>
> > > I am tryin to make the dynamic where clause using
> > append_whereclause.
>
> > > But how i can do that, For eg :
>
> > > I have a==b and c in (1,2,3,4) or d like %s
>
> > > So i made three sqlalchemy expression
>
> > > 1. a==b
> > > 2. c in (1,2,3,4) [ using in_]
> > > 3. d like %s [using like]
>
> > > now i want this 3 to stuff in where clause .
>
> > > I created select like sel = select()
>
> > > How can i make the where clause which have and and or both uisng
> > > append_whereclause.
>
> I think append_whereclause always uses AND, so you need to connect these
> conditions into a single clause and call append_whereclause just once.
>
> It's not clear how you want the grouping of your conditions to work. Is it:
>
> (a==b AND c in (1, 2, 3, 4)) OR d like %s
>
> Or
>
> a==b AND (c in (1, 2, 3, 4) OR d like %s)
>
> I think you need to do something like this (for the first option):
>
> from sqlalchemy import and_, or_
>
> clause = or_(and_(a == b, c.in_([1, 2, 3, 4])),
> d.like(something))
> sel.append_whereclause(clause)
>
> You may also be able to use the '&' and '|' operators, as long as you are
> careful with brackets. See the docs
> athttp://www.sqlalchemy.org/docs/05/sqlexpression.html#conjunctions
>
> Hope that helps,
>
> Simon
--~--~-~--~~~---~--~~
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: Dynamic making of the where clause
:( Is it possible ! On Jun 23, 3:32 pm, Ash wrote: > Hello, > > I am tryin to make the dynamic where clause using append_whereclause. > > But how i can do that, For eg : > > I have a==b and c in (1,2,3,4) or d like %s > > So i made three sqlalchemy expression > > 1. a==b > 2. c in (1,2,3,4) [ using in_] > 3. d like %s [using like] > > now i want this 3 to stuff in where clause . > > I created select like sel = select() > > How can i make the where clause which have and and or both uisng > append_whereclause. > > Thnks 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: insert statment
yeah thnks for the help.
one more dbt. do i have to provide values as dict always ie. with
column name. Can i give direct values like we do in normal sql.?
thnks in advance
On Jun 9, 9:02 am, Michael Bayer wrote:
> you need to use the executemany form described
> athttp://www.sqlalchemy.org/docs/05/sqlexpression.html#executing-multip...
> .
>
> On Jun 8, 2009, at 11:54 PM, Ashish Bhatia wrote:
>
>
>
>
>
> > Sorry, Its my typing mistake :( . I put : insted , . But still my
> > question yeat remained unanswered. :(
>
> > On Jun 8, 7:50 pm, Didip Kerabat wrote:
> >> You have Syntax Error here:
>
> >> ('sdsd':'sdsds')
>
> >> That one should be tuple right?
>
> >> - Didip -
>
> >> On Mon, Jun 8, 2009 at 6:14 AM, Ash
> >> wrote:
>
> >>> Hello ,
>
> >>> I am trying to insert in the table using two ways in the values
> >>> which
> >>> i show below
>
> >>> engine = sqlalchemy.create_engine()
> >>> metadata = MetaData()
>
> >>> t1 = Table('master',metadata) # assume master has 2 feilds name ,
> >>> city
>
> >>> t1.insert({'name':'','city':'bank'})
>
> >>> engine,execute(t1)
>
> >>> This works for fine me.
>
> >>> If i make values like this
> >>> tt = [('asasas','belhium'),('sdsd':'sdsds')]
>
> >>> t1.insert(values=tt)
>
> >>> i get error
> >>> sqlalchemy.exceptions.ProgrammingError: (ProgrammingError) syntax
> >>> error at or near ")"
> >>> LINE 1: INSERT INTO abc () VALUES ()
> >>> ^
> >>> 'INSERT INTO abc () VALUES ()' {}
>
> >>> Can any one guide whts wrong... i jnow value is not being passed so
> >>> anyother way.
--~--~-~--~~~---~--~~
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: insert statment
Sorry, Its my typing mistake :( . I put : insted , . But still my
question yeat remained unanswered. :(
On Jun 8, 7:50 pm, Didip Kerabat wrote:
> You have Syntax Error here:
>
> ('sdsd':'sdsds')
>
> That one should be tuple right?
>
> - Didip -
>
>
>
> On Mon, Jun 8, 2009 at 6:14 AM, Ash wrote:
>
> > Hello ,
>
> > I am trying to insert in the table using two ways in the values which
> > i show below
>
> > engine = sqlalchemy.create_engine()
> > metadata = MetaData()
>
> > t1 = Table('master',metadata) # assume master has 2 feilds name , city
>
> > t1.insert({'name':'','city':'bank'})
>
> > engine,execute(t1)
>
> > This works for fine me.
>
> > If i make values like this
> > tt = [('asasas','belhium'),('sdsd':'sdsds')]
>
> > t1.insert(values=tt)
>
> > i get error
> > sqlalchemy.exceptions.ProgrammingError: (ProgrammingError) syntax
> > error at or near ")"
> > LINE 1: INSERT INTO abc () VALUES ()
> > ^
> > 'INSERT INTO abc () VALUES ()' {}
>
> > Can any one guide whts wrong... i jnow value is not being passed so
> > anyother way.
--~--~-~--~~~---~--~~
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: making join with using clause
Thanks. I have one more question do we have the option of making different type of joins ? i have seen in docs it says it have join and outer join only What abbt left outer right outer natural cross and many ... On May 28, 9:00 pm, "Michael Bayer" wrote: > SQLAlchemy SQL expressions have no need for JOIN USING which is just a > typing saver in straight SQL. you can approximate this in Python as > follows: > > def using(t1, t2, *names): > return join(t1, t2, onclause=and_(*[t1.c[x]==t2.c[x] for x in names])) > > Ash wrote: > > > Hello > > > I want to make a join with using . > > Eg. Table1 join table2 using (id) > > > How can i do this? > > > I have tried for join like table1 join table on t1.xx = t2.xx > > > Making the object like > > t1 = Table(t1 ,metadata) > > t2 = Table(t2 ,metadata) > > > t1xx = Column(xx) > > t1.append_column(t1xx) > > t2xx = Column(xx) > > t2.append_column(t2xx) > > i did lik join(t1,t2, onclause=t1xx == t2xx) > > > How cn i add using in the above way ? > > > 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Creating SQL Expression
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"
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 wrote:
> > > sorry its resolved and working
>
> > > On Feb 25, 12:20 pm, Ash 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
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
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 0x1ec9ff0>
> print t.c.a + t.c.b
> # bobloblaw.a + bobloblaw.b
>
> On Wed, Feb 25, 2009 at 1:25 PM, Ashish Bhatia
> 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 wrote:
> > > sorry its resolved and working
>
> > > On Feb 25, 12:20 pm, Ash 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
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 wrote:
> sorry its resolved and working
>
> On Feb 25, 12:20 pm, Ash 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
sorry its resolved and working
On Feb 25, 12:20 pm, Ash 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
-~--~~~~--~~--~--~---
