import pymysql
import pandas
conn = pymysql.connect(host='mysql.guaminsects.net',user='readonlyguest',passwd='readonlypassword',db='oryctes')
sql = "SELECT * from matt"
df = pandas.io.sql.read_sql(sql, conn)
df.to_csv('matt-expt.csv')
df.tail()
location | week | station | lure | lureStartMass | lureStopMass | maleCRB | femaleCRB | note | |
---|---|---|---|---|---|---|---|---|---|
211 | Leo1 | 6 | 2 | Anis | 2.253 | 0.979 | 0 | 0 | None |
212 | Leo1 | 6 | 3 | Oryc | NaN | NaN | 0 | 2 | None |
213 | Leo1 | 6 | 4 | Cary | 2.086 | 1.788 | 0 | 0 | None |
214 | Leo1 | 6 | 5 | Mix | 12.275 | 8.084 | 1 | 0 | None |
215 | Leo1 | 6 | 6 | Benz | 3.933 | 3.899 | 1 | 0 | None |
sql = """
SELECT lure, SUM(maleCRB+femaleCRB) AS trapCatch
FROM matt
GROUP BY lure
ORDER BY trapCatch DESC;
"""
df = pandas.io.sql.read_sql(sql, conn)
df
lure | trapCatch | |
---|---|---|
0 | Benz | 62 |
1 | Oryc | 60 |
2 | Anis | 54 |
3 | MEOH | 50 |
4 | Cary | 47 |
5 | Mix | 35 |