[sqlalchemy] is it possible to limit a session.query() to return only certain columns - not orm objects ?

i know this is usually done with the select() mechanism. i have a convenience interface sitting on top of some sqlalchemy routines. i'd love to just use this same code block and have it return only a certain column or two - and not rebuild the query itself. -- You received this message because

[sqlalchemy] Re: is it possible to limit a session.query() to return only certain columns - not orm objects ?

you want to look at: 1. query.values(Class.foo, Class.bar) ah, thank you very much. i was struggling with that earlier from the API -- i thought i only needed to do query.values( foo , bar ) but that didn't work. 2. Session.query(Class.foo, Class.bar).all() #2 is in the ORM tutorial,

[sqlalchemy] Re: is it possible to limit a session.query() to return only certain columns - not orm objects ?

On Jun 20, 9:41 pm, Michael Bayer mike...@zzzcomputing.com wrote: its for a scalar:http://www.sqlalchemy.org/docs/reference/orm/query.html?highlight=que... I had read that, but it was unclear. Initially I thought that scalar meant a single column. ie: values(foo.bar) = ((17,),(18,)) ;

[sqlalchemy] functional indexes, ddl and creation

i'm trying to get a functional index to work on an email address a table: class EmailAddress(DeclaredTable): __tablename__ = 'email_address' id = sa.Column(sa.Integer, primary_key=True) address = sa.Column(sa.Unicode(255), nullable=False) which would have in postgres a command

[sqlalchemy] DetachedInstanceError after transaction

I seem to get this after I call a transaction.commit sqlalchemy.orm.exc.DetachedInstanceError DetachedInstanceError: Instance Useraccount at 0x103f9f610 is not bound to a Session; attribute refresh operation cannot proceed In the following code, the first call will print the id [ i

[sqlalchemy] Re: DetachedInstanceError after transaction

my stuff doesn't handle the transaction commit - that's purely transaction / pyramid_tm so i'll look into that code to see if its closing it. great lead, thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to

[sqlalchemy] Re: DetachedInstanceError after transaction

that seems to be it... when you commit, there is a call to _finish() http://www.zodb.org/zodbbook/transactions.html#commit _finish() is documented under abort() http://www.zodb.org/zodbbook/transactions.html#abort and it includes a session.close() -- You received this message because

[sqlalchemy] Re: DetachedInstanceError after transaction

could anyone point in the right direction to either: 1. rebind an object to a new session or 2. allow objects to still be 'read' in the detached state ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to

[sqlalchemy] Re: DetachedInstanceError after transaction

eric- thanks. I'll post a followup on the pylons list. i've already got a call to session.flush() the problem is that i need the transaction itself committed in this block. i have a series of database transactions that happen within the request. the first database transaction should error

[sqlalchemy] Question about Selecting with an Association Pattern

I have a question about the Association Pattern http://docs.sqlalchemy.org/en/latest/orm/relationships.html#association-object I have a structure as such: useraccount group useraccount_2_group ( useraccount_id , group_id , metadata like relation type / date / etc ) Is it

[sqlalchemy] Re: sqlalchemy seem to save entries to the database in an unordered way

Assuming you're not seeing this immediately after the INSERT ( which your question doesn't suggest ) IIRC, in order to update in postgres it would delete an entry and replace it at the end of the table's file. So records were inserted in the right order, but could be reordered on an edit. --

[sqlalchemy] Another Question about Selecting with an Association Pattern

In this example, I have 3 classes: Useraccount id name Group id name Useraccount2Group id role_id useraccount_id group_id useraccount= relationship group= relationship With them, I'd like to do the following 2

[sqlalchemy] how to filter a timestamp field by date ?

I this class/table class Posting: id INT timestamp_created TIMESTAMP i'm trying to figure out how to execute this sql SELECT * FROM posting WHERE date(timestamp_created) = date(%s) ; i can't seem to find a date operator in the api docs. i swear i've seen one before. -- You received

[sqlalchemy] Re: how to filter a timestamp field by date ?

thanks to you both! i managed to just now figure out how to do it with sql.expression.cast as well : filter( sqlalchemy.sql.expression.cast(model.core.Posting.timestamp_created,sqlalchemy.types.Date) == self.request.app_meta.datetime.date() ) the docs on the .func were confusing at first.

[sqlalchemy] How can I use and_ and or_ in the orm query ?

they're in the sql.expression , and the orm.query object doesn't have those methods. what query method do i use to integrate them ? -- 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.

[sqlalchemy] Re: How can I use and_ and or_ in the orm query ?

Dan Kuebrich messaged me off-list -- the filter() method seems to accept the output of and_ or_ it would be great if the docs in each section referenced this. On May 10, 5:19 pm, Jonathan Vanasco jonat...@findmeon.com wrote: they're in the sql.expression , and the orm.query object doesn't have

[sqlalchemy] Re: How can I use and_ and or_ in the orm query ?

it's not in the api docs though - which tends to come up first on the keyword search and is the more obvious place to look. http://docs.sqlalchemy.org/en/rel_0_7/orm/query.html#sqlalchemy.orm.query.Query.filter

[sqlalchemy] how can i generate IS NOT True instead of != True ( orm ) ?

in postgresql i have a boolean field that allows Null values. i'd like to query for the items that are not 'true' filter( tablename.is_deleted != True ) creates this sql: is_deleted != True however this is incorrect and doesn't match the resultset i want. it needs to read : is_deleted

[sqlalchemy] Re: how can i generate IS NOT True instead of != True ( orm ) ?

To be fair, it is correct in terms of doing what you asked, though if you want it to include NULLs I agree it doesn't do what you want... You're absolutely correct. Poorly worded on my part. I meant to convey that it's not the correct statement for me to call ; it is indeed the correct sql

[sqlalchemy] is there a reason why lower() isn't a column operation ?

just wondering why i have to do : sqlalchemy.sql.func.lower( class.column ) == string.lower() instead of : class.column.lower() = string.lower() btw - i know in the archives people have mentioned doing an ilike search, but a WHERE lower(column) = 'string' search will search against a

[sqlalchemy] Re: is there a reason why lower() isn't a column operation ?

well, that's a lot of good reasons! -- 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

[sqlalchemy] Re: is there a reason why lower() isn't a column operation ?

also, just to clarify - i didn't want a case insensitive compare but a specific sql generated just to give a quick illustration With this table structure: CREATE TABLE names ( id serial not null primary key , name varchar(255) not null ); CREATE UNIQUE INDEX

[sqlalchemy] Feature request - sqlalchemy.sql.operators.ColumnOperators.not_in_

I would really like to make a feature request for a `not_in_` operator for sqlalchemy.sql.operators.ColumnOperators there are at least 2 ways to make a valid query right now : filter( ~ table.column.in_( (1,2,) ) ) filter( sqlalchemy.not( table.column.in_( (1,2,) ) ) so there isn't any

Re: [sqlalchemy] Feature request - sqlalchemy.sql.operators.ColumnOperators.not_in_

thanks for the reply. if this ever happens, ., .8, .9, etc doesn't matter. would just be so useful. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit

[sqlalchemy] Problem writing a query with distinct

I'm trying to write a query against Postgres and it's driving me a bit mad. Hoping someone here can help. I'm make the example in something topical... voting! Given: Candidate id name Vote id candidate_id (fkey on Candidate) yay (bool) With this

[sqlalchemy] selecting from a subquery in sqlalchemy

does anyone have a good reference for this ? i'm trying to select Table.Column , count(Table.Column) however I'm not querying the Table, instead the raw sql is more like SELECT a , COUNT(a) FROM ( SELECT FROM TABLE ) AS sq1 i've never had to select from a subquery in sqlalchemy

Re: [sqlalchemy] selecting from a subquery in sqlalchemy

ah, it was in the tutorial thanks so much! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/sY9szZJcGS0J. To post to this group, send email to

[sqlalchemy] how can i use bind parameters with an ilike ?

i need to search something like this: select name from users where name ilike '%jonathan%'; i know i could do this: dbSession.query( models.User )\ .filter( models.User.name.ilike( %%%s%% % 'jonathan' ) but the name is coming from the web, so i want treat it with a bind, like

[sqlalchemy] Re: how can i use bind parameters with an ilike ?

How about using   dbSession.query(models.User).filter(models.User.contains(name)) That generates a LIKE statement , not an ILIKE. I need to case- insensitive match. as a stopgap, i might be able to chain a lower() in there, but something like this should be support. also i'm not so sure

[sqlalchemy] Re: how can i use bind parameters with an ilike ?

On Jan 29, 2:04 pm, Michael Bayer mike...@zzzcomputing.com wrote: ilike is available using column.ilike(some string).   You can turn it into a contains by adding in the appropriate % signs manually.  If you want to do lower() manually, then you can say func.lower(column).contains('some

[sqlalchemy] Re: how can i use bind parameters with an ilike ?

On Jan 29, 7:25 pm, Michael Bayer mike...@zzzcomputing.com wrote: User.name.ilike('%%' + literal(name) + '%%') though even if you are saying 'ilike(%%%s%% % name)', that string value is still converted to a bound parameter, so there's no SQL injection here. i didn't know that was converted

[sqlalchemy] Re: how can i use bind parameters with an ilike ?

i forgot to add that this all comes from my irrational fear of Little Bobby Tables ( http://xkcd.com/327/ ) -- 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] Re: Tracking changes made to mapped objects

when I do stuff like this on the Web (single and multi-page edits), I build up a dict of 'changes'. When rendering forms, I default to the key in changes or fallback to the sqlalchemy object. when ready to save, I copy over the changes to the object - if there are any changes - and flush. the

[sqlalchemy] Re: updating with a Session ?

ah ha! thanks!!! these two approaches worked: results = api.bootstrapped.request.dbSession.writer.execute( model.core.Useraccount.__table__\ .update()\ .where( model.core.Useraccount.id.in_( uids ) )\ .values( migration_a = True )

[sqlalchemy] Is this the right way to do a timestamp filter against CURRENT_TIMESTAMP in postgres ?

i think this is right... query= dbSession.query( model.Post )\ filter_by( is_published = True )\ filter( model.Post.timestamp_publication = sqlalchemy.sql.expression.current_timestamp() ) this is generating the right sql. i just wanted to make

[sqlalchemy] Re: Is this the right way to do a timestamp filter against CURRENT_TIMESTAMP in postgres ?

ok. is from sqlalchemy.sql import func still the recommended import ? -- 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.

[sqlalchemy] Re: Is this the right way to do a timestamp filter against CURRENT_TIMESTAMP in postgres ?

On Feb 15, 11:17 am, Michael Bayer mike...@zzzcomputing.com wrote: or just from sqlalchemy import func sure thanks. friday is try to pay off technical debt day. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group

[sqlalchemy] is there a more proper way to chain dynamic or clauses ?

basd on a bunch of error messages, this example works... criteria = ( ('male',35),('female','35) ) query = session.query( model.Useraccount ) ands = [] for set_ in criteria : ands.append(\ sqlalchemy.sql.expression.and_(\

[sqlalchemy] Re: is there a more proper way to chain dynamic or clauses ?

thanks. i was really focused on the query.filter(sa.or_(*ands)) concept. that's what seemed kind of weird to me. -- 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] Limit invalidates query results on view - 0.4.8

I have query on a view that looks like this: query= dbSession.query(_class).filter('entry_status = 2').order_by ( _class.c.entry_authored_on.desc() ).limit(3).offset(0) results= query.all() print query print - - - - - - print results print == == == this always returns no results. however

[sqlalchemy] after a week or so of working fine, my app starts experiencing sqlalchemy recursion issues

It starts with the code below, and the last two lines in visit_select and in process repeat indefinitely until the recursion error is generated i can't wrap around why this would happen -- i thought I had bad sql or something similar, but restarting the app completely fixes this issue. like

[sqlalchemy] Re: after a week or so of working fine, my app starts experiencing sqlalchemy recursion issues

Michael- There are no jobs on the server. This is happening on a small Pylons site that gets ~10k hits a week; I ran a bench of 30k hits on my dev machine, and couldn't recreate. But on the server it happens every week. If i restart on Monday, it happens on Monday; Tuesday - Tuesday, etc. The

[sqlalchemy] confusion with outer join setup

hi. i'm trying to join two tables in sqlalchemy for a query, and having an issue. from what I understand , it seems that the SQL which is generated is fine ( and it does run if i 'print' and substitute in variables ) -- the problem is that due to my structuring of how I handle date ranges, the

[sqlalchemy] Re: confusion with outer join setup

let me simplify this , maybe it'll make sense to someone presented differently: # do we need to restrict this within a date range ? dates= [] if date_start: dates.append( class_a.timestamp_registered = date_start ) if date_end: dates.append( class_a.timestamp_registered =

[sqlalchemy] CURRENT_TIMESTAMP AT TIME ZONE 'UTC'

i need to replace my calls to `sqlalchemy.func.current_timestamp()` with something that generates CURRENT_TIMESTAMP AT TIME ZONE 'UTC' anyone have quick advice ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and

[sqlalchemy] Re: Dialect for Vertica db connectivity ?

doesn't vertica just implement the postgresql sql set one-to-one ? i haven't looked at it in a while, but i remember their sales pitch the trial was all postgres based. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this

[sqlalchemy] Re: CURRENT_TIMESTAMP AT TIME ZONE 'UTC'

much thanks, Audrius i ended up doing the following (last night) before i read this in the morning. 1. s/sqlalchemy\.func\.current_timestamp/sql_now/g 2. def sql_now(): return sqlalchemy.sql.text((CURRENT_TIMESTAMP AT TIME ZONE 'UTC')) i'm working on implementing your more-proper method now. i

[sqlalchemy] Re: Dialect for Vertica db connectivity ?

@Femi - I did a quick search online, but couldn't find any current ( since HP acquisition ) documentation. HOWEVER -- all of the old documentation and QAs that are still online talk about Vertica reimplementing the PostgreSQL syntax and functions. That's in line with what I remembered earlier,

[sqlalchemy] Re: SQLAlchemy / Memcache layer

i came up with a novel approach that works, but is sadly not integrated with SqlAlchemy yet. It was more proof of concept and creates a parallel Object Model. It's not good but it's simple and seems to work very well . I'm just going to braindump what I do. It might give you some ideas *

[sqlalchemy] Re: Automatic created and modified timestamp columns (best practice?!)

FWIW, I've found this sort of stuff to be better done with CURRENT_TIMESTAMP than NOW() , or doing a NOT NULL constraint with no-default and passing in a time from the application. On databases that support it, CURRENT_TIMESTAMP is locked to the transaction and/or statement while NOW() is

[sqlalchemy] is this select correct ?

This seems to work. I'm just hoping to run this past the wisdom of other users... SqlAlchemy + PostgreSQL We have a table called `example` which contains versioned fields of a record, attributed to an owner. Goal: assemble a record of the most recent field versions for a given owner class

[sqlalchemy] how can i exclude a table on queries ?

Given class Person: id class Topic: id class Person2Topic : id topic_id - fkeys topic(id) person_id - fkeys person(id) class Message: id person_id_author - fkeys person(id) topic_id - fkeys topic(id) I wanted to select by joining the Person2Topic table directly, with a filter query(

Re: [sqlalchemy] how can i exclude a table on queries ?

into this. If i don't find a bug in my code, I'll post a reproducable test-case on github. On Friday, April 26, 2013 7:18:24 PM UTC-4, Michael Bayer wrote: On Apr 26, 2013, at 7:10 PM, Jonathan Vanasco jona...@findmeon.comjavascript: wrote: Given class Person

[sqlalchemy] can a SqlAlchemy session be extracted from an object ?

i'm trying to deal with some old code , and need to 'log' a change. in the current code block, I do not have a SqlAlchemy session object - i merely have an ORM object that exists in a given session. is it possible to extract the session object from that ORM object, so I can just add a new ORM

[sqlalchemy] Re: can a SqlAlchemy session be extracted from an object ?

this is WONDERFUL. thanks! On Thursday, May 2, 2013 12:47:25 PM UTC-4, Jonathan Vanasco wrote: i'm trying to deal with some old code , and need to 'log' a change. in the current code block, I do not have a SqlAlchemy session object - i merely have an ORM object that exists in a given

[sqlalchemy] how are foreign relations stored ?

I've been using a utility method 'columns_as_dict' to help store my data in a cache. It works well. A problem I've encountered... i need to access the related data that i've eagerloaded. My current function looks like this def columns_as_dict(self): as_dict = dict(

Re: [sqlalchemy] how are foreign relations stored ?

, at 1:37 PM, Jonathan Vanasco jona...@findmeon.comjavascript: wrote: I've been using a utility method 'columns_as_dict' to help store my data in a cache. It works well. A problem I've encountered... i need to access the related data that i've eagerloaded. My current function looks like

[sqlalchemy] best practice for SqlAlchemy and webapps ?

Mike blew my mind at the intro to sqlalchemy presentation this week. I learned at least 5 things that I had missed. I still can't believe that the connection itself is basically lazy-loaded and SqlAlchemy doesn't even connect to the DB until you do something. I wrote a bunch of code to

Re: [sqlalchemy] best practice for SqlAlchemy and webapps ?

On Friday, June 21, 2013 2:23:54 PM UTC-4, Michael Bayer wrote: fairly recently i wrote up as much as I could come up with on this, which you can see first in the Session FAQ: http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#session-frequently-asked-questions and then regarding

[sqlalchemy] is it possible to adapt an object without configuring SqlAlchemy ?

I have a custom object that I use within my application: class Foo(bar): def __init__( self , value=None ): self.value = value def __int__ def __str__ def __cmp__ is there any __baz__ method I can use, that will return a value SqlAlchemy can

[sqlalchemy] Re: is it possible to adapt an object without configuring SqlAlchemy ?

actually, nevermind i just remembered Mike talking about __repr__ last week in his presentation and I tried that. it works. -- 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

Re: [sqlalchemy] SQLAlchemy 0.8.2 released

On a tangent... I just noticed that there are several dozen (if not hundreds) of SqlAlchemy projects on PyPi Perhaps SqlAlchemy is now large enough that it should have it's own classifier ? Something like... Topic :: Database :: Front-Ends :: SqlAlchemy Topic :: Database ::

[sqlalchemy] Re: Dogpile caching: can't pickle function objects

I serialize all my cached data into a dict or json before caching, then unserialize into whatever object i need. -- 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] Re: best practice for SqlAlchemy and webapps ?

Mike, thanks again. I finally found time to integrate your recommendations. https://github.com/jvanasco/pyramid_sqlassist -- 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

[sqlalchemy] nested transaction syntax is a bit odd ( wishlist? )

the nested transaction / savepoint syntax in sqlalchemy appears to be: session = SessionFactory() session.begin() # main tx session.begin_nested() # outer tx session.begin_nested() # inner tx session.rollback() # innter tx session.commit() #

Re: [sqlalchemy] nested transaction syntax is a bit odd ( wishlist? )

On Wednesday, July 17, 2013 7:02:10 PM UTC-4, Michael Bayer wrote: because you can transfer control to some other part of the program that doesn't know what kind of transaction has started; it only knows it needs to call commit() or can rollback() if something goes wrong. It's a simple

Re: [sqlalchemy] nested transaction syntax is a bit odd ( wishlist? )

Could something be added to the docs then to elaborate on how the context manager works in regards to how things are committed/rolledback ? I just added comments to the existing example ( http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html?highlight=begin_nested#using-savepoint )

[sqlalchemy] Association Proxy question ( or other ORM feature )

I have three tables that are structured somewhat like this: Document * id * all_images = sa.orm.relationship(Document2Image) Image * id * all_documents = sa.orm.relationship(Document2Image) Document2Image * id * document_id * image_id * image_type_id * document = sa.orm.relationship(Document)

[sqlalchemy] Re: Association Proxy question ( or other ORM feature )

Thanks! This worked: Document Document2Image_1 = sa.orm.relationship( Document2Image, primaryjoin=and_( Document2Image.document_id==Document.id , Document2Image.image_type_id==1 ) , uselist=False ) document_image_1 = association_proxy('Document2Image_1', 'image') -- You received this

[sqlalchemy] how to access dirty/changed ORM attributes ?

Is there a way to access the changed attributes of an ORM object ? Everything I've dug up refers to the dirty objects in a session, not the attributes of an object. ( I'm trying to automate some revision logging /auditing of sqlalchemy objects ) -- You received this message because you

Re: [sqlalchemy] how to access dirty/changed ORM attributes ?

there's a typo in the docs: bad: attr_state = insp.attr.some_attribute works: attr_state = insp.attrs.some_attribute -- 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

Re: [sqlalchemy] how to access dirty/changed ORM attributes ?

Thanks, Mike! That let me slap together a quick mixin class to store versioned data (of specific columns) in PostgreSQL - class RevisionObject(object): revision_columns = None revision_id = sa.Column(sa.Integer, nullable=False, default=0 ) revision_history = sa.Column(

Re: [sqlalchemy] Code organization with declarative models

Just for a bit of perspective... My SqlAlchemy integration for a project is connected to two distinct applications : - Pyramid -- Web Application - Celery -- Background Processing We're also hoping to get it working on a third - Twisted -- More Background Work There are a lot of moving parts

[sqlalchemy] Under what circumstances will a `inspect(myobject).attrs.myattribute.history` be a sequence of more than one item ?

As previously discussed, i'm using an object's history to log changes to the database within an application. http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html?highlight=history#sqlalchemy.orm.attributes.History Each tuple member is an iterable sequence I'm trying to figure out when I

[sqlalchemy] how to tell which columns are deferred in orm ?

i'm trying to generate a list of non-deffered columns from an object referencing this example: class Book(Base): __tablename__ = 'book' book_id = Column(Integer, primary_key=True) title = Column(String(200), nullable=False) summary = Column(String(2000))

Re: [sqlalchemy] Under what circumstances will a `inspect(myobject).attrs.myattribute.history` be a sequence of more than one item ?

On Thursday, August 15, 2013 12:02:02 AM UTC-4, Michael Bayer wrote: yes it will always be one element for a scalar reference, a collection for collections. the ORM internally treats everything like a collection, kind of another artifact that probably wouldnt have been the case if this API

[sqlalchemy] Re: Modification tracking

I had to do this last week. I posted a recipe in this thread: https://groups.google.com/forum/#!topic/sqlalchemy/Xr1llnf5tzQ tracked objects inherit from RevisionObject, which adds 2 columns to the database: revision_id (INT) revision_history (HSTORE) it also adds 2 methods:

[sqlalchemy] testing for an association proxy (possible bug and patch included)

I have this general structure: class Person: # orm relationships are preceded by (o)ne or (l)ist o_Person2Address_ActiveShipping = sa.orm.relationship( Person2Address, primaryjoin=and_( Person2Address.person_id==Person.id , Person2Address.role_id=='active-shipping' ),

Re: [sqlalchemy] Calculate birthdays

On Wednesday, August 28, 2013 12:52:03 PM UTC-4, herzaso wrote: What's wrong with Member.dateofbirth==datetime.today() ? datetime.today() is now -- or August 28, 2013 12:52:03 PM UTC-4 The OP wants a sql operation that matches the Month+Day of Member.dateofbirth to the Month+day of today.

Re: [sqlalchemy] Calculate birthdays

sorry, it looks like the OP did want people born on the current month/day/year combo. you should be able to wrap all the comparisons in a date like this : Member.query.filter( sqlalchemy.func.date(Member.dateofbirth) == '2013-08-27' ).all() Member.query.filter(

[sqlalchemy] Re: Possible bug in select.append_whereclause()?

this looks a little weird to me, because it seems like you're using parts of the ORM (namely sessionmaker) and the rest is the Engine. anyways, you want to address the `table.column`; the results don't exist yet. you can print out any query whenever you'd like below are 2 ways to generate

[sqlalchemy] Re: Can you count the active/open sessions?

in general , It's worth reading these 2 sections of the docs if you haven't already : http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#session-frequently-asked-questions http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#using-thread-local-scope-with-web-applications -- You received

Re: [sqlalchemy] watining for table metadata lock

MyISAM doesn't support transactions, but it does support locking. According to the docs, it actually relies on table locking for certain operations. What are your sqlalchemy connection strings for the engine ? Have you tried explicitly setting autocommit mode within the query ? Have you tried

Re: [sqlalchemy] How-to filter by the time part of a datetime field?

In addition to using `func.*` methods, there's also the `extract` method that works (in most databases) specifically on datetime objects. sqlalchemy.sql.expression.extract(*field*,

Re: [sqlalchemy] Calculate birthdays

that's less of a SqlAlchemy question and more of a general database question. there are a handful of approaches on StackOverflow, and the easier approach can differ across databases. try searching for birthdate/birthday range and postgresql or mysql . I'd suggest that you find one there,

Re: [sqlalchemy] sqlite string concats and datetime arithmetics

This might be a bug then. String || Integer ; Integer || String - PostgreSQL and sqlite both allow for a sting integer to be concat together into a string. Order does not matter. Integer || Integer - PostgreSQL will error if 2 ints are concat together. - sqlite seems to cast both into a

[sqlalchemy] Re: django user (using django ORM) + sqlalchemy for other db tables

Honestly, I wouldn't do this. Django has a lot of magic under the hood, and it's ORM does some very specific things to make this magic happen. It's not just the auth, it's how everything is structured in the database and how the app integrates with the database. You're likely to break things

Re: [sqlalchemy] Re: django user (using django ORM) + sqlalchemy for other db tables

On Tuesday, September 10, 2013 6:47:41 AM UTC-4, Dennis wrote: Thanks for the advice -- your recommendations against this configuration were a surprise to me... It's making me rethink what I want (and how much I want it). I'll post this as a comment to the first stackoverflow question so

Re: [sqlalchemy] Mutable column_properties

I just spend 30mins with pdb; I was wrong ; I think it would be way too hard to get it into the ORM. The way MutableDict seems to be currently integrated, the entire value is updated for the key , and the original value seems to be obliterated. Outside of the ORM -- do you have any references

[sqlalchemy] Re: Mutable column_properties

I might be interpreting all this wrong, but I don't think the column_property needs to be writable. I think the situation is this: Under Postgres, with HSTORE it's possible to INSERT/UPDATE/DELETE only certain values from within the store. Under SqlAlchemy, the entire object is

[sqlalchemy] Re: Mutable column_properties

Actually, this is more correct for multi-key updates: -- select before update SELECT id, kv-'x' AS kv_x , kv-'y' AS kv_y , kv-'z' AS kv_z FROM test_class ; -- update 2 columns ; these 3 are identical kvkv UPDATE test_class SET kv = kv || hstore(ARRAY['z','zz','x','xx']);

Re: [sqlalchemy] Mutable column_properties

Sweet. This works : results = dbSession.execute( TestClass.__table__\ .update()\ .values( kv = TestClass.__table__.c.kv + sqlalchemy.dialects.postgresql.hstore(sqlalchemy.dialects.postgresql.array(['zz123', 'zz123'])) ) ) stmt = select( [

[sqlalchemy] timing queries ?

is there a currently recommended approach ? looking through the backcatalog of qa (from 2008-2010) , it seems like the options are: - manually look at debug data , subtract timestamps for a general idea - write something with ConnectionEvents ( originally ConnectionProxy ) for now, i just want

Re: [sqlalchemy] timing queries ?

it might be better for the recipe to be: logger = logging.getLogger(sqlalchemy.engine.base.Engine) logger.setLevel(logging.INFO) logger.info(Query Complete! Total Time: %f % total) This will correlate it to the existing sql statement logging ( which is a way more natural place

Re: [sqlalchemy] timing queries ?

On Tuesday, September 17, 2013 3:59:31 PM UTC-4, Michael Bayer wrote: there's a modern recipe at: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Profiling but yeah, thats ConnectionEvents. what's the issue there, you need how long result fetching takes ? thanks! yeah. Some of the

[sqlalchemy] Re: Running SQL Alchemy on a QNX RTOS Machine

http://docs.sqlalchemy.org/en/rel_0_8/dialects/index.html Current external dialect projects for SQLAlchemy include: * sqlalchemy-access https://bitbucket.org/zzzeek/sqlalchemy-access - driver for Microsoft Access. https://bitbucket.org/zzzeek/sqlalchemy-access On Wednesday, September 18,

[sqlalchemy] advanced orm join advice - union + alias

I'm using the ORM ( though I could be using expressions ) , and have a bit of code that simply queries for an id + timestamp [ against a join of 6 tables ] i have another bit of code that performs a similar function. all is well. I need to , now, join the two codes, and create sql like such :

[sqlalchemy] Re: advanced orm join advice - union + alias

This is horrible, but it works ( after a few hours of trial error ) the trick was in using labels and aliases in every step it makes perfect sense looking at it now ( though ugly ), but was not how i expected to pull this off. For anyone who gets stuck: if _query_A and _query_B :

[sqlalchemy] api omission? the postgres DISTINCT ON(columns) is only in the ORM, not in the sql expression

this tripped me up when i was working on some queries. orm : distinct( column ) = DISTINCT ON (column) core : distinct( column ) = DISTINCT (column) there doesn't seem to be an easy way to write DISTINCT ON (column) in the orm -- You received this message because you are

Re: [sqlalchemy] api omission? the postgres DISTINCT ON(columns) is only in the ORM, not in the sql expression

Oh crap. I understand. I was trying to do this: query = select( func.distinct( columns ) ) and not query = distinct( columns ) i got thrown off, because the postgres tool is more like a function than an query , and there's also the .distinct() method you can toss onto a query;

  1   2   3   4   5   6   7   8   9   10   >