Why Isn't SQLAlchemy Creating Serial Columns?
Solution 1:
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)
)
Solution 2:
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)
Solution 3:
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
Solution 4:
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.
Post a Comment for "Why Isn't SQLAlchemy Creating Serial Columns?"