Pulling data from ODK central and dumping it in MySQL database table(s)

Support
1

1. What is the issue? Please be detailed.

I just watched the workflow automation video, and it was very helpful. I am trying to incorporate ODK central in my data management processes without changing much on what I was doing previously with aggregate. Most of my data management systems rely on accessing tables/views from databases, especially MySQL databases. I was thinking of automating the pulling of data from ODK central using PyODK and then dumping the data into a MySQL database table. I have managed to pull the data and dump it to an “EXISTING” table in my database which I created manually. I was wondering if it is possible to create a table using Python by getting the submissions schema (variable names, data types, etc.) straight from ODK central and automatically passing this to an object in Python to create a table in the MySQL database.

Automating this would really be nice since I manage a lot of projects and it’s not efficient to manually create database tables for all projects I manage.

2 Likes
2

pandas has a to_sql method that should be helpful here.

Right now, all of our examples in pyODK let pandas infer types from the json. That may be good enough for you but if not, you also have the option of getting schema info from the fields endpoint or by parsing the XLSForm. We intend to have some examples of that at some point but haven't had a chance to yet.