access tables in database using web2py DAL from iPython prompt

[curiouslearn]

Recently I created a simple game in web2py for educational purposes. It worked out well, however, for this game I did not make use of a lot of functionality available in web2py; especially the DAL. I used MySQLdb and raw sql queries to do the job. Based on the recommendations here, I have decided to use web2py DAL and other tools provided by web2py  to rewrite the game and to create other apps using web2py. I have a few questions as I tried a few things. Before I state those, a million thanks to Massimo for creating web2py; and to Anthony and others who have helped me here to get out of it when I am stuck.

Questions about DAL

------------------------------

(1) If I am using DAL to create tables, is it true that I should not go and make changes to the database from mysql prompt?

 For example, I created a table using DAL, then went to mysql prompt and deleted the table. When I tried to run the code again, I kept getting an error saying that "table already exists". I looked up on these forums and there were some suggestions about deleting the .table file related to that table in the databases folder of the application. I did that and still kept getting the same error. 

(2)  In the documentation, there is a suggestion for using db.tablename.truncate() for dropping the table. 

    I tried the following:

   (a) Enter "python web2py.py -S applicationname". This gives the iPython prompt.

   (b) Enter db = DAL(DAL('mysql://root:mypasswd@localhost/ultimatumG')

At this point, when I enter db.tablename.drop() (where tablename is the name of a table in ultimatumG database) I get an error saying :

     

    'DAL' object has no attribute tablename.

How do I make small changes to database? It appears I cannot do them from mysql prompt because that confuses the application and for some reason I am unable to connect from iPython prompt.

I am using version 2.0.9 (2012-09-13) of web2py.

Thanks for your help.

[Massimo Di Pierro]

This works for me:

$ python web2py.py -S welcome -N

>>> db = DAL()

>>> db.define_table('tablename',Field('name'))

<Table tablename (id,name)>

>>> db.tablename.drop()

Are you try to drop a table "tablename" that was not defined?

[curiouslearn]

Hi Massimo,

Thanks for replying. 

I am trying to access tables that were already created from definitions either in the db.py file in the models directory, or the definitions in a controller function. Please see the code below. Do I have to define all the tables from the shell prompt to access them, even when they already exist? If so, do I have to write the whole table definition?

I should also mention that I started with welcome app that web2py comes with, changed the folder name and then commented out all the code in db.py in welcome/models before adding the code below.

Thanks once again for your help.

*** The CODE ***

db.py

db = DAL('mysql://root:passwd@localhost/ultimatumG')


# Table containing teamnames
db.define_table('teams',
                Field('teamname', 'string', length=40, required=True,
                      unique=True, notnull=True),
                Field('passwd', 'password'),
                Field('role', 'string', length=20, required=True,
                      default='NA'),
                format = '%(teamname)s')


# Table showing the ask amount of the first mover
db.define_table('offerdecisions',
                Field('round', 'integer'),
                Field('askamount', 'integer'),
                Field('payoff', 'integer'),
                Field('teamname_id', 'reference teams'))


# Table accept-reject decisions
db.define_table('ardecisions',
                Field('round', 'integer'),
                Field('acceptorreject', 'string', length=2),
                Field('payoff', 'integer'),
                Field('teamname_id', 'reference teams'),
                Field('offerer_id', 'reference teams'))




# Table containing a list of ultimatum game experiments
db.define_table('experimentlist',
                Field('experimentname', 'string', length=40))

In addition to the above tables, the following is created from a controller function. 

default.py

def createteams():
    if request.post_vars:
        experimentname = request.post_vars.experimentname
        numteams = int(float(request.post_vars.numteams))
        tablename = "{0}_teams".format(experimentname)
        migratetablename = "{0}.table".format(tablename)
        db.define_table(tablename,
                Field('teamname', 'string', length=40, required=True,
                      unique=True, notnull=True),
                Field('passwd', 'password'),
                Field('role', 'string', length=20, required=True,
                      default='NA'),
                format = '%(teamname)s')
        teamnames = maketeamnames(numteams)
        for tname in teamnames:
            db[tablename].update_or_insert(teamname=tname)
    return dict()

[Massimo Di Pierro]

Make sure you have the -M and -N options and the tables should be visible.

[curiouslearn]

Thanks, Massimo. That worked.

[Vu Pham]

Hi Massimo,

I am connecting to an existing Microsoft SQL database through DAL ----- db = DAL('mssql4://sa:password@localhost/Unity'). This connection seems to be connected because I also created the fake table, and the table was created.

I can view this table from both Microsoft SQL Manager, and Web2py database admistrator. I can access this table by using db().select(db......

However, I can not figure out how to access the existing tables which were created outside Web2py. I kept reading the manual related to Legacy database, and could find anything ...

I also tried to call db.define_table with migrate = false, and it is shown up in Web2py database admistrator page.   BUT can not query it, it keeps saying DAL object has not attribute...

Thanks for helping.

Vu

[Massimo Di Pierro]

Yes you need db.define_table('....', ...., migrate=False) and you need to list all fields in that table that should be visible to web2py and the type they should be mapped to in web2py.