0001from sqlobject.dbconnection import DBAPI
0002from sqlobject import col
0003MySQLdb = None
0004
0005class MySQLConnection(DBAPI):
0006
0007 supportTransactions = False
0008 dbName = 'mysql'
0009 schemes = [dbName]
0010
0011 def __init__(self, db, user, passwd='', host='localhost', port=None, **kw):
0012 global MySQLdb
0013 if MySQLdb is None:
0014 import MySQLdb
0015 self.module = MySQLdb
0016 self.host = host
0017 self.port = port
0018 self.db = db
0019 self.user = user
0020 self.password = passwd
0021 self.kw = {}
0022 for key in ("unix_socket", "named_pipe", "init_command",
0023 "read_default_file", "read_default_group"):
0024 if key in kw:
0025 self.kw[key] = col.popKey(kw, key)
0026 for key in ("connect_time", "compress", "named_pipe", "use_unicode",
0027 "client_flag", "local_infile"):
0028 if key in kw:
0029 self.kw[key] = int(col.popKey(kw, key))
0030 DBAPI.__init__(self, **kw)
0031
0032 def connectionFromURI(cls, uri):
0033 user, password, host, port, path, args = cls._parseURI(uri)
0034 return cls(db=path.strip('/'), user=user or '', passwd=password or '',
0035 host=host or 'localhost', port=port or 0, **args)
0036 connectionFromURI = classmethod(connectionFromURI)
0037
0038 def makeConnection(self):
0039 try:
0040 conn = self.module.connect(host=self.host, port=self.port,
0041 db=self.db, user=self.user, passwd=self.password, **self.kw)
0042 except self.module.OperationalError, e:
0043 raise self.module.OperationalError(
0044 "%s; used connection string: host=%s, port=%s, db=%s, user=%s, pwd=%s" % (
0045 e, self.host, self.port, self.db, self.user, self.password)
0046 )
0047
0048 if hasattr(conn, 'autocommit'):
0049 conn.autocommit(self.autoCommit)
0050
0051 return conn
0052
0053 def _setAutoCommit(self, conn, auto):
0054 if hasattr(conn, 'autocommit'):
0055 conn.autocommit(auto)
0056
0057 def _executeRetry(self, conn, cursor, query):
0058 while 1:
0059 try:
0060 return cursor.execute(query)
0061 except MySQLdb.OperationalError, e:
0062 if e.args[0] == 2013:
0063 if self.debug:
0064 self.printDebug(conn, str(e), 'ERROR')
0065 else:
0066 raise
0067
0068 def _queryInsertID(self, conn, soInstance, id, names, values):
0069 table = soInstance.sqlmeta.table
0070 idName = soInstance.sqlmeta.idName
0071 c = conn.cursor()
0072 if id is not None:
0073 names = [idName] + names
0074 values = [id] + values
0075 q = self._insertSQL(table, names, values)
0076 if self.debug:
0077 self.printDebug(conn, q, 'QueryIns')
0078 self._executeRetry(conn, c, q)
0079 if id is None:
0080 try:
0081 id = c.lastrowid
0082 except AttributeError:
0083 id = c.insert_id()
0084 if self.debugOutput:
0085 self.printDebug(conn, id, 'QueryIns', 'result')
0086 return id
0087
0088 def _queryAddLimitOffset(self, query, start, end):
0089 if not start:
0090 return "%s LIMIT %i" % (query, end)
0091 if not end:
0092 return "%s LIMIT %i, -1" % (query, start)
0093 return "%s LIMIT %i, %i" % (query, start, end-start)
0094
0095 def createColumn(self, soClass, col):
0096 return col.mysqlCreateSQL()
0097
0098 def createIndexSQL(self, soClass, index):
0099 return index.mysqlCreateIndexSQL(soClass)
0100
0101 def createIDColumn(self, soClass):
0102 return '%s INT PRIMARY KEY AUTO_INCREMENT' % soClass.sqlmeta.idName
0103
0104 def joinSQLType(self, join):
0105 return 'INT NOT NULL'
0106
0107 def tableExists(self, tableName):
0108 for (table,) in self.queryAll('SHOW TABLES'):
0109 if table.lower() == tableName.lower():
0110 return True
0111 return False
0112
0113 def addColumn(self, tableName, column):
0114 self.query('ALTER TABLE %s ADD COLUMN %s' %
0115 (tableName,
0116 column.mysqlCreateSQL()))
0117
0118 def delColumn(self, tableName, column):
0119 self.query('ALTER TABLE %s DROP COLUMN %s' %
0120 (tableName,
0121 column.dbName))
0122
0123 def columnsFromSchema(self, tableName, soClass):
0124 colData = self.queryAll("SHOW COLUMNS FROM %s"
0125 % tableName)
0126 results = []
0127 for field, t, nullAllowed, key, default, extra in colData:
0128 if field == 'id':
0129 continue
0130 colClass, kw = self.guessClass(t)
0131 kw['name'] = soClass.sqlmeta.style.dbColumnToPythonAttr(field)
0132 kw['notNone'] = not nullAllowed
0133 kw['default'] = default
0134
0135
0136 results.append(colClass(**kw))
0137 return results
0138
0139 def guessClass(self, t):
0140 if t.startswith('int'):
0141 return col.IntCol, {}
0142 elif t.startswith('varchar'):
0143 if t.endswith('binary'):
0144 return col.StringCol, {'length': int(t[8:-8]),
0145 'char_binary': True}
0146 else:
0147 return col.StringCol, {'length': int(t[8:-1])}
0148 elif t.startswith('char'):
0149 if t.endswith('binary'):
0150 return col.StringCol, {'length': int(t[5:-8]),
0151 'varchar': False,
0152 'char_binary': True}
0153 else:
0154 return col.StringCol, {'length': int(t[5:-1]),
0155 'varchar': False}
0156 elif t.startswith('datetime'):
0157 return col.DateTimeCol, {}
0158 elif t.startswith('bool'):
0159 return col.BoolCol, {}
0160 elif t.startswith('tinyblob'):
0161 return col.BLOBCol, {"length": 2**8-1}
0162 elif t.startswith('tinytext'):
0163 return col.BLOBCol, {"length": 2**8-1, "varchar": True}
0164 elif t.startswith('blob'):
0165 return col.BLOBCol, {"length": 2**16-1}
0166 elif t.startswith('text'):
0167 return col.BLOBCol, {"length": 2**16-1, "varchar": True}
0168 elif t.startswith('mediumblob'):
0169 return col.BLOBCol, {"length": 2**24-1}
0170 elif t.startswith('mediumtext'):
0171 return col.BLOBCol, {"length": 2**24-1, "varchar": True}
0172 elif t.startswith('longblob'):
0173 return col.BLOBCol, {"length": 2**32}
0174 elif t.startswith('longtext'):
0175 return col.BLOBCol, {"length": 2**32, "varchar": True}
0176 else:
0177 return col.Col, {}