[sqlalchemy] Late and ugly error when mixing timezone-savvy and timezone-naive datetimes
Hi,
I get away with stuffing datetime.datetime.now() into a DateTime
(timezone=True) column, despite the former being timezone-naive
(.utcoffset() is None, .tzinfo is None, etc.).
It is stored in the table with UTC offset +00, which is arguably
incorrect (states information that was not present in the input).
But even if you call it correct, you get in trouble when you read the
value back as an attribute of a mapped class in a session, set the
attribute again to datetime.datetime.now() (again timezone-naive), and
then try to query the session for the same object again. This retches
up a TypeError: “can't compare offset-naive and offset-aware
datetimes”.
Code to reproduce:
from sqlalchemy import Table, MetaData, Column, Integer, DateTime,
create_engine
from sqlalchemy.orm import sessionmaker, mapper
from datetime import datetime, timedelta
from pytz import utc
t = Table('foo', MetaData(), Column('id', Integer, primary_key=True,),
Column('dt', DateTime(timezone=True)))
class T(object):
pass
mapper(T, t)
e = create_engine('postgres://localhost/satest')
t.create(bind=e, checkfirst=True)
e.execute(t.delete()) # in case we are re-running this test
Session = sessionmaker(bind=e)
inst = T()
inst.dt = datetime.now()
assert inst.dt.utcoffset() is None
session = Session()
session.add(inst)
session.commit()
session.close()
session = Session()
inst = session.query(T).first()
assert inst.dt.utcoffset() == timedelta(0)
inst.dt = datetime.now()
assert inst.dt.utcoffset() is None
# next line raises TypeError: can't compare offset-naive and offset-
aware datetimes
inst = session.query(T).first()
SQLAlchemy should either reject the timezone-naive datetime value
right away when it is bound to a timezone-savvy DateTime column, or
tolerate me setting a timezone-naive datetime value again. I root for
the former.
Regards,
- Gulli
--~--~-~--~~~---~--~~
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: Using order_by in an association many-to-many relationship with columns from the association object
On Mar 11, 2009, at 6:17 PM, Scott wrote: > > When I do this... > > cpt_codes = ManyToMany( > ... > order_by = procedure_cpt_codes.c.cpt_codes_idx > ) > > # procedure_cpt_codes is the JOIN table in between the parent > (procedure) <---< children (cpt_codes) > # procedure_cpt_codes has 3 columns...procedure_id (foreign key), > cpt_code_id (foreign key) and cpt_codes_idx that's sorted > > ...I get the following error: > > TypeError: 'Column' object is not iterable this would suggest the order_by argument on Elixir's ManyToMany function is expecting a list. Try asking on the Elixir mailing list about this issue since this is not a SQLAlchemy issue. --~--~-~--~~~---~--~~ 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: Help! More Class->Mapper confusion!
On Mar 11, 2009, at 6:05 PM, batraone wrote: > > Thanks. I don't have control over the instance creation. These pre- > created objects are handed down to me. I could create an adapter that > maps the original class to the one that has been mapped but this is > quite a bit of work as I have to manually copy over each of the source > class attributes to my mapped class. I could be clever and perhaps > look for common attribute names via the internal dictionary but this > too seems clunky. > > I would think this is a common problem (augmenting a class to dump > it's contents to a db but keep the original class untouched (sort of > like shelve - but w/o the > restore capability)). Is there a better way to handle this? Unfortunately the usage of an object relational mapper assumes that some degree of convention can be applied to the classes being mapped. For example, you can't map "old style" classes nor can you map most natively-backed classes (like cElementTree structures), instrumentation will fail if the class relies upon direct __dict__ access, and there's probably many other examples of limitations here. Applying instrumentation to a class to which you don't have control over is generally a bad idea no matter what persistence approach you're using, as even if you do get it working, changes to the implementation of those objects will break your application. So in this case you definitely should be marshalling these external objects immediately into an object structure which you have control over.You also should most certainly be using standard Python idioms like dir(object), getattr() and possibly __dict__ access in order to marshall the state of the incoming objects into your own model using a reflective approach rather than hardcoding every attribute. --~--~-~--~~~---~--~~ 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: SQLAlchemy based syndication feed framework?
-- Forwarded message -- From: Walter Cruz Date: Thu, Mar 12, 2009 at 12:19 PM Subject: [sqlalchemy] Re: SQLAlchemy based syndication feed framework? To: sqlalchemy@googlegroups.com webhelpers, from Pylons, has a handy rss generator. I use it with SQLAlchemy, works fine. Hmm, this app will talk to a few other Pylons apps at some point, so I will have a look, thanks for the tip! On Wed, Mar 11, 2009 at 8:00 PM, Noah Gift wrote: > > > On Thu, Mar 12, 2009 at 11:25 AM, Noah Gift wrote: > >> Does anyone on this list know of a simple WSGI compatible, SQLAlchemy >> based feed framework, like the one offered with Django: >> http://docs.djangoproject.com/en/dev/ref/contrib/syndication/?from=olddocs >> >> To answer my own question, I found this: > > http://www.dalkescientific.com/Python/PyRSS2Gen.html > > Which looks fairly easy to combine with SQLAlchemy + something like this: > http://pypi.python.org/pypi/urlrelay/0.6 > > Hmm, maybe I will make this into a library. > >> >> >> -- >> Cheers, >> >> Noah >> > > > > -- > Cheers, > > Noah > > > -- []' - Walter waltercruz.com -- Cheers, Noah --~--~-~--~~~---~--~~ 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: SQLAlchemy based syndication feed framework?
webhelpers, from Pylons, has a handy rss generator. I use it with SQLAlchemy, works fine. On Wed, Mar 11, 2009 at 8:00 PM, Noah Gift wrote: > > > On Thu, Mar 12, 2009 at 11:25 AM, Noah Gift wrote: > >> Does anyone on this list know of a simple WSGI compatible, SQLAlchemy >> based feed framework, like the one offered with Django: >> http://docs.djangoproject.com/en/dev/ref/contrib/syndication/?from=olddocs >> >> To answer my own question, I found this: > > http://www.dalkescientific.com/Python/PyRSS2Gen.html > > Which looks fairly easy to combine with SQLAlchemy + something like this: > http://pypi.python.org/pypi/urlrelay/0.6 > > Hmm, maybe I will make this into a library. > >> >> >> -- >> Cheers, >> >> Noah >> > > > > -- > Cheers, > > Noah > > > > -- []' - Walter waltercruz.com --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy based syndication feed framework?
On Thu, Mar 12, 2009 at 11:25 AM, Noah Gift wrote: > Does anyone on this list know of a simple WSGI compatible, SQLAlchemy based > feed framework, like the one offered with Django: > http://docs.djangoproject.com/en/dev/ref/contrib/syndication/?from=olddocs > > To answer my own question, I found this: http://www.dalkescientific.com/Python/PyRSS2Gen.html Which looks fairly easy to combine with SQLAlchemy + something like this: http://pypi.python.org/pypi/urlrelay/0.6 Hmm, maybe I will make this into a library. > > > -- > Cheers, > > Noah > -- Cheers, Noah --~--~-~--~~~---~--~~ 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] SQLAlchemy based syndication feed framework?
Does anyone on this list know of a simple WSGI compatible, SQLAlchemy based feed framework, like the one offered with Django: http://docs.djangoproject.com/en/dev/ref/contrib/syndication/?from=olddocs -- Cheers, Noah --~--~-~--~~~---~--~~ 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: Using order_by in an association many-to-many relationship with columns from the association object
When I do this...
cpt_codes = ManyToMany(
...
order_by = procedure_cpt_codes.c.cpt_codes_idx
)
# procedure_cpt_codes is the JOIN table in between the parent
(procedure) <---< children (cpt_codes)
# procedure_cpt_codes has 3 columns...procedure_id (foreign key),
cpt_code_id (foreign key) and cpt_codes_idx that's sorted
...I get the following error:
TypeError: 'Column' object is not iterable
I had tried passing order_by several configurations including the
column object as you suggested to no avail. It seemed when I looked
through the documentation and source (which was a few days ago now so
my memory may be fuzzy) for ManyToMany order_by was expecting a string
that was the name of a column on the secondary table (CptCode in my
example).
I've since started to try to shoe horn the relationship with an
association object representing the join table which seemed the only
way to access a non-foreign key column on the join table to order the
collection by. If there's a better way to do this with the order_by
parameter I'd love to figure it out b/c association object syntax with
ORM gets REALLY messy.
Thanks, Scott
On Mar 10, 12:03 pm, "Michael Bayer" wrote:
> order_by accepts a Column object, i.e. table.c.whatever, so pass that in.
>
>
>
> Scott wrote:
>
> > Is there a way with the current iteration of SQLAlchemy to add a
> > column to the association table in a many-to-many relationship with
> > that column used to order the join? I looked at the order_by attribute
> > of the ManyToMany() relationship definition, but it seems that this is
> > expecting a string naming the column in the related entity. I'm using
> > Elixir on top of alchemy, but here are my relevant class and table
> > definitions:
>
> > procedure_cpt_codes = Table('procedure_cpt_codes', metadata,
> > autoload=True)
>
> > class CptCode(Entity):
> > using_options(tablename='cpt_codes', autosetup=True)
>
> > name = Field(Unicode)
> > code = Field(Unicode)
> > description= Field(Unicode)
>
> > class Procedure(Entity):
> > using_options(tablename='procedures', autosetup=True)
>
> > complications = OneToMany('Complication')
> > cpt_codes = ManyToMany(
> > 'CptCode',
> > table = procedure_cpt_codes, lazy=False,
> > foreign_keys = lambda: [ procedure_cpt_codes.c.procedure_id,
> > procedure_cpt_codes.c.cpt_code_id ],
> > primaryjoin = lambda: Procedure.id ==
> > procedure_cpt_codes.c.procedure_id,
> > secondaryjoin = lambda: CptCode.id ==
> > procedure_cpt_codes.c.cpt_code_id,
> > order_by = procedure_cpt_codes.c.cpt_codes_idx
> > )
> > procedure_date = Field(Date)
>
> > I get the following exception when run as listed:
>
> > Traceback (most recent call last):
> > File "/System/Library/Frameworks/Python.framework/Versions/Current/
> > Extras/lib/python/PyObjC/PyObjCTools/AppHelper.py", line 235, in
> > runEventLoop
> > main(argv)
> > File "/Users/tswall/Documents/workspace/Cocoa/python/Epdb/build/
> > Debug/Epdb.app/Contents/Resources/MyController.py", line 15, in
> > buttonPushed_
> > for instance in Patient.query.all():
> > File "/Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
> > elixir/entity.py", line 641, in __get__
> > elixir.setup_all()
> > File "/Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
> > elixir/__init__.py", line 145, in setup_all
> > File "/Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
> > elixir/entity.py", line 816, in setup_entities
> > method()
> > File "/Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
> > elixir/entity.py", line 421, in setup_properties
> > self.call_builders('create_properties')
> > File "/Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
> > elixir/entity.py", line 433, in call_builders
> > getattr(builder, what)()
> > File "/Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
> > elixir/relationships.py", line 417, in create_properties
> > self.target._descriptor.translate_order_by(kwargs['order_by'])
> > File "/Library/Python/2.5/site-packages/Elixir-0.6.1-py2.5.egg/
> > elixir/entity.py", line 322, in translate_order_by
> > for colname in order_by:
> > TypeError: 'Column' object is not iterable
>
> > When I change the order_by above to
> > order_by = 'procedure_cpt_codes.c.cpt_codes_idx' #or 'cpt_codes_idx'
> > I get an error that it can't find column 'cpt_codes_idx' on relation
> > table 'CptCode'.
>
> > Any advice would be appreciated!
> > Scott
--~--~-~--~~~---~--~~
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: Help! More Class->Mapper confusion!
Thanks. I don't have control over the instance creation. These pre- created objects are handed down to me. I could create an adapter that maps the original class to the one that has been mapped but this is quite a bit of work as I have to manually copy over each of the source class attributes to my mapped class. I could be clever and perhaps look for common attribute names via the internal dictionary but this too seems clunky. I would think this is a common problem (augmenting a class to dump it's contents to a db but keep the original class untouched (sort of like shelve - but w/o the restore capability)). Is there a better way to handle this? Thanks, Raj On Mar 11, 2:48 pm, "Michael Bayer" wrote: > batraone wrote: > > def map(self, myfoo): > > 'Creates the map. ' > > > orm.mapper(Foo, self.t_foo, column_prefix = '_') > > > # Add foo to the database > > print dir(myfoo) # where did _name go?, _phone is there! > > > mf2 = Foo() # ok, let's create a new one. > > print dir(mf2) # same problem > > > if __name__ == '__main__': > > f = Foo() > > fs = FooStore() > > fs.map(f) > > The column_prefix wasn't being honored when the mapper checked for > existing names, this is fixed in trunk r5839. > > But also, don't create instances of the object before the class is mapped. > In particular, it's bad form to create tables and mappers inside of class > methods. Create class-level constructs like tables and mappers at the > module level, in the same scope in which you create your classes. > > > > > mf2 = Foo.Foo() # ok, let's create a new one. > > # AttributeError: 'Foo' object has no attribute > > '_sa_instance_state' > > myfoo._phone = '555-1212' # > > > if __name__ == '__main__': > > orm.clear_mappers() > > f = Foo.Foo() > > fs = FooStore() > > fs.map(f) > > don't create instances of the object before the class is mapped.- Hide quoted > text - > > - Show quoted text - --~--~-~--~~~---~--~~ 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: Help! More Class->Mapper confusion!
batraone wrote: > def map(self, myfoo): > 'Creates the map. ' > > orm.mapper(Foo, self.t_foo, column_prefix = '_') > > # Add foo to the database > print dir(myfoo) # where did _name go?, _phone is there! > > mf2 = Foo() # ok, let's create a new one. > print dir(mf2) # same problem > > if __name__ == '__main__': > f = Foo() > fs = FooStore() > fs.map(f) The column_prefix wasn't being honored when the mapper checked for existing names, this is fixed in trunk r5839. But also, don't create instances of the object before the class is mapped. In particular, it's bad form to create tables and mappers inside of class methods. Create class-level constructs like tables and mappers at the module level, in the same scope in which you create your classes. > > mf2 = Foo.Foo() # ok, let's create a new one. > # AttributeError: 'Foo' object has no attribute > '_sa_instance_state' > myfoo._phone = '555-1212' # > > if __name__ == '__main__': > orm.clear_mappers() > f = Foo.Foo() > fs = FooStore() > fs.map(f) don't create instances of the object before the class is mapped. --~--~-~--~~~---~--~~ 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] Help! More Class->Mapper confusion!
Hi,
OK - I ran into another thing that I do not understand about
sqlalchemy. I am a newbie, so hopefully this will be
straightforward.
There are two issues here:
ISSUE1:
I create a class called foo w/ a method called name().
I map a table that has a column called 'name' but use
the column_prefix = '_' but I do not see _name get added
to the class! But other labels, such as _phone which do
not have a corresponding method name does get added.
Here's the code:
import sqlalchemy as sa
from sqlalchemy import orm
from sqlite3 import dbapi2 as sqlite
class Foo(object):
def __init__(self):
self._myname = 'Bar'
def name(self):
return(self._myname)
class FooStore:
def __init__(self):
self.metadata = sa.MetaData()
# table to map class to
self.t_foo = sa.Table('table_foo', self.metadata,
sa.Column('id', sa.types.Integer, primary_key=True),
sa.Column('name', sa.types.String(100)),
sa.Column('phone', sa.types.String(100))
)
def map(self, myfoo):
'Creates the map. '
orm.mapper(Foo, self.t_foo, column_prefix = '_')
# Add foo to the database
print dir(myfoo) # where did _name go?, _phone is there!
mf2 = Foo() # ok, let's create a new one.
print dir(mf2) # same problem
if __name__ == '__main__':
f = Foo()
fs = FooStore()
fs.map(f)
ISSUE2: I have an object that will be given to me that I want to
store into a database. The class definition is located in a package.
When I map this class to a table and set the attribute - I get
an exception:
AttributeError: 'NoneType' object has no attribute 'set'
This can be seen by modifying the above example - where I put
Foo into a package called 'foo':
import sqlalchemy as sa
from sqlalchemy import orm
from sqlite3 import dbapi2 as sqlite
import sys
import foo.Foo as Foo
class FooStore:
def __init__(self):
self.metadata = sa.MetaData()
# table to map class to
self.t_foo = sa.Table('table_foo', self.metadata,
sa.Column('id', sa.types.Integer, primary_key=True),
sa.Column('name', sa.types.String(100)),
sa.Column('phone', sa.types.String(100))
)
def map(self, myfoo):
'Creates the map. te is the test engine'
orm.mapper(Foo.Foo, self.t_foo, column_prefix = '_')
# Add foo to the database
try:
myfoo._phone = '555-1212' # exception is thrown!
except:
#AttributeError: 'NoneType' object has no attribute 'set'
print sys.exc_info()
mf2 = Foo.Foo() # ok, let's create a new one.
# AttributeError: 'Foo' object has no attribute
'_sa_instance_state'
myfoo._phone = '555-1212' #
if __name__ == '__main__':
orm.clear_mappers()
f = Foo.Foo()
fs = FooStore()
fs.map(f)
What's the right way to adapt this class to a table?
Thanks!
-Raj
--~--~-~--~~~---~--~~
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] How to map columns into a tuple using SQLAlchemy?
Hi, I'm just starting to use SQLAlchemy and hit a roadblock. I have a class Result which contains a tuple called _limits. _limits = (Upper value, lower value, nominal) I would like to map a the table's columns directly into this tuple. >From the documentation, all I can see is columns mapping directly to python attributes (I.e. Result.upper_value, Result.lower_value,..). Is there a way to map the three columns directly into the tuple? I do not want to modify the Result class and therefore cannot create it as composite column type. I'm hoping there is a syntax that states "map these 3 columns" into this tuple via the mapper. Thanks, Raj --~--~-~--~~~---~--~~ 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] How to map columns into a tuple using SQLAlchemy?
Hi, I'm just starting to use SQLAlchemy and hit a roadblock. I have a class Result which contains a tuple called _limits. _limits = (Upper value, lower value, nominal) I would like to map a the table's columns directly into this tuple. >From the documentation, all I can see is columns mapping directly to python attributes (I.e. Result.upper_value, Result.lower_value,..). Is there a way to map the three columns directly into the tuple? I do not want to modify the Result class and therefore cannot create it as composite column type. I'm hoping there is a syntax that states "map these 3 columns" into this tuple via the mapper. Thanks, Raj --~--~-~--~~~---~--~~ 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] How to set default join onclause if table has more than one FK to another table?
Hi All!
How to set default join condition if table has more than one FK to
another table?
It may be Transaction.user_id==User.id or
Transaction.rollback_user_id==User.id, I want first one to be default,
is it possible to do?
transactions_table = sa.Table('transactions', meta.metadata,
sa.Column('id', sa.types.Integer, primary_key=True),
sa.Column('serial', sa.types.Integer, index=True, nullable=False),
sa.Column('person_id', sa.types.Integer, sa.ForeignKey
('people.id'), index=True, nullable=False),
sa.Column('user_id', sa.types.Integer, sa.ForeignKey(SYSTEM_SCHEMA
+'.users.id'), index=True, nullable=False),
sa.Column('service_id', sa.types.Integer, sa.ForeignKey
('services.id'), index=True, nullable=False),
sa.Column('sum', sa.types.Integer, nullable=False),
sa.Column('commit_date', sa.types.Date, index=True,
nullable=False),
sa.Column('commit_time', sa.types.Time, index=True,
nullable=False),
sa.Column('rollback_date', sa.types.Date, index=True,
nullable=True),
sa.Column('rollback_time', sa.types.Time, index=True,
nullable=True),
sa.Column('rollback_user_id', sa.types.Integer, sa.ForeignKey
(SYSTEM_SCHEMA+'.users.id'), index=True, nullable=True)
)
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: Supporting fully-qualified table names and cross-database references in Sybase and SQL Server?
I wasn't able to get it working so easily using the existing entry-
points so I created a new one, quote_schema, and use it explicitly in
a couple of places in compiler.py. The default implementation is the
same as the old one.
pjjH
+def quote_schema(self, schema, force):
+"""Quote a schema.
+
+Subclasses should override this to provide database-dependent
+quoting behavior.
+"""
+return self.quote(schema, force)
+
def quote_schema(self, schema, force=True):
"""Prepare a quoted table and schema name."""
result = '.'.join([self.quote(x, force) for x in schema.split
('.')])
return result
On Mar 10, 5:30 pm, "phrrn...@googlemail.com"
wrote:
> As it happens, this works on the Sybase dialect without fixing the
> quoting at all! Apparently SQL such as this is happily accepted by
> Sybase:
>
> SELECT [fdcommon.dbo].organization.org_id,
> [fdcommon.dbo].organization.abbrev
> FROM [fdcommon.dbo].organization JOIN [fdcommon.dbo].org_type
> ON [fdcommon.dbo].org_type.org_type_id =
> [fdcommon.dbo].organization.org_type
>
> I resorted to some brute-force list operations rather than regular
> expressions to parse out the component names (see diff below). I will
> fix the quoting shortly (within the next day or so) and submit a
> single diff.
>
> thanks,
>
> pjjH
>
> Index: schema.py
>
> ===
>
> --- schema.py (revision 5816)
>
> +++ schema.py (working copy)
>
> @@ -876,17 +876,22 @@
>
> raise exc.ArgumentError(
> "Parent column '%s' does not descend from a "
> "table-attached Column" % str(self.parent))
> -m = re.match(r"^(.+?)(?:\.(.+?))?(?:\.(.+?))?$",
> self._colspec,
> - re.UNICODE)
> +m = self._colspec.split('.')
> if m is None:
> raise exc.ArgumentError(
> "Invalid foreign key column specification: %s" %
> self._colspec)
> -if m.group(3) is None:
> -(tname, colname) = m.group(1, 2)
> +
> +m.reverse()
> +(colname, tname) = m[0:2]
> +
> +if m[2] is None:
> schema = None
> else:
> -(schema, tname, colname) = m.group(1, 2, 3)
> +m1 = m[2:]
> +m1.reverse()
> +schema = '.'.join(m1)
> +
>
> On Mar 5, 7:21 pm, "phrrn...@googlemail.com"
> wrote:
>
> > OK. If it might be as easy as that, I will have a go and see how well
> > it works.
>
> > pjjH
>
> > On Mar 5, 4:31 pm, "Michael Bayer" wrote:
>
> > > phrrn...@googlemail.com wrote:
>
> > > > Sybase (and SQL Server) support cross-database JOINs (Sybase even
> > > > supports cross-database foreign-key constraints). There are four
> > > > components to an object identifier:
>
> > > > 1 = Object name
> > > > 2 = Schema name
> > > > 3 = Database name
> > > > 4 = Server name
>
> > > > the dataserver, database and schema are assumed for one-part
> > > > identifiers (e.g. 'foo'), dataserver and database assumed for two-part
> > > > identifiers (e.g. 'dbo.foo') and, finally, dataserver assumed for
> > > > three-part identifiers ('production.dbo.foo')
>
> > > > e.g. SELECT foo.* FROM BANANA.production.dbo.foo
> > > >SELECT foo.* FROM production.dbo.foo
> > > >SELECT foo.* FROM production..foo -- same as the previous query
> > > > if the callers default schema is dbo
> > > >SELECT foo.* FROM dbo.foo
> > > >SELECT foo.* FROM foo
> > > >SELECT foo.* FROM ..foo
> > > > I am not so interested in supporting four-part identifiers in SA but I
> > > > would like to figure out how to support three-part identifiers as very
> > > > many of our databases have cross-database references.
>
> > > > One natural (to me!) way of doing this is to add a 'database' property
> > > > to the Table and ForeignKeyConstraint schema items and have the Sybase/
> > > > SQL Server dialects always emit fully-qualified three-part identifiers
> > > > for table names.
>
> > > we have an element on Table called "schema". I had in mind that schema
> > > should accept dotted names, so SQLA generally doesn't need to get
> > > involved. The only change needed is to the IdentifierPreparer, such that
> > > when quote_schema is called, it separates the name along the dot first so
> > > that it can quote each token separately.
--~--~-~--~~~---~--~~
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: Using sqlalchemy in twisted.
Hi Jeff, In my project I use the *model* to indicate an instance of Database. I don't really need multiple instances of Database. But when I wrote unit test, I always create an new one database in setup, which create a new sqlite in memory database to avoid conflicts between test cases. About the trick to make *sess* a keywork parameter, that's really clever! Thanks a lot! On Mar 11, 9:05 pm, Jeff FW wrote: > Logging SA objects *after* the session is gone will always be a > problem, unless you make sure to detach all of them from the session. > I'd just log the original request, instead. In my case, I have to > convert all of my SA objects to something Perspective Broker > understands, so I actually log those *after* that, as they're no > longer part of the session--but I'm not sure if you can do that in > your case. > > As for the decorator--I got a little confused with your names--you > call it "model" in your decorator, but it's really an instance of > Database when it gets passed in as "self". One way to get rid of that > parameter would be to make sess a keyword argument, like so: > > def wrapper(*args, **kw): > sess = model.Session() > try: > return f(sess=sess, *args, **kw) > > and then change your method: > > def _getObjectById(self, klass, id, sess=None): > return sess.query(klass).get(id) > > That way, "self" will get passed in *args with no problem. > > Are you planning to have multiple instances of your Database class? > If not, I'd suggest changing everything in it into class methods, so > that way you can call it *without* an instance at all, and don't have > to worry about connecting to the database multiple times by accident. > Just a thought. > > -Jeff > > On Mar 10, 10:38 am, 一首诗 wrote: > > > Hi Jeff, > > > Thanks for your kind suggestion. > > > I first add some log decorators, but i found when it might cause to > > print sqalchemy objects which has not been bound to any session. > > > And I am not quite sure about how to make the decorator mor genreal. > > > Actually, I think I must use model as the first parameter because as a > > instance method, "_getObjectById" require the first parameter to be > > "self". > > Can you write a few lines of code to show your suggestion? > > > On Mar 8, 5:06 am, Jeff FW wrote: > > > > That's pretty similar to what I do, actually, if a bit simpler (but > > > that's good!) One suggestion would be to throw an except (maybe for > > > the base SQLAlchemy exception class) in your try block, otherwise you > > > run the risk of things dying in an ugly way. I'm not familiar with > > > pyamf, so I don't know how it would handle errors, but twisted usually > > > carries on as if nothing happens. > > > > Also, I'd make the decorator a bit more general--don't put the model > > > argument in wrapper(). Put sess first, then take *args and **kwargs, > > > and pass those right to the inner function f(). That way you can reuse > > > it for anything that requires a DB session. > > > > Other things you could add (if so inclined) are decorators for logging > > > and other types of error handling (like catching IntegrityErros thrown > > > by duplicates.) I do those things, but I might be a bit OCD :-) > > > > -Jeff > > > > On Mar 7, 1:41 am, 一首诗 wrote: > > > > > Hi, Thanks for your reply. I'm using it the way like you. The only > > > > difference is that I am using pyamf instead of PB. > > > > > On every request, I delegate required db operations to a class called > > > > Database, similar to these code below. > > > > > I used to use scope_session instead of create and close session every > > > > time. But as I said in my earlier mails, they don't work. > > > > > These code below seems to work right now. But if you have more > > > > suggestion, I will be very thankful. > > > > > #= > > > > > def require_session(f): > > > > '''create and close session for each synchronous method''' > > > > def wrapper(model, *args, **kw): > > > > sess = model.Session() > > > > try: > > > > return f(model, sess, *args, **kw) > > > > finally: > > > > sess.close() > > > > return wrapper > > > > > class Database() > > > > def __init__(self, conn_str): > > > > self.conn_str = conn_str > > > > self.engine = create_engine(self.conn_str, echo=False) > > > > self.Session = sessionmaker(bind = self.engine, > > > > expire_on_commit=False) > > > > > def getObjectById(self, klass, id): > > > > return threads.deferToThread(self._getObjectById, klass, id) > > > > > @require_session > > > > > def _getObjectById(self, sess, klass, id): > > > > > return sess.query(klass).get(id) > > > > #= > > > > > On Mar 6, 5:44 am, Jeff FW wrote: > > > > > > Don't use scoped_session--you'll run into problems no matter what you > > > > > do. I'm using Perspective B
[sqlalchemy] Re: Using sqlalchemy in twisted.
Logging SA objects *after* the session is gone will always be a problem, unless you make sure to detach all of them from the session. I'd just log the original request, instead. In my case, I have to convert all of my SA objects to something Perspective Broker understands, so I actually log those *after* that, as they're no longer part of the session--but I'm not sure if you can do that in your case. As for the decorator--I got a little confused with your names--you call it "model" in your decorator, but it's really an instance of Database when it gets passed in as "self". One way to get rid of that parameter would be to make sess a keyword argument, like so: def wrapper(*args, **kw): sess = model.Session() try: return f(sess=sess, *args, **kw) and then change your method: def _getObjectById(self, klass, id, sess=None): return sess.query(klass).get(id) That way, "self" will get passed in *args with no problem. Are you planning to have multiple instances of your Database class? If not, I'd suggest changing everything in it into class methods, so that way you can call it *without* an instance at all, and don't have to worry about connecting to the database multiple times by accident. Just a thought. -Jeff On Mar 10, 10:38 am, 一首诗 wrote: > Hi Jeff, > > Thanks for your kind suggestion. > > I first add some log decorators, but i found when it might cause to > print sqalchemy objects which has not been bound to any session. > > And I am not quite sure about how to make the decorator mor genreal. > > Actually, I think I must use model as the first parameter because as a > instance method, "_getObjectById" require the first parameter to be > "self". > Can you write a few lines of code to show your suggestion? > > On Mar 8, 5:06 am, Jeff FW wrote: > > > That's pretty similar to what I do, actually, if a bit simpler (but > > that's good!) One suggestion would be to throw an except (maybe for > > the base SQLAlchemy exception class) in your try block, otherwise you > > run the risk of things dying in an ugly way. I'm not familiar with > > pyamf, so I don't know how it would handle errors, but twisted usually > > carries on as if nothing happens. > > > Also, I'd make the decorator a bit more general--don't put the model > > argument in wrapper(). Put sess first, then take *args and **kwargs, > > and pass those right to the inner function f(). That way you can reuse > > it for anything that requires a DB session. > > > Other things you could add (if so inclined) are decorators for logging > > and other types of error handling (like catching IntegrityErros thrown > > by duplicates.) I do those things, but I might be a bit OCD :-) > > > -Jeff > > > On Mar 7, 1:41 am, 一首诗 wrote: > > > > Hi, Thanks for your reply. I'm using it the way like you. The only > > > difference is that I am using pyamf instead of PB. > > > > On every request, I delegate required db operations to a class called > > > Database, similar to these code below. > > > > I used to use scope_session instead of create and close session every > > > time. But as I said in my earlier mails, they don't work. > > > > These code below seems to work right now. But if you have more > > > suggestion, I will be very thankful. > > > > #= > > > > def require_session(f): > > > '''create and close session for each synchronous method''' > > > def wrapper(model, *args, **kw): > > > sess = model.Session() > > > try: > > > return f(model, sess, *args, **kw) > > > finally: > > > sess.close() > > > return wrapper > > > > class Database() > > > def __init__(self, conn_str): > > > self.conn_str = conn_str > > > self.engine = create_engine(self.conn_str, echo=False) > > > self.Session = sessionmaker(bind = self.engine, > > > expire_on_commit=False) > > > > def getObjectById(self, klass, id): > > > return threads.deferToThread(self._getObjectById, klass, id) > > > > @require_session > > > > def _getObjectById(self, sess, klass, id): > > > > return sess.query(klass).get(id) > > > #= > > > > On Mar 6, 5:44 am, Jeff FW wrote: > > > > > Don't use scoped_session--you'll run into problems no matter what you > > > > do. I'm using Perspective Broker from Twisted with SQLAlchemy. I > > > > make sure to create and commit/rollback a session for *every* PB > > > > request. It works perfectly, and that's the only way I was really > > > > able to get it to work in all cases. > > > > > Assuming you're using Twisted in a similar way, you could write a > > > > simple decorator to wrap any functions that need a database session in > > > > the begin/commit stuff as necessary. > > > > > If you can give more details of how you're using Twisted, I might be > > > > able to offer some more insight. > > > > > -Jeff > > > > > On Mar 5, 12:33 a
