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
|
#!/usr/bin/env python3
# needs pandas >= 2.0
import pandas as pd
import os
import sys
import subprocess
import mysql.connector
from sqlalchemy import create_engine
## Create a connection object
## IP address of the MySQL database server
Host = os.environ['PARAM_DB_HOST']
## User name of the database server
User = os.environ['PARAM_DB_USER']
## Password for the database user
Password = os.environ['PARAM_DB_PASS']
## opens directly the database param
database = "param"
engine = create_engine(f"mysql+mysqlconnector://{User}:{Password}@{Host}/{database}")
# WMO definitions
centre = 0
query = f"select chem.id as chemId,shortName as chemShortName,chem.name as chemName,chem.formula as chemFormula,group_concat(concat(attribute.name,'=',coalesce(chem_attributes.attribute_value,'missing()')) order by attribute.o) as attributes from chem join chem_attributes on chem_attributes.chem_id=chem.id join attribute on attribute.id=chem_attributes.attribute_id where chem.centre_id={centre} group by chem.id;"
df = pd.read_sql(query, con=engine)
f1 = open("grib2/chemId.def", "w")
f2 = open("grib2/chemName.def", "w")
f3 = open("grib2/chemShortName.def", "w")
f4 = open("grib2/chemFormula.def", "w")
for i in range(len(df)):
attr_i = df.iloc[i]['attributes'].split(',')
chemName = df.iloc[i]['chemName']
chemId = df.iloc[i]['chemId']
chemShortName = df.iloc[i]['chemShortName']
chemFormula = df.iloc[i]['chemFormula']
print(chemId,chemName)
# os.system('echo "#' + str(chemName) + '"')
f1.write(f"#{chemName}\n")
f2.write(f"#{chemName}\n")
f3.write(f"#{chemName}\n")
f4.write(f"#{chemName}\n")
# os.system("echo \"" + "'" + str(chemId) + "'\" = {")
f1.write("'" + f"{chemId}" + "' " + "=" + " {" + "\n")
f2.write("'" + f"{chemName}" + "' " + "=" + " {" + "\n")
f3.write("'" + f"{chemShortName}" + "' " + "=" + " {" + "\n")
f4.write("'" + f"{chemFormula}" + "' " + "=" + " {" + "\n")
# os.system(f"echo \#{chemName}")
# we loop over the attributes
for j in range(len(attr_i)):
aid = attr_i[j].split('=')[0]
ava = attr_i[j].split('=')[1]
# os.system('echo "\t' + str(aid) + ' = ' + str(ava) + ' ;"')
f1.write(f"\t{aid} = {ava} ;\n")
f2.write(f"\t{aid} = {ava} ;\n")
f3.write(f"\t{aid} = {ava} ;\n")
f4.write(f"\t{aid} = {ava} ;\n")
# os.system('echo "\t"' + "}")
f1.write(f"\t" + '}\n')
f2.write(f"\t" + '}\n')
f3.write(f"\t" + '}\n')
f4.write(f"\t" + '}\n')
f1.close()
f2.close()
f3.close()
f4.close()
# ECMWF definitions
#os.system('mkdir -p grib2/localConcepts/')
#os.system('mkdir -p grib2/localConcepts/ecmf/')
centre = 98
query = f"select chem.id as chemId,shortName as chemShortName,chem.name as chemName,chem.formula as chemFormula,group_concat(concat(attribute.name,'=',coalesce(chem_attributes.attribute_value,'missing()')) order by attribute.o) as attributes from chem join chem_attributes on chem_attributes.chem_id=chem.id join attribute on attribute.id=chem_attributes.attribute_id where chem.centre_id={centre} group by chem.id;"
#cur.execute(query)
#df = cur.fetchall()
df = pd.read_sql(query, con=engine)
f1 = open("grib2/localConcepts/ecmf/chemId.def", "w")
f2 = open("grib2/localConcepts/ecmf/chemName.def", "w")
f3 = open("grib2/localConcepts/ecmf/chemShortName.def", "w")
f4 = open("grib2/localConcepts/ecmf/chemFormula.def", "w")
for i in range(len(df)):
attr_i = df.iloc[i]['attributes'].split(',')
chemName = df.iloc[i]['chemName']
chemId = df.iloc[i]['chemId']
chemShortName = df.iloc[i]['chemShortName']
chemFormula = df.iloc[i]['chemFormula']
print(chemId,chemName)
# os.system('echo "#' + str(chemName) + '"')
f1.write(f"#{chemName}\n")
f2.write(f"#{chemName}\n")
f3.write(f"#{chemName}\n")
f4.write(f"#{chemName}\n")
# os.system("echo \"" + "'" + str(chemId) + "'\" = {")
f1.write("'" + f"{chemId}" + "' " + "=" + " {" + "\n")
f2.write("'" + f"{chemName}" + "' " + "=" + " {" + "\n")
f3.write("'" + f"{chemShortName}" + "' " + "=" + " {" + "\n")
f4.write("'" + f"{chemFormula}" + "' " + "=" + " {" + "\n")
# os.system(f"echo \#{chemName}")
# we loop over the attributes
for j in range(len(attr_i)):
aid = attr_i[j].split('=')[0]
ava = attr_i[j].split('=')[1]
# os.system('echo "\t' + str(aid) + ' = ' + str(ava) + ' ;"')
f1.write(f"\t{aid} = {ava} ;\n")
f2.write(f"\t{aid} = {ava} ;\n")
f3.write(f"\t{aid} = {ava} ;\n")
f4.write(f"\t{aid} = {ava} ;\n")
# os.system('echo "\t"' + "}")
f1.write(f"\t" + '}\n')
f2.write(f"\t" + '}\n')
f3.write(f"\t" + '}\n')
f4.write(f"\t" + '}\n')
f1.close()
f2.close()
f3.close()
f4.close()
|
