Detailed Notes
Focus for this episode: We start into building out the custom SQL to pull limited, but needed source data into our destination system.
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're continuing our SQL python series of tutorials sort of this combo as we're building a database synchronization app we are deep into this at this point and uh this episode will probably be a little more uh python focused because what we're going to do is uh what we've done is we've we're figuring out this sort of uh hierarchy of tables where some tables have referential have references to others and we need to make sure that we take the reference data as opposed to just saying Hey I want you know 10 rows out of this now for the ones that we call our root tables where it's basically hey this table doesn't have any references or it doesn't reference anything so we're just going to take X number of rows that's pretty easy but now we get into the non-root and what we did is we started sort of coding this thing out but what I want to do this time is I'm going to go in and I'm going to extract this out because this is going to end up being um it's going to end up being a procedure and it's one that's going to be let's see we'll do uh we'll call it a referee referential load so what we're going to do with this is this is going to and it's going to be something where it may actually call itself uh actually probably will call itself so we want to make sure that we can do that and we're just going to go ahead and do it right here uh where did I do that so non-root tables I'm going to take that and what I'm probably going to do right here is just do a straight up let's probably do it here is I'm going to do referential um actually I'll start self Dot referential load and then I'm going to give it the table and that may be oh and I'm going to give it imported tables because this is where I'm gonna have to figure out has my table been loaded and then I'm good or has it not as far as like all of the um the parents so I'm going to come up here and this is going to be a table and it is going to be what I call that loaded I'm sorry imported rows and it's uh see it I call it that here I'm sorry it's imported tables and then I probably want to return because I'm going to be updating this as I go um here we go imported tables equals so if I'm done then I'll be adding it to the imported tables there we go I'll be adding it to my list so now what I want to do is in here so I'm going to see if this is referenced in a foreign key so I'm going to try to load this if it is let's see so here if it's referenced then I'm going to have to deal with the parent so here if the parent needs to be loaded then all I have to do is do reference uh self Dot referential load a table comma oh it's actually going to be a parent comma imported tables and then imported tables equals let me get that right so there we go so I could see if it's there and he's not in oop this is imported tables right uh yes okay so the parents not then I've got to load it otherwise um oh so that's for each of those parents and then all I need to do is I'm going to go through once I've got that done uh let's see so I'm going to load all my parents and then all I have to do is load me so I'll actually bring this back up oh wait nope uh uh nope nope nope nope so what I need to do here is for the parents I'm going to have to build this little complicated thing out that's going to get sort of interesting oh no it's not so let's see so what I'm going to do is for each of these and this is where I get this select star and actually I can probably now this is going to be interesting because if I have multiple parents I may have to load for multiple rows uh which is where did I do that I think I had it uh oh so it was something like this oops so I'm going to do is I'm going to get a list of IDs and what I probably want to do uh let's see and so here's sort of what I want to do is I really want to take all of these and Union them I think what I want here and there so what I'm seeing here is this is going to be I'm going to build these things out into a union because what I want to do is say here's all the IDS that I need to cover all of my parents so what I need to do here is I need to say let's see this is going to go it's going to get a little more complicated because um yeah because I'm gonna have to build this out a little bit so what I need to do here is I'm going to do uh smart load SQL equals empty and then here I'm going to say if smart load sequel equals empty then all I have to do is do that else I'm going to do equals whoops smart load SQL Plus and here's going to be Union dot value or that big query type thing and so when I get done let's just get to here and see if I can create this smart load SQL for anybody so let's see what this looks like I don't know if this is going to run at all so let's find out where we at so if we come through here there we go so and so I've got something here that's not off oh I've got a tuple index out of range okay uh which I probably didn't get all these so I've got constraint stema huh what is my Tuple I wonder if it doesn't like this that's probably it this has probably got something a little messed up so let's go like this yep that's it that's going to be the problem there we go so now let's try it so we're going to come through do a little data clean blah blah blah blah blah and now we're still okay so we still have a tuple that's out of range and it is on 513. let's see did we miss something here so the row is zero one two three four let's just do print row and see what it does for me see if I missed something foolishly simple in it here we go up yep I did not get it so Source sequels or SQL Source row well that's my problem um that's what I want I want Source row and this needs to be Source row ah I figured it was something stupid like that so let's go in here and we're going to do this these things out and now let's take a look at it and what we should see is something that's I don't know if it's gonna have a union or not let's see what our first one is that we get uh nothing okay can't concatenate uh so I've got zero one two three four five so I've got that uh let's see what is oh it's gonna be so fetchall but I only need zero so let's look at it that way yeah it's a problem because it was a tuple and it's not there we go okay so my insert is going to look like this let's just pick one here uh giganator dot member [Music] hmm I wonder if that's my issue yep oops I need to move my quotes like that or do this yep there we go okay so let's fix my quotes that's what I figured I have here I need to do that for sorcerer zero source for zero Source row zero and Source row zero so now what I'm going to be doing is I'm going to be basically insert into the destination what I am selecting from the other uh and I wanted to do so how did we do that before is we took it and that's missing tables whoops smart empty referential load I don't need that I need uh where's my insert statement um foreign let's do this let's go find it okay so load table data is basically going to be [Music] oh here we go so for Row in and I need uh okay so I need my insert base which is here so let's do that steal that again uh referential load okay so here what I need to do is now I'm going to go um cursor source execute that and then I'm going to be destination rows equals and let me do a quit here just because and so now what I'm going to do is I don't need that can I call it Source rows I may be able to call it Source rows um nope okay so for for new row in destination rows there's my insert statement uh I need to do this [Music] where did I use row I didn't even use it oh for value and there we go I'm sorry for Value in new row current value equals that uh and so instead of executing in the destination I'm going to do a print insert statement cool okay so now let's go look at it and let's see what it's going to build and we should see a series of inserts uh we did not but I think we'll end here and we're going to debug this but we're getting pretty close next time around we will come in and hopefully we'll have this thing knocked out and be able to actually give it a shot that being said 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're
continuing our SQL python series of
tutorials sort of this combo as we're
building a database synchronization app
we are deep into this at this point and
uh this episode will probably be a
little more uh python focused because
what we're going to do
is uh what we've done is we've we're
figuring out this sort of uh hierarchy
of tables where some tables have
referential have references to others
and we need to make sure that we take
the reference data as opposed to just
saying Hey I want you know 10 rows out
of this
now for the ones that we call our root
tables where it's basically hey this
table doesn't have any references or it
doesn't reference anything so we're just
going to take X number of rows that's
pretty easy
but now we get into the non-root and
what we did is we started sort of coding
this thing out but what I want to do
this time is I'm going to go in
and I'm going to extract this out
because this is going to end up being
um
it's going to end up being a procedure
and it's one that's going to be let's
see we'll do
uh we'll call it a referee
referential load
so what we're going to do with this is
this is going to and it's going to be
something where it may actually call
itself
uh actually probably will call itself
so we want to make sure that we can do
that and we're just going to go ahead
and do it right here uh where did I do
that
so non-root tables
I'm going to take that
and what I'm probably going to do right
here is just do a straight up
let's probably do it here is I'm going
to do referential
um actually I'll start self Dot
referential load
and then I'm going to give it the table
and that may be
oh and I'm going to give it imported
tables
because this is where I'm gonna have to
figure out has my table been loaded and
then I'm good or has it not as far as
like all of the um
the parents
so I'm going to come up here
and this is going to be a table
and it is going to be what I call that
loaded
I'm sorry imported rows
and it's uh see it I call it that here
I'm sorry it's imported tables
and then I probably want to return
because I'm going to be updating this as
I go
um
here we go
imported tables equals
so if I'm done
then
I'll be adding it to the
imported tables there we go I'll be
adding it to my list
so now what I want to do
is in here so I'm going to see if this
is referenced in a foreign key so I'm
going to try to load this
if it is
let's see so here
if it's referenced
then I'm going to have to deal with the
parent so here if the parent needs to be
loaded then all I have to do is do
reference uh
self Dot referential load
a table comma oh it's actually going to
be a parent comma
imported tables
and then imported tables
equals let me get that right
so there we go
so I could see if it's
there and he's not in
oop this is imported tables
right
uh
yes okay so the parents not then I've
got to load it otherwise
um
oh so that's for each of those parents
and then all I need to do is I'm going
to go through once I've got that done
uh let's see
so I'm going to load all my parents and
then all I have to do is load me so I'll
actually bring this back up
oh wait nope uh uh nope nope nope nope
so what I need to do here is for the
parents I'm going to have to build this
little complicated thing out that's
going to get sort of
interesting
oh no it's not so let's see so what I'm
going to do is
for each of these
and this is where I get this select star
and actually I can probably
now this is going to be interesting
because if I have multiple parents I may
have to load for multiple rows
uh
which is where did I do that
I think I had it
uh
oh so it was something like this
oops
so I'm going to do is I'm going to get a
list of IDs
and what I probably want to do
uh
let's see
and so here's sort of what I want to do
is I really want to take all of these
and Union them
I think what I want here
and there so what I'm seeing here is
this is going to be I'm going to build
these things out into a union because
what I want to do is say here's all the
IDS that I need to cover all of my
parents
so what I need to do here is I need to
say let's see
this is going to go it's going to get a
little more complicated because
um yeah because I'm gonna have to build
this out a little bit so what I need to
do here is I'm going to do uh smart load
SQL equals empty
and then here
I'm going to say if
smart load sequel
equals empty
then all I have to do is do that
else
I'm going to do
equals
whoops
smart load
SQL
Plus
and here's going to be Union dot value
or that big query type thing
and so when I get done
let's just get to here and see if I can
create this smart load SQL for anybody
so let's see what this looks like
I don't know if this is going to run at
all so let's find out where we at so if
we come through here
there we go so and so I've got something
here that's not off oh I've got a tuple
index out of range okay
uh which I probably didn't get all these
so I've got constraint stema
huh
what is my Tuple
I wonder if it doesn't like this
that's probably it this has probably got
something a little messed up so let's go
like this
yep that's it
that's going to be the problem
there we go so now let's try it
so we're going to come through do a
little data clean blah blah blah blah
blah
and now we're still okay so we still
have a tuple that's out of range
and it is on 513.
let's see did we miss something here
so the row is zero one two three four
let's just do print row
and see what it does for me
see if I missed something foolishly
simple in it
here we go up yep I did not get it so
Source sequels or SQL
Source row
well that's my problem
um
that's what I want I want Source row
and this needs to be
Source row ah
I figured it was something stupid like
that so let's go in here
and we're going to do this
these things out
and now let's take a look at it
and what we should see is something
that's I don't know if it's gonna have a
union or not let's see what our first
one is that we get uh nothing okay can't
concatenate
uh so I've got zero one two three four
five so I've got that
uh let's see what is
oh it's gonna be
so fetchall but I only need
zero
so let's look at it that way
yeah it's a problem because it was a
tuple and it's not there we go okay
so
my insert is going to look like this
let's just pick one here
uh giganator dot member
[Music]
hmm
I wonder if
that's my issue
yep oops
I need to move my quotes
like that or
do this
yep there we go okay so let's fix my
quotes that's what I figured I have here
I need to do that for sorcerer zero
source for zero
Source row zero
and Source row zero
so now
what I'm going to be doing is I'm going
to be basically insert into
the destination
what I am selecting from the other
uh and I wanted to do so how did we do
that before
is we took it
and that's missing tables
whoops smart empty referential load
I don't need that I need
uh where's my insert statement
um
foreign
let's do this let's go find it
okay
so load table data
is basically going to be
[Music]
oh here we go so for Row in
and I need uh okay so I need my insert
base which is here
so let's do that
steal that again
uh referential load okay so here
what I need to do
is now I'm going to go
um
cursor source
execute
that and then I'm going to be
destination rows equals
and let me do a quit here just because
and so now what I'm going to do is I
don't need that
can I call it Source rows I may be able
to call it Source rows
um
nope okay so for for
new row in
destination rows
there's my insert statement uh
I need
to do this
[Music]
where did I use row I didn't even use it
oh for value and there we go I'm sorry
for Value in new row
current value equals that uh
and so instead of executing in the
destination
I'm going to do a print
insert statement cool okay so now let's
go look at it and let's see what it's
going to build
and we should see a series of inserts
uh we did not
but
I think we'll end here and we're going
to debug this but we're getting pretty
close next time around we will come in
and hopefully we'll have this thing
knocked out and be able to actually give
it a shot
that being said go out there and have
yourself a great day a great week and we
will talk to you next time
foreign