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:[email protected]/' + 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 [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
