Michael Bayer wrote:
> ok, great. if you want to look at the code, its pretty easy, its lib/
> sqlalchemy/databases/mysql.py line 163, "reflecttable". Right now its
> just "describe", loop through rows, a little parsing, then
> table.append_item(Column). thanks !
>
> - mike
>
See if this gets us at least partway down the road. Seems to work:
In [12]: c = engine.execute("SHOW CREATE TABLE child", {})
In [13]: row = c.fetchone()
In [14]: print row[0]
child
In [15]: print row[1]
CREATE TABLE `child` (
`id` int(11) default NULL,
`parent_id` int(11) default NULL,
KEY `par_ind` (`parent_id`),
CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES
`parent` (`id`) ON DELETE CASCADE
) TYPE=InnoDB
In [16]: Table('child', engine, autoload=True)
Out[16]:
Table('child',
<sqlalchemy.databases.mysql.MySQLEngine object at 0xb787a2cc>,
Column('id',MSInteger(),key='id',primary_key=False,nullable=True,hidden=False,default=None),
Column('parent_id',MSInteger(),ForeignKey('parent.id'),key='parent_id',primary_key=False,nullable=True,hidden=False,default=None),
schema=None)
-- Wade
Index: mysql.py
===================================================================
--- mysql.py (revision 915)
+++ mysql.py (working copy)
@@ -164,6 +164,9 @@
# to use information_schema:
#ischema.reflecttable(self, table, ischema_names, use_mysql=True)
+ tabletype, foreignkeyD = self.moretableinfo(table=table)
+ # XXX tabletype == 'InnoDB' or 'MyISAM', what do we do with it?
+
c = self.execute("describe " + table.name, {})
while True:
row = c.fetchone()
@@ -182,7 +185,43 @@
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))
+
+ arglist = []
+ fkey = foreignkeyD.get(name)
+ if fkey is not None:
+ arglist.append(schema.ForeignKey(fkey))
+
+ table.append_item(schema.Column(name, coltype, *arglist,
+ **dict(primary_key=primary_key,
+ nullable=nullable,
+ default=default
+ )))
+
+ def moretableinfo(self, table):
+ """Return (tabletype, {colname:foreignkey...}
+ execute(SHOW CREATE TABLE child) =>
+ 'CREATE TABLE `child` (\n `id` int(11) default NULL,\n `parent_id` int(11) default NULL,\n KEY `par_ind` (`parent_id`),\n CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE\n) TYPE=InnoDB'
+ """
+ c = self.execute("SHOW CREATE TABLE " + table.name, {})
+ desc = c.fetchone()[1].strip()
+ match = re.search(r'\)\s*TYPE=(?P<ttype>.+)$', desc, re.I)
+ if match:
+ tabletype = match.group('ttype')
+ else:
+ tabletype = ''
+ desc = re.sub(r'^[^\(]*\(', '', desc) # remove thru first paren
+ desc = re.sub(r'\)[^\)]*$', '', desc) # remove last paren onward
+ fkpat = (r'FOREIGN KEY\s*\(`?(?P<name>.+?)`?\)'
+ r'\s*REFERENCES\s*`?(?P<reftable>.+?)`?'
+ r'\s*\(`?(?P<refcol>.+?)`?\)'
+ )
+ foreignkeyD = {}
+ L = desc.split(',')
+ for s in L:
+ match = re.search(fkpat, s, re.I)
+ if match:
+ foreignkeyD[match.group('name')] = match.group('reftable') + '.' + match.group('refcol')
+ return (tabletype, foreignkeyD)
class MySQLTableImpl(sql.TableImpl):
