[sqlalchemy] Declarative Sequences
Apologies if I'm posting a question to this group incorrectly or with bad
etiquette. I've been reading the emails from this group for a few years and now
have a question myself.
I am automating the creation of our models and business objects by reading a
schema definition from YAML. We use the declarative base, some metaclasses and
superclasses, a couple of mixes.. and ultimately PostgreSQL 9.x (exclusively)
I create a custom type using a metaclass that creates the primary key column
along with anything else, and decorate anything that needs `@declared_attr`
appropriately, for the declarative mechanism to discover it.
Long story short,
I would like to create custom sequences using Sequence() with a start value,
using a declared Column, and not having to construct a Table itself, as
described in the docs here
http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html
I would like to do something like this:
class User(object):
id = Column(Integer, Sequence('user_id_seq', start=1),
primary_key=True)
This does work, and emits the CREATE SEQUENCE, but does not set the owned table
or the column to user.id
I get around this by using the default SERIAL, using DDL events, by updating
the normal ALTER SEQUENCE [user_id_seq] RESTART WITH [value].
However, I may have just missed something or misunderstood something deep in
declarative.
My goal is to automate as much of the schema, model, and business object
creation as possible using our DSL, which is leveraging the power of
SQLAlchemy, GeoAlchemy2, PostgreSQL, and PostGIS.
Much obliged for any help,
Mat
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Declarative Sequences
On Jun 26, 2013, at 12:02 PM, Mat Mathews m...@miga.me wrote:
I would like to do something like this:
class User(object):
id = Column(Integer, Sequence('user_id_seq', start=1),
primary_key=True)
This does work, and emits the CREATE SEQUENCE, but does not set the owned
table or the column to user.id
I'm not able to reproduce, even assigning the same sequence name to two
different tables simultaneously produces the correct result. Can you modify
the test below to illustrate your issue?
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base, declared_attr
Base = declarative_base()
class IdMixin(object):
id = Column(Integer, Sequence('user_id_seq', start=1), primary_key=True)
class A(IdMixin, Base):
__tablename__ = 'a'
class B(IdMixin, Base):
__tablename__ = 'b'
e = create_engine(postgresql://scott:tiger@localhost/test, echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
sess = Session(e)
a1 = A()
sess.add(a1)
b1 = B()
sess.add(b1)
sess.commit()
assert a1.id == 1
assert b1.id == 10001
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Declarative Sequences
Thanks for the quick reply.
If I modify the name of the sequence in the test, it reproduces what I have
experienced.
When I check the details on the sequence in postgres, there is no table owner
or column specified by the sequence 'test_user_id_seq'.. and I would expect to
see both tables `a` and `b`.
class IdMixin(object):
id = Column(Integer, Sequence('test_user_id_seq', start=1),
primary_key=True)
I will write a complete self contained test, that anyone can run. But wanted to
give my immediate feedback.
Thanks so much,
Mat
On Jun 26, 2013, at 6:37 PM, Michael Bayer mike...@zzzcomputing.com wrote:
On Jun 26, 2013, at 12:02 PM, Mat Mathews m...@miga.me wrote:
I would like to do something like this:
class User(object):
id = Column(Integer, Sequence('user_id_seq', start=1),
primary_key=True)
This does work, and emits the CREATE SEQUENCE, but does not set the owned
table or the column to user.id
I'm not able to reproduce, even assigning the same sequence name to two
different tables simultaneously produces the correct result. Can you modify
the test below to illustrate your issue?
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base, declared_attr
Base = declarative_base()
class IdMixin(object):
id = Column(Integer, Sequence('user_id_seq', start=1),
primary_key=True)
class A(IdMixin, Base):
__tablename__ = 'a'
class B(IdMixin, Base):
__tablename__ = 'b'
e = create_engine(postgresql://scott:tiger@localhost/test, echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
sess = Session(e)
a1 = A()
sess.add(a1)
b1 = B()
sess.add(b1)
sess.commit()
assert a1.id == 1
assert b1.id == 10001
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Declarative Sequences
On Jun 26, 2013, at 1:26 PM, Mat Mathews m...@miga.me wrote:
Thanks for the quick reply.
If I modify the name of the sequence in the test, it reproduces what I have
experienced.
When I check the details on the sequence in postgres, there is no table owner
or column specified by the sequence 'test_user_id_seq'.. and I would expect
to see both tables `a` and `b`.
class IdMixin(object):
id = Column(Integer, Sequence('test_user_id_seq', start=1),
primary_key=True)
I will write a complete self contained test, that anyone can run. But wanted
to give my immediate feedback.
oh, you're looking for a linkage in PG's information schema I guess?
Sequence() doesn't have that functionality. My understanding was that PG's
SERIAL created the linkage as a server default, so you can get this by adding
this server default yourself, see below for demo:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base, declared_attr
Base = declarative_base()
class IdMixin(object):
id = Column(Integer,
Sequence('some_id_seq', start=1),
server_default=text(nextval('some_id_seq')),
primary_key=True)
class A(IdMixin, Base):
__tablename__ = 'a'
class B(IdMixin, Base):
__tablename__ = 'b'
e = create_engine(postgresql://scott:tiger@localhost/test, echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
sess = Session(e)
a1 = A()
sess.add(a1)
b1 = B()
sess.add(b1)
sess.commit()
assert a1.id == 1
assert b1.id == 10001
sess.execute(INSERT INTO b DEFAULT VALUES)
assert sess.execute(SELECT * FROM b WHERE id=10002).scalar()
Thanks so much,
Mat
On Jun 26, 2013, at 6:37 PM, Michael Bayer mike...@zzzcomputing.com wrote:
On Jun 26, 2013, at 12:02 PM, Mat Mathews m...@miga.me wrote:
I would like to do something like this:
class User(object):
id = Column(Integer, Sequence('user_id_seq', start=1),
primary_key=True)
This does work, and emits the CREATE SEQUENCE, but does not set the owned
table or the column to user.id
I'm not able to reproduce, even assigning the same sequence name to two
different tables simultaneously produces the correct result. Can you modify
the test below to illustrate your issue?
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base, declared_attr
Base = declarative_base()
class IdMixin(object):
id = Column(Integer, Sequence('user_id_seq', start=1),
primary_key=True)
class A(IdMixin, Base):
__tablename__ = 'a'
class B(IdMixin, Base):
__tablename__ = 'b'
e = create_engine(postgresql://scott:tiger@localhost/test, echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
sess = Session(e)
a1 = A()
sess.add(a1)
b1 = B()
sess.add(b1)
sess.commit()
assert a1.id == 1
assert b1.id == 10001
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Declarative Sequences
That absolutely solves my issue. I had a serious suspicion I was missing
something, and it was to look at the Column options, not just the Sequence.
Thanks a ton.
On Jun 26, 2013, at 7:44 PM, Michael Bayer mike...@zzzcomputing.com wrote:
On Jun 26, 2013, at 1:26 PM, Mat Mathews m...@miga.me wrote:
Thanks for the quick reply.
If I modify the name of the sequence in the test, it reproduces what I have
experienced.
When I check the details on the sequence in postgres, there is no table
owner or column specified by the sequence 'test_user_id_seq'.. and I would
expect to see both tables `a` and `b`.
class IdMixin(object):
id = Column(Integer, Sequence('test_user_id_seq', start=1),
primary_key=True)
I will write a complete self contained test, that anyone can run. But wanted
to give my immediate feedback.
oh, you're looking for a linkage in PG's information schema I guess?
Sequence() doesn't have that functionality. My understanding was that PG's
SERIAL created the linkage as a server default, so you can get this by
adding this server default yourself, see below for demo:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base, declared_attr
Base = declarative_base()
class IdMixin(object):
id = Column(Integer,
Sequence('some_id_seq', start=1),
server_default=text(nextval('some_id_seq')),
primary_key=True)
class A(IdMixin, Base):
__tablename__ = 'a'
class B(IdMixin, Base):
__tablename__ = 'b'
e = create_engine(postgresql://scott:tiger@localhost/test, echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
sess = Session(e)
a1 = A()
sess.add(a1)
b1 = B()
sess.add(b1)
sess.commit()
assert a1.id == 1
assert b1.id == 10001
sess.execute(INSERT INTO b DEFAULT VALUES)
assert sess.execute(SELECT * FROM b WHERE id=10002).scalar()
Thanks so much,
Mat
On Jun 26, 2013, at 6:37 PM, Michael Bayer mike...@zzzcomputing.com wrote:
On Jun 26, 2013, at 12:02 PM, Mat Mathews m...@miga.me wrote:
I would like to do something like this:
class User(object):
id = Column(Integer, Sequence('user_id_seq', start=1),
primary_key=True)
This does work, and emits the CREATE SEQUENCE, but does not set the owned
table or the column to user.id
I'm not able to reproduce, even assigning the same sequence name to two
different tables simultaneously produces the correct result. Can you
modify the test below to illustrate your issue?
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base, declared_attr
Base = declarative_base()
class IdMixin(object):
id = Column(Integer, Sequence('user_id_seq', start=1),
primary_key=True)
class A(IdMixin, Base):
__tablename__ = 'a'
class B(IdMixin, Base):
__tablename__ = 'b'
e = create_engine(postgresql://scott:tiger@localhost/test, echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
sess = Session(e)
a1 = A()
sess.add(a1)
b1 = B()
sess.add(b1)
sess.commit()
assert a1.id == 1
assert b1.id == 10001
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
