On Feb 4, 2011, at 5:59 PM, Josh Stratton wrote:
> I've tried something like this before creating the table. Here
> len(sites) is definitely evaluating to 6 as it should, but I'm only
> getting the increment issue now.
>
> sequence = Sequence('id_seq', start=siteIndex,
> increment=len(sites), metadata=self.metadata)
> sequence.create()
I don't really know what "the increment issue" is, so here is a little
demonstration of the full usage contract of Sequence as used with Oracle.
Note when it runs, we create a SEQUENCE with a particular INCREMENT BY and
START WITH, so when you run your program you should ensure that the appropriate
DDL is being emitted in that regard, and then as we invoke the SEQUENCE, we
receive incrementing values that correspond to those settings.
from sqlalchemy import create_engine, Sequence
engine = create_engine("oracle://scott:tiger@localhost/xe", echo=True)
sequence = Sequence('some_sequence', start=5, increment=10)
# drop any existing sequence named "some_sequence" first
sequence.drop(engine, checkfirst=True)
# create
sequence.create(engine)
# test it
assert engine.execute(sequence) == 5
assert engine.execute(sequence) == 15
assert engine.execute(sequence) == 25
output:
SELECT USER FROM DUAL
SELECT sequence_name FROM all_sequences WHERE sequence_name = :name AND
sequence_owner = :schema_name
{'name': u'SOME_SEQUENCE', 'schema_name': u'SCOTT'}
DROP SEQUENCE some_sequence
COMMIT
SELECT sequence_name FROM all_sequences WHERE sequence_name = :name AND
sequence_owner = :schema_name
{'name': u'SOME_SEQUENCE', 'schema_name': u'SCOTT'}
CREATE SEQUENCE some_sequence INCREMENT BY 10 START WITH 5
COMMIT
SELECT some_sequence.nextval FROM DUAL
SELECT some_sequence.nextval FROM DUAL
SELECT some_sequence.nextval FROM DUAL
>
> On Fri, Feb 4, 2011 at 2:46 PM, Josh Stratton <[email protected]>
> wrote:
>> I'm currently passing the table the sequence object, when I create the
>> table. I've been building the table with metadata.create_all.
>>
>> Calling sequnce.create() before creating the table complains of no
>> binding to an engine or a connection. I understand this makes sense
>> as I haven't told the sequence where to connect, but I don't see
>> anything in the docs for connecting the sequence to the
>> engine/connection. Is there a hidden parameter for binding? Or if I
>> include this sequence in in the table, should that sequence be
>> associated with the metadata object I use to make the table?
>>
>> sequence = Sequence('id_seq', start=siteIndex, increment=len(sites))
>> #sequence.create()
>>
>> table = Table('acounts', self.metadata,
>> Column(....))
>>
>> self.metadata.create_all()
>>
>>
>> On Fri, Feb 4, 2011 at 1:46 PM, Michael Bayer <[email protected]>
>> wrote:
>>> The sequence has to be created (meaning, the appropriate DDL statements
>>> must be executed) on the target platform with those options, or the
>>> appropriate ALTER statements emitted to ALTER the sequence's settings.
>>>
>>> So when those flags are applied to sqlalchemy.Sequence(), its assumed that
>>> you're using table.create(), metadata.create_all(), or sequence.create() to
>>> issue the CREATE SEQUENCE call. The INCREMENT BY and START WITH clauses
>>> should be emitted as of the 0.6 series of SQLAlchemy.
>>>
>>>
>>>
>>> On Feb 4, 2011, at 2:10 PM, Josh Stratton wrote:
>>>
>>>> I'm connecting to an Oracle database for my work and we do replication
>>>> by periodically joining tables across sites instead of a single server
>>>> just in case a link in between goes down. One issue with this though
>>>> is I need to generate unique keys for a single table so if the
>>>> connection does go down, insertions to both tables won't cause a
>>>> conflict when it's time to merge.
>>>>
>>>> Because the ids aren't important to me during insertion, I just use a
>>>> Sequence object (since Oracle doesn't support autoincrementing). This
>>>> has worked fine, but I'm now trying how to combine this with the
>>>> unique keys across the two sites. I noticed the Sequence class has a
>>>> start and increment tag, which I thought would work perfectly by
>>>> interleaving the keys per site.
>>>>
>>>> For example...
>>>>
>>>> if site1:
>>>> init = 0
>>>> else:
>>>> init = 1
>>>>
>>>> sequence = Sequence('id_seq',start=init,increment=2)
>>>>
>>>> I don't see any actual documentation for what start and increment do
>>>> (http://www.sqlalchemy.org/docs/core/schema.html#sqlalchemy.schema.Sequence),
>>>> so I'm just assuming they function as I'd expect, where start is the
>>>> first id to try and increment is the space between the possible id's,
>>>> so site 1's ids will always be % 2 == 0, while site 2's would be % 2
>>>> == 1.
>>>>
>>>> This doesn't seem to be what I'm getting however. In a few test cases
>>>> on one site, the first id starts at 10,283 and the next one generated
>>>> is 10,284. In this case it's starting at a crazy high value other
>>>> than 0 and it doesn't seem to be incrementing at all. Am I using
>>>> these flags incorrectly? Is this a bug in sqlalchemy? Deprecated
>>>> flags? Or is this whole idea garbage and I should use a guid?
>>>>
>>>> Thanks.
>>>>
>>>> --
>>>> You received this message because you are subscribed to the Google Groups
>>>> "sqlalchemy" group.
>>>> To post to this group, send email to [email protected].
>>>> To unsubscribe from this group, send email to
>>>> [email protected].
>>>> For more options, visit this group at
>>>> http://groups.google.com/group/sqlalchemy?hl=en.
>>>>
>>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>>> "sqlalchemy" group.
>>> To post to this group, send email to [email protected].
>>> To unsubscribe from this group, send email to
>>> [email protected].
>>> For more options, visit this group at
>>> http://groups.google.com/group/sqlalchemy?hl=en.
>>>
>>>
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
