def insert_vendor_list(vendor_list):
sql = """INSERT INTO vendors(vendor_name) VALUES(%s)"""
conn = None
try:
params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.executemany(sql, vendor_list)
conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def get_parts():
conn = None
try:
params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute("SELECT part_id, part_name FROM parts ORDER BY part_name")
rows = cur.fetchall()
print("The number of parts: ", cur.rowcount)
for row in rows:
print(row)
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def test(part_id):
conn = None
sql = "SELECT * FROM parts WHERE part_id = %s"
try:
params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute(sql, (part_id, ))
for i in cur.fetchall():
print(i)
conn.commit()
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def delete_part(part_id):
conn = None
delete_sql = "DELETE FROM parts WHERE part_id = %s;"
try:
params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute(delete_sql, (part_id, ))
rows_deleted = cur.rowcount
conn.commit()
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
return rows_deleted
def insert_teams():
conn = None
try:
params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.executemany("""INSERT INTO teams(team_name, abbreviation) VALUES (%(team)s, %(abv)s)""",
TEAM_ABV_DICTIONARY)
conn.commit()
cur.close()
print('Finished inserting data')
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def add_part(part_name, vendor_list):
insert_part = "INSERT INTO parts(part_name) VALUES(%s) RETURNING part_id;"
assign_vendor = "INSERT INTO vendor_parts(vendor_id,part_id) VALUES(%s,%s)"
conn = None
try:
params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute(insert_part, (part_name, ))
part_id = cur.fetchone()[0]
for vendor_id in vendor_list:
cur.execute(assign_vendor, (vendor_id, part_id))
conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def insert_players():
conn = None
player_list = players.get_active_players()
try:
params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.executemany("""INSERT INTO players VALUES
(%(id)s, %(full_name)s, %(first_name)s, %(last_name)s, %(is_active)s)""",
player_list)
conn.commit()
cur.close()
print('Finished inserting data')
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def update_vendor(vendor_id, vendor_name):
params = config()
conn = None
try:
sql_update = """UPDATE vendors
SET vendor_name = %s
WHERE vendor_id = %s"""
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute(sql_update, (vendor_name, vendor_id))
updated_rows = cur.rowcount
conn.commit()
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
return updated_rows
def get_vendors():
conn = None
try:
params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute(
"SELECT vendor_id, vendor_name FROM vendors ORDER BY vendor_name")
print("The number of parts: ", cur.rowcount)
row = cur.fetchone()
while row is not None:
print(row)
row = cur.fetchone()
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def get_part_vendors():
conn = None
try:
params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute("""
SELECT part_name, vendor_name
FROM parts
INNER JOIN vendor_parts ON vendor_parts.part_id = parts.part_id
INNER JOIN vendors ON vendors.vendor_id = vendor_parts.vendor_id
ORDER BY part_name;
""")
for row in iter_row(cur, 10):
print(row)
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def insert_vendor(vendor_name):
sql = """INSERT INTO vendors(vendor_name)
VALUES(%s) RETURNING vendor_id;"""
conn = None
vendor_id = None
try:
params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute(sql, (vendor_name, ))
s = cur.fetchone()
vendor_id = s[0]
print(s)
conn.commit()
cur.close()
except (Exception, psyopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
return vendor_id
def create_tables():
conn = None
try:
params = config()
conn = psycopg2.connect(**params)
commands = ("""CREATE TABLE [IF NOT EXISTS] vendors(
vendor_id SERIAL PRIMARY KEY,
vendor_name VARCHAR(255) NOT NULL
)""", """CREATE TABLE [IF NOT EXISTS] parts(
part_id SERIAL PRIMARY KEY,
part_name VARCHAR(255) NOT NULL
)""", """CREATE TABLE [IF NOT EXISTS] part_drawings(
part_id INTEGER PRIMARY KEY,
file_extension VARCHAR(5) NOT NULL,
drawing_data BYTEA NOT NULL,
FOREIGN KEY (part id),
REFERENCES parts(part_id)
ON UPDATE CASCADE ON DELETE CASCADE
)""", """CREATE TABLE [IF NOT EXISTS] vendor_parts(
vendor_id INTEGER NOT NULL,
part_id INTEGER NOT NULL,
PRIMARY KEY (vendor_id, part_id)
FOREIGN KEY (vendor_id)
REFERENCES vendors (vendor_id)
ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (part_id)
REFERENCES parts (part_id)
ON UPDATE CASCADE ON DELETE CASCADE
)""")
cur = conn.cursor()
for command in commands:
cur.execute(command)
cur.close()
conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def create_tables():
queries = (
"""
CREATE TABLE if not exists teams (
team_id SERIAL PRIMARY KEY,
team_name VARCHAR(255) NOT NULL,
abbreviation VARCHAR(3) NOT NULL
);
""",
"""
CREATE TABLE if not exists players (
player_id INTEGER PRIMARY KEY,
full_name VARCHAR(255) NOT NULL,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
is_active boolean NOT NULL
);
""",
"""
CREATE TABLE if not exists team_stats_per_game (
team_id SERIAL PRIMARY KEY,
G SMALLINT NOT NULL,
MP DECIMAL(5,1) NOT NULL,
FG NUMERIC(3, 1) NOT NULL,
FGA NUMERIC(3, 1) NOT NULL,
FG_PCT FLOAT(3) NOT NULL,
THREE_PTS NUMERIC(3, 1) NOT NULL,
THREE_PTS_ATT NUMERIC(3, 1) NOT NULL,
THREE_POINTS_PCT FLOAT(3) NOT NULL,
TWO_POINTS NUMERIC(3,1) NOT NULL,
TWO_POINT_ATT NUMERIC(4,2) NOT NULL,
TWO_POINT_PCT FLOAT(3) NOT NULL,
FT NUMERIC(3,1) NOT NULL,
FTA NUMERIC(3,1) NOT NULL,
FT_PCT FLOAT(3) NOT NULL,
ORB NUMERIC(3,1) NOT NULL,
DRB NUMERIC(3,1) NOT NULL,
TRB NUMERIC(3,1) NOT NULL,
AST NUMERIC(3,1) NOT NULL,
STL NUMERIC(3,1) NOT NULL,
BLK NUMERIC (3,1) NOT NULL,
TOV NUMERIC (3,1) NOT NULL,
PF NUMERIC(3,1) NOT NULL,
PTS NUMERIC (5,2) NOT NULL,
TEAM VARCHAR(3) NOT NULL,
SEASON VARCHAR(7) NOT NULL
);
"""
)
conn = None
try:
params = config()
conn = psycopg2.connect(**params)
print('Connected to database')
cur = conn.cursor()
for query in queries:
cur.execute(query)
cur.close()
conn.commit()
print('Queries completed')
except(Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
if __name__ == '__main__':
create_tables()