...
# SQL statements to execute
statements = [
# Drop the film table if it exists
"DROP TABLE IF EXISTS film;",
# Create the film table
"CREATE TABLE film (title text NOT NULL, rating text,length int);",
# Insert records into the film table
"INSERT INTO film(title, length, rating) VALUES \
('ATTRACTION NEWTON', 83, 'PG-13'), \
('CHRISTMAS MOONSHINE', 150, 'NC-17'), \
('DANGEROUS UPTOWN', 121, 'PG'), \
('KILL BROTHERHOOD', 54, 'G'), \
('HALLOWEEN NUTS', 47, 'PG-13'), \
('HOURS RAGE', 122, 'NC-17'), \
('PIANIST OUTFIELD', 136, 'NC-17'), \
('PICKUP DRIVING', 77, 'G'), \
('INDEPENDENCE HOTEL', 157, 'NC-17'), \
('PRIVATE DROP', 106, 'PG'), \
('SAINTS BRIDE', 125, 'G'), \
('FOREVER CANDIDATE', 131, 'NC-17'), \
('MILLION ACE', 142, 'PG-13'), \
('SLEEPY JAPANESE', 137, 'PG'), \
('WRATH MILE', 176, 'NC-17'), \
('YOUTH KICK', 179, 'NC-17'), \
('CLOCKWORK PARADISE', 143, 'PG-13');",
# Select all records from the film table
"SELECT * FROM film;"
]
# Create a cursor object to execute SQL queries
cur = conn.cursor()
# Execute each SQL statement in the list
for statement in statements:
try:
cur.execute(statement)
if statement.startswith("SELECT"):
# Fetch and print results from SELECT statement
outputs = cur.fetchall()
for output in outputs:
logging.info(f"Query Result: {output}")
else:
conn.commit() # Commit changes after each non-SELECT statement
except psycopg2.Error as exec_error:
conn.rollback()
logging.warning(f"Transaction rolled back due to an error")
except psycopg2.Error as e:
logging.info(f"Error during database operation: {e}")
if conn:
conn.rollback() # Rollback the transaction in case of an error
logging.warning(f"Transaction rolled back due to error.")
finally:
# Close the cursor and connection
cur.close()
conn.close()
print(f"Oxla connection closed.")
