def cargar(self):
		db = web.database(dbn="mysql", user="blockbuster", pw="tapw2014", db="blockbuster")
		#consultas
		cliente = db.select("clientes")
		rentas = db.query("SELECT rentas.id_renta,rentas.id_cliente,rentas.id_pelicula,fecha_renta,fecha_entrega,titulo,entregada FROM `rentas`,peliculas WHERE rentas.id_pelicula=peliculas.id_pelicula")
		peliculas = db.select("peliculas")
		ubicaciones = db.select("ubicacion")
		generos = db.select ("generos", order="genero DESC")
		#crear cliente.json
		cliente_row = cliente.list()
		file_cliente=open('static/json/clientes.json','w')
		json.dump(cliente_row,file_cliente)
		file_cliente.close()
		db = web.database(dbn="mysql", user="blockbuster", pw="tapw2014", db="blockbuster")
		#crear rentas.json
		rentas_row = rentas.list()
		file_rentas=open('static/json/rentas.json','w')
		json.dump(rentas_row,file_rentas)
		file_rentas.close()
		#crear peliculas.json
		peliculas_row = peliculas.list()
		file_peliculas = open('static/json/peliculas.json','w')
		json.dump(peliculas_row,file_peliculas)
		#crear ubicacion.json
		ubicaciones_row = ubicaciones.list()
		file_ubicaciones = open('static/json/ubicacion.json', 'w')
		json.dump(ubicaciones_row,file_ubicaciones)
		file_ubicaciones.close()
		#crear generos.json
		generos_row = generos.list()
		file_generos = open('static/json/generos.json','w')
		json.dump(generos_row,file_generos)
		file_generos.close()

def init_web_db():
  parser = ConfigParser.SafeConfigParser()
  parser.optionxform = str
  parser.read("config.cfg")

  section = 'database'
  if 'database' not in parser.sections():
    raise Exception("No database section in config.cfg")

  dbn = parser.get(section, 'dbn')

  if dbn == "mysql":
    db = parser.get(section, 'db')
    user = parser.get(section, 'user')
    pw = parser.get(section, 'pw')
    host = parser.get(section, 'host')
    db = web.database(dbn='mysql', db=db, user=user, pw=pw, host=host)
    
    db.query('SET NAMES utf8;')
    db.query('SET CHARACTER SET utf8;')
    db.query('SET character_set_connection=utf8;')
  elif dbn == "sqlite":
    dbname = parser.get(section, 'db')
    db = web.database(dbn='sqlite', db=dbname)
    
    # We need to mimic some MySQL functions in order to be able to use
    # SQLite or use different SQL commands for each database server. I
    # prefer the 1st option, naturally...
    db._db_cursor().connection.create_function("concat", 2, sqlite_concat)
    db._db_cursor().connection.create_function("conv", 3, sqlite_conv)
    db._db_cursor().connection.create_function("instr", 2, sqlite_instr)
    db._db_cursor().connection.create_function("rand", 0, sqlite_rand)

  return db

	def __init__(self):
		self.db1 = web.database(dbn='mysql', db = 'episode', user = 'root', pw= 'abc111--', host = '127.0.0.1')
		self.db1.printing = True
		self.db2 = web.database(dbn='mysql', db = 'episode_soc', user = 'root', pw= 'abc111--', host = '127.0.0.1')
		self.db2.printing = True
		self.db3 = web.database(dbn='mysql', db = 'cl_episode', user = 'root', pw= 'abc111--', host = '127.0.0.1')
		self.db3.printing = True
		self.db4 = web.database(dbn='mysql', db = 'cl_episode_soc', user = 'root', pw= 'abc111--', host = '127.0.0.1')
		self.db4.printing = True

def setup_database(dbname, pooling=False):
    if dbname == 'sqlite':
        db = web.database(dbn=dbname, db='webpy.db', pooling=pooling)
    else:
        db = web.database(dbn=dbname, db='webpy', user='scott', pw='tiger', pooling=pooling)
        
    if '-v' in sys.argv:
        db.printing = True
    return db

def codecrawler():
	proj=web.database(host='222.201.131.208', port=3306, dbn='mysql', db='proj', user='myadmin', pw='scutensave')
	daplatform=web.database(dbn='mysql', db='daplatform', user='root', pw='123')
	starttime=datetime.datetime.now()
	print '开始收集数据'
	results=proj.select("type_translate", order='code DESC')
	for record in results:
		daplatform.insert("type_translate", code=record.code, type=record.type)
	endtime=datetime.datetime.now()
	print '耗时 ：', (endtime-starttime).seconds ,' s'

def setup_database(dbname, driver=None, pooling=False):
    if dbname == "sqlite":
        db = web.database(dbn=dbname, db="webpy.db", pooling=pooling, driver=driver)
    elif dbname == "postgres":
        user = os.getenv("USER")
        db = web.database(dbn=dbname, db="webpy", user=user, pw="", pooling=pooling, driver=driver)
    else:
        db = web.database(dbn=dbname, db="webpy", user="scott", pw="tiger", pooling=pooling, driver=driver)

    db.printing = True
    return db

def setup_database(dbname, driver=None, pooling=False):
    if dbname == 'sqlite':
        db = web.database(dbn=dbname, db='webpy.db', pooling=pooling, driver=driver)
    elif dbname == 'postgres':
        user = os.getenv('USER')
        db = web.database(dbn=dbname, db='webpy', user=user, pw='', pooling=pooling, driver=driver)
    else:
        db = web.database(dbn=dbname, db='webpy', user='scott', pw='tiger', pooling=pooling, driver=driver)

    db.printing = True
    return db

def connect(test_db=False):
    global db
    if public.isTest():
        db = web.database(port=5432, host='127.0.0.1', dbn='postgres', db='parking', user='parking', pw='parking')
    else:
        db = web.database(port=5432, host='10.1.1.100', dbn='postgres', db='parking', user='parking', pw='parking)()#')

    try:
        db.set_client_encoding('UTF8')
    except (AttributeError):
        db.query("set client_encoding to 'UTF-8'")

def crawler():
	proj=web.database(host='222.201.131.208',port=3306,dbn='mysql',db='proj',user='myadmin',pw='scutensave')
	daplatform=web.database(dbn='mysql',db='daplatform',user='root',pw='123')
	starttime=datetime.datetime.now()
	print 'start crawler...'
	total=0
	while (total<=10000):
		results=proj.select("text", order='id DESC', limit=100, offset=total)
		for record in results:
			daplatform.insert('originText',id=record.id,title=record.title,type=record.type,date=record.date,accuser=record.accuser,defendant=record.defendant,publicProsecutionOrgan=record.publicProsecutionOrgan,authorizedAgent=record.authorizedAgent,issue=record.issue,crime=record.crime,law=record.law,judgement=record.judgement,content=record.content,place=record.place)
		total=total+100
	endtime=datetime.datetime.now()
	print 'cost time ',(endtime-starttime).seconds,' s'

def connect(url):
    r = urlparse.urlparse(url)
    if r.scheme == 'sqlite':
        if (r.path != ''):
            db = r.path
        else:
            db = r.netloc
        return web.database(dbn='sqlite', db=db)
    if r.scheme == 'mysql':
        return web.database(dbn='mysql',
                host = r.hostname,
                user = r.username,
                pw = r.password,
                db = r.path[1:])

def run(db_name, user, passwd, tables):
    db = web.database(dbn='mysql', db=db_name, user=user, passwd=passwd, charset=None)
    db_utf8 = web.database(dbn='mysql', db=db_name, user=user, passwd=passwd)
    db.printing = db_utf8.printing = False

    for table in tables:
        print '#' * 20 + table + '#' * 20
        db.query('alter table %s convert to character set utf8' % table)

        for i, m in enumerate(db.select(table)):
            if i % 100 == 0: print i
        
            for k, v in m.items():
                db_utf8.update(table, where='id=%s' % m.id, **m)

	def PUT(self, setting):
		if is_admin():
			try:
				if setting != "metrics":
					self[setting] = web.input()["value"]
					if setting in ("dbtype", "dbhost", "dbname", "dbuser", "dbpass", "dbport"):
						try:
							db = web.database(dbn=settings["dbtype"], host=settings["dbhost"], db=settings["dbname"], user=settings["dbuser"], password=settings["dbpass"], port=int(settings["dbport"]))
						except ValueError:
							db = web.database(dbn=settings["dbtype"], host=settings["dbhost"], db=settings["dbname"], user=settings["dbuser"], password=settings["dbpass"])
							
					return json.dumps({"status": "OK", "message": "Setting Updated"})
			except KeyError:
				pass
		return web.Forbidden()

    def __init__(self):

        dbfile = os.path.realpath(os.curdir) + '/test.db'
        self.db = web.database(dbn="sqlite", db=dbfile)
        self.db.supports_multiple_insert = True
        
        if not os.path.exists(dbfile):
            """
            category 分类
            parent   第一次发表为空，如果是回复，则这里是原消息的id字段
            user     用户名
            email    用户email
            title    标题
            time     添加时间戳
            message  内容
            id       哈希， md5(category + user + title), 限制: 同一个用户再同一分类下不能发表目录名一致的文章
            """
            self.db.query("create table topic ( category text, parent text, user text, email text, title text, time real, message text, id text )")

            #留言板
            self.addLeavemsg("zhanghua", "[email protected]", "你好，很高兴认识你丫!")
            self.addLeavemsg("陈怡", "[email protected]", "你好，认识你也很高兴", parent="d4eafca37aa5908a497dfd527a72bb2a", message="d4eafca37aa5908a497dfd527a72bb2a")
            self.addLeavemsg("zhanghua", "[email protected]", "QQ是多少啊？可以加个好友吗？", parent="d4eafca37aa5908a497dfd527a72bb2a", message="db382e936792b162f0bc429f312456b9")
            self.addLeavemsg("yanyan", "[email protected]", "好久不见，最近怎么样啊!")

            #课程
            self.addCase("陈怡", "[email protected]", "语文", message="2012-02-07~2012-02-08 语文语文")
            self.addCase("陈怡", "[email protected]", "数学", message="2012-02-07~2012-02-08 数学数学")
            self.addCase("陈怡", "[email protected]", "英语", message="2012-02-07~2012-02-08 英语英语")
            self.addCase("陈怡", "[email protected]", "自习", message="2012-02-07~2012-02-08 自习自习")
            self.addCase("陈怡", "[email protected]", "地理", message="2012-02-07~2012-02-08 地理地理")

            #相册
            self.addAlbum("陈怡", "[email protected]", "我的风采")
            self.addAlbum("陈怡", "[email protected]", "校园风光")

def connect_db():
    global params
    global connection
    if connection is None:
        connection = web.database(**params)
        connection.printing = False  # False by default
    return connection

    def mysqlConnect(self):
        try:
            self.dbconnet = web.database(dbn=self.dbn, user=self.user, pw=self.pwd, db=self.dbname)
            return self.dbconnet

        except Exception as e:
            print e

def update_005():
    import web
    from infogami.infobase._dbstore.store import Store
    
    db = web.database(dbn="postgres", db="openlibrary", user=os.getenv("USER"), pw="")    
    store = Store(db)
    
    for row in db.query("SELECT thing.key, thing.created, account.* FROM thing, account WHERE thing.id=account.thing_id"):
        username = row.key.split("/")[-1]
        account_key = "account/" + username
        
        if store.get(account_key):
            continue
        else:
            account = {
                "_key": account_key,
                "type": "account",
                "email": row.email,
                "enc_password": row.password,
                
                "username": username,
                "lusername": username.lower(),
                
                "bot": row.bot,
                "status": row.verified and "verified" or "pending",
                "created_on": row.created.isoformat(),
            }
            email_doc = {
                "_key": "account-email/" + row.email,
                "type": "account-email",
                "username": username
            }
            store.put_many([account, email_doc])

    def POST(self):
	i = web.input()
	db = web.database(dbn="postgres", user='webpy', pw='webpyz', db="webpydata")
	n = db.insert('test', id=i.id, name=i.name)
	rows = db.select('test')
	raise web.seeother('/dbrows')
	return render.dbrows(rows)

def main():
    m = olmemcache.Client(["ia331532:7060", "ia331533:7060"])
    db = web.database(dbn="postgres", db="openlibrary", user="anand", pw="", host="ia331526")

    t = db.transaction()
    try:
        db.query("DECLARE datacur CURSOR FOR SELECT thing.key, data.data FROM thing, data WHERE thing.id=data.thing_id and data.revision=thing.latest_revision ORDER BY thing.id")
        limit = 10000
        i = 0
        while True:
            i += 1
            result = db.query('FETCH FORWARD $limit FROM datacur', vars=locals()).list()
            if not result:
                break
            t1 = time.time()
            d = dict((r.key, r.data) for r in result)
            try:
                m.set_multi(d)
                #m.add_multi(d)
            except:
                m.delete_multi(d.keys())
                print >> web.debug, 'failed to add to memcached', repr(r.key)

            t2 = time.time()
            print >> web.debug, "%.3f" % (t2-t1), i, "adding memcache records"
    finally:
        t.rollback()

def check_and_fix_db_access_MySQL(params):
    logger.debug("Checking database access...")
    db_name = params.pop('db')
    error_code = 0
    connection = web.database(**params)
    try:
        connection.query("USE {0}".format(db_name))
        logger.debug("\tDatabase access confirmed")
    except OperationalError as e:
        error_code = e[0]
        if e[0] == 1049:
            logger.debug("\tDatabase {0} not found. Creating.".format(db_name))
            try:
                connection.query("CREATE DATABASE IF NOT EXISTS {0};".format(db_name))
                logger.info("\tDatabase access restored.")
                error_code = 0
            except:
                logger.critical("\tError creating database: ")
                logger.critical("\t\tError {0}: {1}".format(e[0], e[1]))
        elif e[0] == 1045:  # Access Denied for '%s'@'%s' (using password: (YES|NO))
            logger.critical("\tUnable to access database: invalid username or password")
            logger.critical("\t  Check your config file or environment variables.")
        else:
            logger.critical("\tUnable to access database: ")
            logger.critical("\t\t{0}: {1}".format(e[0], e[1]))
    return error_code

def longquery(query, vars, chunk_size=10000):
    """Execute an expensive query using db cursors.

    USAGE:

        for chunk in longquery("SELECT * FROM bigtable"):
            for row in chunk:
                print row
    """
    # DB cursor is valid only in the transaction
    # Create a new database to avoid this transaction interfere with the application code
    db = web.database(**db_parameters)
    db.printing = False

    tx = db.transaction()
    try:
        db.query("DECLARE longquery NO SCROLL CURSOR FOR " + query, vars=vars)
        while True:
            chunk = db.query("FETCH FORWARD $chunk_size FROM longquery", vars=locals()).list()
            if chunk:
                yield chunk
            else:
                break
    finally:
        tx.rollback()