Detailed Notes
Focus for this episode: This episode steps into some issues we can see with related data and cleaning up tables when the records are referenced elsewhere.
This tutorial walks through the creation of a database synchronization tool in Python. It continues our Python and SQL Tutorials with a useful application that provides a deeper look at SQL structures and commands.
Repository For Code: git clone [email protected]:rbroadhead/dbsync.git
Transcript Text
thank you [Music] well hello and welcome back we are continuing our SQL python combo tutorial series and we went through a longer one last time I apologize but it was one of those it does take a little bit of time to go do some of that cleanup and uh did get that stuff cleaned up so now we have our uh sort of an organized DB sync class that has stuff sitting in it to sort of help us out and call it from different places and then we're going to run everything from our main script now looking at our to do thing um let's see so we basically got uh how to import related data multiple ways generalize extract key functions okay so we got the first part of this so we're going to do this like this because we're going to come back to it we're going to provide a way to walk multiple databases we're not going to do that today we are going to deal with uh we're going to start dealing with our data issues a little bit someone we can run into this is going to start us on walking the foreign key hierarchy just a little bit but also it highlighted to me that we have not pulled across foreign Keys we deal with primary keys but we actually do not create any other references so that's something we're going to end up picking up as well probably not this episode because we're going to keep it simple this time so I want to show you what I'm I'm running into here uh what got us going so when we go through when we go uh see where's that uh Rose there we go so we do sync rows we come in and we just do a truncate of each table so every table we just go through and empty it and start pulling rows across now we may not want to do that so this is an option you know there's something else that we're going to start exploring as we go further on but right now let's just work with this trunk cut truncate now let's just take two tables here address and lookup type if I truncate them no problem I can go through them however if I suddenly add a foreign key relationship well that's a drop so if I add to the table address a constraint where now its type ID is referenced in lookup type and then we'll see what that looks like real quick if you look at address you can see here instead of PRI it's got this mul for the key and then if I try to do a truncate bam I get an error cannot truncate a table referenced in a foreign keyword constraint so if I have that there when I'm running through trying to do my simple processes so if I come through here let's see where's Maine let's just do this so if I do it then it's not going to like it because it's going to blow up right here foreign key constraint fails so what we need to do is we need to be able to First adjust those constraints and what we really want to do is for that given one we want to check so we're going to do that here when we come in here the first thing we want to do before we do this truncate is we're going to check if this is referenced in a foreign key so what we're going to do is we're going to see if this table which in this case it would be lookup type we've got to see does something reference it now it's not that address references it so we can't find it if we look back at your description of address we don't see that also if we look at description of lookup type we don't see that we don't see where it's it's referenced anywhere so we have to go back into our information schema and this is actually going to be in referential constraints and so what we're going to do is let's just do that and again where the table is tutorial if we look then we're going to see here because we don't notice we didn't even have a name before or I'm sorry here that's a drop when we do add constraint we didn't give it a name it just has a name so we have this constraint name we can see here that the reference table is lookup type which is the that's what we care about and then the table name that references it we've got that so between that because if you remember the way we get rid of a foreign key is it has to be alter table the table name that it's on not the one that reference the one that references it not the table and then the name of the foreign key so we would have the constraint name so we have the constraint name we have the table name and we have the thing that says hey we need to care about this so what we want to do first and we probably don't need all of these so we're going to probably clean this up a little bit let's go with our query so what we want to do is we want to do a first uh let's just do a net we'll go ahead and pull all of them so we really don't need anything other than let's go look at this real quick the shortened version and we really don't need we don't care what the unique constraint name uh we do care about the unique constraint name we don't care about the match option we don't care about the update rule we don't care about the delete rule although we could tweak it there uh let's see and so we have our oh we don't need our unique constraint name so constraint name table name reference table and really what we need to do here and reference table name equals and in this case uh lkp underscore typ and so we're going to get is Bam we have those and we really don't even need that second one so now that's all we need and so from there we're going to take that and we're going to do an altered table because we're going to go ahead and we're just this sort of again sort of a Brute Force approach is we're going to go through and we're going to say hey if it's there we're going to drop it and then we're going to be able to truncate so what we need to do here is this is going to be in the destination database so this is going to be our check SQL equals and what we need to do here is we're going to pull all these and what we're going to do is we're just going to make this painfully simple to do so uh let's see so we're constrained schema equals and that's going to be our table which whoops I already have it down there we've already loaded it up actually I guess it we loaded it right there for table in oh I'm sorry that's constraint that's not the table whoops so here we're going to need our oh this is where so we're going to change this a bit because before we would have places where we would send let me see here we would send our database name so we're going to have to call what yep where we are here so we're going to have to change this and we're going to have to call this um destination DB um let's do destination DB name just to be safe and then when we do sync rows now we're going to have to call it whatever our database name is this is something we're going to clean up a little bit later as well by the way but for now so this is going to be a destination DB name let's just do it like this um do that to sort of clean it up a little bit from its length and then the reference table name this is where we're gonna do that's just going to be table and then I don't need this part that comes later so let's see so close that out so first what we want to do is we're going to go through uh where is our this is our cursor destination and we're going to do a DOT execute um oh good question how do we want to do this let's do uh we're going to do this here first so that's going to be the check SQL but and then we're going to come in and we're going to have to get rid of that thing but we want to take the descriptive query let's see do we want to do that uh yes so first I'm going to do is we're going to try to get rid of that so uh get rid of our data so we come in here blah blah blah Okay so so first we empty our table we don't need to know anything but the table name there so what we can do is we come in here and we can say four row in let's call that for foreign key row in and this will be um let's call it FK rows then we're going to go through here and then for each one we're going to do this uh FK SQL equals and then we're gonna have to clean all this thing up this way this is one of those exciting parts of the tutorial where we will watch Rob type so we go there we go there we go there so we come in here we're going to alter table and we need to give it our table name and that's going to be our table and then we're going to drop foreign key oh actually nope this is going to be in FK row zero because that's our oop our table name is one I'm sorry and then we're going to drop foreign whoops Plus uh FK row zero and then we're going to execute it so we're going to do uh cursor destination dot execute hopefully this won't blow up because we're using same cursor but I think we're okay because we always have our rows and now we should be able to do it so running this same thing before when we got rid of this all right let me pull that back out just to verify it's still there so before it's going to blow up there it blows up now if I come up here and I put our code in to take care of that uh let me just do this real quick just to make sure we show see what we're building here uh that's FK SQL let's make sure I got that right yeah okay so now if we look at it bam up now we're getting a nun type object is not iterable oh if okay rows oh um oh my mistake I need to do a fetch all I knew there was something simple I was missing there uh let's see so that's going to be cursor destination whoops that's where the FK Rose is put my S there now we should see that and so now we're seeing cannot add or update a childhood foreign key constraint fails and a good question so if we come through here before we get rid of it oh we missed something we did not get that right uh let's see data sync for address cannot update uh what did we do here cfk SQL check SQL [Music] and we did something not quite right here so let's see for table and Source tables then I'm going to take let's make sure we got that right maybe I missed something there I probably did uh print the check SQL and if we look let's see select constraint name reference table name equals address oh you know what oh okay so that's none which is good uh I wonder if it's uh giving me this so he should come through and we're missing something here so okay let's figure out what we got in our little typos um cannot add or updated child row constraint which should be fine mean 20 but it's in DB sync 402 cannot add or update a child row oh because we're sinking the rows at a time we need to actually okay what we need to do is we actually need to go through here and first uh see we're gonna do the same thing we've got to get rid of all of these before we do our truncate so let's do this uh first we're going to do that so we're going to come through here we don't need to know we have to do any of the truncated stuff because we've already done that okay and this is going to be data clean for and we're going to do that and then we're going to do the whoops let's take the that part to where we were and so we're going to come in here and we're going to go for each table and first we're going to clean out all the data because what was happening is we're running into some issues where we were pulling data in but we had a foreign key that was blowing us up so now let's see if that'll work uh yeah we still have our prints and we're still running into something here so we're not finding any so when we find one let's make sure I got that right I don't think I did I think I lost one something there um because I don't think it's foreign key yeah that's why that's what I thought I missed something a typo okay so if I go in here and I do 4 key now we're going to do it and now it's going to bring all our data across so once again we've found a couple you know bumped our head against something and been able to make a correction for it now what we have also found is that we did not you know we had to go in and manually create our um our constraint here what we're going to do next time around is we need to be able to uh we're going to be like let's say sync constraints sync non-primary constraints and that is what we're going to tackle next time that being said we can wrap this one up while this thing is running in the background trying to get some data moved across but while we're waiting for that go out there and have yourself a great day a great week and we will talk to you next time foreign
Transcript Segments
thank you
[Music]
well hello and welcome back we are
continuing our SQL python combo tutorial
series and we went through a longer one
last time I apologize but it was one of
those it does take a little bit of time
to go do some of that cleanup
and uh did get that stuff cleaned up so
now we have our uh sort of an organized
DB sync class that has stuff sitting in
it to sort of help us out and call it
from different places and then we're
going to run everything from our main
script
now looking at our to do thing
um let's see so we basically got
uh how to import related data multiple
ways generalize extract key functions
okay so we got the first part of this so
we're going to do
this like this because we're going to
come back to it we're going to provide a
way to walk multiple databases
we're not going to do that today
we are going to deal with uh we're going
to start dealing with our data issues a
little bit someone we can run into this
is going to start us on walking the
foreign key hierarchy just a little bit
but also
it highlighted to me that we have not
pulled across foreign Keys we deal with
primary keys
but we actually do not create any other
references so that's something we're
going to end up picking up as well
probably not this episode because we're
going to keep it simple this time
so I want to show you what I'm I'm
running into here uh what got us going
so when we go through when we go uh
see where's that uh Rose there we go so
we do sync rows we come in and we just
do a truncate of each table so every
table we just go through and empty it
and start pulling rows across now we may
not want to do that so this is an option
you know there's something else that
we're going to start exploring as we go
further on but right now let's just work
with this trunk cut truncate
now let's just take two tables here
address and lookup type
if I truncate them no problem I can go
through them however
if I suddenly add a foreign key
relationship well that's a drop
so if I add to the table address a
constraint where now its type ID is
referenced in lookup type
and then we'll see what that looks like
real quick if you look at address you
can see here instead of PRI it's got
this mul
for the key
and then if I try to do a truncate bam I
get an error cannot truncate a table
referenced in a foreign keyword
constraint so if I have that there when
I'm running through trying to do my
simple processes
so if I come through here let's see
where's Maine
let's just do this
so if I do it then it's not going to
like it because it's going to blow up
right here foreign key constraint fails
so what we need to do is we need to be
able to First adjust those constraints
and what we really want to do
is for that given one we want to check
so we're going to do that here
when we come in here the first thing we
want to do before we do this truncate
is we're going to check if this
is referenced
in a foreign key
so what we're going to do is we're going
to see if this table which in this case
it would be lookup type we've got to see
does something reference it
now it's not that address references it
so we can't find it if we look back at
your description of address we don't see
that also if we look at description of
lookup type
we don't see that we don't see where
it's it's referenced anywhere so
we have to go back into our information
schema
and this is actually going to be in
referential constraints
and so what we're going to do is let's
just do that and again where the table
is tutorial if we look
then we're going to see here because we
don't notice we didn't even have a name
before or I'm sorry here
that's a drop when we do add constraint
we didn't give it a name it just has a
name
so
we have this constraint name we can see
here
that the reference table is lookup type
which is the that's what we care about
and then the table name that references
it we've got that so between that
because if you remember the way we get
rid of a foreign key is it has to be
alter table
the table name that it's on not the one
that reference the one that references
it not the table and then the name of
the foreign key so we would have the
constraint name so we have the
constraint name
we have the table name and we have the
thing that says hey we need to care
about this so
what we want to do first
and we probably don't need all of these
so we're going to probably clean this up
a little bit let's go with our query so
what we want to do is we want to do a
first
uh let's just do a
net we'll go ahead and pull all of them
so we really don't need anything other
than let's go look at this real quick
the shortened version and we really
don't need we don't care what the unique
constraint name
uh we do care about the unique
constraint name we don't care about the
match option we don't care about the
update rule we don't care about the
delete rule although we could tweak it
there
uh let's see and so we have our oh we
don't need our unique constraint name so
constraint name table name reference
table
and really what we need to do here and
reference table name
equals and in this case uh lkp
underscore typ
and so we're going to get
is Bam we have those and we really don't
even need that second one so now that's
all we need
and so from there we're going to take
that and we're going to do an altered
table because we're going to go ahead
and we're just this sort of again sort
of a Brute Force
approach is we're going to go through
and we're going to say hey if it's there
we're going to drop it and then we're
going to be able to truncate so what we
need to do here
is this is going to be in the
destination database so this is going to
be our check SQL equals
and what we need to do here
is
we're going to pull all these and what
we're going to do is we're just going to
make this
painfully simple to do so uh let's see
so we're constrained
schema equals
and that's going to be our table
which whoops
I already have it down there
we've already loaded it up
actually I guess it we loaded it right
there for table in
oh I'm sorry that's constraint that's
not the table whoops
so here we're going to need our
oh this is where so we're going to
change this a bit because before we
would have places where we would send
let me see here
we would send our database name
so we're going to have to call what yep
where we are here so we're going to have
to change this and we're going to have
to call this
um
destination DB
um let's do destination DB name just to
be safe
and then when we do sync rows
now we're going to have to call it
whatever our database name is
this is something we're going to clean
up a little bit later as well by the way
but for now
so this is going to be a destination
DB name
let's just do it like this
um
do that to sort of clean it up a little
bit from its length and then the
reference table name this is where
we're gonna do
that's just going to be table
and then
I don't need this part that comes later
so let's see so close that out so first
what we want to do is we're going to go
through uh where is our this is our
cursor destination
and we're going to do a DOT execute
um
oh good question how do we want to do
this let's do
uh we're going to do this here first
so that's going to be the check SQL
but and then we're going to come in and
we're going to have to get rid of that
thing but we want to take the
descriptive query
let's see do we want to do that
uh yes so first I'm going to do is we're
going to try to get rid of that so
uh get rid of our data so we come in
here blah blah blah
Okay so
so first we empty our table we don't
need to know anything but the table name
there so what we can do is we come in
here and we can say four row
in
let's call that for foreign key row
in
and this will be
um
let's call it FK rows
then we're going to go through here
and then for each one we're going to do
this uh FK SQL
equals and then we're gonna have to
clean all this thing up
this way
this is one of those exciting parts of
the tutorial where we will watch Rob
type so we go there
we go there we go there
so we come in here we're going to alter
table
and we need to give it our table name
and that's going to be our table and
then we're going to drop foreign key
oh actually nope this is going to be in
FK row zero
because that's our
oop our table name is one I'm sorry
and then we're going to drop
foreign whoops
Plus
uh FK row zero
and then we're going to execute it so
we're going to do uh cursor
destination
dot execute
hopefully this won't blow up because
we're using same cursor but I think
we're okay because we always have our
rows and now we should be able to do it
so
running this same thing
before when we got rid of this all right
let me pull that back out just to verify
it's still there so before it's going to
blow up
there it blows up now if I come up here
and I put our code in
to take care of that
uh let me just do this real quick just
to make sure we show see what we're
building here
uh
that's FK SQL let's make sure I got that
right
yeah okay
so now if we look at it
bam up now we're getting a nun type
object is not iterable oh
if
okay rows
oh
um
oh my mistake I need to do a fetch all
I knew there was something simple I was
missing there uh let's see
so that's going to be cursor destination
whoops
that's where the FK Rose is
put my S there
now
we should see that
and so now we're seeing cannot add or
update a childhood foreign key
constraint
fails
and
a good question so if we come through
here
before we get rid of it
oh we missed something we did not get
that right
uh let's see data sync for address
cannot update
uh what did we do here
cfk SQL check SQL
[Music]
and we did something not quite right
here so
let's see for table and Source tables
then I'm going to take
let's make sure we got that right maybe
I missed something there I probably did
uh print the check SQL
and if we look let's see select
constraint name
reference table name equals address
oh you know what
oh okay so that's none which is good uh
I wonder if it's uh giving me this
so he should come through
and we're missing something here so okay
let's figure out what we got in our
little typos
um
cannot add or updated child row
constraint
which should be fine
mean 20 but it's in DB sync 402
cannot add or update a child row
oh because we're sinking the rows at a
time we need to actually okay
what we need to do
is we actually need to go through here
and first uh
see
we're gonna do the same thing we've got
to get rid of all of these before we do
our truncate
so let's do this
uh first we're going to do that so we're
going to come through here we don't need
to know we have to do any of the
truncated stuff because we've already
done that
okay
and this is going to be
data clean for
and we're going to do that and then
we're going to do the whoops
let's take the that part
to where we were and so we're going to
come in here and we're going to go for
each table and first we're going to
clean out all the data because what was
happening is we're running into some
issues where we were pulling data in but
we had a foreign key that was blowing us
up so now let's see if that'll work
uh yeah we still have our prints and
we're still running into something here
so we're not finding any so when we find
one
let's make sure I got that right
I don't think I did I think I lost one
something there
um because I don't think it's foreign
key yeah that's why
that's what I thought I missed something
a typo okay so if I go in here and I do
4 key
now we're going to do it and now it's
going to bring all our data across
so once again we've
found a couple you know bumped our head
against something and been able to make
a correction for it now what we have
also found is that we did not you know
we had to go in and manually
create our
um our constraint here
what we're going to do next time around
is we need to be able to
uh we're going to be like let's say sync
constraints
sync non-primary constraints and that is
what we're going to tackle next time
that being said we can wrap this one up
while this thing is running in the
background trying to get some data moved
across
but while we're waiting for that go out
there and have yourself a great day a
great week and we will talk to you next
time
foreign