Creating a CSV File from SQLAlchemy with Python!
Hey there! Welcome to my blog where I post about my journey as a self-taught developer. You can find my GitHub by clicking HERE.
Quick Note!
This post assumes the reader understands the structure and architecture of a basic Python / Flask app with SQLAlchemy as well. We will be looking at Blueprints, Queries, Models, Tables and the CSV module. With that said, please read on!
CSV Files are commonly used in the business world. Specifically for accounting and closing the books each month. Even certain programs require CSV file uploads (Quickbooks)! Perhaps a company is making a transition from their paper workflow to a more paperless solution. Data is easy to query from SQLAlchemy and upload into a CSV file, where you can easily import to another program from there. (Yes, there are further automations such as scraping and form bots but that is beyond the scope of this post!)
Python has a great module called “csv”. Batteries are definitely included in Python3 (3.8) which is the version of Python I currently use. The “csv” module is worth looking into HERE.
A very useful task indeed, and this is also a quick post, but working with data and using the csv module is something python developers should have in their tool box, considering the csv format as stated in the docs explain, “is the most common import and export format for spreadsheets and databases”. Let’s look at some examples.
Assuming you have imported csv in your python file, let’s create a function within the blueprint @adminUsers in our python file. We will call it QuickBooks and assign the function a decorator of @login_required. We also have a check_admin() function preventing any user who is not admin & logged in to accessing the function.
Let’s take a look at a query from our database! We have a Policy Table in our application which contains columns for an auto insurance policy. Let’s give the query a name of policies. The query begins with db.session.query. There are several ways to query a table in SQLAlchemy, and for more complex queries I have found it helpful to write plain SQL first and then translate it. A bit easier when dealing with subqueries.
So what is going on here? We’ve queried the Policy table, and then we join the User by the Users id and the Policies user_id. SQLAlchemy has a great built in function called order_by which takes in the Policy.id and we can order in descending order. We will do this for all the policies in our database using .all().
But what columns are in the Policy Table? And what about the User Table? I will show you so.
User Table & Imports in our models.py
Policy Table in our models.py
Many columns, but let’s pull out just the following bits of data.
- First Name & Last Name
- Policy Year Make Model
- Policy Total Cost Of Insurance
So there is a good amount of code here, but read it carefully. There are a few bells and whistles the csv module comes with.
with open():
What does this do?!
‘test.csv’ — File we are opening to write in
newline= ‘’ — If newline='' is not specified, newlines embedded inside quoted fields will not be interpreted correctly, and on platforms that use \r\n linendings on write an extra \r will be added. It should always be safe to specify newline='', since the csv module does its own (universal) newline handling.
So it is best practice to use a good naming convention for your csv file, implement the ‘w’, and always use newline=””. This is taken directly from the docs under their simplest writing solution!
The csv module’s writer object has a few public methods. We will be using writerow! Writerow takes one parameter.
Our policies query returned all the policies in our database, if you recall. So we provide a variable p as the iterator and loop through the policies query. This allows us to match up our data correctly with the first row in our csv file!
Remember, we joined the Policy Table and User Table in a query join. We were originally querying the Policy Table so we can access a users information by p.user within the loop.
Here is the entire snippet in the Flask Blueprint.
send_file must be imported from Flask at the beginning of the file, and it is very useful. We have our path to the test.csv file in our codebase, it will have the filename Quickbooks.csv, and it will act as a download in your browser.
I hopes this helps someone on their journey! Medium has been really good to me so I am excited to share some of my learnings and techniques over the last several years.
Happy Coding!
