"""
Web com operações CRUD
"""

# CherryPy
import cherrypy

# CherryTemplate
import cherrytemplate

# SQLAlchemy
import sqlalchemy as sql

# Conecta ao bando
db = sql.create_engine('sqlite:///zoo.db')

# Acesso aos metadados
metadata = sql.MetaData(db)

try:
    # Carrega metadados da tabela
    zoo = sql.Table('zoo', metadata, autoload=True)

except:
    # Define a estrutura da tabela zoo
    zoo = sql.Table('zoo', metadata,
        sql.Column('id', sql.Integer, primary_key=True),
        sql.Column('nome', sql.String(100),
            unique=True, nullable=False),
        sql.Column('quantidade', sql.Integer, default=1),
        sql.Column('obs', sql.String(200), default='')
    )

    # Cria a tabela
    zoo.create()

# Os nomes das colunas
colunas = [col for col in zoo.columns.keys()]
colunas.remove('id')


class Root(object):
    """Raiz do site"""

    @cherrypy.expose
    def index(self, **args):
        """
        Lista os registros
        """

        msg = ''
        op = args.get('op')
        ident = int(args.get('ident', 0))
        novo = {}

        for coluna in colunas:
            novo[coluna] = args.get(coluna)

        if op == 'rem':

            # Remove dados
            rem = zoo.delete(zoo.c.id==ident)
            rem.execute()
            msg = 'registro removido.'

        elif op == 'add':

            novo = {}

            for coluna in colunas:
                novo[coluna] = args[coluna]

            try:
                # Insere dados
                ins = zoo.insert()
                ins.execute(novo)
                msg = 'registro adicionado.'

            except sql.exceptions.IntegrityError:
                msg = 'registro existe.'

        elif op == 'mod':

            novo = {}

            for coluna in colunas:
                novo[coluna] = args[coluna]

            try:
                # Modifica dados
                mod = zoo.update(zoo.c.id==ident)
                mod.execute(novo)
                msg = 'registro modificado.'

            except sql.exceptions.IntegrityError:
                msg = 'registro existe.'

        # Seleciona dados
        sel = zoo.select(order_by=zoo.c.nome)
        rec = sel.execute()

        # Gera a página principal a partir do modelo "index.html"
        return cherrytemplate.renderTemplate(file='index.html',
            outputEncoding='utf-8')

    @cherrypy.expose
    def add(self):
        """
        Cadastra novos registros
        """

        # Gera a página de registro novo a partir do modelo "add.html"
        return cherrytemplate.renderTemplate(file='add.html',
            outputEncoding='utf-8')

    @cherrypy.expose
    def rem(self, ident):
        """
        Confirma a remoção de registros
        """

        # Seleciona o registro
        sel = zoo.select(zoo.c.id==ident)
        rec = sel.execute()
        res = rec.fetchone()

        # Gera a página de confirmar exclusão a partir do modelo "rem.html"
        return cherrytemplate.renderTemplate(file='rem.html',
            outputEncoding='utf-8')

    @cherrypy.expose
    def mod(self, ident):
        """
        Modifica registros
        """

        # Seleciona o registro
        sel = zoo.select(zoo.c.id==ident)
        rec = sel.execute()
        res = rec.fetchone()

        # Gera a página de alteração de registro a partir do modelo "mod.html"
        return cherrytemplate.renderTemplate(file='mod.html',
            outputEncoding='utf-8')


# Inicia o servidor na porta 8080
cherrypy.quickstart(Root())

<py-include="header.html">
<table>
<tr>
<th></th>
<py-for="coluna in colunas">
    <th><py-eval="coluna"></th>
</py-for>
<th></th>
<th></th>
</tr>
<py-for="i, campos in enumerate(rec.fetchall())">
    <tr>
    <th><py-eval="unicode(i + 1)"></th>
    <py-for="coluna in colunas">
        <td><py-eval="unicode(campos[coluna])"></td>
    </py-for>
    <td>
    <a href="/mod?ident=<py-eval="unicode(campos['id'])">">modificar</a>
    </td><td>
    <a href="/rem?ident=<py-eval="unicode(campos['id'])">">remover</a>
    </td>
    </tr>
</py-for>
</table>
<br />
<form action="/add" method="post">
    <input type="submit" value=" adicionar " />
</form>
<p>
<py-eval="msg">
</p>
<py-include="footer.html">

<py-include="header.html">
<form action="/?op=add" method="post">
    <table>
    <py-for="coluna in colunas">
        <tr><td>
        <py-eval="coluna">
        </td><td>
        <input type="text" size="30" name="<py-eval="coluna">" />
        </td></tr>
    </py-for>
    </table>
<br />
<input type="submit" value=" salvar " />
</form>
<br />
[ <a href="/">voltar</a> ]
<py-include="footer.html">

<py-include="header.html">
<form action="/?op=mod&ident=<py-eval="unicode(res['id'])">" method="post">
    <table border="0">
    <py-for="coluna in colunas">
        <tr><th>
        <py-eval="coluna">
        </th><td>
        <input type="text" size="30" name="<py-eval="coluna">"
        value="<py-eval="unicode(res[coluna])">" />
        </td></tr>
    </py-for>
    </table>
<br />
<input type="submit" value=" salvar " />
</form>
<br />
[ <a href="/">voltar</a> ]
<py-include="footer.html">

<py-include="header.html">
<table border="1">
<tr>
<py-for="coluna in colunas">
    <th><py-eval="coluna"></th>
</py-for>
</tr>
<tr>
<py-for="coluna in colunas">
    <td><py-eval="unicode(res[coluna])"></td>
</py-for>
</tr>
</table>
<br />
<form action="/?op=rem&ident=<py-eval="unicode(res['id'])">" method="post">
    <input type="submit" value=" remover " />
</form>
<br />
[ <a href="/">voltar</a> ]
<py-include="footer.html">

<html>
<head>
 <meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
<title>Zoo</title>
<style type="text/css">
<!--
body {
    margin: 10;
    padding: 10;
    font: 80% Verdana, Lucida, sans-serif;
    color: #333366;
}
h1 {
    margin: 0;
    padding: 0;
    font: 200% Lucida, Verdana, sans-serif;
}
a {
    color: #436976;
    text-decoration: none;
}
a:hover {
    background: #c4cded;
    text-decoration: underline;
}
table {
    margin: 1em 0em 1em 0em;
    border-collapse: collapse;
    border-left: 1px solid #858ba1;
    border-bottom: 1px solid #858ba1;
    font: 90% Verdana, Lucida, sans-serif;
}
table th {
    padding: 0em 1em 0em 1em;
    border-top: 1px solid #858ba1;
    border-bottom: 1px solid #858ba1;
    border-right: 1px solid #858ba1;
    background: #c4cded;
    font-weight: normal;
}
table td {
    padding: 0em 1em 0em 1em;
    border-top: 1px solid #858ba1;
    border-right: 1px solid #858ba1;
    text-align: center;
}
form {
    margin: 0;
    border: none;
}
input {
    border: 1px solid #858ba1;
    background-color: #c4cded;
    vertical-align: middle;
}
-->
</style>
</head>
<body>
<h1>Zoo</h1>
<br />

</body>
</html>