[sqlalchemy] Re: ObjectAlchemy - ODMG compatible layer for SQLAlchemy
thank you for your comments. -- http://case.lazaridis.com/wiki/Persist --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Question concerning Unicode-columns
> anyway, the upcoming release has the framework in place to address > this, which allows that different kinds of comparison operations > occur for different kinds of types. for an object attribute that > points to a related user-defined entity, using "is" is the better > choice rather than '==' since identity is what matters in that case. > but for unicodes and strings too, '==' is more approrpriate. so i > added a test case for this scenario as well as a modified compairson > for String/Unicode types in rev 1995. Hi, One of my column contains pickled objects which have a __cmp__ method (but no __eq__) a little bit weird: id doesn't compare anything, but allways raises a exception. This object comes from another library (it's actually a sparse matrix from scipy), so there's allmost nothing I can do to change this weird behavior. Anyway, despite they are user-defined entities, It seems that sqlalchemy tests for equality rather than for identity since the __cmp__ is always called, and thus exception always raised. Any solution to avoid the exception ? Jean-Philippe --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] thank you
I'm sitting here fine tuning a data model for a project and repeatedly delighted to find SA can (efficiently) do the creative things I want to do. I've said before and still think the mapper concept is powerful and flexible. SA has changed the way I program data driven applications giving me better code reuse, simpler logic, and flexibility. Thank you so much and keep up the good work. Randall --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] session.flush() closing connection
Hi,
I am using version 0.2.8 with Python 2.4.3 and MySQL 4.1.21 on an
up-to-date Linux Gentoo box
I am having a problem with session.flush(). It seems that every time I
issue a session
flush the DB connection is closed. If I do something like this
eng =
create_engine('mysql://test:[EMAIL PROTECTED]/test',strategy='threadlocal')
conn=eng.connect()
session = create_session(bind_to=conn)
query=session.query(dbPeople)
query=query.select_by_Lastname
listofpeople=query("Doe")
oneguy=listofpeople[0]
oneguy.Country="Namibia"
session.flush()
listofpeople=query("Smith")
The flush works alright and the database is updated, but the last line
result
in an error message:
sqlalchemy.exceptions.InvalidRequestError: This Connection is closed
Is that the normal behaviour? I would have expected the session to
query the DB and return a new list of dbPeople adding them to its list
of "persistent" object. Am I doing something wrong? Misunderstanding
something?
Cheers,
François
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Is this possible?
Hi, This is my first posting to this list as I am new to SQLAlchemy , so let me express my gratitude to those who develop SQLAlchemy... It's absolutely fabulous... The ORM in particular is fantastic! I am using version 0.2.8 with Python 2.4.3 and MySQL 4.1.21 on an up-to-date Linux Gentoo box Given a dbPeople class, here is what I'd like to do wherep=dbPeople() wherep.Lastname="Smith" wherep.Country="United Kingdom" listofsmith=session.query(dbPeople).select_by(wherep) listofsmithbosses=session.query(dbPeople).select_by(Manager=wherep) Given the right table, classes and mapper definition (omitted here, in the above example, Manager could be defined as a "backref") the first query would produce an SQL "WHERE" clause like "WHERE Lastname="Smith" AND Country="United Kingdom" The list of dbPeople named "Smith" in the UK Whilst the second would produce a query like SELECT FROM People AS Employee, People AS Manager, ManagerRel WHERE Employee.id=ManagerRel.Employee AND Manager.id=ManagerRel.Manager AND Manager.Lastname="Smith" AND Manager.Country="United Kingdom" The list of dbPeople whose manager is a "Smith" in the UK Essentially, the (transient) object would keep track of which properties were set (including set to None) and use those to construct the WHERE clause. Probably easier said then done The case of "Pending" instances should be quite complex ( and in some case would not make any sense) Best Regards, François --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: order_by strangeness
Thanks Michael, I'll make a test case if I get some time this (NZ) evening. SQL echoing is on, and what it's doing seems to make sense, though I'll check that on the test case. This is with PostgreSQL so old sqlite gotchas won't apply. At the moment I wouldn't rule out that I'm doing something silly. Thanks for such a great tool! cheers, Geoff --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: order_by strangeness
a simplified test case would help, also turn on your sql echoing and
take a look at what SQL its generating. also older versions of
sqlite have some order by strangeness going on so thats something to
keep in mind too.
On Oct 17, 2006, at 3:07 PM, GeoffL wrote:
>
> Hi,
>
> I'm using SQLAlchemy 0.28 with Pylons 0.98, and when I define this
> structure (abridged, so I'm not sure it will really compile...)
>
> people_table = Table("people", metadata,
> Column('id', Integer, primary_key=True),
> Column('user_name', String),
> Column('first_name', String),
> Column('last_name', String),
> Column('password', String)
> )
>
> phone_numbers_table = Table("phone_numbers", metadata,
> Column('id', Integer, primary_key=True),
> Column('person_id', Integer, ForeignKey('people.id')),
> Column('is_alert_number', Boolean),
> Column('type', String),
> Column('number', String)
> )
>
> class Person(object):
> pass
>
> class PhoneNumber(object):
> pass
>
> person_mapper = mapper(Person, people_table,
> properties = {
> 'phones' : relation(PhoneNumber, cascade="all, delete-orphan",
> backref="person", order_by=desc("is_alert_number"))
> })
>
> phone_mapper = mapper(PhoneNumber, phone_numbers_table)
>
>
> and then do the following with a person:
>
> for n in a_person.phones:
> print n.number, n.is_alert_number
>
>
> I get inconsistent results - at times just about anything can change -
> usually the results are right but about one time in three the
> is_alert_number is wrong, and occasionally not all the numbers are
> listed or the order changes. Removing the "order_by" seems to fix
> things.
>
> I've looked through the bug list on Trac, and I've looked through the
> group and nothing like this has jumped out at me. Are you interested
> and should I try to get a real simplified test case?
>
> cheers,
> Geoff
>
>
> >
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] order_by strangeness
Hi,
I'm using SQLAlchemy 0.28 with Pylons 0.98, and when I define this
structure (abridged, so I'm not sure it will really compile...)
people_table = Table("people", metadata,
Column('id', Integer, primary_key=True),
Column('user_name', String),
Column('first_name', String),
Column('last_name', String),
Column('password', String)
)
phone_numbers_table = Table("phone_numbers", metadata,
Column('id', Integer, primary_key=True),
Column('person_id', Integer, ForeignKey('people.id')),
Column('is_alert_number', Boolean),
Column('type', String),
Column('number', String)
)
class Person(object):
pass
class PhoneNumber(object):
pass
person_mapper = mapper(Person, people_table,
properties = {
'phones' : relation(PhoneNumber, cascade="all, delete-orphan",
backref="person", order_by=desc("is_alert_number"))
})
phone_mapper = mapper(PhoneNumber, phone_numbers_table)
and then do the following with a person:
for n in a_person.phones:
print n.number, n.is_alert_number
I get inconsistent results - at times just about anything can change -
usually the results are right but about one time in three the
is_alert_number is wrong, and occasionally not all the numbers are
listed or the order changes. Removing the "order_by" seems to fix
things.
I've looked through the bug list on Trac, and I've looked through the
group and nothing like this has jumped out at me. Are you interested
and should I try to get a real simplified test case?
cheers,
Geoff
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: ObjectAlchemy - ODMG compatible layer for SQLAlchemy
Ilias Lazaridis wrote: > peoples words: > > "SQLAlchemy implements the Data Mapper pattern, of which the Active > Record pattern (which SQLObject implements) is a subset." > > please notice: "subset". > > My conclusion is of course correct, and is based on the meaning of the > term "subset". The direct quote of your conclusion was this: "SQLAlchemy (DataMapper) can implement SQLObject (Active Record) SQLObject (Active Record) cannot implement SQLAlchemy (DataMapper)" Which is not "of course correct" because the statement is mixing terms a bit and isn't really very accurate. Let me see if I can help you out a bit on those terms. Libraries cannot implement other libraries. Libraries _can_ implement design patterns, though. SQLAlchemy and SQLObject are libraries. Data Mapper and Active Record are design patterns. Using this as a basis, here is a more correct conclusion that you could draw from the original statement: SQLAlchemy roughly implements the Data Mapper design pattern. SQLObject roughly implements the Active Record design pattern. The Active Record design pattern can be considered a subset of the Data Mapper design pattern. As a result of this, it is possible to implement the Active Record design pattern in SQLAlchemy, which is provided by the ActiveMapper extension to SQLAlchemy. It might be possible for someone to implement an SQLObject compatibility module for SQLAlchemy, but it might be difficult to provide 100% compatibility with the SQLObject API. I hope this makes a bit more sense to you, and I again would encourage you to read up on design patterns a bit so that you can have a better understanding of the subject that you are discussing. > So, possibly "peoples words" were wrong. Please don't take this the wrong way, but you clearly aren't armed with the knowledge that is necessary to come to that conclusion. The original quote is correct, and your interpretation of it is not. Its not a big deal, but its kind of irritating when people make blanket statements from a position of ignorance. Also, if you don't want people to come to the conclusion that you are a troll, it would probably be a good idea to take the advice of the creator of the project, rather than ignoring it. > Sadly, I've currently not the time to further look at the persistency > case. Well, good luck anyhow. > Just wondering more and more about "A Dynamic Language, Without a > Dynamic ORM" (Python). > > Seems like Zope DB and Durus are the only dynamic solutions for > python. > > The ORM league has (till now) failed to produce an dynamic OO layer on > top of Relational databases. I am not going to comment on these statements, because I really don't think that they make any sense at all. Good luck - -- Jonathan LaCour http://cleverdevil.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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to add properties?
Michael Bayer wrote:
> the question is, do you ever want that whitespace in the application
> space ? you might want to just create a custom String subclass that
> converts the whitespace in and out at the Table level. that would be
> cleaner.
That's really the best way to do it.
I probably should have read the documentation more carefully, it's
actually all in there. Kudos again for all of this.
Is this the correct implementation?
import sqlalchemy.types as types
class Name(types.TypeDecorator):
"""Right trimmed, lowercased Strings."""
impl = types.String
def convert_bind_param(self, value, engine):
return value.rstrip(' ').lower()
def convert_result_value(self, value, engine):
return value.rstrip(' ').lower()
user = Table('user', metadata,
Column('name', Name, primary_key=True),
Column('pwd', Name, primary_key=True),
)
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Stored procedures
Michael Bayer wrote: > the "func" keyword is used for stored procedures. in the latest > trunk, you can also create table-like elements out of funcs to > support multi-column stored procedures, and you can create the SQL > corresponding to the patterns you describe. That's pretty cool, too bad I can't use it for MySQL. Nevermind, I rewrote the stored proc using sqlalchemy and numarray to do the heavy number crunching and I'm happy with the result. Not only is the code shorter, but it's at least as fast as before ! George --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to add properties?
On Oct 17, 2006, at 1:46 PM, Christoph Zwerschke wrote: > > The problem is that when I request the user 'fred' with > get_by(name='fred'), and it is stored as 'Fred' in the database, it > will > still not be found, because name is a synonym for _name which is the > original column. Any suggestion how to solve this? > we can get some support for things like that by adding a callable argument to synonym() which does an in-python translation of the value. but that is starting to get ugly, and also starts to have redundancy (i.e. more than one way to do it) vs. using a MapperExtension which can override select_by (and should probably allow get_by and others to be overridden as well). 0.3 is going to have a cleaner way to add extensions onto Query for things like this. the question is, do you ever want that whitespace in the application space ? you might want to just create a custom String subclass that converts the whitespace in and out at the Table level. that would be cleaner. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to add properties?
Michael Bayer wrote:
> the easiest way to have two properties point to the same thing is to
> just use a "property" on your class:
>
> class Foo(object):
>def _get_uname(self):
> return self.user_name
>def _set_uname(self, value):
> self.user_name = value
>username = property(_get_uname, _set_uname)
>
> although one of the points of "synonym" was to also allow the name to
> be used in select_by() so ill look into restoring it.
That would be good. However, I still have a problem here:
Usually, you don't want a simple synonym. For instance, in a legacy
database I have tables like the following:
CREATE TABLE user
(
name character(8) NOT NULL,
pwd character(8),
CONSTRAINT user_pkey PRIMARY KEY (name)
)
Whenever I request the name, it is right padded with blanks which do not
matter at all, but lead to all sorts of problems. Moreover, the name is
also case insensitive.
So I'd like to have a property that transparently gets me a trimmed and
lowercased version of the name instead of the real name stored in the
database. I want to get 'fred' instead of 'Fred', 'FRED' etc.
So what I do is the following, as suggested:
class User(object):
def _get_name(self):
return self._name.rstrip(' ').lower()
def _set_name(self, value):
self._name = value
name = property(_get_name, _set_name)
mapper(User, user, properties = {
'_name': user.c.name,
'name': synonym('_name') }
)
The problem is that when I request the user 'fred' with
get_by(name='fred'), and it is stored as 'Fred' in the database, it will
still not be found, because name is a synonym for _name which is the
original column. Any suggestion how to solve this?
-- Christoph
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/sqlalchemy
-~--~~~~--~~--~--~---
[sqlalchemy] Re: ForeignKeyConstraint or ForeignKey ?
Alexandre CONRAD wrote: > So if I understand well, ForeignKeyConstraint's "onupdate" argument is > at the database level. Meaning you don't have to worry about SA's "on > update" and "on delete" as this job is handled by the database it self, > internally. Eg, when a parent is deleted by SA, all childs refereing to > that parent would also be deleted by the *database* itself, as this is > how the relation rules were designed at the database-level. Right ? > > On the other hand, without any database-level "action" rule, SA handles > "onupdate" and "ondelete" within a session object. Meaning that when SA > will delete a parent object, is also has to send *extra* DELETE commands > to every refering childs so they are as well deleted. > > What would happend if SA's "ondelete" is contradictory to the database's > action rules ? theres an FAQ entry on this, basically everything should work just fine since SA will issue DELETE statements for the rows in the proper order before the "ondelete" ever gets triggered. it also means that other rows in the DB which may have not been loaded into memory get properly deleted too. I might look into adding some relation options for this, regarding "do i load in the full list of child items for a DELETE so that i know what rows to delete?". right now its a little murky whether or not it does that, it should be more explicitly defined. > > If I understand, a database can be designed "freely" without any action > contraints between tables, and SA can handle this at a higher level, > sending "manual" commands to the database, keeping track of what should > be deleted. But shouldn't this be the database's job ? I know this gives > extra flexibility. But I'm just wondering what's the right way to do it... theres no right way to do it, both ways, or a combination, is fine. deleting is not what most applications spend most of their time doing, and we still have to handle all the INSERTs, UPDATEs (since ON UPDATE is generally only when primary keys change, which SA doesnt handle directly...). SA was written without ON DELETE in mind too heavily, and ORM's like HIbernate dont account for it at all AFAICT, but generally each of the two approaches shouldnt conflict too much. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: automatic datatype for a ForeignKey
i like this idea, and i committed a check in the foreign key init to
set the type if a column has NULLTYPE for a type (which is the default
if you send None). but having the parameter optional requires some
positional *args games which id rather not get into right now, so it
looks like:
Column('user_id', None, ForeignKey("users.user_id"))
Alexandre CONRAD wrote:
> Hello,
>
> I was wondering why not having an automatic datatype assigned to a FK
> column instead of repeating twice the data type that has to be set...
>
> a user table would have:
>
>Column('user_id', Integer, primary_key=True),
>
> and an address table refereing to a user would have:
>
>Column('user_id', ForeignKey("users.user_id")),
>
> Here, I don't specify that the FK column is an Integer, because some
> clever mechanics would do that for me.
>
> I suppose that this was already thougt before, but I was just wondering...
>
> Regards,
> --
> Alexandre CONRAD
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: How to add properties?
Michael Bayer wrote:
> nevermind, i just restored SynonymProperty in rev 2002. it will also
> create the "property" on the class for you.
ok i lied, it wont create the "property" unless you say synonym('foo',
proxy=True).
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: ObjectAlchemy - ODMG compatible layer for SQLAlchemy
Ilias Lazaridis wrote: > Seems like Zope DB and Durus are the only dynamic solutions for python. > > The ORM league has (till now) failed to produce an dynamic OO layer on > top of Relational databases. Ok guys, drop the keyboards, don't feed the troll: http://www.encyclopediadramatica.com/index.php/Ilias http://en.wikipedia.org/wiki/Ilias_Lazaridis Thank you all. -- Nicola Larosa - http://www.tekNico.net/ If surfing the extensive network of Debra Lafave-infatuated websites and chat rooms provides any indication of how American men feel about Debra Lafave having sex with her student, the feelings of American men can be summed up in one, rather simple, collective thought: "Where was Debra Lafave when I was in junior high school?" -- David Steinberg, January 2006 --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to add properties?
nevermind, i just restored SynonymProperty in rev 2002. it will also
create the "property" on the class for you.
On Oct 17, 2006, at 12:51 PM, Christoph Zwerschke wrote:
>
> Martin Kaffanke schrieb:
>> Am Dienstag, den 17.10.2006, 14:48 +0200 schrieb Christoph Zwerschke:
>>> mapper(User, pg_user, properties={
>>> 'user_name': pg_user.c.usename,
>>> 'usename' : synonym('user_name')})
>>
>> Thats the solution.
>>
>>> However, this results in the following error:
>>>
>>> NameError: global name 'SynonymProperty' is not defined
>>
>> Try to do that in a single test condition.
>
> Seems to be a problem of the current trunk (rev2001). I'll create a
> ticket for that.
>
> -- Christoph
>
> >
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/sqlalchemy
-~--~~~~--~~--~--~---
[sqlalchemy] Re: How to add properties?
Martin Kaffanke schrieb:
> Am Dienstag, den 17.10.2006, 14:48 +0200 schrieb Christoph Zwerschke:
>> mapper(User, pg_user, properties={
>> 'user_name': pg_user.c.usename,
>> 'usename' : synonym('user_name')})
>
> Thats the solution.
>
>> However, this results in the following error:
>>
>> NameError: global name 'SynonymProperty' is not defined
>
> Try to do that in a single test condition.
Seems to be a problem of the current trunk (rev2001). I'll create a
ticket for that.
-- Christoph
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/sqlalchemy
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Stored procedures
the "func" keyword is used for stored procedures. in the latest trunk, you can also create table-like elements out of funcs to support multi-column stored procedures, and you can create the SQL corresponding to the patterns you describe. however, these patterns were worked out for Postgres users...MySQL doesnt really support this (i didnt even know it had custom functions at all?). SA is only issuing SQL to the database and cant do anything that you couldnt do at a MySQL command line, for example. On Oct 17, 2006, at 12:12 PM, George Sakkis wrote: > > Is there a way to call a stored procedure from sqlalchemy and access > the returned result set ? If it makes a difference, I'm specifically > interested in MySQL stored procedures. What I want to do is use this > result set as part of another query, but MySQL doesn't currently allow > treating a stored procedure as a (temporary) table, e.g. the following > doesn't work: > > Select y > from (call my_proc(1,2)) > where x>3; > > If I can capture the result set of my_proc with sqlalchemy, I can > express the outer query in python and bypass MySQL's lack of syntactic > support for this. Otherwise I'll probably rewrite my_proc in > sqlalchemy, which may not be that bad after all, but I'd rather avoid > this if possible. > > Thanks, > George > > > > --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to add properties?
"synonym" is a function that has sort of died out, i had not even realized it was still in the codebase until this example...so sorry about that! the easiest way to have two properties point to the same thing is to just use a "property" on your class: class Foo(object): def _get_uname(self): return self.user_name def _set_uname(self, value): self.user_name = value username = property(_get_uname, _set_uname) although one of the points of "synonym" was to also allow the name to be used in select_by() so ill look into restoring it. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to map pg_user and pg_group?
Christoph Zwerschke wrote:
> But that expression needs to be parsed to SQL anyway, and doing so all
> involved columns could be tracked. Another clue is that the class for
> the relation object has been stated as 'Group' which is mapped to
> pg_group. Why do you actually need a Foreign*Key*, not a Foreign*Table*?
yes, i could absolutely parse it out. you should dig around the source
code a little bit to see how that works, including the diffs for the
revsion i just made for this one which illustrate how its done.
with regards to "foreignkey", i am just not totally comfortable
throwing some more "automatic" behavior in there just yet, for reasons
already stated, since its easy enough to state it explicitly.
the "foreignkey" parameter is still undergoing an evolution and a
clarification, which is one reason its still a little murky as to what
its used for and what it needs to know. it accomplishes multiple
things and i have not yet worked out a really good way to clarify its
individual roles.
currently, heres why its a list of columns:
- for a self-referential mapper or other self-referring table
relationship, where theres only one table anyway...we need to know
specific columns in that case in order to determine what kind of
relationship we are looking at.
- it can account for all the foreign key columns in a more complicated
join condition, where maybe only one of those columns actually points
to the target table and therefore has meaning.
- it is used in the determination of the "lazy clause" in a
self-referential table relationship so that it can determine what
columns in the clause get converted into a bind parameter for a lazy
load.
- i have recently stepped up its role to also indicate which
column-mapped attributes should actually be "synchronized" when objects
are connected together or detached during flush time. here is a
snippet of the recent example someone had (which is also present in the
"relationships.py" unit test if you want to play with it):
pageversions = Table("pageversions", metadata,
Column("jobno", Unicode(15), primary_key=True),
Column("pagename", Unicode(30),
primary_key=True),
Column("version", Integer, primary_key=True,
default=1),
)
)
pages = Table("pages", metadata,
Column("jobno", Unicode(15),
ForeignKey("jobs.jobno"), primary_key=True),
Column("pagename", Unicode(30),
primary_key=True),
Column("current_version", Integer))
mapper(Page, pages, properties={
'currentversion': relation(PageVersion,
foreignkey=pages.c.current_version,
primaryjoin=and_(pages.c.jobno==pageversions.c.jobno,
pages.c.pagename==pageversions.c.pagename,
pages.c.current_version==pageversions.c.version),
post_update=True),
'versions': relation(PageVersion, cascade="all,
delete-orphan",
primaryjoin=and_(pages.c.jobno==pageversions.c.jobno,
pages.c.pagename==pageversions.c.pagename),
order_by=pageversions.c.version,
backref=backref('page', lazy=False,
primaryjoin=and_(pages.c.jobno==pageversions.c.jobno,
pages.c.pagename==pageversions.c.pagename)))
})
above, the "Page" object points to a list of PageVersion objects, via
the "versions" relation. however, there is also a second relationship
between them called "currentversion", which refers to the "current"
entry in the "versions" relation. When a given PageVersion becomes the
"currentversion", the "current_version" column in Page gets set to its
"version" value, and when it is removed as the "currentversion", the
"version" value gets removed. However, the other columns in the join
condition (jobno and pagename) do not change. In this example they are
primary key columns, and nulling them out raises an error on flush.
the "foreignkey" here refers only to "current_version" and indicates
the only column that actually needs to be changed when
attaching/detaching the "currentversion" to "Page".
its pretty impressive to me how far SA has managed to go with its
current relationship-definition model, which is literally a SQL
expression and a list of columns...because it is so sparse yet it is
more powerful than any other system I have ever seen (including
hibernate by a mile). it might be time for a less sparse approach to
the whole thing, for reasons of clarity, but it hasnt really dawned on
me yet what that might look like.
--~--~-~--~~~---~--~~
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
-~--~~~~--
[sqlalchemy] Re: retrieving and updating using mappers
[EMAIL PROTECTED] wrote:
> Firstly when retrieving information using mappers, I have not been very
> successful at all in this If someone could offer a 2 line example of
> this problem.
>
> Retrieving Email.address if User.name == 'jack'
>
> ...
>
> I have the same problem with locating a row and replacing one value in
> that row. So in the above example if I wanted to change the
> Email.address value for user.name == jack .
Since I'm also trying to learn SA, I wrote a commented example code that
guides you through all necessary steps from creating the tables and the
mappers to adding users and addresses. Most of it is already contained
in the tutorial.
# import everything you need:
from sqlalchemy import *
# connect to your database:
db = create_engine(...)
metadata = BoundMetaData(db)
# define the tables:
user_table = Table('user', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(40)),
Column('password', String(10)),
Column('age', Integer))
email_table = Table('mail_address', metadata,
Column('id', Integer, primary_key=True),
Column('address', String(100), nullable=False),
Column('user_id', Integer, ForeignKey('user.id')))
# create the tables:
email_table.drop()
user_table.drop()
user_table.create()
email_table.create()
# define your Mapper objects:
class User(object):
def __init__(self, name, age, password):
self.name = name
self.age = age
self.password = password
def __str__(self):
return self.name
class Email(object):
def __init__(self, address, user_id=None):
self.address = address
self.user_id = user_id
def __str__(self):
return self.address
# create the mappings:
user_mapper = mapper(User, user_table)
email_mapper = mapper(Email, email_table)
user_mapper.add_property('addresses', relation(Email))
# obtain a session:
session = create_session()
# now you can start to play:
# create users:
user1 = User('Stephen', 42, 'Joshua')
user2 = User('Willy', 21, 'free')
user3 = User('Jack', 33, 'forgot')
# save the users:
session.save(user1)
session.save(user2)
session.save(user3)
# create email addresses:
user1.addresses.append(Email('[EMAIL PROTECTED]', user1.id))
user1.addresses.append(Email('[EMAIL PROTECTED]', user1.id))
user2.addresses.append(Email('[EMAIL PROTECTED]', user2.id))
user3.addresses.append(Email('[EMAIL PROTECTED]'))
user3.addresses.append(Email('[EMAIL PROTECTED]'))
# save everything to the database:
session.flush()
# read user Jack anew from the database:
del user3 # forget about Jack
user = session.query(User).get_by(name='Jack')
# print all email addresses of Jack:
print
print user, 'is', user.age, 'years old'
print 'and has these email addresses:'
for adr in user.addresses:
print '\t', adr
# Jack gets one year older:
user.age += 1
# delete Jacl's aol email addresses:
user.addresses = [adr for adr in user.addresses
if not adr.address.endswith('@aol.com')]
# Jack has got a new email address:
user.addresses.append(Email('[EMAIL PROTECTED]'))
session.flush() # store changes
# read user Jack anew from the database:
del user
user = session.query(User).get_by(name='Jack')
# again, print all email addresses of Jack:
print
print user, 'is now', user.age, 'years old'
print 'and has these email addresses:'
for adr in user.addresses:
print '\t', adr
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Stored procedures
Is there a way to call a stored procedure from sqlalchemy and access the returned result set ? If it makes a difference, I'm specifically interested in MySQL stored procedures. What I want to do is use this result set as part of another query, but MySQL doesn't currently allow treating a stored procedure as a (temporary) table, e.g. the following doesn't work: Select y from (call my_proc(1,2)) where x>3; If I can capture the result set of my_proc with sqlalchemy, I can express the outer query in python and bypass MySQL's lack of syntactic support for this. Otherwise I'll probably rewrite my_proc in sqlalchemy, which may not be that bad after all, but I'd rather avoid this if possible. Thanks, George --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: ObjectAlchemy - ODMG compatible layer for SQLAlchemy
Jonathan LaCour wrote: > Ilias Lazaridis wrote: [...] > > b) a simple confirmation of my conclusion: > > > >>> SQLAlchemy (DataMapper) can implement SQLObject (Active Record) > >>> SQLObject (Active Record) cannot implement SQLAlchemy (DataMapper) > > Your conclusion is misguided because you don't have any understanding [...] > then I suggest that you stop trying to interpret people's words. peoples words: "SQLAlchemy implements the Data Mapper pattern, of which the Active Record pattern (which SQLObject implements) is a subset." please notice: "subset". My conclusion is of course correct, and is based on the meaning of the term "subset". So, possibly "peoples words" were wrong. - Overcomplicating issues, in order to not let people understand is very simple. Simplifying the issues, in order to let people understand - that's the real difficulty. Sadly, I've currently not the time to further look at the persistency case. http://case.lazaridis.com/wiki/Persist Just wondering more and more about "A Dynamic Language, Without a Dynamic ORM" (Python). Seems like Zope DB and Durus are the only dynamic solutions for python. The ORM league has (till now) failed to produce an dynamic OO layer on top of Relational databases. . > -- > Jonathan LaCour > http://cleverdevil.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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy -~--~~~~--~~--~--~---
[sqlalchemy] Re: Using logging with SQLAlchemy
Ok you have to take all the "echo='debug'" and stuff out if you are
using logger (perhaps I should change my decision of "echo" and
"logging" being coupled...since i can see how this is going to go when
i release)
if you want to log SQL with logging, its like this:
import logging
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
i would still very much like to see your logging setup where SA is
managing to conflict with it, since it really shouldnt.
metaperl wrote:
> Michael Bayer wrote:
>
> >
> > make sure that you dont have any "echo=True" keywords anywhere within
> > your SQLAlchemy setup, since that is what makes it try to configure
> > logging on its own.
>
> echo is set to debug. What do I do to allow SA logging output to be
> interspered with my own logging output?
>
>
> self.engine =
> create_engine("mssql://pxe62:[EMAIL PROTECTED]:1433/DTA", echo='debug',
> encoding='utf-16')
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: ObjectAlchemy - ODMG compatible layer for SQLAlchemy
Ilias Lazaridis wrote: > I think it's clear that I'm neither looking for book-tips, nor for an > academic discussion. I think what Michael is trying to convey is that the "simple statement" that you are looking for confirmation on has lots of highly "academic" baggage relating to what programmers call "design patterns." In order for you to understand and interpret the simple statement, you really need to understand the concepts of design patterns, and very specifically the Active Record and Data Mapper design patterns. > I'm just looking for: > > b) a simple confirmation of my conclusion: > >>> SQLAlchemy (DataMapper) can implement SQLObject (Active Record) >>> SQLObject (Active Record) cannot implement SQLAlchemy (DataMapper) Your conclusion is misguided because you don't have any understanding of the underlying concepts presented. Michael's suggestion to read the Fowler book is one way for you to learn about the concepts, then to understand the simple statement, and finally to draw a conclusion. If you aren't interested in buying the book, I hear that Google is a great way to learn. If you aren't interested in learning, then I suggest that you stop trying to interpret people's words. If you want a simple statement relating to SQLObject vs. SQLAlchemy that you don't need to learn to understand, here is one for you: SQLAlchemy is more flexible than SQLObject. Anything much deeper than that is going to require you to brush up a bit on some fairly high-level concepts, like design patterns. Best of luck! -- Jonathan LaCour http://cleverdevil.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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy -~--~~~~--~~--~--~---
[sqlalchemy] Re: Using logging with SQLAlchemy
Michael Bayer wrote:
>
> make sure that you dont have any "echo=True" keywords anywhere within
> your SQLAlchemy setup, since that is what makes it try to configure
> logging on its own.
echo is set to debug. What do I do to allow SA logging output to be
interspered with my own logging output?
self.engine =
create_engine("mssql://pxe62:[EMAIL PROTECTED]:1433/DTA", echo='debug',
encoding='utf-16')
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: How to add properties?
Am Dienstag, den 17.10.2006, 14:48 +0200 schrieb Christoph Zwerschke:
> Martin Kaffanke wr9te:
> > This should normally be done, that all other properties are still there.
> >
> > Try to make an example script where you have problems, which we can run
> > if you have troubles here.
>
> You're right. What confused me is that when you do
>
> mapper(User, pg_user, properties={
> 'user_name': pg_user.c.usename})
> Now I start to understand what the synonym function is for:
>
> mapper(User, pg_user, properties={
> 'user_name': pg_user.c.usename,
> 'usename' : synonym('user_name')})
Thats the solution.
> However, this results in the following error:
>
> NameError: global name 'SynonymProperty' is not defined
Try to do that in a single test condition.
Martin
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: ObjectAlchemy - ODMG compatible layer for SQLAlchemy
Michael Bayer wrote: > On Oct 17, 2006, at 1:50 AM, Ilias Lazaridis wrote: > > > I understand this like this: > > > > SQLAlchemy (DataMapper) can implement SQLObject (Active Record) > > SQLObject (Active Record) cannot implement SQLAlchemy (DataMapper) > > " > > I recommend you read Fowler's book if youre looking for an academic > dicussion of data mapper vs. active record: > > http://www.martinfowler.com/books.html#eaa I think it's clear that I'm neither looking for book-tips, nor for an academic discussion. I'm just looking for: a) a simple confirmation of a statement: "To avoid the technical issues involved the complication can be summarized as: SQLAlchemy implements the Data Mapper pattern, of which the Active Record pattern (which SQLObject implements) is a subset." http://mail.python.org/pipermail/python-list/2006-September/359164.html b) a simple confirmation of my conclusion: > > SQLAlchemy (DataMapper) can implement SQLObject (Active Record) > > SQLObject (Active Record) cannot implement SQLAlchemy (DataMapper) . -- http://case.lazaridis.com/wiki/Persist --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] automatic datatype for a ForeignKey
Hello,
I was wondering why not having an automatic datatype assigned to a FK
column instead of repeating twice the data type that has to be set...
a user table would have:
Column('user_id', Integer, primary_key=True),
and an address table refereing to a user would have:
Column('user_id', ForeignKey("users.user_id")),
Here, I don't specify that the FK column is an Integer, because some
clever mechanics would do that for me.
I suppose that this was already thougt before, but I was just wondering...
Regards,
--
Alexandre CONRAD
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: ForeignKeyConstraint or ForeignKey ?
Michael Bayer wrote: > ForeignKeyConstraint is a table-level definition, ForeignKey is a > column level. If you have just a single ForeignKey on a single column, > they are equivalent. for a composite foreign key, ForeignKeyConstraint > gives you the extra verbosity needed (i.e. its exactly analgous to > SQL's FOREIGN KEY clause which can be placed on a column or on the > table overall). > > "on_update" within ForeignKeyConstraint will place a "ON > UPDATE=" directive on your table's DDL definition. This is > SQL level cascading and is described: > > http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html > (scroll down to "REFERENCES reftable [ ( refcolumn ) ]") > > the thing that "cascades" here is the update of a column value, such as > UPDATE SET x=5. > > the "cascade" flag on relation() also relates to the "cascading" of > data along a relationship, but is talking about a relationship between > two objects, and refers to various session operations such as save(), > update(), delete(), etc. > > the thing that "cascades" here is a session call such as > session.delete(x). > > so the term "cascade" refers to a general class of behavior (traversing > along some kind of relationship), but has two totally different > meanings in context (your database tables vs. a SQLAlchemy session > object) So if I understand well, ForeignKeyConstraint's "onupdate" argument is at the database level. Meaning you don't have to worry about SA's "on update" and "on delete" as this job is handled by the database it self, internally. Eg, when a parent is deleted by SA, all childs refereing to that parent would also be deleted by the *database* itself, as this is how the relation rules were designed at the database-level. Right ? On the other hand, without any database-level "action" rule, SA handles "onupdate" and "ondelete" within a session object. Meaning that when SA will delete a parent object, is also has to send *extra* DELETE commands to every refering childs so they are as well deleted. What would happend if SA's "ondelete" is contradictory to the database's action rules ? If I understand, a database can be designed "freely" without any action contraints between tables, and SA can handle this at a higher level, sending "manual" commands to the database, keeping track of what should be deleted. But shouldn't this be the database's job ? I know this gives extra flexibility. But I'm just wondering what's the right way to do it... I'm still learning a lot about databases in general (I'm pretty new to that). So I might be confusing. I just want to make sure I'm not misunderstanding "actions". I don't want to mix/confuse myself. Regards, -- Alexandre CONRAD --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] retrieving and updating using mappers
Hi, I am new to programming and have been experimenting with alchemy. I have been wanting to ask some really simple questions that I just cant seem to get right. I have been looking for sometime now to try find code examples but I get the feeling everyone is so far ahead of me it seems ridiculous to include this... Firstly when retrieving information using mappers, I have not been very successful at all in this If someone could offer a 2 line example of this problem. Retrieving Email.address if User.name == 'jack' class User(object): def __init__(self,user_id, name, age, password): self.user_id = user_id self.name = name self.age = age self.password = password def __repr__(self): return self.name user_mapper = mapper(UserTable, user_table) class Email(object): def __init__(self, address,user_id): self.address = address self.user_id = user_id def __repr__(self): return self.address email_mapper = mapper(EmailTable, email_table) I have the same problem with locating a row and replacing one value in that row. So in the above example if I wanted to change the Email.address value for user.name == jack . 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to add properties?
Martin Kaffanke wr9te:
> This should normally be done, that all other properties are still there.
>
> Try to make an example script where you have problems, which we can run
> if you have troubles here.
You're right. What confused me is that when you do
mapper(User, pg_user, properties={
'user_name': pg_user.c.usename})
Then the usename column is missing, all the *other* columns are still
there. I had expected that if properties are added, then this would add
user_name as an *alias* for usename.
This feature probably needs better documentation.
Now I start to understand what the synonym function is for:
mapper(User, pg_user, properties={
'user_name': pg_user.c.usename,
'usename' : synonym('user_name')})
However, this results in the following error:
NameError: global name 'SynonymProperty' is not defined
-- Christoph
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/sqlalchemy
-~--~~~~--~~--~--~---
[sqlalchemy] Re: How to add properties?
Am Dienstag, den 17.10.2006, 13:58 +0200 schrieb Christoph Zwerschke:
> If I understand it correctly, when I do
>
> mapper(User, users_table)
>
> then User will have properties corresponding to all columns of the
> users_table. When I want to add an addresses property, I do:
>
> mapper(User, users_table, properties = {
> 'addresses' : relation(Address)})
>
> But then, all the other columns are not mapped any more. Is it possible
> to only *add* the 'addresses' property, keeping all the table columns as
> properties?
This should normally be done, that all other properties are still there.
Try to make an example script where you have problems, which we can run
if you have troubles here.
Martin
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] How to add properties?
If I understand it correctly, when I do
mapper(User, users_table)
then User will have properties corresponding to all columns of the
users_table. When I want to add an addresses property, I do:
mapper(User, users_table, properties = {
'addresses' : relation(Address)})
But then, all the other columns are not mapped any more. Is it possible
to only *add* the 'addresses' property, keeping all the table columns as
properties?
-- Christoph
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/sqlalchemy
-~--~~~~--~~--~--~---
[sqlalchemy] Re: How to map pg_user and pg_group?
Michael Bayer wrote:
> Christoph Zwerschke wrote:
>> mapper(User, pg_user, properties={
>> 'user_id': pg_user.c.usesysid,
>> 'user_name': pg_user.c.usename,
>> 'is_super': pg_user.c.usesuper,
>> 'groups': relation(Group, viewonly=True,
>> primaryjoin=pg_user.c.usesysid==func.any
>> (pg_group.c.grolist))})
>>
>> I get this error:
>>
>> sqlalchemy.exceptions.ArgumentError: On relation 'groups', can't
>> figure out which side is the foreign key for join condition
>> 'pg_user.usesysid = any(pg_group.grolist)'. Specify the
>> 'foreignkey' argument to the relation.
>>
>> Shouldn't it be clear what the foreign key is in this situation?
>
> to a human, maybe. to a python interpreter the right side of the
> binary '==' expression is just a sqlalchemy.sql.Function, which looks
> nothing like the sqlalchemy.schema.Column type which it expects to
> locate as a foreign key.
But that expression needs to be parsed to SQL anyway, and doing so all
involved columns could be tracked. Another clue is that the class for
the relation object has been stated as 'Group' which is mapped to
pg_group. Why do you actually need a Foreign*Key*, not a Foreign*Table*?
> for the "lazy clause" generation, which is when it takes "x=y" and
> converts it into "x=?", this is the same issue as the foreign key.
> but for this, i have committed in rev 2001 a more thorough search for
> a "Column" in each side of the clause so that it can identify which
> side of a "=" operation it can apply a bind parameter to, so a test
> program can now generate:
>
> SELECT pg_group.groname AS pg_group_groname, pg_group.grolist AS
> pg_group_grolist, pg_group.grosysid AS pg_group_grosysid
> FROM pg_catalog.pg_group
> WHERE %(lazy_bcba)s = any(pg_group.grolist) ORDER BY pg_group.grosysid
Thanks, this works great now!
-- Christoph
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/sqlalchemy
-~--~~~~--~~--~--~---
[sqlalchemy] Re: ForeignKeyConstraint or ForeignKey ?
ForeignKeyConstraint is a table-level definition, ForeignKey is a column level. If you have just a single ForeignKey on a single column, they are equivalent. for a composite foreign key, ForeignKeyConstraint gives you the extra verbosity needed (i.e. its exactly analgous to SQL's FOREIGN KEY clause which can be placed on a column or on the table overall). "on_update" within ForeignKeyConstraint will place a "ON UPDATE=" directive on your table's DDL definition. This is SQL level cascading and is described: http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html (scroll down to "REFERENCES reftable [ ( refcolumn ) ]") the thing that "cascades" here is the update of a column value, such as UPDATE SET x=5. the "cascade" flag on relation() also relates to the "cascading" of data along a relationship, but is talking about a relationship between two objects, and refers to various session operations such as save(), update(), delete(), etc. the thing that "cascades" here is a session call such as session.delete(x). so the term "cascade" refers to a general class of behavior (traversing along some kind of relationship), but has two totally different meanings in context (your database tables vs. a SQLAlchemy session object) --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] ForeignKeyConstraint or ForeignKey ?
Hello, What's the difference between using object "ForeignKeyConstraint" or using object "Column" with a ForeignKey object as argument ? From this question follows this other one: what's the difference between using onupdate="CASCADE", ondelete="CASCADE" from "ForeignKeyConstraint" and argument cascade="all, delete-orphan" from the relation() function ? I don't know which should be used between those 2 explanations in the docs: http://www.sqlalchemy.org/docs/datamapping.myt#datamapping_relations_lifecycle and http://www.sqlalchemy.org/docs/metadata.myt#metadata_tables_onupdate Regards, -- Alexandre CONRAD --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to map pg_user and pg_group?
On Oct 17, 2006, at 5:38 AM, Christoph Zwerschke wrote:
> mapper(User, pg_user, properties={
> 'user_id': pg_user.c.usesysid,
> 'user_name': pg_user.c.usename,
> 'is_super': pg_user.c.usesuper,
> 'groups': relation(Group, viewonly=True,
> primaryjoin=pg_user.c.usesysid==func.any
> (pg_group.c.grolist))})
>
> I get this error:
>
> sqlalchemy.exceptions.ArgumentError: On relation 'groups', can't
> figure
> out which side is the foreign key for join condition
> 'pg_user.usesysid =
> any(pg_group.grolist)'. Specify the 'foreignkey' argument to the
> relation.
>
> Shouldn't it be clear what the foreign key is in this situation?
to a human, maybe. to a python interpreter the right side of the
binary '==' expression is just a sqlalchemy.sql.Function, which looks
nothing like the sqlalchemy.schema.Column type which it expects to
locate as a foreign key.
> Now when I explicitly specify the foreign key (as pg_group.grosysid or
> pg_group.grolist, doesn't matter),
>
> mapper(User, pg_user, properties={
> 'user_id': pg_user.c.usesysid,
> 'user_name': pg_user.c.usename,
> 'is_super': pg_user.c.usesuper,
> 'groups': relation(Group, viewonly=True,
> primaryjoin=pg_user.c.usesysid==func.any(pg_group.c.grolist),
> foreignkey=pg_group.c.grosysid)})
>
> Then the groups property returns all existing groups, not the
> groups of
> the corresponding user (the following query is echoed by the engine):
>
> SELECT pg_group.grolist, pg_group.grosysid, pg_group.groname
> FROM pg_user, pg_group
> WHERE pg_user.usesysid = any(pg_group.grolist)
> ORDER BY pg_group.grosysid
for the "lazy clause" generation, which is when it takes "x=y" and
converts it into "x=?", this is the same issue as the foreign key.
but for this, i have committed in rev 2001 a more thorough search for
a "Column" in each side of the clause so that it can identify which
side of a "=" operation it can apply a bind parameter to, so a test
program can now generate:
SELECT pg_group.groname AS pg_group_groname, pg_group.grolist AS
pg_group_grolist, pg_group.grosysid AS pg_group_grosysid
FROM pg_catalog.pg_group
WHERE %(lazy_bcba)s = any(pg_group.grolist) ORDER BY pg_group.grosysid
a similar approach might apply to the "foreignkey" detection issue
although i like to keep these "clause analysis" functions as
conservative as possible, since forcing explicitness in the case of
unusual configurations (joining on a function is an unusual
configuration) reduces the chances of surprise behavior. in the case
of the "lazy clause" i would rather not have users start to worry
about manually defining those.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Problem with SQL views
On Oct 17, 2006, at 3:26 AM, artee wrote:
>> of postgres views.
> Views are one of most important thing in DB programming.
> I'm surprised that this topic isn't tested in SA :(
heh...i meant just the reflection of views. if you create a view in
oracle and try to reflect it like a table, youll fail miserably.
>> mapped table, you can use the "primary_key" argument to mapper()
> I've tried to do this but some errors occur on session.query
> (wlt.select pass):
> Wlt.mapper = mapper(Wlt, wlt, primary_key=wlt.c.id)
> items = wlt.select(w, order_by=order, limit=5).execute() # OK, 5 items
> items = session.query(Wlt).select(w, order_by=order, limit=5)
> -> TypeError: iteration over non-sequence
> -> in
> -> File "build\bdist.win32\egg\sqlalchemy\orm\mapper.py", line 398, in
> _compile_tables
>
primary_key is an array argument...
>> itself and force the columns that should be used for the pk.
> There is a primary key defined - Column('id', String(),
> primary_key=True)
> It isn't enough ?
it should be. you are probably having some problems with the
overriding of primary keys during table reflection, since there was a
recent bugfix that is only in the SVN trunk with regards to that.
>
> If you want I can sent to you full DB structure to test :)
>
sure. first try it with the latest SVN trunk to see if that helps.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: ObjectAlchemy - ODMG compatible layer for SQLAlchemy
On Oct 17, 2006, at 1:50 AM, Ilias Lazaridis wrote: > I understand this like this: > > SQLAlchemy (DataMapper) can implement SQLObject (Active Record) > SQLObject (Active Record) cannot implement SQLAlchemy (DataMapper) > " I recommend you read Fowler's book if youre looking for an academic dicussion of data mapper vs. active record: http://www.martinfowler.com/books.html#eaa --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to map pg_user and pg_group?
Michael Bayer schrieb:
> in theory you should be able to do this:
>
> 'groups':relation(Group, primaryjoin=pg_user.c.usesysid==func.any
> (pg_group.c.grolist), viewonly=True)
>
> notice the "viewonly" flag which is in the trunk only, which will
> tell SA not to try persisting that mapping (since it cant)...i would
> gather that was the problem you had if you had tried it this way before.
Without the "viewonly" flag, I got "No syncrules generated" errors.
These have disappeared, but there are still problems here.
Again, assume the following setup:
pg_user = Table('pg_user', metadata,
Column('usesysid', Integer, primary_key=True),
Column('usename', String, unique=True),
Column('usesuper', Boolean))
pg_group = Table('pg_group', metadata,
Column('grosysid', Integer, primary_key=True),
Column('groname', String, unique=True),
Column('grolist', String))
class User(object):
pass
class Group(object):
pass
Now when I map as follows:
mapper(User, pg_user, properties={
'user_id': pg_user.c.usesysid,
'user_name': pg_user.c.usename,
'is_super': pg_user.c.usesuper,
'groups': relation(Group, viewonly=True,
primaryjoin=pg_user.c.usesysid==func.any(pg_group.c.grolist))})
I get this error:
sqlalchemy.exceptions.ArgumentError: On relation 'groups', can't figure
out which side is the foreign key for join condition 'pg_user.usesysid =
any(pg_group.grolist)'. Specify the 'foreignkey' argument to the relation.
Shouldn't it be clear what the foreign key is in this situation?
Now when I explicitly specify the foreign key (as pg_group.grosysid or
pg_group.grolist, doesn't matter),
mapper(User, pg_user, properties={
'user_id': pg_user.c.usesysid,
'user_name': pg_user.c.usename,
'is_super': pg_user.c.usesuper,
'groups': relation(Group, viewonly=True,
primaryjoin=pg_user.c.usesysid==func.any(pg_group.c.grolist),
foreignkey=pg_group.c.grosysid)})
Then the groups property returns all existing groups, not the groups of
the corresponding user (the following query is echoed by the engine):
SELECT pg_group.grolist, pg_group.grosysid, pg_group.groname
FROM pg_user, pg_group
WHERE pg_user.usesysid = any(pg_group.grolist)
ORDER BY pg_group.grosysid
-- Christoph
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/sqlalchemy
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Problem with SQL views
Michael Bayer wrote:
> first thing, "wlt" is a view? thats very surprising that "autoload"
> would work with that...but then im not so famliar with the specifics
It's working :) Views are transparent to the DB engine and should be
visible as tables.
> of postgres views.
Views are one of most important thing in DB programming.
I'm surprised that this topic isn't tested in SA :(
> mapped table, you can use the "primary_key" argument to mapper()
I've tried to do this but some errors occur on session.query
(wlt.select pass):
Wlt.mapper = mapper(Wlt, wlt, primary_key=wlt.c.id)
items = wlt.select(w, order_by=order, limit=5).execute() # OK, 5 items
items = session.query(Wlt).select(w, order_by=order, limit=5)
-> TypeError: iteration over non-sequence
-> in
-> File "build\bdist.win32\egg\sqlalchemy\orm\mapper.py", line 398, in
_compile_tables
I suppose that the problem is in limit statement but without success,
the second
query returns only 10 records instead of about 30 records:
session.query(Wlt).select(w) # Fail
> itself and force the columns that should be used for the pk.
There is a primary key defined - Column('id', String(),
primary_key=True)
It isn't enough ?
It's another strange behavior:
With "metadata.engine.echo = True" I have different queries for the
same situation:
"wlt.select" creates following SQL:
SELECT wlt.id, /*blabla*/ FROM wlt ORDER BY wlt.pat_name ASC LIMIT 5
but "session.query(Wlt).select" creates:
SELECT wlt.id AS wlt_id, /*blabla*/ FROM wlt ORDER BY wlt.pat_name ASC
LIMIT 5
In the second example, there are no primary key (id) defined but
according to definition:
wlt = Table('wlt', metadata, Column('id', String(), primary_key=True),
autoload=True)
there should be a primary key (id).
When I change definition to:
wlt = Table('wlt', metadata, Column('wlt_id', String(),
primary_key=True), autoload=True)
an error occurs:
(ProgrammingError) column wlt.wlt_id does not exist
If you want I can sent to you full DB structure to test :)
Cheers,
Artur
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
