taskopk.blogg.se - Export json to csv python

Now that we have our final query, we need to build a csv.DictWriter and iterate through the rows that are returned from our query. This code will act as the foundation for both of our scripts. This query is a bit complicated, but we’re getting the information that we want out of the database, making only 4 queries regardless of how many posts and comments there are. outerjoin(positive_comments, Post.id = positive_comments.c.post_id) outerjoin(negative_comments, Post.id = negative_comments.c.post_id) outerjoin(comments, Post.id = comments.c.post_id) We’ll use these three subqueries as part of the main query that we’ll be using to get the posts.

Session.query(Comment.post_id, unt("*").label("positive_comments")) Session.query(Comment.post_id, unt("*").label("negative_comments"))

Session.query(Comment.post_id, unt("*").label("comments")) Let’s write these subqueries now:Įxport_csv.py # SQLAlchemy configuration omitted Because we need some information about the comments table but want to receive a row for each post, we’ll need to create a few different subqueries to calculate the various comment counts. With the SQLAlchemy boilerplate out of the way, we’re now ready to use our models. We’re also going to pass the database connection in using a DB_URL environment variable when we run the script: This is all done using SQLAlchemy itself instead of relying on code in the forum package.

Create an instance of our Session class to make queries to the database.

Create a Session class that is bound to our engine.

Create a SQLAlchemy engine to connect to our database server.

The foundation of both of our export scripts will be the same, so let’s write that first. Write the Posts Query in the `export_csv.py` Script Now we can use the forum library from within Python scripts and the REPL. With the virtualenv in place we just need to activate it and then install the forum package itself: $ pipenv shell This project utilized Pipenv in development, and we can use that now to create the virtualenv and install the dependencies: $ cd ~/forum

We’re going to create a new Python 3.7 virtualenv and install the package locally. We have the source for our forum package downloaded to ~/forum. Successfully complete this lab by achieving the following learning objectives: Create a Virtualenv and Install the `forum` Package