Hey Mike, thanks for getting back with me. I have to go to work soon but
I'll try that out as soon as possible. Can you elaborate a little on what
you mean by getting tripped up if I push it too far?
On Tuesday, August 22, 2017 at 9:26:42 PM UTC-5, Mike Bayer wrote:
>
> On Tue, Aug 22, 2017 at 9:42 PM, Joshua Peppeman
> <joshuap...@gmail.com <javascript:>> wrote:
> > Hello,
> >
> > I asked this question on Reddit and StackOverflow without any luck
> getting
> > an answer. Here is the StackOverflow question. I'll paste it below
> again.
> >
> >
> > I have two tables set up in Python with sqlalchemy using mySQL. They
> look
> > something like this:
> >
> > from sqlalchemy import *
> > from sqlalchemy.orm import *
> > from sqlalchemy.ext.declarative import declarative_base
> >
> > class Test(Base):
> > __tablename__ = 'test'
> > id = Column(Integer, primary_key=True)
> > questions = relationship('Question', cascade='all,delete',
> > backref='test', uselist=True)
> >
> > class Question(Base):
> > __tablename__ = 'question'
> > testID = Column(Integer, ForeignKey('test.id', ondelete='CASCADE'))
> > sequence = Column(Integer, primary_key=True)
> > text = Column(String(500))
> >
> >
> >
> > Right now when I run my code and get the tables set up, they look like
> this:
> >
> > testID|sequence|text
> >
> > 1 | 1 | text
> >
> > 1 | 2 | text
> >
> > 1 | 3 | text
> >
> > 1 | 4 | text
> >
> > 2 | 5 | text
> >
> > 2 | 6 | text
> >
> > 2 | 7 | text
> >
> > But I want them to look like this:
> >
> > testID|sequence|text
> >
> > 1 | 1 | text
> >
> > 1 | 2 | text
> >
> > 1 | 3 | text
> >
> > 1 | 4 | text
> >
> > 2 | 1 | text
> >
> > 2 | 2 | text
> >
> > 2 | 3 | text
> >
> > I know that sequence is auto-incrementing because it is the first int
> > primary key, but I only want it to auto-increment until there's a new
> > testID.
> >
> > I could leave it and just sort them later, but I'd really like to set it
> up
> > where sequence resets with every new testID. I'm pretty new to
> sqlAlchemy so
> > I may be missing something obvious. Any help is appreciated. Thanks.
>
> MySQL's autoincrement feature is strictly a single integer that
> increases steadily. If you want an integer that is cycling, you'd
> need to maintain that number yourself on the Python side.
> Additionally, you'd want to set up a composite primary key on
> Question. The orderinglist extension is a quick way to get the
> counting-per-collection effect you are looking for (works on the
> Python side though and it can get tripped up if you push it too far):
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.ext.orderinglist import ordering_list
> Base = declarative_base()
>
>
> class Test(Base):
> __tablename__ = 'test'
> id = Column(Integer, primary_key=True)
> questions = relationship(
> 'Question', cascade='all,delete', backref='test', uselist=True,
> collection_class=ordering_list("sequence", count_from=1))
>
>
> class Question(Base):
> __tablename__ = 'question'
> testID = Column(Integer, ForeignKey('test.id', ondelete='CASCADE'),
> primary_key=True)
> sequence = Column(Integer, primary_key=True)
> text = Column(String(500))
>
> e = create_engine("sqlite://", echo=True)
> Base.metadata.create_all(e)
> s = Session(e)
>
>
> s.add_all([
> Test(
> questions=[
> Question(text="t1q1"),
> Question(text="t1q2"),
> Question(text="t1q3"),
> Question(text="t1q4"),
> ]
> ),
> Test(
> questions=[
> Question(text="t2q1"),
> Question(text="t2q2"),
> Question(text="t2q3"),
> Question(text="t2q4"),
> ]
> )
> ])
>
> s.commit()
>
>
> for row in s.query(Question.testID, Question.sequence, Question.text).\
> order_by(Question.testID, Question.sequence):
> print row
>
>
>
>
>
> >
> > --
> > SQLAlchemy -
> > The Python SQL Toolkit and Object Relational Mapper
> >
> > http://www.sqlalchemy.org/
> >
> > To post example code, please provide an MCVE: Minimal, Complete, and
> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> > description.
> > ---
> > 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+...@googlegroups.com <javascript:>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
