Web.by Web.py 2: Databases

Continuing from yesterday's post, today we will be using python to modify our site database(s). That being said, if you are new, you might want to check out the Apache web server, and your choice of either Postgresql or MariaDB for a database manager. Remember that python's primary purpose in the LAMP set-up is to communicate with both the web server and the database manager, meaning it will deal with most of the posting and getting back and forth. Like yesterday, we will start by starting the Apache and database manager daemons. From there, we will pull on the code from yesterday, which looked like this:

import web
render = web.template.render('templates/')

urls = (
    '/(.*)', 'index'
)

class index:
    def GET(self, name):
        i = web.input(name=None)
        return render.index(i.name)

if __name__ == "__main__":
    app = web.application(urls, globals())
    app.run()

With a /templates/index.html that looked like this:

$def with (name)

$if name:
    I just wanted to say <em>hello</em> to $name.
$else:
    <em>Hello</em> world!

Again, we will be working off of this tutorial. First things first, we must tell web.py which database manager we are using by using something similar to the following line:

db = web.database(dbn='postgres', user='username', pw='password', db='dbname')

to be placed under the headers. If you wish to use MariaDB instead of Postgresql, simply replace "postgres" with "mysql." If you have not set up a username, password, or databasename, please refer back to my SoC posts about Postgres or MariaDB. In both cases, we finished the day in the same way: by accessing the database manager's admin interface. 

Now, let's create a simple "to do" list again, like we did the other day, except that we will be using our database managers and web.py instead of just Python. To do this, we must create a table in our databases called "todo." First, we must access our database manager's admin interface (assuming it is set up properly).

For Postgres:

$ psql -d myDatabaseName

For MariaDB:

$ mysql -u user -p'password' databasename

Once done, simply type (or copy and paste) the following lines:

CREATE TABLE todo (
  id serial primary key,
  title text,
  created timestamp default now(),
  done boolean default 'f'    );

This, is simply creating a table with the title "todo" with otherwise standard formatting. We can add a first line by typing:

INSERT INTO todo (title) VALUES ('Learn web.py');

Which inputs the phrase "learn web.py" into the table. I might suggest opening up a second terminal here to have access to both your admin database management and your code, because now we must go back to our code.py and alter the index.GET function to read our table:

def GET(self):
    todos = db.select('todo')
    return render.index(todos)

This is following the same format as yesterday. This time, though, we create a variable that uses the select function of our database to choose the "todo" table we just created. It will then return a rendering of that variable. We should also change the url scheme at the top of the file back to how it was:

'/', 'index',

Finally, to read the table, we must change our templates/index.html file to:

$def with (todos)
<ul>
$for todo in todos:
    <li id="t$todo.id">$todo.title</li>
</ul>

Which uses python to select out the "todo" table within the variable "todos" we just created. We then use html to title each item and give it a line to itself. If you visit your site by running the code, you should see the phrase "Learn web.py", Which means we have successfully read from our database. Now, let's work on writing to it. In this case, we will add in any values we place in the url by adding in the following class:

class add:
    def POST(self):
        i = web.input()
        n = db.insert('todo', title=i.title)
        raise web.seeother('/')




This is very similar to when we input our name yesterday, except now we are adding the data directly into the database. Of course, to do this, we must change the allowed urls to:

'/', 'index',
'/add', 'add'

Which allows for the subdirectory "add" with the class "add" in the url. 

Finally, we must add

<form method="post" action="add">
<p><input type="text" name="title" /> <input type="submit" value="Add" /></p>
</form>




to the end of templates/index.html to allow posting through the site, itself. In full, your code.py should look like:

import web
render = web.template.render('templates/')
db = web.database(dbn='postgres', user='user', pw='password', db='dbname')

urls = (
    '/', 'index',
    '/add', 'add'
)

class index:
    def GET(self):
        todos = db.select('todo')
        return render.index(todos)

class add:
    def POST(self):
        i = web.input()
        n = db.insert('todo', title=i.title)
        raise web.seeother('/')

if __name__ == "__main__":
    app = web.application(urls, globals())
    app.run()




And templates/index.html:

$def with (todos)
<ul>
$for todo in todos:
    <li id="t$todo.id">$todo.title</li>
</ul>

<form method="post" action="add">
<p><input type="text" name="title" /> <input type="submit" value="Add" /></p>
</form>

Once done, run code.py one more time, and you should have a box that dynamically posts more data into your database. In my opinion, that's pretty cool. 

From here, you should have enough information about web.py to tackle some more code from their code samples and cookbook pages. We will probably return to web.py later, but for now we have done enough.

As always, thanks for reading.

-Leios.