Python web.query函数代码示例

def external_point_update(point, user):
    db_point = list(point_by_uuid(point.uuid) or [])
    if db_point:
        # !!! should we update lat, lon too?
        point.id = db_point[0].id
        web.query("""
            UPDATE locations
                SET title=$title
                WHERE id = $id
                ;
            """, vars=point.copy())
        # how to update tags???
        # tags_insert(point, user, point.tags, deny_namespaces=[])
        return "update"
    else:
        web.insert("locations", 
                    lat=point.lat, 
		    lon=point.lon, 
		    title=point.title,
                    uuid=point.uuid,
                    user_id=user.id, 
		    origin=point.origin,
                    added=point.added,  #!!!?
		    url=point.url)
        db_point = list(point_by_uuid(point.uuid))[0]
        tags_insert(db_point, user, point.tags, deny_namespaces=[])
        point.id = db_point.id
        return "insert"

def clean(lifetime=2592000):
    """Delete all sessions older than lifetime
    We could call this on every request, but may as well just do it 
    at some periodic interval. """
    timestamp = int(time.time()) - lifetime
    web.query("DELETE FROM sessions WHERE timestamp < $timestamp", vars=locals())
    return True

def remove_datasource_from_project(ds, project):
    web.query("""
        DELETE FROM locations_datasources
            WHERE locations_datasources.location_id = $project_id
                AND locations_datasources.datasource_id = $ds_id
            ;
    """, vars=dict(ds_id=ds.id, project_id=project.id))

def drupy_cron():
    """clean out old stuff in the watchdog table"""
    time1 = time() - variable_get('watchdog_clear', 604800)
    time2 = time() - 3600
    web.transact()
    web.query('DELETE FROM watchdog WHERE timestamp < $time1', vars=locals())
    web.query('DELETE FROM flood WHERE timestamp < $time2', vars=locals())
    web.commit()

def point_update(point, user):
    web.query("""
        UPDATE locations
            SET title=$title,
                uuid=$uuid
            WHERE id = $id
            ;
        """, vars=point.copy())

def regenerate(uid=0):
    """Called when an anonymous user becomes authenticated or vice-versa."""
    old_session_id = web.cookies()._SID_
    new_session_id = _generate_id()
    web.setcookie("_SID_",new_session_id)
    #uid = int(uid)
    #print web.query("UPDATE sessions SET uid = '$uid', sid = $new_session_id WHERE sid = $old_session_id", vars=locals(),_test=True)
    web.query("UPDATE sessions SET uid = $uid, sid = $new_session_id WHERE sid = $old_session_id", vars=locals())

def remove_point_from_project(point, project):
    web.query("""
        UPDATE projects_points
            SET visible = 0
            WHERE location_id = $point_id
                AND project_id = $project_id
            ;
    """, vars=dict(point_id=point.id, project_id=project.id))

def unset_policies(object, user, roles, adder_user):
    for role in roles:
        web.query("""
        DELETE FROM locations_policy_table
            WHERE user_id = $user_id 
            AND location_id=$location_id
            AND role=$role
        ;""", vars={'user_id': user.id, 'location_id': object.id,
            'role': role})

 def delete(self, id, providerName):
     username = self.usernameProvider.get()
     web.transact() 
     web.delete('places',  where="providerid=%s and provider=%s" % \
         (web.db.sqlquote(id), web.db.sqlquote(providerName)))
     web.query(self.SQL_ACTION % (web.db.sqlquote(username),
                      web.db.sqlquote('elimino propiedad %s-%s' %
                      (providerName, id))));
     web.commit()

def query(sql_query, vars={}, limit = 10, count_query = None, processed=False, _test=False):
    """Works very similar to web.query(), but it returns a tuple of the query result 
    and pager object (which just holds the page numbers links to display)
    
    Typical use:
    iter_nodes, page_nums = inc.pager.query('SELECT * FROM node WHERE uid=5 ORDER BY nid')
    for node in iter_nodes:
        print node
    print page_nums.render()
    
    DOCTEST
    >>> import pager
    >>> limit=10
    >>> iter_nodes, page_nums = pager.query('''SELECT n.nid, c.cache, c.nid \
    ...     AS cache_nid, c.vid as cache_vid, n.vid, n.type, \
    ...     n.status, n.created, n.changed, n.comment, n.promote, n.sticky, \
    ...     u.uid, u.name, u.picture, u.data FROM node n INNER JOIN \
    ...     users u ON u.uid = n.uid LEFT JOIN cache_node c ON c.nid = n.nid \
    ...     AND c.vid = n.vid WHERE n.promote = 1 AND n.status = 1 \
    ...     ORDER BY n.sticky DESC, n.created DESC''',limit=limit, _test=True)
    count_query: SELECT COUNT(*) FROM node n INNER JOIN     users u ON u.uid = n.uid LEFT JOIN cache_node c ON c.nid = n.nid     AND c.vid = n.vid WHERE n.promote = 1 AND n.status = 1    
    >>> iter_nodes
    <sql: 'SELECT n.nid, c.cache, c.nid     AS cache_nid, c.vid as cache_vid, n.vid, n.type,     n.status, n.created, n.changed, n.comment, n.promote, n.sticky,     u.uid, u.name, u.picture, u.data FROM node n INNER JOIN     users u ON u.uid = n.uid LEFT JOIN cache_node c ON c.nid = n.nid     AND c.vid = n.vid WHERE n.promote = 1 AND n.status = 1     ORDER BY n.sticky DESC, n.created DESC LIMIT 10 OFFSET 40'>
    
    NOTE: right now the regex only works when the sql is all caps.  
        i.e. inc.pager.query('select * From node WHERE uid=5 ORDER BY nid')
        would not work.  It's a good convention, but maybe should fix the regex
        to accept non caps in the future?
    """

    if not processed and not isinstance(sql_query, web.db.SQLQuery):
        sql_query = str(web.db.reparam(sql_query, vars))

    if not count_query:
        p = re.compile(r'SELECT.*?FROM (.*) ORDER BY .*')
        count_query = p.sub(lambda m: "SELECT COUNT(*) FROM %s" % m.group(1), sql_query)

    if _test:
        num_pages=10
        page = 5
        print 'count_query:', count_query
    else:
        count = web.query(count_query)[0].values()[0]
        num_pages = int(float(count) / limit + 1)
        page = _current_page()
    
    #page number validation
    #todo !!! wait a minute, maybe these two lines are no good. 
    # cause then there can be many urls for the first and last pages...
    if page < 1: page=1
    elif page > num_pages: page=num_pages
    
    p = pager(page,num_pages)
    
    offset = (page-1)*limit
    return web.query(''.join((sql_query,' LIMIT $limit OFFSET $offset')),
            vars={'limit':limit,'offset':offset},_test=_test), p

 def __delitem__(self, key):
     if not isinstance(key, tuple):
         key = None, key
     web.query("""DELETE FROM profile 
             WHERE lang=$lang AND prop_key=$prop_key;""", 
             vars=dict(lang=key[0], prop_key=key[1],))
     try:
         del self.__dict__["_data"][key]
     except:
         pass  # no problem if the key is not there

 def __setitem__(self, key, item):
     # this should be transaction, but is ok as
     # profile should not be edited by many people at once
     self.__delitem__(key)
     if not isinstance(key, tuple):
         key = None, key
     web.query("""INSERT INTO profile (lang, prop_key, prop_value) 
                 VALUES ($lang, $prop_key, $prop_value);""", 
               vars=dict(lang=key[0], prop_key=key[1], prop_value=item))
     self.__dict__["_data"][key] = item

    def POST(self, providerName, id, state):
        lid = scrappers[providerName].local(id)
        web.transact() 
        web.query(self.SQL_UPDATE % (web.db.sqlquote(state), lid))
        web.query(self.SQL_ACTION % (web.db.sqlquote(usernameProvider.get()),
          web.db.sqlquote('cambio al estado %s la prop  %s-%s' % 
             (state,providerName,id))));

        web.commit()
        web.seeother('../../')

def multiple_insert(table, values, seqname=None):
    """Inserts multiple rows into a table using sql copy."""
    def escape(value):
        if value is None:
            return "\N"
        elif isinstance(value, basestring): 
            value = value.replace('\\', r'\\') # this must be the first one
            value = value.replace('\t', r'\t')
            value = value.replace('\r', r'\r')
            value = value.replace('\n', r'\n')
            return value
        elif isinstance(value, bool):
            return value and 't' or 'f'
        else:
            return str(value)
            
    def increment_sequence(seqname, n):
        """Increments a sequence by the given amount."""
        d = web.query(
            "SELECT setval('%s', $n + (SELECT last_value FROM %s), true) + 1 - $n AS START" % (seqname, seqname), 
            locals())
        return d[0].start
        
    def write(path, data):
        f = open(path, 'w')
        f.write(web.utf8(data))
        f.close()
        
    if not values:
        return []
        
    if seqname is None:
        seqname = table + "_id_seq"
        
    #print "inserting %d rows into %s" % (len(values), table)
        
    columns = get_table_columns(table)
    if seqname:
        n = len(values)
        start = increment_sequence(seqname, n)
        ids = range(start, start+n)
        for v, id in zip(values, ids):
            v['id'] = id
    else:
        ids = None
        
    data = []
    for v in values:
        assert set(v.keys()) == set(columns)
        data.append("\t".join([escape(v[c]) for c in columns]))
        
    filename = tempfile.mktemp(suffix='.copy', prefix=table)
    write(filename, "\n".join(data))
    web.query("COPY %s FROM '%s'" % (table, filename))
    return ids

def get_default_values(sel_ik, index):
  """will print all the possible keys an index could use in order to still
  show at least one file with the current selection of i/k in mind"""
  value_per_index = []


  #get all the possible values from the index
  if sel_ik:

    #building the ignore clause
    for (sel_index, sel_value) in sel_ik:
      if sel_index == index:
        if value_per_index:
          value_per_index.append(sel_value)
        else:
          value_per_index = [sel_value, ]
    if value_per_index:
      ignore_clause = "and %s not in ("+", ".join(['"'+ivalue+'"' for ivalue in value_per_index])+")"
    else:
      ignore_clause = ""

    if index in config.have_many_values:
      additional_clauses = build_clauses(sel_ik, 'and ', 'images.')
      if ignore_clause:
        temp_ignore_clause = ignore_clause % "value"
      else:
        temp_ignore_clause = ""
      #web.debug('GET VALUE : select value, count(images.id) as quantity from images_%(index)ss , %(index)ss, images where %(index)s_id = %(index)ss.id and images_%(index)ss.image_id = images.id %(temp_ignore_clause)s %(additional_clauses)s group by %(index)s_id' % (vars()))
      return web.query('select value, count(images.id) as quantity from images_%ss , %ss, images where %s_id = %ss.id and images_%ss.image_id = images.id %s %s group by %s_id order by %s' % (index, index, index, index, index, temp_ignore_clause , additional_clauses, index, get_order(index)))
    else:
      additional_clauses = build_clauses(sel_ik, 'and ')
      if index in config.compound_indexes.keys():
        #need to get database specific query to match value
        db_value =  config.compound_indexes[index]['reverse_query']()
      else:
        db_value = index
      if ignore_clause:
        temp_ignore_clause = ignore_clause % db_value
      else:
        temp_ignore_clause = ""
      #web.debug('GET VALUE: select %s as value, count(id) as quantity from images where 1=1 %s %s group by value' % (db_value, temp_ignore_clause , additional_clauses))
      return web.query('select %s as value, count(id) as quantity from images where 1=1 %s %s group by value order by %s' % (db_value, temp_ignore_clause , additional_clauses, get_order(index)))
  else:
    #simpler case, left here for the sake of simplicity
    if index in config.have_many_values:
      #web.debug('select value, count(image_id) as quantity from images_%ss , %ss where %s_id = id  group by %s_id' % (index, index, index, index))
      return web.query('select value, count(image_id) as quantity from images_%ss , %ss where %s_id = id  group by %s_id order by %s' % (index, index, index, index , get_order(index)))
    else :
      if index in config.compound_indexes.keys():
        #need to get database specific query to match value
        db_value =  config.compound_indexes[index]['reverse_query']()
      else:
        db_value = index
      return web.query('select %s as value, count(id) as quantity from images group by value order by %s' % (db_value, get_order(index)))