def cli():
cli_docs = """Box Exporter: Take that data and put it in a box.
Usage:
boxex <filepath> <filename> [--url=<url>]
boxex (-h | --help)
Options:
-h --help Show this screen
--url=<url> The database URL to use. Defaults to $DATABASE_URL
Notes:
- While you may specify a database connection string with --url, box-exporter
will automatically default to the value of $DATABASE_URL, if available.
- filepath is intended to be the path of a SQL file.
- All box credentials are set via environmental variables. Make sure you have
the following environment variables set or a KeyError will occur:
$BOX_CLIENT_ID
$BOX_CLIENT_SECRET
$BOX_ENTERPRISE_ID
$BOX_RSA_PRIVATE_KEY_PASS
$BOX_RSA_PRIVATE_KEY_PATH
$BOX_JWT_KEY_ID
$BOX_FOLDER_ID
"""
arguments = docopt(cli_docs)
# Create the database object
db = Database(arguments['--url'])
# Authenticate the box client
client = BoxClient()
queryfile = arguments['<filepath>']
filename = arguments['<filename>']
# Execute the query, if it is found.
if os.path.isfile(queryfile):
rows = db.query_file(queryfile).all()
if rows:
# grab the first row and use keys as fieldnames
fieldnames = rows[0].as_dict().keys()
client.upload(to_csv(fieldnames, rows), filename)
else:
print('There was no query file that was found')
from records import Database
from json import load
reviews = load(open('./reviews.json'))
db = Database('postgres:///pitchfork-reviews')
for review in reviews:
album = review['album']
album_id = db.query_file('./insert_album.sql', **album).first().id
db.query_file("insert_review.sql", album_id=album_id)
from records import Database
from json import load
reviews = load(open('./reviews.json'))
db = Database('postgres:///pitchfork-reviews')
i = 0
for review in reviews:
album = review['album']
album_id = db.query_file('./insert_album.sql', **album).first().id
reviewer = review['author']
reviewer_id = db.query_file('./insert_reviewer.sql', **reviewer).first().id
idn = review['id']
url = review['url']
date = review['date']
year = date['year']
month = date['month']
day = date['day']
release_date = f'{year}-{month}-{day}'
score = review['score']
ibm = review['is_best_new_music']
i += 1
print(i)
db.query_file("insert_review.sql",
url=url,
reviewerId=reviewer_id,
albumId=album_id,
release_date=release_date,
score=score,