|
Julián Ciccalè
jciccale at mac.com
Fri Feb 13 18:30:10 EST 2004
I do the following with psycopg: In my app, at module level, a connection is opened using a specified user and password and dbname. So within your mod_python app, you could use a module that opens a connection at module level and leaves it there. Suppose your module is called "db.py" (something like this): import psycopg as pg con = db.connect( "username=testuser password=testpass dbname=testdb" ) Then you could use it like this: import db def my_func(): cur = db.cursor() cur.execute( "select col1, col2 from table" ) t = cur.dictfetchall() for row in t: print "Col1 is " + row[ "col1" ] print "Col2 is " + row[ "col2" ] psycopg implements DB API 2.0 of python so you could you read the docs at python.org on DB API 2.0 specs. The connection pooling and everything is implemented in C, so you would have to look there to see the code. The connection gets released when apache is restarted. Julian -----Original Message----- From: Neal Timm [mailto:ntimm at intellithreat.com] Sent: viernes, 13 de febrero de 2004 18:07 To: 'Julián Ciccalè' Subject: RE: [mod_python] Mod Python Issue? Yea I just installed psycopg will just have to port a lot of code to it. Can you show me how it does the pooling is it a script or variable I just did a couple of queries with it and had the same issue of it leaving a posgres instance open. I couldn't find a lot of docs on it's usage. -----Original Message----- From: Julián Ciccalè [mailto:jciccale at mac.com] Sent: Friday, February 13, 2004 9:45 AM To: 'David Fraser'; 'Neal Timm' Cc: 'ModPython mail list' Subject: RE: [mod_python] Mod Python Issue? Neal, Im using the psycopg module to connect to postgres, that does connection pooling itself and is thread safe. I'm running apache 2.0.48 and mod_python 3.1.2b and works great. You can find psycopg at www.initd.org . Hope it helps. Julian -----Original Message----- From: mod_python-bounces at modpython.org [mailto:mod_python-bounces at modpython.org] On Behalf Of David Fraser Sent: viernes, 13 de febrero de 2004 16:17 To: Neal Timm Cc: ModPython mail list Subject: Re: [mod_python] Mod Python Issue? Neal The danger with closing the connections is that you may have multithreaded code trying to use the connection you have closed. Generally you want to keep some connections open, because it is time-consuming opening them, and to do so for each request makes them slow. How many apache processes are you getting, and how many database connections? It should be the same number... But I'm not sure I'm answering you, maybe somebody else on the list can help... David Neal Timm wrote: >Shouldn't these > postgres: varlog varlog [local] idle >Die when the apache connection is dropped. >The only way I can get rid of them is to restart apache or postgres. >And ventually when the max db connections is hit it doesn't except any >more cause of all these idle process. I have tried using a db.close >statement but then I have come connection issues saying connection is >not valid. Is there any other way I can close these or reuse them. >Varweb calls the db with db =pg.DB() > >-----Original Message----- >From: David Fraser [mailto:davidf at sjsoft.com] >Sent: Friday, February 13, 2004 7:55 AM >To: ntimm >Cc: ModPython mail list >Subject: Re: [mod_python] Mod Python Issue? > > >ntimm wrote: > > > >>Are you saying import the module with pythonimport command? Her is a >>piece of one of the scripts basically all scripts look like this except >>different query and results. I have looked at sql relay but just >>don't want to change all our code to support it. >>I have tried the pythonImport command but didn't know the correct >>syntax. >> >> >> >> >I don't think the PythonImport command is relevant here, your problem >isn't with importing modules but connection caching... > > > >>The varweb module contains a lot of the session handling. >> >> >> >> >How many database connections are you getting? I presume you are >running > >this under Unix? >Are you aware that you get one connection for each Apache process? It >may be that you have 20 or more processes, that is why you are seeing >multiple connections > >David > > > >>import sys, shelve, random, pg, md5, time, string sys.path.insert(0, >>"/home/varlog/lib") from mod_python import apache >>#db = pg.connect('varlog','localhost') >> >> >>from varweb import * > > >>def default(req,sid): >> sid = '%s'%(sid) >> if check_session(sid) == 1: >> >>[user,auth_level,sla_level,org_id,host_id,ip]=get_sess_info(sid) >> orgid = '%s' %(org_id) >> hid = '%s' %(host_id) >> vquery = "select * from vuln_scan where o_id = %s and >>h_id = %s order by level desc limit 50;" %(orgid,hid) >> output = vuln(sid,vquery) >> else: >> output = var_login_redirect() >> return output >> >>def vuln(sid,query): >> table = [''] >> for data in db.query(query).dictresult(): >> Hid = data['h_id'] >> Oid = data['o_id'] >> Id = data['id'] >> Ip = data['ip'] >> Port = data['port'] >> Nid = data['nessus_id'] >> Adv = data['advisory'] >> Lev = data['level'] >> Date = data['time'] >> dat = '<a >>href="/text.py/default?sid=%s&id=%s">Details</a>' %(sid,Id) >> False = data['is_false'] >> if False == 0: >> False = '<a >>href="/vulnerabilities.py/false?sid=%s&id=%s">No</a>' %(sid,Id) >> else: >> False = 'Yes' >> html = >>sub_table_r([Hid,Oid,Id,Ip,Port,Nid,Adv,Lev,Date,dat,False]) >> table.append(html) >> table_html = string.join(table,'\n') >> output = >>sub_pattern_mult('/var/www/ids-events.com/templates/vuln.html', >>table_html,sid) >> return output >> >>-----Original Message----- >>From: David Fraser [mailto:davidf at sjsoft.com] >>Sent: Friday, February 13, 2004 12:47 AM >>To: ntimm >>Cc: mod_python at modpython.org >>Subject: Re: [mod_python] Mod Python Issue? >> >>ntimm wrote: >> >> >> >> >> >>>I am running mod python 3.0.4 with apache 2.0.47 connecting to a >>>postgresql database. The only issue I am having is that each time a >>>mod python script is called that has a db query in all just select >>>statements if leaves a instance of postgres running and eventually my >>>max connection to postgres is hit. I have tried the db.close >>>statements but then that cause sporadic errors with the connection >>>not >>> >>> >>> >>> >> >> >> >> >>>being valid. The only way I have found to close the postgres instance >>>is either restart apache or postgres. I there some mod python >>>variable >>> >>> >>> >>> >> >> >> >> >>>that I can use or any type of connection pooling Or maybe a apache >>>config option I have tried messing with the apache config but with no >>>luck. >>> >>>I have tried to use PythonImport variable but couldn't get it to work >>>with the pg module at all. >>> >>>Any help would be appreciated. >>> >>>Thanks, >>> >>> >>> >>> >>> >>If you post some of the relevant code it will be easier to see what >>the >> >> > > > >>problem is... But basically if you import the pg module, it will not >>be >> >> > > > >>re-imported for each call. >>So you want to set up a global variable that is the connection, then >>just create a new cursor each time the script is run, and close the >>cursor at the end. Running under Unix, you will still get multiple >>connections because there are multiple Python Interpreters - one for >>each Apache process - but there will be a limit. >>Not sure about the connection pooling, but have a look at >>http://sqlrelay.sourceforge.net/ - I haven't used it, but it seems to >> >> >be > > >>the kind of thing you want... >> >>David >> >> >> >> >> > > > > _______________________________________________ Mod_python mailing list Mod_python at modpython.org http://mailman.modpython.org/mailman/listinfo/mod_python
|