dbtest.py

python web programming 部分

   where hours*rate>500 and (rate<100 or hours>5)""",
"""select name, rate, hours, hours*rate as pay
   from workt
   where hours*rate>500 and rate<100 or hours>5""",
"""select avg(rate), min(hours), max(hours), sum(hours*rate) as expenses
   from workt""",
"""select * from pagerefs""",
"""select month, sum(hits) as totalhits
   from pagerefs
   where month<>1
   group by month
   order by 2""",
"""select month, sum(hits) as totalhits
   from pagerefs
   group by month
   order by 2 desc""",
"""select month, sum(hits) as totalhits
   from pagerefs
   group by month
   having sum(hits)<3000
   order by 2 desc""",
"""select count(distinct month), count(distinct page)
   from pagerefs""",
"""select month, hits, page
   from  pagerefs
   order by month, hits desc""",
]
    
queries = [
"""select * from nondrinkers""",
"""select drinker as x from likes
   union select beer as x from serves
          union select drinker as x from frequents""",
"""select f.drinker, s.bar, l.beer
   from frequents f, serves s, likes l
   where f.drinker=l.drinker and s.beer=l.beer and s.bar=f.bar""",
"""select * from
   likes where beer in ('bud', 'pabst')""",
"""select l.beer, l.drinker, count(distinct s.bar)
   from likes l, serves s
   where l.beer=s.beer
   group by l.beer, l.drinker
   order by 3 desc""",
"""select l.beer, l.drinker, count(distinct s.bar) as nbars
   from likes l, serves s
   where l.beer=s.beer
   group by l.beer, l.drinker
    union
     select distinct beer, drinker, 0 as nbars
     from likes
     where beer not in (select beer from serves)
   order by 3 desc""",
"""select avg(perweek) from frequents""",
"""select * 
   from frequents
   where perweek <= (select avg(perweek) from frequents)""",
"""select * 
   from serves""",
"""select bar, avg(quantity)
   from serves
   group by bar""",
"""select * 
   from serves s1
   where quantity <= (select avg(quantity) 
                      from serves s2 
                      where s1.bar=s2.bar)""",
"""select * from frequents
   where perweek > (select avg(perweek) from frequents)""",
"""select * from frequents f1
   where perweek > (
   select avg(perweek) from frequents f2
   where f1.drinker = f2.drinker)""",
"""select * from frequents
   where perweek < any (select perweek from frequents)""",
"""select * from frequents
   where perweek >= all (select perweek from frequents)""",
"""select * from frequents
   where perweek <= all (select perweek from frequents)""",
"""select * from frequents f1
   where perweek < any 
   (select perweek from frequents f2
    where f1.drinker = f2.drinker)""",
"""select * from frequents f1
   where perweek = all 
   (select perweek from frequents f2
    where f1.drinker = f2.drinker)""",
"""select * from frequents f1
   where perweek <> all
   (select perweek from frequents f2
    where f1.drinker <> f2.drinker)""",
"""select beer
   from serves
   where beer = any (select beer from likes)""",
"""select beer
   from serves
   where beer <> all (select beer from likes)""",
"""select beer
   from serves
   where beer in (select beer from likes)""",
"""select beer
   from serves
   where beer not in (select beer from likes)""",
"""select *
   from frequents
   where perweek between 2 and 
        (select avg(perweek) from frequents)""",
"""select *
   from frequents
   where perweek not between 2 and 5""",
"""select f.drinker, l.beer, s.bar
   from frequents f, serves s, likes l
   where f.drinker=l.drinker and s.bar=f.bar and s.beer=l.beer""",
   #"stop!",
"""select * from serves""",
"""select * from likes""",
"""select * from frequents
   where drinker = 'norm'""",
"""select drinker from likes
   union
   select drinker from frequents""",
"""select drinker from likes
   union
   select distinct drinker from frequents""",
"""select * from frequents
   where drinker>'norm'""",
"""select * from frequents
   where drinker<='norm'""",
"""select * from frequents
   where drinker>'norm' or drinker<'b'""",
"""select * from frequents
   where drinker<>'norm' and 'pierre'<>drinker""",
"""select * from frequents
   where drinker<>'norm'""",
"""select *
   from frequents as f, serves as s
   where f.bar = s.bar""",
"""select *
   from frequents as f, serves as s
   where f.bar = s.bar and
     not exists(
       select l.drinker, l.beer
       from likes l
       where l.drinker=f.drinker and s.beer=l.beer)""",
"""select *
   from likes l, frequents f
   where f.bar='cheers' and l.drinker=f.drinker and l.beer='bud'""",
"""select *
   from serves s
   where not exists (
     select *
     from likes l, frequents f
     where f.bar = s.bar and f.drinker=l.drinker and s.beer=l.beer)""",
"""select 'nonbeer drinker '+f.drinker
   from frequents f
   where not exists
      (select l.drinker, l.beer from likes l where l.drinker=f.drinker)""",
"""select l.drinker+' likes '+l.beer+' but goes to no bar'
   from likes l
   where not exists (select f.drinker from frequents f where f.drinker=l.drinker)""",
"""select bar from frequents""",
"""select distinct bar from frequents""",
"""select sum(quantity), avg(quantity), count(*), sum(quantity)/count(quantity)
   from serves""",
"""select beer, sum(quantity), avg(quantity), count(*), sum(quantity)/count(quantity)
   from serves 
   group by beer""",
"""select sum(quantity), avg(quantity), count(*), sum(quantity)/count(quantity)
   from serves
   where beer<>'bud'
""",
"""select bar, sum(quantity), avg(quantity), count(*), sum(quantity)/count(quantity)
   from serves
   where beer<>'bud'
   group by bar
   having sum(quantity)>500 or count(*)>3
   order by 2 desc
""",
"""select beer, sum(quantity), avg(quantity), count(*)
   from serves
   where beer<>'bud'
   group by beer
   having sum(quantity)>100
   order by 4 desc, beer
""",
"""select l.drinker, l.beer, count(*), sum(l.perday*f.perweek)
   from likes l, frequents f
   where l.drinker=f.drinker
   group by l.drinker, l.beer
   order by 4 desc, l.drinker, l.beer
""",
"""select l.drinker, l.beer, f.bar, l.perday, f.perweek
   from likes l, frequents f
   where l.drinker=f.drinker
   order by l.drinker, l.perday desc, f.perweek desc
""",
]

dynamic_queries = [
( "select bar from frequents where drinker=?", ("norm",) ), 
( "select * from frequents where drinker=? or bar=?", ("norm", "cheers") )
]

updates = [
"""select * from frequents""",
"""select * from likes""",
"""select * from serves""",
"""select bar, sum(quantity), avg(quantity), count(*), sum(quantity)/count(quantity)
   from serves
   where beer<>'bud'
   group by bar
   having sum(quantity)>500 or count(*)>3
   order by 2 desc
""",

"""select count(*), d from nondrinkers group by d""",
"""insert into frequents (drinker, perweek, bar)
   values ('billybob', 4, 'cheers')""",
"""select * from nondrinkers""",
"""create table templikes (dr varchar(10), be varchar(12))""",
"""select * from templikes""",
"""insert into templikes(dr, be)
   select drinker, beer from likes""",
"""create index tdindex on templikes(dr)""",
"""create index tbindex on templikes(be)""",
"""select * from templikes""",
"""delete from templikes where be='rollingrock' """,
"""select * from templikes""",
"""update templikes set dr=dr+'an' where dr='norm' """,
#"""drop index tdindex""",
"""delete from templikes
   where dr=(select min(dr) from templikes)""",
"""insert into templikes (dr, be) 
   select max(dr), min(be) from templikes""",
"""select * from templikes""",
"""select * from frequents""",
"""update frequents 
   set perweek=(select max(perweek) 
                from frequents
                where drinker='norm')
   where drinker='woody'""",
"""select * from frequents""",
"""create view lazy as
   select drinker, sum(perweek) as wasted 
   from frequents
   group by drinker
   having sum(perweek)>4""",
"""select * from lazy""",
"""drop view lazy""",
"""drop table templikes""",
]


cleanup_queries = [
#"""drop index wname""",
"""drop view nondrinkers""",
"""drop table workt""",
"""drop table pagerefs""",
"""drop table empty""",
"""drop table frequents""",
"""drop table likes""",
"""drop table serves""",
]

table_names = [
'workt',
'pagerefs',
'empty',
'frequents',
'likes',
'serves',
]
if __name__=="__main__":
    import sys
    argv = sys.argv
    if len(argv)<2:
        sys.exit("""
USAGE: python %s <dbtype> <db_parameters>
  dbtype: gadfly | mx | odbc
""" % argv[0])
    else:
        dbtype = argv[1].lower()
        if dbtype == "gadfly":
            if len(argv) < 3:
                sys.exit("""
USAGE: python %s gadfly <directory>
""" % argv[0])
            directory = argv[2]
            def dbconnect():
                from gadfly import gadfly
                conn = gadfly()
                conn.startup("test", directory)
                return conn
            def reconnect():
                from gadfly import gadfly
                return gadfly("test", directory)
        elif dbtype == "mx":
            if len(argv) < 4:
                sys.exit("""
USAGE: python %s mx DSN user
""" % argv[0])
            connectstring = argv[2]
            user=argv[3]
            def dbconnect():
                import mx.ODBC.Windows as odbc
                return odbc.connect(connectstring, user=user)
            reconnect = dbconnect
        elif dbtype == "odbc":
            if len(argv) < 3:
                sys.exit("""
USAGE: python %s odbc DSN
""" % argv[0])
            connectstring = argv[2]
            def dbconnect():
                import odbc
                return odbc.odbc(connectstring)
            reconnect = dbconnect
        else:
            sys.exit("""
USAGE: python %s <dbtype> <db_parameters>
  dbtype: gadfly | mx | odbc
""" % argv[0])
#
# Now we should be in a position to test any of the three
#
    test()