Write script to upload all experiments to Zenodo

[MichaelCurrie]

Building from doing it a single time in https://github.com/openworm/open-worm-analysis-toolbox/issues/204, let's do it multiple times.

PSEUDOCODE FOR PROOF OF CONCEPT for Python script:

• remotely connect to AWS machine mysql worm database
• loop through top 10 experiments:
  • find the local path field given in experiments_full
  • take everything in the local path and zip it
  • create a deposition with metadata from experiments_full
  • send as a DOI to sandbox.zenodio
  • insert that DOI to a column in the mysql database

[MichaelCurrie]

It looks like back in February @ver228 took a stab at this: https://github.com/ver228/single-worm-analysis/blob/master/upload_zenodo/test.py. I think I can make a version of the script that also uploads the metadata, however.

[MichaelCurrie]

Another duplicate issue relating to this long-outstanding task is this one: https://github.com/openworm/open-worm-analysis-toolbox/issues/192

[MichaelCurrie]

This issue is in my zenodio repo but should really be in movement_cloud since it deals with the mysql database: https://github.com/MichaelCurrie/zenodio/issues/4

I'll use pandas and from this example:

import mysql.connector as sql
import pandas as pd

db_connection = sql.connect(host='hostname', database='db_name', user='username', >password='password')
db_cursor = db_connection.cursor()
db_cursor.execute('SELECT * FROM table_name')

table_rows = db_cursor.fetchall()

df = pd.DataFrame(table_rows)

I found I needed to install an older version since the latest development version has a dependency on something called "Protobuf" which is hard to meet:

sudo pip3 install mysql-connector==2.1.4 sudo pip3 install pandas

[MichaelCurrie]

From André:

It would make sense to have most of the metadata there. At least the strain, gene and allele (where available), time stamp, sex, and condition (i.e. on food or not) would all be useful to have as a quick summary for anyone looking at the data directly on the page.

[MichaelCurrie]

@ver228 FYI after much delay I am working on this one as my top priority. I hope to have it for you by our Thursday meeting. Thanks

(For my notes, here is a script from Avelino to extract JSON metadata which I forget why is important but I might remember: https://github.com/ver228/single-worm-analysis/blob/master/2_create_database/helper/db_info.py#L82)

[MichaelCurrie]

@ver228 the dependency for this is now getting OWAT to be support WCON, i.e. to be able to create a feature file in the WCON format. I think this should be what's uploaded. We are working on making event features supported natively within the tracker-commons format also.

So there are some moving parts here...

[MichaelCurrie]

After a meeting 1.5 weeks ago we decided we DIDN'T need to get fancy about having the features in the WCON file. So this can proceed again without that blocker.

Also, example code to post a deposition to zenodo is here: https://github.com/MichaelCurrie/zenodio/blob/master/examples/adam_smith.py