[sqlalchemy] Re: Beginner: query.join not cooperating
On Tue, 24 Jun 2008 18:22:49 -0700 (PDT)
bukzor [EMAIL PROTECTED] wrote:
The Query.join() documentations says:
def join(self, prop, id=None, aliased=False, from_joinpoint=False)
'prop' may be one of:
* a class-mapped attribute, i.e. Houses.rooms
What exactly counts as class-mapped? I've set up a ForeignKey in my
Files table as well as a backref relation in my Projects table, but
none of these work:
print session.query(File).join(Project).filter_by(project='mario',
tapeout='sa11').one()
print
session.query(File).join(Project.files).filter_by(project='mario',
tapeout='sa11').one()
print session.query(File).join(Project.id).filter_by(project='mario',
tapeout='sa11').one()
print
session.query(File).join(File.project).filter_by(project='mario',
tapeout='sa11').one()
print
session.query(File).join(File.project_id).filter_by(project='mario',
tapeout='sa11').one()
They all fail with:
sqlalchemy.exceptions.InvalidRequestError: Mapper 'Mapper|File|files'
has no property 'sqlalchemy.orm.attributes.InstrumentedAttribute
object at 0x2a9a283650'
It seems like .join(Project) should 'just work' after the information
I put in the sytem, but I don't know much...
If there's some documentation that makes this clear, feel free to just
link it.
If you want more code, let me know what to copy/paste.
The ORM tutorial [1] is a little more instructive here, what join()
actually wants is a string with the name of the relation you put on (in
this case) File's mapper.
I'll assume it's something like
properties = {
...
'project': relation(Project),
...}
in which case you want join('project'). SA's rationale for doing it this
way is that you can do things like setting up multiple relations from
File to Project, and tweak the parameters on each relation to get
different behavior, without the joins being ambiguous. [2] has an
example of a setup like this.
Thanks!
--Buck
HTH
-Kyle
[1] http://sqlalchemy.org/docs/04/ormtutorial.html#datamapping_joins
[2] http://sqlalchemy.org/docs/04
/mappers.html#advdatamapping_relation_customjoin_multiplejoin
--~--~-~--~~~---~--~~
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: Beginner: query.join not cooperating
Thanks! That works, but is the line I quoted just wrong or outdated or
what?
This is what I'm talking about, under def join()
http://www.sqlalchemy.org/docs/04/sqlalchemy_orm_query.html#docstrings_sqlalchemy.orm.query_Query
By the way, is 0.4 the recommended version to use for a new project?
On Jun 24, 11:52 pm, Kyle Schaffrick [EMAIL PROTECTED] wrote:
On Tue, 24 Jun 2008 18:22:49 -0700 (PDT)
bukzor [EMAIL PROTECTED] wrote:
The Query.join() documentations says:
def join(self, prop, id=None, aliased=False, from_joinpoint=False)
'prop' may be one of:
* a class-mapped attribute, i.e. Houses.rooms
What exactly counts as class-mapped? I've set up a ForeignKey in my
Files table as well as a backref relation in my Projects table, but
none of these work:
print session.query(File).join(Project).filter_by(project='mario',
tapeout='sa11').one()
print
session.query(File).join(Project.files).filter_by(project='mario',
tapeout='sa11').one()
print session.query(File).join(Project.id).filter_by(project='mario',
tapeout='sa11').one()
print
session.query(File).join(File.project).filter_by(project='mario',
tapeout='sa11').one()
print
session.query(File).join(File.project_id).filter_by(project='mario',
tapeout='sa11').one()
They all fail with:
sqlalchemy.exceptions.InvalidRequestError: Mapper 'Mapper|File|files'
has no property 'sqlalchemy.orm.attributes.InstrumentedAttribute
object at 0x2a9a283650'
It seems like .join(Project) should 'just work' after the information
I put in the sytem, but I don't know much...
If there's some documentation that makes this clear, feel free to just
link it.
If you want more code, let me know what to copy/paste.
The ORM tutorial [1] is a little more instructive here, what join()
actually wants is a string with the name of the relation you put on (in
this case) File's mapper.
I'll assume it's something like
properties = {
...
'project': relation(Project),
...}
in which case you want join('project'). SA's rationale for doing it this
way is that you can do things like setting up multiple relations from
File to Project, and tweak the parameters on each relation to get
different behavior, without the joins being ambiguous. [2] has an
example of a setup like this.
Thanks!
--Buck
HTH
-Kyle
[1]http://sqlalchemy.org/docs/04/ormtutorial.html#datamapping_joins
[2]http://sqlalchemy.org/docs/04
/mappers.html#advdatamapping_relation_customjoin_multiplejoin
--~--~-~--~~~---~--~~
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: Beginner: query.join not cooperating
On Wed, 25 Jun 2008 00:23:41 -0700 (PDT) bukzor [EMAIL PROTECTED] wrote: Thanks! That works, but is the line I quoted just wrong or outdated or what? This is what I'm talking about, under def join() http://www.sqlalchemy.org/docs/04/sqlalchemy_orm_query.html#docstrings_sqlalchemy.orm.query_Query Fascinating. You're right, although I personally never knew it was supposed to allow that syntax; I've never used it. That exception is coming from the mapper code, which I'm not intimately familiar with, but the answer to your question would probably lie in the particulars of your mapper setup. In any case, in my experience the string form will always work, all other things being correct. By the way, is 0.4 the recommended version to use for a new project? I'm not a core dev, but it seems like 0.5 final release is beginning to shape up, so if you're just starting your project I'd imagine the general consensus would be to go with 0.5, unless you need Python 2.3 support, which is slated to be dropped. Judging by the traffic on the list of late, 0.4 (while it's quite usable and still being maintained) has a couple fairly obscure won't fix type issues, and if you run into any of them you'll probably be advised to upgrade to 0.5 anyway :) As well, 0.5's a good bit faster, several non-intuitive legacy behaviors have been removed, and many polymorphism improvements. A number of namespace and method names are changed, which might make migrating after the fact nontrivial. [1] lists most of the big differences in 0.5. For what it's worth, the beta seems quite stable to me. -Kyle [1] http://sqlalchemy.org/trac/wiki/05Migration --~--~-~--~~~---~--~~ 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] read only column with declaritive
Michael had helped me in the past to get read only columns defined like
this:
## old model non declarative
##class Quality(OrmObject):
##def comboname(self):
##return self._comboname
##comboname = property(comboname)
##
##quality = sao.mapper(Quality, quality_table,
##properties={
##'_comboname': quality_table.c.comboname,
##'comboname': sao.synonym('_comboname'),
I tried to translate this based on the declarative doc (BTW, there is a
typo on synonym_for it is shown as synonyn_for, i.e. an n instead of
the m and came up with this:
class Quality(Base):
__table__ = sa.Table(u'quality', metadata,
...
sa.Column(u'comboname', sa.String(length=63, convert_unicode=False),
server_default=),
...
)
# read only columns
_comboname = sao.column_property(__table__.c.comboname)
@sad.synonym_for('_comboname')
@property
def comboname(self):
return self._comboname
When reading from Quality I do not see the comboname column but I also
do not get any error.
Would appreciate if someone can put me right.
Best regards
Werner
--~--~-~--~~~---~--~~
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] Exception when changing to declarative
I am changing my model to using declarative.
I am getting an exception bool' object has no attribute
'__visit_name__' (full exception below) which was relatively difficult
for me to trace down.
The bool exception I get when I change this:
vrecingrwfit = sao.relation('Vrecingrwfit', primaryjoin=
(__table__.c.recipeid=='vrecingrwfit.fk_recipeid'),
passive_deletes=True)
to this:
vrecingrwfit = sao.relation('Vrecingrwfit', primaryjoin=
('Recipe.recipeid'=='vrecingrwfit.fk_recipeid'),
passive_deletes=True)
It happens as I did not define the primaryjoin correctly.
Correcting it to one string, i.e. removing the middle quotes fixed it:
vrecingrwfit = sao.relation('Vrecingrwfit', primaryjoin=
('Recipe.recipeid==vrecingrwfit.fk_recipeid'),
passive_deletes=True)
May I suggest to change/improve the exception, i.e. give some pointer
where in the model the problem is.
Werner
Traceback (most recent call last):
File saTest.py, line 42, in module
x = session.query(dbmin.Prefminimal).get(1)
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\session.py,
line 894, in query
return self._query_cls(entities, self, **kwargs)
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\query.py,
line 97, in __init__
self.__setup_aliasizers(self._entities)
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\query.py,
line 111, in __setup_aliasizers
mapper, selectable, is_aliased_class = _entity_info(entity,
ent.entity_name)
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\util.py,
line 398, in _entity_info
mapper = class_mapper(entity, entity_name, compile)
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\util.py,
line 488, in class_mapper
mapper = mapper.compile()
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\mapper.py,
line 370, in compile
mapper.__initialize_properties()
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\mapper.py,
line 391, in __initialize_properties
prop.init(key, self)
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\interfaces.py,
line 378, in init
self.do_init()
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\properties.py,
line 510, in do_init
self._determine_synchronize_pairs()
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\orm\properties.py,
line 605, in _determine_synchronize_pairs
eq_pairs = criterion_as_pairs(self.primaryjoin,
consider_as_foreign_keys=self._foreign_keys, any_operator=self.viewonly)
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\sql\util.py,
line 268, in criterion_as_pairs
visitors.traverse(expression, {}, {'binary':visit_binary})
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\sql\visitors.py,
line 123, in traverse
return traverse_using(iterate(obj, opts), obj, visitors)
File
c:\python25\lib\site-packages\sqlalchemy-0.5.0beta1-py2.5.egg\sqlalchemy\sql\visitors.py,
line 115, in traverse_using
meth = visitors.get(target.__visit_name__, None)
AttributeError: 'bool' object has no attribute '__visit_name__'
--~--~-~--~~~---~--~~
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: read only column with declaritive - resolved
It looks like I just confused myself, in my test case I got None returned and I thought it was a model definition issue but it was me leaving one of the columns used for the computed column at None. So if I do just the following the read only column (FB SQL computed column) are working for me. class Quality(Base): __table__ = sa.Table(u'quality', metadata, ... sa.Column(u'comboname', sa.String(length=63, convert_unicode=False), server_default=), ... ) # read only columns comboname = sao.column_property(__table__.c.comboname) Werner --~--~-~--~~~---~--~~ 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: accessing fields by name
Alex, alex bodnaru wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 hello friends, as opposed to a table.query() that returns a list of records of that table, with fields accessible as record attributes, a select() statement returns a list of tuples with the values of the fields in the virtual record. i'd like to access the fields by their column name or label, at least the non calculated or labelled ones. it would suffice to be able to retrieve a list with the labels of the fields in the resulted tuples. could you point me in the right direction? No expert, so take this with a grain of salt. q = db.Quality.__table__.select() print q print dir(q) print q.columns q.columns is giving the column names as a list Werner --~--~-~--~~~---~--~~ 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: accessing fields by name
AFAIK non-orm queries yield RowProxies, which are sort of ordered dicts, i.e. can be used as sequences or as dicts, keyed by column-name or by column-object itself. class RowProxy(object): Proxy a single cursor row for a parent ResultProxy. Mostly follows ordered dictionary behavior, mapping result values to the string-based column name, the integer position of the result in the row, as well as Column instances which can be mapped to the original Columns that produced this result set (for results that correspond to constructed SQL expressions). On Wednesday 25 June 2008 13:01:11 alex bodnaru wrote: hello friends, as opposed to a table.query() that returns a list of records of that table, with fields accessible as record attributes, a select() statement returns a list of tuples with the values of the fields in the virtual record. i'd like to access the fields by their column name or label, at least the non calculated or labelled ones. it would suffice to be able to retrieve a list with the labels of the fields in the resulted tuples. could you point me in the right direction? thanks in advance, alex --~--~-~--~~~---~--~~ 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: accessing fields by name
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 thanks werner, but i was asking about a generic query (not about one table) alex Werner F. Bruhin wrote: | Alex, | | alex bodnaru wrote: | -BEGIN PGP SIGNED MESSAGE- | Hash: SHA1 | | | hello friends, | | as opposed to a table.query() that returns a list of records of that table, with | fields accessible as record attributes, a select() statement returns a list of | tuples with the values of the fields in the virtual record. | | i'd like to access the fields by their column name or label, at least the non | calculated or labelled ones. it would suffice to be able to retrieve a list with | the labels of the fields in the resulted tuples. | | could you point me in the right direction? | | No expert, so take this with a grain of salt. | | q = db.Quality.__table__.select() | print q | print dir(q) | print q.columns | | q.columns is giving the column names as a list | | Werner | | | | -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iQCVAwUBSGIzI9pwN1sq38njAQKqoQP/VaLmQ87HFKc8At/iuMAk3PdWsJqBvOdi HLveRI+FGZPEr/ICezW0YzVtXjTiAqPees5j/6PjMsoHeY4AEJKSTCpCBEwCQSdY lcnyXrdg0cram1uhSItnLSyQslgQAPvAANQEv5AgJlh1garw/3O1h+yWi4kgqswO mYAClwKrBxE= =N3xj -END PGP SIGNATURE- --~--~-~--~~~---~--~~ 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: accessing fields by name
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 thanks a lot, it realy works :) [EMAIL PROTECTED] wrote: | AFAIK non-orm queries yield RowProxies, which are sort of ordered | dicts, i.e. can be used as sequences or as dicts, keyed by | column-name or by column-object itself. | | class RowProxy(object): | Proxy a single cursor row for a parent ResultProxy. | | Mostly follows ordered dictionary behavior, mapping result | values to the string-based column name, the integer position of | the result in the row, as well as Column instances which can be | mapped to the original Columns that produced this result set (for | results that correspond to constructed SQL expressions). | | | | On Wednesday 25 June 2008 13:01:11 alex bodnaru wrote: | hello friends, | | as opposed to a table.query() that returns a list of records of | that table, with fields accessible as record attributes, a select() | statement returns a list of tuples with the values of the fields in | the virtual record. | | i'd like to access the fields by their column name or label, at | least the non calculated or labelled ones. it would suffice to be | able to retrieve a list with the labels of the fields in the | resulted tuples. | | could you point me in the right direction? | | thanks in advance, | alex | | | | -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iQCVAwUBSGJC2NpwN1sq38njAQLk0gQAtG3y4MPPxVux6zFHYxaz+hpIHBHjAJkI nuJPi+c+iAm58OSQPGZXfyXadzaoFgu+DAgLsx7MaL5bwOtjnEUsuRQ9cMgaWIWZ b0cqJo7jXOuF39IglrEaLO3pNjl6mzkjHPkBXUO+KnybJxuCoazkwRC/moXV/kiN e/CGoq1fMM0= =OH0/ -END PGP SIGNATURE- --~--~-~--~~~---~--~~ 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] New istance in one-to-one relationship
Hi.
I'm trying to insert new data into db using one-to-one relationship,
but i'm getting this error:
sqlalchemy.exceptions.OperationalError: (OperationalError) (1048,
Column 'address_id' cannot be null) u'INSERT INTO companies
(address_id, company, ico, dic, bank_account) VALUES (%s, %s, %s, %s,
%s)' [None, u'Vnet a.s.', u'2332521351', u'SK234623513',
u'132412153/0900']
Here is the code:
class Address(Template):
pass
class Client(Template):
pass
addresses = Table('addresses', metadata, autoload=True)
clients = Table('clients', metadata,
Column('address_id', Integer,
ForeignKey('addresses.id')),
autoload=True)
orm.mapper(Client, clients, properties={
'address': orm.relation(Address, backref=backref('client',
uselist=False)) })
ses = SQLSession()
client = Client(**client_data)
address = Address(**address_data)
client.address = address
ses.save(client)
ses.commit()
ses.close()
The problem is, that sqlalchemy does not set the 'address_id' column
in 'clients' table.
How is the sqlalchemy-way to do this??
I was able to do it this way:
ses.SQLSession()
client = Client(address_id=0, **client_data)
ses.save(client)
ses.commit()
ses.rollback()
client.address = b.Address(**address_data)
ses.commit()
ses.close()
Thanks
Pavel
--~--~-~--~~~---~--~~
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: Beginner: printing out queries
On Jun 24, 2008, at 9:27 PM, bukzor wrote: Is there a way to print out the query as it would execute on the server? I'd like to copy/paste it into the server to get the 'explain' output, and the '%s' variables are very unhelpful here. the string output of str(statement) is what's actually sent to the client lib. In some cases, that is the actual string that goes to the server, such as cx_oracle, which receives the bind parameters separately within the network conversation. The fact that MySQLDB and psycopg2 do an in-client substitution of the string before passing on is an implementation artifact of those libraries. Feel free to construct the string yourself (this is specific to MySQL's bind param style): stmt = str(statement) compiled = statement.compile() params = compiled.params stmt = stmt % [params[k] for k in compiled.positiontup] I'd also like to turn off the 'alias and backtick-escape every column' default behavior if I can. we don't backtick every column. We quote case sensitive idenfitier names, if that's what you mean, where case sensitive is any identifier that is spelled out in MixedCase - this is required for the column to be properly recognized by the database. Use all lower case letters to indicate a case insensitive identifier. --~--~-~--~~~---~--~~ 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: New istance in one-to-one relationship
maybe try uselist=True in both relation and backref ??
just guessing
On Wednesday 25 June 2008 11:36:02 [EMAIL PROTECTED] wrote:
Hi.
I'm trying to insert new data into db using one-to-one
relationship, but i'm getting this error:
sqlalchemy.exceptions.OperationalError: (OperationalError) (1048,
Column 'address_id' cannot be null) u'INSERT INTO companies
(address_id, company, ico, dic, bank_account) VALUES (%s, %s, %s,
%s, %s)' [None, u'Vnet a.s.', u'2332521351', u'SK234623513',
u'132412153/0900']
Here is the code:
class Address(Template):
pass
class Client(Template):
pass
addresses = Table('addresses', metadata, autoload=True)
clients = Table('clients', metadata,
Column('address_id', Integer,
ForeignKey('addresses.id')),
autoload=True)
orm.mapper(Client, clients, properties={
'address': orm.relation(Address,
backref=backref('client', uselist=False)) })
ses = SQLSession()
client = Client(**client_data)
address = Address(**address_data)
client.address = address
ses.save(client)
ses.commit()
ses.close()
The problem is, that sqlalchemy does not set the 'address_id'
column in 'clients' table.
How is the sqlalchemy-way to do this??
I was able to do it this way:
ses.SQLSession()
client = Client(address_id=0, **client_data)
ses.save(client)
ses.commit()
ses.rollback()
client.address = b.Address(**address_data)
ses.commit()
ses.close()
Thanks
Pavel
--~--~-~--~~~---~--~~
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: New istance in one-to-one relationship
On Jun 25, 2008, at 4:36 AM, [EMAIL PROTECTED] wrote:
Hi.
I'm trying to insert new data into db using one-to-one relationship,
but i'm getting this error:
sqlalchemy.exceptions.OperationalError: (OperationalError) (1048,
Column 'address_id' cannot be null) u'INSERT INTO companies
(address_id, company, ico, dic, bank_account) VALUES (%s, %s, %s, %s,
%s)' [None, u'Vnet a.s.', u'2332521351', u'SK234623513',
u'132412153/0900']
Here is the code:
class Address(Template):
pass
class Client(Template):
pass
addresses = Table('addresses', metadata, autoload=True)
clients = Table('clients', metadata,
Column('address_id', Integer,
ForeignKey('addresses.id')),
autoload=True)
orm.mapper(Client, clients, properties={
'address': orm.relation(Address, backref=backref('client',
uselist=False)) })
ses = SQLSession()
client = Client(**client_data)
address = Address(**address_data)
client.address = address
ses.save(client)
ses.commit()
ses.close()
The problem is, that sqlalchemy does not set the 'address_id' column
in 'clients' table.
How is the sqlalchemy-way to do this??
something might be up with the table reflection in this case, try not
using autoload=True. FTR, heres a working proof of concept using
SQLite:
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite://', echo=True)
metadata = MetaData(engine)
class Address(object):
pass
class Client(object):
pass
addresses = Table('addresses', metadata, Column('id', Integer,
primary_key=True))
clients = Table('clients', metadata,
Column('id', Integer, primary_key=True),
Column('address_id',
Integer,ForeignKey('addresses.id'))
)
metadata.create_all()
mapper(Address, addresses)
mapper(Client, clients, properties={
'address': relation(Address, backref=backref('client',
uselist=False)) })
ses = sessionmaker()()
client = Client()
address = Address()
client.address = address
ses.save(client)
ses.commit()
ses.close()
assert client.address_id == address.id == 1
--~--~-~--~~~---~--~~
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] Mapped class and c attribute in 0.5
Hi, I read in the 0.5 release notes that the c attribute was no longer necessary when doing queries using the mapped class, but I did not see it mentioned that the c attribute was removed all together. It's just that I've been using the c attribute in my Mapped classes to access the database/table metadata and now all my code will need to be changed to use the table.c attribute instead. Was there a reason for removing the c attribute from mapped classes altogether ? Thanks, Huy --~--~-~--~~~---~--~~ 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: Mapped class and c attribute in 0.5
On Jun 25, 2008, at 10:48 AM, Huy Do wrote: Hi, I read in the 0.5 release notes that the c attribute was no longer necessary when doing queries using the mapped class, but I did not see it mentioned that the c attribute was removed all together. It's just that I've been using the c attribute in my Mapped classes to access the database/table metadata and now all my code will need to be changed to use the table.c attribute instead. Was there a reason for removing the c attribute from mapped classes altogether ? in 0.5, the attributes on classes vs. the columns on Tables are very different beasts now. Its not at all the same thing if you say: sess.query(MyClass.id, MyClass.name) vs. sess.query(mytable.c.id, mytable.c.name) since in the former case, we know that we are dealing with the MyClass mapper; in the latter case, we're not. The behavior of things like join(), polymorphic loading, other options, etc. are completely different - in the case of joined table inheritance it's dramatically different, where Subclass.id and subtable.c.id are literally different columns. So we really can't have a casual .c. attribute hanging around on classes with the new behavior; the user needs to be very explicit when choosing between MyClass.attr vs. table.c.attr. That said, you can retroactively add your .c. attribute using a MapperExtension that implements instrument_class(), and assigns the c attribute from the table to the class. --~--~-~--~~~---~--~~ 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] Divide columns with possible zeroDivisionError
Hello, A beginner question: I have a table with 4 columns: id, colA, colB, colC. I want to order (and make other operations as well ) the table asc or desc using the result of colB / colC knowing that colB and colC may equal to 0. When I try using query, the returned results are not correctly ordered: session.query(Mytable).order_by(asc(Mytable.colB / Mytable.colC)).all() I guess that the zero present in colB and colC may also interfere Is it possible to do this with queries with SA 0.4.6 ? Should I use select() and how or is it preferable to go for SA 0.5 ? How should I do to get what I want ? Additional question: I ordered Essential SqlAlchemy on the French Amazon, but haven't received it yet (may be still on print ?). Does anybody know if it has been issued already in the US ? Many thanks in advance for helping me Dominique --~--~-~--~~~---~--~~ 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] validation and old values
Hi, I'm adding validation to a (generic) gtk sql editor. The first thing I'd like to know is if I can reach the old values of an instance after I modified it (before committing). The reason is that I'd like to give the possibility to have new and old values in the validation of the record. thanks in advance sandro --~--~-~--~~~---~--~~ 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: Beginner: query.join not cooperating
On Jun 25, 2008, at 1:24 PM, bukzor wrote: Thanks for that versioning overview. Sorry for changing the topic (Should I make a separate post?), but is there a way to make the joins more automatic? I'd like to just specify some filter against table A and another against table B and have the system join them, even if the join needs to go through C or D. Of course the results would be undefined if there was more than one path between A and B, but this is not the case in my database and I'm sure a good subset of most databases. Will I need to roll this myself? Would people appreciate it if I added this functionality to the mapper class? If so, what code would you suggest editing? I just need some representation of the database as a graph. While you're of course free to create your own Query subclass which implements a graph traversal of relations to achieve this effect, this actual functionality was long ago removed (in the form of the old join_by() method), since it amounts to guessing; issues were apparent almost immedately after its introduction and it was soon deprecated. It requires an expensive graph traversal each time it's used, and leads to applications that silently, randomly fail as soon as a new foreign key path between the two target tables is added. It fits perfectly the kind of behavior that's targeted by explicit is better than implicit. --~--~-~--~~~---~--~~ 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: validation and old values
On Jun 25, 2008, at 1:45 PM, sandro dentella wrote: Hi, I'm adding validation to a (generic) gtk sql editor. The first thing I'd like to know is if I can reach the old values of an instance after I modified it (before committing). The reason is that I'd like to give the possibility to have new and old values in the validation of the record. thanks in advance the full historical data for an attribute is available as: from sqlalchemy.orm import attributes hist = attributes.get_history(attributes.instance_state(myobject), 'someattribute') the return value is a tuple of (newvalues, unchangedvalues, deletedvalues) and is the same form for collections and scalars. --~--~-~--~~~---~--~~ 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: Beginner: printing out queries
Thanks. Trying to do this in 0.5, it seems someone deleted the Query.compile() method without updating the rest of the code: Traceback (most recent call last): File ./test1.py, line 139, in ? try: exit(main(*argv)) File ./test1.py, line 121, in main print_query(q) File ./test1.py, line 20, in print_query print str(q) File /python-2.4.1/lib/python2.4/site-packages/ SQLAlchemy-0.5.0beta1-py2.4.egg/sqlalchemy/orm/query.py, line 1448, in __str__ return str(self.compile()) AttributeError: 'Query' object has no attribute 'compile' Reverting to 0.4, there are other problems. Statement.params is a function, so I added some ()'s, but it just returns the same query again. Statement.positiontup doesn't exist, and the string stmt doesn't have formatting to make use of python's % operator. After about an hour of looking, I can't figure out how to get my scalars out of the query object. On Jun 25, 7:25 am, Michael Bayer [EMAIL PROTECTED] wrote: On Jun 24, 2008, at 9:27 PM, bukzor wrote: Is there a way to print out the query as it would execute on the server? I'd like to copy/paste it into the server to get the 'explain' output, and the '%s' variables are very unhelpful here. the string output of str(statement) is what's actually sent to the client lib. In some cases, that is the actual string that goes to the server, such as cx_oracle, which receives the bind parameters separately within the network conversation. The fact that MySQLDB and psycopg2 do an in-client substitution of the string before passing on is an implementation artifact of those libraries. Feel free to construct the string yourself (this is specific to MySQL's bind param style): stmt = str(statement) compiled = statement.compile() params = compiled.params stmt = stmt % [params[k] for k in compiled.positiontup] I'd also like to turn off the 'alias and backtick-escape every column' default behavior if I can. we don't backtick every column. We quote case sensitive idenfitier names, if that's what you mean, where case sensitive is any identifier that is spelled out in MixedCase - this is required for the column to be properly recognized by the database. Use all lower case letters to indicate a case insensitive identifier. --~--~-~--~~~---~--~~ 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: Divide columns with possible zeroDivisionError
it was suggested by someone last week, for ordering dates that can be null, to use something like order_by( start_date is not null, start_date, end_date is null, end_date), i.e. use extra prefixing/suffixing boolean expressions to subcategorize the actual values. so depending on where do u want those rows with (B!=0, C==0 -inf) and (B==0, C==0 -- nan), u can put some extra expressoins in the order_by. or use CASE On Wednesday 25 June 2008 21:05:34 Michael Bayer wrote: On Jun 25, 12:36 pm, Dominique [EMAIL PROTECTED] wrote: Hello, A beginner question: I have a table with 4 columns: id, colA, colB, colC. I want to order (and make other operations as well ) the table asc or desc using the result of colB / colC knowing that colB and colC may equal to 0. When I try using query, the returned results are not correctly ordered: session.query(Mytable).order_by(asc(Mytable.colB / Mytable.colC)).all() I guess that the zero present in colB and colC may also interfere Is it possible to do this with queries with SA 0.4.6 ? Should I use select() and how or is it preferable to go for SA 0.5 ? it should be fine from a SQL generation perspective either way. To confirm this turn on SQL echoing, most easily by passing echo=True to your create_engine() call. As far as the ordering actually working, I'm not sure about typical database behavior when a division can result in a divide by zero - it would either raise an error or produce a NaN value (I think the latter might be more common). As far as how that orders you'd have to read the documentation for the DB in use. To have explicit cases set up for the divisor, use a CASE expression (which SQLA will generate for you using the case() construct). How should I do to get what I want ? Additional question: I ordered Essential SqlAlchemy on the French Amazon, but haven't received it yet (may be still on print ?). Does anybody know if it has been issued already in the US ? Many thanks in advance for helping me Dominique --~--~-~--~~~---~--~~ 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: Beginner: printing out queries
On Jun 25, 2008, at 2:14 PM, bukzor wrote: Thanks. Trying to do this in 0.5, it seems someone deleted the Query.compile() method without updating the rest of the code: Traceback (most recent call last): File ./test1.py, line 139, in ? try: exit(main(*argv)) File ./test1.py, line 121, in main print_query(q) File ./test1.py, line 20, in print_query print str(q) File /python-2.4.1/lib/python2.4/site-packages/ SQLAlchemy-0.5.0beta1-py2.4.egg/sqlalchemy/orm/query.py, line 1448, in __str__ return str(self.compile()) AttributeError: 'Query' object has no attribute 'compile' its been fixed in trunk. Keep an eye on http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/CHANGES . beta2 is out very soon (like, this week). Reverting to 0.4, there are other problems. Statement.params is a function, so I added some ()'s, but it just returns the same query again. Statement.positiontup doesn't exist, and the string stmt doesn't have formatting to make use of python's % operator. my code example used the 0.5 APIs, but in all cases you have to get the compiled object first. This is described in the tutorials for both 0.4 and 0.5, such as at http://www.sqlalchemy.org/docs/05/sqlexpression.html#sql_insert . --~--~-~--~~~---~--~~ 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: overzealous check breaks doctesting
jason kirtland wrote: [snip] Could the check somehow be modified to still find true builtins but not those defined in a doctest? Sure. Any suggestions for an alternate check? Heh, no. It's quite difficult to come up with any alternative.. I wonder why doctest.DocFileSuite makes these classes appear as __builtin__. I just went digging in doctest, but unfortunately this seems to be an unavoidable side effect of the behavior of the 'exec' statement, which doctest uses. I've just did some experiments, but whatever I do, any class definition I exec ends up with a __module__ set to __builtin__. Regards, Martijn --~--~-~--~~~---~--~~ 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: overzealous check breaks doctesting
On Jun 25, 2008, at 2:35 PM, Martijn Faassen wrote: jason kirtland wrote: [snip] Could the check somehow be modified to still find true builtins but not those defined in a doctest? Sure. Any suggestions for an alternate check? Heh, no. It's quite difficult to come up with any alternative.. I wonder why doctest.DocFileSuite makes these classes appear as __builtin__. I just went digging in doctest, but unfortunately this seems to be an unavoidable side effect of the behavior of the 'exec' statement, which doctest uses. I've just did some experiments, but whatever I do, any class definition I exec ends up with a __module__ set to __builtin__. this is probably naive but something like cls in __builtin__.__dict__.values() ? --~--~-~--~~~---~--~~ 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: overzealous check breaks doctesting
Martijn Faassen wrote:
jason kirtland wrote:
[snip]
Could the check somehow be modified to still find true builtins but not
those defined in a doctest?
Sure. Any suggestions for an alternate check?
Heh, no. It's quite difficult to come up with any alternative..
I wonder why doctest.DocFileSuite makes these classes appear as __builtin__.
I just went digging in doctest, but unfortunately this seems to be an
unavoidable side effect of the behavior of the 'exec' statement, which
doctest uses.
I've just did some experiments, but whatever I do, any class definition
I exec ends up with a __module__ set to __builtin__.
I think that comes from __name__ in the exec globals context:
d = {'__name__': 'foo'}
exec 'class Quux(object): pass' in d
d['Quux'].__module__
'foo'
or
__name__ = 'bar'
class O(object): pass
...
O.__module__
'bar'
--~--~-~--~~~---~--~~
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: overzealous check breaks doctesting
jason kirtland wrote: Martijn Faassen wrote: jason kirtland wrote: [snip] Could the check somehow be modified to still find true builtins but not those defined in a doctest? Sure. Any suggestions for an alternate check? Heh, no. It's quite difficult to come up with any alternative.. I wonder why doctest.DocFileSuite makes these classes appear as __builtin__. I just went digging in doctest, but unfortunately this seems to be an unavoidable side effect of the behavior of the 'exec' statement, which doctest uses. I've just did some experiments, but whatever I do, any class definition I exec ends up with a __module__ set to __builtin__. I think that comes from __name__ in the exec globals context: Yes, I just found out myself, Fred Drake told me. zope.testing actually has some code that does a workaround based on this trick too. Regards, Martijn --~--~-~--~~~---~--~~ 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] sharding id_chooser query_chooser
I am trying to understand what id_chooser and query_chooser do.
Id_chooser basically uses a instance primary key to determine what
shard the intance should be saved to? My primary keys(globally
unique) are made of up more than one fields. Would that be a problem
with Id_chooser?
How/when would do you query_chooser?
Can you set id_chooser and query_chooser to be None in:
create_session.configure(shards = {blah}, shard_chooser =
shard_chooser, id_chooser = None, query_chooser = None) ?
--~--~-~--~~~---~--~~
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: Mapped class and c attribute in 0.5
Michael Bayer wrote: On Jun 25, 2008, at 10:48 AM, Huy Do wrote: Hi, I read in the 0.5 release notes that the c attribute was no longer necessary when doing queries using the mapped class, but I did not see it mentioned that the c attribute was removed all together. It's just that I've been using the c attribute in my Mapped classes to access the database/table metadata and now all my code will need to be changed to use the table.c attribute instead. Was there a reason for removing the c attribute from mapped classes altogether ? in 0.5, the attributes on classes vs. the columns on Tables are very different beasts now. Its not at all the same thing if you say: sess.query(MyClass.id, MyClass.name) vs. sess.query(mytable.c.id, mytable.c.name) since in the former case, we know that we are dealing with the MyClass mapper; in the latter case, we're not. The behavior of things like join(), polymorphic loading, other options, etc. are completely different - in the case of joined table inheritance it's dramatically different, where Subclass.id and subtable.c.id are literally different columns. So we really can't have a casual .c. attribute hanging around on classes with the new behavior; the user needs to be very explicit when choosing between MyClass.attr vs. table.c.attr. That said, you can retroactively add your .c. attribute using a MapperExtension that implements instrument_class(), and assigns the c attribute from the table to the class. Michael, Thanks for the suggestion. Is there any metadata on the MyClass.id field at all ? like which column it's mapped too ? Lucky for me, I don't use any of the advanced SA features like inheritance and polymorphic stuff, so usually my MyClass.id is the same as my mytable.c.id (in the old SA). Thanks again, Huy --~--~-~--~~~---~--~~ 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: Adding a filter_by() clause by default.
Michael Bayer wrote: using 0.5: from sqlalchemy.orm import Query, sessionmaker class MyQuery(Query): def __new__(cls, entities, **kwargs): if hasattr(entities[0], 'deleted_at'): return Query(entities, **kwargs).filter_by(deleted_at=None) else: return object.__new__(cls) Session = sessionmaker(query_cls=MyQuery) This is cool stuff. huy --~--~-~--~~~---~--~~ 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: sharding id_chooser query_chooser
On Jun 25, 2008, at 6:30 PM, lilo wrote:
I am trying to understand what id_chooser and query_chooser do.
Id_chooser basically uses a instance primary key to determine what
shard the intance should be saved to?
id_chooser receives a primary key identifier, and then returns a list
of shards in which to search for that primary key. If you dont know
which shard to look on, it can return all the shards and they'll all
be checked in the order they were given. Obviously its better if it
returns a single shard identifier. If your app got hit a lot with get-
by-primary-keys and you couldn't determine the best shard, you might
want to return the shards in a different order each time to balance
load.
My primary keys(globally
unique) are made of up more than one fields. Would that be a problem
with Id_chooser?
the argument to id_chooser is a list, forming a composite identiifer.
How/when would do you query_chooser?
the Query calls upon query_chooser when it is ready to return
instances, passing in itself as an argument. the function returns the
list of shards in which to execute the query, and the total results of
all those shards are aggregated together.
Can you set id_chooser and query_chooser to be None in:
create_session.configure(shards = {blah}, shard_chooser =
shard_chooser, id_chooser = None, query_chooser = None) ?
nope. sharding requires these in order to do its work. If you want a
default chooser function, have them return a list of all shard names.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
