alembic handles no primary key table

[junepeach]

I know it is not good idea that there is no primary key in a table. But if some people just want to design a table like that, how can alembic tool handle that? It seems Alembic doesn't allow to create a non-primary key table.

Thanks a lot and have a good weekend!

[Michael Bayer]

On Nov 30, 2012, at 5:13 PM, junepeach wrote:

> I know it is not good idea that there is no primary key in a table. But if some people just want to design a table like that, how can alembic tool handle that? It seems Alembic doesn't allow to create a non-primary key table.
>
> Thanks a lot and have a good weekend!

alembic has no dependency on primary key columns (need detail on "it seems"). the SQLAlchemy ORM does, but that's a different issue.

[junepeach]

I have 2 simple tables defined as below:
class Test1(Base):
__tablename__ = 'test1'
id = Column(Integer, primary_key = True)
name = Column(String(15))

class Test2(Base):
__tablename__ = 'test2'
fid = Column(Integer, ForeignKey('test1.id'))
tname = Column(String(15))

You can see that Test2 is a table without a primary key. When I run 'alembic revision --autogenerate' to create migration script, and got below error:
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/__init__.py", line 1129, in mapper
return Mapper(class_, local_table, *args, **params)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/mapper.py", line 203, in __init__
self._configure_pks()
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/mapper.py", line 767, in _configure_pks
(self, self.mapped_table.description))
sqlalchemy.exc.ArgumentError: Mapper Mapper|Test2|test2 could not assemble any primary key columns for mapped table 'test2'

So I wonder how to create a migration script for a table without primary key. Non-primary key is not good design, but acceptable in mysql, sqlite, postgresql.

Thank you very much!

[Werner]

Not an expert, but above is using the SA orm which requires a primary
key, you need to define the table using:
http://sqlalchemy.readthedocs.org/en/rel_0_8/core/schema.html#metadata-describing

Werner

[Michael Bayer]

On Dec 1, 2012, at 11:49 AM, junepeach wrote:

> I have 2 simple tables defined as below:
> class Test1(Base):
> __tablename__ = 'test1'
> id = Column(Integer, primary_key = True)
> name = Column(String(15))
>
> class Test2(Base):
> __tablename__ = 'test2'
> fid = Column(Integer, ForeignKey('test1.id'))
> tname = Column(String(15))
>
> You can see that Test2 is a table without a primary key. When I run 'alembic revision --autogenerate' to create migration script, and got below error:
> File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/__init__.py", line 1129, in mapper
> return Mapper(class_, local_table, *args, **params)
> File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/mapper.py", line 203, in __init__
> self._configure_pks()
> File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/mapper.py", line 767, in _configure_pks
> (self, self.mapped_table.description))
> sqlalchemy.exc.ArgumentError: Mapper Mapper|Test2|test2 could not assemble any primary key columns for mapped table 'test2'

that is an ORM error. an ORM mapped table requires a primary key - background on that is here: http://www.sqlalchemy.org/trac/wiki/FAQ#IhaveaschemawheremytabledoesnthaveaprimarykeycanSAsORMhandleit . Note that the mapping can be handed a list of Column objects which comprise the primary key even without the primary_key=True flag being present on those columns.

This is not related to Alembic. With Alembic, you define the creation of new tables in a migration script using the create_table() directive: https://alembic.readthedocs.org/en/latest/ops.html#alembic.operations.Operations.create_table

So here, if you're looking for "autogenerate" to pick up on a table that's in your MetaData, and it's not an ORM mapped table, you'd use Table:

Table('mytablewithnopk', Base.metadata, Column(...), Column(...))

background on Table: http://docs.sqlalchemy.org/en/rel_0_8/core/schema.html#describing-databases-with-metadata

Hope this helps.

[junepeach]

THanks Werner and Michiael. Yeah ORM requires a primary key for every table. Michiael, I don't have a Metadata yet. I am creating a migration script to be shared among several databases. We will use this script to create tables schema in several databases. So, should we add a primary for this non-primary key table? Thanks you very much!

[Michael Bayer]

On Dec 2, 2012, at 5:00 PM, junepeach wrote:

> Michiael, I don't have a Metadata yet.

yes you do, your Base has one as Base.metadata

> So, should we add a primary for this non-primary key table? Thanks you very much!

if you don't want the table having a primary key, then don't add a primary key to it.

[junepeach]

Thank you very much, Machiael. Yes, it helps a lot. So, to create migration script, when i should use ORM, and when I should use MetaData object? What is the difference? I have been confused about this two for long time. Please give more detail if you can :) Thanks again!