Detailed Notes
Focus for this episode: We wrap up some code cleanup in this episode. This will position us for a more user friendly approach moving forward.
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
foreign [Music] our Series where we're looking at we're basically going through our SQL and Python tutorial combining those into our application and this episode We are continuing doing some some cleanup we essentially did a pretty much Brute Force raw kind of script and now we're going through and cleaning things up extracting things out into a separate class this MySQL synchronized that theoretically we could do it with you know have at some point an oracle or a SQL Server do it very SQL specific and we're doing some cleanup here and we are getting into our missing indexes uh is basically where we left off last time so let's uh dive right back into it I can do our missing indexes same thing we don't need a tables list now because it's actually going to be whoop let's take this and pop that there and this is going to be do I still have that yep I do okay so I've got that built this is going to be self.source tables and should be good there so we're going to do this again here and I'll do one more though oops there we go take this and actually let's go ahead down to sink Rose great missing column tables there we go I'm going to take that whole kit and caboodle there I don't know if we need all of it we're going to find out um so we did missing triggers right yep now we've got missing columns is good um I don't know we need to return missing columns but we will anyways missing indexes is I think all good sync columns okay so that's not going to be what we need so he's gonna be self dot Source tables we need our two um interesting we created these queries again each time so let's do that Christian destination pressure destination there we go and we're gonna do it again this time we're going to call him update cursor I know this is not the most uh glamorous of our tutorials but let's go ahead and get rid of those I don't think we need those right now uh let's see we don't need all those prints we're good we're good we're good we're coming through we found our differences we building out our stuff Bam Bam Bam we are good to go what is this another string complaining about now it might be referenced before assignment uh where is let's look at whoops null string equals not null and let's do here um let's make him equal to nothing right here there so we should be good uh he's gonna come around we're gonna do that and we get to sink rows which again is going to be uh self we probably don't need a table yet because we don't need tables this we do want our Max rows did I keep that I did not keep that my mistake so let's go back up here oh one more and we come down here the sink Rose that's going to be self.source tables and [Music] we're all good in here do we need anything else oh we don't need that extra there create missing tables it's going to be the same thing well okay so this just needs to be self uh sorry self.source and let me get rid of that little comment looking good and then so here we go so that's the end of our class right that should work so let's keep the comments here and we'll go over to Main and now we really don't need any of that oh I don't know we're gonna need any of that other than we're going to do import um all right let's see from DB sync import MySQL synchronize okay and then we're going to come here basically call it the same way but we're going to come in here [Music] and we're going to do a uh I'm just going to call sync equals my SQL synchronize and then we're going to do sync Dot connect to source sync dot connect to destination We compare the tables which is probably here compare tables we don't need this and then we're going to walk through these dot missing tables uh let's actually create missing tables I believe oh first we need sync Dot do we need I guess we do need missing tables so we need missing tables let's go look at that real quick oh because we did oh we need to set this in tables first okay uh let's see compare tables set missing tables is that how that works so we set the missing compare is based off of these so we have to get the yeah we have to generate compare first okay so that makes sense the pair set create um we don't really need that we don't need create missing tables here now we're going to do Source uh sync dot create whoop missing columns and uh so what we're going to need here is when we do missing columns we need to actually execute it so we weren't executing that I don't think so if we go to uh DB sync missing columns we were returning that let's see let's go ahead and do this and missing columns I print command uh really I can just take the cursor destination destination.execute I can probably do it like that okay so we're gonna do it like that we're gonna clean that up a little bit uh let's see so that's columns rows dot sync rows and that's going to be my X rows and then sync.missing triggers and he's going to have the two names and then sync Dot missing procedures there it is I don't give it those two uh see this is going to be sync.m mixing functions way back here missing indexes let's go ahead yeah we'll get these last couple okay sync dot missing indexes doesn't need anything um some missing indexes is doing the same I'm going to need to actually build this out and then we're good uh and so we're going to print uh application complete and we're going to go over here oh I'm going to spell it right and we're going to go to missing indexes and we're going to throw that little thing in because it apparently is creating our yeah so it just does it here so we're going to come through here for index in indexes uh let's just do this we'll do the same thing here so we're going to do um self Dot destinationdb.cursor execute failed to create cursor 2 close boom and sync columns is that not being called uh uh oh too many blank lines oh well too many blank lines too bad um there we go oh I wonder if it's supposed to be like that oh okay my mistake sometimes it complains it complains in different ways okay this is a silly little cleanup thing so we now have everything and it's a very short script it's going to call this if we call it it should run the same but it's going to give us some issues so uh sync.connect to Source oh because I probably didn't put oh I did put itself um not sure let's see oh okay so these need at least that so all right so it's a neat little thing but um so we'll get that taken care of and next time around we can continue looking at a little bit of our cleanup here and moving into the next piece apologies if we went a little bit long this time but hey that's you know somehow we got to go through a couple of these sort of boring things to clean some stuff up and make sure we're you know crushing all the t's dotting all the eyes but until next time go out there have yourself a great day a great week and we will talk to you next time [Music]
Transcript Segments
foreign
[Music]
our Series where we're looking at we're
basically going through our SQL and
Python tutorial combining those into our
application and this episode We are
continuing doing some some cleanup we
essentially did a pretty much Brute
Force raw kind of script and now we're
going through and cleaning things up
extracting things out into a separate
class this MySQL synchronized that
theoretically we could do it with you
know have at some point an oracle or a
SQL Server
do it very SQL specific and we're doing
some cleanup here and we are getting
into our missing indexes uh is basically
where we left off last time so let's uh
dive right back into it
I can do our missing indexes
same thing we don't need a tables list
now
because it's actually going to be whoop
let's take this
and pop that there
and this is going to be do I still have
that yep I do okay so I've got that
built
this is going to be self.source tables
and should be good there
so we're going to do this again
here
and I'll do one more though oops
there we go
take this and
actually let's go ahead down to sink
Rose great missing column tables there
we go
I'm going to take that whole kit and
caboodle there
I don't know if we need all of it we're
going to find out
um so we did missing triggers
right
yep
now we've got missing columns
is good
um I don't know we need to return
missing columns but we will anyways
missing indexes
is I think all good
sync columns
okay so that's not going to be what we
need
so he's gonna be self dot Source tables
we need our two
um
interesting we created these queries
again each time so let's do that
Christian destination
pressure destination there we go
and we're gonna do it again this time
we're going to call him update cursor
I know this is not the most uh glamorous
of
our tutorials but
let's go ahead and get rid of those I
don't think we need those right now
uh let's see we don't need all those
prints we're good we're good we're good
we're coming through we found our
differences we building out our stuff
Bam Bam Bam we are good to go what is
this another string complaining about
now it might be referenced before
assignment
uh where is
let's look at whoops null string
equals not null
and let's do here
um let's make him equal to nothing right
here
there
so we should be good uh he's gonna come
around we're gonna do that and we get to
sink rows which again is going to be uh
self
we probably don't need a table yet
because we don't need tables this we do
want our Max rows
did I keep that I did not keep that my
mistake so let's go back up here
oh one more
and we come down here
the sink Rose
that's going to be
self.source
tables and
[Music]
we're all good in here do we need
anything else
oh we don't need that extra there
create missing tables
it's going to be the same thing
well
okay
so this just needs to be self
uh sorry
self.source
and let me get rid of that little
comment
looking good and then so here we go so
that's the end of our class
right
that should work
so let's keep the comments here
and we'll go over to Main
and now we really don't need any of that
oh
I don't know we're gonna need any of
that other than we're going to do import
um
all right let's see
from
DB sync
import
MySQL synchronize okay
and then we're going to come here
basically call it the same way but we're
going to come in here
[Music]
and we're going to do a uh I'm just
going to call sync equals
my SQL synchronize
and then we're going to do
sync Dot
connect to source
sync dot connect to destination
We compare the tables which is
probably here
compare tables
we don't need this and then we're going
to walk through these dot missing tables
uh let's actually create missing tables
I believe
oh
first we need sync Dot
do we need
I guess we do need missing tables so we
need missing tables
let's go look at that real quick
oh because we did
oh we need to set this in tables first
okay
uh let's see compare tables
set missing tables is that how that
works
so we set the missing compare
is based off of these so we have to get
the yeah we have to generate compare
first okay so that makes sense
the pair set create
um
we don't really need that we don't need
create missing tables here
now we're going to do Source uh sync dot
create whoop
missing columns
and uh
so what we're going to need here is when
we do missing columns we need to
actually execute it so we weren't
executing that I don't think so if we go
to
uh DB sync
missing columns
we were returning that let's see let's
go ahead
and do this
and missing columns
I print command
uh really I can just take the cursor
destination
destination.execute
I can probably do it like that okay so
we're gonna do it like that we're gonna
clean that up a little bit uh let's see
so that's columns
rows
dot sync rows
and that's going to be my X rows
and then sync.missing triggers
and he's going to have the two names
and then
sync Dot
missing procedures
there it is
I don't give it those two
uh see this is going to be sync.m mixing
functions
way back here
missing indexes
let's go ahead yeah we'll get these last
couple okay
sync dot missing indexes
doesn't need anything
um
some missing indexes is doing the same
I'm going to need to actually build this
out
and then we're good
uh and so we're going to print uh
application complete
and we're going to go over here oh I'm
going to spell it right
and we're going to go to missing indexes
and we're going to throw that little
thing in because it apparently is
creating our
yeah so it just does it here so we're
going to come through here for index in
indexes
uh let's just do this we'll do the same
thing here so we're going to do
um
self Dot destinationdb.cursor
execute failed to create
cursor 2 close boom
and sync columns is that not being
called
uh uh oh too many blank lines oh well
too many blank lines too bad
um
there we go
oh I wonder if it's supposed to be like
that oh okay my mistake sometimes it
complains it complains in different ways
okay this is a silly little cleanup
thing so we now have everything and it's
a very short script it's going to call
this if we call it it should run the
same
but it's going to give us some issues so
uh sync.connect to Source oh
because I probably didn't put oh I did
put itself
um
not sure
let's see
oh okay so these need at least that so
all right
so
it's a neat little thing but um so we'll
get that taken care of and next time
around we can continue looking at a
little bit of our cleanup here and
moving into the next piece apologies if
we went a little bit long this time but
hey that's you know somehow we got to go
through a couple of these sort of boring
things to clean some stuff up and make
sure we're you know crushing all the t's
dotting all the eyes
but until next time go out there have
yourself a great day a great week and we
will talk to you next time
[Music]