Skip to content

Instantly share code, notes, and snippets.

@sourceperl
Last active Apr 11, 2019
Embed
What would you like to do?
flyspray CSV export tool
#!/bin/sh
# populate pub/ http directory with flyspray CSV export
# copy this script to /etc/cron.weekly
# csv file build name
YEAR=$(date +%Y)
CSV_FILE=export_$YEAR.csv
/usr/local/bin/flyspray2csv > /var/www/html/pub/flyspray/$CSV_FILE
#!/usr/bin/env python3
# export data from flyspray to a CSV file (on stdout)
from codecs import BOM_UTF8
import csv
from datetime import datetime
import sys
import pymysql
# some const
SQL = """
SELECT t.task_id, p.project_title, c.category_name, s.status_name, tt.tasktype_name,
t.item_summary, t.detailed_desc,
t.date_opened, t.date_closed, t.last_edited_time,
uo.user_name AS opened_by_user,
ua.user_name AS assigned_user,
COUNT(cm.comment_id) AS comments_nb
FROM flyspray_tasks AS t
LEFT JOIN flyspray_projects AS p ON t.project_id = p.project_id
LEFT JOIN flyspray_list_category AS c ON t.product_category = c.category_id
LEFT JOIN flyspray_list_status AS s ON t.item_status = s.status_id
LEFT JOIN flyspray_list_tasktype AS tt ON t.task_type = tt.tasktype_id
LEFT JOIN flyspray_users AS uo ON t.opened_by = uo.user_id
LEFT JOIN flyspray_assigned AS a ON t.task_id = a.task_id
LEFT JOIN flyspray_users AS ua ON a.user_id = ua.user_id
LEFT JOIN flyspray_comments AS cm ON t.task_id = cm.task_id
GROUP BY t.task_id
ORDER BY last_edited_time DESC LIMIT 5000
"""
CSV_FIELDS = ["task_id", "project_title", "category_name", "status_name", "tasktype_name", "opened_by_user", "last_edited_time", "date_opened", "date_closed",
"assigned_user", "comments_nb", "item_summary", "detailed_desc"]
# some class
class ExcelFr(csv.excel):
delimiter = ";"
csv.register_dialect("excel-fr", ExcelFr())
# connect to DB
db = pymysql.connect(db="flyspray",
user="flysprayexport",
password="flysprayexport",
charset="utf8mb4",
cursorclass=pymysql.cursors.DictCursor)
with db.cursor() as cursor:
# do request
if cursor.execute(SQL):
# print an UTF-8 BOM (for overide default charset on Excel)
sys.stdout.write(BOM_UTF8.decode("utf8"))
# build CSV
w = csv.DictWriter(sys.stdout, fieldnames=CSV_FIELDS,
extrasaction="ignore", dialect="excel-fr")
# add header line
w.writeheader()
# add lines
for d in cursor.fetchall():
# update datetime fields (timestamp -> str date)
for (k, v) in d.items():
if k.startswith("date_") or k.endswith("_time"):
ts = int(v)
if ts > 0:
d[k] = datetime.utcfromtimestamp(ts).strftime("%Y-%m-%d %H:%M:%S")
else:
d[k] = ""
# add current line to CSV
w.writerow(d)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment