Re: [sqlalchemy] filter like using a string in a column of timestamps without time zones - postgres

[George Brande]

Thank you! You eliberated me to finally move to the next step in my 
project, this kept me at a standstill. 
Thank you !

marți, 11 septembrie 2018, 12:59:18 UTC+3, Simon King a scris:
>
> On Tue, Sep 11, 2018 at 9:39 AM George Brande  > wrote: 
> > 
> > Hello. 
> > 
> > My angular is using a datepicker to send a date in string format(ex: 
> 2018-09-11) to my flask app to postgres via sqlalchemy. 
> > In my postgres all rows have a column ef_time of timestamps type.(ex: 
> 2018-09-07 13:24:30.138) 
> > 
> > 
> > @app.route('/orders/') 
> > def get_orders(ide): 
> > session = Session() 
> > orders_objects = 
> session.query(Orders).filter(Orders.ef_time.like(ide+"%")).all() 
> > schema = OrdersSchema(many=True) 
> >orders = schema.dump(orders_objects) 
> > 
> > session.close() 
> > return json.dumps(orders.data) 
> > 
> > When sending a test, /orders/2018-09-11 my flask app gives me an error: 
> operator does not exist: timestamp without time zone ~~~unknown. 
> > Line3: Where ord7.ef_time LIKE '2018-09-11%' because, obviously i am 
> seding a string and my ef_time column is of type datestamp without timezone 
> in postgres. 
> > 
> > Please give some support, i don't know how to get out this situation. 
> king regards. 
>
> I think you need to convert your string to a python datetime object 
> (eg. using datetime.strptime) and calculate the end date (eg. by using 
> timedelta), rather than using "LIKE". 
>
> Something like this: 
>
>   import datetime as dt 
>   import sqlalchemy as sa 
>
>   starttime = dt.datetime.strptime(ide, "%Y-%m-%d") 
>   endtime = starttime + dt.timedelta(days=1) 
>   condition = sa.and_(Orders.ef_time >= starttime, Orders.ef_time < 
> endtime) 
>   orders_objects = session.query(Orders).filter(condition).all() 
>
> Hope that helps, 
>
> Simon 
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] filter like using a string in a column of timestamps without time zones - postgres

[Simon King]

On Tue, Sep 11, 2018 at 9:39 AM George Brande  wrote:
>
> Hello.
>
> My angular is using a datepicker to send a date in string format(ex: 
> 2018-09-11) to my flask app to postgres via sqlalchemy.
> In my postgres all rows have a column ef_time of timestamps type.(ex: 
> 2018-09-07 13:24:30.138)
>
>
> @app.route('/orders/')
> def get_orders(ide):
> session = Session()
> orders_objects = 
> session.query(Orders).filter(Orders.ef_time.like(ide+"%")).all()
> schema = OrdersSchema(many=True)
>orders = schema.dump(orders_objects)
>
> session.close()
> return json.dumps(orders.data)
>
> When sending a test, /orders/2018-09-11 my flask app gives me an error: 
> operator does not exist: timestamp without time zone ~~~unknown.
> Line3: Where ord7.ef_time LIKE '2018-09-11%' because, obviously i am seding a 
> string and my ef_time column is of type datestamp without timezone in 
> postgres.
>
> Please give some support, i don't know how to get out this situation. king 
> regards.

I think you need to convert your string to a python datetime object
(eg. using datetime.strptime) and calculate the end date (eg. by using
timedelta), rather than using "LIKE".

Something like this:

  import datetime as dt
  import sqlalchemy as sa

  starttime = dt.datetime.strptime(ide, "%Y-%m-%d")
  endtime = starttime + dt.timedelta(days=1)
  condition = sa.and_(Orders.ef_time >= starttime, Orders.ef_time < endtime)
  orders_objects = session.query(Orders).filter(condition).all()

Hope that helps,

Simon

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] filter like using a string in a column of timestamps without time zones - postgres

[George Brande]

Hello.

My angular is using a datepicker to send a date in string format(ex: 
2018-09-11) to my flask app to postgres via sqlalchemy.
In my postgres all rows have a column ef_time of timestamps type.(ex: 
2018-09-07 
13:24:30.138)


@app.route('/orders/')
def get_orders(ide):
session = Session()
orders_objects = session.query(Orders).filter(Orders.ef_time.like(ide+"%")).
all()
schema = OrdersSchema(many=True)
   orders = schema.dump(orders_objects)

session.close()
return json.dumps(orders.data)

When sending a test, /orders/2018-09-11 my flask app gives me an error: 
operator does not exist: timestamp without time zone ~~~unknown.
Line3: Where ord7.ef_time LIKE '2018-09-11%' because, obviously i am seding 
a string and my ef_time column is of type datestamp without timezone in 
postgres.

Please give some support, i don't know how to get out this situation. king 
regards.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Looks like there is no event to catch "before" a rollback happens

[mike bayer]

On 03/11/2017 10:15 AM, mike bayer wrote:



On 03/10/2017 11:12 AM, Alessandro Molina wrote:



On Fri, Mar 10, 2017 at 3:40 PM, mike bayer > wrote:

If this is truly, "unexpected error but we need to do things",
perhaps you can use before_flush() to memoize the details you need
for a restore inside of session.info .

An event hook can be added but it would need to be carefully
considered what the specific use case for this hook is.   For
example I'm not sure "before_rollback()" is really what this should
be, it likely should be "on flush exception" similar to how engine
does it.


My specific need is related to https://github.com/amol-/depot/issues/36

DEPOT allows loading files associated to database data.
In case of a rollback DEPOT deletes the files that got uploaded.

That works in case of `.flush()` + `.rollback()` because it gathers the
history of the entity and the changed files in `before_flush`, but if a
rollback is issued without a flush it currently lacks an event from
which it can get the state of the objects and their history before the
rollback.



Looking at the code for how the events are called I would say that
moving the after_rollback() event call to be before the attributes are
reset might be more appropriate.   The after_commit() event works this
way, that is, inside of after_commit() things have not yet been expired.
  This would be an example of how critical it is that we consider really
deeply how these events are implemented.   Throwing them in quickly is
how these design mistakes occur.



propose after_rollback() emit before the snapshot is cleared at 
https://bitbucket.org/zzzeek/sqlalchemy/issues/3934/call-after_rollback-before-accounting. 
  If you can evaluate the patch at 
https://gerrit.sqlalchemy.org/#/c/334/ that would be helpful.  Note that 
you still cannot emit SQL in this event.












--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
.
To post to this group, send email to sqlalchemy@googlegroups.com
.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Looks like there is no event to catch "before" a rollback happens

[mike bayer]

On 03/10/2017 11:12 AM, Alessandro Molina wrote:



On Fri, Mar 10, 2017 at 3:40 PM, mike bayer > wrote:

If this is truly, "unexpected error but we need to do things",
perhaps you can use before_flush() to memoize the details you need
for a restore inside of session.info .

An event hook can be added but it would need to be carefully
considered what the specific use case for this hook is.   For
example I'm not sure "before_rollback()" is really what this should
be, it likely should be "on flush exception" similar to how engine
does it.


My specific need is related to https://github.com/amol-/depot/issues/36

DEPOT allows loading files associated to database data.
In case of a rollback DEPOT deletes the files that got uploaded.

That works in case of `.flush()` + `.rollback()` because it gathers the
history of the entity and the changed files in `before_flush`, but if a
rollback is issued without a flush it currently lacks an event from
which it can get the state of the objects and their history before the
rollback.



Looking at the code for how the events are called I would say that 
moving the after_rollback() event call to be before the attributes are 
reset might be more appropriate.   The after_commit() event works this 
way, that is, inside of after_commit() things have not yet been expired. 
  This would be an example of how critical it is that we consider 
really deeply how these events are implemented.   Throwing them in 
quickly is how these design mistakes occur.









--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
.
To post to this group, send email to sqlalchemy@googlegroups.com
.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Looks like there is no event to catch "before" a rollback happens

[Simon King]

On Fri, Mar 10, 2017 at 4:12 PM, Alessandro Molina
 wrote:
>
>
> On Fri, Mar 10, 2017 at 3:40 PM, mike bayer 
> wrote:
>>
>> If this is truly, "unexpected error but we need to do things", perhaps you
>> can use before_flush() to memoize the details you need for a restore inside
>> of session.info.
>>
>> An event hook can be added but it would need to be carefully considered
>> what the specific use case for this hook is.   For example I'm not sure
>> "before_rollback()" is really what this should be, it likely should be "on
>> flush exception" similar to how engine does it.
>
>
> My specific need is related to https://github.com/amol-/depot/issues/36
>
> DEPOT allows loading files associated to database data.
> In case of a rollback DEPOT deletes the files that got uploaded.
>
> That works in case of `.flush()` + `.rollback()` because it gathers the
> history of the entity and the changed files in `before_flush`, but if a
> rollback is issued without a flush it currently lacks an event from which it
> can get the state of the objects and their history before the rollback.
>

Could you collect the necessary data using the before_attach or
after_attach events, rather than before_flush?

Simon

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Looks like there is no event to catch "before" a rollback happens

[Alessandro Molina]

On Fri, Mar 10, 2017 at 3:40 PM, mike bayer 
wrote:

> If this is truly, "unexpected error but we need to do things", perhaps you
> can use before_flush() to memoize the details you need for a restore inside
> of session.info.
>
> An event hook can be added but it would need to be carefully considered
> what the specific use case for this hook is.   For example I'm not sure
> "before_rollback()" is really what this should be, it likely should be "on
> flush exception" similar to how engine does it.
>

My specific need is related to https://github.com/amol-/depot/issues/36

DEPOT allows loading files associated to database data.
In case of a rollback DEPOT deletes the files that got uploaded.

That works in case of `.flush()` + `.rollback()` because it gathers the
history of the entity and the changed files in `before_flush`, but if a
rollback is issued without a flush it currently lacks an event from which
it can get the state of the objects and their history before the rollback.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Looks like there is no event to catch "before" a rollback happens

[mike bayer]

On 03/10/2017 01:57 AM, Alessandro Molina wrote:

I have been looking for  a way to know what's going to be rolled back in
SQLAlchemy so that I can know what was changed and restore other
database unrelated things to their previous state.

By http://docs.sqlalchemy.org/en/latest/orm/events.html#session-events
it looks like it's available an after_soft_rollback event, but in that
even the objects already got rolled back and so their history is gone.
In the most common scenario users do Session.flush() and then
Session.rollback(), and in that case I have before_flush that can tell
me everything that changed (than I can restore the state of related
things into after_soft_rollback which can benefit from the knowledge
that I gathered in before_flush) but if the user does a direct
Session.rollback() without a flush I couldn't find an event I could
attach to know what changed an so what is going to be rolled back.

Not sure if that can be achieved or a new event would be needed.


When an integrity error or something like that happens, often the 
database transaction is unusable anyway, and no further SELECT can be 
emitted.  See 
http://docs.sqlalchemy.org/en/latest/faq/sessions.html#but-why-does-flush-insist-on-issuing-a-rollback 
for detail.   An event that is added before this rollback occurs would 
provide an environment that can't work consistently because the database 
connection may or may not be usable depending on specifics.   So if I 
add that event, then I get the endless parade of "I can't do X in the 
before_rollback event but only when Q, P, R exist and I'm using backend Z!".


The idiomatic way to be able to rollback an operation but still continue 
to work with the data is to use savepoints.


If this is truly, "unexpected error but we need to do things", perhaps 
you can use before_flush() to memoize the details you need for a restore 
inside of session.info.


An event hook can be added but it would need to be carefully considered 
what the specific use case for this hook is.   For example I'm not sure 
"before_rollback()" is really what this should be, it likely should be 
"on flush exception" similar to how engine does it.






--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
.
To post to this group, send email to sqlalchemy@googlegroups.com
.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Looks like there is no event to catch "before" a rollback happens

[Alessandro Molina]

I have been looking for  a way to know what's going to be rolled back in 
SQLAlchemy so that I can know what was changed and restore other database 
unrelated things to their previous state.

By http://docs.sqlalchemy.org/en/latest/orm/events.html#session-events it 
looks like it's available an after_soft_rollback event, but in that even 
the objects already got rolled back and so their history is gone.
In the most common scenario users do Session.flush() and then 
Session.rollback(), and in that case I have before_flush that can tell me 
everything that changed (than I can restore the state of related things 
into after_soft_rollback which can benefit from the knowledge that I 
gathered in before_flush) but if the user does a direct Session.rollback() 
without a flush I couldn't find an event I could attach to know what 
changed an so what is going to be rolled back.

Not sure if that can be achieved or a new event would be needed.


-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] virtual-like entities, concrete table inheritance

[Julien Cigar]

Hello,

I'm currently implementing a RBAC-like model for a webapp with the 
usual suspects: users, roles, permissions, etc where a Role has one 
or more Permissions, and an User can be in 1 or more Role.


I would like to some virtual-like Role that are automatically 
attribued in some situations. For example AnonymousUser is the user is 
not logged, AuthenticatedUser is the user is authenticated, Owner if 
the user owns an object, etc. Those roles should always exist and 
should not be part of the unit-of-work process, in fact I don't want to 
store them in the database.
I wondered if there is an elegant way to do that kind of stuff 
transparently with SQLAlchemy?


Another question: I have different kind of Permission (some of them 
are linked to a module, some to a content_type, some are core 
permissions, etc) and I planned to use one separate table per 
permission type with concrete table inheritance. I only used joined 
load inheritance (which work very well) and from what I understood from 
the documentation concrete table inheritance are somewhat discouraged?


Thank you :-)
Julien

--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] virtual-like entities, concrete table inheritance

[Michael Bayer]

On May 22, 2013, at 7:14 AM, Julien Cigar jci...@ulb.ac.be wrote:

 Hello,
 
 I'm currently implementing a RBAC-like model for a webapp with the usual 
 suspects: users, roles, permissions, etc where a Role has one or more 
 Permissions, and an User can be in 1 or more Role.
 
 I would like to some virtual-like Role that are automatically attribued in 
 some situations. For example AnonymousUser is the user is not logged, 
 AuthenticatedUser is the user is authenticated, Owner if the user owns 
 an object, etc. Those roles should always exist and should not be part of the 
 unit-of-work process, in fact I don't want to store them in the database.
 I wondered if there is an elegant way to do that kind of stuff transparently 
 with SQLAlchemy?

well if they aren't DB persisted, then I'd start by not mapping them .   
you just would write some Python code to set up those objects as needed?  
depends on what you use to determine the roles.If the info is stored in the 
database, then I'd map something to those tables.


 
 Another question: I have different kind of Permission (some of them are 
 linked to a module, some to a content_type, some are core permissions, etc) 
 and I planned to use one separate table per permission type with concrete 
 table inheritance. I only used joined load inheritance (which work very well) 
 and from what I understood from the documentation concrete table inheritance 
 are somewhat discouraged?

if you only need to select from one concrete class at a time, there's no 
issue.  its only when people want to query across multiple types of classes 
that things get very tedious.

-- 
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] NOT LIKE

[Michael Bayer]

On Aug 22, 2012, at 11:38 PM, Warwick Prince wrote:

 Thanks Michael
 
 I struggle sometimes to find examples of the simple things, so eventually 
 searched out the like_op as it was in the same place as eq() etc.
 
 So, on that subject - is it better to use query.where(eq(a, b)) or 
 query.where(a==b), or does it make no difference really?  

it makes no difference at all.   

The closest thing we have to a rundown of operators is here: 
http://docs.sqlalchemy.org/en/rel_0_7/orm/tutorial.html#common-filter-operators

I suppose having an exhaustive list, with lots of links pointing to it, over 
here: http://docs.sqlalchemy.org/en/rel_0_7/core/expression_api.html would be 
helpful.



 
 not sure why the like_op and nolike_op have come into your normal 
 vocabulary here as they are usually just the ops used internally.
 
 LIKE is column.like(other) and NOT LIKE is ~column.like(other).
 
 
 On Aug 22, 2012, at 9:15 PM, Warwick Prince wrote:
 
 Hi 
 
 When creating a basic query, how does one code a NOT LIKE using SA?
 
 I can do this;
 
 query = table.select().where(like_op(table.c.name, 'fred%'))
 
 I can not find a NOT LIKE operator.  The ones there notlike_op and 
 notilike_op raise NotImplemented.
 
 I've placed it in as text('%s NOT LIKE %s') % (column, value) for now, 
 but I just KNOW that's going to blow up when I change databases down the 
 track..
 
 Is there another way?   I've been burnt badly recently changing from MySQL 
 to Postgres with text(), so I'm trying to avoid at all costs! :-)
 
 Cheers
 Warwick
 
 -- 
 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.

Re: [sqlalchemy] NOT LIKE

[Warwick Prince]

OK - cool.

I had looked at the first ORM tutorial, but I guess I had glossed over it, as 
it was talking about session.query, and I believed I was looking for something 
lower level than that for the direct table.select.  Obviously not. :-)  Makes 
sense that it would all follow suit, but I was expecting to have to use lower 
level constructs - I'm pleasantly surprised.

Thanks for your seemingly infinite patients!

 On Aug 22, 2012, at 11:38 PM, Warwick Prince wrote:
 
 Thanks Michael
 
 I struggle sometimes to find examples of the simple things, so eventually 
 searched out the like_op as it was in the same place as eq() etc.
 
 So, on that subject - is it better to use query.where(eq(a, b)) or 
 query.where(a==b), or does it make no difference really?  
 
 it makes no difference at all.   
 
 The closest thing we have to a rundown of operators is here: 
 http://docs.sqlalchemy.org/en/rel_0_7/orm/tutorial.html#common-filter-operators
 
 I suppose having an exhaustive list, with lots of links pointing to it, over 
 here: http://docs.sqlalchemy.org/en/rel_0_7/core/expression_api.html would be 
 helpful.
 
 
 
 
 not sure why the like_op and nolike_op have come into your normal 
 vocabulary here as they are usually just the ops used internally.
 
 LIKE is column.like(other) and NOT LIKE is ~column.like(other).
 
 
 On Aug 22, 2012, at 9:15 PM, Warwick Prince wrote:
 
 Hi 
 
 When creating a basic query, how does one code a NOT LIKE using SA?
 
 I can do this;
 
 query = table.select().where(like_op(table.c.name, 'fred%'))
 
 I can not find a NOT LIKE operator.  The ones there notlike_op and 
 notilike_op raise NotImplemented.
 
 I've placed it in as text('%s NOT LIKE %s') % (column, value) for now, 
 but I just KNOW that's going to blow up when I change databases down the 
 track..
 
 Is there another way?   I've been burnt badly recently changing from MySQL 
 to Postgres with text(), so I'm trying to avoid at all costs! :-)
 
 Cheers
 Warwick
 
 -- 
 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.
 

-- 
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] NOT LIKE

[Warwick Prince]

Hi 

When creating a basic query, how does one code a NOT LIKE using SA?

I can do this;

query = table.select().where(like_op(table.c.name, 'fred%'))

I can not find a NOT LIKE operator.  The ones there notlike_op and 
notilike_op raise NotImplemented.

I've placed it in as text('%s NOT LIKE %s') % (column, value) for now, but I 
just KNOW that's going to blow up when I change databases down the track..

Is there another way?   I've been burnt badly recently changing from MySQL to 
Postgres with text(), so I'm trying to avoid at all costs! :-)

Cheers
Warwick

-- 
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] NOT LIKE

[Michael Bayer]

not sure why the like_op and nolike_op have come into your normal 
vocabulary here as they are usually just the ops used internally.

LIKE is column.like(other) and NOT LIKE is ~column.like(other).


On Aug 22, 2012, at 9:15 PM, Warwick Prince wrote:

 Hi 
 
 When creating a basic query, how does one code a NOT LIKE using SA?
 
 I can do this;
 
 query = table.select().where(like_op(table.c.name, 'fred%'))
 
 I can not find a NOT LIKE operator.  The ones there notlike_op and 
 notilike_op raise NotImplemented.
 
 I've placed it in as text('%s NOT LIKE %s') % (column, value) for now, but 
 I just KNOW that's going to blow up when I change databases down the track..
 
 Is there another way?   I've been burnt badly recently changing from MySQL to 
 Postgres with text(), so I'm trying to avoid at all costs! :-)
 
 Cheers
 Warwick
 
 -- 
 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] NOT LIKE

[Warwick Prince]

Thanks Michael

I struggle sometimes to find examples of the simple things, so eventually 
searched out the like_op as it was in the same place as eq() etc.

So, on that subject - is it better to use query.where(eq(a, b)) or 
query.where(a==b), or does it make no difference really?  

 not sure why the like_op and nolike_op have come into your normal 
 vocabulary here as they are usually just the ops used internally.
 
 LIKE is column.like(other) and NOT LIKE is ~column.like(other).
 
 
 On Aug 22, 2012, at 9:15 PM, Warwick Prince wrote:
 
 Hi 
 
 When creating a basic query, how does one code a NOT LIKE using SA?
 
 I can do this;
 
 query = table.select().where(like_op(table.c.name, 'fred%'))
 
 I can not find a NOT LIKE operator.  The ones there notlike_op and 
 notilike_op raise NotImplemented.
 
 I've placed it in as text('%s NOT LIKE %s') % (column, value) for now, but 
 I just KNOW that's going to blow up when I change databases down the track..
 
 Is there another way?   I've been burnt badly recently changing from MySQL 
 to Postgres with text(), so I'm trying to avoid at all costs! :-)
 
 Cheers
 Warwick
 
 -- 
 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] Select like but the other way around

[Paul]

I'm trying to do a like statement in a query filter. I'm fine doing it one way
for instance

session.query(Table).filter(Table.path.like(C:\Test\%))

which would hopefully return all folders and files in the folder Test

but what if I want to do it the other way around and pass 
C:\Test\testfile.txt 
and return all the folders. I want something like 
C:\Test\testfile.txt.like(Table.path+%) but obviously a string wont have 
the 
method like.

is there a function I can import that takes 2 arguements or another method I 
can 
use to achieve this?

Thanks!

Paul



-- 
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 like but the other way around

[Michael Bayer]

sure it does, if you convert it to a SQL token first:

literal(C:\test\testfile.txt).like(Table.path + %)

or even

literal(C:\test\testfile.txt).startswith(Table.path)



On Nov 7, 2011, at 8:40 AM, Paul wrote:

 I'm trying to do a like statement in a query filter. I'm fine doing it one way
 for instance
 
 session.query(Table).filter(Table.path.like(C:\Test\%))
 
 which would hopefully return all folders and files in the folder Test
 
 but what if I want to do it the other way around and pass 
 C:\Test\testfile.txt 
 and return all the folders. I want something like 
 C:\Test\testfile.txt.like(Table.path+%) but obviously a string wont have 
 the 
 method like.
 
 is there a function I can import that takes 2 arguements or another method I 
 can 
 use to achieve this?
 
 Thanks!
 
 Paul
 
 
 
 -- 
 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] Django-like test fixtures via SQLAlchemy?

[Todd Rowell]

Hi All-

I'm a long-time Django user who has become accustomed to Django's easy
generation and loading of test fixtures (i.e., a known database state
for testing) and I'm looking for something similar for SQLAlchemy.

I've seen and been working with Kumar's fixture project (http://
pypi.python.org/pypi/fixture) but it seems largely intended to allow
loading of simpler, hand-created fixtures; support for creating
fixtures from an existing database is pretty limited and seems to
require additional coding instead of just using your existing ORM
models. It also looks like the export functionality runs a single
query at a time; not that big a limitation but not as nice as the
Django fixture generator.

I've also seen BootAlchemy, but it doesn't seem to provide for
generating fixtures.

Any ideas?

Thanks,
Todd

-- 
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] Django-like test fixtures via SQLAlchemy?

[Michael Bayer]

On Mar 11, 2011, at 2:25 PM, Todd Rowell wrote:

 Hi All-
 
 I'm a long-time Django user who has become accustomed to Django's easy
 generation and loading of test fixtures (i.e., a known database state
 for testing) and I'm looking for something similar for SQLAlchemy.
 
 I've seen and been working with Kumar's fixture project (http://
 pypi.python.org/pypi/fixture) but it seems largely intended to allow
 loading of simpler, hand-created fixtures; support for creating
 fixtures from an existing database is pretty limited and seems to
 require additional coding instead of just using your existing ORM
 models. It also looks like the export functionality runs a single
 query at a time; not that big a limitation but not as nice as the
 Django fixture generator.
 
 I've also seen BootAlchemy, but it doesn't seem to provide for
 generating fixtures.


not knowing what a fixture generator means, I googled and found this:

https://github.com/alex/django-fixture-generator

Looking at that I don't see *too* much automation - at the very least I see 
explicit usage of models and explicit fixture data on the part of the 
developer.  I'm not really sure what we get when we run manage.py though, 
test_users() and test_groups() already seem to be doing the work that is 
specific to these fixtures.

Perhaps this is one of those 20 foot cultural divide kind of things, but the 
problem of consistent fixtures is something I usually address with plain 
coding conventions, though the decorator trickery I see there, which appears to 
mark various fixture-generation methods into some kind of registry, is probably 
not hard to roll either, and would make a nice external project for SQLAlchemy 
if you were inclined.

As to how I approach that kind of thing, building off the transactional example 
at 
http://www.sqlalchemy.org/docs/orm/session.html#joining-a-session-into-an-external-transaction
 , these days I tend to organize fixtures, common assertion methods, into a 
structure like that below.   Mixins define things like transactional behavior 
and common fixture/assertion methods:

class TransactionalTest(object):
Mixin which sets up/tears down a transaction

def setUp(self):
# connect to the database
self.connection = engine.connect()

# begin a non-ORM transaction
self.trans = connection.begin()

# bind an individual Session to the connection
self.session = Session(bind=self.connection)

def tearDown(self):
# rolls everything back.
self.session.close()
self.trans.rollback()
self.connection.close()

class UserTest(object):
A test that deals with User objects.

def _user_fixture(self):
Example fixture method.
self.session.add_all([User(name='ed'),User(name='wendy')])

def _assert_ed(self, user):
Example assertion method.
assert user.name == 'ed', Username is not 'ed'

class MyTest(UserTest, TransactionalTest, TestCase):

def test_ed(self):
self._user_fixture()

ed = self.session.query(User).filter_by(name='ed').one()
self._assert_ed(ed)




 
 Any ideas?
 
 Thanks,
 Todd
 
 -- 
 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] Something like orderinglist for secondary tables?

[Torsten Landschoff]

Hi *,

I am fighting half a day with something I expected to be trivial: Keep
the order of items in a collection implemented vi a secondary table
(many-to-many relationship).

Basically, I have a Collection class with a relationship to Items in the
collection. That relationship is configured via

items=relation(Item,
secondary=collection_item_table,
order_by=[collection_item_table.c.item_order])

Now my problem is: How to update the item_order column in the table? So
far I did not find any way to do that. For a many-to-one relationship,
orderinglist will do this just fine.

I tried using a MapperExtension that goes through the relevant rows in
the secondary table and updates the item_order column (code attached).
It turns out that the after_insert and after_update extension points are
called before the child collections are flushed, so the primary keys of
any new items are not available at that time. Apart from that, it is a
bit heavy on the database to update all the item rows for a selection on
each change...

Another approach I tried was to use replace the relationship via custom
code querying the relation when reconstructing a collection instance.
The loading part works fine but I failed in populating the
collection_item_table in the first place, as the items must be flushed
before the Collection for the item_id to be available and I did not find
a way to tell SQLAlchemy of this dependency.

Any hint on how to do this is greatly appreciated.

Greetings, Torsten

-- 
DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH
Torsten Landschoff

Office Dresden
Tel: +49-(0)351-4519587
Fax: +49-(0)351-4519561

mailto:torsten.landsch...@dynamore.de
http://www.dynamore.de

Registration court: Mannheim, HRB: 109659, based in Karlsruhe,
Managing director:  Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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.

#! /usr/bin/python

from sqlalchemy import *
from sqlalchemy.orm import *

# Set up the tables
meta = MetaData()
collection_table = Table(collection, meta,
Column(collection_id, Integer, primary_key=True))
item_table = Table(item, meta,
Column(item_id, Integer, primary_key=True),
Column(name, String))
collection_item_table = Table(collection_item, meta,
Column(collection_id, ForeignKey(collection_table.c.collection_id)),
Column(item_id, ForeignKey(item_table.c.item_id)),
Column(item_order, Integer))

# Mapped classes
class Collection(object):
def shallow_copy(self):
new = Collection()
new.items = self.items
return new

class Item(object):
def __init__(self, name):
self.name = name

# In a function to test without committing (which will work of course)
def maybe_commit(session):
session.commit()
pass


class CorrectOrderExtension(MapperExtension):
Updates the order of the entries in the collection_item_table to match with
the order in the items field of a Collection instance.

Does not work if items were not flushed before the Collection - how to force
flushing order? I would have expected that after_update/after_insert are called
after all collection attributes are completely written out.

def after_update(self, mapper, connection, instance):
update = collection_item_table.update().where(
collection_item_table.c.collection_id==bindparam('b_collection')).where(
collection_item_table.c.item_id==bindparam('b_item_id')
).values(item_order=bindparam('b_item_order'))

collection_id = instance.collection_id
index = 0
updates = []
for item in instance.items:
item_id = item.item_id
assert item_id
updates.append(dict(
b_collection=collection_id,
b_item_id=item_id,
b_item_order=index))
index += 1

if updates:
connection.execute(update, updates)
return EXT_CONTINUE

def after_insert(self, mapper, connection, instance):
return self.after_update(mapper, connection, instance)


# Do the Object Relational Mapping
mapper(Item, item_table)
mapper(Collection, collection_table,
extension=CorrectOrderExtension(),
properties=dict(
items=relation(Item,
secondary=collection_item_table,
order_by=[collection_item_table.c.item_order])))

# Open database
engine = create_engine(sqlite:///, echo=True)
Session = sessionmaker(bind=engine)
meta.create_all(engine)

# Some items to play with
items = [Item(name) for name in (foo, bar, baz, qux)]

session = Session()
c = Collection()
c.items = items[:3]
session.add(c)
maybe_commit(session)

Re: [sqlalchemy] Something like orderinglist for secondary tables?

[Michael Bayer]

On Nov 18, 2010, at 9:32 AM, Torsten Landschoff wrote:

 Hi *,
 
 I am fighting half a day with something I expected to be trivial: Keep
 the order of items in a collection implemented vi a secondary table
 (many-to-many relationship).
 
 Basically, I have a Collection class with a relationship to Items in the
 collection. That relationship is configured via
 
items=relation(Item,
secondary=collection_item_table,
order_by=[collection_item_table.c.item_order])
 
 Now my problem is: How to update the item_order column in the table? So
 far I did not find any way to do that. For a many-to-one relationship,
 orderinglist will do this just fine.

this is correct.  The functionality provided by secondary is that SQLA will 
maintain a table with foreign keys to the related primary keys on either side.  
It does not do anything at all with additional columns on the secondary 
table.   If your secondary table has additional columns you need to deal 
with, you no longer use secondary and instead use the association object 
pattern : 
http://www.sqlalchemy.org/docs/orm/relationships.html#association-object .
To make this pattern act more like secondary in the usual case, you use 
associationproxy:  
http://www.sqlalchemy.org/docs/orm/extensions/associationproxy.html

You could most likely use the existing orderinglist extension in conjunction 
with associationproxy to maintain the ordering you want, in Python.


 
 I tried using a MapperExtension that goes through the relevant rows in
 the secondary table and updates the item_order column (code attached).
 It turns out that the after_insert and after_update extension points are
 called before the child collections are flushed, so the primary keys of
 any new items are not available at that time. Apart from that, it is a
 bit heavy on the database to update all the item rows for a selection on
 each change...

MapperExtension is never the right place to do anything involving 
relationships.   SessionExtension is where you'd organize everything before or 
after the entire flush occurs.   In this case its likely much easier to 
manipulate the collection_item_table directly before any flush happens.





 
 Another approach I tried was to use replace the relationship via custom
 code querying the relation when reconstructing a collection instance.
 The loading part works fine but I failed in populating the
 collection_item_table in the first place, as the items must be flushed
 before the Collection for the item_id to be available and I did not find
 a way to tell SQLAlchemy of this dependency.
 
 Any hint on how to do this is greatly appreciated.
 
 Greetings, Torsten
 
 -- 
 DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH
 Torsten Landschoff
 
 Office Dresden
 Tel: +49-(0)351-4519587
 Fax: +49-(0)351-4519561
 
 mailto:torsten.landsch...@dynamore.de
 http://www.dynamore.de
 
 Registration court: Mannheim, HRB: 109659, based in Karlsruhe,
 Managing director:  Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@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.
 
 mapper_extension.pysession_extension.py

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Something like orderinglist for secondary tables?

[Torsten Landschoff]

Hi Michael,

Thanks for your lightning fast reply!

On Thu, 2010-11-18 at 10:17 -0500, Michael Bayer wrote:

 this is correct.  The functionality provided by secondary is that SQLA will 
 maintain a table with foreign keys to the related primary keys on either 
 side.  It does not do anything at all with additional columns on the 
 secondary table.   If your secondary table has additional columns you 
 need to deal with, you no longer use secondary and instead use the 
 association object pattern : 
 http://www.sqlalchemy.org/docs/orm/relationships.html#association-object .
 To make this pattern act more like secondary in the usual case, you use 
 associationproxy:  
 http://www.sqlalchemy.org/docs/orm/extensions/associationproxy.html
 
 You could most likely use the existing orderinglist extension in conjunction 
 with associationproxy to maintain the ordering you want, in Python.

Okay, I updated my example code and it actually works now. However, it
feels like a lot of additional complexity just for adding order.

Greetings, Torsten

-- 
DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH
Torsten Landschoff

Office Dresden
Tel: +49-(0)351-4519587
Fax: +49-(0)351-4519561

mailto:torsten.landsch...@dynamore.de
http://www.dynamore.de

Registration court: Mannheim, HRB: 109659, based in Karlsruhe,
Managing director:  Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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.

#! /usr/bin/python

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.orderinglist import ordering_list
from sqlalchemy.ext.associationproxy import association_proxy

meta = MetaData()

collection_table = Table(collection, meta,
Column(collection_id, Integer, primary_key=True))

item_table = Table(item, meta,
Column(item_id, Integer, primary_key=True),
Column(name, String))

collection_item_table = Table(collection_item, meta,
# Need a surrogate key to allow duplicate entries in the list.
Column(id, Integer, primary_key=True),
Column(collection_id, ForeignKey(collection_table.c.collection_id), index=True),
Column(item_id, ForeignKey(item_table.c.item_id)),
Column(item_order, Integer))


class Collection(object):
def shallow_copy(self):
new = Collection()
new.items = self.items
return new

items = association_proxy(items_relation, item)

class Item(object):
def __init__(self, name):
self.name = name

class CollectionItemAssociation(object):
def __init__(self, item):
self.item = item

def maybe_commit(session):
session.commit()
pass

mapper(Item, item_table)
mapper(CollectionItemAssociation, collection_item_table, properties=dict(
item=relation(Item)))
mapper(Collection, collection_table, properties=dict(
items_relation=relation(
CollectionItemAssociation,
order_by=[collection_item_table.c.item_order],
collection_class=ordering_list('item_order'),)))

engine = create_engine(sqlite:///, echo=True)
Session = sessionmaker(bind=engine)
meta.create_all(engine)

items = [Item(name) for name in (foo, bar, baz, qux)]

session = Session()
c = Collection()
c.items = items[:3]
session.add(c)
maybe_commit(session)

c.items[1] = items[3]
maybe_commit(session)

assert [x.name for x in c.items] == [foo, qux, baz]

c.items.append(c.items[0])
maybe_commit(session)
assert [x.name for x in c.items] == [foo, qux, baz, foo]

Re: [sqlalchemy] Something like orderinglist for secondary tables?

[Michael Bayer]

On Nov 18, 2010, at 11:01 AM, Torsten Landschoff wrote:

 Hi Michael,
 
 Thanks for your lightning fast reply!
 
 On Thu, 2010-11-18 at 10:17 -0500, Michael Bayer wrote:
 
 this is correct.  The functionality provided by secondary is that SQLA 
 will maintain a table with foreign keys to the related primary keys on 
 either side.  It does not do anything at all with additional columns on the 
 secondary table.   If your secondary table has additional columns you 
 need to deal with, you no longer use secondary and instead use the 
 association object pattern : 
 http://www.sqlalchemy.org/docs/orm/relationships.html#association-object .   
  To make this pattern act more like secondary in the usual case, you use 
 associationproxy:  
 http://www.sqlalchemy.org/docs/orm/extensions/associationproxy.html
 
 You could most likely use the existing orderinglist extension in conjunction 
 with associationproxy to maintain the ordering you want, in Python.
 
 Okay, I updated my example code and it actually works now. However, it
 feels like a lot of additional complexity just for adding order.

Hm, I just looked at the script and compared it to your previous two pages of 
Mapper/Session extensions, seems a lot simpler to me !

The relationship + secondary approach does support ordering by an extra 
column in the association table, it just doesn't support direct mutation of 
that value.  

The rationale for the association object pattern is that it is a consistent way 
of establishing full control over the secondary table, using the exact same 
paradigms as that which grant control over the rows of any other table.   If we 
did it via flags and switches to relationship(), the API and internal 
complexity would increase significantly as would the potential for bugs, not to 
mention ambiguity in preferred methodology.




 
 Greetings, Torsten
 
 -- 
 DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH
 Torsten Landschoff
 
 Office Dresden
 Tel: +49-(0)351-4519587
 Fax: +49-(0)351-4519561
 
 mailto:torsten.landsch...@dynamore.de
 http://www.dynamore.de
 
 Registration court: Mannheim, HRB: 109659, based in Karlsruhe,
 Managing director:  Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@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.
 
 association_object.py

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] looks like bug ses.query(data).update()

[bogun . dmitriy]

I try to update counter for omr object ang got following:

Traceback (most recent call last):
  File /home/vugluskr/tmp/z/sa.py, line 56, in module
main()
  File /home/vugluskr/tmp/z/sa.py, line 52, in main
q2.update({data.cnt: data.cnt + 1})
  File /usr/lib/python2.6/site-packages/sqlalchemy/orm/query.py,
line 2184, in update
difference(to_evaluate))
  File /usr/lib/python2.6/site-packages/sqlalchemy/orm/state.py,
line 257, in expire_attributes
impl = self.manager[key].impl
KeyError: 'counter'

Tests script and full output in attaches.

Maybe I going wrong way and SA have more simpler way awailable to make
query like:
update tbl set counter = counter + 1 where ...

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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.

INFO:sqlalchemy.engine.base.Engine.0x...9b4c:PRAGMA table_info(data)
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:()
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:
CREATE TABLE data (
idnr INTEGER NOT NULL, 
counter INTEGER NOT NULL, 
PRIMARY KEY (idnr)
)


INFO:sqlalchemy.engine.base.Engine.0x...9b4c:()
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:COMMIT
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:BEGIN
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:INSERT INTO data (counter) VALUES 
(?)
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:(0,)
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:COMMIT
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:BEGIN
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:SELECT data.counter AS 
data_counter, data.idnr AS data_idnr 
FROM data
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:()
DEBUG:sqlalchemy.engine.base.Engine.0x...9b4c:Col ('data_counter', 'data_idnr')
DEBUG:sqlalchemy.engine.base.Engine.0x...9b4c:Row (0, 1)
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:UPDATE data SET 
counter=(data.counter + ?) WHERE data.idnr = ?
INFO:sqlalchemy.engine.base.Engine.0x...9b4c:(1, 1)
Traceback (most recent call last):
  File /home/vugluskr/tmp/z/sa.py, line 56, in module
main()
  File /home/vugluskr/tmp/z/sa.py, line 52, in main
q2.update({data.cnt: data.cnt + 1})
  File /usr/lib/python2.6/site-packages/sqlalchemy/orm/query.py, line 2184, 
in update
difference(to_evaluate))
  File /usr/lib/python2.6/site-packages/sqlalchemy/orm/state.py, line 257, in 
expire_attributes
impl = self.manager[key].impl
KeyError: 'counter'
#!/usr/bin/env python
#-*- coding:utf-8 -*-

#import pdb

import os, sys
import time
import logging
#import re
#import errno
#import locale
#import pprint

# mutable inputs
from sqlalchemy import *
from sqlalchemy import orm
from sqlalchemy.ext.declarative import declarative_base

decl = declarative_base()

class data(decl):
__tablename__ = 'data'

idnr= Column(Integer, primary_key=True)
cnt = Column('counter', Integer, nullable=False, default=0)

def __repr__(self):	
	return 'tbl.%s(idnr=%s, cnt=%s)' % (
	self.__class__.__name__, self.idnr, self.cnt)

def main():
logging.basicConfig()
logging.getLogger().setLevel(logging.DEBUG)
log = logging.getLogger('sqlalchemy.engine')
log.setLevel(logging.DEBUG)

eng = create_engine('sqlite://')
sm = orm.sessionmaker(bind=eng, autoflush=False, autocommit=False)

decl.metadata.create_all(eng)
ses = sm()

e = data()
ses.add(e)
ses.commit()
ses.close()

ses = sm()
q = ses.query(data)
for e in q:
	q2 = ses.query(data).filter(data.idnr==e.idnr)
	q2.update({data.cnt: data.cnt + 1})
ses.commit()

if __name__ == '__main__':
main()

Re: [sqlalchemy] looks like bug ses.query(data).update()

[Michael Bayer]

this is a bug , created at http://www.sqlalchemy.org/trac/ticket/1935 , and a 
patch which fixes this issue is there. will try to get this committed soon.



On Sep 30, 2010, at 9:57 AM, bogun.dmit...@gmail.com wrote:

 I try to update counter for omr object ang got following:
 
 Traceback (most recent call last):
  File /home/vugluskr/tmp/z/sa.py, line 56, in module
main()
  File /home/vugluskr/tmp/z/sa.py, line 52, in main
q2.update({data.cnt: data.cnt + 1})
  File /usr/lib/python2.6/site-packages/sqlalchemy/orm/query.py,
 line 2184, in update
difference(to_evaluate))
  File /usr/lib/python2.6/site-packages/sqlalchemy/orm/state.py,
 line 257, in expire_attributes
impl = self.manager[key].impl
 KeyError: 'counter'
 
 Tests script and full output in attaches.
 
 Maybe I going wrong way and SA have more simpler way awailable to make
 query like:
 update tbl set counter = counter + 1 where ...
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@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.
 
 update-output.txtsa.py

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] looks like bug ses.query(data).update()

[bogun . dmitriy]

2010/9/30 Michael Bayer mike...@zzzcomputing.com:
 this is a bug , created at http://www.sqlalchemy.org/trac/ticket/1935 , and a 
 patch which fixes this issue is there.     will try to get this committed 
 soon.

Thanks, patch fix issue.

 I try to update counter for omr object ang got following:

 Traceback (most recent call last):
  File /home/vugluskr/tmp/z/sa.py, line 56, in module
    main()
  File /home/vugluskr/tmp/z/sa.py, line 52, in main
    q2.update({data.cnt: data.cnt + 1})
  File /usr/lib/python2.6/site-packages/sqlalchemy/orm/query.py,
 line 2184, in update
    difference(to_evaluate))
  File /usr/lib/python2.6/site-packages/sqlalchemy/orm/state.py,
 line 257, in expire_attributes
    impl = self.manager[key].impl
 KeyError: 'counter'

 Tests script and full output in attaches.

 Maybe I going wrong way and SA have more simpler way awailable to make
 query like:
 update tbl set counter = counter + 1 where ...

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] dictionary-like objects for ORM

[yota]

Hello,

sqlalchemy seems to be the proper tool for my needs but I can't figure
out how to design my project or set the ORM properly.

Let's say, I build a music database, storing tracks and their
associated metadata in an sql-like database defined as such :

TRACK_TABLE ( ident *, url , duration )
METADATA_TABLE ( track_ident *, field_name *, field_content )

(track_ident, field_name) being the primary key for METADATA_TABLE...


intuitively, a meaningful object for a metadata set would be a
dictionary-like object. The name and number of fields being unknown in
advance but stored in the METADATA_TABLE as one row per field.

Here is the question : how may I use the ORM to map
a table :
123 / title / waka waka
123 / artist / shakira
123 / featuring / my sister

to an object like :
metadata.title = waka waka
metadata.artist = shakira
metadata.featuring = my sister

or a dictionary like :
metadata = { title : waka waka, artist : shakira,
featuring : my sister }

or whatsoever ...

the solution might also be in rewrite of the sql schema :)


thanks for your advices

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] dictionary-like objects for ORM

[Conor]

On 08/17/2010 11:32 AM, yota wrote:
 Hello,

 sqlalchemy seems to be the proper tool for my needs but I can't figure
 out how to design my project or set the ORM properly.

 Let's say, I build a music database, storing tracks and their
 associated metadata in an sql-like database defined as such :

 TRACK_TABLE ( ident *, url , duration )
 METADATA_TABLE ( track_ident *, field_name *, field_content )

 (track_ident, field_name) being the primary key for METADATA_TABLE...


 intuitively, a meaningful object for a metadata set would be a
 dictionary-like object. The name and number of fields being unknown in
 advance but stored in the METADATA_TABLE as one row per field.

 Here is the question : how may I use the ORM to map
 a table :
 123 / title / waka waka
 123 / artist / shakira
 123 / featuring / my sister

 to an object like :
 metadata.title = waka waka
 metadata.artist = shakira
 metadata.featuring = my sister

 or a dictionary like :
 metadata = { title : waka waka, artist : shakira,
 featuring : my sister }

 or whatsoever ...

 the solution might also be in rewrite of the sql schema :)


 thanks for your advices
   

SQLAlchemy supports for this via the attribute_mapped_collection and
association_proxy classes:

from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm.collections import attribute_mapped_collection

def create_metadata(field_name, field_content):
return TrackMetaData(field_name=field_name, field_content=field_content)

class Track(Base):
__tablename__ = track

id = Column(Integer, primary_key=True)
[...]

field2metadata = relationship(TrackMetaData,
  backref=track,
  
collection_class=attribute_mapped_collection(field_name))
field2content = association_proxy(field2metadata,
  field_content,
  creator=create_metadata)

# I'm only naming this class TrackMetaData to prevent confusion with 
sqlalchemy.MetaData.
class TrackMetaData(Base):
__tablename__ = metadata

track_id = Column(Integer, ForeignKey(track.id), primary_key=True)
field_name = Column(Unicode(...), primary_key=True)
field_content = Column(Unicode(...), nullable=False)

In this way you can access field2content like a dictionary:

artist = track.field2content[uartist]
track.field2content[utitle] = uwaka waka

Note that association_proxy does not supply a comparator yet, so if you
want to join/query on metadata then you need to use the field2metadata
relationship:

# Find all tracks by artist Shakira.
q = Session.query(Track)
q = q.filter(Track.field2metadata.any(and_(TrackMetaData.field_name == 
uartist,
   TrackMetaData.field_content = 
uShakira)))
tracks = q.all()

# Load all tracks, eagerloading their metadata.
q = Session.query(Track)
q = q.options(joinedload(Track.field2metadata))
tracks = q.all()

-Conor

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Strange LIKE behavior with TypeDecorator

[Mike Orr]

I have the following TypeDecorator type to store a tuple of strings as
a delimited string.  It works fine but I discovered an abnormality
with LIKE.  The right side of a like expression is being passed to the
converter, so it has to be a one-item tuple instead of a string.  This
makes my model unintuitive.  Am I doing something wrong or is this
just a corollary of how TypeDecorator works?


 m.Chemical.synonyms.like((like,)) # Ugly

 q = q.filter(m.UN.synonyms.like((% + term + %,))) # Ugly


class MultiText(sa.types.TypeDecorator):
Store a tuple of string values as a single delimited string.

Legal values are a tuple of strings, or ``None`` for NULL.
Lists are not allowed because SQLAlchemy can't recognize in-place
modifications.

Note that during SQL queries (e.g., column LIKE %ABC%), the
comparision is against the delimited string.  This may cause unexpected
results if the control value contains the delimeter as a substring.


impl = sa.types.Text

def __init__(self, delimiter, *args, **kw):
Constructor.

The first positional arg is the delimiter, and is required.

All other positional and keyword args are passed to the underlying
column type.

if not isinstance(delimiter, basestring):
msg = arg ``delimiter`` must be string, not %r
raise TypeError(msg % delimiter)
self.delimiter = delimiter
sa.types.TypeDecorator.__init__(self, *args, **kw)

def process_bind_param(self, value, dialect):
Convert a tuple of strings to a single delimited string.

Exceptions:
``TypeError`` if the value is neither a tuple nor ``None``.
``TypeError`` if any element is not a string.
``ValueError`` if any element contains the delimeter as a substring.

if value is None:
return None
if not isinstance(value, tuple):
msg = %s value must be a tuple, not %r
tup = self.__class__.__name__, value
raise TypeError(msg % tup)
for i, element in enumerate(value):
if self.delimiter in element:
msg = delimiter %r found in index %d of %s: %r
tup = (self.delimiter, i, self.__class__.__name, value)
raise ValueError(msg % tup)
return self.delimiter.join(value)

def process_result_value(self, value, dialect):
Convert a delimited string to a tuple of strings.
if value is None:
return None
elif value == :
return ()
elements = value.split(self.delimiter)
return tuple(elements)

def copy(self):
return self.__class__(self.delimiter, self.impl.length)




-- 
Mike Orr sluggos...@gmail.com

--~--~-~--~~~---~--~~
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] django like inspectdb

[dusans]

is there something similar to inspectdb in sqlalchemy
where it returns orm classes for tables already in the db?
--~--~-~--~~~---~--~~
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] pretend like an object was loaded, not created

[qvx]

I have a web application which is accessed from different sub-domains.
Each sub-domain corresponds to one row/object in installation table.
I am fetching this one row/object on every request which is
unnecessary.
My question is: how can I fetch this object only once and somehow
stuff it inside a session on each request, from memory.

Currently I'm doing this:

def web_method():
# fetch every time:
subdomain = get_subdomain()
installation = session.query(Installation).filter_by
(subdomain=subdomain).one()

# use installation as filter
session.query(SomeOb).filter_by(installation=installation).all()

I want this:

def web_method2():
# get from cache
installation_data = get_subdomain_data()
installation = Installation(**installation_data)

# somehow add installation to session so that it appears
# as if it has just been fetched from db
???

# this must work
session.query(SomeOb).filter_by(installation=installation).all()

# this must also work
# select child/parent objects from corresponding relations
len(installation.related_child_object_list)
installation.related_parent_object

Thanks,
Tvrtko
--~--~-~--~~~---~--~~
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: LIKE filter and psycopg2

[Michael Bayer]

On Nov 12, 2008, at 12:14 PM, Steve Howe wrote:


 Hello all,

 I'm having trouble using SQLAlchemy 0.50.rc3 and like query  
 filters with the
 psycopg2 adapter:

 class Activity(Base):
  __tablename__ = 'activities'
  id = Column(Integer, primary_key=True, autoincrement=True)
  name = Column(Unicode(100), index=True)

 [...]
 filter_name = 'john';
 activities = db_session.query(model.Activity)
 activities = activities.filter(model.Activity.name.like('%%' +  
 filter_name +
 '%%'))

 The query run from the above statements does not get expanded by the  
 adapter:

 SELECT activities.id AS activities_id, activities.name AS  
 activities_name
 FROM activities
 WHERE activities.name LIKE %(name_1)s

the bind parameter %(name_1)s is being populated with the value of '% 
%' + filter_name + '%%'.   This should be all you need.

 This syntax:

 activities = activities.filter(name ~~ '%%%s%%' % filter_name) )

 ... will produce a valid SQL:

 SELECT activities.id AS activities_id, activities.name AS  
 activities_name
 FROM activities
 WHERE name ~~ '%john%'

 However, it raises this error:

 [...]
self.dialect.do_execute(cursor, statement, parameters,  
 context=context)
  File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc3-
 py2.5.egg/sqlalchemy/engine/default.py, line 122, in do_execute
cursor.execute(statement, parameters)
 TypeError: 'dict' object is unindexable

 I'm stuck. What should I be doing ? Use another syntax ? Replace  
 psycopg2's
 paramstyle to non-escaping mode ?

its not clear to me what is actually going wrong in that case.   does  
it work if you use a raw psycopg2 script ?



--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: LIKE filter and psycopg2

[Steve Howe]

Hello Michael,

 its not clear to me what is actually going wrong in that case.   does
 it work if you use a raw psycopg2 script ?
Yes it does, however I figured out the print statement from the other block 
was just printing what would be sent to the adapter and not to the database - 
that confused me.

It's working now, thanks, I needed the ILIKE function.
-- 
Best Regards,
Steve Howe

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: LIKE doesn't work in direct SQL queries

[Michael Bayer]

try two percent signs to escape it - %%.


On May 14, 2008, at 10:50 AM, Artur Siekielski wrote:


 Hi.
 I want to execute ready SQL query using SA's engine:

 engine.execute(r''' SELECT * FROM City WHERE name LIKE 'a%' ''')

 I get this strange error:

 /usr/share/python2.5/site-packages/SQLAlchemy-0.4.6dev_r4720- 
 py2.5.egg/
 sqlalchemy/engine/default.py in do_execute(self, cursor, statement,
 parameters, context)
128
129 def do_execute(self, cursor, statement, parameters,
 context=None):
 -- 130 cursor.execute(statement, parameters)
131
132 def is_disconnect(self, e):
 TypeError: 'dict' object is unindexable

 I'm using Python 2.5.2, PostgreSQL 8.3.1, psycopg2 2.0.7, SQLAlchemy
 0.4.5 and latest from trunk (0.4 branch).
 What is not working is the percent sign: when I don't use them,
 everything is OK. Underscores work. I suspect that it's an error in
 psycopg2, which thinks that percents are for parameters.

 It's strange that such a simple thing doesn't work - or am I doing
 something wrong?

 Regards,
 Artur
 


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: LIKE doesn't work in direct SQL queries

[Artur Siekielski]

It works :). Ah, I was sure I have tried it :).
Thanks for fast reply.

On 14 Maj, 18:42, Michael Bayer [EMAIL PROTECTED] wrote:
 try two percent signs to escape it - %%.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] SQLAlchemy: like and security (sql injection attacks)

[Felix Schwarz]

Hi,

I have a question related to sql injection when using a clause like 
this: User.c.username.like('%' + userinput + '%')

What restrictions do I have to put on the variable userinput? Of course, 
I will ensure that is no percent character ('%') in userinput. Is that 
enough (assuming that SQLAlchemy will do the rest by applying 
database-specific quoting rules) or do I need to filter more characters? 
Is this specific for database used?

Thank you very much
fs

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: like doesn't work with objects

[Techniq]

On Jun 6, 12:37 am, Mike Orr [EMAIL PROTECTED] wrote:
 On 6/5/07, Techniq [EMAIL PROTECTED] wrote:





  I'm going through the wiki cookbook
 http://docs.pythonweb.org/display/pylonscookbook/SQLAlchemy+for+peopl...
  and I'm discovering that even though 'model.class.c.column_name.like'
  is available it doesn't perform a LIKE in the query.

  from 'paster shell'

  In [20]:
  model.Accounting.select(model.Accounting.c.usr.like('TSmith'))
  Out[21]:
  [syslogsql.models.Accounting object at 0x2626d70,
   syslogsql.models.Accounting object at 0x2626ad0,
   syslogsql.models.Accounting object at 0x2626910]
  In [22]: model.Accounting.select(model.Accounting.c.usr.like('Smith'))
  Out[22]: []

  ...BUT...

  In [23]: model.Accounting.select(model.Accounting.c.usr.like('%Smith
  %'))
  Out[27]:
  [syslogsql.models.Accounting object at 0x262d670,
   syslogsql.models.Accounting object at 0x2626d70,
   syslogsql.models.Accounting object at 0x2626ad0,
   syslogsql.models.Accounting object at 0x262d770,
   syslogsql.models.Accounting object at 0x262d790,
   syslogsql.models.Accounting object at 0x262d7b0,
   syslogsql.models.Accounting object at 0x2626910]

  Should I have to add the '%' around the string?

 What is the SQL in those cases?  (engine.echo = True)

 Yes, you need the '%':  column.like('%Smith%')  The reason is that you
 may prefer the wildcard in a different position: '%son', 'ra%s'.

 It should work.  I'm using an ORM query like that now, and I think I
 had a select query with .like before that.

 --
 Mike Orr [EMAIL PROTECTED]\

OK, that's all I needed was to confirm that the '%' was required, I
didn't think about having in in a diff position in the string, makes
perfect sense.  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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: like doesn't work with objects

[Mike Orr]

On 6/5/07, Techniq [EMAIL PROTECTED] wrote:

 I'm going through the wiki cookbook
 http://docs.pythonweb.org/display/pylonscookbook/SQLAlchemy+for+people+in+a+hurry
 and I'm discovering that even though 'model.class.c.column_name.like'
 is available it doesn't perform a LIKE in the query.

 from 'paster shell'

 In [20]:
 model.Accounting.select(model.Accounting.c.usr.like('TSmith'))
 Out[21]:
 [syslogsql.models.Accounting object at 0x2626d70,
  syslogsql.models.Accounting object at 0x2626ad0,
  syslogsql.models.Accounting object at 0x2626910]
 In [22]: model.Accounting.select(model.Accounting.c.usr.like('Smith'))
 Out[22]: []

 ...BUT...

 In [23]: model.Accounting.select(model.Accounting.c.usr.like('%Smith
 %'))
 Out[27]:
 [syslogsql.models.Accounting object at 0x262d670,
  syslogsql.models.Accounting object at 0x2626d70,
  syslogsql.models.Accounting object at 0x2626ad0,
  syslogsql.models.Accounting object at 0x262d770,
  syslogsql.models.Accounting object at 0x262d790,
  syslogsql.models.Accounting object at 0x262d7b0,
  syslogsql.models.Accounting object at 0x2626910]

 Should I have to add the '%' around the string?

What is the SQL in those cases?  (engine.echo = True)

Yes, you need the '%':  column.like('%Smith%')  The reason is that you
may prefer the wildcard in a different position: '%son', 'ra%s'.

It should work.  I'm using an ORM query like that now, and I think I
had a select query with .like before that.

-- 
Mike Orr [EMAIL PROTECTED]

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---