source: orange/orange/orngMySQL.py @ 2427:c134207367c7

Revision 2427:c134207367c7, 10.1 KB checked in by blaz <blaz.zupan@…>, 8 years ago (diff)

Interface to MySQL

Line 
1# A module that interfaces with MySQL and is used to write orange data
2# tables to MySQL and read data tables from MySQL and converts them to
3# Orange ExampleTable.
4
5# Module was first drafted by Uros Rozac as a part of his
6# undergraduate thesis.
7
8# Uses:     MySQL, MySQLpy
9
10import MySQLdb
11import re, sys, string
12import orange
13
14delimit_char="$"       # delimiter for attribute name when writing to mysql table, e.g. m$meta, c$class
15delimit_char_read="$"  # delimiter used when reading attribute names mysql table
16# the following names can't be used for field names; if any of these are
17# used in orange, then '_' will be added to the end of the name, like select_ , or condition_
18reserved_words=['select','condition','while','insert','update','alter','join']
19char_sql="CHAR(200)"   # for length of string fields in MySQL
20
21class Connect:
22 
23  def __init__(self, host, user, passwd, db):
24    self.data = MySQLdb.Connect(host, user, passwd, db)
25    self.SQLDomainDepot = orange.DomainDepot()
26    self.special_value='""'           # for enumvariable and string
27    self.special_float_value=-99999   # for int and float
28   
29  def load(self, table, use=None):
30    data=self.query('select * from %s'%(table),use)
31    return data
32 
33  def showTables(self):
34    db=self.data
35    cursor=db.cursor()
36    cursor.execute('show tables')
37    numrows = int(cursor.rowcount)
38    print "Tables: ",
39    for x in range(0,numrows-1):
40      row = cursor.fetchone()
41      print row[0]+",", 
42    row=cursor.fetchone()
43    print row[0]
44
45  def __attrname2sql(self, a):
46    pom = a.name
47    subs = [("'",''), ("-",'_'), ("/",'_'), ("\\",'_'), ("+",'__'), (" ",'_')]
48    for s in subs:
49      pom = pom.replace(*s)
50    if pom in reserved_words:            # name of attributes can't be select,insert....
51      pom = "$" + pom                    # for attribute names that are SQL keywords
52    if a.varType == orange.VarTypes.Continuous:
53      pom += " FLOAT"
54    elif a.varType == orange.VarTypes.Discrete:
55      if a.values:
56        pom += " ENUM (%s)" % reduce(lambda x, y: "%s, %s" % (x, y), ['"%s"' % i for i in a.values])
57      else:
58        pom += ' ENUM ("empty")'
59    elif a.varType == orange.VarTypes.String:
60      pom += " " + char_sql
61    return pom
62
63  def __attrvalue2sql(self, a):
64    if a.isSpecial():
65      if a.varType==orange.VarTypes.Continuous:
66        return self.special_float_value
67      else:
68        return self.special_value
69    if a.varType == orange.VarTypes.Continuous:
70      return a.value
71    elif a.varType == orange.VarTypes.Discrete:
72      return '"%s"' % a.value
73    else:
74      return '"%s"' % str(a)
75     
76  # ************************************************************************************   
77  def write(self, table, data, overwrite=0):
78    db=self.data
79    data_temp=data
80
81    #Creates string with all attributes and types of attributes
82    #Get Attributes
83    list_variables = [self.__attrname2sql(a) for a in data.domain.attributes]
84    if data.domain.classVar:
85      list_variables.append("c$"+self.__attrname2sql(data.domain.classVar))
86
87    metas = data.domain.getmetas().values()     
88    list_variables.extend(["m$"+self.__attrname2sql(a) for a in metas])
89
90    meta_exist = bool(data.domain.getmetas())
91
92    attr_string = reduce(lambda x, y: "%s, %s" % (x, y), list_variables)
93   
94    #get information about tables, if table already exists...
95    cursor=db.cursor()
96    cursor.execute('show tables')
97
98    for x in range(int(cursor.rowcount)):
99      if table == cursor.fetchone()[0]:
100        if overwrite:
101          cursor.execute('drop table ' + table)
102          break
103        else:
104          raise "Table '%s' already exists" % table
105
106    cursor.execute('create table %s(%s)'%(table,attr_string))
107
108    # writes data to table     
109    for x in data:
110      data_line = reduce(lambda x, y: ("%s, %s") % (x, y), [self.__attrvalue2sql(v) for v in x] + [self.__attrvalue2sql(x[m]) for m in metas])
111      cursor.execute('insert into %s values(%s)'%(table, data_line))
112     
113    cursor.close()
114   
115  #*************************************************************************** 
116  def query(self, statement, use=None):
117    # first, remove repeated blanks and blanks after commas
118    p = re.compile('[ ]+')
119    statement = p.sub(' ', statement)
120    p = re.compile(', ')
121    statement = p.sub(',', statement)
122
123    db=self.data
124   
125    names_values=[]
126    # get the name of the table, if there is one table. Otherwise table="x" (control)
127    # get names of fields (with or without the names of the tables)
128    stat_split=string.split(statement," ")
129    manyTables = 0
130    allAttributes = 0
131    table=[]
132    pom1_attr=[]   
133    if stat_split[1]=='distinct':
134      if len(string.split(stat_split[4],","))==1:
135          table.append(stat_split[4])
136      else:
137          manyTables = 1 
138          table=string.split(stat_split[4],",") #includes names of tables
139      if stat_split[2]=='*':
140          allAttributes = 1            # control for * in SQL statement
141          for z in table:
142              cursor4=db.cursor()
143              cursor4.execute("describe %s" %(z))
144              numrows = int(cursor4.rowcount)
145              for x in range(0,numrows): 
146                  row = cursor4.fetchone()
147                  pom1_attr.append(row[0])   
148              cursor4.close
149            #control for the end of attributes in first,second...tables
150              pom1_attr.append('***')           
151      else:
152          pom_attr=stat_split[2]
153    else:
154     
155      if len(string.split(stat_split[3],","))==1:
156            table.append(stat_split[3])
157      else:
158          manyTables = 1
159          table=string.split(stat_split[3],",") #includes names of tables
160      if stat_split[1]=='*':
161          allAttributes = 1
162          for z in table:
163              cursor4=db.cursor()
164              cursor4.execute("describe %s" %(z))
165              numrows = int(cursor4.rowcount)
166              for x in range(0,numrows): 
167                  row = cursor4.fetchone()
168                  pom1_attr.append(row[0]) 
169              cursor4.close
170              #control for the end of attributes in first,second...tables
171              pom1_attr.append('***') 
172          # select statement with describe...
173      else:   
174          pom_attr=stat_split[1]
175
176    if not allAttributes:
177        pom1_attr=string.split(pom_attr,",")
178    attr_count=len(pom1_attr)
179 
180    #init variables
181    cursor2=db.cursor()
182    table_count=0
183    position_attr=-1
184    hasClass = 0
185    names=[]
186    list_of_values = []
187    attrNames = []
188    intAttrs = []
189    pos_int = []
190    for x in pom1_attr:            # pom1_attr  'table1.field1','table2.field2' ...
191      if x=='***':
192        table_count += 1
193      else:
194        position_attr += 1
195        attr=string.split(x,".")#attr 'table1','field1'
196        if manyTables:
197          if not allAttributes:            # there are more than one tables       
198             field=attr[1]
199             table_pom=attr[0]
200          else:
201             field=attr[0]
202             table_pom=table[table_count]
203        else:                              # there is only a single table
204              field=attr[0]
205              table_pom=table[0]
206
207        cursor2.execute("show columns from %s like '%s' " %(table_pom,field))
208        row2=cursor2.fetchone()
209        pom2_values=row2[1]
210        pom2_values=pom2_values[5:]
211        pom2_values=pom2_values[:-1]
212       
213        values=string.split(row2[1],"(")  #values become second field, where the type of the field is
214        attr_type=values[0]
215        list_of_values.append(pom2_values)
216        attr_type=string.upper(attr_type)
217
218        if len(field)>2 and field[1] == "$" and field[0] in "cm":
219          pdAttrName = field[0]
220          attrName = field[2:]
221          if pdAttrName == "c":
222            if hasClass:
223              raise "SQL statement error: more than one class attribute specified"
224            else:
225              hasClass = 1
226        else:
227          attrName = field
228          pdAttrName = ""
229
230        if attr_type=='INT' or attr_type=='INTEGER' or attr_type=='BIGINT':
231            pos_int.append(position_attr)
232
233        if attr_type in ["ENUM", "BIT", "BOOLEAN", "BINARY"]:
234          pdAttrName += "D"
235        elif attr_type in ["FLOAT", "REAL", "DOUBLE"]:
236          pdAttrName += "C"
237        elif attr_type in ["CHAR", "TEXT", "STRING", "DATE", "TIME", "VARCHAR", "TIMESTAMP", "LONGVARCHAR"]:
238          pdAttrName += "S"
239        elif attr_type in ["INT", "INTEGER", "BIGINT", "SMALLINT", "DECIMAL", "TINYINT"]:
240            intAttrs.append(position_attr)
241            cursor3=db.cursor()
242            cursor3.execute('select distinct %s from %s order by %s asc' %(field,table_pom,field))
243            numrows=int(cursor3.rowcount)
244            if numrows > 10:
245              pdAttrName += "C"
246            else:
247                for z in range(0,numrows):
248                   
249                    row=(cursor3.fetchone())[0]
250                    print row
251                    row=int(row)   
252                    if row<0 or row>9:
253                        pdAttrName += "C"
254                        break
255                else:
256                    pdAttrName += "D"
257            cursor3.close
258        else:
259            raise "cannot convert SQL values of type '%s' to Orange" % attr_type
260
261        names.append(pdAttrName + "#" + attrName)
262        attrNames.append(attrName)
263        cursor2.close
264
265    if use:
266      if self.SQLDomainDepot.checkDomain(use, names):
267        domain = use
268      else:
269        raise "the given domain does not match the query"
270    else:
271      domain, metaIDs, isNew = self.SQLDomainDepot.prepareDomain(names)
272       
273    data = orange.ExampleTable(domain)
274
275    # create a cursor
276    cursor=db.cursor()
277    cursor.execute(statement)
278    numrows = int(cursor.rowcount)
279
280    # write data to example table
281    for x in range(0,numrows):
282      example = orange.Example(domain)
283      row = cursor.fetchone()
284      count_pos=0
285      for i, y in enumerate(row):
286         if y != self.special_float_value and y != self.special_value:
287           if i in intAttrs:
288              y=str(int(y))
289
290           var = example[attrNames[i]].variable
291           if type(var) == orange.EnumVariable and y not in var.values:
292             var.values.append(y)
293           example[attrNames[i]] = y
294           
295      data.append(example)
296
297    cursor.close 
298    db.close
299    return data
Note: See TracBrowser for help on using the repository browser.