Hello,
I am using web2py on heroku. I have two installed applications using two
different postgres databases. I am trying to copy all the data from one of
the databases to the other. One of the tables has an upload field which
uses s3 as the filesystem.
I don't want to duplicate the files, I just want the new database to know
where the files are and be able to download them from s3. I tried just
passing whatever came from db1 to db2 and that didn't error, but then when
I download the file it is empty (they are gzips of csv files). I also tried
passing a file handle that I got from s3 using boto, but that complained
and I also thought that it might duplicate the file on s3, which I don't
want.
What is a good way to do this?
Here is my table definition for application and db 1:
from datetime import datetime
import fs.s3fs, os
myfs =
fs.s3fs.S3FS(os.environ['AWS_S3_BUCKET_NAME'],os.environ['AWS_S3_BUCKET_PREFIX'],os.environ['AWS_S3_KEY'],
os.environ['AWS_S3_SECRET'])
db.define_table('log_files',
Field('das_id'),
Field('device_id'),
Field('das_id_dev_id'),
Field('log_filename'),
Field('log_file', 'upload'),
Field('date_added','datetime'),
)
db.log_files.log_file.uploadfs=myfs
And here is my table definition for app and db 2 (it's basically the same)
from datetime import datetime
import fs.s3fs, os
myfs =
fs.s3fs.S3FS(os.environ['AWS_S3_BUCKET_NAME'],os.environ['AWS_S3_BUCKET_PREFIX'],os.environ['AWS_S3_KEY'],
os.environ['AWS_S3_SECRET'])
db.define_table('log_files',
Field('das_id' ,type='string'),
Field('device_id' ,type='string'),
Field('das_id_dev_id' ,type='string'),
Field('log_filename' ,type='string'),
Field('log_file' ,type='upload'),
Field('date_added' ,type='datetime'),
)
db.log_files.log_file.uploadfs=myfs
And here is the python script that I've been playing around with to try and
get what I want:
import os, psycopg2, psycopg2.extras, boto
from psycopg2.extensions import AsIs
table_name="log_files"
params=dict(table_name=AsIs(table_name))
# Open the connections to each database
conn_old = psycopg2.connect(os.environ['HEROKU_POSTGRESQL_ONYX_URL'])
conn_new = psycopg2.connect(os.environ['HEROKU_POSTGRESQL_BROWN_URL'])
## Create Curstors for each database
cur_old = conn_old.cursor(cursor_factory = psycopg2.extras.DictCursor)
cur_new = conn_new.cursor()
## Select files from the source db and fetch them.
cur_old.execute("SELECT * FROM %(table_name)s WHERE id = 4451744", params)
bucket=connect_to_s3()
for rec_old in cur_old:
columns = rec_old.keys()
values = rec_old
insert_statement = ('INSERT INTO %(table_name)s ( das_id, device_id,
das_id_dev_id, log_filename, log_file, date_added )'
'VALUES ( %(das_id)s, %(device_id)s, %(das_id_dev_id)s, %(log_filename)s,
%(log_file)s, %(date_added)s )'
)
params=dict(
table_name = AsIs(table_name),
das_id = values['das_id'],
device_id = values['device_id'],
das_id_dev_id = values['das_id_dev_id'],
log_filename = values['log_filename'],
date_added = values['date_added'],
## Help!
log_file = get_key(bucket, values['log_file']),
)
try:
cur_new.execute(insert_statement, params)
conn_new.commit()
except psycopg2.IntegrityError:
conn_new.rollback()
#close new connection
cur_new.close()
conn_new.close()
# close old connection
cur_old.close()
conn_old.close()
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
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/d/optout.
