Category Archives: Learning

Select (cRud) using Perl and DBD::ORACLE

In this post, we’re going to take a look at the R in CRUD: Retrieve.

We will be using the DBD::Oracle driver to retrieve some data from the database tables, using the connection object created in the Initial Setup section of the first post in this series.

Simple query

We will perform a simple query that pulls all of the records in no particular order.  Here are the steps we’ll follow in the code snippet below.

  1. Create the connection object and set it to raise errors.  We will use this object to perform our database operations.
  2. Prepare the SQL SELECT statement, specifying the columns desired from the table.
  3. Execute the statement.
  4. Fetch the results and dump them to $fh.
  5. Disconnect.
When I run this code in my Perl session, I see:

Extra Fun 1

Modify the statement to order by age.  When you’re done the results should be:

Answer

Select specific rows

Now suppose I only want to see the data for Kim. I want, therefore, to restrict the rows returned by the SELECT. This is done with a WHERE clause. There are several ways to do this.

We could just put the where clause in the statement and it would work.

However, we want to choose the name at run time and store it in a variable called person_name.  You could accept the value in as an argument passed into a function, but we’ll just set a variable to keep it simple.

It is possible to simply concatenate the value into the statement.

This is very dangerous and opens our code to a SQL Injection attack.  You can follow that link for more information, but we won’t be going into detail in this series.  Just know that you should, generally, never allow end user input to be fed directly into a dynamic SQL statement.

A much safer way to pass external values into the SQL statement is by using bind variables with prepared statements.

You have a couple different options:

Placeholders:
Notice the bind_param(1,  and bind_param(2, are switched in the two examples.  With a placeholders statement, you use a ? to indicate where the bind variable value goes, then when you assign the bind_param you indicate which placeholder to assign the value to.

Named:
With this method, the :name variable will be assigned the value of ‘name’ in the provided key value set.

Notice, in both examples, that we do not wrap the bind variable for the name with quotes.  This is handled automatically when the statement is prepared for execution.

Example:
  1. Create the connection object and set it to raise errors.
  2. Assign ‘Kim’ to person_name.
  3. Prepare the SQL statement using a bind variable.
  4. Bind the value of $person_name to :name.
  5. Execute the statement.
  6. Fetch the results and dump them to $fh.
  7. Disconnect.
This will return only the data for Kim:

Extra Fun 2

Modify the statement and variable to get the people older than 30.  When you’re done the results should be:

Answer

In this section, we took a look at some basic query functionality.  When you experiment with more complex queries, if you run into problems leave a comment here or on twitter and we’ll find an answer together.

Some things you could try
  • Join the lcs_people and lcs_pets table to get the people and their pets
  • Only retrieve the person’s name and age
  • Change the order to display in descending order.

Hint – If you have trouble getting a query to run in your code, try running it in SQL Plus or another database console tool.  This will help determine if the problem is with the query or the code.

Series sections

Initial Setup
Create records
Retrieve records
Update records
Delete records

Basic CRUD operations using Perl and DBD::Oracle

In this series, we’re going to take a look at performing CRUD (Create Retrieve Update Delete) operations using the DBD::Oracle driver.

A good ORM will handle most of your needs.

An ORM tool can handle many of the repetitive processes when interfacing with a database.  Your project might call for an ORM tool such as DBIx::Class.  No doubt about it,  a good ORM can come in very handy.  An ORM application will typically have a function for passing in raw SQL if needed, so you may not need to go straight to the driver.

Why learn to use the driver directly?

An ORM brings its own, different complexity to a project.  It may be overkill for some projects.  There are also times when a specific task is just different enough that an ORM may not be able to help, or its application to your requirements become so complex that your code becomes difficult to maintain.

And if you’re like me, it’s hard to be satisfied with a black box approach.  You want to know more about how your tools work and you want to have options, just in case.

Martin Fowler said “Mapping to a relational database involves lots of repetitive, boiler-plate code.  A framework that allows me to avoid 80% of that is worthwhile even if it is only 80%.”

When you have enough knowledge to implement direct CRUD operations, you are in a better position to choose the right tool for the right job.

Common Setup

All examples in this series will use the same database objects and connection information.

Creating the Database Objects

The following can be used to setup the initial tables we’ll use.  Please make sure you’re connected to a schema in which you can safely execute commands like these.

 Making the Connection
  1. use strict
  2. use the DBD::Oracle driver.
  3. Get the connection string from the environment variable.
  4. Create the connection object.
  5. Set the connection to raise errors.

We will include this code section with all examples and use the connection object “con” throughout the series.

Cleanup

To  clean up the database when you are finished with the series, you just need to drop the two tables.  Please make sure you’re connected to the correct schema where you created the tables.

Guide to Perl DBD::Oracle CRUD Series

Initial Setup
Create records
Retrieve records
Update records
Delete records

Basic CRUD operations using Ruby-OCI8

In this series, we’re going to take a look at performing CRUD (Create Retrieve Update Delete) operations using the ruby-oci8 driver.

A good ORM will handle most of your needs.

An ORM tool can handle many of the repetitive processes when interfacing with a database.  Your project might call for an ORM tool such as ActiveRecord.  No doubt about it,  a good ORM can come in very handy.  An ORM application will typically have a function for passing in raw SQL if needed, so you may not need to go straight to the driver.

Why learn to use the driver directly?

An ORM brings its own, different complexity to a project.  It may be overkill for some projects.  There are also times when a specific task is just different enough that an ORM may not be able to help, or its application to your requirements become so complex that your code becomes difficult to maintain.

And if you’re like me, it’s hard to be satisfied with a black box approach.  You want to know more about how your tools work and you want to have options, just in case.

Martin Fowler said “Mapping to a relational database involves lots of repetitive, boiler-plate code.  A framework that allows me to avoid 80% of that is worthwhile even if it is only 80%.”

When you have enough knowledge to implement direct CRUD operations, you are in a better position to choose the right tool for the right job.

Common Setup

All examples in this series will use the same database objects and connection information.

Creating the Database Objects

The following can be used to setup the initial tables we’ll use.  Please make sure you’re connected to a schema in which you can safely execute commands like these.

 Making the Connection
  1. require the ruby-oci8 driver.
  2. Get the connection string from the environment variable.
  3. Create the connection object.

We will include this code section with all examples and use the “con” connection object “con” throughout the series.

Cleanup

To  clean up the database when you are finished with the series, you just need to drop the two tables.  Please make sure you’re connected to the correct schema where you created the tables.

Guide to Ruby-OCI8 CRUD Series

Initial Setup
Create records
Retrieve records
Update records
Delete records

Use Oracle Spatial to locate the dinosaurs

In a previous post, we used Oracle Spatial to generate a random polygon we can use as a home for our dinosaurs.  Now, let’s have the dinosaurs using DinoDate tell us where they are so we can update the system.

Our goal is to create a function that accepts a valid polygon (the island) and returns a point at a random location inside that polygon.  We’ll call it generate_random_location.  We’ll also add in a bunch of variables we’re going to need.

As we discussed in the previous post, every so often the island the dinosaurs live on randomly changes shape and possibly location. When this happens, the dinosaurs all group up at the center of the island until it’s reformed then they move out at random from there.  (At least that’s the best “explanation” I can come up with to avoid a discussion of “true randomness” in the distribution of points.)

We’re going to use some of the same Oracle Spatial functions we used in the previous post to generate a point (x) kilometers from the island’s center in a random direction and we’ll take a look at some new functions.

In the sdo_geom package, there are some functions we can use to determine properties of the minimum bounding circle (MBC) for our polygon.  This is basically the smallest circle that our island will fit in within a given tolerance.  We’ll need to figure out the coordinates for the center of the island and its maximum radius.

We’ll add a little to the radius just to make sure we have a distance slightly larger than the island and we’ll get the X, Y coordinates for the center.

Let’s create a random radian, generate a point in that direction at a distance of our v_radius we calculated above and get it’s X, Y coordinates.

We’re going to use this new point and the center of the island to create a line.  Then, using another function from the sdo_geom package, sdo_intersection,  we can create a line that is just the intersection of the new line and our island.  The length of this line tells us how far it is from the center of the island to the edge in the given direction.

Finally, we generate a new point along our new_radian at a random distance from 0 (center of the island) to v_length (edge of the island) and return that point.

You can find the full formatted code in this gist.

Now let’s figure out where those dinosaurs are.

Before we get started, let’s clear out any pre-existing data.

We’re going to need a couple variables to hold a new island and a list of dinosaurs.

Using the function from the previous post, we can create a new island at the center of the Bermuda Triangle, and insert it into the location table.

For reasons explained here, we’re going to use a bulk collect followed by a forall update instead of a simple update statement, to populate the dinosaurs locations.

Finally, we can print out the number of changed rows and commit.

You can find the full formatted code in this gist.

Using Oracle SQL Developer, we can see our island in the Map View.

Screenshot from 2016-07-26 14-48-31

We’ll put a dot up for each dinosaur on the island.

Screenshot from 2016-07-26 14-50-35

Notice the points tend to cluster in the center and spread out from there.  That’s due to always using the center point as one end of our random calculation.

Please experiment with other options and share in the comments below.

Leave your comfort zone behind: head to an open conference

If you are a(n) [insert language here] programmer, you probably attend a(n) [insert language here] conference when you’re able.  That’s great, since conferences are an awesome way to improve your skills in [insert language here] .

However, I also recommend that you try to attend an “open (source, hardware, data, ….)” conference if and when you can.  The broader the scope, the better.  Odds are, there will still be something related to your [insert language here] skills that you will find useful (and can use as justification to attend, if need be) but the real value comes from semi-random association with new stuff. Stuff you’ve never seen before, maybe never thought about before.

Recently, I attended OpenWest and of course there were plenty of sessions on technologies I use regularly (NodeJS, Python, Raspberry Pi,  Docker,  Privacy/Security,  and a ton of stuff I don’t know how to do …. yet).  I attended a few of these and learned some new tricks.  I also attended some sessions that were out of my comfort zone.  I learned a bit about Bitcoin and Block Chain, tried to keep up with some Linux server discussions, was completely out of my league listening to a session on Recent Advances in Microcontrollers and, after struggling through some basic “hacker” challenges, I got to dust off my soldering skills and make a cool LED badge.

Why does this matter?  Diversity is the key to sustained growth.  This famously applies to your financial investments, but it’s also important to your growth as a technologist.  If you continue to do the same/similar thing over and over, with the same technology, you will undoubtedly get really good at it.  The potential downside, however, is that you may be perceived as that person who only has a hammer, so everything looks like a nail.

If you enjoy being the go-to person when there’s a problem to be solved in technology X and no expert in X in sight, you need to keep adding to your bag of tricks. You need to keep challenging yourself.

This doesn’t mean that you have to have all of the answers. Sometimes it’s enough to know that a better solution exists “out there” somewhere, and help get the team pointed in the right direction.

Before going to OpenWest, I had experimented with my Raspberry Pi, but only in software ways like OSMC and OpenHab.  I do have an Arduino and I’ve read through a few projects that look fun, but I’ve hesitated to get started, assuming that it’s going to take too much time to ramp up my skills and I’ll probably burn it up unless I practice my soldering skills…. a lot.

Now, I’ve learned that the hardware components are more rugged than I assumed and I’m not going to burn them up with a minor solder error and once again I’m experiencing with how much fun it is to be the “new kid” learning cool, new things.

It is definitely time to go buy a good soldering iron.

Draw a maple leaf using Oracle Spatial

Natalka asked if I can make a maple leaf with Oracle Spatial.

The most basic way to draw any polygon with Oracle Spatial is to”

  1. Plot the shape on a graph.
  2. Add the grid coordinates to a polygon SDO_GEOMETRY object. Remember to go in a counter-clockwise direction.

For a maple leaf, I searched duckduckgo.com for a bit and found a very detailed grid mapping of a maple leaf.

The points in that doc are ordered in a clockwise direction, so I reversed the order and added them to an SDO_ORDINATE_ARRAY which generates the following.

MapleLeaf1

It’s a nice leaf but not quite Canadian enough.

I was unable to find a complete mapping of a leaf closer to the Canadian flag, so I found several examples that were all similar but incomplete, and did my best to guess at the missing data.

I started at the top point and worked my way counter-clockwise around the leaf points to the bottom left stem point.

Since I centered it on the 0 x axis I simply removed the negative from the x ordinates, reversed the order of the coordinates and added them to the SDO_ORDINATE_ARRAY.  Notice the start and end points are the same to close the polygon.

After checking that the object is valid using .st_isvalid() I generated this leaf.

MapleLeaf2

Making Polygons with Oracle Spatial

Lately, I’ve been digging into Oracle Spatial.  It’s a lot of fun, but wow, there’s a lot to learn.

I’m working on a series of tutorials that will walk through the process of building an island for our DinoDate dinosaurs to live on.  But in the meantime, I thought we could have some fun and look at a function to generate simple shapes.

For a detailed explanation of the SDO_GEOMETRY object, you can check out the docs, or watch for my upcoming tutorial.  In this post, I’ll just add brief comments to explain the parameters.

I realize the code formatting isn’t the best on this post, so I’ve created a gist with the finished function.

Our function accepts a latitude and longitude we’ll use to position our shape, the number of sides, a radius and a starting radian.

A Point

Accepting the defaults gives us a 1 sided object with a 0 radius, sounds like a point to me.  In fact, let’s make anything with a radius of 0 a point.

  • 2001, –2 dimensions, 0 LRS, 01 is a point
  • 4326, –SRID for Earth lat/lon system
  • MDSYS.SDO_POINT_TYPE(p_lon, p_lat,NULL), –point located p_lon, p_lat
  • NULL, –not used for a point
  • NULL) –not used for a point;

Generating Shapes

When we generate the rest of our shapes, we will:

  1. Start with the given point at p_lon / p_lat.
  2. Go a distance of p_radius in meters.
  3. In a direction of p_start_radian.
  4. Get the lat / lon position of the new point.
  5. Decrement our current radian by (2pi / sides. ) 2pi = (asin(1)*4)
  6. Repeat for each corner.

If you’re like me and it’s been so long since you took a trig class that you’re a bit fuzzy on what a radian is here’s an excellent definition.  The example in that post shows a 0 radian starting in the 3 o’clock position and moving counter clockwise.  use a 0 radian at the 12 o’clock position and move clockwise.

Generating Corner Points

The function we’re using to generate corner points is sdo_util.point_at_bearing.  It considers 0 radians to be at the 12 o’clock position and moves clockwise.

Parameters:

  • sdo_geometry point object
  • radian from 0 to 2pi
  • radius in meters

Once we have the new point, we’ll get the x / y coordinates of the point using sdo_util.getvertices and we’ll add those values to an sdo_ordinate_array.

The sdo_ordinate_array defines the corner point for our shapes (or end points for a line) and the sdo_elem_info_array acts as a key used to define how we use those coordinates.

Once again, if you want to dig into the details look at the docs, but here I’ll add short definitions.

Let’s add some new variables to work with.

A Line

A 1 sided object with a radius > 0 could be a line or a circle.  I’ve chosen to use an SRID of 4326 which is a lat/lon earth based system and is not a geodetic coordinate system, so circles are a bit more complex than I want to cover in this post.  So, a line it is and we’ll include 2 sided objects as lines also just to keep it simple.

I know, I used a bunch of fancy words and didn’t explain them.  I’m trying to keep this post simple, but if you’d like to dig into all of the details, take a look at the docs.

  • 2002, –2 dimensions, 0 LRS, 02 is a line
  • 4326, –SRID for Earth lat/lon system
  • NULL, — only used for a point
  • SDO_ELEM_INFO_ARRAY(1, –start with 1st ordinate
    • 2, –a straight line
    • 1), –a simple straight line
  • SDO_ORDINATE_ARRAY(p_lon, p_lat, –start coordinates
  • v_ordinate_array_points(1), v_ordinate_array_points(2))); –end coordinates

For a line, we use the p_lon, p_lat parameters as our first point and our generated values as our second point.

Polygons With 3 or More Sides

For our polygons, we use the p_lon, p_lat parameters as a center point and generate our corner points around the center.

When you define a simple polygon the exterior edge points need to be defined in a counter-clockwise direction.  If you create more complex polygons, such as a polygon with holes, the interior polygons are defined in a clockwise direct.  Check out the docks if you want to experiment with more complex objects.

When we define a closed polygon, our sdo_ordinate_array will contain a set of points for each corner.  In order to close our polygon, we add an extra point at the end that is the same as the start point.

  • 2003,  — 2-dimensional polygon
  • 4326, –SRID for Earth lat/lon system
  • NULL, — only used for a point
  • SDO_ELEM_INFO_ARRAY(1, –start with 1st ordinate
    • 1003, — simple polygon
    • 1), — simple polygon
  • v_ordinate_array_points –coordinate array

Let’s make some shapes

First, we’ll make a point at the center of the Bermuda Triangle.

Using the SQL Developer Map View we can see our point.

start_point

Now let’s add a 100-meter line at 0 radians.

line_0_radian

We’ll move the starting radian to -.75 * pi.  We’ll also set p_sides to 2 since we treat 1 and 2 as a line.

line_neg_three_quarter_pi

From here on, we’ll leave this point and line on the view for reference and we’ll just increment the p_sides parameter.

triangle

diamond

pentagon

seven_sides

octagon

nine_sides

ten_sides

I think that’s enough examples.  I hope you’ve found this useful or at least a little fun.

Feel free to leave questions or suggestions in the comments.

 

DinoDate – a demonstration platform for Oracle Database

I’ve heard people talk about how “SQL databases are dinosaurs.”  I’m sure they don’t mean it as a compliment, but if you think about it, well, why wouldn’t it be?

dino-date-text

Dinosaurs were the dominant terrestrial vertebrates for 135 million years.  It took a giant asteroid to stop them from ruling the earth forever (or for a few million more years, anyway).

Here at Oracle, we admire dinosaurs so much, we launched an expedition to see if we could find any.  And what do you know? We found a remote island that doesn’t appear on any maps….populated entirely with dinosaurs!

Who would have thought?

Instead of building an amusement park on the island, we decided to stealthily observe the dinosaurs, so we can better understand them. Here’s what we’ve learned so far:

  • Dinosaurs are busy: they spend a lot of time searching for food and trying to avoid becoming food.
  • Dinosaurs are very shy: that’s why they have gone undetected for so long.

Unfortunately, being busy and shy is a bad combination when it comes to finding a spouse and propagating the species. So the Oracle Developer Advocates team decide to help them out.

Just last week, I flew to this secret, hidden island and presented to our unsuspecting users, for the first time ever, DinoDate: the premier online dating service for dinosaurs.

OK, so, not really. We didn’t really find an island of dinosaurs. We aren’t really going to be able to help dinosaurs find their perfect match.

But we hope you agree with us that DinoDate is more entertaining than another human resources app built on to top of the employees and departments tables.

DinoDate as a Teaching Platform

Oracle Database is packed full of features that can be of great assistance to application developers, whether building those applications in Oracle Application Express, JavaScript, Python or any number of other scripting languages.

Such features include SQL itself, PL/SQL, Oracle Spatial, Advanced Queueing, Oracle Text and more.

Yet many developers are not aware of these features or greatly underutilize them in their application development.

We believe that the results of this underutilization are applications that are not as fast as they could be, not as secure as they could be, and not as easy to maintain as they could be.

So we want to help application developers learn about – and learn how to use – all these great features. And DinoDate will be one of our delivery vehicles for this learning process.

DinoDate will provide examples of advanced Oracle Database features such as spatial queries, advanced queuing and text searches.  We will continue to add new examples and features, such as analytical functions, over time. And we will not only use these features, but show you how and why we are using them.

ShowCode

DinoDate Architecture: diversity is key to survival

We’ve designed DinoDate so that we can show how Oracle Database appdev features can be leveraged from  many different programming languages, starting with Python, NodeJS and PHP and adding others later.  This will benefit those who want to learn a new language by allowing them to see how the feature works in each language.

Each feature will be implemented in each language while attempting to follow recommended practices.  Since the database interface takes place at the server application level, each language specific application will provide a restful interface instead of creating views.

AngularJS is used to provide a common shared front end.  If we add a language that is unable to support the restful interface we’ll create custom views.

At the time of this post, we have the AngularJS and Python pieces in place, parts of NodeJS working and the legacy PHP code included.  The plan is to finish with NodeJS then refactor PHP.

After that we have a few things in mind, but the main plan is to choose the next stack based on your interest.  Please leave comments about where you’d like us to go next.

I’m sure there are a lot of people much better at each of these languages than we are, so if you see something we did incorrectly let us know and we’ll get the changes incorporated.

Please download the project and after you get it up and running, feel free to add comments below for any improvements we can make.

This has been a fun project so far, and I’m just getting started. I look forward to learning more – from the technology and from you – as we expand DinoDate.

Getting DinoDate Up and Running

You’ve downloaded DinoDate, the premier dating website for dinosaurs. Now lets get it up and running!

Prerequisites

  • Download Logger and extract it to dino-date/coreDatabase
  • Check Logger’s create_user.sql file. If you see an exit statement at the very end , remove it.
  • Install Bower.
  • Install Oracle Instant Client with the sdk modules.

Installation

Database Schemas and Objects
IMPORTANT – Please make sure you’re using a database instance in which you can safely create  schemas named DD, DD_NON_EBR and DD_LOGGER.
  1. Navigate to dino-date/coreDatabase
  2. Run dd_master_install.sql from an account connected “as sysdba”.
  3. First prompt is for the directory into which you extracted Logger, e.g. Logger_3.1.1
  4. Second prompt is what you want to name the schema that holds the Logger database objects.
  5. Third and fourth prompts are for your Tablespace and Temporary Tablespace.
  6. Fourth prompt is for the logger schema password
Common Client

Open a prompt and navigate to dino-date/commonClient

RESTful Tier

Currently there are RESTful APIs written in both NodeJS and Python.  You can choose to run one or both.

NodeJS

Open a prompt and navigate to dino-date/nodejs

Python

Open a prompt and navigate to dino-date/python

Configure your Environment

DinoDate uses environment variables for database connection and port settings.

Create the following environment variables using the correct values for your system.

  • dd_connectString=localhost:1521/orcl
  • dd_user=dd
  • dd_password=dd
  • dd_port=8888
  • dd_python_port=8080
  • dd_node_port=3000

Run DinoDate

NodeJS

Open a prompt and navigate to dino-date/nodejs

DinoDate (NodeJS) will be listening on the dd_node_port port you defined above.

Python

Open a prompt and navigate to dino-date/python

DinoDate (Python) will be listening on the dd_python_port port you defined above.

Running Both

You can run both versions at the same time, provided you specified different ports.

This will allow you to switch between languages by changing the ports and view the code examples specifically for each language.

Please leave a comment if you run into trouble.

Delete (cruD) using cx_Oracle

In this post we’re going to take a look at the D in CRUD: Delete.

We use the cx_Oracle driver to delete some data in the database tables, using the connection object created in the Initial Setup section of the first post in this series.

PLEASE REVIEW ALL EXAMPLE CODE AND ONLY RUN IT IF YOU ARE SURE IT WILL NOT CAUSE ANY PROBLEMS WITH YOUR SYSTEM.

Helper Function

My helper function get_all_rows() encapsulates a select statement used to verify that the deletes worked. The select functionality is covered in the R part of this series, so I won’t go into the details here.

Add this function to the top of your file.

Resetting the data

To keep the examples clean and precise, I will reset the data at times.

Create a new file called reset_data.py with the following code and run it whenever you would like to reset the data. (Notice this version adds people and pet data not included in other sections.)

Boilerplate template

The template we will be using is:

For each exercise, replace the “# Your code here” line with your code.

Reset the data

First let’s run reset_data.py to setup our data.

Simple delete

We will perform a simple delete that removes a single record in the cx_people table.  These are the steps performed in the code snippet below.

  • Get a cursor object from our connection.  We will use this cursor to perform our database operations.
  • Prepare a SQL DELETE statement, deleting the cx_pets record with an id of 1.
  • Execute the statement using bind variables.  (see the R part of this series for an explanation of bind variables)
  • Commit the transaction.
When I run this code in my Python session, I see:

Extra Fun 1

Delete all the birds .

Your results should be:

Answer

Reset the data

Now is a good time to run reset_data.py.

Boilerplate change

Change the boilerplate get_all_rows statements to get people and pet data.

Deleting records referenced by Foreign Keys

If you are using integrity constraints in your database (of course you are, because then you let the database do some heavy lifting for you), you will sometimes need to change the way you process your changes.

In our design, we have a Foreign Key constraint in cx_pets that ensures if a pet has an owner, that owner exists.

This is the statement that creates the constraint in the Creating the Database Objects section of the Initial Setup post.

If we attempt to delete a record in cx_people that is referenced in cx_pets (Person has a pet,) we get an error.

When I run this code in my Python session, I see:

Before deleting the person you have to handle the pet (watch out for claws and teeth).

There are a few options here, depending on your database design:

  • If: pets are not required to have an owner and you only want to delete the person, not the pets.  Then: you can update the pets and set their owner to null.
  • If: pets are required to have an owner.  Then: you can delete the pets for the owner.

In either of the above scenarios you can update the pets and set their owner to another person.

Bob is moving out of our area and his new apartment doesn’t allow pets, so he’s giving them to Kim.  Let’s use that last option here.

  • Get a cursor object from our connection.  We will use this cursor to perform our database operations.
  • Prepare a SQL UPDATE statement, changing owner to 2 (Kim) for the records with an owner of 1 (Bob).  Updating is covered in the U part of this series.
  • Execute the statement using bind variables.
  • Prepare a SQL DELETE statement, deleting records with an id of 1 (Bob).
  • Execute the statement using bind variables. (see the R part of this series for an explanation of bind variables)
  • Commit the transaction.
When I run this code in my Python session, I see:

When you change data it’s a good idea to verify the number of affected rows.  This is covered in the R part of this series.

Extra Fun 2

Due to a zoning change, snakes are no longer allowed in our area.  Stacey has decided to move and take Sneaky with her.

Lets fix our data.

Your results should be:

Answer

Some other things you could try
  • Change the database constraints to delete or Null the child record on delete (a cascading delete).  Delete a person and let the database handle the children.
  • Remove the people who don’t have any pets.

Series sections

Initial Setup
Create records
Retrieve records
Update records
Delete records