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()