[sqlalchemy] Deprecation of the objects argument to session.flush()
Hi, all! I use just one session in my project. Objects (loaded from the database) can changed by user while the program in progress. But only some of them should be saved. Usually I use session.flush (selected_objects), but now this opportunity is deprecated :( Please help me to find a solution of my problem. Thanks in advance. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: index in SA
i want to add a composite index to the class inherited from
declarative_base
I tried this,
class MyClass:
__tablename__ = 'my_table'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
type = Column(String, nullable=False)
__table_args__ = (
Index('ix_name_type','name','type',unique=True)
)
gave me an error,
File /m2svn/trunk/src/model/MyClass.py, line 32, in MyClass
__table_args__ = (
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.5-py2.4.egg/
sqlalchemy/schema.py, line 1461, in __init__
self._init_items(*columns)
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.5-py2.4.egg/
sqlalchemy/schema.py, line 1465, in _init_items
self.append_column(_to_schema_column(column))
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.5-py2.4.egg/
sqlalchemy/schema.py, line 2145, in _to_schema_column
raise exc.ArgumentError(schema.Column object expected)
sqlalchemy.exc.ArgumentError: schema.Column object expected
On Aug 21, 3:23 am, Michael Bayer mike...@zzzcomputing.com wrote:
this is usually accomplished using Index(). see the metadata docs
for details.
On Aug 20, 2009, at 12:35 PM, rajasekhar911 wrote:
Hi
Is it possible to add index to my table using sqlalchemy?
Or should i do it directly on the database?
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: new questions
On Aug 19, 2:27 am, King Simon-NFHD78 simon.k...@motorola.com wrote: -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of darkblueB Sent: 19 August 2009 02:58 To: sqlalchemy Subject: [sqlalchemy] Re: new questions Hi Simon thanks for the reply.. Your second part is straightforward.. The first one, not so much.. I have gone back to a simple meta.bind = //my engine here meta.reflect() I can see meta.tables, and meta.tables_sorted() but how do I know what mapped objects exist? (there should be about 12 tables, with a few one to many and one many to many defined) I feel like time is passing by, I would like to use ORM but this is new to me thanks much -Brian Ah - I see what you mean now. meta.reflect() will only create Table objects (part of the SQL expression language layer). It doesn't automatically create mapped classes for you. If you want to use the declarative system, you would need to create at least a basic class for each of your reflected tables. For example (untested): import sqlalchemy as sa from sqlalchemy.ext.declarative import declarative_base meta = sa.Metadata(bind='sqlite:///your.db') meta.reflect() Base = declarative_base(metadata=meta) class SomeObject(Base): __table__ = meta.tables['some_table'] class SomeOtherObject(Base): __table__ = meta.tables['some_other_table'] I suppose you could automate this by iterating over meta.tables and creating the classes in a loop. You would end up with something similar to SqlSoup (http://www.sqlalchemy.org/trac/wiki/SqlSoup). I suppose it should be possible to automatically create ORM-level relations by inspecting your foreign keys as well if you really wanted. ok, I have done this I have an object def and a __table__ for all of the main actors in my setup I defined an __init__() for one of them I use the declarative base when the objects get created, they seem to have a __mapper__ in them already (the primary mapper) but when I do simple queries that would draw upon a foreign key constraint present in the DB I dont get any lookups for example, (attribute? - I dont know all the terms here) //meta is setup already, sqlite // read in an existing DB cBase = declarative_base(metadata=meta) class saProject(cBase): __table__ = meta.tables['bt_projects'] def __init__(self, inName, inFacilName, inImpModifier=, inProbModifier=, inIsInviteOnly=0, inAllowWt=0, inAllowEvtSummary=1, inAllowAssSum=1, inShowEvtRateAuth=1, inShowEvtRateCom=1 ): self.name = inName self.facilitator = inFacilName self.impact_mod = inImpModifier self.prob_mod = inProbModifier . so now, I read one project in to tProj tProj.name = 'FBLS' but tProj.impact_mod = Decimal(0) *not* a lookup with the foreign key here is the relevant SQL CREATE DB CREATE TABLE 'bt_projects' ( name TEXT UNIQUE, facilitator TEXT, active NUMERIC DEFAULT 1, impact_mod NUMERIC DEFAULT 0, prob_mod NUMERIC DEFAULT 0, key INTEGER NOT NULL PRIMARY KEY, FOREIGN KEY (impact_mod) REFERENCES bt_evt_prob_modifiers(key), FOREIGN KEY (prob_mod) REFERENCES bt_evt_impact_modifiers(key) ); so, I have to make an explicit mapper, too? perhaps I am missing something -Brian --~--~-~--~~~---~--~~ 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] ORM query with overlaps operator
Has anyone generated ORM queries using the OVERLAPS SQL operator that
reference columns in the tables in the query? I've been experimenting
with various approaches and can't seem to cleanly get the column names
(with their appropriate alias based on the rest of the query) into the
overlaps clause.
I'm basically issuing an ORM query and want to check that the date
range given by two columns in one of the objects being queried is
overlaps with a computed date range. In some cases the object whose
columns I am checking is the primary target of the query whereas in
others it's a joined class.
I found an older post from March where Michael suggested the form
somexpression.op('OVERLAPS', someotherexpression)
but I can't figure out how to apply that, and in particular what sort
of expression will produce a tuple at the SQL layer, yet still support
the op method? Namely the output needs to be of the form:
(start, stop) OVERLAPS (start, stop)
So I figured I'd try straight text, and was attempting something like:
query(MappedClass).
filter('(:c_start, :c_end) overlaps (:start, :end)').
params(c_start=MappedClass.start_col, c_end=MappedClass.end_col,
start=datetimevalue, end=datetimevalue)
but I'm having trouble identifying an appropriate value for the
c_start/c_end params to generate the column names in the resulting
SQL. The above gives can't adapt the InstrumentedAttribute references
in the params.
In the meantime I can fall back to a pure textual filter which will
have to assume how the mapped class will be aliased, but that feels
fragile and it'd be nice if I could let SQLAlchemy generate the column
names somehow. I get the feeling though that OVERLAPS is a bit
unusual in terms of the necessary support since it has non-scalar left
and right values for the operator.
Thanks for any help.
-- David
--~--~-~--~~~---~--~~
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: table creation oddity
Faheem Mitha wrote:
Hi,
The following script is then followed by its output, and
finally by the
table output.
I don't get what is going on here. Yes, I should commit the
session, and
the table is empty as expected, but why does the id keep
incrementing on
successive runs, and where is this table living, if not in
the db? I'd
expect to see the id stay at 1. Also, I'd expect to see something in
session.dirty. Deleting the table resets the counter back to 1, so it
looks like it is using the table in some way, but as already
stated, the
table shows as empty via a select * command.
If anyone can clarify what is going on here and satisfy my
curiosity, I'd
appreciate it. Please CC me on any reply. Thanks.
Regards, Faheem.
***
oddity.py
***
from sqlalchemy import *
from sqlalchemy.orm import mapper, relation, sessionmaker
def add_obj(session, obj):
Check if object primary key exists in db. If so,exit, else
add.
from sqlalchemy import and_
from sqlalchemy.orm import object_mapper
mapper = object_mapper(obj)
pid = mapper.primary_key_from_instance(obj)
criterion = and_(*(col == val for col, val in
zip(mapper.primary_key,
mapper.primary_key_from_instance(obj
if session.query(obj.__class__).filter(criterion).count() 0:
print %s object with id %s is already in
db.%(type(obj).__name__, pid)
exit
else:
session.add(obj)
metadata = MetaData()
mytest_table = Table(
'mytest', metadata,
Column('id', Integer, primary_key=True),
)
class MyTest(object):
def __init__(self):
pass
mapper(MyTest, mytest_table)
dbstring = postgres://username:pas...@localhost:5432/oddity
db = create_engine(dbstring)
metadata.bind = db
metadata.create_all()
conn = db.connect()
Session = sessionmaker()
session = Session()
t1 = MyTest()
add_obj(session, t1)
print session.query(MyTest).count()
stmt = mytest_table.select()
for row in stmt.execute():
print row
stmt = select([mytest_table.c.id])
print anno statement is %s\n%stmt
for row in stmt.execute():
print row
print session.dirty is %s%session.dirty
#session.commit()
#session.flush()
#conn.close()
*
script output
*
$ python oddity.py
1
(1,)
anno statement is SELECT mytest.id
FROM mytest
(1,)
session.dirty is IdentitySet([])
$ python oddity.py
1
(2,)
anno statement is SELECT mytest.id
FROM mytest
(2,)
session.dirty is IdentitySet([])
table output
oddity=# select * from mytest;
id
(0 rows)
I've never used postgres, but I believe auto-incrementing counters are
implemented using database sequences. I think these are incremented
outside of a transaction - this ensures that two seperate database
connections using the sequence at the same time will get distinct
values. So although you aren't commiting your transaction, the sequence
still advances.
I guess the sequence must be associated with the table, so when you drop
the table it destroys the sequence as well (I don't know if this is SA
behaviour or PG behaviour).
session.dirty only contains objects that have been loaded from the DB
and subsequently modified. You don't ever actually modify your object,
so it shouldn't appear in session.dirty. (It should appear in
session.new though)
Hope that helps,
Simon
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: index in SA
hi thanks for the reply..
but one doubt
how will i access the MyClass inside MyClass
i tried
ndex('ix_name_type',
MyClass.__table__.c.name,MyClass.__table__.c.type, unique=True)
it is giving the error
NameError: name 'MyClass' is not defined
thanks
On Aug 21, 2:04 pm, King Simon-NFHD78 simon.k...@motorola.com
wrote:
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of rajasekhar911
Sent: 21 August 2009 07:30
To: sqlalchemy
Subject: [sqlalchemy] Re: index in SA
i want to add a composite index to the class inherited from
declarative_base
I tried this,
class MyClass:
__tablename__ = 'my_table'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
type = Column(String, nullable=False)
__table_args__ = (
Index('ix_name_type','name','type',unique=True)
)
gave me an error,
File /m2svn/trunk/src/model/MyClass.py, line 32, in MyClass
__table_args__ = (
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.5-py2.4.egg/
sqlalchemy/schema.py, line 1461, in __init__
self._init_items(*columns)
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.5-py2.4.egg/
sqlalchemy/schema.py, line 1465, in _init_items
self.append_column(_to_schema_column(column))
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.5-py2.4.egg/
sqlalchemy/schema.py, line 2145, in _to_schema_column
raise exc.ArgumentError(schema.Column object expected)
sqlalchemy.exc.ArgumentError: schema.Column object expected
I'm not sure if this is the root cause of your error, but __table_args__
must either be a dictionary or a tuple where the last element is a
dictionary (according
tohttp://www.sqlalchemy.org/docs/05/reference/ext/declarative.html#table-c
onfiguration)
Also, I think Index may require actual column parameters rather than
strings (according
tohttp://www.sqlalchemy.org/docs/05/metadata.html#indexes). You may be
able to use something like the following after your class definition:
Index('ix_name_type', MyClass.__table__.c.name,
MyClass.__table__.c.type, unique=True)
or even
Index('ix_name_type', MyClass.name, MyClass.type, unique=True)
Hope that helps,
Simon
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: index in SA
i tried
class MyClass:
__tablename__ = 'my_table'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
type = Column(String, nullable=False)
__table_args__ = (
Index('ix_name_type', name , type ,unique=True)
)
it errors out
__table_args__ = (
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.5-py2.4.egg/
sqlalchemy/schema.py, line 1461, in __init__
self._init_items(*columns)
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.5-py2.4.egg/
sqlalchemy/schema.py, line 1465, in _init_items
self.append_column(_to_schema_column(column))
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.5-py2.4.egg/
sqlalchemy/schema.py, line 1476, in append_column
self._set_parent(column.table)
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.5-py2.4.egg/
sqlalchemy/schema.py, line 1469, in _set_parent
self.metadata = table.metadata
AttributeError: 'NoneType' object has no attribute 'metadata'
thanks
On Aug 21, 2:22 pm, rajasekhar911 rajasekhar...@gmail.com wrote:
hi thanks for the reply..
but one doubt
how will i access the MyClass inside MyClass
i tried
ndex('ix_name_type',
MyClass.__table__.c.name,MyClass.__table__.c.type, unique=True)
it is giving the error
NameError: name 'MyClass' is not defined
thanks
On Aug 21, 2:04 pm, King Simon-NFHD78 simon.k...@motorola.com
wrote:
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of rajasekhar911
Sent: 21 August 2009 07:30
To: sqlalchemy
Subject: [sqlalchemy] Re: index in SA
i want to add a composite index to the class inherited from
declarative_base
I tried this,
class MyClass:
__tablename__ = 'my_table'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
type = Column(String, nullable=False)
__table_args__ = (
Index('ix_name_type','name','type',unique=True)
)
gave me an error,
File /m2svn/trunk/src/model/MyClass.py, line 32, in MyClass
__table_args__ = (
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.5-py2.4.egg/
sqlalchemy/schema.py, line 1461, in __init__
self._init_items(*columns)
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.5-py2.4.egg/
sqlalchemy/schema.py, line 1465, in _init_items
self.append_column(_to_schema_column(column))
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.5-py2.4.egg/
sqlalchemy/schema.py, line 2145, in _to_schema_column
raise exc.ArgumentError(schema.Column object expected)
sqlalchemy.exc.ArgumentError: schema.Column object expected
I'm not sure if this is the root cause of your error, but __table_args__
must either be a dictionary or a tuple where the last element is a
dictionary (according
tohttp://www.sqlalchemy.org/docs/05/reference/ext/declarative.html#table-c
onfiguration)
Also, I think Index may require actual column parameters rather than
strings (according
tohttp://www.sqlalchemy.org/docs/05/metadata.html#indexes). You may be
able to use something like the following after your class definition:
Index('ix_name_type', MyClass.__table__.c.name,
MyClass.__table__.c.type, unique=True)
or even
Index('ix_name_type', MyClass.name, MyClass.type, unique=True)
Hope that helps,
Simon
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: index in SA
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of rajasekhar911
Sent: 21 August 2009 10:25
To: sqlalchemy
Subject: [sqlalchemy] Re: index in SA
i tried
class MyClass:
__tablename__ = 'my_table'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
type = Column(String, nullable=False)
__table_args__ = (
Index('ix_name_type', name , type ,unique=True)
)
it errors out
__table_args__ = (
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.5-py2.4.egg/
sqlalchemy/schema.py, line 1461, in __init__
self._init_items(*columns)
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.5-py2.4.egg/
sqlalchemy/schema.py, line 1465, in _init_items
self.append_column(_to_schema_column(column))
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.5-py2.4.egg/
sqlalchemy/schema.py, line 1476, in append_column
self._set_parent(column.table)
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.5-py2.4.egg/
sqlalchemy/schema.py, line 1469, in _set_parent
self.metadata = table.metadata
AttributeError: 'NoneType' object has no attribute 'metadata'
thanks
The problem is that at the time you are calling Index, the table
object doesn't exist. Apparently the Index object doesn't work with
declarative in this way.
However, if you just move your Index definition outside the class
definition completely, I think it should be fine.
ie.
class MyClass(Base):
__tablename__ = 'my_table'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
type = Column(String, nullable=False)
Index('ix_name_type', MyClass.name, MyClass.type, unique=True)
Simon
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: index in SA
exactly
On Aug 21, 2:33 pm, King Simon-NFHD78 simon.k...@motorola.com
wrote:
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of rajasekhar911
Sent: 21 August 2009 10:25
To: sqlalchemy
Subject: [sqlalchemy] Re: index in SA
i tried
class MyClass:
__tablename__ = 'my_table'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
type = Column(String, nullable=False)
__table_args__ = (
Index('ix_name_type', name , type ,unique=True)
)
it errors out
__table_args__ = (
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.5-py2.4.egg/
sqlalchemy/schema.py, line 1461, in __init__
self._init_items(*columns)
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.5-py2.4.egg/
sqlalchemy/schema.py, line 1465, in _init_items
self.append_column(_to_schema_column(column))
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.5-py2.4.egg/
sqlalchemy/schema.py, line 1476, in append_column
self._set_parent(column.table)
File /usr/lib/python2.4/site-packages/SQLAlchemy-0.5.5-py2.4.egg/
sqlalchemy/schema.py, line 1469, in _set_parent
self.metadata = table.metadata
AttributeError: 'NoneType' object has no attribute 'metadata'
thanks
The problem is that at the time you are calling Index, the table
object doesn't exist. Apparently the Index object doesn't work with
declarative in this way.
However, if you just move your Index definition outside the class
definition completely, I think it should be fine.
ie.
class MyClass(Base):
__tablename__ = 'my_table'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
type = Column(String, nullable=False)
Index('ix_name_type', MyClass.name, MyClass.type, unique=True)
Simon
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: ORM query with overlaps operator
This is something that could be improved in SQLAlchemy, but as a
workaround you can use the compiler extension to create the support
yourself. Here's some example code. It uses some private internals
from SQLAlchemy so you need to keep an eye on it that it doesn't break
when changing versions.
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql import ClauseElement
from sqlalchemy.sql.expression import _literal_as_binds, _CompareMixin
from sqlalchemy.types import NullType
class TupleClause(ClauseElement, _CompareMixin):
def __init__(self, *columns):
self.columns = [_literal_as_binds(col) for col in columns]
self.type = NullType()
@compiles(TupleClause)
def compile_tupleclause(element, compiler, **kw):
return (%s) % , .join(compiler.process(col) for col in
element.columns)
# Usage:
def overlaps(a_pair, b_pair):
return TupleClause(*a_pair).op('OVERLAPS')(TupleClause(*b_pair))
query.filter(overlaps((MappedClass.start_col, MappedClass.end_col),
(start_time, end_time)))
On Aug 21, 10:50 am, David Bolen db3l@gmail.com wrote:
Has anyone generated ORM queries using the OVERLAPS SQL operator that
reference columns in the tables in the query? I've been experimenting
with various approaches and can't seem to cleanly get the column names
(with their appropriate alias based on the rest of the query) into the
overlaps clause.
I'm basically issuing an ORM query and want to check that the date
range given by two columns in one of the objects being queried is
overlaps with a computed date range. In some cases the object whose
columns I am checking is the primary target of the query whereas in
others it's a joined class.
I found an older post from March where Michael suggested the form
somexpression.op('OVERLAPS', someotherexpression)
but I can't figure out how to apply that, and in particular what sort
of expression will produce a tuple at the SQL layer, yet still support
the op method? Namely the output needs to be of the form:
(start, stop) OVERLAPS (start, stop)
So I figured I'd try straight text, and was attempting something like:
query(MappedClass).
filter('(:c_start, :c_end) overlaps (:start, :end)').
params(c_start=MappedClass.start_col, c_end=MappedClass.end_col,
start=datetimevalue, end=datetimevalue)
but I'm having trouble identifying an appropriate value for the
c_start/c_end params to generate the column names in the resulting
SQL. The above gives can't adapt the InstrumentedAttribute references
in the params.
In the meantime I can fall back to a pure textual filter which will
have to assume how the mapped class will be aliased, but that feels
fragile and it'd be nice if I could let SQLAlchemy generate the column
names somehow. I get the feeling though that OVERLAPS is a bit
unusual in terms of the necessary support since it has non-scalar left
and right values for the operator.
Thanks for any help.
-- David
--~--~-~--~~~---~--~~
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: table creation oddity
On Fri, 21 Aug 2009, King Simon-NFHD78 wrote: I've never used postgres, but I believe auto-incrementing counters are implemented using database sequences. I think these are incremented outside of a transaction - this ensures that two seperate database connections using the sequence at the same time will get distinct values. So although you aren't commiting your transaction, the sequence still advances. I guess the sequence must be associated with the table, so when you drop the table it destroys the sequence as well (I don't know if this is SA behaviour or PG behaviour). session.dirty only contains objects that have been loaded from the DB and subsequently modified. You don't ever actually modify your object, so it shouldn't appear in session.dirty. (It should appear in session.new though) Hope that helps, Simon Hi Simon, Thanks for the fast and helpful response. This looks like an artifact of how I am creating the table. I wonder if this would still show up if I explicitly specified the id. I could check this. Also, presumably if I had other cols in the table, they wouldn't show up in sqla's printout. There is a discussion of this in http://neilconway.org/docs/sequences/ Quote: A sequence is a special kind of database object designed for generating unique numeric identifiers. It is typically used to generate artificial primary keys. I'm still puzzled why sqlalchemy lists the entries in the table, when querying directly via psql doesn't show it. Regards, Faheem. --~--~-~--~~~---~--~~ 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: table creation oddity
Faheem Mitha wrote:
Thanks for the fast and helpful response. This looks like an artifact
of
how I am creating the table. I wonder if this would still show up if I
explicitly specified the id. I could check this. Also, presumably if I
had
other cols in the table, they wouldn't show up in sqla's printout.
The sequence isn't directly related to the table (as far as I'm aware).
Instead, when you insert a row into the table, you do something like
SELECT NEXTVAL('sequencename') to get the next ID value. SQLALchemy
does this automatically for you.
I'm still puzzled why sqlalchemy lists the entries in the table, when
querying directly via psql doesn't show it.
Your SQLAlchemy operations are happening within a transaction that never
gets committed. If you turned on SQL echoing (use echo=True or
echo='debug' in your call to create_engine), you would see that your
object is actually being inserted into the 'mytest' table. When you
SELECT from the table, you are still in the same transaction, so you see
the rows that you have inserted.
However, when the script exits, the transaction gets rolled back, so you
never see the new rows in psql. The only evidence that anything ever
happened is that the sequence has moved on (see the note at the bottom
of http://www.postgresql.org/docs/8.1/static/functions-sequence.html)
Simon
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: interpret (string) NULLs as empty strings, not None
On Thu, Aug 6, 2009 at 3:14 PM, Michael Bayermike...@zzzcomputing.com wrote:
When populating objects through the ORM, I'd like to interpret all
NULL values fetched from VARCHAR2 / NVARCHAR2 columns in the database
as empty strings ('') instead of `None`s.
use a TypeDecorator that creates that behavior.
Belated but huge thanks for this. This is a much simpler solution
than I had hoped for.
class NullCatchingString(types.TypeDecorator):
# NULL results are returned as empty strings.
impl = types.String
def process_result_value(self, value, engine):
if value:
return value
else:
return ''
To get a global effect,
perhaps in your imports make sure that your custom type overrides the
usage of String/Unicode in your Table definitions.
In this case, my tables are autoload=True, so I don't think overriding
String will help. However, I realized that I've got a limited number
of columns that need this behavior, and explicitly defining those
columns isn't too much of a pain after all.
tbl = Table('mytable', metadata,
Column('suchandsuch', NullCatchingString),
autoload=True, autoload_with=engine)
Thanks again!
--
- Catherine
http://catherinedevlin.blogspot.com/
*** PyOhio * July 25-26, 2009 * pyohio.org ***
--~--~-~--~~~---~--~~
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: new questions
On Aug 21, 2:17 am, King Simon-NFHD78 simon.k...@motorola.com wrote: darkblueB wrote: ok, I have done this I have an object def and a __table__ for all of the main actors in my setup I defined an __init__() for one of them I use the declarative base when the objects get created, they seem to have a __mapper__ in them already (the primary mapper) but when I do simple queries that would draw upon a foreign key constraint present in the DB I dont get any lookups for example, (attribute? - I dont know all the terms here) //meta is setup already, sqlite // read in an existing DB cBase = declarative_base(metadata=meta) class saProject(cBase): __table__ = meta.tables['bt_projects'] def __init__(self, inName, inFacilName, inImpModifier=, inProbModifier=, inIsInviteOnly=0, inAllowWt=0, inAllowEvtSummary=1, inAllowAssSum=1, inShowEvtRateAuth=1, inShowEvtRateCom=1 ): self.name = inName self.facilitator = inFacilName self.impact_mod = inImpModifier self.prob_mod = inProbModifier . so now, I read one project in to tProj tProj.name = 'FBLS' but tProj.impact_mod = Decimal(0) *not* a lookup with the foreign key here is the relevant SQL CREATE DB CREATE TABLE 'bt_projects' ( name TEXT UNIQUE, facilitator TEXT, active NUMERIC DEFAULT 1, impact_mod NUMERIC DEFAULT 0, prob_mod NUMERIC DEFAULT 0, key INTEGER NOT NULL PRIMARY KEY, FOREIGN KEY (impact_mod) REFERENCES bt_evt_prob_modifiers(key), FOREIGN KEY (prob_mod) REFERENCES bt_evt_impact_modifiers(key) ); so, I have to make an explicit mapper, too? perhaps I am missing something -Brian The lookup with foreign key that you are asking for is what SQLALchemy calls a relation (Seehttp://www.sqlalchemy.org/docs/05/ormtutorial.html#building-a-relation). You have to create them explicitly, and they can't have the same name as the foreign key itself. When the foreign keys are simple, SA can normally figure out the join conditions by itself. For example, to get the behaviour your were expected, your class definitions should look something like this: import sqlalchemy.orm as saorm class saEvtProbModifiers(cBase): __table__ = meta.tables['bt_evt_prob_modifiers'] class saEvtImpactModifiers(cBase): __table__ = meta.tables['bt_evt_impact_modifiers'] class saProject(cBase): __table__ = meta.tables['bt_projects'] prob_modifier = saorm.relation(saEvtProbModifiers) impact_modifier = saorm.relation(saEvtImpactModifiers) Now when you access tProj.impact_modifier, you should get an instance of the saEvtImpactModifiers class. The relation function gies you lots of other options. You can configure a 'back reference' (so you could automatically add a 'projects' property to saEvtImpactModifiers and saEvtProbModifiers for example). You can also explicitly define the join conditions if SA is unable to work them out. It's worth working through the ORM tutorial (http://www.sqlalchemy.org/docs/05/ormtutorial.html) which covers all of this. ok, some of that sounds familiar naturally, I built this database app I am trying to replace! but I thought somehow that meta.reflect() would pick up on the fk constraints ... ok - I will press on here Hope that helps, thank you I appreciate your response Simon --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: table creation oddity
Hi Simon,
On Fri, 21 Aug 2009, King Simon-NFHD78 wrote:
Faheem Mitha wrote:
Thanks for the fast and helpful response. This looks like an artifact
of how I am creating the table. I wonder if this would still show up if
I explicitly specified the id. I could check this. Also, presumably if
I had other cols in the table, they wouldn't show up in sqla's
printout.
The sequence isn't directly related to the table (as far as I'm aware).
Instead, when you insert a row into the table, you do something like
SELECT NEXTVAL('sequencename') to get the next ID value. SQLALchemy
does this automatically for you.
Ah, so that is something I would have to do manually if working with pg
more directly? Nice of sqla to do it for me.
I'm still puzzled why sqlalchemy lists the entries in the table, when
querying directly via psql doesn't show it.
Your SQLAlchemy operations are happening within a transaction that never
gets committed. If you turned on SQL echoing (use echo=True or
echo='debug' in your call to create_engine), you would see that your
object is actually being inserted into the 'mytest' table. When you
SELECT from the table, you are still in the same transaction, so you see
the rows that you have inserted.
However, when the script exits, the transaction gets rolled back, so you
never see the new rows in psql. The only evidence that anything ever
happened is that the sequence has moved on (see the note at the bottom
of http://www.postgresql.org/docs/8.1/static/functions-sequence.html)
Thanks, that's a very clear and educational explanation. So mytest
contains this sequence, but is not written to disk.
Regards, Faheem.
--~--~-~--~~~---~--~~
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: new questions
but I thought somehow that meta.reflect() would pick up on the fk constraints ... Class MetaData is part of the core api that ORM builds on top of, and meta.reflect() does pick up the fk constraints from the database. Think of it this way, MetaData, whether reflected or declared in its entirety, is holding a database level description of your tables and the fk relationships, what you need to do is declare to SA's ORM how you will use the relationships; e.g., are they eager loading, are they 1-1, 1-n or m-n, are they 2-way, give a name to the relation to use in your Python code, etc. There are a lot of defaults, but you at least have to give relationships a name so that they can be used in you program. --~--~-~--~~~---~--~~ 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] Database with audit trail table
Heyho! Instead of creating changeby / changed fields on all my tables, I'm planning to write some model classes where changes would be recorded in a separate audit trail table (the obvious benefit beyond not requiring the additional fields is that I can preserve the history as far back as I want) So, like a table audit ( timestamp, dbchange, info, ...) where dbchange would be some kind of machine interpretable description and info would be a textual description of the event (created user blah, removed product x from order y, ...) Where do I start to get this automated? Session has the information about what needs to be done (insert, delete, update), and the model classes know what should be recorded into the audit records. So where do I hook into so that I automatically can add these audit records at flush time? (this would need to be after flush, actually, since autogenerated values need to be available. But as long as I get into the same transaction I'm just fine.) Perhaps this has even been done before? (Obviously, this kind of audit trail would be lost upon rollback. For a first variant this is fine with me; later I' guess an option to use a separate session for the audit stuff would be nice, but that'd need to log which audit records were part of a transaction that was later rolled back...) Ideas comments? cheers -- vbi -- Bill Dickey is learning me his experience. -- Yogi Berra in his rookie season. signature.asc Description: This is a digitally signed message part.
[sqlalchemy] Re: Database with audit trail table
You might want to start here http://www.sqlalchemy.org/trac/wiki/UsageRecipes/LogVersions --~--~-~--~~~---~--~~ 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: ORM query with overlaps operator
Ants Aasma ants.aa...@gmail.com writes: This is something that could be improved in SQLAlchemy, but as a workaround you can use the compiler extension to create the support yourself. Here's some example code. It uses some private internals from SQLAlchemy so you need to keep an eye on it that it doesn't break when changing versions. Ah - thanks! I had fiddled briefly trying to get away with just some manual use of ClauseElement directly, but obviously that didn't work with the tuple of columns. I'm not that familiar with the SQLAlchemy internals, so having a concrete TupleClause sample is very helpful. -- David --~--~-~--~~~---~--~~ 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: Database with audit trail table
Adrian von Bidder avbid...@fortytwo.ch writes: Ideas comments? For what it's worth, I'd think that the best sort of audit would be something done in the database itself, since it would audit any changes whether done through any interface. It depends on the database involved, but for example, in PostgreSQL you could establish audit rules on the relevant tables that copied old row values into a mirror audit table whenever they changed. You can put the audit tables off in a different schema (which also lets you keep the same table names) to avoid them being visible by default to normal users/applications in the database. -- David --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
