Webware for Python

Chris AtLee wrote:
> Hey there,
> 
> The code that I'm writing makes extensive use of MySQL connections.  My =
> first implementation had one connection per class, but then I realized =
> that this was probably not a good idea since if multiple people were =
> viewing the page at one time, I could run into syncronization problems. =
> (My understanding is that the Page classes are instatiated once per =
> server, not once per user.)  Then I thought about putting the database =
> connection into the session, since that is user-specific.  =
> Unfortunately, this has the problem that MySQL connection objects aren't =
> picklable, so sweepSessions dies.
> 
> What can I do about this?  Can I mark some parts of the session =
> non-pickleable (why is it pickling the sessions anyway?)
1.) You can use own implementation of SessionStore.  If you use
ThreadedAppServer or AsyncThreadedAppserver, which are persistent and
use SessionMemoryStore, simply strip code which store Session to file.
But if you restart AppServer all session information are lost ( like in
Java EJB Statefull Session Bean :-).

2.) Second solution is own implementation Session. Sessions are stored
by SessionStore by using python module pickle. In documentation in that
module ( pickle ) You can read, how make own policy for persistent
instaces ( see using methods __getstate__ () and __setstate__())

> Or is there a better way to handle per-user database connections?
Realy need one connection per user ? Each user is logged into database
with own login-name and password ? 
If all users, use same login-name, better solution is realizing
connection pooling. I realize this for Informix. 
Some changes to class Application and Transaction are needed.

		vlk

-- 
Mgr. Vladimir Kralik
Softip a.s.
Zvolenska cesta 19
974 01 Banska Bystrica
Slovak Republic

tel. +421 88 4358 176

Didn't someone like Geoff have a DB connection pool object running around?

Also, is this something that could be done through cans, Jay?

-Chuck


At 09:58 PM 11/21/00 -0500, Chris AtLee wrote:
>Hey there,
>
>The code that I'm writing makes extensive use of MySQL connections.  My 
>first implementation had one connection per class, but then I realized 
>that this was probably not a good idea since if multiple people were 
>viewing the page at one time, I could run into syncronization problems. 
>(My understanding is that the Page classes are instatiated once per 
>server, not once per user.)  Then I thought about putting the database 
>connection into the session, since that is user-specific.  Unfortunately, 
>this has the problem that MySQL connection objects aren't picklable, so 
>sweepSessions dies.
>
>What can I do about this?  Can I mark some parts of the session 
>non-pickleable (why is it pickling the sessions anyway?)  Or is there a 
>better way to handle per-user database connections?
>
>Thanks for your help,
>Chris

There was some discussion of MySQL connection pooling on the mailing list a
while ago.  Check out the mailing list archives:

http://www.geocrawler.com/search/?config=3854&words=database+connection+pool&SUBMIT=Search+webware-discuss

I think the idea is that you'd write a ConnectionPool class which would handle
all the connection pooling, and you'd construct a single global ConnectionPool
instance in an __init__.py and store it in the Application object, then all
servlets would use this global instance of the ConnectionPool through the
Application object.  We talked about including this as a standard part of
WebWare, but it hasn't happened yet.  Maybe someone with a better grasp of the
details can elaborate.

Chuck Esterbrook wrote:

> Didn't someone like Geoff have a DB connection pool object running around?

I'm using ADO connection pooling on Windows, which works great but is
completely different from what you'd do for MySQL.

> Also, is this something that could be done through cans, Jay?
>
> -Chuck

--


- Geoff Talvola
  Parlance Corporation
  gtalvola@...

At 09:58 PM 11/21/00 -0500, Chris AtLee wrote:
>Hey there,
>
>The code that I'm writing makes extensive use of MySQL connections.  My 
>first implementation had one connection per class, but then I realized 
>that this was probably not a good idea since if multiple people were 
>viewing the page at one time, I could run into syncronization problems. 
>(My understanding is that the Page classes are instatiated once per 
>server, not once per user.)  Then I thought about putting the database 
>connection into the session, since that is user-specific.  Unfortunately, 
>this has the problem that MySQL connection objects aren't picklable, so 
>sweepSessions dies.
>
>What can I do about this?  Can I mark some parts of the session 
>non-pickleable (why is it pickling the sessions anyway?)  Or is there a 
>better way to handle per-user database connections?
>

Sessions get pickled as a means of backup, so that if the app crashes, or 
is brought down on purpose, the next freshly started app can load the 
sessions and continue like nothing happened.

Gee, we thought this was a feature.  :-)


The quickest, but hackiest way would be to open 
WebKit/SessionMemoryStory.py and comment out the implementation of 
storeAllSessions() (and put a 'pass' in there). You might also want to 
remove all files in WebKit/Sessions/.

I think that was basically what Vladimir was saying.


You can also accomplish the same thing without hacking on 
SessionMemoryStory. You can create a subclass of SessionMemoryStore and 
override storeAllSessions() as 'pass'. If your class name is 
"SessionAtLeeStore", then you'll need to set Application.config's 
'SessionStore' setting to 'AtLee' and add SessionAtLeeStore to the list of 
classes found in Application.py (search for SessionMemoryStore and you'll 
find it).

We should eliminate that last part since we could load the module with the 
class name instead of requiring the list of available classes appearing in 
Application.py (e.g., hard coding). Then you could really do this without 
modifying the original source at all.


And finally, if you wanted one connection per app (instead of per user), 
you could do this in SitePage:

     def __init__(self):
         self._conn = None

     def conn(self):
         if self._conn is None:
             app = self.application()
             if not hasattr(app, 'conn'):
                 app.conn = MySQLdb(db='whatever')
             self._conn = app.conn
         return self._conn


Now all pages can say self.conn() to get the connection, but it's always 
the same instance. The above method still caches the reference per Page for 
speed.

This assumes all your pages ultimately inherit from some common page class, 
often called SitePage. This is a really good practice to follow as you can 
put utility methods and even common look-and-feel things in SitePage as 
your site grows.

In a future WebKit, it will be easier to create custom subclasses of 
Application, which would make the above code cleaner. Valdimir submitted a 
patch for this recently, but I haven't had a chance to look at it yet.


Hope that helped,

-Chuck

ConnectionPools sound like a good solution, thanks.  It puts a bound on the
number of database connections, which is a good thing :)  So is the idea
that you grab a connection from the pool whenever you need it?

Cheers,
Chris

BTW, how's FormKit coming along?

----- Original Message -----
From: "Geoff Talvola" <gtalvola@...>
To: <webware-discuss@...>
Sent: Wednesday, November 22, 2000 10:59 AM
Subject: Re: [Webware-discuss] Non-pickleable objects in session


> There was some discussion of MySQL connection pooling on the mailing list
a
> while ago.  Check out the mailing list archives:
>
>
http://www.geocrawler.com/search/?config=3854&words=database+connection+pool
&SUBMIT=Search+webware-discuss
>
> I think the idea is that you'd write a ConnectionPool class which would
handle
> all the connection pooling, and you'd construct a single global
ConnectionPool
> instance in an __init__.py and store it in the Application object, then
all
> servlets would use this global instance of the ConnectionPool through the
> Application object.  We talked about including this as a standard part of
> WebWare, but it hasn't happened yet.  Maybe someone with a better grasp of
the
> details can elaborate.
>
> Chuck Esterbrook wrote:
>
> > Didn't someone like Geoff have a DB connection pool object running
around?
>
> I'm using ADO connection pooling on Windows, which works great but is
> completely different from what you'd do for MySQL.
>
> > Also, is this something that could be done through cans, Jay?
> >
> > -Chuck
>
> --
>
>
> - Geoff Talvola
>   Parlance Corporation
>   gtalvola@...
>
> _______________________________________________
> Webware-discuss mailing list
> Webware-discuss@...
> http://lists.sourceforge.net/mailman/listinfo/webware-discuss

One of the things I was worried about in using one connection per
application is that things like last_insert_id() aren't guaranteed to work
anymore.  Since multiple people could be sharing the same connection, if
they both insert something at the same time, the results of last_insert_id()
are not predictable.  One connection per user solves this problem (I
think...Hopefully MySQL handles this properly), but also one connection per
transaction works (using ConnectionPools).

I'm going to try out the ConnectionPools idea, but wouldn't writing a
wrapper class for the db connection work (to solve the sweepSession
problem)?  One that overrides the methods that pickle calls to get state
information?

Cheers,
Chris
----- Original Message -----
From: "Chuck Esterbrook" <echuck@...>
To: <webware-discuss@...>
Sent: Wednesday, November 22, 2000 11:25 AM
Subject: Re: [Webware-discuss] Non-pickleable objects in session


> At 09:58 PM 11/21/00 -0500, Chris AtLee wrote:
> >Hey there,
> >
> >The code that I'm writing makes extensive use of MySQL connections.  My
> >first implementation had one connection per class, but then I realized
> >that this was probably not a good idea since if multiple people were
> >viewing the page at one time, I could run into syncronization problems.
> >(My understanding is that the Page classes are instatiated once per
> >server, not once per user.)  Then I thought about putting the database
> >connection into the session, since that is user-specific.  Unfortunately,
> >this has the problem that MySQL connection objects aren't picklable, so
> >sweepSessions dies.
> >
> >What can I do about this?  Can I mark some parts of the session
> >non-pickleable (why is it pickling the sessions anyway?)  Or is there a
> >better way to handle per-user database connections?
> >
>
> Sessions get pickled as a means of backup, so that if the app crashes, or
> is brought down on purpose, the next freshly started app can load the
> sessions and continue like nothing happened.
>
> Gee, we thought this was a feature.  :-)
>
>
> The quickest, but hackiest way would be to open
> WebKit/SessionMemoryStory.py and comment out the implementation of
> storeAllSessions() (and put a 'pass' in there). You might also want to
> remove all files in WebKit/Sessions/.
>
> I think that was basically what Vladimir was saying.
>
>
> You can also accomplish the same thing without hacking on
> SessionMemoryStory. You can create a subclass of SessionMemoryStore and
> override storeAllSessions() as 'pass'. If your class name is
> "SessionAtLeeStore", then you'll need to set Application.config's
> 'SessionStore' setting to 'AtLee' and add SessionAtLeeStore to the list of
> classes found in Application.py (search for SessionMemoryStore and you'll
> find it).
>
> We should eliminate that last part since we could load the module with the
> class name instead of requiring the list of available classes appearing in
> Application.py (e.g., hard coding). Then you could really do this without
> modifying the original source at all.
>
>
> And finally, if you wanted one connection per app (instead of per user),
> you could do this in SitePage:
>
>      def __init__(self):
>          self._conn = None
>
>      def conn(self):
>          if self._conn is None:
>              app = self.application()
>              if not hasattr(app, 'conn'):
>                  app.conn = MySQLdb(db='whatever')
>              self._conn = app.conn
>          return self._conn
>
>
> Now all pages can say self.conn() to get the connection, but it's always
> the same instance. The above method still caches the reference per Page
for
> speed.
>
> This assumes all your pages ultimately inherit from some common page
class,
> often called SitePage. This is a really good practice to follow as you can
> put utility methods and even common look-and-feel things in SitePage as
> your site grows.
>
> In a future WebKit, it will be easier to create custom subclasses of
> Application, which would make the above code cleaner. Valdimir submitted a
> patch for this recently, but I haven't had a chance to look at it yet.
>
>
> Hope that helped,
>
> -Chuck
>
> _______________________________________________
> Webware-discuss mailing list
> Webware-discuss@...
> http://lists.sourceforge.net/mailman/listinfo/webware-discuss

Chris AtLee wrote:

> ConnectionPools sound like a good solution, thanks.  It puts a bound on the
> number of database connections, which is a good thing :)  So is the idea
> that you grab a connection from the pool whenever you need it?

Right.

If MySQL is fully thread-safe, meaning you can safely use the same connection
from different threads, then why even have a connection pool -- you could just
use one connection for the entire application, as Chuck suggested.  Note: I
don't know squat about MySQL so I don't know if that would work -- someone who
knows please chime in. If it doesn't work, then use a ConnectionPool-type
implementation like the one in the mailing list archives.

I also like Chuck's idea of putting this into a "conn" method of SitePage that
initializes the connection (or connection pool) the first time it's called.
That's what I've done with ADO.  I also took it a step further and wrote a
"recordset" function that takes a SQL string and returns a recordset object
based on executing that query (a recordset is roughly equivalent to a MySQL
cursor).  Also, instead of putting it into SitePage I put it into a separate
mixin class that I inherit along with SitePage.  That's just a matter of
personal preference, I guess.

--


- Geoff Talvola
  Parlance Corporation
  gtalvola@...

At 11:37 AM 11/22/00 -0500, Chris AtLee wrote:
>BTW, how's FormKit coming along?

Gee, I was afraid you were going to ask that question. :-)

I got side swiped again with MiddleKit and other things. Let me clean up 
any minor problems and post a copy to anon ftp. I still don't want to 
upload to CVS until a few basic design issues pan out.

I can get this by sometime tomorrow.

-Chuck

At 11:46 AM 11/22/00 -0500, Chris AtLee wrote:
>I'm going to try out the ConnectionPools idea, but wouldn't writing a
>wrapper class for the db connection work (to solve the sweepSession
>problem)?  One that overrides the methods that pickle calls to get state
>information?

I believe that would work as well. I don't know as much about pickle as I 
should, but you might also be able to customize session to not pickle the 
connection, but to recreate it when it is unpickled.

Let us know if you do anything in this area.

-Chuck

'cause I'm a nice guy (and despite my being almost nearly mute for the last
little while), here's the DBPool class, originally envisioned by a bunch of
people, implemented crappily by myself and fixed by Geoff.

I think Jay's used it.

Err. Is this part of the distribution? Can it be? If it is, mea culpa for
duplicating it on the list.

In any case, using it is pretty simple. Just add an instance of it to somewhere
(Application makes the most sense), and change your database connections

On Wed, Nov 22, 2000 at 11:37:52AM -0500, Chris AtLee wrote:
> ConnectionPools sound like a good solution, thanks.  It puts a bound on the
> number of database connections, which is a good thing :)  So is the idea
> that you grab a connection from the pool whenever you need it?
> 
> Cheers,
> Chris
> 
> BTW, how's FormKit coming along?

-- 
Dan Green
"..so watch out, Laszlo Panaflax!"
  - Troy McClure

Just a quick fix:

line 73 should read
self.queue.put(con)
instead of
self.queue.put(con.db)

I discovered this after finding out that I'm not using 0.2 or later MySQLdb
packages, so threadsafety is only 1, not 2.  Ach.

Chris
----- Original Message -----
From: "Dan Green" <daniel.green@...>
To: "Chris AtLee" <catlee@...>
Cc: <webware-discuss@...>
Sent: Wednesday, November 22, 2000 5:46 PM
Subject: Re: [Webware-discuss] Non-pickleable objects in session


> Because I'm an idiot, and typed "y" when I wanted to postpone, here's that
> message, in full. I've been writing non-Python too much recently. My brain
> is strating to atrophy...
>
> ---
>
> 'cause I'm a nice guy (and despite my being almost nearly mute for the
last
> little while), here's the DBPool class, originally envisioned by a bunch
of
> people, implemented crappily by myself and fixed by Geoff.
>
> I think Jay's used it.
>
> Err. Is this part of the distribution? Can it be? If it is, mea culpa for
> duplicating it on the list.
>
> In any case, using it is pretty simple. Just add an instance of it to
somewhere
> (Application makes the most sense), and instead of using the connect
method of
> your database module, do this:
>
> conn = pool.getConnection()
>
> conn will behave exactly like your database module, except that close()ing
it
> will return it to the pool. It will automatically return to the pool when
> deleted (thanks to Geoff).
>
> There's a wee little example at the end of the file.
>
> Incidentally, howdy y'all. I'll be more active in the list and in annoying
Chuck
> shortly. :)
>
> On Wed, Nov 22, 2000 at 11:37:52AM -0500, Chris AtLee wrote:
> > ConnectionPools sound like a good solution, thanks.  It puts a bound on
the
> > number of database connections, which is a good thing :)  So is the idea
> > that you grab a connection from the pool whenever you need it?
> >
> > Cheers,
> > Chris
> >
> > BTW, how's FormKit coming along?
>
> --
> Dan Green
> "..so watch out, Laszlo Panaflax!"
>   - Troy McClure
>

And line 16 should read
self.pool.returnConnection(self.db)
instead of
self.pool.returnConnection(self)

I think....At least for using the Queue'd version, it does, otherwise the
next access to that PooledConnection fails since you just set db = None.

Chris

----- Original Message -----
From: "Chris AtLee" <catlee@...>
To: <webware-discuss@...>
Sent: Wednesday, November 22, 2000 11:26 PM
Subject: Re: [Webware-discuss] Non-pickleable objects in session


> Just a quick fix:
>
> line 73 should read
> self.queue.put(con)
> instead of
> self.queue.put(con.db)
>
> I discovered this after finding out that I'm not using 0.2 or later
MySQLdb
> packages, so threadsafety is only 1, not 2.  Ach.
>
> Chris
> ----- Original Message -----
> From: "Dan Green" <daniel.green@...>
> To: "Chris AtLee" <catlee@...>
> Cc: <webware-discuss@...>
> Sent: Wednesday, November 22, 2000 5:46 PM
> Subject: Re: [Webware-discuss] Non-pickleable objects in session
>
>
> > Because I'm an idiot, and typed "y" when I wanted to postpone, here's
that
> > message, in full. I've been writing non-Python too much recently. My
brain
> > is strating to atrophy...
> >
> > ---
> >
> > 'cause I'm a nice guy (and despite my being almost nearly mute for the
> last
> > little while), here's the DBPool class, originally envisioned by a bunch
> of
> > people, implemented crappily by myself and fixed by Geoff.
> >
> > I think Jay's used it.
> >
> > Err. Is this part of the distribution? Can it be? If it is, mea culpa
for
> > duplicating it on the list.
> >
> > In any case, using it is pretty simple. Just add an instance of it to
> somewhere
> > (Application makes the most sense), and instead of using the connect
> method of
> > your database module, do this:
> >
> > conn = pool.getConnection()
> >
> > conn will behave exactly like your database module, except that
close()ing
> it
> > will return it to the pool. It will automatically return to the pool
when
> > deleted (thanks to Geoff).
> >
> > There's a wee little example at the end of the file.
> >
> > Incidentally, howdy y'all. I'll be more active in the list and in
annoying
> Chuck
> > shortly. :)
> >
> > On Wed, Nov 22, 2000 at 11:37:52AM -0500, Chris AtLee wrote:
> > > ConnectionPools sound like a good solution, thanks.  It puts a bound
on
> the
> > > number of database connections, which is a good thing :)  So is the
idea
> > > that you grab a connection from the pool whenever you need it?
> > >
> > > Cheers,
> > > Chris
> > >
> > > BTW, how's FormKit coming along?
> >
> > --
> > Dan Green
> > "..so watch out, Laszlo Panaflax!"
> >   - Troy McClure
> >
>
> _______________________________________________
> Webware-discuss mailing list
> Webware-discuss@...
> http://lists.sourceforge.net/mailman/listinfo/webware-discuss

i think you need a lock/mutex/semaphore/etc around, the
_threadsafe_getConnection, cause it is not threadsafe. other than that,
very cool.

scenario
two threads try and getConnections, thread one
gets to the first line of getConnection and has its connection
allocated.

thread one gets yanked off the cpu, thread two comes along and does the
same.

both threads now have refs to the same connection before the
incrementing of the critical value nextConn.



cheers

kapil



Dan Green wrote:
> 
> Because I'm an idiot, and typed "y" when I wanted to postpone, here's that
> message, in full. I've been writing non-Python too much recently. My brain
> is strating to atrophy...
> 
> ---
> 
> 'cause I'm a nice guy (and despite my being almost nearly mute for the last
> little while), here's the DBPool class, originally envisioned by a bunch of
> people, implemented crappily by myself and fixed by Geoff.
> 
> I think Jay's used it.
> 
> Err. Is this part of the distribution? Can it be? If it is, mea culpa for
> duplicating it on the list.
> 
> In any case, using it is pretty simple. Just add an instance of it to somewhere
> (Application makes the most sense), and instead of using the connect method of
> your database module, do this:
> 
> conn = pool.getConnection()
> 
> conn will behave exactly like your database module, except that close()ing it
> will return it to the pool. It will automatically return to the pool when
> deleted (thanks to Geoff).
> 
> There's a wee little example at the end of the file.
> 
> Incidentally, howdy y'all. I'll be more active in the list and in annoying Chuck
> shortly. :)
> 
> On Wed, Nov 22, 2000 at 11:37:52AM -0500, Chris AtLee wrote:
> > ConnectionPools sound like a good solution, thanks.  It puts a bound on the
> > number of database connections, which is a good thing :)  So is the idea
> > that you grab a connection from the pool whenever you need it?
> >
> > Cheers,
> > Chris
> >
> > BTW, how's FormKit coming along?
> 
> --
> Dan Green
> "..so watch out, Laszlo Panaflax!"
>   - Troy McClure
> 
>   ------------------------------------------------------------------------
> 
>    DBPool.pyName: DBPool.py
>             Type: Plain Text (text/plain)

actually the lock around getConnections (in ts version) isn't nesc from
a functional standpoint as the two threads should be able to share the
connection at dbi level 2, but it is a semantic error that illustrates
that the class isn't ts.

kapil

Ender wrote:
> 
> i think you need a lock/mutex/semaphore/etc around, the
> _threadsafe_getConnection, cause it is not threadsafe. other than that,
> very cool.
> 
> scenario
> two threads try and getConnections, thread one
> gets to the first line of getConnection and has its connection
> allocated.
> 
> thread one gets yanked off the cpu, thread two comes along and does the
> same.
> 
> both threads now have refs to the same connection before the
> incrementing of the critical value nextConn.
> 
> cheers
> 
> kapil
> 
> Dan Green wrote:
> >
> > Because I'm an idiot, and typed "y" when I wanted to postpone, here's that
> > message, in full. I've been writing non-Python too much recently. My brain
> > is strating to atrophy...
> >
> > ---
> >
> > 'cause I'm a nice guy (and despite my being almost nearly mute for the last
> > little while), here's the DBPool class, originally envisioned by a bunch of
> > people, implemented crappily by myself and fixed by Geoff.
> >
> > I think Jay's used it.
> >
> > Err. Is this part of the distribution? Can it be? If it is, mea culpa for
> > duplicating it on the list.
> >
> > In any case, using it is pretty simple. Just add an instance of it to somewhere
> > (Application makes the most sense), and instead of using the connect method of
> > your database module, do this:
> >
> > conn = pool.getConnection()
> >
> > conn will behave exactly like your database module, except that close()ing it
> > will return it to the pool. It will automatically return to the pool when
> > deleted (thanks to Geoff).
> >
> > There's a wee little example at the end of the file.
> >
> > Incidentally, howdy y'all. I'll be more active in the list and in annoying Chuck
> > shortly. :)
> >
> > On Wed, Nov 22, 2000 at 11:37:52AM -0500, Chris AtLee wrote:
> > > ConnectionPools sound like a good solution, thanks.  It puts a bound on the
> > > number of database connections, which is a good thing :)  So is the idea
> > > that you grab a connection from the pool whenever you need it?
> > >
> > > Cheers,
> > > Chris
> > >
> > > BTW, how's FormKit coming along?
> >
> > --
> > Dan Green
> > "..so watch out, Laszlo Panaflax!"
> >   - Troy McClure
> >
> >   ------------------------------------------------------------------------
> >
> >    DBPool.pyName: DBPool.py
> >             Type: Plain Text (text/plain)
> _______________________________________________
> Webware-discuss mailing list
> Webware-discuss@...
> http://lists.sourceforge.net/mailman/listinfo/webware-discuss