Kategorien
Writing a CSV export for Admidio
Admidio is a small CRM tool for sport clubs. The data is saved to a mysql database in a cruel way, if you see it from „let me change the system“ point of view. The user related fields are extensible and saved in one table. The real user data is scaled vertically in another table. You get tuples like (entry_id, user_id, field_id, value). This is a horror to export in a horizontal scaled format like CSV! But! Here is a solution, written in python:
# -*- coding: utf-8 -*-def main(): import MySQLdb conn = MySQLdb.connect (host="localhost", user="root", passwd="", db="crm") cursor = conn.cursor () cursor.execute ("SELECT usf_id, usf_name FROM adm_user_fields") aNameRows = cursor.fetchall() kvMapNames = {} for aNameRow in aNameRows: kvMapNames[aNameRow[0]] = aNameRow[1] headline = ",".join(kvMapNames.values()) + "\n" cursor.execute("SELECT DISTINCT usd_usr_id FROM adm_user_data") aUserData = cursor.fetchall() aCSVRows = [] for row in aUserData: cursor.execute("SELECT * FROM adm_user_data WHERE usd_usr_id=%d ORDER BY usd_usf_id" % row[0]) aData = cursor.fetchall() aDataPresent = {} for urow in aData: print urow aDataPresent[urow[2]] = urow[3] for missingKey in [k for k in kvMapNames.keys() if k not in aDataPresent.keys()]: aDataPresent[missingKey] = "" aCSVRows.append(",".join(aDataPresent.values()) + "\n") print headline print "".join(aCSVRows) f = open ("out.csv", "w") f.write(headline) f.write("".join(aCSVRows)) f.close()if __name__ == '__main__': main()
