-
Notifications
You must be signed in to change notification settings - Fork 2
/
DBUtil_pool.py
executable file
·113 lines (104 loc) · 4.12 KB
/
DBUtil_pool.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
#!/usr/bin/env python
# -*- coding: utf-8 -*-
'''
使用MySQL连接池进行连接数据库，但是速度没有原来快，故弃用，改用Redis
'''
from DBUtils.PooledDB import PooledDB
import MySQLdb
class DB(object):
def __init__(self):
print "create MySQL pool!!!!!!"
self.pool = PooledDB(
MySQLdb,
10, # 10为连接池里的最少连接数
host='10.1.13.29',
user='root',
passwd='tdlabDatabase',
db='techpooldata',
port=3306
)
self.tables = {'paper': 'expert_paper_join', 'patent': 'expert_patent_join', 'project': 'expert_project_join'}
self.columns = {'paper': 'PAPER_ID', 'patent': 'PATENT_ID', 'project': 'PROJECT_ID'}
# 返回以docId为key，以authors为value的map
def getAuthors(self, typee, ids):
if len(ids) == 0:
return {}
authorIds = {}
sql = "select " + self.columns[typee] + ",EXPERT_ID from " + self.tables[typee] + " where " + self.columns[
typee] + " in("
for i in range(len(ids)):
sql = sql + "'" + ids[i] + "'"
if i != len(ids) - 1:
sql = sql + ','
sql = sql + ') order by ' + self.columns[typee] + ',expert_role'
conn = self.pool.connection()
cur = conn.cursor()
cur.execute(sql)
results = cur.fetchall()
cur.close()
conn.close()
for line in results:
if line[0] not in authorIds:
authorIds[line[0]] = []
authorIds[line[0]].append(line[1])
return authorIds
# 返回id是否在数据库中，因为有时数据不同步
def idInDB(self, typee, id):
conn = self.pool.connection()
cur = conn.cursor()
sql = "select * from " + self.tables[typee] + " where " + self.columns[typee] + " ='" + id + "'"
count = cur.execute(sql)
cur.close()
conn.close()
return count > 0
# 返回一个作者的论文，包括非第一作者
def getPapers(self, expertId):
sql = "select p.PAPER_ID, p.name, p.abstract,p.authors, p.journal_name, p.year from expert_paper_join j JOIN paper p on j.PAPER_ID=p.PAPER_ID where j.EXPERT_ID='" + expertId + "';"
conn = self.pool.connection()
cur = conn.cursor()
cur.execute(sql)
r = cur.fetchall()
result = []
for line in r:
if line[2] is None:
abstract = ''
else:
abstract = line[2]
result.append([line[0], line[1], abstract, None, line[3], line[4], line[5]])
cur.close()
conn.close()
return result
# 返回一个作者的专利，包括非第一作者
def getPatents(self, expertId):
sql = "select p.PATENT_ID, p.name, p.abstract, p.application_no, p.inventors, p.applicant, p.year from expert_patent_join j JOIN patent p on j.PATENT_ID=p.PATENT_ID where j.EXPERT_ID='" + expertId + "';"
conn = self.pool.connection()
cur = conn.cursor()
cur.execute(sql)
r = cur.fetchall()
result = []
for line in r:
if line[2] is None:
abstract = ''
else:
abstract = line[2]
result.append([line[0], line[1], abstract, None, line[3], line[4], line[5], line[6]])
cur.close()
conn.close()
return result
# 返回一个作者的项目，包括非第一作者
def getProjects(self, expertId):
sql = "select p.PROJECT_ID, p.name, p.abstract_ch, p.member, p.unit, p.year, p.type from expert_project_join j JOIN project p on j.PROJECT_ID=p.PROJECT_ID where j.EXPERT_ID='" + expertId + "';"
conn = self.pool.connection()
cur = conn.cursor()
cur.execute(sql)
r = cur.fetchall()
result = []
for line in r:
if line[2] is None:
abstract = ''
else:
abstract = line[2]
result.append([line[0], line[1], abstract, None, line[3], line[4], line[5], line[6]])
cur.close()
conn.close()
return result