def update_vendor(vendor_id, vendor_name):
""" update vendor name based on the vendor id """
sql = """
UPDATE vendors
SET vendor_name = %s
WHERE vendor_id = %s
"""
conn = None
updated_rows = 0
try:
params = config.config()
conn = psycopg2.connect(**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()
# count of rows
return updated_rows
def connect():
""" Connect to the PostgreSQL database server"""
conn = None
try:
# read connection parameters
params = config.config()
# connect to the PostgreSQL server
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
# create a cursor
cur = conn.cursor()
# execute a statement
print('PostgreSQL database version:')
cur.execute('SELECT version()')
# display the PostgreSQL database server version
db_version = cur.fetchone()
print(db_version)
# close the communication with the PostgreSQL
cur.close()
except (Exception, psycopg2.Error) as error:
print("F**K" + str(error))
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def create_table():
""" Create tables in the PostgreSQL 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:
# read the connection parameters
params = config.config()
# connect to PostgreSQL server
conn = psycopg2.connect(**params)
cur = conn.cursor()
# create table one by one
for command in commands:
cur.execute(command)
# close communication with the PostgreSQL database server
cur.close()
# commit the changes
conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
print("ERROR: " + str(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 = config.config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute(sql, (vendor_name, ))
fetch = cur.fetchone()
vendor_id = fetch[0]
conn.commit()
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
return vendor_id
def get_parts():
""" fetchall() get """
conn = None
try:
params = config.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 delete_part(part_id):
conn = None
rows_deleted = 0
try:
params = config.config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute("DELETE FROM parts WHERE part_id = %s", (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 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.config()
with psycopg2.connect(**params) as conn:
with conn.cursor() as cur:
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))
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def get_vendors():
""" fetchone() get """
conn = None
try:
params = config.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 write_blob(part_id, path_to_file, file_extension):
""" insert a BLOB into a table """
conn = None
try:
# read data from a picture
drawing = open(path_to_file, 'rb').read()
params = config.config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute(
"INSERT INTO part_drawings(part_id, file_extension,"
"drawing_data) VALUES (%s, %s, %s)",
(part_id, file_extension, psycopg2.Binary(drawing)))
conn.commit()
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def read_blob(part_id, path_to_dir):
""" read BLOB data from a table """
conn = None
try:
params = config.config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute(
""" SELECT part_name, file_extension, drawing_data
FROM part_drawings
INNER JOIN parts on parts.part_id = part_drawings.part_id
WHERE parts.part_id = %s """, (part_id, ))
blob = cur.fetchone()
open(path_to_dir + blob[0] + '.' + blob[1], 'wb').write(blob[2])
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def get_part_vendors():
""" query part and vendor data from multiple tables """
conn = None
try:
params = config.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_list(vendor_list):
""" insert multiple vendors into the vendors table """
sql = "INSERT INTO vendors(vendor_name) VALUES(%s)"
conn = None
try:
params = config.config()
conn = psycopg2.connect(**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()
