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're continuing our tutorial SQL python sort of combo thing and last time around we were getting into some of the uh the complicated Parts basically of the data and this episode we're going to continue through that we were we're basically getting into our uh let me see where I'm at here uh we're getting into the point we're actually walking through and copying in uh some of our data moving it based on relational issues and we hit a bug and so that was where we were last at so if we look at uh DB sync 518 so in here what's coming what it's doing is it's coming through and saying that hey we've got something that's not quite right and how I did this and let's see so what are we gonna do so we have this local variable insert base before silent so we are referring to it and the base is uh let's see I don't think we need values oh this is where okay so what we're going to do is we're going to come in we're going to load the data for the table but then what we're going to need to do is going to have to actually execute that which we get so that's what we got is we got our destination rows and let's uh let's do this real quick we're going to do a quit and we're just going to do a print whoop destination rows whoop that's not what I want I want to save it I'm going to run it and then it's going to come through oh so there aren't any so let's look at it's equal and see if we built that right and if I type that right so if we look at our SQL so let's see what did we break here select star from giganator.member where member ID and select distinct member from giganator.performer interesting so here oh let's do that and where was that from that was from the source so let's go somewhere down here we can execute there we go so he works fine but for here he works fine too so why let's see he's gonna from the source which is right he's gonna execute given me a no oh I know why um let's see I want to do a fetch haul so it's actually this way whoops uh let's do this little copy here sorry so I come in here that's going to be destination rows and I think I can do it like that so now if we print it there we go so here's all our rows and what we're going to need to do is we're going to go through we're going to create that base which we didn't before so let's go see if we can steal that code and that's here so we can do that first uh uh that's not the one I just did jump down to here we go so here's one I just did so I'm going to create that but here okay so here I'm gonna do first row first right because blah blah blah foreign so what I really want to do is for item in uh so I need to figure out the results which is going to be interesting because I gotta go figure that out first um so wait let's go back to here we're going to have to do this a little differently because what we did uh here we go let's describe and if we do all of that it's going to look something like that probably so um actually let's see and then we're going to do that whole thing well let's see uh it's here and here's our insert base here I think is that let's see if that works let's see what do we got here okay A little table for the here we go so destination rows which we're not using right now we did our description we get our description results we create out our base we don't need this so that's our data query and insert into that why did we do that before that's an interesting one I don't think we need that because now we're going to go through destination rows gives us all of our rows and then we're going to go through for each of those that's in the description results uh that's that now we do our values okay which is going to be up here I could actually walk through all of this but this seems a little here we go so he does that data query select blank from blank description limit blah blah blah blah that's the data query so he does sorceress okay where it's actually going to be destination rows this time um uh this one so for each one of these so we're going to come through we're going to do select blah blah blah I hate it a little differently last time so let's see see which one we're going to do that yeah and then we're going to do our values and then this is going to be in destination rows and then I'm going to come through I don't need Source rows anymore because I use that destination rows I guess I don't need first row [Music] um why do I use first row good question surveys if first don't think I need that so I probably don't even need first row and that's just to do the comma okay and then I'm going to come through and then I'm gonna do an execute so instead I want to do that I'm going to print my insert statement and let's see what this looks like and let's see I should have I think about what 10 different little insert statements so I'm going to so let's look at these and let's take a couple of these and just sort of see if those look right because if we come in here so it's going to say insert into performer which is okay connected.performer 4 ID blah blah blah blah blah which is uh each of these values oh it's taking oh this is a different so maybe I'm doing the wrong thing uh insert into table so this is going to be select referenced so my table name is here so that's where I found each of those so that's the table but I actually need to and so okay so contract has certain ones and then for example he has some performer stuff which is let's go ahead and print that out just because just to make sure we got the right one because I think I'm looking at two different records uh see here we go so here we go Okay so uh the reference table is member and it's referenced by performer and so oh okay so I'm grabbing the wrong I'm describing from the wrong table so what I need to do here when I do the describe where did I do that describe describe is not table now it's actually um I don't know why it's popping that up it's actually going to be Source row uh this needs to be the reference table name so it'd probably be one see what that looks like and let's see if this looks a little different so if I take those two so that's the one okay so if I take that that's my I should be dealing with a member is in for is inserting performers which should be that performer which uh oh because it's not getting the uh which I probably need that so I probably need uh this uh I need to do it like this there we go so now if I do it's gonna look a little different and well it's the same description but now I need to go to uh insert into member uh see yeah I use the table name wrong again so now yeah remember remember ID okay so now if I try that see so I want to do that locally just because if I go to my local database so there we go so if I go to local uh where did I just put that uh that's performer that's not what I want remember here we go so if I do that so it's going to give me a duplicate entry because I always have that record in there so I am now able to go through and actually create these records now the problem I've got is that I have not deleted all of the records at this point in the uh the destination so what I'm going to need to do which becomes its own little challenge because what we can do is we can say let's only add records so what we could do is we could just basically see hey if it doesn't exist then we'll just skip it I think we can do like an insert ignore I think it's that simple yep and so it's going to tell me it's success even though it's going to give me that little warning so I could do that I could do it that simply we're here I could just say okay if it already exists cool we're going to add it in in which case uh let's see let's do this let's do our execute and let's see what this does and oh so it is giving me that um so let's see am I in the right place so wait let's do that so maybe it doesn't like that it may not do it in this uh whoops insert statement it may be this driver is not going to give us out let's see I bet that's it so if I take this and run it yeah so here but now it's going to give me that it gives me an error here so I can't just get away from can't do it as simple as insert ignore but I really wanted to do anyways is get rid of that data beforehand so we can either go back to our truncation of tables which I think I just dropped that out of what it was doing if I go to main quit smart empty sink compare yeah so one of the things I'm not doing is I'm not actually going in I think I already dropped that out of here so I can go in and I can actually clean up the data delete it and then reinsert it or we can look at ways to add it and so I think what we're going to do first is we're going to go back to our removing of data which will be if I go to smart empty sink I think it's as simple as uh let's see select reference table blah blah blah oh I have somewhere here I want to do not referential load I want to do nope load table data I think calls uh somewhere is it sync ropes well drop data there we go so drop data I don't really need that guy which means I don't need oh did I not clean that up I didn't clean that up so I need to take a cursor destination Clues so and so drop data here is going to just like crank through this stuff uh except for it probably needs oh I think because I set that up front uh yeah I've got my databases set up so I should be able to yeah so see I have this D in feature flag so if I don't have it then I'm not going to drop it and so I need to actually do do I sync rows somewhere yeah here we go so now I'm going to go back and actually fix a few things so I start with the missing tables I deal with the feature Flags oh I'm sorry with the missing columns so what I can do is then come into here uh rows and that's we're going to start next time we're going to run this sucker through or see how it looks and we will uh carry on actually the next thing we'll probably look at is the ability to only bring in additional records so we're going to have to do a little bit of extra check to take a little longer but we're going to basically do the same insert except for now we're going to check beforehand we're gonna find a way to be able to avoid that that error and just say hey if it's not there then we don't have to worry about it which is going to allow us at some point to do some things like uh potentially working off time stamps and some things like that so still got some other ways you want to play around with data but I think that'll wrap it up for this one we're getting there we're starting to see that we are building this stuff out we can execute it we can cleanly add some rows in and we'll start playing around with that 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're
continuing our tutorial SQL python sort
of combo thing and last time around we
were getting into some of the uh the
complicated Parts basically of the data
and this episode we're going to continue
through that we were we're basically
getting into our
uh let me see where I'm at here
uh we're getting into the point
we're actually walking through and
copying in uh some of our data moving it
based on relational issues and we hit a
bug and so that was where we were last
at so if we look at uh
DB sync 518 so in here what's coming
what it's doing is it's coming through
and saying that hey we've got something
that's not quite right
and how I did this
and let's see so what are we gonna do so
we have this local variable insert base
before silent so we are referring to it
and the base is
uh let's see
I don't think we need
values
oh this is where okay so what we're
going to do is we're going to come in
we're going to load the data for the
table
but then what we're going to need to do
is going to have to actually execute
that which we get
so that's what we got is we got our
destination rows and let's uh let's do
this real quick we're going to do a quit
and we're just going to do a print
whoop
destination rows
whoop that's not what I want I want to
save it I'm going to run it
and then it's going to come through oh
so there aren't any
so let's look at
it's equal and see if we built that
right and if I type that right
so if we look at our SQL
so let's see what did we break here
select star from giganator.member where
member ID and select distinct member
from giganator.performer
interesting
so here
oh
let's do that and
where was that from that was from the
source
so let's go somewhere down here
we can execute there we go so he works
fine
but for here
he works fine too so why
let's see
he's gonna from the source which is
right he's gonna execute
given me a
no oh I know why
um
let's see I want to do a fetch haul so
it's actually
this way whoops uh let's do this little
copy here sorry
so I come in here
that's going to be destination rows
and I think I can do it like that
so now if we print it
there we go so here's all our rows
and what we're going to need to do
is we're going to go through we're going
to create that base
which we didn't before so let's go see
if we can steal that code
and that's here
so we can do that first
uh
uh that's not the one I just did
jump down to here we go so here's one I
just did
so I'm going to create that but here
okay so here I'm gonna do first row
first right because blah blah blah
foreign
so what I really want to do is for item
in
uh so I need to figure out
the results
which is going to be interesting because
I gotta go figure that out first
um so wait let's go back to here we're
going to have to do this a little
differently because what we did
uh here we go let's describe
and if we do all of that
it's going to look something like
that probably so
um
actually let's see
and then we're going to do that whole
thing well let's see uh it's here
and
here's our insert base
here
I think is that
let's see if that works
let's see what do we got here
okay A little table for the here we go
so
destination rows which we're not using
right now we did our description we get
our description results we create out
our base
we don't need this so that's our data
query
and
insert into that
why did we do that before that's an
interesting one I don't think we need
that because now we're going to go
through destination rows gives us all of
our rows
and then we're going to go through for
each of those that's in the description
results
uh that's that now we do our values okay
which is going to be up here
I could actually walk through all of
this but this seems a little
here we go
so he does that data query select blank
from blank description limit blah blah
blah blah that's the data query so he
does sorceress okay
where it's actually going to be
destination rows this time
um
uh this one
so for each one of these so we're going
to come through we're going to do select
blah blah blah
I hate it a little differently last time
so let's see see which one we're going
to do that yeah and then we're going to
do our values and then this is going to
be in destination rows
and then I'm going to come through I
don't need Source rows anymore because I
use that destination rows I guess I
don't need first row
[Music]
um
why do I use first row
good question
surveys
if first
don't think I need that
so I probably don't even need first row
and that's just to do the comma okay
and then
I'm going to come through
and then I'm gonna do an execute so
instead
I want to
do that I'm going to print my insert
statement
and let's see what this looks like
and let's see I should have I think
about what 10 different little insert
statements so
I'm going to so let's look at these and
let's take a couple of these and just
sort of see if those look right
because if we come in here
so it's going to say insert into
performer which is okay
connected.performer 4 ID blah blah blah
blah blah which is
uh each of these values
oh it's taking
oh this is a different
so maybe I'm doing the wrong thing uh
insert into
table
so this is going to be select referenced
so my table name is here
so that's where I found each of those
so that's the table but I actually need
to and so okay
so contract has certain ones
and then for example
he has some performer stuff which is
let's go ahead and print that out
just because
just to make sure we got the right one
because I think I'm looking at two
different records
uh see here we go
so here we go Okay so
uh the reference table is member and
it's referenced by performer
and so oh okay so I'm grabbing the wrong
I'm describing from the wrong table so
what I need to do here
when I do the describe where did I do
that describe describe is not table now
it's actually
um
I don't know why it's popping that up
it's actually going to be Source row
uh this needs to be the reference table
name so it'd probably be one
see what that looks like
and let's see if this looks a little
different
so if I take those two
so that's the one okay so if I take that
that's my I should be dealing with a
member
is in for is inserting performers which
should be
that performer
which uh oh because it's not getting the
uh which I probably need that so I
probably need
uh this uh I need to do it like this
there we go
so now if I do it's gonna look a little
different
and well it's the same description but
now I need to go to
uh insert into
member uh see yeah I use the table name
wrong again
so now
yeah remember remember ID okay so now if
I try that
see so I want to do that locally
just because if I go to my local
database
so there we go so if I go to local
uh where did I just put that uh that's
performer that's not what I want
remember here we go
so if I do that
so it's going to give me a duplicate
entry because I always have that record
in there so
I am now able to go through and actually
create these records now the problem
I've got is that I have not deleted all
of the records at this point
in the uh the destination so what I'm
going to need to do
which becomes its own little challenge
because what we can do is we can say
let's only add records
so what we could do is we could just
basically see hey if it doesn't exist
then we'll just skip it
I think we can do like an insert ignore
I think it's that simple
yep
and so it's going to tell me it's
success even though it's going to give
me that little warning so I could do
that I could do it that simply we're
here I could just say okay if it already
exists cool we're going to add it in in
which case
uh let's see let's do this let's do our
execute and let's see what this does
and oh so it is giving me that
um
so let's see am I in the right place
so wait let's do that
so maybe it doesn't like that
it may not do it in this uh whoops
insert statement it may be this driver
is not going to give us out
let's see
I bet that's it so if I take this
and run it yeah so here but now it's
going to give me that it gives me an
error here so I can't just get away from
can't do it as simple as insert ignore
but I really wanted to do anyways is get
rid of that data
beforehand
so we can either go back to our
truncation of tables which I think I
just dropped that out of what it was
doing
if I go to main
quit smart empty sink
compare yeah so one of the things I'm
not doing is I'm not actually going in I
think I already dropped that out of here
so I can go in and I can actually clean
up the data delete it and then reinsert
it
or we can look at ways to add it and so
I think what we're going to do first is
we're going to go back to our removing
of data
which will be if I go to smart empty
sink
I think
it's as simple as
uh let's see select reference table blah
blah blah oh I have somewhere here
I want to do not referential load I want
to do nope
load table data I think calls
uh
somewhere
is it sync ropes
well
drop data there we go
so drop data
I don't really need that guy
which means I don't need oh
did I not clean that up I didn't clean
that up
so I need to take a cursor destination
Clues
so and so drop data here is going to
just like crank through this stuff
uh except for it probably needs
oh I think because I set that up front
uh yeah I've got my databases set up so
I should be able to yeah
so see I have this D in feature flag so
if I don't have it then I'm not going to
drop it
and
so I need to actually do do I sync rows
somewhere yeah here we go
so now I'm going to go back and actually
fix a few things
so I start with the missing tables I
deal with the feature Flags oh I'm sorry
with the missing columns
so what I can do is then come into here
uh rows
and that's we're going to start next
time we're going to run this sucker
through or see how it looks and we will
uh carry on actually the next thing
we'll probably look at is the ability to
only bring in additional records so
we're going to have to do a little bit
of extra check to take a little longer
but we're going to basically do the same
insert except for now we're going to
check beforehand we're gonna find a way
to be able to avoid that that error and
just say hey if it's not there then we
don't have to worry about it which is
going to allow us at some point to do
some things like
uh potentially working off time stamps
and some things like that so still got
some other ways you want to play around
with data but I think that'll wrap it up
for this one we're getting there we're
starting to see that we are building
this stuff out we can execute it we can
cleanly add some rows in and we'll start
playing around with that 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