Ok, I want use the customer sequence in the postgresql primary key.
so I define a sequence in the primary key column as default.
but It’s seem not work.
==================================== list 1
Python 2.6.4 (r264:75706, Feb 24 2010, 22:27:51)
[GCC 4.2.1 (Apple Inc. build 5646) (dot 1)] on darwin
Type “help”, “copyright”, “credits” or “license” for more information.
>>> import sqlalchemy
>>> print sqlalchemy.__version__
0.6beta3
>>> from sqlalchemy import create_engine
>>> engine = create_engine(’postgresql://postgres::@localhost/sqlalchemy’, echo=True)
>>> from sqlalchemy import Table, Column, Integer, String, MetaData
>>> from sqlalchemy import Sequence
>>> metadata = MetaData()
>>> users = Table(’users’, metadata,
… Column(’id’, Integer, default=Sequence(’users_pk_seq’, start=100), primary_key=True),
… Column(’name’, String),
… Column(’fullname’, String),
… )
>>>
>>> metadata.create_all(engine)
2010-04-07 20:22:30,209 INFO sqlalchemy.engine.base.Engine.0x…ab50 select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and lower(relname)=%(name)s
2010-04-07 20:22:30,210 INFO sqlalchemy.engine.base.Engine.0x…ab50 {’name’: u’users’}
2010-04-07 20:22:30,219 INFO sqlalchemy.engine.base.Engine.0x…ab50 SELECT relname FROM pg_class c join pg_namespace n on n.oid=c.relnamespace where relkind=’S’ and n.nspname=current_schema() and lower(relname)=%(name)s
2010-04-07 20:22:30,220 INFO sqlalchemy.engine.base.Engine.0x…ab50 {’name’: u’users_pk_seq’}
2010-04-07 20:22:30,221 INFO sqlalchemy.engine.base.Engine.0x…ab50 CREATE SEQUENCE users_pk_seq START WITH 100 // the sequence is created
2010-04-07 20:22:30,221 INFO sqlalchemy.engine.base.Engine.0x…ab50 {}
2010-04-07 20:22:30,346 INFO sqlalchemy.engine.base.Engine.0x…ab50 COMMIT
2010-04-07 20:22:30,348 INFO sqlalchemy.engine.base.Engine.0x…ab50
CREATE TABLE users (
id INTEGER NOT NULL, // but there is not use the ‘users_pk_seq’ sequence.this column hope (id INTEGER DEFAULT nextval(users_pk_seq) NOT NULL,)
name VARCHAR,
fullname VARCHAR,
PRIMARY KEY (id)
)
2010-04-07 20:22:30,348 INFO sqlalchemy.engine.base.Engine.0x…ab50 {}
2010-04-07 20:22:30,410 INFO sqlalchemy.engine.base.Engine.0x…ab50 COMMIT
>>>
==================================== list 1
the sqlalchemy schema
==================================== list 2
sqlalchemy=# \d users;
Table “public.users”
Column | Type | Modifiers
———-+——————-+———–
id | integer | not null
name | character varying |
fullname | character varying |
Indexes:
“users_pkey” PRIMARY KEY, btree (id)
sqlalchemy=#
==================================== list 2
so I hack a file in the sqlalchemy
this is the diff file.
OK, if you have another better way,I will thank you if you tell me.
—–
the new version
==================================== list 3
>>> metadata.create_all(engine)
2010-04-07 20:49:12,602 INFO sqlalchemy.engine.base.Engine.0x…ab90 select version()
2010-04-07 20:49:12,602 INFO sqlalchemy.engine.base.Engine.0x…ab90 {}
2010-04-07 20:49:12,604 INFO sqlalchemy.engine.base.Engine.0x…ab90 select current_schema()
2010-04-07 20:49:12,604 INFO sqlalchemy.engine.base.Engine.0x…ab90 {}
2010-04-07 20:49:12,607 INFO sqlalchemy.engine.base.Engine.0x…ab90 select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and lower(relname)=%(name)s
2010-04-07 20:49:12,607 INFO sqlalchemy.engine.base.Engine.0x…ab90 {’name’: u’users’}
2010-04-07 20:49:12,610 INFO sqlalchemy.engine.base.Engine.0x…ab90 SELECT relname FROM pg_class c join pg_namespace n on n.oid=c.relnamespace where relkind=’S’ and n.nspname=current_schema() and lower(relname)=%(name)s
2010-04-07 20:49:12,610 INFO sqlalchemy.engine.base.Engine.0x…ab90 {’name’: u’users_pk_seq’}
2010-04-07 20:49:12,611 INFO sqlalchemy.engine.base.Engine.0x…ab90
CREATE TABLE users (
id INTEGER DEFAULT nextval(’users_pk_seq’) NOT NULL, // bingo.
name VARCHAR,
fullname VARCHAR,
PRIMARY KEY (id)
)
2010-04-07 20:49:12,612 INFO sqlalchemy.engine.base.Engine.0x…ab90 {}
2010-04-07 20:49:12,694 INFO sqlalchemy.engine.base.Engine.0x…ab90 COMMIT
==================================== list 3
in postgresql
==================================== list 4
sqlalchemy=# \d users;
Table “public.users”
Column | Type | Modifiers
———-+——————-+—————————————————-
id | integer | not null default nextval(’users_pk_seq’::regclass)
name | character varying |
fullname | character varying |
Indexes:
“users_pkey” PRIMARY KEY, btree (id)
sqlalchemy=#
==================================== list 4
here is the patch file
http://my.unix-center.net/~WeiZhicheng/wp-content/sa_sequence.patch




