A good number of my consulting clients use the very useful and powerful survey tool Limesurvey. Unfortunately, since version 1.92+, it seems impossible to reimport deactivated responses tables into new response tables if the survey was modified. I’m sure this doesn’t matter for long form surveys and mainly static surveys, but some of my clients use this platform as a dynamic form engine. In that case, forms can and will change over the duration of a project.
To resolve this problem and enable the importation of old responses tables, I’ve written a quick Python script. It uses MySQLdb, but that library should be installed by default on most Linux boxes. The script also requires a MySQL database backend but it should be easily adaptable to other database engines.
Before running the script, four variables must be configured:
[sourcecode language=”python”]
dbaddr=’127.0.0.1′ #localhost
dbuser=’username’ #user name used to access the limesurvey tables
dbpass=’password’ #password used to access the limesurvey tables
dbdb=’limesurveyDB’ #limesurvey’s database name
[/sourcecode]
After configuring these settings, you should be able to launch the script. Launching the script with the –help switch will print out the usage help:
python importOldAnswers.py --help usage: importOldAnswers.py [-h] [--list SID] [--copy TABLENAME SID] Import old results in current answer table optional arguments: -h, --help show this help message and exit --list SID list old result table for a given SID --copy TABLENAME SID copy old table to a new result table
Using the script with the –list SID argument will list all the old result tables for a given survey id (SID):
python importOldAnswers.py --list 15946 lime_old_survey_15946_20120124220544 lime_old_survey_15946_20120130222036 lime_old_survey_15946_20120304201731 lime_old_survey_15946_20120703192749 lime_old_survey_15946_20130304201731
From there, you can choose an old result source table to import into an existing response table:
python importOldAnswers.py --copy lime_old_survey_15946_20130304201731 15946 Copying table lime_old_survey_15946_20130304201731 to lime_survey_15946 Retrieve headers Number of columns in source table: 228 Number of columns in target table: 229 Number of columns in merged table: 228 Number of answers in source table: 2 Done copying
Now for the caveats: the script will not convert from a data type to another, it will not truncate data and it will not do any form of sanity check before inserting data. Never use on a production database, always work with a copy. If you find a bug, please let me know. I’ve released the code with a GPL license, but if that causes a problem let me know. If you know me in real life and you find that script useful, you can always buy me a beer! 🙂
You can download the code here, or copy and paste the following listing:
[sourcecode language=”python”]
__author__ = ‘Olivier H. Beauchesne’
__license__ = ‘GPL’
__version__ = ‘0.1’
__email__ = ‘olivier@olihb.com’
import sys
import MySQLdb as mysql
import argparse
#database settings
dbaddr=’127.0.0.1′ #localhost
dbuser=’username’ #user name used to access the limesurvey tables
dbpass=’password’ #password used to access the limesurvey tables
dbdb=’limesurveyDB’ #limesurvey’s database name
#this should not change if using limesurvey’s defaults
TablePrefix=’lime_survey_’
OldTablePrefix="lime_old_survey_"
def getHeaders(table):
cursor = db.cursor()
sql = "SELECT * FROM %s" % table
cursor.execute(sql)
desc = cursor.description
header = []
for i in desc:
header.append(i[0])
return header
def buildResultMap(table, headers):
cursor = db.cursor()
sql = "SELECT * FROM %s" % table
cursor.execute(sql)
data = cursor.fetchall()
m = dict()
for h in headers:
m[h]=[]
for d in data:
for i in range(0,len(d)):
header = headers[i]
m[header].append(d[i])
return m
def getOldTables(sid):
cursor = db.cursor()
sql = "SHOW TABLES"
cursor.execute(sql)
data = cursor.fetchall()
tableList = map(lambda x:x[0],data)
return filter(lambda x: x.count(str(sid)) and x!=TablePrefix+str(sid),tableList)
def mergeHeaders(table1,table2):
return filter(lambda x: x in table2,table1)
def insertNewData(header, results,tableName):
numItems = len(results[‘id’])
print "Number of answers in source table: "+str(numItems)
header.remove(‘id’)
cursor = db.cursor()
columns = ",".join(header)
for i in range(0,numItems):
columnsValues = map(lambda x: results[x][i],header)
format_strings = ‘,’.join([‘%s’] * len(header))
cursor.execute("INSERT INTO "+tableName+" ("+columns+") VALUES (%s)" % format_strings, tuple(columnsValues))
def main():
global db
#argument parsing
parser = argparse.ArgumentParser(description=’Import old results in current answer table’)
parser.add_argument(‘–list’,type=int, help="list old result table for a given SID",nargs=1,metavar=’SID’)
parser.add_argument(‘–copy’,type=str, help="copy old table to a new result table", nargs=2, metavar=(‘TABLENAME’,’SID’))
#exit if empty
args=parser.parse_args()
if len(sys.argv)==1:
parser.print_help()
sys.exit(1)
#connect to db
db=mysql.connect(host=dbaddr,user=dbuser,passwd=dbpass,db=dbdb)
#show list
if args.list:
tables = getOldTables(args.list[0])
for t in tables:
print t
#copy table
if args.copy:
#format table name
sourceTable = args.copy[0]
targetTable = TablePrefix+str(args.copy[1])
print "Copying table "+sourceTable+" to "+targetTable
#get headers
print "Retrieve headers"
headersSource = getHeaders(sourceTable)
headersTarget = getHeaders(targetTable)
mergedHeaders = mergeHeaders(headersSource,headersTarget)
print "Number of columns in source table: "+str(len(headersSource))
print "Number of columns in target table: "+str(len(headersTarget))
print "Number of columns in merged table: "+str(len(mergedHeaders))
#merge results
resultSource = buildResultMap(sourceTable, headersSource)
insertNewData(mergedHeaders,resultSource,targetTable)
print "Done copying"
#entry point
if __name__ == ‘__main__’:
main()
[/sourcecode]