Create tables.py

# from flask import Flask
# from flask_bootstrap import Bootstrap
import sqlite3


db_conn = sqlite3.connect('phase3.db')
print "database created!"
createCursor = db_conn.cursor()

try:
	db_conn.execute("CREATE TABLE TRANSACTION(TRANSACTION_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
					"TIMESTAMP DATE NOT NULL,"
					"IS_SUCCESS BOOLEAN NOT NULL,"
					"SALE_TYPE TEXT NOT NULL);")
	db_conn.execute("CREATE TABLE BUYS(USER_ID INTEGER PRIMARY KEY ,"
										"TRANSACTION_ID INTEGER PRIMARY KEY ,"
										"PRODUCT_ID INTEGER PRIMARY KEY);")
	db_conn.execute("CREATE TABLE USER(USER_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
					"USERNAME TEXT NOT NULL,"
					"ANNUAL_INCOME INTEGER NOT NULL,"
					"PASSWORD TEXT NOT NULL, "
					"EMAIL TEXT NOT NULL,"
					"NAME TEXT NOT NULL);")
	db_conn.execute("CREATE TABLE RATES(USER_ID INTEGER PRIMARY KEY, "
                    "DESCRIPTION TEXT NOT NULL,"
                    "RATING INTEGER NOT NULL);")
	db_conn.execute("CREATE TABLE ADDRESS_INFO(USER_ID INTEGER PRIMARY KEY NOT NULL,"
					"STREET TEXT PRIMARY KEY NOT NULL);")
	db_conn.execute("CREATE TABLE ADDRESS(STREET TEXT PRIMARY KEY NOT NULL,"
					"CITY TEXT NOT NULL,"
					"STATE TEXT NOT NULL);")
	db_conn.execute("CREATE TABLE CREDIT_CARD_INFO(USER_ID INTEGER PRIMARY KEY NOT NULL,"
					"CCNUMBER INTEGER PRIMARY KEY NOT NULL);")
	db_conn.execute("CREATE TABLE CREDIT_CARD(TYPE TEXT NOT NULL,"
					"CCNUMBER INTEGER PRIMARY KEY NOT NULL);")
	db_conn.execute("CREATE TABLE PHONE_INFO(USER_ID INTEGER PRIMARY KEY NOT NULL,"
					"PHONE_NUMBER INTEGER PRIMARY KEY NOT NULL);")
	db_conn.execute("CREATE TABLE PHONE(PHONE_NUMBER INTEGER PRIMARY KEY NOT NULL);")
	db_conn.execute("CREATE TABLE VIEWS(USER_ID INTEGER PRIMARY KEY NOT NULL,"
					"PRODUCT_ID INTEGER PRIMARY KEY,"
					"TIMESTAMP DATE);")
	db_conn.execute("CREATE TABLE USER_SELLS(USER_ID INTEGER PRIMARY KEY NOT NULL,"
					"PRODUCT_ID INTEGER PRIMARY KEY);")
	db_conn.execute("CREATE TABLE SALE_ITEM(PRODUCT_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
					"LIST_PRICE FLOAT NOT NULL,"
					"PRODUCT_NAME TEXT NOT NULL, "
					"PRODUCT_DESCRIPTION TEXT NOT NULL,"
					"URL TEXT NOT NULL);")
	db_conn.execute("CREATE TABLE HAS_A(PRODUCT_ID INTEGER PRIMARY KEY NOT NULL,"
					"CATEGORY_NAME TEXT PRIMARY KEY);")
	db_conn.execute("CREATE TABLE CATEGORY(CATEGORY_NAME TEXT PRIMARY KEY);")
	db_conn.execute("CREATE TABLE SELLER_ITEM(PRODUCT_ID INTEGER PRIMARY KEY NOT NULL,"
					"RESERVE_PRICE FLOAT NOT NULL,"
					"CURRENT_BID FLOAT NOT NULL, "
					"TIME_REMAINING DATE NOT NULL,"
					"PRODUCT_LOCATION TEXT NOT NULL);")
	db_conn.execute("CREATE TABLE SUPPLIER_ITEM(PRODUCT_ID INTEGER PRIMARY KEY NOT NULL,"
					"STOCK_LEVEL INTEGER NOT NULL);")
	db_conn.execute("CREATE TABLE SUPPLIER_SELLS(PRODUCT_ID INTEGER PRIMARY KEY NOT NULL,"
					"SUPPLIER_ID INTEGER PRIMARY KEY NOT NULL);")
	db_conn.execute("CREATE TABLE SUPPLIER(SUPPLIER_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
					"PHONE_NUMBER INTEGER NOT NULL,"
					"ADDRESS TEXT NOT NULL,"
					"POC TEXT NOT NULL,"
					"COMPANY_NAME TEXT NOT NULL,"
					"REVENUE FLOAT NOT NULL,"
					"SUPPLIER_CATEGORY TEXT NOT NULL,);")
	db_conn.commit()

except sqlite3.OperationalError:
    print "table creation error!"

print "Table has been created"
"""
RUN THESE IN ORDER YOU MUST ENTER THE USER,SALE_ITEM,TRANSACTION_ID AND SUPPLIER TO CREATE THE ID'S
"""
db_conn.execute("INSERT INTO TRANSACTION (TIMESTAMP, IS_SUCCESS, SALE_TYPE)"
" VALUES(sysdate, TRUE, 'BID'),"
		"(sysdate, TRUE, 'BUY NOW'),"
		"(sysdate, TRUE, 'BID'),"
		"(sysdate, TRUE, 'BID'),"
		"(sysdate, TRUE, 'BUY NOW');")
db_conn.execute("INSERT INTO USER (USERNAME, ANNUAL_INCOME, PASSWORD,EMAIL,NAME)"
" VALUES('ABC11', 40235, 'MONKEY123','ABC11@hotmail.com','Abe Cambrige'),"
		"('MHG13', 65231, 'CAT123','MHG13@hotmail.com','Mike Griffith'),"
		"('TYR14', 91201, 'FISH123','TYR14@hotmail.com','Tyler Roudolf'),"
		"('SPW15', 56210, 'DOG123','SPW15@hotmail.com','Steve Witherspoon'),"
		"('NWB12', 120365, 'RAT123','NWR12@hotmail.com','Nate Bedford');")
db_conn.execute("INSERT INTO SALE_ITEM (LIST_PRICE, PRODUCT_NAME, PRODUCT_DESCRIPTION, URL)"
" VALUES(65.14, 'WHIRLPOOL BLENDER', '10 INCH BLENDER STAINLESS STEEL BLADES','www.himalaya.com/AOIHSDF8ADUHFASD'),"
"(15.32, 'QUICKSILVER T-SHIRT', 'RED COTTON T-SHIRT','www.himalaya.com/OAIQWHEOF7W0E9'),"
"(122, 'JORDAN SHOES', 'BLUE JORDAN SHOES','www.himalaya.com/2A1WRFWRSG31A5'),"
"(40.21, 'RGH-12 MOUSE', 'TOSHIBA MOUSE','www.himalaya.com/QAHWEF51SE'),"
"(5.13, 'NIKE SOCKS', 'BLACK LARGE SOCKS','www.himalaya.com/PINWEF155AWE');")
db_conn.execute("INSERT INTO SUPPLIER (PHONE_NUMBER, ADDRESS, POC, COMPANY_NAME,REVENUE,SUPPLIER_CATEGORY)"
" VALUES(2651452698, '5406 BOULDER DRIVE', 'XXXX','BIBLER CO.',6654654.2,'ELECTRONICS'),"
"(1526459584, '5891 MAIN DRIVE', 'YYYY','WIBLER INC.',1256954.5,'CLOTHING'),"
"(5412859451, '6842 OAK ROAD', 'ZZZZ','SHNIDEL INC.',2594875.3,'JEWELRY'),"
"(4869542631, '11 FRANK AVE', 'JJJJ','JEMBLE CO.',4584951.2,'AUTOMOTIVE'),"
"(3629514726, '36 BEAVER AVE', 'PPPP','STIENBERG INC.',654128.8,'HARWARE TOOLS');")
db_conn.execute("INSERT INTO BUYS (USER_ID,TRANSACTION_ID,PRODUCT_ID)"
" VALUES(1,5,4),"
"(1,5,2),"
"(1,4,1),"
"(1,3,3),"
"(1,2,5);")
db_conn.execute("INSERT INTO RATES (RATING,DESCRIPTION,USER_ID)"
" VALUES(4,'VERY GOOD',4),"
"(2,'LACKLUSTER',1),"
"(5,'AMAZING',2),"
"(1,'TERRIBLE',5),"
"(3,'THE WORST',3);")
db_conn.execute("INSERT INTO ADDRESS_INFO (USER_ID,STREET)"
" VALUES(4,'1 BOULDER DRIVE'),"
"(2,'6 MAIN DRIVE'),"
"(5,'62 OAK DRIVE'),"
"(1,'75 HACIENDA DRIVE'),"
"(3,'88 CACTUS DRIVE');")
db_conn.execute("INSERT INTO ADDRESS (CITY,STREET,STATE)"
" VALUES('PITTSBURGH','1 BOULDER DRIVE','PA'),"
"('PHILIDELPHIA','6 MAIN DRIVE','PA'),"
"('NEW YORK CITY','62 OAK DRIVE','NY'),"
"('LOS ANGELOS','75 HACIENDA DRIVE','CA'),"
"('AUSTIN','88 CACTUS DRIVE','TX');")
db_conn.execute("INSERT INTO CREDIT_CARD_INFO (USER_ID,CCNUMBER)"
" VALUES(4,4444555566668888),"
"(2,1111222255558888),"
"(5,2222555566669999),"
"(1,6666555588889999),"
"(3,2222999944446666);")
db_conn.execute("INSERT INTO CREDIT_CARD (TYPE,CCNUMBER)"
" VALUES('VISA',4444555566668888),"
"('MASTER',1111222255558888),"
"('VISA',2222555566669999),"
"('MASTER',6666555588889999),"
"('DISCOVER',2222999944446666);")
db_conn.execute("INSERT INTO PHONE_INFO (USER_ID,PHONE_NUMBER)"
" VALUES(4,6543219874),"
"(2,3216549874),"
"(5,9638527410),"
"(1,7419638520),"
"(3,9516238470);")
db_conn.execute("INSERT INTO PHONE_INFO (PHONE_NUMBER)"
" VALUES(6543219874),"
"(3216549874),"
"(9638527410),"
"(7419638520),"
"(9516238470);")
db_conn.execute("INSERT INTO VIEWS (USER_ID,PRODUCT_ID,TIMESTAMP)"
" VALUES(4,4,3:15PM),"
"(2,1,4:15PM),"
"(5,2,5:45PM),"
"(1,5,9:05PM),"
"(3,3,3:15PM);")
db_conn.execute("INSERT INTO USER_SELLS (USER_ID,PRODUCT_ID)"
" VALUES(4,4),"
"(2,1),"
"(5,2),"
"(1,5),"
"(3,3);")
db_conn.execute("INSERT INTO HAS_A (USER_ID,PRODUCT_ID)"
" VALUES(4,'ELECTRONICS'),"
"(2,'CLOTHING'),"
"(5,'JEWELRY'),"
"(1,'AUTOMOTIVE'),"
"(3,'HARWARE TOOLS');")
db_conn.execute("INSERT INTO CATEGORY (PHONE_NUMBER)"
" VALUES('ELECTRONICS'),"
"('CLOTHING),"
"('JEWELRY'),"
"('AUTOMOTIVE),"
"('HARWARE TOOLS');")
db_conn.execute("INSERT INTO SELLER_ITEM (PRODUCT_ID,RESERVE_PRICE,CURRENT_BID,TIME_REMAINING,PRODUCT_LOCATION)"
" VALUES(4,60.21,40.36,3:28PM, 'PITTSBURGH'),"
"(2,15.32,7.69,3:28PM, 'NEW YORK CITY'),"
"(5,122,80,3:28PM, 'NEW YORK CITY'),"
"(1,40.21,32,3:28PM, 'ST.LOUIS'),"
"(3,5.13,1.23,3:28PM, 'PITTSBURGH');")
db_conn.execute("INSERT INTO SUPPLIER_ITEM (PRODUCT_ID,STOCK_LEVEL)"
" VALUES(4,65),"
"(2,14),"
"(5,12),"
"(1,40),"
"(3,5);")
db_conn.execute("INSERT INTO SUPPLIER_ITEM (PRODUCT_ID,SUPPLIER_ID)"
" VALUES(4,4),"
"(2,2),"
"(5,5),"
"(1,4),"
"(3,3);")
db_conn.commit()


"""
print "data base is created"
app = Flask(__name__)
Bootstrap(app)
@app.route('/')
def hello():
    return "Hello World!"
@app.route('/login')
def login():
    return "<h2> Welcome. Please enter your user name and" \
           " PW to proceed"
@app.route('/home')
def home_page():
    return "Welcome back! ___"  # include var for username in underline
@app.route('/browse')
def browse():
    return "this is where they will be able to search products"
if __name__ == "__main__":
    app.run(debug=True)
"""

db_conn.close()
print "database is closed"