-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathqueries.py
executable file
·124 lines (111 loc) · 3.12 KB
/
queries.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
#!/usr/bin/python
import os
import sys
import math
starch_query = """
SELECT
starch_yield.name,
starch_yield.id as yield_id,
staerkegehalt_g_kg,
knollenmasse_kgfw_parzelle,
cultures.plantspparcelle,
locations.limsid as location_id,
locations.elevation,
locations.gridref_north as latitude_n,
locations.gridref_east as longitude_e,
cultures.id as culture_id,
cultures.name as culture_name,
cultures.plantspparcelle,
cultures.planted,
cultures.terminated,
subspecies.cultivar as cultivar,
subspecies.id as sub_id,
subspecies.limsid as sub_limsid,
subspecies.breeder,
subspecies.reifegrclass as reifegruppe,
treatments.id as treatment_id,
treatments.value_id as treatment,
plants.aliquot as plant_aliquot,
plants.subspecies_id as psub_id
FROM starch_yield, locations, cultures, treatments, plants, subspecies
WHERE locations.id = starch_yield.location_id
AND treatments.aliquotid = starch_yield.aliquotid
AND plants.aliquot = starch_yield.aliquotid
AND plants.subspecies_id = subspecies.id
AND cultures.id = plants.culture_id
AND subspecies.id != 37
""".strip()
climate_query = """
SELECT
tmin,
tmax,
precipitation,
irrigation,
datum,
locations.limsid,
locations.id,
cultures.planted,
cultures.terminated
FROM temps, locations, cultures
WHERE temps.location_id = locations.id
AND cultures.location_id = locations.id
AND temps.datum BETWEEN cultures.planted AND cultures.terminated
AND (invalid != 1 OR invalid is NULL)
""".strip()
irrigation_query = """
SELECT
date,
treatment_id,
locations.limsid,
value as irrigation_amount,
cultures.limsstudyid
FROM irrigation, locations, cultures
WHERE irrigation.location_id = locations.limsid
AND cultures.location_id = locations.id
AND irrigation.date BETWEEN cultures.planted AND cultures.terminated
AND treatment_id != 169
AND treatment_id != 171
AND value > 0
""".strip()
def main(argv):
return None
if __name__ == '__main__': main(sys.argv[1:])
golm_starch_query = """
SELECT
samples.name as sample_name,
plants.id as plant_id,
`values`.attribute as v_attr,
phenotype_values.number as x_weight,
cultures.plantspparcelle,
locations.limsid as location_id,
locations.elevation,
locations.gridref_north as latitude_n,
locations.gridref_east as longitude_e,
cultures.id as culture_id,
cultures.name as culture_name,
cultures.plantspparcelle,
cultures.planted,
cultures.terminated,
subspecies.cultivar as cultivar,
subspecies.id as sub_id,
subspecies.limsid as sub_limsid,
subspecies.breeder,
subspecies.reifegrclass as reifegruppe,
treatments.id as treatment_id,
treatments.value_id as treatment,
plants.aliquot as plant_aliquot,
plants.subspecies_id as psub_id
FROM starch_yield, locations, cultures, treatments, plants, subspecies,
samples, phenotypes, `values`, phenotype_values
WHERE samples.plant_id = plants.id
AND phenotypes.sample_id = samples.id
AND phenotype_values.phenotype_id = phenotypes.id
AND `values`.id = phenotype_values.value_id
AND `values`.id = 69
AND locations.id = cultures.location_id
AND treatments.aliquotid = starch_yield.aliquotid
AND plants.aliquot = starch_yield.aliquotid
AND plants.subspecies_id = subspecies.id
AND cultures.id = plants.culture_id
AND subspecies.id != 37
""".strip()