-
Notifications
You must be signed in to change notification settings - Fork 0
/
database.py
executable file
·83 lines (76 loc) · 4.78 KB
/
database.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
# -- coding: utf-8 --
import MySQLdb
string_create_table = """
CREATE TABLE data(
trial_id LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
trial_title LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
trial_acronym LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
trial_url LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
summary_lay LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
summary_sci LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
org_data LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
secondary_id LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
health_condition LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
characteristics LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
outcome_primary LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
outcome_secondary LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
recruitment_countries LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
recruitment_locations LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
recruitment LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
criteria_inclusion LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
criteria_inclusion_add LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
criteria_exclusion LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
primary_sponsor_add_institution LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
primary_sponsor_add_street LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
primary_sponsor_add_city LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
primary_sponsor_add_country LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
primary_sponsor_add_phone LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
primary_sponsor_add_fax LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
primary_sponsor_add_mail LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
primary_sponsor_add_url LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
primary_sponsor_add_name LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
contact_sci_queries_add_institution LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
contact_sci_queries_add_street LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
contact_sci_queries_add_city LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
contact_sci_queries_add_country LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
contact_sci_queries_add_phone LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
contact_sci_queries_add_fax LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
contact_sci_queries_add_mail LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
contact_sci_queries_add_url LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
contact_sci_queries_add_name LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
contact_pub_queries_add_institution LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
contact_pub_queries_add_street LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
contact_pub_queries_add_city LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
contact_pub_queries_add_country LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
contact_pub_queries_add_phone LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
contact_pub_queries_add_fax LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
contact_pub_queries_add_mail LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
contact_pub_queries_add_url LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
contact_pub_queries_add_name LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci
)
"""
class Database:
def __init__(self, address, username, password, db_name):
self.db = MySQLdb.connect(address, username, password, db_name, charset='utf8', init_command='SET NAMES UTF8', use_unicode=True)
self.db_cursor = self.db.cursor()
self.db_cursor.execute('DROP TABLE IF EXISTS data')
self.db_cursor.execute(string_create_table)
def insert_data(self, data):
keys = data.val.keys()
sql = str("INSERT INTO data(")
type(sql)
for key in keys:
sql += key
if key != keys[-1]:
sql += str(", ")
sql += str(") VALUES (")
for key in keys:
escaped_key = data.val[key].encode("utf-8")
sql += str('"' + MySQLdb.escape_string(escaped_key) + '"')
if key != keys[-1]:
sql += str(', ')
sql += str(')')
self.db_cursor.execute(sql)
self.db.commit()
def close_connection(self):
self.db.close()