Python DAL.define_table方法代码示例

# 需要导入模块: from pydal import DAL [as 别名]
# 或者: from pydal.DAL import define_table [as 别名]
class DyTables(object):
    def __init__(self, uri=None):
        self._uri = uri
        self._schema = uri.split("/")[-1]
        self._dal = DAL(self._uri)
        self._datatapy_dict = datatype_mysql()
        self.get_tables()

    def get_tables(self):
        _tables = GetAllTables(uri="/".join(self._uri.split("/")[:-1])
                                   + "/information_schema",
                               schema=self._schema)
        for numb, table in enumerate(_tables):
            fields = []
            for field in _tables.get(table):
                try:
                    fields.append(Field(field[0], self._datatapy_dict[field[1]]))
                except SyntaxError:
                    fields.append(Field("r_" + field[0],
                                        self._datatapy_dict[field[1]],
                                        rname=field[0]))
            self._dal.define_table(table, *fields, primarykey=[], migrate=False)

    def get_db(self):
        return self._dal

# 需要导入模块: from pydal import DAL [as 别名]
# 或者: from pydal.DAL import define_table [as 别名]
 def testRun(self):
     db = DAL(DEFAULT_URI, check_reserved=["all"], entity_quoting=True)
     db.define_table("tt", Field("aa"), Field("bb", "boolean"))
     sql = db._adapter.dialect.create_index("idx_aa_f", db.tt, [db.tt.aa], where=str(db.tt.bb == False))
     self.assertEqual(sql, 'CREATE INDEX "idx_aa_f" ON "tt" ("aa") WHERE ("tt"."bb" = \'F\');')
     rv = db.tt.create_index("idx_aa_f", db.tt.aa, where=(db.tt.bb == False))
     self.assertTrue(rv)
     rv = db.tt.drop_index("idx_aa_f")
     self.assertTrue(rv)
     drop(db.tt)

# 需要导入模块: from pydal import DAL [as 别名]
# 或者: from pydal.DAL import define_table [as 别名]
 def testRun(self):
     db = DAL(DEFAULT_URI, check_reserved=['all'])
     db.define_table('tt', Field('vv'))
     vv = 'ἀγοραζε'
     id_i = db.tt.insert(vv=vv)
     row = db(db.tt.id == id_i).select().first()
     self.assertEqual(row.vv, vv)
     db.commit()
     drop(db.tt)
     db.close()

# 需要导入模块: from pydal import DAL [as 别名]
# 或者: from pydal.DAL import define_table [as 别名]
 def testRun(self):
     db = DAL(DEFAULT_URI, check_reserved=['all'])
     db.define_table('tt', Field('vv'))
     db.define_table('ttt', Field('vv'), Field('tt_id', 'reference tt', notnull=True))
     self.assertRaises(Exception, db.ttt.insert, vv='pydal')
     # The following is mandatory for backends as PG to close the aborted transaction
     db.commit()
     drop(db.ttt)
     drop(db.tt)
     db.close()

# 需要导入模块: from pydal import DAL [as 别名]
# 或者: from pydal.DAL import define_table [as 别名]
def get_sys_table(uri="mysql://lms_test:[email protected]/information_schema"):
    sys_tab = DAL(uri=uri)

    sys_tab.define_table('COLUMNS',
                         Field("TABLE_SCHEMA", ),
                         Field("TABLE_NAME"),
                         Field("COLUMN_NAME"),
                         Field("IS_NULLABLE"),
                         Field("DATA_TYPE"),
                         Field("COLUMN_TYPE"),
                         primarykey=[],
                         migrate=False)
    return sys_tab

# 需要导入模块: from pydal import DAL [as 别名]
# 或者: from pydal.DAL import define_table [as 别名]
 def testRun(self):
     cache = SimpleCache()
     db = DAL(DEFAULT_URI, check_reserved=['all'])
     db.define_table('tt', Field('aa'))
     db.tt.insert(aa='1')
     r0 = db().select(db.tt.ALL)
     r1 = db().select(db.tt.ALL, cache=(cache, 1000))
     self.assertEqual(len(r0), len(r1))
     r2 = db().select(db.tt.ALL, cache=(cache, 1000))
     self.assertEqual(len(r0), len(r2))
     r3 = db().select(db.tt.ALL, cache=(cache, 1000), cacheable=True)
     self.assertEqual(len(r0), len(r3))
     r4 = db().select(db.tt.ALL, cache=(cache, 1000), cacheable=True)
     self.assertEqual(len(r0), len(r4))
     drop(db.tt)

# 需要导入模块: from pydal import DAL [as 别名]
# 或者: from pydal.DAL import define_table [as 别名]
 def testRun(self):
     for ref, bigint in [('reference', False), ('big-reference', True)]:
         db = DAL(DEFAULT_URI, check_reserved=['all'], bigint_id=bigint)
         db.define_table('tt', Field('vv'))
         db.define_table('ttt', Field('vv'), Field('tt_id', '%s tt' % ref,
                                                   unique=True))
         id_i = db.tt.insert(vv='pydal')
         # Null tt_id
         db.ttt.insert(vv='pydal')
         # first insert is OK
         db.ttt.insert(tt_id=id_i)
         self.assertRaises(Exception, db.ttt.insert, tt_id=id_i)
         # The following is mandatory for backends as PG to close the aborted transaction
         db.commit()
         drop(db.ttt)
         drop(db.tt)
         db.close()

# 需要导入模块: from pydal import DAL [as 别名]
# 或者: from pydal.DAL import define_table [as 别名]
class DBCache(Cache):
    '''An implementation of the c9r.file.cache.FileCache with PyDAL using
    database.
    '''
    defaults = {
        'db': 'sqlite://cache.db',      # Database URL
        }
    def_conf = ['~/.etc/cache-conf.json']

    def clear(self, clear_all=True):
        '''Remove the entire content(s) in the cache.
        '''
        db = self.db
        db((db.vars.id>=0) if clear_all else (db.vars.expires<time())).delete()

    def clear_cache(self, vset, names):
        '''
        '''

    def get(self, vset, name):
        '''Get a named data from this cache.
        '''
        db = self.db
        rows = db((db.vars.name==name)&(db.vars.expires<time())).select()
        return rows[0].value

    def put(self, vset, name, data):
        '''Save given data into the cache with given name.
        '''
        self.db.vars.insert(name=name, value=data, expires=time()+self.window)

    def __init__(self, conf=[], initconf=None):
        '''
        '''
        Cache.__init__(self, conf, initconf)
        self.db = DAL(self.config('db'))
        self.db.define_table('varset', Field('name'))
        self.db.define_table('vars',
                             Field('name'), Field('value', 'json'),
                             Field('varset'),
                             Field('expires', 'integer'),
                             primarykey=['varset', 'name'])
        self.window = int(self.config('window'))

# 需要导入模块: from pydal import DAL [as 别名]
# 或者: from pydal.DAL import define_table [as 别名]
 def testRun(self):
     db = DAL(DEFAULT_URI, check_reserved=['all'])
     db.define_table('val_and_insert',
                     Field('aa'),
                     Field('bb', 'integer',
                           requires=IS_INT_IN_RANGE(1, 5)))
     rtn = db.val_and_insert.validate_and_insert(aa='test1', bb=2)
     if NOSQL:
         self.assertEqual(isinstance(rtn.id, long), True)
     else:
         self.assertEqual(rtn.id, 1)
     #errors should be empty
     self.assertEqual(len(rtn.errors.keys()), 0)
     #this insert won't pass
     rtn = db.val_and_insert.validate_and_insert(bb="a")
     #the returned id should be None
     self.assertEqual(rtn.id, None)
     #an error message should be in rtn.errors.bb
     self.assertNotEqual(rtn.errors.bb, None)
     #cleanup table
     drop(db.val_and_insert)

# 需要导入模块: from pydal import DAL [as 别名]
# 或者: from pydal.DAL import define_table [as 别名]
    def setUp(self):
        db = DAL('sqlite:memory')

        db.define_table('color', Field('name', requires=IS_NOT_IN_DB(db, 'color.name')))
        db.color.insert(name='red')
        db.color.insert(name='green')
        db.color.insert(name='blue')

        db.define_table('thing', Field('name'), Field('color', 'reference color'))
        db.thing.insert(name='Chair', color=1)
        db.thing.insert(name='Chair', color=2)
        db.thing.insert(name='Table', color=1)
        db.thing.insert(name='Table', color=3)
        db.thing.insert(name='Lamp', color=2)
    
        db.define_table('rel', Field('a', 'reference thing'), Field('desc'), Field('b','reference thing'))
        db.rel.insert(a=1, b=2, desc='is like')
        db.rel.insert(a=3, b=4, desc='is like')
        db.rel.insert(a=1, b=3, desc='is under')
        db.rel.insert(a=2, b=4, desc='is under')
        db.rel.insert(a=5, b=4, desc='is above')

        api = DBAPI(db, ALLOW_ALL_POLICY)

        self.db = db
        self.api = api

# 需要导入模块: from pydal import DAL [as 别名]
# 或者: from pydal.DAL import define_table [as 别名]
 def preloop(self):
     db = DAL("sqlite://grades.db")
     db.define_table(
         "students",
         Field("fname", notnull=True),
         Field("lname", notnull=True),
         Field("dob", "date", notnull=True),
         Field("sex_is_f", "boolean", notnull=True),
     )
     db.define_table(
         "courses",
         Field("course_id", notnull=True),
         Field("category", notnull=True),
         Field("description", notnull=True),
         Field("professor", notnull=True),
         Field("classroom", notnull=True),
         Field("long_descr", "text"),
         Field("start_date", "date", notnull=True),
         Field("end_date", "date", notnull=True),
     )
     db.define_table(
         "students_to_courses",
         Field("student", "reference students", notnull=True),
         Field("course", "reference courses", notnull=True),
     )
     db.define_table(
         "assignments",
         Field("course", "reference courses", notnull=True),
         Field("description", notnull=True),
         Field("max_points", "integer", notnull=True),
         Field("due_date", "date", notnull=True),
     )
     db.define_table(
         "grades",
         Field("student", "reference students", notnull=True),
         Field("assignment", "reference assignments", notnull=True),
         Field("grade", "integer"),
     )
     self.db = db

# 需要导入模块: from pydal import DAL [as 别名]
# 或者: from pydal.DAL import define_table [as 别名]
class DbHelper(object):
	"""docstring for DbHelper"""
	def __init__(self, arg):
		super(DbHelper, self).__init__()
		self.arg = arg
		self.db = DAL('mongodb://140.143.247.178:27099/spider')
		self.define_table()
		'''
		self.db.thing.insert(name='Chair')
		query = self.db.thing.name.startswith('C')
		rows = self.db(query).select()
		print(rows[0].name)
		self.db.commit()
		'''

	def define_table(self):
		print(self.db._dbname)
		self.db.define_table('douban_topic',Field('title'),Field('title_url'),Field('people'),Field('people_url')
                             ,Field('replay_num'),Field('post_time'))

	def insert_models(self,table_name='',items=[]):
		a = list(map(dict,items))
		self.db.douban_topic.bulk_insert(a)
		self.db.commit()

# 需要导入模块: from pydal import DAL [as 别名]
# 或者: from pydal.DAL import define_table [as 别名]
class DNSAPI(object):
    """DNSAPI is an easy way to use your database so dockerlab-dns knows how to read your records. Use this class to interact with the database or just use it as an example."""

    def __init__(self, db_user, db_pass, db_host, db_name, migrate=False):
        super(DNSAPI, self).__init__()
        self.db = DAL("postgres://%s:%[email protected]%s/%s" % (db_user, db_pass, db_host, db_name), migrate=migrate)

        if self.db:
            print 'Successfully connected to db "%s" on host "%s"' % (db_name, db_host)

        self.db.define_table(
            "dns_zones",
            Field(
                "name", "string"
            ),  # ends in . (e.g. example.com.); input should probably have a validator to ensure zones end in a .
        )

        self.db.define_table(
            "dns_zone_records",
            Field("zone", "reference dns_zones"),
            Field("record_name", "string"),  # (e.g. ns1.example.com.)
            Field(
                "record_type", "string", default="A", requires=IS_IN_SET(RECORD_TYPES)
            ),  # (e.g. A, AAAA, CNAME, MX, NS)
            Field(
                "record_value", "string"
            ),  # (e.g. an IP for A or AAAA, an address for CNAME, and an address and priority for MX)
            Field(
                "record_ttl", "integer", default=60 * 5
            ),  # A TTL in seconds before a client should check for a new value. Can reasonably set to lower or higher depending on the volatility of the records
        )

    def get_zones(self):
        return self.db(self.db.dns_zones).select().as_list()

    def get_zone(self, zone_name):
        return self.db(self.db.dns_zones.name == zone_name).select().as_list()

    def get_records(self, zone_id):
        return self.db((self.db.dns_zone_records.zone == zone_id)).select().as_list()

    def get_records_matching(self, zone_id, record_name=None, record_type=None):
        q = self.db.dns_zone_records.zone == zone_id
        if not record_name is None:
            q = q & (self.db.dns_zone_records.record_name == record_name)
        if not record_type is None:
            q = q & (self.db.dns_zone_records.record_type == record_type)
        return self.db(q).select().as_list()

    def delete_zone(self, zone_name):
        self.db.dns_zones(
            (self.db.dns_zone_records.zone == self.db.dns_zones.id) & (self.db.dns_zones.name == zone_name)
        ).delete()
        return self.db(self.db.dns_zones.name == zone_name).delete()

    def delete_record(self, record_id):
        return self.db((self.db.dns_zone_records.id == record_id)).delete()

    def delete_record_matching(self, zone_id, record_name, record_type):
        return self.db(
            (self.db.dns_zone_records.zone == zone_id)
            & (self.db.dns_zone_records.record_name == record_name)
            & (self.db.dns_zone_records.record_type == record_type)
        ).delete()

    def create_zone(self, zone_name):
        return self.db.dns_zones.insert(name=zone_name)

    def create_record(self, zone_id, record_name, record_type, record_value, record_ttl):
        return self.db.dns_zone_records.insert(
            zone=zone_id,
            record_name=record_name,
            record_type=record_type,
            record_value=record_value,
            record_ttl=record_ttl,
        )

# 需要导入模块: from pydal import DAL [as 别名]
# 或者: from pydal.DAL import define_table [as 别名]
def model():
    db = DAL('sqlite://pin.db',pool_size=1,folder='./',migrate=False)
    Pin=db.define_table('pin',Field('title'),Field('image'))
    return (db,Pin)

# 需要导入模块: from pydal import DAL [as 别名]
# 或者: from pydal.DAL import define_table [as 别名]
from pydal import DAL, Field

db = DAL('sqlite:memory:')

db.define_table('persons',
	Field('name'),
	Field('age')
)

amy = db.persons.insert( name='Amy', age=52 )
bob = db.persons.insert( name='Bob', age=48 )
cat = db.persons.insert( name='Cat', age=23 )
dan = db.persons.insert( name='Dan', age=17 )
edd = db.persons.insert( name='Edd', age=77 )
fan = db.persons.insert( name='Fan', age=65 )
gin = db.persons.insert( name='Gin', age=27 )
hil = db.persons.insert( name='Hil', age=30 )
iri = db.persons.insert( name='Iri', age=62 )
jac = db.persons.insert( name='Jac', age=18 )
db.commit()

# Export the 'persons' database
with open( 'persons.csv', 'wb' ) as f:
	f.write( str(db(db.persons.id).select()) )

# Export only the young persons
with open( 'young-people.csv', 'wb') as f:
	people = db( db.persons.age <= 30 ).select()
	f.write( str( people ) )