Detailed Notes
Focus for this episode: We move into trying to build our query to pull only required data in child tables. This mostly walks through some research and examples to try to build our solution.
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 season or series of tutorials we're looking at now we're sort of combining SQL in Python last episode we started working through a little bit of on the Fly problem solving and we're going to continue that this time our goal being to be able to limit rows that are pulled across but to grab the related rows in those cases because there's situations where it's not going to be the same and of course the rows we grab would not necessarily be the same now last time we left off we were able to we're going through here and we're basically saying hey uh when we're with this new smart empty sink thing that we're looking at we can go through all the root tables if they don't have anything else we're going to pull uh you know for example whatever our Max rows is so if we want to pull 100 rows we'll go to our root tables and we'll pull 100 rows of those that's pretty easy but then what we need to do is that means okay now we've got to go through the non-root tables and that we're going to go through we go through each of those we identify what the parents are and then for each of those we're going to load the parent table data before we get down to this one and what we can do is now this is where it can I because what we're going to end up doing is we're going to find for each one so for example here we've got two uh references and so this is actually going to be a good little tricky thing to do because what we're going to need to do for each reference that we have we're going to say we're going to go to the parent and we're going to select all the values that are that reference value and then we're going to use those values to select values from the child so it's going to look a little bit I think it's one of those it's probably worth doing it uh with not automated first so let's look here I'll show you what we're going to go through and then we'll come back later and we'll do the programming side so okay so this one let's look at uh let's see so we're going to look at let me order by table name I want to get a couple um I'd like to get a couple where it's the same twice um so just hit references same table uh I'll have to come back to that uh we'll take a look at that specific let's start with contract so the contract table references performer host and performance and let's go ahead and take the same one I'm going to need all of the values because I think that's one where we did not we're not grabbing the column that we need and so for example for contract we were we're going to uh we're going to select some ID from performer because then that's where we're going to have all of the we're going to find out what contracts we need and uh and it's really going to be just a distinct because we only need it for each one but we need to figure out what is that what's that ID value and so let's go look at the where constraint and we're going to label it we're going to limit this a little bit and table name equals contract so we'll simplify what we're doing right now so here's our three and the first one we're dealing with is performer and so performer here we can see that we have a reference table and we're going to be able to see uh let's see we need to find what it is referenced on so we're probably going to need our constraint information then uh let's see so that's a referential let's just make sure so that's catalog schema make sure I didn't mention it Miss it in the update rule delete rule table name reference table name so I need to know what the values are with that and so this is going to come from uh let's see referentials constraints we may have to flip back over to the code where did we find that we found that out of go back over here because we did that when we built our indexes drop table same columns missing indexes so we're probably going to do let's do show indexes let's just show indexes and I think it's from table name yep so if we do show indexes from and we'll get our table which is uh what do we say it was contract let me move this back up so I'm sort of moving doing all this coding in the same place let's come up here and let's do these okay so I may not need that so instead I'm going to do show indexes from contract okay so here I can see a key name and now here what you'll see is I don't have the column I'm sorry the table that it is uh linked to so what I'm going to need is I'm going to need this information here so I need to for example find this cons this constraint name here that's on performer because I've got to figure out what is how is it linked and so if I look at the indexes and then I can see here I can see where my performer is but I don't know how to get that back to a table and so I'm going to need probably from something else let's see what else have we found information on let's go look at the information schema if this may get a little bit complex um you let's go here let's just go to information schema it is show tables because what we're going to need is probably let's see if we look at like we have referential constraints so we knew that this is on so this is good so we do missing indexes we were going to get it from here so we needed to get it from the name on table column name oh this is an index I'm sorry we need foreign keys so we need oh did we not this may be a gap did we not get we've got indexes and oh it was in the table itself it was in the create table um so if we go to create table here we go we got that information from describe so let's try that so if we go here if we just do describe contract but we have to go back to the giganator one here if we go here okay so now see we're still Source stock because what we're trying to figure out is here where is our table so I wonder if we missed one here because that's a primary so primary key easy enough but what we do need uh that's referential constraints and this one is referential and constraints uh let's see referential constraints referential constraints so this may be a little bit tricky because what we need to do is rebuild that index and that's missing functions procedures so we're going to have to figure this sucker out we're gonna have to go back here then we're going to do a little bit of looking around so let's go look at actually I'm going to flip it over here because I think I can probably so if I go over here too information schema uh is he going to tell me oh it's just going to show me views it's not going to give me my tables okay so let's go back over here so I'm going to go here I'm going to show tables and so what I'm going to need is probably something so we know that it's not in referential constraints because if we pull everything from that we don't see we see our schema but we do not see I'm sorry we see our schema oh here we go shoot we did have it okay my mistake I totally missed it so good well there you can see where we sometimes go through stuff okay so here what I need to do is so that was on my left I can't believe I didn't go there so what I need to do here is I really need from this I need the referenced referenced table name and then I need uh oh from that let me do I need from that I need the column so that is it so I get my table name from there is this going to give me my oops uh let's go back over here and I do not have my now I do see here where I will see I can always go to that column but um wait let's go back so that's my table name but what I need is to figure out the how do I tie the column to the table that I'm creating and so I think this is going to be it so here I can see that I have it but I don't have a name um whoop if I come jump all the way back up here if I do star so I do need okay I'm sorry not as far along as I thought I was on this and being able to track this one down so for example here what I need to know I know this is performer ID but I need to figure out how do I know that other than I just know it here I don't know what my column name is in this table that ties to this uh this constraint so now I need to go back to my columns um did I do okay I lost it so I'm just gonna go here and do show tables whoop let's have some check constraints let's see what looks like it could be useful I have profiling referential contains constraints privileges stats variables tables let's look at tables and table constraints and see what those give me so let's do same thing but this is going to be let's do table constraints see if it gives me okay so here's table constraints and again it gives me the primary table but it doesn't give me the column nor the the table in the column that's left to and so now if I do what I say I can do constraints let's see if this works it's not constraints it's uh what did I call it it was table oh tables let's see if tables will give me anything okay so it's not constraint schema let's see if this will give me okay so here it just tells me what the table is uh and this is just table level stuff so it's not going to be tables let's see what else I can get so I could look at columns let's see if columns will get me anything will it uh let's see because I really want so it's not table constraints could be index statistics let's look at that real quick indexed statistics and that just gives me a table and index name an a schema ooh let's try that let's say we grab from statistics so I may have to do a little combo here where table name equals contract okay oops I'm in the wrong place am I um why okay interesting okay so I don't have any statistics so that's not the one I need uh I'll show tables again so let's see if I did let's see use your variables this may be an nodb one so let's go look at is there anything that looks really good there's an index table sys fields locks so I think we want to go back to so we did table constraints let's go look at oh we did look at table constraints didn't we uh from table strengths and that gives us just the name and nothing else it gives us our foreign key but what we don't get is we don't get our foreign keys anywhere so what we're going to need to do is I'm going to step into a little more research on this one because what we're not finding is how to link our table and our ID so we're sort of there we know that from here we're going to be able to look to it and we're going to say here's our parents but we need to know is what field in our table links us to the table there because here what we're going to do is [Music] um let's go to gigganite uh well [Music] we'll do it like this because what we're going to do is we're going to do here let's describe kickinator.performer and I'll show you where we're going with this uh that lowercase G it's a lowercase G okay so we're gonna want like for example for contract there is probably in here somewhere oh I'm sorry contract is oh which we can probably do it that way oh wait maybe we'll just go here we just grab the primary key so it's like oh my mistake we just fixed it all right I just found it so we're going to select distinct performer ID from oh okay from that except oh I'm sorry we need to do it from here we need to we need to do it from contract first so my mistake so what we want to do here if we describe contract maybe I'll be able to fix this and we'll have to come back in the next episode so this will be a cliffhanger so we describe this so here yeah what we need to do is we need to know like for example um uh so contract has a performer so what we need to do for oh that's right because this is for so contract needs to have those performers exist so we need to know what is the ID in the performer table because what we're going to need to do is we're going to do distinct uh we're going to actually select from contract we're going to select distinct performer ID and then that is where we're going to have to do select star from kinginator performer where uh where is I think it's a performer ID here uh from for former where let's do this where performer ID in this and that's going to give us the records that we need because we don't need anything if it's not in contract um oh there we go so that's what we're going to try to get to figure out how to automate that and that's what we're going to tackle next time around so again we're doing a little bit of problem solving through this but I think it's one of those that it's useful to see how we got here otherwise you may get a little bit lost we start cranking out some of the code so what we're going to you know we know what our goal is now what we have to do here is create this and so in this case we know that our table we know this is our table name so this is this is the current Table and we could get this performer ID in the select constraints uh can't we no we can get it from uh contract so we may be able to find it here but we know that this uh let's see where so this comes from the this is the parent which we got from table constraints um oh not sorry not table constraints from referentialing constraints so this is the reference table name uh so this is actually referenced table name and this is going to be the primary key so we're going to so we'll have to be able to get that what we know that we get this from the reference table name here and so what we need is we need to be able to see where in this constraint what is that and so we're going to dig that up next time apologies we're going a little long but like I said I think the problem solving may be useful as well it's good for us to just sort of go back and see what we did why we did it that being said now let's get to it so 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 season or series of
tutorials we're looking at now we're
sort of combining SQL in Python last
episode we started working through a
little bit of on the Fly problem solving
and we're going to continue that this
time our goal being
to be able to limit rows that are pulled
across but
to grab the related rows in those cases
because there's situations where it's
not going to be the same and of course
the rows we grab would not necessarily
be the same now last time we left off
we were able to we're going through here
and we're basically saying hey uh when
we're with this new smart empty sink
thing that we're looking at we can go
through all the root tables if they
don't have anything else we're going to
pull uh you know for example whatever
our Max rows is so if we want to pull
100 rows we'll go to our root tables and
we'll pull 100 rows of those
that's pretty easy
but then what we need to do is that
means okay now we've got to go through
the non-root tables and that we're going
to go through we go through each of
those we identify what the parents are
and then for each of those we're going
to load the parent table data before we
get down to this one
and what we can do is now this is where
it can I because what we're going to end
up doing
is we're going to find for each one so
for example here we've got two
uh references
and so this is actually going to be a
good little tricky thing to do because
what we're going to need to do for each
reference that we have
we're going to say
we're going to go to the parent
and we're going to select all the values
that are that reference value
and then we're going to use those values
to select values from the child
so it's going to look a little bit I
think it's one of those it's probably
worth doing it uh
with not automated first so let's look
here I'll show you what we're going to
go through and then we'll come back
later and we'll do the programming side
so okay so this one let's look at
uh let's see so we're going to look at
let me order by table name I want to get
a couple
um
I'd like to get a couple where it's the
same twice
um
so just hit references same table uh
I'll have to come back to that uh we'll
take a look at that specific let's start
with contract
so the contract table
references performer host and
performance
and let's go ahead and take the same one
I'm going to need all of the values
because I think that's one
where we did not we're not grabbing the
column that we need
and so for example
for contract we were we're going to uh
we're going to select some ID
from performer
because then that's where we're going to
have all of the we're going to find out
what contracts we need
and uh and it's really going to be just
a distinct
because we only need it for each one
but we need to figure out what is that
what's that ID value
and so let's go look at the where
constraint and we're going to label it
we're going to limit this a little bit
and table name equals
contract so we'll simplify what we're
doing right now
so here's our three
and the first one we're dealing with is
performer and so performer here
we can see that we have a reference
table
and we're going to be able to see
uh let's see we need to find what
it is referenced on so we're probably
going to need our constraint information
then
uh let's see so that's a referential
let's just make sure so that's catalog
schema
make sure I didn't mention it Miss it in
the update rule delete rule table name
reference table name
so I need to know what the values are
with that and so this is going to come
from
uh let's see referentials constraints
we may have to flip back over to the
code where did we find that we found
that out of
go back over here
because we did that when we built our
indexes
drop table same columns missing indexes
so we're probably going to do let's do
show indexes
let's just show indexes and I think it's
from table name
yep so if we do show indexes from
and we'll get our table which is
uh what do we say it was contract let me
move this back up so I'm sort of moving
doing all this coding in the same place
let's come up here
and let's do these okay
so I may not need that so instead I'm
going to do show indexes from contract
okay so here
I can see
a key name
and now here what you'll see is I don't
have the column I'm sorry the table
that it is uh linked to
so what I'm going to need
is I'm going to need this information
here
so I need to for example find this cons
this constraint name here
that's on performer because I've got to
figure out what is how is it linked and
so if I look at the indexes
and then I can see here I can see where
my performer is
but I don't know how to get that back to
a table
and so I'm going to need probably from
something else let's see what else have
we
found information on let's go look at
the information schema
if this may get a little bit complex
um
you let's go here
let's just go to information schema
it is show tables
because what we're going to need is
probably
let's see if we look at like
we have referential constraints
so we knew that this is on so this is
good so we do missing indexes
we were going to get it from here
so we needed to get it from the name
on table
column name oh this is an index I'm
sorry we need foreign keys
so we need
oh did we not this may be a gap did we
not get we've got indexes
and
oh it was in the table itself it was in
the create table
um
so if we go to create table
here we go
we got that information from describe
so
let's try that
so if we go here
if we just do describe contract but we
have to go back to the giganator one
here
if we go here okay
so now
see we're still Source stock because
what we're trying to figure out is here
where is our table
so
I wonder if we missed one here because
that's a primary
so primary key easy enough
but what we do need
uh that's referential constraints
and this one is referential and
constraints
uh let's see referential constraints
referential constraints
so this may be a little bit tricky
because what we need to do is rebuild
that index
and that's missing functions
procedures so we're going to have to
figure this sucker out we're gonna have
to go back here
then we're going to do a little bit of
looking around so let's go look at
actually I'm going to flip it over here
because I think I can probably so if I
go over here too
information schema
uh is he going to tell me oh it's just
going to show me views it's not going to
give me my tables okay
so let's go back over here
so I'm going to go here I'm going to
show tables
and so what I'm going to need is
probably
something so we know that it's not in
referential constraints
because if we pull everything from that
we don't see we see our schema
but we do not see I'm sorry we see our
schema
oh here we go
shoot we did have it okay my mistake I
totally missed it so
good well there you can see where we
sometimes go through stuff okay so here
what I need to do is so that was on my
left I can't believe I didn't go there
so what I need to do here is I really
need from this I need the referenced
referenced
table name
and then I need uh oh from that let me
do I need from that I need the
column so that is it so I get my table
name from there
is this going to give me my oops
uh let's go back over here
and I do not have my now I do see here
where I will see I can always go to that
column but
um wait let's go back so that's my table
name but what I need is
to figure out the how do I tie the
column to the table
that I'm creating
and so I think this is going to be it so
here I can see that I have it but I
don't have a name
um
whoop if I come jump all the way back up
here
if I do star
so I do need okay I'm sorry
not as far along as I thought I was on
this and being able to track this one
down so for example here what I need to
know I know this is performer ID but I
need to figure out how do I know that
other than I just know it here
I don't know what my column name is in
this table that ties to this uh
this constraint
so now I need to go back to
my columns
um
did I do okay
I lost it so I'm just gonna go here
and do show tables
whoop
let's have some check constraints let's
see what looks like it could be useful I
have profiling referential contains
constraints privileges
stats variables tables
let's look at tables and table
constraints
and see what those give me
so let's do
same thing but this is going to be let's
do table constraints
see if it gives me okay so here's table
constraints
and again it gives me the primary table
but it doesn't give me the column nor
the
the table in the column that's left to
and so now if I do what I say I can do
constraints let's see if this works
it's not constraints it's
uh what did I call it it was table oh
tables
let's see if tables will give me
anything
okay so it's not constraint schema
let's see if this will give me
okay
so here it just tells me what the table
is uh and this is just table level stuff
so it's not going to be tables
let's see what else I can get
so I could look at columns let's see if
columns will get me anything will it
uh let's see
because I really want so it's not table
constraints
could be index statistics
let's look at that real quick
indexed statistics
and that just gives me a table and index
name
an a schema ooh let's try that
let's say we
grab from statistics
so I may have to do a little combo here
where table name equals contract okay
oops I'm in the wrong place
am I
um
why okay
interesting okay so I don't have any
statistics so that's not the one I need
uh I'll show tables again
so let's see if I did
let's see use your variables
this may be
an nodb one so let's go look at
is there anything that looks really good
there's an index table sys fields
locks
so I think we want to go back to so we
did table constraints let's go look at
oh we did look at table constraints
didn't we uh from table
strengths
and that gives us just the name
and nothing else it gives us our foreign
key but what we don't get is we don't
get our foreign keys anywhere
so what we're going to need to do is I'm
going to step into a little more
research on this one
because what we're not finding is how to
link our table and our ID so we're sort
of there we know that from here
we're going to be able to look to it and
we're going to say here's
our parents but we need to know is what
field in our table links us to the table
there because here what we're going to
do is
[Music]
um
let's go to gigganite uh well
[Music]
we'll do it like this because what we're
going to do is we're going to do
here let's describe
kickinator.performer
and I'll show you where we're going with
this
uh
that lowercase G it's a lowercase G
okay so we're gonna want like for
example for contract
there is probably in here somewhere oh
I'm sorry contract
is
oh
which we can probably do it that way oh
wait maybe we'll just go here we just
grab the primary key so it's like oh my
mistake we just fixed it all right I
just found it so we're going to select
distinct performer ID
from oh
okay from that except
oh I'm sorry we need to do it from here
we need to we need to do it from
contract first
so my mistake
so what we want to do here if we
describe contract
maybe I'll be able to fix this
and we'll have to come back in the next
episode so this will be a cliffhanger so
we describe this
so here
yeah what we need to do is we need to
know like for example
um
uh so contract has a performer
so what we need to do for oh that's
right because this is for so contract
needs to have those performers exist
so we need to know what is the ID in the
performer table because what we're going
to need to do is we're going to do
distinct uh we're going to actually
select from
contract
we're going to select distinct performer
ID and then that
is where we're going to have to do
select
star from
kinginator performer
where
uh
where is I think it's a performer ID
here
uh from for former where
let's do this
where
performer ID in
this
and that's going to give us the records
that we need because we don't need
anything if it's not in contract
um oh
there we go so that's what we're going
to try to get to figure out how to
automate that and that's what we're
going to tackle next time around so
again we're doing a little bit of
problem solving through this but I think
it's one of those that it's useful to
see how we got here otherwise
you may get a little bit lost we start
cranking out some of the code so what
we're going to you know we know what our
goal is now what we have to do here is
create this and so in this case we know
that our table
we know this is our table name so this
is this is the current Table
and we could get this performer ID
in the select constraints
uh can't we no we can get it from uh
contract
so we may be able to find it here
but we know that this
uh let's see where so this comes from
the this is the parent
which we got from table constraints
um
oh not sorry not table constraints from
referentialing constraints
so this is the reference table name
uh so this is actually
referenced
table name and this is going to be the
primary key so we're going to so we'll
have to be able to get that
what we know that we get this
from the reference table name
here
and so what we need is we need to be
able to see where
in this constraint what is that and so
we're going to dig that up next time
apologies we're going a little long but
like I said I think the problem solving
may be useful as well it's good for us
to just sort of go back and see what we
did why we did it that being said now
let's get to it so go out there and have
yourself a great day a great week and we
will talk to you next time
foreign