SQLAlchemy proper Model definition and Selecting / Inserting
Question: I'm having trouble with SQL Alchemy with both setting up the models and
selecting / inserting

If I setup the model as follows and insert in item into a table, it works:

    
    
    #!/usr/bin/python
    
    import pymysql
    import sqlalchemy
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy import *
    from sqlalchemy.orm import sessionmaker
    from sqlalchemy.sql import select
    
    engine = sqlalchemy.create_engine('mysql+pymysql://root@127.0.0.1/music?charset=utf8&use_unicode=0', pool_recycle=3600)
    connection = engine.connect()
    
    Base = declarative_base(bind=engine)
    Base
    
    metadata = MetaData()
    
    directory = Table('directory', metadata,
        Column('id', Integer, primary_key=True),
        Column('size', Integer),
        Column('name', String, unique=True),
        )
    
    words = Table('words', metadata,
        Column('id', Integer, primary_key=True),
        Column('source', Integer, ForeignKey('directory.id')),
        Column('words', String),
        )
    
    ratios = Table('ratios', metadata,
        Column('id', Integer, primary_key=True),
        Column('source', Integer, ForeignKey('directory.id')),
        Column('target', Integer, ForeignKey('directory.id')),
        Column('ratio', Integer),
        )
    
    metadata.create_all(engine)
    
    # ---------------------------------------------------------------------
    # Execute Insert
    # ---------------------------------------------------------------------
    
    i = words.insert().values(words='jack', source=1)
    result = connection.execute(i)
    

However, if I use the above model and try to select using the ID of a record
(command below) I get no results:

    
    
    s = directory.select().where(id == 1)
    result = connection.execute(s)
    
    for r in result:
        print r
    

The table does have a record with that ID, so it should return a result!

If instead I setup the models / tables as follows and use the Select command
(as below), this works:

    
    
    #!/usr/bin/python
    
    import pymysql
    import sqlalchemy
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy import *
    from sqlalchemy import UniqueConstraint
    
    engine = sqlalchemy.create_engine('mysql+pymysql://root@127.0.0.1/music?charset=utf8&use_unicode=0', pool_recycle=3600)
    connection = engine.connect()
    
    
    Base = declarative_base(bind=engine)
    Base
    
    class Directory(Base):
        __tablename__ = "directory"
        id = Column(Integer, primary_key=True)
        name = Column(String(767), unique=True)
        size = Column(Integer)
    
    class Words(Base):
        __tablename__ = "words"
        id = Column(Integer, primary_key=True)
        source = Column(Integer, ForeignKey('directory.id'))
        words = Column(String(1500))
    
    class Ratios(Base):
        __tablename__ = "ratios"
        id = Column(Integer, primary_key=True)
        source = Column(Integer, ForeignKey('directory.id'))
        target = Column(Integer, ForeignKey('directory.id'))
        ratio = Column(Integer)
    
    
    class Rename(Base):
        __tablename__ = "rename"
        id = Column(Integer, primary_key=True)
        source = Column(Integer, ForeignKey('directory.id'))
        name = Column(String(1500))
    
    
    Base.metadata.create_all()
    
    # ---------------------------------------------------------------------
    # Execute Select
    # ---------------------------------------------------------------------
    
    s = select([Directory]).where(Directory.id == 1)
    result = connection.execute(s)
    
    for r in result:
        print r
    

But then if I follow similar syntax as Select for an Insert, this doesn't
work:

    
    
    i = insert([Words]).values(source=1, words=out)
    connection.execute(i)
    

Questions: Can someone recommend which style of creating the models is correct
/ best?

Can someone clarify the syntax for insert, update, select for the recommended
model?

Is there a tutorial you can recommend that's concise and clear? I've been
going through the SQLAlchemy documentation and it's really confusing -- I
found examples to setup the models in both ways above, but haven't found any
tutorial that's "end-to-end" in a straightforward and consistent way.

Thank you.


Answer: Are you intentionally trying not to use the ORM? If not, please check out the
[comprehensive ORM
tutorial](http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html).

Here's a working example of your objects with select, insert, and update
examples using the ORM:

    
    
    import sqlalchemy
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy import *
    from sqlalchemy import orm
    
    engine = sqlalchemy.create_engine('sqlite://')
    connection = engine.connect()
    
    
    Base = declarative_base()
    
    
    class Directory(Base):
        __tablename__ = "directory"
        id = Column(Integer, primary_key=True)
        name = Column(String(767), unique=True)
        size = Column(Integer)
    
        def __repr__(self):
            return (
                "Directory(id={self.id}, name={self.name}, size={self.size}"
                .format(self=self))
    
    
    class Words(Base):
        __tablename__ = "words"
        id = Column(Integer, primary_key=True)
        source = Column(Integer, ForeignKey('directory.id'))
        directory = orm.relationship('Directory', backref='words')
        words = Column(String(1500))
    
        def __repr__(self):
            return (
                "Words(id={self.id}, source={self.source}, words={self.words}"
                .format(self=self))
    
    
    class Ratios(Base):
        __tablename__ = "ratios"
        id = Column(Integer, primary_key=True)
        source = Column(Integer, ForeignKey('directory.id'))
        target = Column(Integer, ForeignKey('directory.id'))
        ratio = Column(Integer)
    
    
    class Rename(Base):
        __tablename__ = "rename"
        id = Column(Integer, primary_key=True)
        source = Column(Integer, ForeignKey('directory.id'))
        name = Column(String(1500))
    
    
    def select_objs(session):
        #
        # Select examples
        #
        print 'Retrieving objects -----------------'
        word = session.query(Words).filter(Words.id == 1).first()
        print word
        print 'Access the directory via a relationship!'
        print word.directory
        print '---------------------------'
    
    
    if __name__ == '__main__':
        engine = create_engine('sqlite://', echo=True)
        Session = orm.sessionmaker()
        Session.configure(bind=engine)
        Base.metadata.bind = engine
        Base.metadata.create_all()
    
        session = Session()
    
        #
        # Insert some objects
        #
    
        dir1 = Directory(id=1, name='Some Dir', size=10)
        session.add(dir1)
        # Note because of the relationship configuration and the fact that dir1
        # is already in the session, word doesn't have to be added
        # (no harm in doing so though)
        word = Words(id=1, directory=dir1, words='Some words!')
        session.flush()
    
        select_objs(session)
    
        #
        # Update them
        #
    
        # Update via ORM
        word.words = 'Some new words!'
        # update via query
        (
            session.query(Directory)
            .filter(Directory.id == 1)
            .update({"name": "A new directory name!"}))
    
        # Prove they're updated: flush session, expunge existing objects
        session.flush()
        session.expunge(word)
    
        select_objs(session)
    

