reflection failure with MySQL: Mapper could not assemble any primary key columns for mapped table

[Felix Schwarz]

Hey,

I'm trying to use reflection with SQLAlchemy 0.8 but I always get this exception:
sqlalchemy.exc.ArgumentError: Mapper Mapper|links|links could not assemble any
primary key columns for mapped table 'links'

mysql> show fields from links;
+--------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------+------+-----+---------+-------+
| l_from | int(8) unsigned | NO | PRI | 0 | |
| l_to | int(8) unsigned | NO | PRI | 0 | |
+--------+-----------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

The class definition is pretty simple:

class links(Base):
__table__ = Table('links', Base.metadata, autoload=True)

Traceback:
File "…/main.py", line 36, in connect_to_db
class links(Base):
File "…/sqlalchemy/ext/declarative.py", line 1343, in __init__
_as_declarative(cls, classname, cls.__dict__)
File "…/sqlalchemy/ext/declarative.py", line 1336, in _as_declarative
**mapper_args)
File "…/sqlalchemy/orm/__init__.py", line 1129, in mapper
return Mapper(class_, local_table, *args, **params)
File "…/sqlalchemy/orm/mapper.py", line 203, in __init__
self._configure_pks()
File "…/sqlalchemy/orm/mapper.py", line 773, in _configure_pks
(self, self.mapped_table.description))

Any idea why this happens and how I fix the problem? (without having to
specify the ORM mapping myself)

fs

[Michael Bayer]

can you send the "SHOW CREATE TABLE", I'll copy it exactly

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>

[Felix Schwarz]

> can you send the "SHOW CREATE TABLE", I'll copy it exactly

CREATE TABLE `links` (
`l_from` int(8) unsigned NOT NULL DEFAULT '0',
`l_to` int(8) unsigned NOT NULL DEFAULT '0',
UNIQUE KEY `l_from` (`l_from`,`l_to`),
KEY `l_to` (`l_to`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1


(sorry for breaking the threading, due to some misconfiguration I saw
Michael's mail only in the web interface.)

[Michael Bayer]

OK well that table has no primary key established.

If I create a table with a PK:

create table test (id integer primary key);

you see PRIMARY KEY in the output:

mysql> show create table test;
+-------+----------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

so in this case you'd need to specify PK cols in your Table or in your mapper def.

[Felix Schwarz]

Am 11.03.2013 17:27, schrieb Michael Bayer:
> OK well that table has no primary key established.

I see. So even if MySQL tells me (in 'show fields') that a column is a primary
key, SQLAlchemy won't recognize it unless the column is explicitely marked as
primary key (as opposed to a unique key).

Makes sense, still a pitty that my DB schema is so broken (it's an old
MediaWiki dump).

fs

[Mauricio de Abreu Antunes]

Yes, you need to tell SQLAlchemy that key is a primary key.

2013/3/12 Felix Schwarz <felix....@oss.schwarz.eu>

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

--

Mauricio de Abreu Antunes

Mobile: (51)930-74-525

Skype: mauricio.abreua

[Michael Bayer]

a unique key is not the same thing as a primary key, and MySQL reflection uses the SHOW CREATE TABLE output to reflect, as we can't get all the information we need in any other way for that platform (one of the many ways MySQL is unlike any other backend).

I suppose the reflection could have some feature where you flag it as, "single unique key w/no primary key means that's the primary key", but we've not had requests for this feature before.

We will at some point be adding a feature whereby one can intercept the event where we reflect the primary key; we have this event for individual columns now. When that event is present, you'll be able to flip on the "primary key" flag for unique column sets as needed.

[Felix Schwarz]

Am 12.03.2013 15:43, schrieb Michael Bayer:
> I suppose the reflection could have some feature where you flag it as,
> "single unique key w/no primary key means that's the primary key", but
> we've not had requests for this feature before.
>
> We will at some point be adding a feature whereby one can intercept the
> event where we reflect the primary key; we have this event for individual
> columns now. When that event is present, you'll be able to flip on the
> "primary key" flag for unique column sets as needed.

That sounds very helpful - looking forward to every new version of SQLAlchemy :-)

fs