def get_vendors():
""" query data from the vendors table """
conn = None
sql = """ SELECT vendor_id, vendor_name FROM vendors ORDER BY vendor_name """
try:
params = get_dbconfig()
conn = connect_db(**params)
cur = conn.cursor()
cur.execute(sql)
print ("The number of vendors: ", 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 delete_vendor(vendor_id):
""" delete data from the vendors table
@:return : updated row
"""
conn = None
rows_deleted = 0
sql = """ DELETE FROM vendors WHERE vendor_id=%s """
try:
params = get_dbconfig()
conn = connect_db(**params)
cur = conn.cursor()
cur.execute(sql, (vendor_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 update_vendor(vendor_id, vendor_name):
""" update vendor name based on the vendor id
@return: updated row count
"""
sql = """ UPDATE vendors
SET vendor_name = %s
WHERE vendor_id = %s """
conn = None
updated_rows = 0
try:
params = get_dbconfig()
conn = connect_db(**params)
cur = conn.cursor()
cur.execute(sql, (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 create_tables():
""" create tables in ptdb database"""
commands = ("""
CREATE TABLE vendors (
vendor_id SERIAL PRIMARY KEY,
vendor_name VARCHAR(255) NOT NULL
)
""", """ CREATE TABLE parts (
part_id SERIAL PRIMARY KEY,
part_name VARCHAR(255) NOT NULL
)
""", """
CREATE TABLE 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 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
)
""")
conn = None
try:
params = get_dbconfig()
conn = connect_db(**params)
cur = conn.cursor()
for command in commands:
cur.execute(command)
conn.commit()
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def insert_vendor_list(vendor_list):
""" insert multiple vendors into the vendors table """
sql = "INSERT INTO vendors(vendor_name) VALUES(%s)"
conn = None
try:
params = get_dbconfig()
conn = connect_db(**params)
cur = conn.cursor()
cur.executemany(sql, vendor_list)
conn.commit()
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def insert_vendor(vendor_name):
""" insert a new vendor into the vendors table """
sql = """INSERT INTO vendors(vendor_name)
VALUES(%s) RETURNING vendor_id;"""
conn = None
vendor_id = None
try:
params = get_dbconfig()
conn = connect_db(**params)
cur = conn.cursor() #New transaction
cur.execute(sql, (vendor_name, ))
#Get the vendor ID
vendor_id = int(cur.fetchone()[0])
conn.commit()
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
return vendor_id
from configdatabase import get_dbconfig
import psycopg2
def connect_db(**kwargs):
try:
dbconn = psycopg2.connect(database=kwargs['database'],
user=kwargs['user'],
password=kwargs['password'],
host=kwargs['host'])
except Exception as e:
raise e
return dbconn
if __name__ == '__main__':
try:
dbparams = get_dbconfig()
connect_db(**dbparams)
print("Successfully connected to database.")
except Exception as e:
print("Unable to connect database. Error(%s)" % (str(e)))