Source Code for Module json_to_relation.mysqldb

  1  ''' 
  2  Created on Sep 24, 2013 
  3   
  4  @author: paepcke 
  5  ''' 
  6   
  7  import pymysql 
  8   
  9   
 10 -class MySQLDB(object): 
 11      ''' 
 12      Shallow interface to MySQL databases. Some niceties nonetheless. 
 13      The query() method is an iterator. So:: 
 14          for result in mySqlObj.query('SELECT * FROM foo'): 
 15              print result 
 16      ''' 
 17   
 18 -    def __init__(self, host='127.0.0.1', port=3306, user='root', passwd='', db='mysql'): 
 19           
 20          # If all arguments are set to None, we are unittesting: 
 21          if all(arg is None for arg in (host,port,user,passwd,db)): 
 22              return 
 23           
 24          self.cursors = [] 
 25          try: 
 26              self.connection = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db) 
 27          except pymysql.OperationalError: 
 28              pwd = '...............' if len(passwd) > 0 else '<no password>' 
 29              raise ValueError('Cannot reach MySQL server with host:%s, port:%s, user:%s, pwd:%s, db:%s' % 
 30                               (host, port, user, pwd, db)) 
 31           
 32 -    def close(self): 
 33          ''' 
 34          Close all cursors that are currently still open. 
 35          ''' 
 36          for cursor in self.cursors: 
 37              try: 
 38                  cursor.close() 
 39              except: 
 40                  pass 
 41   
 42 -    def createTable(self, tableName, schema): 
 43          ''' 
 44          Create new table, given its name, and schema. 
 45          The schema is a dict mappingt column names to  
 46          column types. Example: {'col1' : 'INT', 'col2' : 'TEXT'} 
 47          @param tableName: name of new table 
 48          @type tableName: String 
 49          @param schema: dictionary mapping column names to column types 
 50          @type schema: Dict<String,String> 
 51          ''' 
 52          colSpec = '' 
 53          for colName, colVal in schema.items(): 
 54              colSpec += str(colName) + ' ' + str(colVal) + ',' 
 55          cmd = 'CREATE TABLE IF NOT EXISTS %s (%s) ' % (tableName, colSpec[:-1]) 
 56          cursor = self.connection.cursor() 
 57          try: 
 58              cursor.execute(cmd) 
 59              self.connection.commit() 
 60          finally: 
 61              cursor.close() 
 62   
 63 -    def dropTable(self, tableName): 
 64          ''' 
 65          Delete table safely. No errors 
 66          @param tableName: name of table 
 67          @type tableName: String 
 68          ''' 
 69          cursor = self.connection.cursor() 
 70          try: 
 71              cursor.execute('DROP TABLE IF EXISTS %s' % tableName) 
 72              self.connection.commit() 
 73          finally: 
 74              cursor.close() 
 75   
 76 -    def insert(self, tblName, colnameValueDict): 
 77          ''' 
 78          Given a dictionary mapping column names to column values, 
 79          insert the data into a specified table 
 80          @param tblName: name of table to insert into 
 81          @type tblName: String 
 82          @param colnameValueDict: mapping of column name to column value 
 83          @type colnameValueDict: Dict<String,Any> 
 84          ''' 
 85          colNames, colValues = zip(*colnameValueDict.items()) 
 86          cursor = self.connection.cursor() 
 87          try: 
 88              cmd = 'INSERT INTO %s (%s) VALUES (%s)' % (str(tblName), ','.join(colNames), self.ensureSQLTyping(colValues)) 
 89              cursor.execute(cmd) 
 90              self.connection.commit() 
 91          finally: 
 92              cursor.close() 
 93           
 94 -    def ensureSQLTyping(self, colVals): 
 95          ''' 
 96          Given a list of items, return a string that preserves 
 97          MySQL typing. Example: (10, 'My Poem') ---> '10, "My Poem"' 
 98          Note that ','.join(map(str,myList)) won't work: 
 99          (10, 'My Poem') ---> '10, My Poem' 
100          @param colVals: list of column values destined for a MySQL table 
101          @type colVals: <any> 
102          ''' 
103          resList = [] 
104          for el in colVals: 
105              if isinstance(el, basestring): 
106                  resList.append('"%s"' % el) 
107              else: 
108                  resList.append(el) 
109          return ','.join(map(str,resList))         
110           
111 -    def query(self, queryStr): 
112          ''' 
113          Query iterator. Given a query, return one result for each 
114          subsequent call. 
115          @param queryStr: query 
116          @type queryStr: String 
117          ''' 
118          cursor = self.connection.cursor() 
119          # For if caller never exhausts the results by repeated calls: 
120          self.cursors.append(cursor) 
121          cursor.execute(queryStr) 
122          while True: 
123              nextRes = cursor.fetchone() 
124              if nextRes is None: 
125                  cursor.close() 
126                  return 
127              yield nextRes 
128