Ejemplo n.º 1
0
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,