I also have a somewhat more experimental patch that tries to parse
everything from the SHOW CREATE TABLE. It needs (a lot) more testing
than I have time for right now (it's pretty late here), but I wanted
to give you the opportunity to take a look at it.
Things I'm not sure about:
1. What it's supposed to do if there is more than one column listed
in the PRIMARY KEY. Currently it adds them to the table's list of
primary keys directly... is this right?
2. What to do about cases that SQLAlchemy doesn't seem to support
(e.g. constraints, foreign keys with more than one column).
3. The regexps can probably be tweaked a bit (e.g. I haven't been
consistent about using \s vs space characters, and there might be
better ways to match some constructs).
Also, I'm quite certain that many people reading this list are more
experienced Python programmers than me, so please forgive any
stylistic blunders or other newbie errors :-)
Kind regards,
Alastair.
--
http://www.alastairs-place.net
Index: lib/sqlalchemy/databases/mysql.py
===================================================================
--- lib/sqlalchemy/databases/mysql.py (revision 919)
+++ lib/sqlalchemy/databases/mysql.py (working copy)
@@ -92,6 +92,65 @@
'blob' : MSBinary,
}
+class MySQLParseError (Exception):
+ """Raised if there is a problem parsing the result of SHOW
CREATE TABLE."""
+ pass
+
+coloptspat = (r'\s+'
+ r'(?P<option>NOT NULL'
+ r'|NULL'
+ r"|DEFAULT\s+(?P<default>'([^']|'')*'|\d+|NULL)"
+ r'|AUTO_INCREMENT'
+ r'|PRIMARY KEY'
+ r'|KEY'
+ r"|COMMENT\s+'(?P<comment>([^']|'')*)'"
+ r"|REFERENCES\s+`(?P<table>\w+)`(?:\s+\((?P<refcols>.*)
\))?"
+ r'(?: MATCH FULL)?(?: MATCH PARTIAL)?'
+ r'(?: ON (?:DELETE|UPDATE) '
+ r'(?:RESTRICT|CASCADE|SET NULL|NO ACTION|SET
DEFAULT))?)')
+coloptsregexp = re.compile(coloptspat, re.IGNORECASE)
+
+def parse_column_options(options):
+ """Parses MySQL column options from SHOW CREATE TABLE. Returns
a tuple
+ (nullable, default, auto_increment, primary_key,
foreign_key)."""
+ nullable = True
+ default = None
+ primary_key = False
+ foreign_key = None
+
+ pos = 0
+
+ while pos < len(options):
+ m = coloptsregexp.match(options, pos)
+ if not m:
+ break
+
+ pos = m.end()
+ kind = m.group('option').upper()
+
+ if kind == 'NOT NULL':
+ nullable = False
+ elif kind == 'NULL':
+ nullable = True
+ elif kind == 'PRIMARY KEY':
+ primary_key = True
+ elif kind.startswith('COMMENT'):
+ comment = m.group('comment')
+ elif kind.startswith('REFERENCES'):
+ ref_table = m.group('table')
+ ref_columns = m.group('refcols').split(',')
+
+ # What to do if ref_columns contains more than one entry?
+ if len(ref_columns) > 1:
+ raise MySQLParseError('Foreign key too complex')
+
+ ref_column = ref_columns[0].strip(' \t`')
+ foreign_key = ref.table + '.' + ref.column
+ elif kind.startswith('DEFAULT'):
+ default = m.group('default')
+
+ return (nullable, default, primary_key, foreign_key)
+
def engine(opts, **params):
return MySQLEngine(opts, **params)
@@ -163,28 +222,89 @@
def reflecttable(self, table):
# to use information_schema:
#ischema.reflecttable(self, table, ischema_names,
use_mysql=True)
+
+ c = self.execute("show create table " + table.name, {})
+ row = c.fetchone()
+ create = row[1].split('\n')
+
+ # Get the engine and character set
+ m = re.match(r'\) (?:ENGINE|TYPE)=(\w+) DEFAULT CHARSET=(\w+)',
+ create[-1])
+ if m:
+ engine = m.group(1)
+ charset = m.group(2)
+ else:
+ engine = None
+ charset = None
+
+ table.mysql_engine = engine
- c = self.execute("describe " + table.name, {})
- while True:
- row = c.fetchone()
- if row is None:
- break
- #print "row! " + repr(row)
- (name, type, nullable, primary_key, default) = (row[0],
row[1], row[2] == 'YES', row[3] == 'PRI', row[4])
+ normalpat = (r'(?i)\s*`([^`]+)` (\w+)(?:\((.*)\))?'
+ r'(?: UNSIGNED)?(?: ZEROFILL)?')
+ fkeypat = (r'\s*(?:CONSTRAINT `\w+` )?'
+ r'FOREIGN KEY(?:\s+`\w+`)? '
+ r'\(`(?P<fkey>\w+)`\) REFERENCES `(?P<table>\w+)` '
+ r'\((?P<cols>.*)\)')
+ pkeypat = (r'\s*(?:CONSTRAINT `\w+` )?'
+ r'PRIMARY KEY\s+(?:USING \w+ )?\((.*)\)')
+ fkeys = {}
+ pkeys = []
+
+ # Go through the remainder of the lines looking for keys
+ for line in create[1:-1]:
+ m = re.match(fkeypat, line)
+
+ if m:
+ cols = m.group('cols').split(',')
+ if len(cols) > 1:
+ raise MySQLParseError('Foreign key too complex')
+ col = cols[0].strip().strip('`')
+ fkeys[m.group('fkey')] = m.group('table') + '.' + col
+ continue
+
+ m = re.match(pkeypat, line)
+
+ if m:
+ pks = m.group(1).split(',')
+ for pk in pks:
+ pkeys.append(pk.strip().strip('`'))
+
+ # Now go through the remainder of the lines generating columns
+ for line in create[1:-1]:
+ # First look for a normal column
+ m = re.match(normalpat, line)
+
+ if not m:
+ continue
- match = re.match(r'(\w+)(\(.*?\))?', type)
- coltype = match.group(1)
- args = match.group(2)
-
- #print "coltype: " + repr(coltype) + " args: " + repr(args)
- coltype = ischema_names.get(coltype, MSString)
+ # This is a column definition
+ (name, type, args) = m.groups()
+
+ coltype = ischema_names.get(type, MSString)
if args is not None:
args = re.findall(r'(\d+)', args)
- #print "args! " +repr(args)
coltype = coltype(*[int(a) for a in args])
- table.append_item(schema.Column(name, coltype,
primary_key=primary_key, nullable=nullable, default=default))
-
+
+ (nullable, default, primary_key, foreign_key) =
parse_column_options(line[m.end():])
+ foreign_key = fkeys.get(name, foreign_key)
+
+ arglist = []
+ if foreign_key:
+ arglist.append(schema.ForeignKey(foreign_key))
+
+ table.append_item(schema.Column(name, coltype, *arglist,
+ **dict
(primary_key=primary_key,
+ nullable=nullable,
+ default=default)))
+
+ # Finally, add all the primary keys
+ if len(pkeys):
+ for name in pkeys:
+ table.primary_key.append(table.columns[name])
+ if len(pkeys) == 1:
+ table.columns[name].primary_key = True
+
class MySQLTableImpl(sql.TableImpl):
"""attached to a schema.Table to provide it with a Selectable
interface
as well as other functions
-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems? Stop! Download the new AJAX search engine that makes
searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
