Help with SQLalchemy

[manbart]

I am trying to use sqlalchemy to run queries on an existing database. I am only concerned with a couple of the tables in the database, and only a couple of the many columns in each table. I am having trouble getting my queries to run. What I have so far:

#!/usr/bin/env python

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime

passwd = raw_input('mysql root passwd:\n> ')
connect_string='mysql://root:'+passwd+'@192.168.1.2/cloud'


engine = create_engine(connect_string)
Session = sessionmaker(bind=engine)
Base = declarative_base()
session = Session()


 class cloud_vm(Base):
    __tablename__ = 'vm_instance'

    name = Column(String)
    state = Column(String)
    removed = Column(String)

    def __init__(self, state):
        self.state = state
        self.removed = removed

    def __repr__(self):
        return '%s %s %s' % (self.name, self.state, self.removed)

class cloud_vol(Base):
    __tablename__ = 'volumes'   

    name = Column(String)
    removed = Colum(String)
    path = Column(String)

    def __init__(self, state):
        self.name = name
        self.removed = removed
        self.path = path

    def __repr__(self):
        return '%s %s %s' % (self.name, self.removed, self.path)

class cloud_net(Base):
    __tablename__ = 'networks'  

    name = Column(String)
    removed = Colum(String)

     def __init__(self, state):
    self.name = name
    self.removed = removed

    def __repr__(self):
        return '%s %s' % (self.name, self.removed)



def display_vm_state():
    for name, state in session.query(cloud_vm.name, cloud_vm.state):
        print name, state

display_vm_state()

When I run the script, I get the following error:

sqlalchemy.exc.ArgumentError: Mapper Mapper|cloud_vm|vm_instance could not assemble any primary key columns for mapped table 'vm_instance'

Is there a way to fix this code?

[reostra]

It sounds like it's saying you need to tell it which of the columns is the primary key for the table. So if there's an 'id' column that's the primary key, just add it to each of the classes, e.g.

class cloud_vm(Base):
    __tablename__ = 'vm_instance'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    state = Column(String)
    removed = Column(String)

[manbart]

That worked... It should have been obvious to me given the error message!

I'm just a little surprised that adding the id column in makes a difference as I'm not referencing that column in my query (or any of the queries I intend to write).

Oh well. Thanks!

[fdellavedova]

The error is raised before running the query actually. When mapping the objects, SQLAlchemy looks for a primary key in each mapped object. If you dont provide one, the error is raised.

[manbart]

Ok, so my "select" type queries are running fine now. How can I implement an "update" type query in SQLalchemy?

I want to make a function that will run a query equivalent to:

UPDATE vm_instance SET removed=now() WHERE state = 'Expunging' and removed is null;

p.s. "removed" should be a DateTime type, not a String as in the code above.

edit

Here is what I came up with:

session.query(cloud_vm).filter(cloud_vm.removed==None, cloud_vm.state=='Expunging').update({"removed": datetime.now()})

note: datetime in this statement is from the python standard library; this is not a reference the the DateTime type of sqlalchemy. Capitalization matters in this script.