How use Jinja to generate sql scripts from csv file

A while ago I was tasked to generate an sql script to add test users in database and assign roles. If you have 1 or 2 users you can do it manually, copy, paste and update values, but if you have more, the chances to make mistakes increases and you should use a tool to generate the scrips automatically.

After a short research a found this project on GitHub github.com/rjchee/jinja-csv. Using this you can take a csv file as input and a Jinja2 template to generate a file or a list of files.

The project is not maintained anymore so I forked the project I fixed some issues and I added the necessary files to create a package github.com/gabihodoroaga/jinja-csv.

Jinja is a very powerful templating language for Python, supports template inheritance, loops control, custom filters and many more. You can find the full documentation here Template Designer Documentation.

How to use it and what is special about this project

First install the package

pip3 install https://github.com/gabihodoroaga/jinja-csv/releases/download/v0.2/jinja_csv-0.2-py3-none-any.whl

then create a csv file users.csv with the following content

userid,role,pass,name
admin1,admin,password,Administrator
user1,viewer,password,User 1
user2,editor;admin,password,User 2

and then create the template file users.j2 with the following content

-- begin script
{% for row in rows %}
-- begin user script : {{row['userid']}}
INSERT INTO user(userid,pass,name) VALUES ('{{row['userid']}}','{{row['pass']}}','{{row['name']}}');
{% for role in row['role'].split(';') %}
INSERT INTO role(userid,role) VALUES ('{{row[0]}}','{{role.strip()}}');
{% endfor %}
-- end user script
{% endfor %}
-- end script

in the template you can use both the column name row[‘userid’] or the column id row[0]

and the final step si to generate the sql

jinja-csv -i users.csv -t users.j2

you can pipe the result to another command

jinja-csv -i users.csv -t users.j2 | mysql -u root -p testdb

The package supports generation of one file for each row in the csv file

This is the list with all the command line arguments

-i, --inputfile     The input csv file.
-t, --template      The Jinja template file
-o, --output        The output file, or the output folder when --file argument is present
-f, --files         Generate one file per row
-r, --row           Index of the row to be used for the name of the file
-e, --ext           The file extension to be used

I hope this tool will be useful for anyone else as it was for me.

Conclusion

Jinja is a very powerful and designer-friendly templating engine and together with this tool it can save you a lot of time.