def select_galleriesphotos():
try:
conn = pgconnection.get_connection("codeinpython")
cursor = conn.cursor()
cursor.execute(
"SELECT galleryname, gallerydescription, phototitle, photodescription FROM galleriesphotos"
)
# get a list of tuples containing the data
data = cursor.fetchall()
cursor.close()
conn.close()
for row in data:
print(row)
except psycopg2.Error as e:
print(type(e))
print(e)
def delete_fk():
delete_dict = {"galleryid": 1}
try:
conn = pgconnection.get_connection("codeinpython")
cursor = conn.cursor()
cursor.execute(
"DELETE FROM galleries WHERE galleryid = %(galleryid)s;",
delete_dict)
conn.commit()
print("Gallery deleted")
cursor.close()
conn.close()
except psycopg2.Error as e:
print(type(e))
print(e)
def insert_photo_invalid_fk():
invalid_photo = {
"galleryid": 6,
"title": "Hong Kong Photo 1",
"description": "Hong Kong Photo 1",
"photographer": "Chris Webb",
"datetaken": datetime.date(2018, 8, 27)
}
try:
conn = pgconnection.get_connection("codeinpython")
cursor = conn.cursor()
cursor.execute(
"""INSERT INTO photos(galleryid, title, description, photographer, datetaken)
VALUES (%(galleryid)s, %(title)s, %(description)s, %(photographer)s, %(datetaken)s);""",
invalid_photo)
conn.commit()
print("Photo inserted")
cursor.close()
conn.close()
except psycopg2.Error as e:
print(type(e))
print(e)
def select_photos():
# Wildcards:
# percent sign % for 0 or more characters
# underscore _ for exactly 1 character
select_dict = {"where_like": '%Oslo%'}
try:
conn = pgconnection.get_connection("codeinpython")
cursor = conn.cursor()
# To make LIKE case insensitive use ILIKE
cursor.execute(
"SELECT galleryid, title, description, photographer, datetaken FROM photos WHERE description LIKE %(where_like)s;",
select_dict)
# get a list of tuples containing the data
data = cursor.fetchall()
cursor.close()
conn.close()
for row in data:
print(row)
except psycopg2.Error as e:
print(type(e))
print(e)
def update_photos():
update_dict = {"description": "Edinburgh Photo 2", "photoid": 10}
try:
conn = pgconnection.get_connection("codeinpython")
cursor = conn.cursor()
cursor.execute(
"UPDATE photos SET description = %(description)s WHERE photoid = %(photoid)s;",
update_dict)
conn.commit()
print("Photo updated")
cursor.close()
conn.close()
except psycopg2.Error as e:
print(type(e))
print(e)
def insert_typesdemo():
row = {
"intcolumn": 123,
"realcolumn": 456.789,
"varcharcolumn": "Now is the winter of our discontent",
"datecolumn": datetime.date(2018, 2, 17),
"booleancolumn": True
}
try:
conn = pgconnection.get_connection("codeinpython")
cursor = conn.cursor()
cursor.execute(
"""INSERT INTO typesdemo(intcolumn, realcolumn, varcharcolumn, datecolumn, booleancolumn)
VALUES (%(intcolumn)s, %(realcolumn)s, %(varcharcolumn)s, %(datecolumn)s, %(booleancolumn)s);""",
row)
conn.commit()
print("typesdemo row inserted")
cursor.close()
conn.close()
except psycopg2.Error as e:
print(type(e))
print(e)
def insert_galleries():
galleries = ({
"name": "London 2018",
"description": "Photos of London in 2018"
}, {
"name": "Paris 2016",
"description": "Photos of Paris in 2016"
}, {
"name": "Oslo 2018",
"description": "Photos of Oslo in 2018"
}, {
"name": "Copenhagen 2017",
"description": "Photos of Copenhagen in 2017"
}, {
"name": "Edinburgh 2015",
"description": "Photos of Edinburgh in 2015"
})
try:
conn = pgconnection.get_connection("codeinpython")
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
cursor = conn.cursor()
for gallery in galleries:
cursor.execute(
"""INSERT INTO galleries(name, description)
VALUES (%(name)s, %(description)s);""", {
'name': gallery["name"],
'description': gallery["description"]
})
print("Gallery inserted")
cursor.close()
conn.close()
except psycopg2.Error as e:
print(type(e))
print(e)
def reset_serial():
try:
conn = pgconnection.get_connection("codeinpython")
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
cursor = conn.cursor()
cursor.execute("ALTER SEQUENCE photos_photoid_seq RESTART WITH 1")
print("photo table serial reset")
cursor.close()
conn.close()
except psycopg2.Error as e:
print(type(e))
print(e)
def main():
print("-----------------------")
print("| codedrome.com |")
print("| PostgreSQL to Excel |")
print("-----------------------")
try:
conn = pgconnection.get_connection("codeinpython")
query_string = "SELECT galleryname, gallerydescription, phototitle, photodescription FROM galleriesphotos"
filepath = "galleriesphotos.xlsx"
pgtoexcel.export_to_excel(conn,
query_string,
("Gallery Name", "Gallery Description", "Photo Title", "Photo Description"),
"filepath")
except Exception as e:
print(type(e))
print(e)
def delete_photos():
delete_dict = {"photoid": 10}
try:
conn = pgconnection.get_connection("codeinpython")
cursor = conn.cursor()
cursor.execute("DELETE FROM photos WHERE photoid = %(photoid)s;",
delete_dict)
conn.commit()
print("Photo deleted")
cursor.close()
conn.close()
except psycopg2.Error as e:
print(type(e))
print(e)
def insert_photos():
photos = ({
"galleryid": 1,
"title": "London Photo 1",
"description": "London Photo 1",
"photographer": "Chris Webb",
"datetaken": datetime.date(2018, 5, 17)
}, {
"galleryid": 1,
"title": "London Photo 2",
"description": "London Photo 2",
"photographer": "Chris Webb",
"datetaken": datetime.date(2018, 5, 18)
}, {
"galleryid": 2,
"title": "Paris Photo 1",
"description": "Paris Photo 1",
"photographer": "Chris Webb",
"datetaken": datetime.date(2016, 9, 1)
}, {
"galleryid": 2,
"title": "Paris Photo 2",
"description": "Paris Photo 2",
"photographer": "Chris Webb",
"datetaken": datetime.date(2016, 9, 1)
}, {
"galleryid": 3,
"title": "Oslo Photo 1",
"description": "Oslo Photo 1",
"photographer": "Chris Webb",
"datetaken": datetime.date(2018, 7, 5)
}, {
"galleryid": 3,
"title": "Oslo Photo 2",
"description": "Oslo Photo 2",
"photographer": "Chris Webb",
"datetaken": datetime.date(2018, 7, 5)
}, {
"galleryid": 4,
"title": "Copenhagen Photo 1",
"description": "Copenhagen Photo 1",
"photographer": "Chris Webb",
"datetaken": datetime.date(2017, 4, 12)
}, {
"galleryid": 4,
"title": "Copenhagen Photo 2",
"description": "Copenhagen Photo 2",
"photographer": "Chris Webb",
"datetaken": datetime.date(2017, 4, 13)
}, {
"galleryid": 5,
"title": "Edinburgh Photo 1",
"description": "Edinburgh Photo 1",
"photographer": "Chris Webb",
"datetaken": datetime.date(2015, 8, 21)
}, {
"galleryid": 5,
"title": "Edinburgh Photo 2",
"description": "Edinburg Photo 2",
"photographer": "Chris Webb",
"datetaken": datetime.date(2015, 8, 21)
})
try:
conn = pgconnection.get_connection("codeinpython")
cursor = conn.cursor()
for photo in photos:
cursor.execute(
"""INSERT INTO photos(galleryid, title, description, photographer, datetaken)
VALUES (%(galleryid)s, %(title)s, %(description)s, %(photographer)s, %(datetaken)s);""",
photo)
conn.commit()
print("Photo inserted")
cursor.close()
conn.close()
except psycopg2.Error as e:
print(type(e))
print(e)
def main():
"""
Demonstrate creation of PostgreSQL database, tables and views using psycopg2
"""
print("------------------")
print("| codedrome.com |")
print("| PostgreSQL DDL |")
print("------------------\n")
# Create a tuple of dictionaries containing the SQL to create database, tables and views
queries = ({
"Description": "Create database",
"Database": "postgres",
"SQL": "CREATE DATABASE codeinpython"
}, {
"Description":
"Create galleries table ",
"Database":
"codeinpython",
"SQL":
"CREATE TABLE galleries(galleryid serial PRIMARY KEY, name varchar(64) NOT NULL, description varchar(256))"
}, {
"Description":
"Create photos table ",
"Database":
"codeinpython",
"SQL":
"CREATE TABLE photos(photoid serial PRIMARY KEY, galleryid smallint REFERENCES galleries(galleryid) NOT NULL, title varchar(64) NOT NULL, description varchar(256) NOT NULL, photographer varchar(64) NOT NULL, datetaken date)"
}, {
"Description":
"Create typesdemo table ",
"Database":
"codeinpython",
"SQL":
"CREATE TABLE typesdemo(serialid serial PRIMARY KEY, intcolumn integer, realcolumn real, varcharcolumn varchar(64), datecolumn date, booleancolumn boolean)"
}, {
"Description":
"Create view galleriesphotos ",
"Database":
"codeinpython",
"SQL":
"CREATE VIEW galleriesphotos AS SELECT galleries.name AS galleryname, galleries.description AS gallerydescription, photos.title AS phototitle, photos.description AS photodescription FROM galleries LEFT JOIN photos ON photos.galleryid = galleries.galleryid"
})
# iterate and run queries
try:
for query in queries:
conn = pgconnection.get_connection(query["Database"])
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
cursor = conn.cursor()
cursor.execute(query["SQL"])
print("Executed {}".format(query["Description"]))
cursor.close()
conn.close()
except psycopg2.ProgrammingError as e:
print(e)