This is probably more information that you wanted and it
might be
different for CherryPy, but I have built applications with
mod_python
that scaled well using the approach of holding open
connections for
some users. For admins I usually create a separate
connection for
more granularity and auditing.
The application I built was for a large hospital, so I
classified the
connections into three types for security and auditing
purposes:
1. Need to query data but will never have a legitimate
reason to
change anything.
2. Need to query data and may need to update or insert
records.
3. Need to query and may need to make larger data changes
in the
database such as creating or authorizing users. Usually an
administrator or power user.
My application created persistent connections for #1 and #2.
At this
stage of the application I was not concerned with who the
user was,
only what rights their account had. The login process
validated
against either RADIUS, Active Directory or SecurID depending
on the
level of access requested. Some accounts (like mine)
required Active
Directory and SecurID
These accounts also had restrictions on where they could
login from, I
could only login with admin rights from the IP assigned to
my desk
computer. Logging in at any other location would succeed,
but
downgrade my rights to the #1 category by default.
The connections for category #1 and #2 were not per user
though, they
were available by any account with the correct permissions.
The second type of connections were presented with a screen
to make
changes but any query data was heavily scrutinized. This
was largely
because of concern over someone entering sub-selects that
were either
destructive or would consume too many resources due to
their
complexity.
For these first two types of accounts a database connection
pool was
created and left open for any requests. The first type of
connections
used an account that had no rights in the database to modify
anything
and were never presented with a screen for making changes.
The second
type of user also shared a database account behind the code
with
limited permissions.
The third category of users, which was very small, had a
connection
created upon login. This was mostly for additional auditing
and to
generate an email and SMS alert. When an admin logged in I
paged
their mobile device with a brief notice that they had logged
in on
that system.
This way if they got a page saying they had logged into the
application from a different workstation or at odd times the
admin
would know something was wrong.
If you don't have the same requirements for auditing and
security this
will probably be over-kill. Usually when I program
something most of
my time is spent on building the security portion (to
include
auditing), at this point it is mostly just a habit but can
tend to be
over-kill for a lot of apps.
If you have people requesting the same thing you can also
just
pre-generate that and separate what people see from when it
is
generated. One log analysis program I wrote generated a
list of "Top
10 ..." every hour, day and week. These were computed
by a
system-level script every 5 minutes and the results were
saved to a
static file.
I took this approach because of the work involved with
computing the
data; I didn't want it to start every time someone looked at
the page
(especially since most wouldn't stick around to see the
results if it
took too long to display).
Wayne
On 3/3/07, 1stpoint <1stpoint gmail.com> wrote:
>
> I developed a large web app with multiple forms and
heavy mysql
> interaction.
> I wrote a sql processor that
> 1. opens a connection to mysql
> 2. executes the sql code (and optional commits for
updates,deletes)
> 3. closes the connection
> 4. returns the resultset.
>
> The 1st iteration of the site went live last year and
is working
> well. The next iteration significanly increases the
sql access.
>
> Is this a bad approach?
> I am concerned that if hundreds of people are using
this site
> concurrently (which will happen), will we run out of
resources?
> Is it better to leave a persistent connection open? (I
did this
> earlier on in the development and ran out of
connections)
> would something like SQL Alchemy be a better approach?
>
>
> Here is the sql processor module:
> def process_sql(Sql,mode):
> db=MySQLdb.connect
>
(host=hostname,user=username,passwd=dbpassword,db=dbname)
> cur=db.cursor()
> retval=''
> try:
> result=cur.execute(Sql)
> except:
> result=0
> if result != 0:
> if mode == "SELECT":
> try:
> retval=cur.fetchone()
> except:
> retval=''
> if mode == "SELECT_ALL":
> try:
> retval=cur.fetchall()
> except:
> retval=''
> cur.close()
> db.close()
> return retval
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "cherrypy-users" group.
To post to this group, send email to cherrypy-usersgooglegroups.com
To unsubscribe from this group, send email to
cherrypy-users-unsubscribegooglegroups.com
For more options, visit this group at h
ttp://groups.google.com/group/cherrypy-users?hl=en
-~----------~----~----~----~------~----~------~--~---
|
