pgdml.py Part 2

pgdml_2.py

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