[sqlalchemy] sequence incrementing

[Josh Stratton]

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 sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] sequence incrementing

[Josh Stratton]

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 mike...@zzzcomputing.com 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 sqlalchemy@googlegroups.com.
 To unsubscribe from this group, send email to 
 sqlalchemy+unsubscr...@googlegroups.com.
 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 sqlalchemy@googlegroups.com.
 To unsubscribe from this group, send email to 
 sqlalchemy+unsubscr...@googlegroups.com.
 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 sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] simple update without a session or mapping

[Josh Stratton]

I'm currently interfacing with an Oracle db using sqlalchemy without
any sessions or mappings.  Selects and inserts work great, but I'd
like to be able to update a row without having to delete and reinsert
it.

# remove the id
table.delete(table.c.id == row['id']).execute()

# add it back with new value
row['name'] = 'now frank'
self.conn.execute(table.insert(), row)

I realize there there's an update function in table, but I'm at a loss
as to how to use the syntax for it.  I'm just changing one or two
non-primary-key values.  All the examples I see use the ORM mappings
with sessions, or use the update to change every field in a column.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Re: attempting something like a bulk insert ignore

[Josh Stratton]

 you'd need to establish the primary key from the mapper's point of
 view in terms of both userId and userName.   mapper() accepts a
 primary_key argument for this purpose.

That kind of surprises me sqlalchemy isn't aware of what's a primary
key and what isn't.  Looking at the docs, it states mapper accepts a
primary_key argument as a list of Column objects.  I assume that's the
objects stored in the 'c' attribute.

mapper(Job, joinOfJobsAndUsers,
   properties={
'jobId' : jobTable.c.farmJobId, # part
of the primary key of jobs table
'try' : jobTable.c.tryIndex, # part of
the primary key of the jobs table
'userId' : [jobsTable.c.userId,
usersTable.c.userId], # the 2nd is the primary key of usersTable
'user' : usersTable.c.userName
},
   primary_key=[usersTable.c.userId, usersTable.c.userName])

Is this what you mean?  I've tried this and
primary_key=[usersTable.c.userId] and all the attributes contributing
to the primary key and still see IntegrityErrors when inserting into
the users table.

class 'sqlalchemy.exceptions.IntegrityError': (IntegrityError)
column userName is not unique u'INSERT INTO users (userName) VALUES
(?)' ['totsuka']

According to the 0.5.6 docs, which I assume is pretty close to .4,

primary_key
A list of Column objects which define the primary key to be used
against this mapper’s selectable unit. This is normally simply the
primary key of the local_table, but can be overridden here.

Looking at this, I'm not sure why userName has to be included at all.

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: attempting something like a bulk insert ignore

[Josh Stratton]

 OK, you have the unique constraint on userName alone.  that means that you
 *cannot* have two rows like this:

 userId      userName
 1           someuser
 2           someuser

Right.

 because unlike a primary key that consists of userId and userName, the
 distinctness here of userName is independent of that of userId.

 Assuming this is what you want, please issue a query() beforehand for each
 object you wish to create, asking for any existing object which has the
 given username.   If it already exists, populate that object with your new
 data.  Otherwise create a new object and add() it to your session.

Maybe my setup isn't correct, but if I do something like this:

session.add(myObj)
session.flush()

# try adding to the database here
# check if it's in there
matches = [obj for obj in session.query(MyObj)]

And matches is an empty list, can I assume something is seriously
wrong?  Shouldn't this work even without the flush since the session
keeps track of it?

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: purpose of class_mapper

[Josh Stratton]

I just figured the mapper had to be called every time as it didn't
just compile the class, but actually needed a database table to map to
and wasn't sure what was done under the hood to setup that up and if
it was valid for another database.

 Just to be sure, but would using a mapper setup as described above
 prevent normal autoincrementing for merge's or saveOrUpdate's into a
 table or do those only work with straight inserts?

 im not sure what you mean here but using mappers is a prerequisite
 using a merge, if you're referring to session.merge().

I was referring to a session.merge(), but from what I understand of
sqlalchemy, you can do inserts into a table without the primary key
and if it's an integer, it will provide a primary key on the insert.
When using a session and adding data with a session.merge() I seem to
be getting errors of not being about to add data due to lack of a
primary key.  If I had a table with an integer primary key 'userId'
and String 'userName', I mapped a class that just had a 'userName'
string field, and merged it into the database, would you expect a
primary key to be provided as a non-session insert would?  If so, are
you aware of any complications to this specifically with sqlite?

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: attempting something like a bulk insert ignore

[Josh Stratton]

 From what I've read of sqlalchemy, I originally wanted to have a main
 table with one attribute foreign keyed to another table's
 autoincremented integer primary key userId and a userName.  I thought
 I could join the tables together and set that as the mapper.  The save
 every object in my session.  I thought I had it all figured out until
 I found out that specifically for sqlite, autoincrement (or
 autogenerated primary keys) don't work with composite tables/using a
 join.

 as long as the primary key of table #1 is a single-column primary key,
 SQLite autoincrements it.  To get the pk into your joined table, set the
 two columns to the same attribute.   e.g. as in
 http://www.sqlalchemy.org/docs/05/mappers.html#mapping-a-class-against-multiple-tables

Ah, I was going to reference another page, but I see you are the author.  Heh.

http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg09503.html

Anyway, isn't this the same problem?  If I don't use foreign keys
pointing the main table to the user lookup, I get an error:

class 'sqlalchemy.exceptions.ArgumentError': Can't find any foreign
key relationships between 'jobs' and 'users'

Maybe I'm explaining my problem wrong.

I have table A that has a few preassigned primary keys and
non-primary_key column 'userId', which references table B.
I have table B that has an autoincrementing primary key 'userId' and a
userName column is unique.  Based on the linked message you wrote
earlier, it seems like what you're now proposing isn't possible in
sqlite.

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: attempting something like a bulk insert ignore

[Josh Stratton]

 this is always easily worked around by specifing the ON clause to your
 join(), as the second argument of table1.join(table2,
 and_(table1.c.foo==table2.c.bar, table1.c.bat==table2.c.hoho, ...)).

Ah, okay.  No foreign key, just a join.  For reference, this was what
I ended up going with.
j = jobsTable.join(usersTable, jobsTable.c.userId.like(usersTable.c.userId))

 as long as the sqlite table has only one column that is declared as
 primary key in the CREATE TABLE statement, sqlite can autoincrment.  a
 userId column declared as the PRIMARY KEY and a second userName column
 that only has a unique constraint on it does not impact SQLite's
 autoincrement capability.

Ah, I see.  I think the only thing I don't understand still is using
this mapping with a session and handling inserts that aren't unique.
Using a session, the problem presents itself at the commit, which
seems to mess up the entire insertion instead of the single collision.
 Maybe it's more of an SQL question, but when using a session/mapper
configuration like this and one were continually trying to insert data
into the database that might have been added already, how do you
efficiently skip that insertion?

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: attempting something like a bulk insert ignore

[Josh Stratton]

 2. load-each-row-at-a-time

 just remove the first query.  the session will then look up things using
 merge() as you ask.

But wouldn't this still cause an insertion error when I try merging a
job object, who's userName is already in the table?

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: purpose of class_mapper

[Josh Stratton]

 What is the purpose of class_mapper?  I can't find it in the .4 docs
 for the tutorials I'm looking at, yet when I don't call it as

 https://svn.enthought.com/svn/enthought/sandbox/EnvisageSQLAlchemy/enthought/sqlalchemy/has_traits_orm.py

 notes, I get a

 AttributeError:
             # 'ColumnProperty' object has no attribute 'key'

 that is an ancient bug which refers to the fact that mappers have not been
 compiled, yet an expression is doing something along the lines of
 User.some_property == somevalue.   The Class.descriptor expression
 approach was new in 0.4 and had some incompleteness.

 To work around it in 0.4 the most general way is to call
 compile_mappers().   class_mapper() also has the effect of invoking a
 compile if the mapper being returned was not yet compiled.
 class_mapper() in modern SQLAlchemy would normally only be used if
 additional configuration needs to be added to an existing mapper, given
 only a class.

Adding the class_mapper call seems to create other problems.

for db in allDataBases:
   # setup the session, engine, and metadata
   ...

   # create the tables if they don't exist
   ...

   # grab a table out
   someTable = metadata.tables['someTable']

   # map it
   mapper(MyObject, someTable)

   # call the class mapper
   class_mapper(MyObject) # has to be done after the mapper call or I
receive an Invalid Request no mapper associated with it

   for attr in dir(myObjectInstance):
  print(attr)
  getattr(myObjectInstance, attr) # this now throws the following error


AttributeError: 'MyObject' object has no attribute '_state'

I don't see any _state printed out.  The only additional attribute I
see is the 'c' variable, where pull tables from.  What is causing this
_state thing to appear?  Do I need to call class_mapper after each
mapper only only once after any mapper?

Thanks.

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: purpose of class_mapper

[Josh Stratton]

 AttributeError: 'MyObject' object has no attribute '_state'

 I don't see any _state printed out.  The only additional attribute I
 see is the 'c' variable, where pull tables from.  What is causing this
 _state thing to appear?  Do I need to call class_mapper after each
 mapper only only once after any mapper?

 this error indicates that an instance of MyObject was instantiated
 before mappers were created.   In your example you're accessing
 myObjectInstance however I don't see where that's being instantiated, so
 the fact that the mapper is set up just above would be the cause.

 the configuration of mappers (note that this does not include the
 configuration of engines or sessions - just mappers) should occur at
 application start time as closely as possible to the point at which the
 classes themselves have been defined.    This is one reason the
 declarative extension has been popular, since it performs both at the
 same time by definition.

Okay, that seemed to work.  Thanks.  A mapper() call followed by a
class_mapper() call for each database did the trick.

Josh

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: purpose of class_mapper

[Josh Stratton]

 AttributeError: 'MyObject' object has no attribute '_state'

 I don't see any _state printed out.  The only additional attribute I
 see is the 'c' variable, where pull tables from.  What is causing this
 _state thing to appear?  Do I need to call class_mapper after each
 mapper only only once after any mapper?

 this error indicates that an instance of MyObject was instantiated
 before mappers were created.   In your example you're accessing
 myObjectInstance however I don't see where that's being instantiated, so
 the fact that the mapper is set up just above would be the cause.

 the configuration of mappers (note that this does not include the
 configuration of engines or sessions - just mappers) should occur at
 application start time as closely as possible to the point at which the
 classes themselves have been defined.    This is one reason the
 declarative extension has been popular, since it performs both at the
 same time by definition.

 Okay, that seemed to work.  Thanks.  A mapper() call followed by a
 class_mapper() call for each database did the trick.

Just to be sure, but would using a mapper setup as described above
prevent normal autoincrementing for merge's or saveOrUpdate's into a
table or do those only work with straight inserts?

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---