Have SQLAlchemy automatically update tables

[Chris Norman]

Hi all,

My name is Chris Norman, and I am a hobbyist programmer in the UK.

Firstly, please let me thank the creators for a truly wonderful piece of software! I was starting my application and coding the SQL by hand, which was a pain considering I don’t really know SQL all that well. SQLAlchemy actually gets around a load of problems I was having with the initial idea.

Anyways, now for my problem:

I’m trying to make a Pythonic sort of copy (with the view to splitting off) of the popular Lambda MOO server. I’m using a proper database backend, so that certain things which are a major pain in MOO will become much easier. That, and it would be cool to have a threaded version of MOO, without all the annoying bits.

Anyways, my question is this:

I created my tables with declarative and all that - works fine in fact. But just tonight I added a new column to one table. When I did Base.metadata.create_all(), I thought SQLAlchemy would realise there’s an added column, and add it to the database for me, but it hasn’t. Is there a way to make it do that so I don’t have to do any clever things when the server loads to check for differences between the database and the objects I’ve created?

Secondly, and sort of incidentally because I’ve already worked around this, is there any way to return an object from an id without having to use session.get? I kind of have this in mind:

class Object(Base):

 """My standard object."""

 __tablename__ = 'objects'

 id = Column(Integer, primary_key = True, nullable = False) # The primary key.

 location = Column(MagicType, default = None) # Location field.

The MagicType would contain an integer database-side, like 1, or 2, whatever, but would map directly to the object with that id.

I have gotten round this by using:

@property

def location(self):

 return self.location.id if self.location else None

@location.setter

def location(self, value):

 self._location = value.id if value else None

But I was wondering if there was a type which would do this with less properties being defined?

Cheers for all the help, and thanks once again for SLQAlchemy, it looks mighty fine to me! :-)

Cheers, and take care.

Chris Norman.

[Mike Bayer]

On 7/4/15 5:09 PM, Chris Norman wrote:

Hi all,

My name is Chris Norman, and I am a hobbyist programmer in the UK.

Firstly, please let me thank the creators for a truly wonderful piece of software! I was starting my application and coding the SQL by hand, which was a pain considering I don’t really know SQL all that well. SQLAlchemy actually gets around a load of problems I was having with the initial idea.

Anyways, now for my problem:

I’m trying to make a Pythonic sort of copy (with the view to splitting off) of the popular Lambda MOO server. I’m using a proper database backend, so that certain things which are a major pain in MOO will become much easier. That, and it would be cool to have a threaded version of MOO, without all the annoying bits.

Anyways, my question is this:

I created my tables with declarative and all that - works fine in fact. But just tonight I added a new column to one table. When I did Base.metadata.create_all(), I thought SQLAlchemy would realise there’s an added column, and add it to the database for me, but it hasn’t. Is there a way to make it do that so I don’t have to do any clever things when the server loads to check for differences between the database and the objects I’ve created?

this is a bigger issue in relational databases for which you need to run specific commands on the database to add new columns and features.   From an application point of view we call this "schema migrations" and the current tool recommended for this is Alembic: http://alembic.readthedocs.org/

There's a blurb on the whole subject here:

http://docs.sqlalchemy.org/en/rel_1_0/core/metadata.html#altering-schemas-through-migrations

as for sqlalchemy-migrate I am actually one of the maintainers for it now and while we have to keep it running for Openstack, it's not by any means under "active development" at this point that paragraph is out of date.

Secondly, and sort of incidentally because I’ve already worked around this, is there any way to return an object from an id without having to use session.get? I kind of have this in mind:

I'm assuming you mean query.get(), session i think may have had a get() method at some point but it's long removed.  You would just query.filter(object.id == <foo>), but below it seems like that's not what you mean.

class Object(Base):

 """My standard object."""

 __tablename__ = 'objects'

 id = Column(Integer, primary_key = True, nullable = False) # The primary key.

 location = Column(MagicType, default = None) # Location field.

The MagicType would contain an integer database-side, like 1, or 2, whatever, but would map directly to the object with that id.

I have gotten round this by using:

@property

def location(self):

 return self.location.id if self.location else None

@location.setter

def location(self, value):

 self._location = value.id if value else None

But I was wondering if there was a type which would do this with less properties being defined?

so.....are we saying here, get() is OK, but it's the fact that you need to know the name "MyClass.id" ?   I'm not really understanding what this @property does.    A brief but complete example of use would be helpful.

Cheers for all the help, and thanks once again for SLQAlchemy, it looks mighty fine to me! :-)

Cheers, and take care.

Chris Norman.

--
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.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Chris Norman]

Hi,
Thanks for the reply. I'm just reading over the main body of your email again, but I just wanted to reply to the location thing I was on about...

I don't think I explained it all that well, so let me give you a use case.

I have a player object, called Chris. That player's _location is 3, which happens to be the id of another object in the database. It's fine, I can do session.get(3), and get the exact object, but I'd like to just do Chris.location, which would be the fully qualified object. That way, I could do like Chris.location.location.name.

As I said, I've already got that with my @property, but it would be cool if there was some kind of type which would do it for me... Think I'm sort of asking about foreign keys, but within the same table perhaps? Perhaps not the best explanation, and I don't really know databases, I just know what I want to happen LOL.

Cheers,

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/p37SMlbKHoQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

[Mike Bayer]

On 7/5/15 2:53 PM, 'Chris Norman' via sqlalchemy wrote:

Hi,
Thanks for the reply. I'm just reading over the main body of your email again, but I just wanted to reply to the location thing I was on about...

I don't think I explained it all that well, so let me give you a use case.

I have a player object, called Chris. That player's _location is 3, which happens to be the id of another object in the database. It's fine, I can do session.get(3), and get the exact object, but I'd like to just do Chris.location, which would be the fully qualified object. That way, I could do like Chris.location.location.name.

this is called a relationship.  This is first introduced in the tutorial at http://docs.sqlalchemy.org/en/rel_1_0/orm/tutorial.html#building-a-relationship.   The example there shows "Address->User" as the many-to-one, this is "Player->Location".

As I said, I've already got that with my @property, but it would be cool if there was some kind of type which would do it for me... Think I'm sort of asking about foreign keys, but within the same table perhaps?

Player and Location sound like different tables?   If they are the same this is called an adjacency list relationship.   See http://docs.sqlalchemy.org/en/rel_1_0/orm/self_referential.html.