应用程序读取库里面的表列表［原创］

近日需要对syslog表进行按日期分表处理。我的想法是每隔10天分一次表（大概数据量在500--800万）加上合理的索引应该能够快速查询。先写一个分表的小脚本。

#!/usr/bin/python2.4
#coding:utf-8
import time
import MySQLdb
"""max(len(line) for line in open('file.txt')) """
def _getcurrtablename():
    """get current table name releate to system time."""
    leap = lambda year : (year % 400 == 0) or (year%4==0 and year%100!=0)
    year = time.strftime("%Y")
    month = time.strftime("%m")
    date = time.strftime("%d")
    tablename = ""
    if month == "02":
        if leap(year) and date == "29":
            tablename = "syslog_"+year+"_"+month+"_3"
        elif not leap(year) and date == "28":
            tablename = "syslog_"+year+"_"+month+"_3"
        elif date == "01":
            tablename = "syslog_"+year+"_"+month+"_1"
        elif date == "10":
            tablename = "syslog_"+year+"_"+month+"_2"
        else:pass
    if month in ["01","03","05","07","08","10","12"]:
        if date == "31":
            tablename = "syslog_"+year+"_"+month+"_3"
        elif date == "01":
            tablename = "syslog_"+year+"_"+month+"_1"
        elif date == "10":
            tablename = "syslog_"+year+"_"+month+"_2"
    if month in ["04","06","09","11"]:
        if date == "30":
            tablename = "syslog_"+year+"_"+month+"_3"
        elif date == "01":
            tablename = "syslog_"+year+"_"+month+"_1"
        elif date == "10":
            tablename = "syslog_"+year+"_"+month+"_2"
    return tablename

"""
Connection to mysql
"""
conn= MySQLdb.connect("127.0.0.1","root","321","test",port=3306,connect_timeout=10,compress=True)
currtablename = _getcurrtablename()
if currtablename == "":pass
else:
    """rename table"""
    sql = "ALTER  TABLE syslog RENAME TO " + currtablename
    cursor = conn.cursor()
    cursor.execute(sql)
    sql = "create table `syslog` like " +currtablename
    cursor.execute(sql)
    cursor = None
    conn = None

现在数据库test里面有许多个表，在应用层查询其表列表。
PHP实现方法如下：

$tablesql = "show tables";
$tableresulttmp = $CI->db->query($tablesql);
$data['tablelist'] = $tableresulttmp->result();

  view.py
   表列表:
 
     foreach ($tablelist as $row):?>
   Tables_in_test?>
  
   

类似的ASP实现：

表:<select name="table">
<% while not jifei.eof %>
<option ><% response.write jifei("Tables_in_test") %></option>
<%jifei.movenext
Wend
%>

这样在应用层依据用户选择不同的表进行范围过滤。可以解决目前的问题～