Why isn't SQLAlchemy creating serial columns?

Question

SQLAlchemy is generating, but not enabling, sequences for columns in postgresql. I suspect I may be doing something wrong in engine setup.

Using an example from the SQLAlchemy tutorial (http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html):

#!/usr/bin/env python

from sqlalchemy import create_engine, Column, Integer, String, Sequence
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
    name = Column(String(50))
    fullname = Column(String(50))
    password = Column(String(12))

    def __repr__(self):
        return "<User(name='%s', fullname='%s', password='%s')>" % (
                                self.name, self.fullname, self.password)

db_url = 'postgresql://localhost/serial'
engine = create_engine(db_url, echo=True)
Base.metadata.create_all(engine)

With this script, the following table is generated:

serial=# \d+ users
                                 Table "public.users"
  Column  |         Type          | Modifiers | Storage  | Stats target | Description 
----------+-----------------------+-----------+----------+--------------+-------------
 id       | integer               | not null  | plain    |              | 
 name     | character varying(50) |           | extended |              | 
 fullname | character varying(50) |           | extended |              | 
 password | character varying(12) |           | extended |              | 
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
Has OIDs: no

However, a sequence was created:

serial=# select sequence_schema,sequence_name,data_type from information_schema.sequences ;
 sequence_schema | sequence_name | data_type 
-----------------+---------------+-----------
 public          | user_id_seq   | bigint

SQLAlchemy 0.9.1, Python 2.7.5+, Postgresql 9.3.1, Ubuntu 13.10

-Reece

Plain `\d users`? Is there a `default nextval('users_id_seq')` in there? — Craig Ringer, Jan 24 '14 at 09:34
Check if your CREATE TABLE statement is correct, using instructions from here: http://docs.sqlalchemy.org/en/latest/faq.html#how-can-i-get-the-create-table-drop-table-output-as-a-string . Also, I use Postgres and I don't explictly define sequences, I let Postgres' SERIAL type do it for me. I just define `id = Column(Integer, primary_key=True)` and SQL Alchemy by default sets this as the SERIAL type — dtheodor, Jan 24 '14 at 14:12
That's it, @dtheodor! Teaches me to not read the docs! (Actually SQLAlchemy's docs are some of the best around.) Thanks. — Reece, Jan 24 '14 at 16:34
@dtheodor the link is no longer active and I am having the same issue. Have a different link? I'd like to check it out as I too have migrated from `sqllite3` to `postgresql` http://stackoverflow.com/questions/33225636/integrityerror-error-null-value-in-column-user-id-violates-not-null-constrai?noredirect=1#comment54286858_33225636 — thesayhey, Oct 20 '15 at 17:06
@thesayhey Looks like it was moved here: http://docs.sqlalchemy.org/en/rel_0_9/faq/metadata_schema.html#how-can-i-get-the-create-table-drop-table-output-as-a-string (found by Googling "sqlalchemy how-can-i-get-the-create-table-drop-table-output-as-a-string") — Reece, Oct 20 '15 at 21:14
Thanks @Reece ! It's good to spell it out for the next person I suppose ;) — thesayhey, Oct 20 '15 at 23:02
Yes, sqlalchemy's documentation refactor destroyed all previously valid urls... You have to go back to version 0.8.0 to get the old documentation layout. http://docs.sqlalchemy.org/en/rel_0_8/faq.html#how-can-i-get-the-create-table-drop-table-output-as-a-string — dtheodor, Nov 17 '15 at 09:30

score 45 · Accepted Answer · answered Jan 25 '14 at 03:31

this is because you provided it with an explicit Sequence. The SERIAL datatype in postgresql generates its own sequence, which SQLAlchemy knows how to locate - so if you omit the Sequence, SQLAlchemy will render SERIAL, assuming the intent is that the column is auto-incrementing (which is determined by the autoincrement argument in conjunction with Integer primary_key; it defaults to True). But when Sequence is passed, SQLAlchemy sees the intent that you don't want the sequence implicitly created by SERIAL but instead the one you are specifying:

from sqlalchemy import create_engine, Column, Integer, String, Sequence
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class T1(Base):
    __tablename__ = 't1'

    # emits CREATE SEQUENCE + INTEGER
    id = Column(Integer, Sequence('user_id_seq'), primary_key=True)

class T2(Base):
    __tablename__ = 't2'

    # emits SERIAL
    id = Column(Integer, primary_key=True)

class T3(Base):
    __tablename__ = 't3'

    # emits INTEGER
    id = Column(Integer, autoincrement=False, primary_key=True)

engine = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.create_all(engine)

output:

CREATE SEQUENCE user_id_seq

CREATE TABLE t1 (
    id INTEGER NOT NULL, 
    PRIMARY KEY (id)
)


CREATE TABLE t2 (
    id SERIAL NOT NULL, 
    PRIMARY KEY (id)
)


CREATE TABLE t3 (
    id INTEGER NOT NULL, 
    PRIMARY KEY (id)
)

Thanks, Mike. dtheodor's advice (== your class T2) is working perfectly. The confusion was that the tutorial shows another way that I was expecting to generate SERIAL. Now I understand why those are different. — Reece, Jan 25 '14 at 20:40

score 3 · Answer 2 · answered Jun 22 '15 at 20:40

If you need to create the sequence explicitly for some reason, like setting a start value, and still want the same default value behavior as when using the Column(Integer, primary_key=True) notation, it can be accomplished with the following code:

#!/usr/bin/env python

from sqlalchemy import create_engine, Column, Integer, String, Sequence
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
USER_ID_SEQ = Sequence('user_id_seq')  # define sequence explicitly
class User(Base):
    __tablename__ = 'users'
    # use sequence in column definition, and pass .next_value() as server_default
    id = Column(Integer, USER_ID_SEQ, primary_key=True, server_default=USER_ID_SEQ.next_value())
    name = Column(String(50))
    fullname = Column(String(50))
    password = Column(String(12))

    def __repr__(self):
        return "<User(name='%s', fullname='%s', password='%s')>" % (
                                self.name, self.fullname, self.password)

db_url = 'postgresql://localhost/serial'
engine = create_engine(db_url, echo=True)
Base.metadata.create_all(engine)

from where did `USER_ID_SEQ ` came from ? – Ciasto piekarz Aug 20 '17 at 16:15 — Ciasto piekarz, Aug 20 '17 at 16:15

score 0 · Answer 3 · answered Oct 31 '15 at 19:40

Reece

I also used that tutorial as a model, and just could not get it to work with any Postgres tables that already existed and had key ID columns with serial sequences to generate the new key ID values.

Like David, I found the Sequence had to be defined separately to the class. For anyone using the "db.Model" approach, here's one example.

from flask.ext.sqlalchemy import SQLAlchemy
from sqlalchemy import Sequence
db = SQLAlchemy()

pageimpression_imp_id_seq = Sequence('pageimpression_imp_id_seq')
class PageImpression(db.Model):
        __tablename__ = 'pageimpression'
        imp_id = db.Column(db.Integer,     
    pageimpression_imp_id_seq,           
    server_default=usersession_sessionid_seq.next_value(),primary_key=True)
    logdate = db.Column(db.DateTime)
    sessionid = db.Column(db.String)
    path = db.Column(db.String)
    referrer = db.Column(db.String)

def __init__(self, imp_id, logdate, sessionid, path, referrer):
    self.imp_id = imp_id
    self.logdate = logdate
    self.sessionid = sessionid
    self.path = path
    self.referrer = referrer

def __repr__(self):
   return "<PageImpression(imp_id='%s', logdate='%s',sessionid='%s', path='%s', referrer='%s')>" % (self.imp_id, self.logdate, self.sessionid, self.path, self.referrer)

def PageImpressionAdd(sessionid):
    sessionid = 0 # dummy value for unit testing
    current_time = datetime.now().isoformat()
    if CurrentConfig.IMPRESSION_LOGGING_ON == True:     
        path = request.path
        if request.environ.get('HTTP_REFERER') and not request.environ.get('HTTP_REFERER').isspace():
            referrer = request.environ.get('HTTP_REFERER') # the string is not-empty
        else:
            referrer = '' # the string is empty
        from website.models import PageImpression
        thisPageImpression = PageImpression(None,current_time,sessionid, path, referrer)
        db.session.add(thisPageImpression)
        db.session.commit()
        # get the values created by the Postgres table defaults
        imp_id = thisPageImpression.imp_id
        logdate = thisPageImpression.logdate
    return current_time

score 0 · Answer 4 · answered Aug 10 '21 at 12:07

0

You can also change Sequence without any SQL script by GUI pgAdmin as below:

select your DB -> Schemas -> public -> Sequences -> right click -> properties -> Definition -> Current value.

answered Aug 10 '21 at 12:07

Fisal Assubaieye

306
4
6

Why isn't SQLAlchemy creating serial columns?

4 Answers4

Linked