Python executeSelect示例

示例#1

import sys
import traceback
import logging
import python_db

mysql_username = '******'  # please change to your username
mysql_password = '******'  # please change to your MySQL password

try:
    python_db.open_database('localhost', mysql_username, mysql_password,
                            mysql_username)  # open database
    # res =python_db.executeSelect('SELECT NAME FROM DEVELOPER;')
    # res=res.split('\n')# split the header and data for printing
    # print("<br/>"+ "Developer List:"+"<br/>" + res[0]+ "<br/>"+res[1]+ "<br/>")
    # for i in range(len(res)-2):
    #     print(res[i+2]+"<br/>")
    # insert into item tables by getting the values passed from PHP
    NAME = sys.argv[1]
    val = "'" + NAME + "'"
    res = python_db.executeSelect(
        'SELECT g.NAME, g.RELEASE_DATE, g.TOTAL_SALES, g.PLATFORM, p.MANUFACTURER, g.DEVELOPER, d.Games, d.Location FROM GAME g, DEVELOPER d, PLATFORM p WHERE TOTAL_SALES =(select MAX(TOTAL_SALES) FROM GAME where DEVELOPER = '
        + val + ') and g.DEVELOPER = d.NAME AND g.PLATFORM = p.NAME;')
    res = res.split('\n')  # split the header and data for printing
    print("<br/>" + "<br/>")
    print("<br/>" + "" + "<br/>" + res[0] + "<br/>" + res[1] + "<br/>")
    for i in range(len(res) - 2):
        print(res[i + 2] + "<br/>")
    python_db.close()  # close db
except Exception as e:
    logging.error(traceback.format_exc())

示例#2

import sys
import traceback
import logging
import python_db 

mysql_username = '******'
mysql_password = '******'

try:
    python_db.open_database('localhost', mysql_username, mysql_password, mysql_username)
    teamName = sys.argv[1]
    query = """SELECT t1.TeamName AS 'Team Name', t1.Nickname, r.GameID, r.ScoreOne AS 'Team Score', 
            r.ScoreTwo AS 'Opponents Score', t2.TeamName AS 'Opponets Name', g.date, r.Winner FROM RESULT r 
            LEFT JOIN TEAM t1 ON r.TeamOneID = t1.TeamID LEFT JOIN TEAM t2 ON r.TeamTwoID = t2.TeamID 
            LEFT JOIN GAME g on r.GameID = g.GameID WHERE t1.TeamName = '""" + teamName + """' UNION 
            SELECT t2.TeamName AS 'Team Name', t2.Nickname, r.GameID, r.ScoreOne AS 'Team Score', 
            r.ScoreTwo AS 'Opponents Score', t1.TeamName AS 'Opponets Name', g.date, r.Winner FROM RESULT r 
            LEFT JOIN TEAM t1 ON r.TeamOneID = t1.TeamID LEFT JOIN TEAM t2 ON r.TeamTwoID = t2.TeamID 
            LEFT JOIN GAME g on r.GameID = g.GameID WHERE t2.TeamName = '""" + teamName + "';" 
    results = python_db.executeSelect(query)
    results = results.split('\n')
    print("<br/>" + "Games that team played in" + results[0] + "<br/>" + results[1])
    for i in range(len(results)):
        print(results[i])
    python_db.close_db()
except Exception as e:
    logging.error(traceback.format_exc())

示例#3

import sys
import traceback
import logging
import python_db


mysql_username = '******' # please change to your username
mysql_password= '******'  # please change to your MySQL password

try:
    python_db.open_database('localhost',mysql_username,mysql_password,mysql_username) # open database
    res =python_db.executeSelect('SELECT * FROM PLATFORM;')
    res=res.split('\n')# split the header and data for printing
    print("<br/>"+ "PLATFORM Table"+"<br/>" + res[0]+ "<br/>"+res[1]+ "<br/>")
    for i in range(len(res)-2):
        print(res[i+2]+"<br/>")
    python_db.close() # close db    
except Exception as e:
        logging.error(traceback.format_exc())

示例#4

import sys
import traceback
import logging
import python_db

mysql_username = '******'  # please change to your username
mysql_password = '******'  # please change to your MySQL password

try:
    python_db.open_database('localhost', mysql_username, mysql_password,
                            mysql_username)  # open database
    res = python_db.executeSelect('SELECT * FROM DEVELOPER;')
    res = res.split('\n')  # split the header and data for printing
    print("<br/>" + "Table DEVELOPER before:" + "<br/>" + res[0] + "<br/>" +
          res[1] + "<br/>")
    for i in range(len(res) - 2):
        print(res[i + 2] + "<br/>")
    # insert into item tables by getting the values passed from PHP
    NAME = sys.argv[1]
    CREATION = sys.argv[2]
    GAMES = sys.argv[3]
    Location = sys.argv[4]
    val = "'" + NAME + "'" + ", STR_TO_DATE('" + CREATION + "','" + "%m/%d/%Y'),'" + GAMES + "','" + Location + "'"
    python_db.insert("DEVELOPER", val)
    res = python_db.executeSelect('SELECT * FROM DEVELOPER;')
    res = res.split('\n')  # split the header and data for printing
    print("<br/>" + "<br/>")
    print("<br/>" + "Table DEVELOPER after:" + "<br/>" + res[0] + "<br/>" +
          res[1] + "<br/>")
    for i in range(len(res) - 2):
        print(res[i + 2] + "<br/>")

示例#5

import sys
import traceback
import logging
import python_db

mysql_username = '******'  # please change to your username
mysql_password = '******'  # please change to your MySQL password

try:
    python_db.open_database('localhost', mysql_username, mysql_password,
                            mysql_username)  # open database
    result = python_db.executeSelect("SELECT * FROM RESULT;")
    result = result.split('\n')
    print("<br/>" + "Table RESULT before: " + result[0] + "<br/>" + result[1])
    for i in range(len(result)):
        print(result[i])
    gameID = sys.argv[1]
    teamOne = sys.argv[2]
    teamTwo = sys.argv[3]
    teamOneScore = sys.argv[4]
    teamTwoScore = sys.argv[5]
    winner = sys.argv[6]
    val = gameID + ", " + teamOne + ", " + teamTwo + ", " + teamOneScore + ", " + teamTwoScore + ", '" + winner + "'"
    python_db.insert("RESULT", val)
    result = python_db.executeSelect("SELECT * FROM RESULT;")
    result = result.split('\n')
    print("<br/>" + "Table RESULT After: " + result[0] + "<br/>" + result[1])
    for i in range(len(result)):
        print(result[i])
    python_db.close_db()

示例#6

文件： add_team.py 项目： Zachary-Chapman/CSCE4523_Project

import sys
import traceback
import logging
import python_db

mysql_username = '******'  # please change to your username
mysql_password = '******'  # please change to your MySQL password

try:
    python_db.open_database('localhost', mysql_username, mysql_password,
                            mysql_username)  # open database
    res = python_db.executeSelect('SELECT * FROM TEAM;')
    res = res.split('\n')  # split the header and data for printing
    print("<br/>" + "Table TEAM before:" + res[0] + "<br/>" + res[1])
    for i in range(len(res) - 2):
        print(res[i + 2])
    teamname = sys.argv[1]
    nickname = sys.argv[2]
    rank = sys.argv[3]
    val = "NULL" + ",'" + teamname + "','" + nickname + "','" + rank + "'"
    python_db.insert("TEAM", val)
    res = python_db.executeSelect('SELECT * FROM TEAM;')
    res = res.split('\n')  # split the header and data for printing
    print("<br/>" + "<br/>")
    print("<br/>" + "Table TEAM after:" + res[0] + "<br/>" + res[1])
    for i in range(len(res) - 2):
        print(res[i + 2])
    python_db.close_db()  # close db
except Exception as e:
    logging.error(traceback.format_exc())

示例#7

文件： Insert_Game.py 项目： moyeneka/DBMS-project

import sys
import traceback
import logging
import python_db

mysql_username = '******'  # please change to your username
mysql_password = '******'  # please change to your MySQL password

try:
    python_db.open_database('localhost', mysql_username, mysql_password,
                            mysql_username)  # open database
    res = python_db.executeSelect('SELECT * FROM GAME;')
    res = res.split('\n')  # split the header and data for printing
    print("<br/>" + "Table GAME before:" + "<br/>" + res[0] + "<br/>" +
          res[1] + "<br/>")
    for i in range(len(res) - 2):
        print(res[i + 2] + "<br/>")
    # insert into item tables by getting the values passed from PHP
    ID = sys.argv[1]
    NAME = sys.argv[2]
    RELEASE_DATE = sys.argv[3]
    PLATFORM = sys.argv[4]
    DEVELOPER = sys.argv[5]
    GENRE = sys.argv[6]
    TOTAL_SALES = sys.argv[7]
    val = "'" + ID + "','" + NAME + "'" + ", STR_TO_DATE('" + RELEASE_DATE + "','" + "%m/%d/%Y'),'" + PLATFORM + "','" + DEVELOPER + "','" + GENRE + "','" + TOTAL_SALES + "'"
    python_db.insert("GAME", val)
    res = python_db.executeSelect('SELECT * FROM GAME;')
    res = res.split('\n')  # split the header and data for printing
    print("<br/>" + "<br/>")
    print("<br/>" + "Table GAME after:" + "<br/>" + res[0] + "<br/>" + res[1] +

示例#8

文件： select_developer.py 项目： moyeneka/DBMS-project

import sys
import traceback
import logging
import python_db

mysql_username = '******'  # please change to your username
mysql_password = '******'  # please change to your MySQL password

try:
    python_db.open_database('localhost', mysql_username, mysql_password,
                            mysql_username)  # open database
    # res =python_db.executeSelect('SELECT NAME FROM DEVELOPER;')
    # res=res.split('\n')# split the header and data for printing
    # print("<br/>"+ "Developer List:"+"<br/>" + res[0]+ "<br/>"+res[1]+ "<br/>")
    # for i in range(len(res)-2):
    #     print(res[i+2]+"<br/>")
    # insert into item tables by getting the values passed from PHP
    NAME = sys.argv[1]
    val = "'" + NAME + "'"
    res = python_db.executeSelect(
        'select d.NAME, Location, g.NAME, RELEASE_DATE, GENRE from DEVELOPER d, GAME g where DEVELOPER = d.NAME and d.NAME = '
        + val + ';')
    res = res.split('\n')  # split the header and data for printing
    print("<br/>" + "<br/>")
    print("<br/>" + "" + "<br/>" + res[0] + "<br/>" + res[1] + "<br/>")
    for i in range(len(res) - 2):
        print(res[i + 2] + "<br/>")
    python_db.close()  # close db
except Exception as e:
    logging.error(traceback.format_exc())