Detailed Notes
Focus for this episode: We look at synchronizing indexes and start that process.
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
[Music] well hello and welcome back we are continuing our series of tutorials that are really crossing lines here between uh python and mysql last time around uh we finished up i was just dealing with a little bit of a synchronization bug on the the columns and it turned out that what i had was i was actually calling the sync columns i had that twice and the first time i was calling it i had the two databases reversed which doesn't help very much that actually was throwing it off quite a bit so that was just one of those little things particularly when you're running a script like this you want to make sure that you understand what your source and your destination is this time around um because one of the things that we bailed out on before is we bailed out on indexes when we were looking at the columns so i want to actually take that one um specifically i want to make that its own little thing so let's see when we did let's do missing columns and we're going to basically copy and paste this because we're going to turn it around and this is going to be missing indexes and we're going to get the same thing here let's see missing columns we don't need that uh i don't think we're going to return it this time i don't think there's a need for that and then instead let's see so i don't need that missing columns thing i don't need it here well actually let's do it that way let's keep it that way so we'll do this the same way is what we're going to do is we're going to return the missing indexes and we will build all of our sql together and then when we're done we'll go through and execute it because it may take a second anyway so what we're going to do is we're going to take the missing columns way down here um i don't need that run twice i'm not sure i had that let's do let's see so this is going to be called this idx's and this could be missing indexes and i'll show you oh that's idx's and idx's i'll call it idx we'll go ahead and print it because we can uh there we go oh except for the kind oh that is a command but that's all right we'll do it this way okay so that gets us started on it because now we want to go back up here and we're going to look at this missing indexes so actually right now first let's not we're just going to quit right here because we're going to get our missing indexes and the way we're going to do that is we're going to come in here and instead of the describe we are going to use show indexes and the same thing or show index is from and we're still going to have that table so show indexes from and what i want to do here is let's see i'm going to do that for both of these i'm going to call it well let's clean it up so we're going to call it the idx query instead of the col query and let's see so there's our cursors source idx with an s destination idx with an s uh destination name so it's me com index in destination indexes and then let's call idx and then this is going to be for column row and [Music] let's do this we'll do uh we'll do this we'll call it idx row hello that's in source idx's and then we're gonna come in here and that's gonna be the idx row let's do it this way so we have call row col roll we're gonna change that over yeah i know it's taking a little time i apologize so now this part let's just get rid of this thing completely because what we want to do is um [Music] let's we'll come back because this this is going to be basically this is going to be a create index and we know we need to do create index and it's going to be uh let's see plus name and let's call uh yeah let's call it that create that and we will go ahead and use our little uh things here just in case it's something weird so we create that index on and then we're gonna have to add the table whoops and then this is going to be we're going to need the column name and it looks something like that so if we go look at a create index example we can see here here i'm just going to snag one just so you can see what it looks like just a reminder and this one's actually an example that is a multi-column but basically we need this create index we give it the index name on whatever the table and then in parenthesis it's going to be the column and so in this case we're going to assume a single column but first let's see so now we're coming through here i think we have let's do this first let's just come through for each one that's for destination indexes and i'm not even sure so let's let's just do it this way because it's going to be easier as opposed to right through so when we do a show indexes we get something a little more complicated than what we've seen before so what we see here is in zero is going to be the table name um so what we need to do so that what we're going to need to do here is we're going to have to match the actually i guess we can do it by the oh see so it doesn't give us the index name because it's oh because it's a primary key let's go look at that real quick let's see if we can get show indexes i want to see and it's not going to give me the actual index name so what i'm going to need to do is i'm going to have to compare and it's going to be that i want the table and the column name actually this is probably a combo of stuff so actually let's just take the whole thing so if we look at destiny oops we're just going to take the whole thing so we're going to take uh call idx and we're just going to compare the entire array because it should be the same anything's different we're going to rebuild basically and so now we're gonna come in here and then we're gonna say the name is equal to let's do this so it'll be that it'll be table name plus um let's do column name which is uh one two three four idx row 4 [Music] because this is in yeah so this will be from there and we need to know if let's see if this will work idx row is not in destination names then i'm going to print it so let's just see how this works and i'm not going to make any real changes here so let's go play with that for a second so seeing what we've got now if we run it somewhere in here all right it's going to blow up a little bit because oh because we're doing something here oh here um we're doing this little creation which we don't need we don't need the column type we're gonna print the row um so call name is let's do this that's cll name and we need an actual name oh i'm sorry whoops we do need we want the name and the column name is going to be equal to idx4 and we're just going to keep the same name so let's see what we kick out here so doing this we're going to come in and we're going to see this uh here okay so we've got one that's not in the target and we can see oh actually quite a few are not in the target and it's not printing out my rows okay so let's go look at this real quick what did we do okay those columns are not oh here we go so here's where i step into it so i'm going to see here's all of my indexes that are in source and i can see that all of those are not in the target and then so for each one of those like it says here it says that this thing does not exist in my target so let's go try that one real quick so let's take a look at that create index whoops let's do it this way so he's address t1id what i want to do i'm going to change this to be that name where do i create the name when i create the name on that i want it to be here we go i'm going to add an underscore just because just because i can so it'll make this look better instead of this address t underscore one lb address underscore t1 um so let's just do that real quick so now let's create that index and see if i go over here and i create this index let's clean all this other crap out oops um let me use tutorial okay so it allowed me to create that and so i should see on most of these i'm going to see indexes and i don't think i had any multiples so let's go ahead and try this let's first run this again and now we're going to see that it's still not matching up so here we have uh address so i need to say let's see so [Music] is that right so it's saying that this row does not exist here so what i want to do here is i'm going to do um so i'm probably gonna have to get deeper into this and so let's just do it this way so now when i come through it's saying that that row does not exist but it does oh no it doesn't so it's because of that value right let's see address primary blah blah blah blah and i'm not sure what that is so if we do let's go back into that let's look at that and see what that um three is ah so see i created that one and i also have this there's a cardinality to it so i can probably get rid of that so what i'm gonna do is now i'm gonna have to get a little more complicated in this is i'm gonna have to build out a combo which is gonna be uh let's see in destinations what i need to do is i want to do uh i'm going to call this row string and he's going to be equal to or she i don't want to misgender it index 0 which is going to be the table plus the [Music] let's do the key name which is 2. and really we should be able to do those um because if we're in a table oh and this is in the same table so we know it's going to be the same table so really i just need that so i can probably do that let's do that let's see uh let's just do it this way name equals and oh wow let's fight quite a bit so maybe i can get away with it using the key name and then uh let's see so here i'm going to do this i'm going to say if the name oh here name equals 2 row 2 or column 2 again and if name is not in destination names then i'm gonna do that so now let's see what it looks like [Music] oop and if we run it we're going to see here so it's going to say that hey this doesn't exist oh let me print out sql uh print table sequel now let's look at it so if we see here so i've got this guy exists right it says it's not the same uh the primary is there and it's because i built this one here um which may mean i have the time those two swapped again on me um no missing indexes db db c2 let's go look up here so now let's look at missing index so here let's combine these two i may not maybe i don't have much uh i want to do um oh let's do name and names let's see so here we go so if name is not in name which it says it's not oh and so it's off of type id okay that makes sense the other one is showing up so we should be good it's interesting that it's giving me that type id let me go back to mine i may have those i may have miscounted on my oops let's try this again and we'll prob one more time there we go so if i see the indexes key name should be two so those should be my two names those are my two names but he's coming in he's saying to create an index which should not occur i wonder if we got some extra prints in here let's do this uh let's see that's missing columns okay so we're just going to do this real quick we're going to do a start missing index we have a domain okay so the first thing we come into here we do missing index is type id and it's saying that that doesn't exist in these so the two lists it built uh same column oh so that means it's not there okay so now let me uh let me go do that so i've moved on to the next one um whoops let's get rid of that same columns missing indexes let's get rid of a couple of these prints let's do that and if we go down here okay and now we're gonna get our sql so we should see that there's gonna be a bunch missing so also on index we have on address we have this one so now we should see if we create this oh if i create it right then next time through i'm going to see nothing in address so it's now going to run through all of them and if i don't quit i am going to not sync my rows because that's going to take a second i'm not going to sync my columns yeah let's just do quit right here just to be safe so i'm going to go through and fix all the indexes so now it's creating all those indexes but now i've got a duplicate so i've got an issue oh because i've got the uh i've got a duplicate name so we're gonna have to look into that one and that'll be what we take care of next time so go out there and have yourself a great day a great week and we will talk to you next time you
Transcript Segments
[Music]
well hello and welcome back we are
continuing our series of tutorials that
are really crossing lines here between
uh python and
mysql
last time around uh we finished up i was
just dealing with a little bit of a
synchronization bug on the
the columns
and
it turned out that what i had was i was
actually calling the sync columns i had
that twice
and the first time i was calling it i
had the two databases reversed which
doesn't help very much that actually was
throwing it off quite a bit so that was
just one of those little things
particularly when you're running a
script like this
you want to make sure that you
understand what your source and your
destination is
this time around um because one of the
things that we bailed out on before
is we bailed out on
indexes when we were looking at the
columns so i want to actually take that
one
um specifically
i want to make that its own little thing
so let's see when we did let's do
missing columns
and we're going to
basically copy and paste this because
we're going to turn it around and this
is going to be
missing indexes
and we're going to get the same thing
here let's see missing columns we don't
need that uh i don't think we're going
to return it this time i don't think
there's a need for that
and then instead let's see so i don't
need that missing columns thing i don't
need it here
well
actually let's do it that way let's keep
it that way so we'll do this the same
way is what we're going to do is we're
going to return the missing indexes
and we will build all of our
sql together
and then when we're done
we'll go through and execute it because
it may take a second anyway so
what we're going to do is we're going to
take the missing columns way down here
um
i don't need that run twice i'm not sure
i had that
let's do
let's see so this is going to be
called this idx's and this could be
missing indexes
and i'll show you
oh
that's idx's and
idx's
i'll call it idx
we'll go ahead and print it because we
can
uh
there we go
oh except for the kind oh that is a
command but that's all right we'll do it
this way
okay so that gets us started on it
because now we want to go back up here
and we're going to look at this
missing indexes so actually right now
first let's
not
we're just going to quit right here
because we're going to get our missing
indexes and the way we're going to do
that is we're going to come in
here
and instead of
the describe
we are going to use
show indexes and the same thing or show
index is from and we're still going to
have that table
so show indexes from
and what i want to do here is
let's see
i'm going to do that for both of these
i'm going to call it well let's clean it
up so we're going to call it the idx
query instead of the col query
and
let's see so there's our cursors source
idx
with an s
destination idx
with an s
uh destination name so it's me
com index
in
destination indexes
and then
let's call idx
and then this is going to be
for column row and
[Music]
let's do this we'll do
uh
we'll do this we'll call it idx row
hello that's in
source idx's
and then we're gonna come in here and
that's gonna be the idx row
let's do it this way so we have call row
col roll we're gonna change that over
yeah i know it's taking a little time i
apologize so now this part let's just
get rid of this thing completely because
what we want to do
is
um
[Music]
let's
we'll come back because this this is
going to be basically this is going to
be a create index
and we know we need to do create index
and it's going to be uh let's see
plus
name
and let's call uh yeah let's call it
that
create that
and
we will go ahead and use our little
uh things here just in case it's
something weird
so we create that index on
and then we're gonna have to add the
table whoops
and then this is going to be
we're going to need the
column name
and it looks something like
that so if we go look at a create index
example
we can see here here i'm just going to
snag one just so you can see what it
looks like just a reminder
and this one's actually an example that
is a multi-column but basically we need
this create index we give it the index
name on whatever the table and then in
parenthesis it's going to be the column
and so in this case we're going to
assume a single column
but first let's see so now we're coming
through here i think we have
let's do this first let's just come
through for each one
that's for destination indexes
and i'm not even sure so let's let's
just do it this way because it's going
to be easier as opposed to right through
so when we do a
show indexes
we get something a little more
complicated than what we've seen before
so what we see here is in zero is going
to be the table name
um so what we need to do so that
what we're going to need to do here
is we're going to have to match
the
actually i guess we can do it by the
oh see so it doesn't give us
the
index name
because it's oh because it's a primary
key
let's go look at that real quick let's
see if we can get
show indexes
i want to see
and it's not going to give me the actual
index name
so
what i'm going to need to do is i'm
going to have to compare and it's going
to be
that i want the table
and the column name
actually this is probably a combo of
stuff
so
actually let's just take the whole thing
so if we look at destiny oops
we're just going to take the whole thing
so we're going to take uh
call idx
and we're just going to compare
the entire array because it should be
the same anything's different we're
going to rebuild basically
and so now we're gonna come in here
and then we're gonna say
the name is equal to
let's do this
so it'll be that it'll be table name
plus
um
let's do column name which is uh one two
three four
idx row 4
[Music]
because this is in yeah so this will be
from there
and we need to know if
let's see if this will work idx row is
not in destination names then i'm going
to print it so let's just see how this
works
and i'm not going to make any real
changes here
so let's go play with that for a second
so seeing what we've got now if we run
it
somewhere in
here all right it's going to blow up a
little bit because
oh because we're doing something here oh
here um
we're doing this little creation which
we don't need
we don't need the column type we're
gonna print the row
um
so call name is let's do this
that's cll name
and we need an actual name oh i'm sorry
whoops
we do need we want the name
and
the column name
is going to be equal to
idx4
and we're just going to keep the same
name
so let's see what we kick out here
so doing this we're going to come in and
we're going to see this uh here
okay so we've got one that's not in the
target
and we can see
oh actually quite a few are not in the
target
and it's not printing out
my rows okay so let's go look at this
real quick
what did we do
okay those columns are not oh here we go
so here's where
i step into it
so i'm going to see here's all of my
indexes that are in source
and i can see that all of those are not
in the target
and then so for each one of those like
it says
here
it says that this thing
does not exist
in my
target so let's go try that one real
quick so let's take a look at that
create index
whoops let's do it this way
so he's
address t1id what i want to do i'm going
to change this to be
that name where do i create the name
when i create the name on that i want it
to be
here we go i'm going to add an
underscore
just because just because i can
so it'll make this look better instead
of this address t underscore one lb
address underscore t1
um so let's just do that real quick
so now let's create that index and
see if i go over here and i create this
index
let's clean all this other crap out
oops um
let me use tutorial
okay so it allowed me to create that
and so i should see on most of these i'm
going to see indexes and i don't think i
had any multiples
so let's go ahead
and try this let's first run this again
and now we're going to see
that it's still not matching up
so here we have
uh address so i need to say
let's see
so
[Music]
is that right
so it's saying
that this row does not exist
here so what i want to do here is i'm
going to do um
so i'm probably gonna have to get deeper
into this and so let's just do it this
way
so now when i come through it's saying
that that row
does not exist but it does oh no it
doesn't
so it's because of that value right
let's see address primary blah blah blah
blah
and i'm not sure what that is so if we
do let's go back into that let's look at
that and see
what that um
three is
ah so see i created that one and i also
have this there's a cardinality to it so
i can probably get rid of that so what
i'm gonna do is now i'm gonna have to
get a little more complicated in this
is i'm gonna have to build out a combo
which is gonna be
uh let's see in destinations what i need
to do is i want to do uh
i'm going to call this row string
and he's going to be equal to or she i
don't want to misgender it
index
0 which is going to be the
table plus
the
[Music]
let's do the key name
which is
2. and really
we should be able to do those
um
because if we're in a table
oh and this is in the same table so we
know it's going to be the same table
so
really i just need that
so i can probably do that let's do that
let's see uh let's just do it this way
name equals
and
oh
wow let's fight quite a bit so maybe i
can get away with it using the key name
and then uh let's see so here
i'm going to do this i'm going to say if
the name
oh here name equals 2
row 2
or column 2 again and
if name is not in destination names
then i'm gonna do that so now let's see
what it looks like
[Music]
oop
and if we run it we're going to see here
so
it's going to say that hey this doesn't
exist oh let me print out
sql
uh
print table sequel
now let's look at it
so if we see here so i've got this guy
exists
right it says it's not the same
uh the primary is there and it's because
i built this one here
um
which may mean i have the time those two
swapped again on me
um
no missing indexes
db db c2
let's go look up here
so now let's look at missing index so
here let's combine these two i may not
maybe i don't have much uh i want to do
um
oh
let's do name and names
let's see so
here we go
so if name is not in name
which it says it's not
oh and so it's off of type id okay that
makes sense the other one is showing up
so
we should be
good it's interesting that it's giving
me that type id
let me go back to mine i may have those
i may have miscounted on my
oops
let's try this again
and we'll prob
one more time
there we go
so if i see the indexes
key name
should be two
so those should be my two names
those are my two names
but he's coming in he's saying to create
an index
which should not occur
i wonder if we got some extra prints in
here
let's do this
uh let's see that's missing columns okay
so we're just going to do this real
quick
we're going to do a
start
missing index
we have a domain
okay
so the first thing we come into here we
do missing index is type id
and it's saying that that doesn't exist
in these so the two lists it built uh
same column
oh so that means it's not there okay
so now let me
uh let me go do that
so i've moved on to the next one um
whoops
let's get rid of that
same columns missing indexes let's get
rid of a couple of these prints
let's do that
and if we go down here
okay and now we're gonna get our sql so
we should see that there's gonna be a
bunch missing
so also on index we have on address we
have this one
so now we should see if we create this
oh if i create it right
then next time through
i'm going to see nothing in address so
it's now going to run through all of
them
and if i don't quit
i am going to not sync my rows because
that's going to take a second
i'm not going to sync my columns
yeah let's just do quit right here just
to be safe
so i'm going to go through and fix all
the indexes
so now it's creating all those indexes
but now i've got a duplicate
so i've got an issue oh because i've got
the
uh i've got a duplicate name so we're
gonna have to look into that one and
that'll be what we take care of next
time
so go out there and have yourself a
great day a great week and we will talk
to you
next time
you