Use web2py without DAL

[curiouslearn]

Hi,

I would like to use web2py without DAL. I want to use it with an sqlite database in a .db file. Are there good examples of how to do this? I am looking for things such as 

(a) Where do I save the .db database file (i.e., which folder within the application).

(b) Can I connect to it and use it (read information, store information) using the standard python sqlite3 module commands? If so, is there a specific way to specify the path to the database (from the controller functions)? 

I know I cannot use SQLFORM and other facilities to generate forms. I don't really care about it right now. I am learning sqlite also and would like to use the raw sqlite commands to work with it (or sqlite3 python module) and would like to have the database available for use even outside web2py. 

Thanks for your help.

[pbreit]

I have no idea why you would want to do this but here's a start. test.db will be in your web2py directory.

def sqlite():

    import sqlite3 as lite

    con = None

    try:

        con = lite.connect('test.db')

        cur = con.cursor()    

        cur.execute('SELECT SQLITE_VERSION()')

        data = cur.fetchone()

    

    except lite.Error, e:

        data = 'error'

    

    finally:

        if con:

            con.close()

    return dict(data=data)

[simon]

You could go half way and use the DAL to connect to the database and execute SQL commands directly.  This would give you ability to experiment with sqlite and also allow access to the time saving things in the DAL later.:

  http://web2py.com/books/default/chapter/29/6#Raw-sql 

The database file is in the "databases" folder (gee why do they make it so complicated!!!)

You can still use the database outside web2py whether using the DAL or not.. If you use db.define_table then it will automatically add an id field to each table. Other than that it is just a standard database.

[Anthony]

(a) Where do I save the .db database file (i.e., which folder within the application).

Anywhere you want. One option is in /web2py/applications/yourapp/databases, but doesn't have to be in the application folder at all.

 

(b) Can I connect to it and use it (read information, store information) using the standard python sqlite3 module commands?

Sure.

 

If so, is there a specific way to specify the path to the database (from the controller functions)?

The path to your application folder is available in request.folder, so you could use that as the starting point and traverse up or down from there. Or just use an absolute path on your filesystem.

Anthony

[Curiouslearn]

pbreit, Simon and Anthony,

Thanks very much for your responses. I am trying out Anthony's
suggestions first.

Can you please help me with the path? I can connect when I give the
absolute path on the filesystem, but I want to use relative path so
that it works on different computers and I can email the application
to colleagues.

I tried the path '/myapplicationname/databases/test.sqlite', the sort
of path that can be used for images. But I get the following error.

<class 'sqlite3.OperationalError'> unable to open database file

What is request.folder? How do I find it?

The reason I want to do this without DAL is the following: I am new to
databases, SQL and Sqlite, webdevelopment, and web2py. I feel that I
should gain some basic understanding of sqlite before I connect to it
using other facilities such as DAL, and hence want to play directly
with it along with learning development of web applications. Also,
while I am learning it, I want to avoid the automation, such as
creation of id column.

Thanks again.

[Anthony]

I tried the path '/myapplicationname/databases/test.sqlite', the sort
of path that can be used for images. But I get the following error.

<class 'sqlite3.OperationalError'> unable to open database file

What is request.folder? How do I find it?

The request object is available in the web2py environment on every request -- request.folder stores the filesystem path to the current application's folder. You can access it anywhere in your app code. See http://web2py.com/books/default/chapter/29/4#request. To construct a full path, you should use os.path.join:

import os
sqlite_file_path = os.path.join(request.folder, 'databases', 'test.sqlite')

That should produce something like '/path/to/web2py/applications/yourapplication/databases/test.sqlite' (of course, it will vary by OS -- on Windows it would start with C: and use \'s instead of /'s).

Anthony

[Curiouslearn]

Thanks a lot Anthony. I was just going to email that I figured it out.
Sorry, should have searched the documentation for request.folder
earlier.

Thanks to you and everyone else here who is providing help with web2py.

[pbreit]

I sort of understand that reasoning but I think it's unwise to fight the tools, especially as a beginner. Web2py shows the SQL queries on every page. The DAL syntax mimics SQL to quite an extent. If you want to practice queries, get the SQLite Manager plugin on Firefox. You'll start to learn SQL. But trying to talk to SQLite directly will be a total rat hole.