On Tue, Aug 22, 2017 at 9:42 PM, Joshua Peppeman
<[email protected]> 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 [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
