项目成果：比赛得分数据库

关键词

数据库，PostgreSQL，SQL语句

项目背景

为体育比赛运动员成绩创建数据库，使用Python的DB-API编写SQL语句并对数据库进行操作。

获得技能

创建数据库，SQL增删改查，调用DB-API。

1. 就处理表格数据而言，和excel的用途是一样的，不同之处是SQL直接对数据库操作，所以SQL语句省去了excel中必要的复制粘贴，语义也比较直观。
2. 自动化方面，是通过python自带的DB-API模块对数据库进行操作的。excel则是通过宏。
3. SQL的主要优势就是在处理大规模数据时，比python、excel更快；但显然不能数据可视化。

（项目代码已通过所有测试用例）

#!/usr/bin/env python
#
# tournament.py -- implementation of a Swiss-system tournament
#

import psycopg2


def connect():
    """Connect to the PostgreSQL database.  Returns a database connection."""
    return psycopg2.connect("dbname=tournament")



def deleteMatches():
    """Remove all the match records from the database."""
    db = connect()
    c = db.cursor()
    c.execute("update records set matches = 0, wins = 0")
    db.commit()
    db.close()


def deletePlayers():
    """Remove all the player records from the database."""
    db = connect()
    c = db.cursor()
    c.execute("delete from records")
    db.commit()
    db.close()


def countPlayers():
    """Returns the number of players currently registered."""
    db = connect()
    c = db.cursor()
    c.execute("select count(*) from records")
    count = c.fetchall()[0][0]
    db.close()
    return count


def registerPlayer(name):
    """Adds a player to the tournament database.

    The database assigns a unique serial id number for the player.  (This
    should be handled by your SQL database schema, not in your Python code.)

    Args:
      name: the player's full name (need not be unique).
    """
    db = connect()
    c = db.cursor()
    c.execute("insert into records(player) values(%s)", (name, ))
    db.commit()
    db.close()


def playerStandings():
    """Returns a list of the players and their win records, sorted by wins.

    The first entry in the list should be the player in first place, or a player
    tied for first place if there is currently a tie.

    Returns:
      A list of tuples, each of which contains (id, name, wins, matches):
        id: the player's unique id (assigned by the database)
        name: the player's full name (as registered)
        wins: the number of matches the player has won
        matches: the number of matches the player has played
    """
    db = connect()
    c= db.cursor()
    c.execute("select serial, player, wins, matches from records order by wins")
    records = [(row[0], row[1], row[2], row[3])
              for row in c.fetchall()]
    db.close()
    return records


def reportMatch(winner, loser):
    """Records the outcome of a single match between two players.

    Args:
      winner:  the id number of the player who won
      loser:  the id number of the player who lost
    """
    db = connect()
    c = db.cursor()
    c.execute("update records set wins = wins + 1 "
            "where serial = %s", (winner,))
    c.execute("update records set matches = matches + 1 where serial = %s or serial = %s", (winner, loser,))
    db.commit()
    db.close()



def swissPairings():
    """Returns a list of pairs of players for the next round of a match.

    Assuming that there are an even number of players registered, each player
    appears exactly once in the pairings.  Each player is paired with another
    player with an equal or nearly-equal win record, that is, a player adjacent
    to him or her in the standings.

    Returns:
      A list of tuples, each of which contains (id1, name1, id2, name2)
        id1: the first player's unique id
        name1: the first player's name
        id2: the second player's unique id
        name2: the second player's name
    """
    db = connect()
    c = db.cursor()
    c.execute("select serial, player from records order by wins desc")
    # starting from the first row in the the sorted table,
    # adding the adjacent two into one tuple
    # and adding the tuple into the list in order.
    i = 0
    pair = ()
    pairs = []
    for row in c.fetchall():
        if i < 2:
            pair = pair + row
            i = i + 1
        if i == 2:
            pairs.append(pair)
            pair = ()
            i = 0
    db.close()
    return pairs