Make .CSV file using MySQL and Python

In this blog, I am going to show how to develop an application by which we can make .csv files from MySQL database using Python.

Steps

SQL command

Now, let us start by getting data from Database. Lets say we have a DB names csv_test and it contains a table named test with fields id and name, and for testing purpose let us have some values inserted into it like this:

+------+--------+
| id   | name   |
+------+--------+
|    1 | test_1 |
|    2 | test_2 |
|    3 | test_3 |
+------+--------+

Now, to get all data from this table, the SQL command should be:

SELECT * FROM csv_test.test;

Run SQL command in python

We shall run this vary command using python like this:

import subprocess

try:
    sql_cmd = "mysql -uroot -proot -e SELECT * FROM csv_test.test;"
    results = subprocess.check_output(
            [sql_cmd], shell=True)
    print(results)
except Exception as e:
        print(e)

If we run the above command, we shell find a binary string like this:

id	name
1	test_1
2	test_2
3	test_3

Actual output will be something like this:

b'id\tname\n1\t\test_1\n2\ttest_2\n3\ttest_3'

Now we will convert this binary string to unicode string by running this command:

data = results.decode('utf-8')

And the data will be like this:

'id\tname\n1\t\test_1\n2\ttest_2\n3\ttest_3'

So, now we have the MySQL data as string. Now we shall start making .csv file out of it.

To csv

First, lets make an empty .csv file, ie:

filename = 'report_{0}.csv'.format(datetime.datetime.now().strftime('%d-%m-%Y:%H:%M'))
csv_out = open(filename, 'w')

From the data string, we will start making .csv file’s contents. Let us split the string in new lines(\n) and loop through that list, also replace tabs(\t) with comma(,):

csv_data = ''
for item in data.split(`\n`):
    csv_data += '{0}\r\n'.format(item.replace('\t', ','))

Now we write that csv_data variable to csv_out(empty .csv file) like this:

csv_out.write(csv_data)
csv_out.close()

Thats it, our work is done. We shall see a .csv file named REPORT_DD-MM-YYYY:HH:MM:SS.csv in the project directory.

In conclusion

Special Thanks to this post: https://redmoses.me/flask-and-shell/ about using MySQL and Flask togather

Source is here: https://github.com/ruddra/mysql-python-csv

Last updated: Jul 13, 2024