使用 web2py DAL.define_table() 初始化数据库表的正确方法

发布于 2024-12-23 03:15:59 字数 2606 浏览 1 评论 0原文

我正在尝试使用完全相同的 SQL 定义动态构建名为 db.blog 和 db.code 的表。定义它们后，我想用 10 行随机数据填充它们，并且不再执行该初始化代码。

我的问题是，当我查看 db.code 或 db.blog 的 newblog appadmin 界面时，每次在浏览器上刷新时都会执行初始化代码：https://172.25.1.1/newblog/appadmin/select/db?query=db.code.id>0

我初始化 db.blog 和 < newblog/models/newblog.py 中的 code>db.code：

from gluon import *
from gluon.contrib.populate import populate

## initialize db.blog and db.code: 
##     At runtime, build TAGGED_TABLES (once)
TAGGED_TABLES = set(['blog', 'code'])
for tt in TAGGED_TABLES:
    if not db.get(tt, False):
        db.define_table(tt,
            Field('name', length=32, notnull=True),
            Field('value', length=65535, notnull=True),
            Field('tags', type='list:reference tag', unique=False, notnull=False),
            )

        populate(db.get(tt), 10)

        ## cross-reference db.tagged_tables to this one
        db.tagged_tables.insert(name=tt,
            database_pointer='reference %s' % tt)
        db.commit()

不知何故 if not db.get(tt, False): 允许例程多次执行在它下面。我不明白为什么......如果表已经创建，那么 not db.get(tt, False) 应该是 False。但是，web2py 永远不会跳过初始化代码，这意味着 db.blog 和 db.code 在每次重新加载时都会增加 10 个条目。

问题：为什么 if not db.get(tt, False): 不阻止多次执行？

我在 Debian 6.0 / sqlite 3.7.3 / Cherokee 1.2.101 / uWSGI 0.9.9.3 上运行 web2py 1.99.4

基于 Interrobang 答案的解决方案，

正确的写法是：

from gluon import *
from gluon.contrib.populate import populate


TAGGED_TABLES = set(['blog', 'code'])
for tt in TAGGED_TABLES:
    # db.define_table() must be called on **every page**
    #    this sets things up in memory...
    db.define_table(tt,
        Field('name', length=32, notnull=True),
        Field('value', length=65535, notnull=True),
        Field('tags', type='list:reference tag', unique=False, notnull=False),
        )

    ## initialize db.blog and db.code: 
    ##     At runtime, populate tables named in TAGGED_TABLES (once)
    if not (db(db.get(tt).id>0).select()):
        populate(db.get(tt), 10)
        ## cross-reference db.tagged_tables to this table (named in var tt)
        db.tagged_tables.insert(name=tt,
            database_pointer='reference %s' % tt)
        db.commit()

现在 db.blog 和db.code 保持恒定大小。

总结

每次页面渲染都必须调用db.define_tables()；我的理解（它只需要运行一次即可将表定义写入磁盘）是不正确的。

原文

I am trying to dynamically build tables called db.blog and db.code with exactly the same SQL definitions. After I define them, I want to populate them with 10 rows of random data, and never execute that initialization code again.

My problem is that the initialization code executes every time I hit refresh on the browser while I view the newblog appadmin interface for db.code or db.blog: https://172.25.1.1/newblog/appadmin/select/db?query=db.code.id>0

I initialize db.blog and db.code in newblog/models/newblog.py:

from gluon import *
from gluon.contrib.populate import populate

## initialize db.blog and db.code: 
##     At runtime, build TAGGED_TABLES (once)
TAGGED_TABLES = set(['blog', 'code'])
for tt in TAGGED_TABLES:
    if not db.get(tt, False):
        db.define_table(tt,
            Field('name', length=32, notnull=True),
            Field('value', length=65535, notnull=True),
            Field('tags', type='list:reference tag', unique=False, notnull=False),
            )

        populate(db.get(tt), 10)

        ## cross-reference db.tagged_tables to this one
        db.tagged_tables.insert(name=tt,
            database_pointer='reference %s' % tt)
        db.commit()

Somehow if not db.get(tt, False): allows multiple executions of the routine below it. I don't understand why... if the table has already been created, then not db.get(tt, False) should be False. However, web2py never skips the initialization code, which means db.blog and db.code grow by 10 entries on each reload.

Question: Why isn't if not db.get(tt, False): preventing multiple executions?

I am running web2py 1.99.4 on Debian 6.0 / sqlite 3.7.3 / Cherokee 1.2.101 / uWSGI 0.9.9.3

Solution

Based on Interrobang's answer the correct way to write this is:

from gluon import *
from gluon.contrib.populate import populate


TAGGED_TABLES = set(['blog', 'code'])
for tt in TAGGED_TABLES:
    # db.define_table() must be called on **every page**
    #    this sets things up in memory...
    db.define_table(tt,
        Field('name', length=32, notnull=True),
        Field('value', length=65535, notnull=True),
        Field('tags', type='list:reference tag', unique=False, notnull=False),
        )

    ## initialize db.blog and db.code: 
    ##     At runtime, populate tables named in TAGGED_TABLES (once)
    if not (db(db.get(tt).id>0).select()):
        populate(db.get(tt), 10)
        ## cross-reference db.tagged_tables to this table (named in var tt)
        db.tagged_tables.insert(name=tt,
            database_pointer='reference %s' % tt)
        db.commit()

Now db.blog and db.code stay a constant size.

Summary

db.define_tables() must be called for every page rendering; my understanding (that it only needed to run once to write the table definition to disk) was incorrect.

分享到QQ

分享到微博