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
|
# -*- coding: utf8 -*-
#==============================================================
# GNUmed database schema change script
#
# License: GPL v2 or later
# Author: karsten.hilbert@gmx.net
#
#==============================================================
import os
import csv
from Gnumed.pycommon import gmPG2
from Gnumed.pycommon import gmTools
#--------------------------------------------------------------
SQL_CREATE_DATA_SOURCE = u"""
delete from ref.data_source
where
name_short = 'ATC'
and
version like '2009-01-%'
and
lang in ('en','de','fr','de_DE','en_EN','fr_FR');
insert into ref.data_source (
name_long,
name_short,
version,
description,
source,
lang
) values (
'Anatomical Therapeutic Chemical Classification',
'ATC',
'2009-01-EN',
'ATC codes downloaded from the source of the GPL software FreeDiams',
'http://freemedforms.googlecode.com/svn/trunk/global_resources/sql/atc_utf8.csv',
'en'
);
insert into ref.data_source (
name_long,
name_short,
version,
description,
source,
lang
) values (
'Anatomical Therapeutic Chemical Classification 1/2009 Deutschland',
'ATC',
'2009-01-DE',
'ATC codes downloaded from the source of the GPL software FreeDiams',
'http://freemedforms.googlecode.com/svn/trunk/global_resources/sql/atc_utf8.csv',
'de'
);
insert into ref.data_source (
name_long,
name_short,
version,
description,
source,
lang
) values (
'Anatomical Therapeutic Chemical Classification',
'ATC',
'2009-01-FR',
'ATC codes downloaded from the source of the GPL software FreeDiams',
'http://freemedforms.googlecode.com/svn/trunk/global_resources/sql/atc_utf8.csv',
'fr'
);"""
SQL_GET_DATA_SOURCES = u"""
select
(select pk from ref.data_source where name_short = 'ATC' and version like '2009-01-%' and lang = 'de') as de,
(select pk from ref.data_source where name_short = 'ATC' and version like '2009-01-%' and lang = 'fr') as fr,
(select pk from ref.data_source where name_short = 'ATC' and version like '2009-01-%' and lang = 'en') as en
;"""
SQL_INSERT = u"""
INSERT INTO ref.atc (
fk_data_source,
code,
term
) values (
%(src)s,
%(code)s,
%(term)s
)"""
def run(conn=None):
gmPG2.run_rw_queries(link_obj = conn, queries = [{'cmd': SQL_CREATE_DATA_SOURCE}], end_tx = False)
rows, idx = gmPG2.run_ro_queries(link_obj = conn, queries = [{'cmd': SQL_GET_DATA_SOURCES}], get_col_idx = False)
data_sources = rows[0]
data_fname = os.path.join('..', 'sql', 'v14-v15', 'data', 'atc_only-utf8.csv')
with open(data_fname, newline = '', encoding = 'utf8', errors = 'replace') as csv_file:
atc_reader = csv.DictReader(csv_file, delimiter = ",", quotechar = '"', fieldnames = [u'atc', u'en', u'fr', u'de'], restkey = 'list_of_values_of_unknown_fields')
for atc_line in atc_reader:
queries = [
{'cmd': SQL_INSERT, 'args': {u'src': data_sources['en'], u'code': atc_line['atc'], 'term': atc_line['en']}},
{'cmd': SQL_INSERT, 'args': {u'src': data_sources['fr'], u'code': atc_line['atc'], 'term': atc_line['fr']}},
{'cmd': SQL_INSERT, 'args': {u'src': data_sources['de'], u'code': atc_line['atc'], 'term': atc_line['de']}}
]
gmPG2.run_rw_queries(link_obj = conn, queries = queries, end_tx = False)
conn.commit()
#================================================================================
|
