On 3/17/06, Michael Bayer <[EMAIL PROTECTED]> wrote:
Yes I can see that. The reason that I started using SA was that it was the best fit for my needs. Imagine doing this in other ORM's.
Sometimes I'm not sure how to associate objects and weather if it would work if I did. For example, I have a table similar to this:
JOB (
ID INTEGER
SOURCE_TYPE VARCHAR
SOURCE_REF VARCHAR
)
The idea is not to keep JOB_ID in dozen tables that have some work performed on them. The reasons are that there are maybe more different jobs that coud be performed on the table (IMPORT, AUTOJOIN, BILLING, ...) or som of the jobs can be performed more than once. So I keep relation data directly inside JOB table inside SOURCE_TYPE and SOURCE_REF columns. So I cannot say that SOURCE_TYPE/SOURCE_REF is foreign key to any particular table.
I generally have classes like this:
class Campaign(...):
@propgetmemo
def last_import_job(self):
return Job.select(source_type='SOME_TAG', source_ref=str(self.id )) # this is actually more complicated because of dates, subqueries and other stuff
@propgetmemo
def last_autojoin_job(self):
return Job.select(source_type='SOME_TAG2', source_ref=str(self.id))
Is there a way to somehow move my create_campaign() function inside Campaign.__init__? It is not wery important, more the matter of style.
Well, maybe the simplest answer is that those proceures are used from other systems as well (like Oracle forms). It is an existing system and it seemed inapropriate to duplicate the functionality in another place. This concrete app that I'm doing now is actually a webapp used to connect three different systems each with its own functionality. Another thing is that there are maybe other applications comming that will be using same procedures (we are evaluating different web frameworks by building the same web app).
More complicated answer is that the stored procedure is located directly where data is. Inside database. So it performs wery well when it has to access several dozens of tables. It is responsible for automatically creating campaigns based on previous history data, target user preferences, tables of several million users that have to be somehow automatically connected to each other using weights and such. It is just not suitable to do it from Python.
Not necessarily. The fact that I have to construct SQL string and then in another blurb of code call it with dozen of parameters (times two). I also have to get cursor, module and engine (this is maybe related with OUT parameters.) every time.
On Mar 17, 2006, at 8:18 AM, Qvx 3000 wrote:
> 1. If I replace all those INSERTs and create_campaign with SA
> objects nothing is written to database prior to calling
> cc_util.process_campaign_import. The matter is even worse because I
> need ID's for processing but they are not yet available. If I call
> commit to make sure objects are in database (inserted) and that IDs
> are available, I cannot ROLLBACK this in case of error. The real
> question is maybe: How to do insert without database COMMIT?
>
well it seems like the approach youve taken with your application is
having somewhat of a clash with the application approach fostered by
SQLAlchemy's ORM. The general idea with the SA ORM is that you
worry about your objects and associating them together, but you
typically never need to deal with the primary keys explicitly, those
are handled for you.
Yes I can see that. The reason that I started using SA was that it was the best fit for my needs. Imagine doing this in other ORM's.
Sometimes I'm not sure how to associate objects and weather if it would work if I did. For example, I have a table similar to this:
JOB (
ID INTEGER
SOURCE_TYPE VARCHAR
SOURCE_REF VARCHAR
The idea is not to keep JOB_ID in dozen tables that have some work performed on them. The reasons are that there are maybe more different jobs that coud be performed on the table (IMPORT, AUTOJOIN, BILLING, ...) or som of the jobs can be performed more than once. So I keep relation data directly inside JOB table inside SOURCE_TYPE and SOURCE_REF columns. So I cannot say that SOURCE_TYPE/SOURCE_REF is foreign key to any particular table.
I generally have classes like this:
class Campaign(...):
@propgetmemo
def last_import_job(self):
return Job.select(source_type='SOME_TAG', source_ref=str(self.id )) # this is actually more complicated because of dates, subqueries and other stuff
@propgetmemo
def last_autojoin_job(self):
return Job.select(source_type='SOME_TAG2', source_ref=str(self.id))
However, if you do need the IDs explicitly, just create them
yourself, using a sequence, stored procedure, or whatever, and assign
that to the appropriate ID attribute on your object. When it gets
INSERT'ed by the ORM, thats the ID that will be used. the defaults
you create on Columns can be executed manually , i.e.
id = mytable.c.mycolumn.default.execute ()
(more for the docs....)
> 2. Creating objects. Sometimes to create an object is nothing more
> than inserting it into database, but sometimes it takes much more.
> This is the reason that for most of my objects I have stored
> procedures that create them. You might say use triggers, but this
> system is accessed not only from this Python (semi-experimental)
> app. My typical response to this is similar to create_campaign
> function: I call store procedure, take back ID, fetch object via
> Campaign.get(id), return object. Can I somehow move this into
> __init__ or do it better in some other way? Those create_x
> functions are annoying.
Is there a way to somehow move my create_campaign() function inside Campaign.__init__? It is not wery important, more the matter of style.
What are the stored procedures doing that is necessary ? can those
concepts be replaced by simpler ideas such as plain table
relationships ? do the stored procedures completely supercede the
need to call INSERT on the table, or are you looking for a "pre" or
"post" phase to a particular INSERT ? The easiest way would be via
MapperExtensions ( http://www.sqlalchemy.org/docs/
adv_datamapping.myt#adv_datamapping_extending ). These will let you
execute something before/after each INSERT (more hooks can be added
to this as well).
Well, maybe the simplest answer is that those proceures are used from other systems as well (like Oracle forms). It is an existing system and it seemed inapropriate to duplicate the functionality in another place. This concrete app that I'm doing now is actually a webapp used to connect three different systems each with its own functionality. Another thing is that there are maybe other applications comming that will be using same procedures (we are evaluating different web frameworks by building the same web app).
More complicated answer is that the stored procedure is located directly where data is. Inside database. So it performs wery well when it has to access several dozens of tables. It is responsible for automatically creating campaigns based on previous history data, target user preferences, tables of several million users that have to be somehow automatically connected to each other using weights and such. It is just not suitable to do it from Python.
> 3. Calling stored procedures in not so verbose and explicit way.
>
youre talking about the fact that OUT parameters are not supported
yet, right ?
Not necessarily. The fact that I have to construct SQL string and then in another blurb of code call it with dozen of parameters (times two). I also have to get cursor, module and engine (this is maybe related with OUT parameters.) every time.
> Maybe there are some answers in docs, but I've already read it once
> top to bottom (not remembering everything). Now I'm just doing
well, your app is constructed very differently from the typical ORM-
styled application. I would take a look at the ZBlog demo app in
Myghty to see what I had in mind with the ORM, maybe youd get some
ideas on how to "bridge" your architecture to something more along
those lines (or maybe youre beyond that). Also consider that the
ORM is optional; if youre only using small pieces of it, you might
want to use the SQL construction by itself, or maybe just use mappers
to SELECT but your own method to insert records, etc., if in fact
your stored procedures are handling the issue of dependent objects.
