UPET/MySQLProcessor.py at master · lawinsider/UPET · GitHub

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
125
126
127
128
129
130
131
132
133
134
135
136
import MySQLdb

class MySQLProcess:
    """
    UPET(USPTO Patent Exploring Tool)
        provides Python code for downloading, parsing, and loading USPTO patent bulk data into a local MySQL database.
    Website:
        http://abel.lis.illinois.edu/upet/
    Authors:
        Qiyuan Liu (http://liuqiyuan.com, qliu14@illinois.edu),
        Vetle I. Torvik (http://people.lis.illinois.edu/~vtorvik/, vtorvik@illinois.edu)
    Updated:
        12/09/2012
    """
    """
    Used to operate the MySQL database.
    Remind to replace the arguments in the following funciton '__init__' with your own database parameters
    """
    def __init__(self, host="127.0.0.1", port=3306,user="qliu14", passwd="qliu14password",db="uspto_patents", charset="utf8"):
        # host="127.0.0.1", port=3306,user="root", passwd="LIUqiyuan",db="qliu14", charset="utf8"
        # host="127.0.0.1", port=3306,user="qliu14", passwd="qliu14password",db="qliu14", charset="utf8"
        self._host = host
        self._port = port
        self._username = user
        self._password = passwd
        self._dbname = db
        self._charset = charset
        self._conn = None
        self._cursor = None

    def load(self,sql):
        if self._conn==None:
            self.connect()
            self._cursor.execute(sql)
            self._conn.commit()
            result = self._cursor.fetchall()  #fetchone(), fetchmany(n)
            return result  #return affected rows
        else:
            self._cursor.execute(sql)
            self._conn.commit()
            result = self._cursor.fetchall()  #fetchone(), fetchmany(n)
            return result  #return affected rows

    def execute(self, sql): # no parameters
        #try:
        if self._conn==None:
            self.connect()
            self._cursor.execute(sql)
            self._conn.commit()
            result = self._cursor.fetchall()  #fetchone(), fetchmany(n)
            return result  #return affected rows
        else:
            self._cursor.execute(sql)
            self._conn.commit()
            result = self._cursor.fetchall()  #fetchone(), fetchmany(n)
            return result  #return affected rows
        #finally:
            #self.close()
            #function "close()" can be added efficiently after multiple lines of SQL statements.

    # used to retrieve ID buy matching fields of values
    def query(self,sql):
        #try:
        if self._conn == None:
            self.connect()
            self._cursor.execute(sql)
            self._conn.commit()
            result=self._cursor.fetchone()
            return int(result[0])
        else:
            self._cursor.execute(sql)
            self._conn.commit()
            result=self._cursor.fetchone()
            return int(result[0])
        #finally:
            #self.close()

    # used to verify whether the applicationID is in the current table APPLICATION
    def verify(self,sql):
        if self._conn == None:
            self.connect()
            self._cursor.execute(sql)
            self._conn.commit()
            return self._cursor.fetchone()
        else:
            self._cursor.execute(sql)
            self._conn.commit()
            return self._cursor.fetchone() #None or not

    def executeMany(self, sql, params):
        #try:
        if self._conn == None:
            self.connect()
            count = self._cursor.executemany(sql, params)
            self._conn.commit()
            return count
        else:
            count = self._cursor.executemany(sql, params)
            self._conn.commit()
            return count
        #finally:
            #self.close()

    def executeParam(self, sql, param):
        #try:
        if self._conn == None:
            self.connect()
            self._cursor.execute(sql, param)
            self._conn.commit()
            result = self._cursor.fetchall()  #fetchone(), fetchmany(n)
            return result  #return a tuple ((),())
        else:
            self._cursor.execute(sql, param)
            self._conn.commit()
            result = self._cursor.fetchall()  #fetchone(), fetchmany(n)
            return result  #return a tuple ((),())
        #finally:
            #self.close()

    def connect(self):
        if self._conn == None:
            self._conn = MySQLdb.connect(host=self._host, user=self._username,
                                    passwd=self._password, db=self._dbname,
                                    port=self._port, charset=self._charset)
            print "Connected successfully!"
        if self._cursor == None:
            self._cursor = self._conn.cursor()

    def close(self):
        if self._cursor != None:
            self._cursor.close()
            self._cursor = None
        if self._conn != None:
            self._conn.close()
            self._conn = None
        print 'Closed successfully!'