[sqlalchemy] Emptying out the session of new objects
Hey all, I'm unit testing my DB api's, and I've found that if I don't explicitly expunge my objects from the session at the end of each test, I get objects left over in the session when the next test runs. Is there an easy way of flushing all objects that are categorised as new in the session ?? Cheers Dave --~--~-~--~~~---~--~~ 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: Emptying out the session of new objects
Is there an easy way of flushing all objects that are categorised as new in the session ?? I think you can use session.clear(), it will remove objects from session, and persistent objects will stay on database. Att Alexandre --~--~-~--~~~---~--~~ 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: Emptying out the session of new objects
On Friday 04 January 2008 23:32:21 Alexandre da Silva wrote: Is there an easy way of flushing all objects that are categorised as new in the session ?? I think you can use session.clear(), it will remove objects from session, and persistent objects will stay on database. Att Alexandre I had been using session.clear up until now, but as of 0.4.2 I've found that I get this error for alot of my tests, object has no attribute '_sa_session_id' As background, the tests I have use a sqlite memory database, and on setUp do a meta.create_all and on tearDown do a meta.drop_all I've assumed that this error is due to me leaving objects around in the session, so my current theory is that I need to manually expunge the objects I create at the end of the test. --~--~-~--~~~---~--~~ 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] get related table object via mapped class
Hello all, is there any way to access class related by an relationship? sample: # Table definition ommited class Person(object): pass class Address(object): pass mapper(Person, person_table, properties=dict( address=relation(Address, uselist=False) ) ) now I want to access the Address class (and/or address_table) through a Person object, any sugestion? -- Alexandre da Silva Analista de Sistemas - Bacharel em Sistemas de Informação (2003-2007) gedit-todo http://alexandredasilva.wordpress.com/gedit-todo-list-plugin/ MicroDB http://www.assembla.com/space/microdb Open Sales Force System http://www.assembla.com/space/osfs opencomanche ([stopped] mail-me to get the latest source code) http://sourceforge.net/projects/opencomanche/ -- Alexandre da Silva Analista de Sistemas - Bacharel em Sistemas de Informação (2003-2007) gedit-todo http://alexandredasilva.wordpress.com/gedit-todo-list-plugin/ MicroDB http://www.assembla.com/space/microdb Open Sales Force System http://www.assembla.com/space/osfs opencomanche ([stopped] mail-me to get the latest source code) http://sourceforge.net/projects/opencomanche/ --~--~-~--~~~---~--~~ 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: Emptying out the session of new objects
On Friday 04 January 2008 14:44:32 Dave Harrison wrote: On Friday 04 January 2008 23:32:21 Alexandre da Silva wrote: Is there an easy way of flushing all objects that are categorised as new in the session ?? I think you can use session.clear(), it will remove objects from session, and persistent objects will stay on database. Att Alexandre I had been using session.clear up until now, but as of 0.4.2 I've found that I get this error for alot of my tests, object has no attribute '_sa_session_id' As background, the tests I have use a sqlite memory database, and on setUp do a meta.create_all and on tearDown do a meta.drop_all I've assumed that this error is due to me leaving objects around in the session, so my current theory is that I need to manually expunge the objects I create at the end of the test. the above means that the object has been removed from the session (which is what clear() does) BUT later someone (inside it) needs that session for something, e.g. to read some lazy-attribute or whatever. u may try to do clear_mappers() too - just a suggestion. --~--~-~--~~~---~--~~ 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: get related table object via mapped class
Person.address._get_target_class() thank you for reply, it is always more readable that Person.address.property.mapper.class_ Thank's for all -- Alexandre da Silva Analista de Sistemas - Bacharel em Sistemas de Informação (2003-2007) gedit-todo http://alexandredasilva.wordpress.com/gedit-todo-list-plugin/ MicroDB http://www.assembla.com/space/microdb Open Sales Force System http://www.assembla.com/space/osfs opencomanche ([stopped] mail-me to get the latest source code) http://sourceforge.net/projects/opencomanche/ --~--~-~--~~~---~--~~ 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: get related table object via mapped class
using the Person class directly: Person.address.property.mapper.class_ Person.address.property.mapper.mapped_table Thank you so much, works like a charm -- Alexandre da Silva Analista de Sistemas - Bacharel em Sistemas de Informação (2003-2007) gedit-todo http://alexandredasilva.wordpress.com/gedit-todo-list-plugin/ MicroDB http://www.assembla.com/space/microdb Open Sales Force System http://www.assembla.com/space/osfs opencomanche ([stopped] mail-me to get the latest source code) http://sourceforge.net/projects/opencomanche/ --~--~-~--~~~---~--~~ 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: get related table object via mapped class
On Jan 4, 2008, at 11:54 AM, Alexandre da Silva wrote: Hello all, is there any way to access class related by an relationship? sample: # Table definition ommited class Person(object): pass class Address(object): pass mapper(Person, person_table, properties=dict( address=relation(Address, uselist=False) ) ) now I want to access the Address class (and/or address_table) through a Person object, any sugestion? using the Person class directly: Person.address.property.mapper.class_ Person.address.property.mapper.mapped_table --~--~-~--~~~---~--~~ 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: get related table object via mapped class
On Friday 04 January 2008 18:54:29 Alexandre da Silva wrote: Hello all, is there any way to access class related by an relationship? sample: # Table definition ommited class Person(object): pass class Address(object): pass mapper(Person, person_table, properties=dict( address=relation(Address, uselist=False) ) ) now I want to access the Address class (and/or address_table) through a Person object, any sugestion? Person.address._get_target_class() Person.address.mapper.class_ and eventualy the table from the above mapper.local_table; see what kind of tables the mapper keeps. --~--~-~--~~~---~--~~ 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 generates invalid SQL query with empty or_()
Hi, I noticed that SQLAlchemy 0.4.2 generates invalid SQL queries if I use or_() [without parameters] in a filter condition: or_conditions = [] condition = and_(User.c.name==John, or_(*or_conditions)) query = session.query(User).filter(condition) The generated SQL is: SELECT users.id AS users_id, users.name AS users_name FROM users WHERE users.name = ? AND () ORDER BY users.oid And executing this query will cause an exception: File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 847, in _execute_compiled self.__execute_raw(context) File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 859, in __execute_raw self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 875, in _cursor_execute raise exceptions.DBAPIError.instance(statement, parameters, e) sqlalchemy.exceptions.OperationalError: (OperationalError) near ): syntax error u'SELECT users.id AS users_id, users.name AS users_name, users.no_phone AS users_no_phone, users.no_mail AS users_no_mail \nFROM users \nWHERE users.name = ? AND () ORDER BY users.oid' ['John'] I'm not sure if this is considered as a bug but I noticed that this code does not trigger an exception: or_conditions = [] condition = or_(*or_conditions) query = session.query(User).filter(condition) Thank you very much for SQLAlchemy :-) 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] doing replaces
I have code that need to do a mysql replace instead of an insert.
The quick hack to just create a string and execute has problems with
quoting things, sql injection ...
If a table.replace() is not easy to do is there a way to create a statement
that works that way. If I could do a conn.execute(statement, [list of
dict]) that
would be great.
The current code look like:
table = mappings.TableForTag(setName)
keys = sorted([str(k) for k in records[0].keys() if
table.c.has_key((str(k)))])
statement = 'REPLACE INTO %s(%s) VALUES ' %(table.name,
','.join([`%s` % k for k in keys]))
values = []
for record in records:
values.append('(%s)' % ','.join(['%s' % str(record.get(k))
for k in keys]))
values = ','.join(values)
conn = engine.connect()
conn.execute(statement + values)
I would like to be able to do something like:
table = mappings.TableForTag(setName)
conn = engine.connect()
conn.execute(table.replace(), records)
--~--~-~--~~~---~--~~
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: SQLAlchemy generates invalid SQL query with empty or_()
On Fri, Jan 04, 2008 at 05:55:21PM +0100, Felix Schwarz wrote: I noticed that SQLAlchemy 0.4.2 generates invalid SQL queries if I use or_() [without parameters] in a filter condition: or_conditions = [] condition = and_(User.c.name==John, or_(*or_conditions)) query = session.query(User).filter(condition) Since you can chain filters together I'd suggest something like: query = session.query(User).filter(User.c.name==John) if or_conditions: query = query.filter(or_(*or_conditions)) Cheers Christoph --~--~-~--~~~---~--~~ 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: doing replaces
you should be able to use conn.execute(anystring, [list of dict]) just
fine. the only thing that wont work at the moment is autocommit so
youd have to run a begin()/commit() pair.
to bring the string version of REPLACE into SQLAlchemy bind parameter
processing etc. use text() - the basic idea (havent tested this code,
may need tweaks) is this:
def replace(table, keys):
statement = 'REPLACE INTO %s(%s) VALUES (%s)' %
(
table.name,
','.join(table.c[key].name for key in keys),
','.join(: + table.c[key] for key in keys))
)
return text(statement, bindparams=[bindparam(key,
type_=table.c[key].type) for key in keys])
trans = conn.begin()
conn.execute(replace(mytable, ['one', 'two', 'three']), [{'one':1,
'two':2, 'three':3}, {...}, {...}, ...])
trans.commit()
On Jan 4, 2008, at 1:27 PM, Mike Bernson wrote:
I have code that need to do a mysql replace instead of an insert.
The quick hack to just create a string and execute has problems with
quoting things, sql injection ...
If a table.replace() is not easy to do is there a way to create a
statement
that works that way. If I could do a conn.execute(statement, [list of
dict]) that
would be great.
The current code look like:
table = mappings.TableForTag(setName)
keys = sorted([str(k) for k in records[0].keys() if
table.c.has_key((str(k)))])
statement = 'REPLACE INTO %s(%s) VALUES ' %(table.name,
','.join([`%s` % k for k in keys]))
values = []
for record in records:
values.append('(%s)' % ','.join(['%s' % str(record.get(k))
for k in keys]))
values = ','.join(values)
conn = engine.connect()
conn.execute(statement + values)
I would like to be able to do something like:
table = mappings.TableForTag(setName)
conn = engine.connect()
conn.execute(table.replace(), records)
--~--~-~--~~~---~--~~
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: doing replaces
Thanks
That is just the thing I needed. Can wait till the book comes out to
learn even
more.
As normal sqlalchemy can handle the task. I just need to learn
something new
about it.
Michael Bayer wrote:
you should be able to use conn.execute(anystring, [list of dict]) just
fine. the only thing that wont work at the moment is autocommit so
youd have to run a begin()/commit() pair.
to bring the string version of REPLACE into SQLAlchemy bind parameter
processing etc. use text() - the basic idea (havent tested this code,
may need tweaks) is this:
def replace(table, keys):
statement = 'REPLACE INTO %s(%s) VALUES (%s)' %
(
table.name,
','.join(table.c[key].name for key in keys),
','.join(: + table.c[key] for key in keys))
)
return text(statement, bindparams=[bindparam(key,
type_=table.c[key].type) for key in keys])
trans = conn.begin()
conn.execute(replace(mytable, ['one', 'two', 'three']), [{'one':1,
'two':2, 'three':3}, {...}, {...}, ...])
trans.commit()
On Jan 4, 2008, at 1:27 PM, Mike Bernson wrote:
I have code that need to do a mysql replace instead of an insert.
The quick hack to just create a string and execute has problems with
quoting things, sql injection ...
If a table.replace() is not easy to do is there a way to create a
statement
that works that way. If I could do a conn.execute(statement, [list of
dict]) that
would be great.
The current code look like:
table = mappings.TableForTag(setName)
keys = sorted([str(k) for k in records[0].keys() if
table.c.has_key((str(k)))])
statement = 'REPLACE INTO %s(%s) VALUES ' %(table.name,
','.join([`%s` % k for k in keys]))
values = []
for record in records:
values.append('(%s)' % ','.join(['%s' % str(record.get(k))
for k in keys]))
values = ','.join(values)
conn = engine.connect()
conn.execute(statement + values)
I would like to be able to do something like:
table = mappings.TableForTag(setName)
conn = engine.connect()
conn.execute(table.replace(), records)
--~--~-~--~~~---~--~~
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] Handling unique constraints
I'm using SQLAlchemy with Pylons and am having trouble validating
data. I have an App object mapped to a table with a unique constraint
on App.number.
Here's some code:
q = Session.query(App)
if app_id:
q = q.filter_by(id=app_id).first()
if q:
c.app = q
number = request.params.get('number')
notes = request.params.get('notes')
if appmodel and number:
try:
q.number = number
q.notes = notes
Session.save(q)
Session.commit()
c.message = 'Record updated'
except:
# restore pre-form data ?? how??
c.message = 'Error updating record'
return render('index.mtl')
else:
return self.index()
My questions are:
1) When I do the try statement, the value of q.number changes to
whatever the user passed in via the form -- even if it's invalid, so
that when I render the page, the invalid value is used. How do I
reset the object to have the values it had before I did the try? Do I
have to get it afresh from the db?
2) How do I let the user know which value caused the record not to
update? What information does SQLAlchemy provide back that I can use
to say: You're number must be unique... and such-and-such must be
greater than 0, etc..?
Thanks,
Matt Haggard
--~--~-~--~~~---~--~~
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: SQLAlchemy generates invalid SQL query with empty or_()
Christoph Haas wrote: Since you can chain filters together I'd suggest something like: query = session.query(User).filter(User.c.name==John) if or_conditions: query = query.filter(or_(*or_conditions)) Yes, I use a similar workaround in my application currently. But I was not sure if the described behavior is considered buggy - despite easy workarounds are available. fs smime.p7s Description: S/MIME Cryptographic Signature
[sqlalchemy] Re: Emptying out the session of new objects
On Saturday 05 January 2008 03:08:54 Michael Bayer wrote:
On Jan 4, 2008, at 7:44 AM, Dave Harrison wrote:
On Friday 04 January 2008 23:32:21 Alexandre da Silva wrote:
Is there an easy way of flushing all objects that are categorised
as new in the session ??
I think you can use session.clear(), it will remove objects from
session, and persistent objects will stay on database.
Att
Alexandre
I had been using session.clear up until now, but as of 0.4.2 I've
found that I get this error for alot of my tests,
object has no attribute '_sa_session_id'
thats a bug. can you please provide a reproducing test case ?
this is actually pretty serious and a new release will be available
the moment we fix this.
Hey Mike,
Below is a minimal test case that always produces the below failure
for me under 0.4.2 but not under 0.4.1,
Cheers
Dave
==
ERROR: test_repr (__main__.uScheduledEvent)
--
Traceback (most recent call last):
File ./test_db_minimal.py, line 19, in tearDown
db.session.clear()
File
/usr/local/lib/python2.5/site-packages/SQLAlchemy-0.4.2-py2.5.egg/sqlalchemy/orm/scoping.py,
line 74, in do
return getattr(self.registry(), name)(*args, **kwargs)
File
/usr/local/lib/python2.5/site-packages/SQLAlchemy-0.4.2-py2.5.egg/sqlalchemy/orm/session.py,
line 577, in clear
self._unattach(instance)
File
/usr/local/lib/python2.5/site-packages/SQLAlchemy-0.4.2-py2.5.egg/sqlalchemy/orm/session.py,
line 1039, in _unattach
if instance._sa_session_id == self.hash_key:
AttributeError: 'MonthlyEvent' object has no attribute '_sa_session_id'
--
dbapi_minimal.py --
import datetime, uuid
from sqlalchemy import *
from sqlalchemy.orm import *
session = scoped_session(sessionmaker(autoflush=False,
transactional=True))
mapper = session.mapper
metadata = MetaData()
## Static variables and mappings
EventType_Daily = 0
EventType_Weekly = 1
EventType_Monthly = 2
EventType_Yearly = 3
eventTable = Table(
'event',
metadata,
Column('id', Integer, primary_key=True),
Column('name', String(100), nullable=False, unique=True),
Column('type', Integer, nullable=False),
Column('action', BLOB), # Pickled arguments for running an
action
Column('step', Integer, nullable=False, default=1),
Column('fromDate', DateTime), # None indicates now
Column('untilDate', DateTime), # None indicates forever
Column('monthly_dayofweek', Boolean),
)
scheduledEventTable = Table(
'scheduledevent',
metadata,
Column('id', Integer, primary_key=True),
Column('event_id', Integer, ForeignKey('event.id')),
Column('runtime', DateTime),
)
class Event(object):
def __init__(self, name, action, step=1, fromDate=None,
untilDate=None):
action: Pickled ActionArgument object for running the
event
fromDate : First time to run this event
untilDate : Time at which this event is no longer valid
self.name = name
self.action = action
self.step = step
self.fromDate = fromDate
self.untilDate = untilDate
def next(self, last):
last: most recent timestamp when this event was executed
raise NotImplementedError(Events must provide a next())
def __repr__(self):
return Event type:%s id:%s % (self.typeName, self.id)
class MonthlyEvent(Event):
MonthlyEvent has an additional init arg of dayofweek.
E.G if (dayofweek == True) then do every first Monday
E.G if (dayofweek == False) then do every nth day of the month
[beware choosing a day 28 lest some months skip it]
def __init__(self, name, action, step=1, fromDate=None,
untilDate=None, dayofweek=True):
Event.__init__(self, name, action, step, fromDate, untilDate)
self.dayofweek = dayofweek
def next(self, last):
monthQuot,monthRem = divmod((last.month + self.step), 12)
# Div cycles to 0, so we need to handle this differently
# 0 indicates December in the current year
if monthRem == 0:
monthRem = 12
monthQuot = 0
incMonth = monthRem
incYear = last.year + monthQuot
newTS = datetime.datetime(
year = incYear,
month = incMonth,
day = last.day,
hour = self.fromDate.hour,
minute = self.fromDate.minute,
)
if newTS self.untilDate:
return None
return newTS
class ScheduledEvent(object):
This is an event that is waiting for its runtime to arrive
def __init__(self, event, runtime):
self.event = event
