Detailed Notes
Focus for this episode: This episode continues the exploration of pulling only needed and related data into the target tables. It is tedious, but a step towards the final product.
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 series on SQL and Python and this episode We are continuing looking at the complicated polling related data piece now I apologize I didn't kick I didn't turn on record I was actually troubleshooting a few things but uh luckily it's something I can catch us up pretty quick and straighten a few things out so first thing is is if you remember last time we were working from our constraint names so if we did uh here so we get our constraint name uh yeah so like in this case so whoop when I when I'm looking up uh because first thing we do is we find all the constraints related to a table so it'd be something like this I'm going to get all my constraint names and now with each constraint name what I want to do is I'm going to go in for that constraint name I'm going to say hey what is the referenced table because then what I need to do is I'm going to pull table from data from that table based on which IDs exist in my actual table and we one of the things that I missed was actually what we need to do is now we need to find all the things that reference that table name so that's going to be uh well in this case let's say host so I'm going to do for now what I'm going to do is this is going to be referenced table name and if we call it host hopefully it's going to be yeah so what we need to do is we're going to what we really need here is we're going to say okay all of these tables have data that references some record in here so what we're going to do is where we were running through and doing our our little Union thing that we were building which was all of the cases uh where let's see let's see if I've got that one sitting here real quick uh yeah should be stuff like this so I do this and get what's all the records where I'm gonna grab all the records where they are referred to and then I'm gonna have to Union that and we ended up doing something moderately complicated because we have to do that for each of the tables so if we go back to like our host we're going to need to do this for each of these tables which becomes a tad of a challenge because what we're going to have to do is for each one of those and that's actually we don't want it out of that we want it out of our constraints I think let's see yeah so it's actually instead what we need is we need to find each of these where uh reference table name equals our table name sort of host in this case because what we need to do yeah so each of these we got to figure out what their what the table is referring to so in this case we're going to build like a series of selects we weren't quite doing it right before so first thing we do and that's sort of where I got to so the first thing we're doing is we're going to grab our constraint so this is one where we get our cons based on our constraint name which is here and then what we want to do is we want to take the reference table name and we're going to build out this query here using the reference table name and actually we don't need to do it if that table's already been loaded so what we're going to do is we're going to come here uh Source SQL blah blah blah blah blah we're going to grab our okay so here we grab our table in our schema which is right here pointed that doesn't help since you're not seeing that so it's our table here and we're going to say if uh and this is a table Source table not in imported tables then we're going to have to do some loading on it and if it is then we can just skip it so if it is I'm sorry if it's not this is where we're going to come in and do this query based on the reference table name and so if we look here like that so if you see here this is this select reference table name constraint schema constraint name table name constraint schema let's just do that whole thing I think that's what I want I think I just go with those guys and so I'm going to do those from referential constraints and then it's going to be where reference table name equals the source table and then I'm going to set that guy up I'm going to execute and I'm going to do fetch all so this is going to give me uh this one right yeah so this is going to give me this one which is not the one I want is it this isn't what I want oh apologies I just blew up my okay let's fix that it's probably the one I had before I shouldn't have done that but that's okay so let's go back so we're going to take our select so select that oops from here oh where yeah we're reference table name equals okay so that gets us this list down here and so this is where we're going to start building our rather complicated stuff and here so let's say so the first one we're going to do is going to be very similar so we've got uh for Source row and Source rows is that what we want um let's see oh I'm sorry so we're going to take this now move that over because now what we want to do is we're going to go through our source rows which is there and for each one of those we don't need to worry about it being in imported tables because we've already checked for it up here and then in each of these we're going to take select from the constraint schema which is zero dot table where Source Row 2 the reference column name is in select distinct column name from the schema constraint schema and then the table name so if we do that and we're going to do the same thing here and so what we should see is let's do this let's see how this looks and we're going to print it hopefully we'll be able to see a good example here let's see what it's going to run for us and let's see so it's going to come in and it's going to do that and it's not really giving me too much here because oh so if it's not in the imported tables then I need to import it otherwise the data is good so I can move on oh and this is the problem is this whole thing so I have to insert that data so where am I at here so I'm doing the referential loads that means I'm going to go through all of these guys which means I can probably just take all of that and move it down one oops yeah if he's not in then I'm gonna have to describe them and we have to do my inserts and I'm going to build this big honking thing looks like this I think that's how it's going to look let's see how this runs through and see I should see one here at some point maybe not so if he's not in imported tables then he does a referential load if he is and I'll go through here I'm going to get all my parents and maybe missing one here let's see what did I do so check if it's referenced in a foreign key oh this may be what maybe I double dipped on this so that is this one all right maybe that one uh from referential constraints so it's here so let's see if it exists if it does okay that's my keys I don't really need that check SQL so I'm going to execute that blah blah blah blah I don't think I need you so all right let's just walk through this thing real quick so grab my rose if I get a row back then what I'm going to do is for each of those rows I'm going to walk back I'm going to look at the parent s here so if he's already covered if he's not in imported tables then I'm going to take him up a level and actually let's see what I can probably do is take this and not worry about that else because what happens is if he is not oh so if he's not then what I want to do oh okay then I want to do is I want to take him okay oh that's wait then I'm going to call him for the parents okay and let's reach of the parents okay so I may be all right and then I'm going to come in and I'm going to get the columns let's see if for example did I get all of my data come through it did not okay so that's what I thought so I'm going to come in if I find them I'm going to see is the oh those are those constraints so if all those constraints are loaded then I'm good otherwise which means if all of those constraints are loaded then I need to check to see I need to load the parents uh let's see otherwise and if I do it's where I'm off some so if I if parents are loaded then I gotta load this guy which is I think like that oh just do one more okay let's try that Okay so load all this parents now that his parents are done now I got to go figure out what I need to load for him so now I'm going to go in and find anywhere that he exists uh which actually I don't even need to do that because I already know what he is right so now I need to go find for that table and then uh let me get my so I'm going to make sure all of those guys are done so if he's not there oh actually I don't have to worry about that anymore because I what I need to do is check all the places where this table exists and I've already checked about him not being in there so I may be able to simplify this up quite a bit apologies while I'm thinking my way thinking on my feet here so let me go to all his parents which is here someone goes through all those parents I'm going to say are they loaded once they're loaded now I can do the contract so now what I'm going to do is I'm going to save like in this case I'm going to say everywhere that he's in there so he's referenced in contract document so now I just need to go to him which is let's see is this the right one so this is the one where referential constraints which is this one okay so I'm going to go through that and then I'm going to say all right for each of those I don't need this uh actually I guess I can do it from here so I can just go straight to uh I want to go straight to the call which is column usage or just keep calling usage I had that in here somewhere keep column usage so if I go to that to contract or you know let's say contract for this example and then I just need to build it so I can actually shrink that thing out uh and that's everywhere that this is equal to table oh which means I probably don't even need that check SQL so let's do this so now I'm going to get those I'm going to get my source rows do my smart load Source rows blah blah blah blah blah so let's do that and I don't have a source to now so I can close that I don't have to worry about closing him out so now let's look at it so I should see it quit right there so I'm going to do that so this is that contract one so I'm actually right on track for this oh and now I'm back to sort of where I was because what it's going to do is build this it only exists once okay oh contract document doesn't have any records oh so I don't even need a contract there you go okay so for example uh let's see look up States so I wonder if that means that there's nobody that uses lookup States okay and he's going to do that he's going to do that for item and results uh he's going to build that for River row and destination Rose which is probably going to be none so now if we look at our smart loaded sequel It's going to be probably nothing uh Source schema is and 509 so we probably missed one oh uh Source schema is let's just do we can do that we can go where'd we call that SRC schema equals Source rows zero zero well let's see this I do that otherwise if we have data for that then Source schema equals Source row zero and then where do I do that now I say if let's see this ifs or schema not equal to that then I'm actually going to go do some stuff then I have some stuff to insert and so I've got a couple of things so Source table is just going to be table oh no where is it some reference table name yeah so that's just table I don't really need it Source table is somewhere else I used it there we go that's just table and that means I'm done so I get to impend it and say that yes it's loaded and so if so if his Source schema is not equal to that then I do something otherwise what I can do is else did I give it a column number nope um let's do this comma Max rows and then Haze else gonna do uh he's gonna do load table data which is going to be table comma X rows so when I do referential load he also gets oops so who calls referential load when he gets called here he gets called from okay smart empty Max Rose so I think is that the only place he gets called yes it is so now we're gonna see this come through so that's our contract so we can go pick on this and we're getting a little long so we're probably gonna wrap this one up but let's go look real quick here okay so he does a smart load he's good and then here well let's go ahead and run it through let's see what happens I don't know if I'm going to quits left we'll find out in a second okay so we are blowing up here and so it looks like it's doing it because it already exists so it's double entering on uh the member table so we're going to go look there's someone we're probably not we're loading and we're probably not loading it into um probably not marking that it has been properly loaded it's not in the oh which is probably right here because I think we need uh imported tables equals so I think if we do load table data uh let's see so oh maybe not load table data does not return okay so uh let's sync rows which means oh I just load table data he doesn't add it to sync rows because there are sync tables oh that's sync we need smart sync uh so the table data is let's go back there load table data oops load table data load table there we go so here we actually need this to be we don't need that here but what we do know what we do need is to go to imported tables and add which we probably already have that somewhere I am not seeing it oh here we go so in this case he closes it up in this case he does that okay and then returns it so we should be in good shape let's see let's see we'll give it one more try here see if it comes through if it blows up again and it does so we're gonna go check that out next time around I think we've gone long enough this time and we will continue chasing this down because it is getting a little complicated and uh we'll just continue to walk through it and see where it's blowing up see where we have missed uh double entering a record and uh we'll just move forward from there hopefully we're getting pretty close and we'll be able to see this thing wrap up in the next episode or two that being said go out there and have yourself a great day a great week and we will talk to you next time thank you foreign
Transcript Segments
thank you
[Music]
well hello and welcome back we are
continuing our series on SQL and Python
and this episode We are continuing
looking at the complicated polling
related data piece now I apologize I
didn't kick I didn't turn on record
I was actually troubleshooting a few
things but uh luckily it's something I
can catch us up pretty quick and
straighten a few things out so first
thing is is if you remember last time we
were working from our constraint names
so if we did
uh here so we get our constraint name
uh yeah so like in this case
so whoop
when I when I'm looking up uh because
first thing we do is we find all the
constraints related to a table so it'd
be something like this I'm going to get
all my constraint names
and now with each constraint name what I
want to do
is I'm going to go in for that
constraint name I'm going to say hey
what is the referenced table
because then what I need to do is I'm
going to pull
table from data
from that table based on which IDs exist
in my actual table
and we one of the things that I missed
was actually what we need to do
is now we need to find all the things
that reference that table name
so that's going to be
uh well in this case let's say host so
I'm going to do
for now what I'm going to do is this is
going to be referenced
table name
and if we call it host
hopefully it's going to be yeah so what
we need to do
is we're going to what we really need
here is we're going to say okay all of
these tables
have data
that references some record in here
so what we're going to do is where we
were running through and doing our our
little Union thing that we were building
which was all of the cases
uh where let's see
let's see if I've got that one sitting
here real quick
uh yeah should be stuff like this so I
do this
and get what's all the records where I'm
gonna grab all the records where they
are referred to and then I'm gonna have
to Union that
and we ended up doing something
moderately complicated because we have
to do that for each of the tables so if
we go back to like our host
we're going to need to do this for each
of these tables
which becomes
a tad of a challenge because what we're
going to have to do is for each one of
those
and that's actually we don't want it out
of that we want it out of our
constraints I think let's see
yeah so it's actually instead what we
need is we need to find each of these
where
uh reference table name
equals our table name
sort of host in this case because what
we need to do yeah so each of these we
got to figure out what their what the
table is referring to so in this case
we're going to build like a series of
selects we weren't quite doing it right
before so first thing we do
and that's sort of where I got to so the
first thing we're doing is we're going
to grab our constraint
so this is one where we get our cons
based on our constraint name which is
here
and then what we want to do is we want
to take the reference table name and
we're going to build out this query here
using the reference table name
and actually we don't need to do it if
that table's already been loaded so what
we're going to do is we're going to come
here
uh Source SQL blah blah blah blah blah
we're going to grab our
okay so here we grab our table in our
schema which is right here pointed that
doesn't help since you're not seeing
that so it's our table here
and we're going to say if
uh and this is a table Source table
not in
imported tables
then we're going to have to do some
loading on it
and if it is then we can just skip it so
if it is I'm sorry if it's not
this is where we're going to come in and
do
this query
based on the reference table name and so
if we look here
like that so if you see here this is
this select
reference table name constraint schema
constraint name table name constraint
schema let's just do that whole thing
I think that's what I want I think I
just go with those guys and so I'm going
to do those
from referential constraints
and then it's going to be where
reference table name equals the source
table
and then I'm going to set that guy up
I'm going to execute and I'm going to do
fetch all so this is going to give me
uh this one right yeah so this is going
to give me this one
which is not the one I want is it this
isn't what I want
oh apologies I just blew up my okay
let's fix that
it's probably the one I had before I
shouldn't have done that but that's okay
so let's go back so we're going to take
our
select so select that
oops from here
oh
where
yeah we're reference table name equals
okay
so that gets us this list down here and
so this is where we're going to start
building our rather complicated stuff
and here so let's say so the first one
we're going to do
is going to be very similar so we've got
uh for Source row and Source rows is
that what we want
um
let's see oh I'm sorry
so we're going to take this now move
that over because now what we want to do
is we're going to go through our source
rows which is there and for each one of
those
we don't need to worry about it being in
imported tables
because we've already checked for it up
here
and then in each of these we're going to
take
select from the constraint schema which
is zero
dot table
where Source Row 2 the reference column
name
is in select distinct column name from
the schema
constraint schema and then the table
name
so if we do that and we're going to do
the same thing here
and so what we should see is let's do
this let's see how this looks
and we're going to print it
hopefully we'll be able to see
a good example here
let's see what it's going to run for us
and let's see so it's going to come in
and it's going to do that
and it's not really giving me too much
here because
oh so if it's not in the imported tables
then I need to import it otherwise the
data is good so I can move on
oh and this is the problem is
this whole thing
so I have to insert that data
so where am I at here so I'm doing the
referential loads that means I'm going
to go through
all of these guys which means I can
probably just take all of that and move
it down one oops
yeah if he's not in
then I'm gonna have to describe them and
we have to do my inserts
and I'm going to build this big honking
thing
looks like this
I think that's how it's going to look
let's see how this
runs through
and
see I should see one here at some point
maybe not
so if he's not in
imported tables
then he does a referential load
if he is and I'll go through here I'm
going to get all my parents
and maybe missing one here
let's see what did I do
so check if it's referenced in a foreign
key
oh this may be what maybe I double
dipped on this so that is
this one
all right maybe that one
uh from referential constraints so it's
here
so let's see if it exists if it does
okay that's my keys
I don't really need that check SQL
so I'm going to execute that blah blah
blah blah
I don't think I need you
so
all right let's just walk through this
thing real quick
so grab my rose if I get a row back then
what I'm going to do is for each of
those rows I'm going to walk back
I'm going to look at the parent
s here
so if he's already covered
if he's not in imported tables then I'm
going to take him up a level
and actually
let's see
what I can probably do is take this and
not worry about that else
because what happens is if he
is not oh
so if he's not then what I want to do oh
okay then I want to do is I want to take
him
okay oh that's wait
then I'm going to call him
for the parents okay
and let's reach of the parents
okay so I may be all right and then I'm
going to come in and I'm going to get
the columns
let's see if for example did I get all
of my
data come through
it did not
okay
so that's what I thought so I'm going to
come in if I find them
I'm going to see is the oh those are
those constraints so if all those
constraints are loaded then I'm good
otherwise
which means if all of those constraints
are loaded
then
I need to check to see I need to load
the parents
uh let's see otherwise and if I do
it's where I'm off some so if I if
parents are loaded then I gotta load
this guy which is
I think like that
oh just do one more
okay let's try that
Okay so
load all this parents now that his
parents are done now I got to go figure
out what I need to load for him
so now I'm going to go in and find
anywhere that he
exists
uh which actually I don't even need to
do that because I already know what he
is right
so now I need to go find for that table
and then uh let me get my so I'm going
to make sure all of those guys are done
so if
he's not there
oh actually I don't have to worry about
that anymore
because I what I need to do
is check all the places where this table
exists
and I've already checked about him not
being in there so I may be able to
simplify this up quite a bit
apologies while I'm thinking my way
thinking on my feet here
so
let me go to all his parents
which is
here
someone goes through all those parents
I'm going to say are they loaded once
they're loaded
now I can do the contract so now what
I'm going to do is I'm going to save
like in this case I'm going to say
everywhere that he's in there
so he's referenced
in contract document so now I just need
to go to him
which is let's see is this the right one
so this is the one where referential
constraints
which is this one okay so I'm going to
go through that and then I'm going to
say all right for each of those
I don't need this
uh actually I guess I can do it from
here so I can just go straight to
uh I want to go straight to the call
which is
column usage or just keep calling usage
I had that in here somewhere
keep column usage so if I go to that to
contract or you know let's say contract
for this example
and then I just need to build it so I
can actually shrink that thing out
uh and that's everywhere that this is
equal to table
oh which means I probably don't even
need that check SQL
so let's do this
so now I'm going to get those
I'm going to get my source rows
do my smart load Source rows blah blah
blah blah blah so let's do that
and
I don't have a source to now
so I can close that I don't have to
worry about closing him out
so now let's look at it
so I should see it quit right there
so
I'm going to do that so this is that
contract one so I'm actually right on
track for this
oh and now I'm back to sort of where I
was because what it's going to do is
build this it only exists once okay
oh contract document doesn't have any
records
oh so I don't even need a contract
there you go
okay
so for example
uh let's see look up States
so I wonder if that means that there's
nobody that uses lookup States okay
and he's going to do that he's going to
do that for item and results
uh he's going to build that for River
row and destination Rose
which is probably going to be none
so now if we look at our smart loaded
sequel It's going to be probably nothing
uh Source schema
is and 509 so we probably missed one
oh
uh Source schema
is
let's just do
we can do that we can go where'd we call
that SRC schema
equals
Source rows
zero zero
well let's see this
I do that
otherwise if we have data for that then
Source schema
equals
Source row zero
and then where do I do that
now I say if
let's see this ifs or schema
not equal to that then I'm actually
going to go do some stuff then I have
some stuff to insert
and so I've got a couple of things so
Source table
is just going to be table
oh no
where is it
some reference table name
yeah so that's just table I don't really
need it
Source table is somewhere else I used it
there we go that's just table
and that means I'm done so I get to
impend it and say that yes it's loaded
and so if
so if his Source schema is not equal to
that
then I do something otherwise what I can
do
is else
did I give it a column number nope
um
let's do this
comma Max rows
and then Haze else gonna do
uh he's gonna do load table data
which is going to be table comma
X rows
so when I do referential load
he also gets oops so who calls
referential load
when he gets called here he gets called
from okay smart empty
Max Rose
so I think
is that the only place he gets called
yes it is so now
we're gonna see this come through
so that's our contract so we can go pick
on this
and we're getting a little long so we're
probably gonna wrap this one up but
let's go look real quick here okay so he
does a smart load he's good
and then here
well let's go ahead and run it through
let's see what happens
I don't know if I'm going to quits left
we'll find out in a second okay so we
are blowing up here
and so it looks like it's doing it
because it already exists so it's double
entering on uh the member table so we're
going to go look there's someone we're
probably not we're loading and we're
probably not loading it into
um
probably not marking that it has been
properly loaded it's not in the oh which
is probably right here
because I think we need uh imported
tables equals
so I think if we do load
table
data
uh let's see
so
oh maybe not
load table data does not return okay so
uh let's sync rows
which means
oh I just load table data
he doesn't add it to sync rows because
there are sync tables
oh that's sync we need smart sync
uh so the table data
is
let's go back there
load table data oops
load table data load table there we go
so here
we actually need this to be we don't
need that here but what we do know
what we do need is to go to imported
tables and add
which we probably already have that
somewhere
I am not seeing it oh here we go
so in this case he closes it up in this
case he does that okay
and then returns it so we should be in
good shape let's see
let's see we'll give it one more try
here
see if it comes through if it blows up
again and it does so we're gonna go
check that out next time around I think
we've gone long enough this time
and we will continue chasing this down
because it is getting a little
complicated and uh we'll just continue
to walk through it and see where it's
blowing up see where we have missed uh
double entering a record and uh we'll
just move forward from there hopefully
we're getting pretty close and we'll be
able to see this thing wrap up in the
next episode or two that being said go
out there and have yourself a great day
a great week and we will talk to you
next time
thank you
foreign