Where is this code? If it is in a web2py model or controller you do not need to close. If it is a a script that you run with web2py.py ... -R script.py than you need to close it. You should close it with
my_db.commit()
What is the reason for this?
res = my_db.executesql("SELECT * FROM information_schema.tables where
table_name='summary';");
if res:
On Friday, 16 August 2013 13:44:26 UTC-5, ewon...@gmail.com wrote:
>
> I have a list of legacy postgres databases (the list is variable) and I
> want to connect to each of them and pull in data from their summary tables
> into an SQLite table. I have the list of databases in my controller and
> there I am looping through them and calling a function I put in a module.
> This is my module function (please ignore funky spacing issues that came
> about when I pasted into the code block):
>
> #opens the postgres database named 'pg_db' and queries its Summary table
> to populate the
>
>
> #sqlite local_db master table
>
>
>
> def populate_master_table(pg_db, local_db):
>
>
>
> my_db = DAL('postgres://dbuser:dbu...@xxx.xx.xxx.xx/' + pg_db,pool_size
> =2, migrate=False, auto_import=False)
>
>
>
>
>
>
> res = my_db.executesql("SELECT * FROM information_schema.tables where
> table_name='summary';");
>
>
>
>
>
> if res: #the db has a summary table
>
>
>
> my_db.define_table('summary',
>
>
>
> Field('record'),
>
>
>
> Field('program'),
>
>
>
> Field('platform'))
>
>
>
>
>
>
> for row in my_db().select(my_db.summary.ALL):
>
>
>
> local_db.master.insert( record=row.record,
>
>
>
> program=row.program,
>
>
>
> platform=row.platform)
>
>
>
>
>
>
> my_db._adapter.connection.close()
>
> I believe the way I am closing the db connection is causing issues. When
> it gets to the first legacy database that has a summary table, I get an
> InterfaceError: connection already closed and it seems like it's for the
> my_db().select statement. The reason why I'm trying to close the
> connection is because without it, I was getting an error from postgres that
> the connection limit was exceeded for non-superusers. My limit is 100 so I
> don't want to raise the limit, I want to close the connection after I pull
> the data from the summary table. I tried a plain old my_db.close() but
> that did not do the trick--still exceeded the connection limit.
>
> How should I close the connection? Or is there a better method of pulling
> data from multiple legacy postgres databases?
>
--
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
